摘要：MySQL一張表最多能存多少數(shù)據(jù)？

本文分享自華為云社區(qū)《為什么MySQL單表不能超過(guò)2000萬(wàn)行？》，作者： GaussDB 數(shù)據(jù)庫(kù) 。

最近看到一篇《我說(shuō)MySQL每張表最好不要超過(guò)2000萬(wàn)數(shù)據(jù)，面試官讓我回去等通知》的文章，非常有趣。

文中提到，他朋友在面試的過(guò)程中說(shuō)，自己的工作就是把用戶操作信息存到MySQL里，因?yàn)閿?shù)據(jù)量超大（5000萬(wàn)條左右），需要每天定時(shí)生成3張表，然后將數(shù)據(jù)取模分別存到這三張表里。

下面是兩人的對(duì)話：

面試后續(xù)暫且不論，不過(guò)，互聯(lián)網(wǎng)江湖上的確流傳著一個(gè)說(shuō)法：?jiǎn)伪頂?shù)據(jù)量超過(guò)500萬(wàn)行時(shí)就要進(jìn)行分表分庫(kù)，已經(jīng)超過(guò)2000萬(wàn)行時(shí)MySQL的性能就會(huì)急劇下降。

那么，MySQL一張表最多能存多少數(shù)據(jù)？

今天我們就從技術(shù)層面剖析一下，MySQL單表數(shù)據(jù)不能過(guò)大的根本原因是什么？

猜想一：是索引深度嗎？

很多人認(rèn)為：數(shù)據(jù)量超過(guò)500萬(wàn)行或2000萬(wàn)行時(shí)，引起B(yǎng)+tree的高度增加，延長(zhǎng)了索引的搜索路徑，進(jìn)而導(dǎo)致了性能下降。事實(shí)果真如此嗎？

我們先理一下關(guān)系，MySQL采用了索引組織表的形式組織數(shù)據(jù)，葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)，非葉子節(jié)點(diǎn)存儲(chǔ)主鍵與頁(yè)面號(hào)的映射關(guān)系。若用戶的主鍵長(zhǎng)度是8字節(jié)時(shí)，MySQL中頁(yè)面偏移占4個(gè)字節(jié)，在非葉子節(jié)點(diǎn)的時(shí)候?qū)嶋H上是8+4=12個(gè)字節(jié)，12個(gè)字節(jié)表示一個(gè)頁(yè)面的映射關(guān)系。

MySQL默認(rèn)是16K的頁(yè)面，拋開(kāi)它的配置header，大概就是15K，因此，非葉子節(jié)點(diǎn)的索引頁(yè)面可放15*1024/12=1280條數(shù)據(jù)，按照每行1K計(jì)算，每個(gè)葉子節(jié)點(diǎn)可以存15條數(shù)據(jù)。同理，三層就是15*1280*1280=24576000條數(shù)據(jù)。只有數(shù)據(jù)量達(dá)到24576000條時(shí)，深度才會(huì)增加為4，所以，索引深度沒(méi)有那么容易增加，詳細(xì)數(shù)據(jù)可參考下表：

搜索路徑延長(zhǎng)導(dǎo)致性能下降的說(shuō)法，與當(dāng)時(shí)的機(jī)械硬盤和內(nèi)存條件不無(wú)關(guān)系。

之前機(jī)械硬盤的IOPS在100左右，而現(xiàn)在普遍使用的SSD的IOPS已經(jīng)過(guò)萬(wàn)，之前的內(nèi)存最大幾十G，現(xiàn)在服務(wù)器內(nèi)存最大可達(dá)到TB級(jí)。

因此，即使深度增加，以目前的硬件資源，IO也不會(huì)成為限制MySQL單表數(shù)據(jù)量的根本性因素。

那么，限制MySQL單表不能過(guò)大的根本性因素是什么？

猜想二：是SMO無(wú)法并發(fā)嗎？

我們可以嘗試從MySQL所采用的存儲(chǔ)引擎InnoDB本身來(lái)探究一下。

大家知道InnoDB引擎使用的是索引組織表，它是通過(guò)索引來(lái)組織數(shù)據(jù)的，而它采用B+tree作為索引的數(shù)據(jù)結(jié)構(gòu)。

