關註上方 浩道Linux ,回復 資料 ,即可獲取海量 L inux 、 Python 、 網路通訊、網路安全 等學習資料!
前言
大家好,這裏是 浩道Linux ,主要給大家分享 L inux 、 P ython 、 網路通訊、網路安全等 相關的IT知識平台。
今天浩道跟大家分享一道運維面試中常見的經典題目----為什麽MySQL單表不能超過2000萬行?一起看看究竟是怎麽回事。
文章來源:https://www.cnblogs.com/huaweiyun/p/17420032.html
最近看到一篇【我說MySQL每張表最好不要超過2000萬數據,面試官讓我回去等通知】的文章,非常有趣。
文中提到,他朋友在面試的過程中說,自己的工作就是把使用者操作資訊存到MySQL裏,因為數據量超大(5000萬條左右),需要每天定時生成3張表,然後將數據取模分別存到這三張表裏。
下面是兩人的對話:
面試後續暫且不論,不過,互聯網江湖上的確流傳著一個說法:單表數據量超過500萬行時就要進行分表分庫,已經超過2000萬行時MySQL的效能就會急劇下降。
那麽,MySQL一張表最多能存多少數據?
今天我們就從技術層面剖析一下,MySQL單表數據不能過大的根本原因是什麽?
猜想一:是索引深度嗎?
很多人認為:數據量超過500萬行或2000萬行時,引起B+tree的高度增加,延長了索引的搜尋路徑,進而導致了效能下降。事實果真如此嗎?
我們先理一下關系,MySQL采用了索引組織表的形式組織數據,葉子節點儲存數據,非葉子節點儲存主鍵與頁面號的對映關系。若使用者的主鍵長度是8字節時,MySQL中頁面偏移占4個字節,在非葉子節點的時候實際上是8+4=12個字節,12個字節表示一個頁面的對映關系。
MySQL預設是16K的頁面,拋開它的配置header,大概就是15K,因此,非葉子節點的索引頁面可放15*1024/12=1280條數據,按照每行1K計算,每個葉子節點可以存15條數據。同理,三層就是15*1280*1280=24576000條數據。只有數據量達到24576000條時,深度才會增加為4,所以,索引深度沒有那麽容易增加,詳細數據可參考下表:
搜尋路徑延長導致效能下降的說法,與當時的機械硬碟和記憶體條件不無關系。
之前機械硬碟的IOPS在100左右,而現在普遍使用的SSD的IOPS已經過萬,之前的記憶體最大幾十G,現在伺服器記憶體最大可達到TB級。
因此,即使深度增加,以目前的硬體資源,IO也不會成為限制MySQL單表數據量的根本性因素。
那麽,限制MySQL單表不能過大的根本性因素是什麽?
猜想二:是SMO無法並行嗎?
我們可以嘗試從MySQL所采用的儲存引擎InnoDB本身來探究一下。
大家知道InnoDB引擎使用的是索引組織表,它是透過索引來組織數據的,而它采用B+tree作為索引的數據結構。
B+Tree操作非原子,所以當一個執行緒做結構調整(SMO,Struction-Modification-Operation)時一般會涉及多個節點的改動。
SMO動作過程中,此時若有另一個執行緒進來可能會存取到錯誤的B+Tree結構,InnoDB為了解決這個問題采用了樂觀鎖和悲觀鎖的並行控制協定。
InnoDB對於葉子節點的修改操作如下:
方式一,先采用樂觀鎖的方式嘗試進行修改
對根節點加S鎖(shared lock,叫共享鎖,也稱讀鎖),依次對非葉子節點加S鎖。
如果葉子節點的修改不會引起B+Tree結構變動,如分裂、合並等操作,那麽只需要對葉子節點進行加X鎖(exclusive lock,叫排他鎖,也稱為寫鎖)即可完成修改。如下圖中所示 :
方式二,采用悲觀鎖的方式
如果對葉子結點的修改會觸發SMO,那麽會采用悲觀鎖的方式。
采用悲觀鎖,需要重新遍歷B+Tree,對根節點加全域SX鎖(SX鎖是行鎖),然後從根節點到葉子節點可能修改的節點加X鎖。
在整個SMO過程中,根節點始終持有SX鎖(SX鎖表示有意向修改這個保護的範圍,SX鎖與SX鎖、X鎖沖突,與S鎖不沖突),此時其他的SMO則需要等待。
因此,InnoDB對於簡單的主鍵查詢比較快,因為數據都儲存在葉子節點中,但對於數據量大且改操作比較多的TP型業務,並行會有很嚴重的瓶頸問題。
在對葉子節點的修改操作中,InnoDB可以實作較好的1與1、1與2的並行,但是無法解決2的並行。因為在方式2中,根節點始終持有SX鎖,必須序列執行,等待上一個SMO操作完成。這樣在具有大量的SMO操作時,InnoDB的B+Tree實作就會出現很嚴重的效能瓶頸。
解決方案
目前業界有一個更好的方案B-Link Tree,與B+Tree相比,B-Link Tree最佳化了B+Tree結構調整時的鎖粒度,只需要逐層加鎖,無需對root節點加全域鎖。因此,可以做到在SMO過程中寫操作的並行執行,保持高並行下效能的穩定。
B-Link Tree主要改進點有2個:
1.中間節點增加link指標,指向右兄弟節點;
2.每個節點內增加欄位high key,儲存該節點中最大的key值。
新增的link指標是為了解決SMO過程中並行寫的問題,在SMO過程中,B-Link Tree對修改節點逐層加鎖,修改完一層即可放鎖,然後去加上一層節點的鎖繼續修改。這樣在InnoDB引擎中被SMO阻塞的寫操作可以有機會在SMO操作過程中並行進行。
如下圖所示,在節點2分裂為節點2和4的過程中,只需要在最後一步將父節點1指向新節點4時,對父節點1加鎖,其他操作均無需對父節點加鎖,更無需對root節點加鎖,因此,大大提升了SMO過程中寫操作的並行度。
由此可見,與B+Tree全域加鎖對比,B-Link Tree在高並行操作下的效能是顯著優於B+Tree的。GaussDB當前采用的就是B-Link Tree索引數據結構。
InnoDB的索引組織表更容易觸發SMO
索引組織表的葉子節點,儲存主鍵以及應對行的數據,InnoDB預設頁面為16K,若每行數據的大小為1000字節,每個葉子節點僅能儲存16行數據。
在索引組織表中,當葉子節點的扇出值過低時,SMO的觸發將更加頻繁,進而放大了SMO無法並行寫的缺陷。
目前業界有一個堆組織表的數據組織方案,也是華為雲資料庫GaussDB采用的方案。它的葉子節點儲存索引鍵以及對應的行指標(所在的頁面編號及頁內偏移),堆組織表葉子節點可以存更多的數據,分析可得在同樣的數據量與業務並行量下,堆組織表會比索引組織表發生SMO機率低許多。
效能對比
在8U32G的兩台伺服器分別搭建了MySQL(B+Tree和索引組織表)與GaussDB(B-Link Tree和堆組織表)的環境,進行了如下效能驗證:
實驗場景:在基礎表的場景上,測試增量隨機插入效能。
1.基礎表總大小10G,包含主鍵隨機分布的1000w行數據,每行數據1k;
2.插入主鍵隨機分布的1000w行數據,每行數據大小1k,測試並行插入效能。
結論: 隨著並行數的上升,GaussDB能穩步提升系統的TPS,而MySQL並行數的提高並不能帶來TPS的顯著提升。
綜上所述,MySQL無法支持大數據量下並行修改的根本原因,是由於其索引並行控制協定的缺陷造成的,而MySQL選擇索引組織表,又放大了這一缺陷。所以, 開源MySQL資料庫更適用於主鍵查詢為主的簡單業務場景,如互聯網類套用,對於復雜的商業場景限制比較明顯。
相比之下 ,采用B-Link Tree和堆組織表的GaussDB資料庫在效能和場景套用方面更勝一籌。
更多精彩
關註公眾號 「 浩道Linux 」
浩道Linux ,專註於 Linux系統 的相關知識、 網路通訊 、 網路安全 、 Python相關 知識以及涵蓋IT行業相關技能的學習, 理論與實戰結合,真正讓你在學習工作中真正去用到所學。同時也會分享一些面試經驗,助你找到高薪offer,讓我們一起去學習,一起去進步,一起去漲薪!期待您的加入~~~ 關註回復「資料」可 免費獲取學習資料 (含有電子書籍、視訊等)。
喜歡的話,記得 點「贊」 和 「在看」 哦