B+樹(shù)索引如何對(duì)Mysql單表數(shù)據(jù)量造成影響

Mysql 單表適合的最大數(shù)據(jù)量是多少？

我們說(shuō) Mysql 單表適合存儲(chǔ)的最大數(shù)據(jù)量，自然不是說(shuō)能夠存儲(chǔ)的最大數(shù)據(jù)量，如果是說(shuō)能夠存儲(chǔ)的最大量，那么，如果你使用自增 ID，最大就可以存儲(chǔ) 2^32 或 2^64 條記錄了，這是按自增 ID 的數(shù)據(jù)類型 int 或 bigint 來(lái)計(jì)算的；如果你不使用自增 id，且沒(méi)有 id 最大值的限制，如使用足夠長(zhǎng)度的隨機(jī)字符串，那么能夠限制單表最大數(shù)據(jù)量的就只剩磁盤(pán)空間了。顯然我們不是在討論這個(gè)問(wèn)題。

影響 Mysql 單表的最優(yōu)最大數(shù)量的一個(gè)重要因素其實(shí)是索引。

我們知道 Mysql 的主要存儲(chǔ)引擎 InnoDB 采用 B+樹(shù)結(jié)構(gòu)索引。（至于為什么 Mysql 選擇 b+樹(shù)而不是其他數(shù)據(jù)結(jié)構(gòu)來(lái)組織索引，不是本文討論的話題，之后的文章會(huì)講到。）那么 B+樹(shù)索引是如何影響 Mysql 單表數(shù)據(jù)量的呢？

B+樹(shù)

一棵 B+樹(shù)如下所示：

Mysql 的 B+樹(shù)索引存儲(chǔ)在磁盤(pán)上，Mysql 每次讀取磁盤(pán) Page 的大小是 16KB，為了保證每次查詢的效率，需要保證每次查詢?cè)L問(wèn)磁盤(pán)的次數(shù)，一般設(shè)計(jì)為 2-3 次磁盤(pán)訪問(wèn)，再多性能將嚴(yán)重不足。Mysql B+樹(shù)索引的每個(gè)節(jié)點(diǎn)需要存儲(chǔ)一個(gè)指針（8Byte）和一個(gè)鍵值（8Byte）。因此計(jì)算16KB/（8B+8B）=1K 16KB 可以存儲(chǔ) 1K 個(gè)節(jié)點(diǎn)，3 次磁盤(pán)訪問(wèn)（即 B+樹(shù) 3 的深度）可以存儲(chǔ) 1K _ 1K _ 1K 即 10 億數(shù)據(jù)。

如果查詢依賴非主鍵索引，那么還涉及二級(jí)索引。這樣數(shù)據(jù)量將更小。

拆分

分而治之——沒(méi)有什么問(wèn)題不能通過(guò)拆分一次來(lái)解決，不行就拆多次。

Mysql 單表存儲(chǔ)的數(shù)據(jù)量有限。一個(gè)解決大數(shù)據(jù)量存儲(chǔ)的辦法就是分庫(kù)分表。說(shuō)白了就是一個(gè)數(shù)據(jù)庫(kù)一張表放不下那么多數(shù)據(jù)，那就分多個(gè)數(shù)據(jù)庫(kù)多張表存儲(chǔ)。

拆分可分為垂直拆分和水平拆分。

垂直拆分是按照不同的表（或者 Schema）來(lái)切分到不同的數(shù)據(jù)庫(kù)（主機(jī)）之上，水平拆分則是根據(jù)表中的數(shù)據(jù)的邏輯關(guān)系，將同一個(gè)表中的數(shù)據(jù)按照某種條件拆分到多臺(tái)數(shù)據(jù)庫(kù)（主機(jī)）上面或多張相同 Schema 的不同表中。

垂直拆分的最大特點(diǎn)就是規(guī)則簡(jiǎn)單，實(shí)施也更為方便，尤其適合各業(yè)務(wù)之間的耦合度非常低，相互影響很小，業(yè)務(wù)邏輯非常清晰的系統(tǒng)。在這種系統(tǒng)中，可以很容易做到將不同業(yè)務(wù)模塊所使用的表分拆到不同的數(shù)據(jù)庫(kù)中。根據(jù)不同的表來(lái)進(jìn)行拆分，對(duì)應(yīng)用程序的影響也更小，拆分規(guī)則也會(huì)比較簡(jiǎn)單清晰。