B+Tree操作非原子，所以當(dāng)一個(gè)線程做結(jié)構(gòu)調(diào)整（SMO，Struction-Modification-Operation）時(shí)一般會(huì)涉及多個(gè)節(jié)點(diǎn)的改動(dòng)。

SMO動(dòng)作過(guò)程中，此時(shí)若有另一個(gè)線程進(jìn)來(lái)可能會(huì)訪問(wèn)到錯(cuò)誤的B+Tree結(jié)構(gòu)，InnoDB為了解決這個(gè)問(wèn)題采用了樂(lè)觀鎖和悲觀鎖的并發(fā)控制協(xié)議。

InnoDB對(duì)于葉子節(jié)點(diǎn)的修改操作如下：

方式一，先采用樂(lè)觀鎖的方式嘗試進(jìn)行修改

對(duì)根節(jié)點(diǎn)加S鎖（shared lock，叫共享鎖，也稱讀鎖），依次對(duì)非葉子節(jié)點(diǎn)加S鎖。

如果葉子節(jié)點(diǎn)的修改不會(huì)引起B(yǎng)+Tree結(jié)構(gòu)變動(dòng)，如分裂、合并等操作，那么只需要對(duì)葉子節(jié)點(diǎn)進(jìn)行加X(jué)鎖（exclusive lock，叫排他鎖，也稱為寫鎖）即可完成修改。如下圖中所示 ：

方式二，采用悲觀鎖的方式

如果對(duì)葉子結(jié)點(diǎn)的修改會(huì)觸發(fā)SMO，那么會(huì)采用悲觀鎖的方式。

采用悲觀鎖，需要重新遍歷B+Tree，對(duì)根節(jié)點(diǎn)加全局SX鎖（SX鎖是行鎖），然后從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)可能修改的節(jié)點(diǎn)加X(jué)鎖。

在整個(gè)SMO過(guò)程中，根節(jié)點(diǎn)始終持有SX鎖（SX鎖表示有意向修改這個(gè)保護(hù)的范圍，SX鎖與SX鎖、X鎖沖突，與S鎖不沖突），此時(shí)其他的SMO則需要等待。

因此，InnoDB對(duì)于簡(jiǎn)單的主鍵查詢比較快，因?yàn)閿?shù)據(jù)都存儲(chǔ)在葉子節(jié)點(diǎn)中，但對(duì)于數(shù)據(jù)量大且改操作比較多的TP型業(yè)務(wù)，并發(fā)會(huì)有很嚴(yán)重的瓶頸問(wèn)題。

在對(duì)葉子節(jié)點(diǎn)的修改操作中，InnoDB可以實(shí)現(xiàn)較好的1與1、1與2的并發(fā)，但是無(wú)法解決2的并發(fā)。因?yàn)樵诜绞?中，根節(jié)點(diǎn)始終持有SX鎖，必須串行執(zhí)行，等待上一個(gè)SMO操作完成。這樣在具有大量的SMO操作時(shí)，InnoDB的B+Tree實(shí)現(xiàn)就會(huì)出現(xiàn)很嚴(yán)重的性能瓶頸。

解決方案

目前業(yè)界有一個(gè)更好的方案B-Link Tree，與B+Tree相比，B-Link Tree優(yōu)化了B+Tree結(jié)構(gòu)調(diào)整時(shí)的鎖粒度，只需要逐層加鎖，無(wú)需對(duì)root節(jié)點(diǎn)加全局鎖。因此，可以做到在SMO過(guò)程中寫操作的并發(fā)執(zhí)行，保持高并發(fā)下性能的穩(wěn)定。

B-Link Tree主要改進(jìn)點(diǎn)有2個(gè)：

1.中間節(jié)點(diǎn)增加link指針，指向右兄弟節(jié)點(diǎn)；

2.每個(gè)節(jié)點(diǎn)內(nèi)增加字段high key，存儲(chǔ)該節(jié)點(diǎn)中最大的key值。

新增的link指針是為了解決SMO過(guò)程中并發(fā)寫的問(wèn)題，在SMO過(guò)程中，B-Link Tree對(duì)修改節(jié)點(diǎn)逐層加鎖，修改完一層即可放鎖，然后去加上一層節(jié)點(diǎn)的鎖繼續(xù)修改。這樣在InnoDB引擎中被SMO阻塞的寫操作可以有機(jī)會(huì)在SMO操作過(guò)程中并發(fā)進(jìn)行。

