摘要：MySQL一張表最多能存多少數據？ 本文分享自華為雲社區《為什麼MySQL單表不能超過2000萬行？》，作者： GaussDB 資料庫 。 最近看到一篇《我說MySQL每張表最好不要超過2000萬數據，面試官讓我回去等通知》的文章，非常有趣。 文中提到，他朋友在面試的過程中說，自己的工作就是把 ...
摘要：MySQL一張表最多能存多少數據？
本文分享自華為雲社區《為什麼MySQL單表不能超過2000萬行？》，作者： GaussDB 資料庫 。
最近看到一篇《我說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資料庫在性能和場景應用方面更勝一籌。