水平拆分與垂直切分相比，相對(duì)來(lái)說(shuō)稍微復(fù)雜一些。因?yàn)橐獙⑼粋€(gè)表中的不同數(shù)據(jù)拆分到不同的數(shù)據(jù)庫(kù)中，對(duì)于應(yīng)用程序來(lái)說(shuō)，拆分規(guī)則本身就較根據(jù)表名來(lái)拆分更為復(fù)雜，后期的數(shù)據(jù)維護(hù)也會(huì)更為復(fù)雜一些。

垂直拆分最直接的就是按領(lǐng)域拆分服務(wù)，隔離領(lǐng)域數(shù)據(jù)庫(kù)。如此每個(gè)庫(kù)所承擔(dān)的數(shù)據(jù)壓力就減少了。

水平拆分就是將同一個(gè) Schema 的數(shù)據(jù)拆分到不同的庫(kù)或不同的表中，這樣每個(gè)表的數(shù)據(jù)量也將減小，查詢效率將更高效。水平拆分就涉及到表的分片規(guī)則問(wèn)題。

幾種典型的分片規(guī)則包括：

按照用戶 ID 求模，將數(shù)據(jù)分散到不同的數(shù)據(jù)庫(kù)，具有相同數(shù)據(jù)用戶的數(shù)據(jù)都被分散到一個(gè)庫(kù)中。

按照日期，將不同月甚至日的數(shù)據(jù)分散到不同的庫(kù)中。

按照某個(gè)特定的字段求摸，或者根據(jù)特定范圍段分散到不同的庫(kù)中。

實(shí)現(xiàn)

門(mén)面模式——沒(méi)有什么問(wèn)題不能通過(guò)添加一個(gè)中間層來(lái)解決。

垂直拆分的一個(gè)方案就是在應(yīng)用層使用多個(gè)數(shù)據(jù)源，按業(yè)務(wù)訪問(wèn)不同的數(shù)據(jù)源。另外更好方案其實(shí)就是微服務(wù)化。按不同的業(yè)務(wù)領(lǐng)域來(lái)拆分微服務(wù)，明確領(lǐng)域邊界，隔離領(lǐng)域數(shù)據(jù)庫(kù)。這樣將對(duì)數(shù)據(jù)的存取內(nèi)聚到獨(dú)立的服務(wù)之中，對(duì)外提供統(tǒng)一的接口。在需要同時(shí)依賴多個(gè)服務(wù)時(shí)，我們可以通過(guò)添加門(mén)面應(yīng)用來(lái)組合底層服務(wù)的數(shù)據(jù)，以提供更符合上層業(yè)務(wù)需求的接口，這些服務(wù)往往更接近真實(shí)的業(yè)務(wù)。而底層的服務(wù)則是更加內(nèi)聚的資源服務(wù)。

代理模式——沒(méi)有什么問(wèn)題不能通過(guò)添加一個(gè)中間層來(lái)解決。

對(duì)于水平拆分應(yīng)該盡量屏蔽拆分帶來(lái)的數(shù)據(jù)訪問(wèn)困惱，為了讓上層業(yè)務(wù)無(wú)需關(guān)心下層數(shù)據(jù)組織方式。水平拆分往往通過(guò)添加一個(gè)代理層來(lái)做這些事情，代理層對(duì)上提供虛擬表，這些虛擬表就像我們?cè)趩螏?kù)上設(shè)計(jì)的單表一樣；代理層對(duì)下解析和拆分執(zhí)行 sql，然后按相應(yīng)規(guī)則在不同的庫(kù)和表執(zhí)行相應(yīng)的 sql 請(qǐng)求，再合并數(shù)據(jù)，并將合并后的結(jié)果返回給上層調(diào)用者。

一般代理方式分為如下兩種：

進(jìn)程內(nèi)代理