如下圖所示，在節(jié)點(diǎn)2分裂為節(jié)點(diǎn)2和4的過(guò)程中，只需要在最后一步將父節(jié)點(diǎn)1指向新節(jié)點(diǎn)4時(shí)，對(duì)父節(jié)點(diǎn)1加鎖，其他操作均無(wú)需對(duì)父節(jié)點(diǎn)加鎖，更無(wú)需對(duì)root節(jié)點(diǎn)加鎖，因此，大大提升了SMO過(guò)程中寫操作的并發(fā)度。

由此可見(jiàn)，與B+Tree全局加鎖對(duì)比，B-Link Tree在高并發(fā)操作下的性能是顯著優(yōu)于B+Tree的。GaussDB當(dāng)前采用的就是B-Link Tree索引數(shù)據(jù)結(jié)構(gòu)。

InnoDB的索引組織表更容易觸發(fā)SMO

索引組織表的葉子節(jié)點(diǎn)，存儲(chǔ)主鍵以及應(yīng)對(duì)行的數(shù)據(jù)，InnoDB默認(rèn)頁(yè)面為16K，若每行數(shù)據(jù)的大小為1000字節(jié)，每個(gè)葉子節(jié)點(diǎn)僅能存儲(chǔ)16行數(shù)據(jù)。

在索引組織表中，當(dāng)葉子節(jié)點(diǎn)的扇出值過(guò)低時(shí)，SMO的觸發(fā)將更加頻繁，進(jìn)而放大了SMO無(wú)法并發(fā)寫的缺陷。

目前業(yè)界有一個(gè)堆組織表的數(shù)據(jù)組織方案，也是華為云數(shù)據(jù)庫(kù)GaussDB采用的方案。它的葉子節(jié)點(diǎn)存儲(chǔ)索引鍵以及對(duì)應(yīng)的行指針（所在的頁(yè)面編號(hào)及頁(yè)內(nèi)偏移），堆組織表葉子節(jié)點(diǎn)可以存更多的數(shù)據(jù)，分析可得在同樣的數(shù)據(jù)量與業(yè)務(wù)并發(fā)量下，堆組織表會(huì)比索引組織表發(fā)生SMO概率低許多。

性能對(duì)比

在8U32G的兩臺(tái)服務(wù)器分別搭建了MySQL（B+Tree和索引組織表）與GaussDB（B-Link Tree和堆組織表）的環(huán)境，進(jìn)行了如下性能驗(yàn)證：

實(shí)驗(yàn)場(chǎng)景：在基礎(chǔ)表的場(chǎng)景上，測(cè)試增量隨機(jī)插入性能。

1.基礎(chǔ)表總大小10G，包含主鍵隨機(jī)分布的1000w行數(shù)據(jù)，每行數(shù)據(jù)1k；

2.插入主鍵隨機(jī)分布的1000w行數(shù)據(jù)，每行數(shù)據(jù)大小1k，測(cè)試并發(fā)插入性能。

結(jié)論：隨著并發(fā)數(shù)的上升，GaussDB能穩(wěn)步提升系統(tǒng)的TPS，而MySQL并發(fā)數(shù)的提高并不能帶來(lái)TPS的顯著提升。

綜上所述，MySQL無(wú)法支持大數(shù)據(jù)量下并發(fā)修改的根本原因，是由于其索引并發(fā)控制協(xié)議的缺陷造成的，而MySQL選擇索引組織表，又放大了這一缺陷。所以，開(kāi)源MySQL數(shù)據(jù)庫(kù)更適用于主鍵查詢?yōu)橹鞯暮?jiǎn)單業(yè)務(wù)場(chǎng)景，如互聯(lián)網(wǎng)類應(yīng)用，對(duì)于復(fù)雜的商業(yè)場(chǎng)景限制比較明顯。

相比之下 ，采用B-Link Tree和堆組織表的GaussDB數(shù)據(jù)庫(kù)在性能和場(chǎng)景應(yīng)用方面更勝一籌。

?

點(diǎn)擊關(guān)注，第一時(shí)間了解華為云新鮮技術(shù)~文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-454427.html

到了這里，關(guān)于為什么MySQL單表不能超過(guò)2000萬(wàn)行？的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！