進(jìn)程內(nèi)代理即將代理層嵌入到業(yè)務(wù)服務(wù)內(nèi)部，攔截 sql 請(qǐng)求并做相應(yīng)的處理。這樣的好處是簡(jiǎn)單，但是侵入性大，且不夠靈活。

進(jìn)程外代理

進(jìn)程外代理即將代理獨(dú)立成服務(wù)，代理真實(shí)業(yè)務(wù)服務(wù)和數(shù)據(jù)庫(kù)之間的請(qǐng)求。這樣是比較復(fù)雜的，需要高可用的代理服務(wù)架構(gòu)。但是這樣對(duì)業(yè)務(wù)的侵入性低，且易于升級(jí)擴(kuò)展。

問(wèn)題

分布式事務(wù)問(wèn)題

什么是分布式事務(wù)？本地事務(wù)的定義就是一系列相關(guān)的數(shù)據(jù)庫(kù)操作完成后要滿足 ACID 四大特性，而分布式事務(wù)就是將同一進(jìn)程的操作放到不同的微服務(wù)進(jìn)程中，即不同微服務(wù)應(yīng)用進(jìn)程的數(shù)據(jù)庫(kù)操作滿足事務(wù)要求，或者對(duì)不同數(shù)據(jù)庫(kù)的一系列操作需滿足事務(wù)要求。

這里就有兩個(gè)問(wèn)題需要解決。一個(gè)是因?yàn)閼?yīng)用的分布式造成的，一個(gè)是因?yàn)閿?shù)據(jù)庫(kù)本身的分布式造成的。數(shù)據(jù)庫(kù)本身的分布式事務(wù)問(wèn)題一般由數(shù)據(jù)庫(kù)自身解決，大多數(shù)分布式數(shù)據(jù)庫(kù)都可以做到一定的數(shù)據(jù)一致性保證，如 HBase 保證的強(qiáng)一致性，Cassandra 保證的最終一致性。

應(yīng)用數(shù)據(jù)的一致性事務(wù)方案我們也可以參考分布式數(shù)據(jù)庫(kù)的實(shí)現(xiàn)原理來(lái)實(shí)現(xiàn)。業(yè)界也有很多分布式事務(wù)的解決思路，如：

XA 方案

TCC 方案

本地消息表

可靠消息最終一致性方案

最大努力通知方案

多表 Join 問(wèn)題

通過(guò)分析 Join sql，將 sql 拆分成獨(dú)立的查詢請(qǐng)求，然后分別執(zhí)行，并將結(jié)果合并計(jì)算返回給調(diào)用者。這個(gè)地方會(huì)涉及到很多執(zhí)行優(yōu)化的問(wèn)題。

數(shù)據(jù)統(tǒng)計(jì)問(wèn)題

當(dāng)數(shù)據(jù)被分片到不同的數(shù)據(jù)庫(kù)或不同的表中時(shí)，要對(duì)數(shù)據(jù)做一些全局的或涉及大量數(shù)據(jù)的統(tǒng)計(jì)時(shí)便會(huì)遇到一些問(wèn)題。如求 Max，Min，Sum 等聚合問(wèn)題。如果統(tǒng)計(jì)的數(shù)據(jù)有一定的業(yè)務(wù)規(guī)則，如只會(huì)按用戶維度去統(tǒng)計(jì)，如統(tǒng)計(jì)某個(gè)用戶的訂單量，那么對(duì)訂單表的分片，其實(shí)可以采用按用戶 id 來(lái)分片，如此就可以解決這類統(tǒng)計(jì)問(wèn)題。但是這種方案不通用。很多分片代理服務(wù)都需要將 sql 分片到不同的節(jié)點(diǎn)上去執(zhí)行，然后再合并結(jié)果返回。

ID 問(wèn)題

使用分庫(kù)分表之后，就無(wú)法使用 Mysql 的表自增作為 id，因?yàn)椴煌瑤?kù)和表的自增將出現(xiàn)沖突的 id。解決這個(gè)問(wèn)題就需要引入分布式 id 生成技術(shù)。

責(zé)任編輯：gt