本篇文章主要從InnoDB數(shù)據(jù)存儲結(jié)構(gòu)的角度分析，在何種情況下，SQL查詢效率會降低。 經(jīng)常在網(wǎng)上看到一些文章在吐槽，數(shù)據(jù)量大的情況下，查詢效率會降低很多。表關(guān)聯(lián)的多的時候，查詢效率會降低。單表數(shù)據(jù)量不要超過百萬等等。

數(shù)據(jù)庫版本： 8.0 引擎：InnoDB 參考資料：掘金小冊 《從根上理解Mysql》，有時間的建議親自看一下。

樣例表：

CREATE TABLE `hospital_info` (   `pk_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',   `id` varchar(36)  NOT NULL COMMENT '外鍵',   `hospital_code` varchar(36)  NOT NULL COMMENT '醫(yī)院編碼',   `hospital_name` varchar(36)  NOT NULL COMMENT '醫(yī)院名稱',   `is_deleted` tinyint DEFAULT NULL COMMENT '是否刪除 0否 1是',   `gmt_created` datetime DEFAULT NULL COMMENT '創(chuàng)建時間',   `gmt_modified` datetime DEFAULT NULL COMMENT 'gmt_modified',   `gmt_deleted` datetime(3) DEFAULT '9999-12-31 23:59:59.000' COMMENT '刪除時間',   PRIMARY KEY (`pk_id`),   KEY `hospital_code` (`hospital_code`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='醫(yī)院信息';

InnoDB 行格式

從一行數(shù)據(jù)開始看起，先了解一下單行數(shù)據(jù)的存儲格式。 目前行格式有4種，分別是Compact、Redundant、Dynamic和Compressed行格式。 在創(chuàng)建表的時候一般不需要刻意指定，5.7以上的版本會默認(rèn)Dynamic。 每種行格式大同小異，這里以Compact作為一個樣例，簡單的了解一下，每行數(shù)據(jù)是如何記錄的。

如上圖所示。 分為“額外信息”和“真實(shí)數(shù)據(jù)”兩個部分。

變長字段列表

這個比較有意思，一般在定義字段的時候都需要指定字段的類型和長度，

比如：樣例表中的hospital_code字段定義VARCHAR(36)。在實(shí)際使用中hospital_code字段長度只用了32位。

那剩下的4個字符長度會怎么辦？若強(qiáng)行填充空字符，豈不是白白浪費(fèi)4個字符的內(nèi)存。若不填充，怎么判斷當(dāng)前字段到底保存了多少個字符？占用多少內(nèi)存？

此時，變長字段列表就會按字段反序，用1~2個字節(jié)，記錄每個變長字段實(shí)際的長度。這樣可以有效的利用內(nèi)存空間。

與之類似的字段：VARBINARY、各種TEXT類型，各種BLOB類型。

相對的也存在“定長字段”，比如：CHAR(10)，該類型的字段，在初始化的時候就會默認(rèn)占用指定字符長度的空間，若不夠則填充空字符，因此對空間上是比較浪費(fèi)的，一般建議按需設(shè)置長度。

當(dāng)然“變長字段列表”不是必定存在的，若定義的字段類型沒有“變長字段”則不會有。

拓展：對于TEXT或BLOB類型的字段，長度很可能一頁無法存下，這時會將大部分?jǐn)?shù)據(jù)記錄在其他頁中，在當(dāng)前記錄中保留下一頁數(shù)據(jù)的地址。

NULL值列表

在實(shí)際保存數(shù)據(jù)的時候，某些列可能存儲的是NULL值，如果這些值都記錄在真實(shí)的數(shù)據(jù)中，則會浪費(fèi)存儲空間。在Compact格式中，會把這些值為NULL的列統(tǒng)一管理，存儲到NULL值列表中。

若一行數(shù)據(jù)中，沒有字段為NULL則不會產(chǎn)生此列。

存儲的方式也比較有意思，是二進(jìn)制方式倒序記錄。

以樣例表來分析，表中存在is_deleted、gmt_created、gmt_modified三個字段可能為空。假設(shè)在一條記錄中gmt_created、gmt_modified都為空，那對應(yīng)到NULL值列表中應(yīng)該是下面的樣子。

拓展： Mysql是支持二進(jìn)制數(shù)據(jù)存儲的，充分利用，可以減少很大的存儲空間。

記錄頭信息

記錄頭信息由固定的5個字符組成，即40個二進(jìn)制位長度。

先作為一個了解，這里有一個比較有意思標(biāo)識：delete_mask用過redis的都知道，redis的中被刪除的數(shù)據(jù)不會被立刻清除，相同的mysql中也一樣，被刪除的數(shù)據(jù)不會立刻被清理，因?yàn)榍謇淼倪^程會引發(fā)IO操作，這是很影響效率的。 被刪除的數(shù)據(jù)會組成一個鏈表，想當(dāng)與一個可復(fù)用的空間。

記錄真實(shí)數(shù)據(jù)數(shù)據(jù)

這個其實(shí)沒啥好說的，就是記錄真實(shí)的非NULL數(shù)據(jù)。

有一個網(wǎng)上經(jīng)常能看到的問題：若沒有設(shè)置主鍵會怎樣？

InnoDB下，主鍵是一條記錄的唯一標(biāo)識，如果用戶沒有指定，mysql會從Unique（唯一）鍵中選取一個作為主鍵，如果沒有Unique鍵，則會添加一個名為row_id隱藏列，作為主鍵。

此外還會添加添加 transaction_id（事務(wù)ID） 和 roll_pointer(回滾指針) 這兩個列。

小結(jié)

4種行格式大同小異，就不一一介紹了，都分為“額外信息”和“真實(shí)數(shù)據(jù)”兩個部分。區(qū)別主要在與“額外信息”記錄的內(nèi)容，以及變長字段的保存上的些許不同。

InnoDB數(shù)據(jù)頁

數(shù)據(jù)頁的概念，相信已經(jīng)耳熟能詳了。它是InnoDB管理存儲空間的基本單位，單頁大小一般是16KB。根據(jù)不同的目的設(shè)計了許多不同類型的頁，如：存放表空間頭部信息的頁，存放Insert Buffer信息的頁，存放INODE信息的頁，存放undo日志信息的頁等等。

頁空間劃分如下：

總共7個組成部分，大致描述一下7個部分。

其中File header和Page header中的屬性非常多，這里不一一介紹，只要知道這兩個地方記錄頁的一些屬性，比如：頁號，上一頁和下一頁的頁號，頁的類型，以及頁的內(nèi)存占用等等。這里說一下，頁與頁之間是雙向鏈表進(jìn)行連接的。數(shù)據(jù)記錄是單項(xiàng)鏈表。

File Trailer是校驗(yàn)頁數(shù)據(jù)完整性的，當(dāng)頁數(shù)據(jù)從內(nèi)存重新寫入磁盤的時候需要校驗(yàn)，防止數(shù)據(jù)頁損壞。

重點(diǎn)關(guān)注下User Records（已用空間）和Free Space（剩余空間），這里是保存真實(shí)的數(shù)據(jù)記錄。

此外 Infimum 和 Supremum，分別標(biāo)識最小記錄和最大記錄。即一個頁產(chǎn)生的時候，就默認(rèn)包含這兩條記錄，不過不用擔(dān)心這兩條記錄只是作為數(shù)據(jù)鏈表的頭和尾，不影響真實(shí)數(shù)據(jù)。

綜上，記錄在頁中的存儲如下：

簡單的來說，就是Free Space到User Records的轉(zhuǎn)化，當(dāng)Free Space耗盡時則視為數(shù)據(jù)頁已經(jīng)滿了。

到此，數(shù)據(jù)已經(jīng)寫入了數(shù)據(jù)頁中。那該怎么取出呢？上面知道了數(shù)據(jù)記錄是單項(xiàng)鏈表組成的，難道要從Infimum（最?。┯涗涢_始沿著鏈表遍歷嗎？

顯然，mysql的開發(fā)大佬不可能這么蠢，否則我上我也行，哈哈。

這里就要提到 Page Directory（頁目錄）了。在頁中，對數(shù)據(jù)進(jìn)行了分組，每組最后一條記錄的地址偏移量單獨(dú)提取出來按順序存儲到靠近頁尾的“頁目錄”中，頁目錄中的這些地址偏移量被稱為“槽”，此外最后一條記錄頭部（n_owned）還要保存所在分組中有多少條記錄。

頁目錄是由一個個的槽組成的。 整體結(jié)構(gòu)圖如下：

有了目錄之后，查詢就比較簡單了?？梢允褂?strong>二分法進(jìn)行快查。上圖中，知道最小槽為0，最大為4. 舉個栗子：

假設(shè)要查詢主鍵記錄為6的數(shù)據(jù)。

1）計算中間槽位置即（0+4）/ 2 = 2。取出槽對應(yīng)的記錄主鍵為8，因?yàn)?>6。

2）同理，將最大的槽設(shè)置為2，即（0+2）/2 =1,槽1對應(yīng)的主鍵為4，因?yàn)?4 < 6, 所以可以確定數(shù)據(jù)就在槽2中。

為了方便后續(xù)的描述，將頁的數(shù)據(jù)形式簡化為如下圖所示的樣子。

B+樹索引

不妨思考一個問題，前面說了。數(shù)據(jù)頁之間使用的是雙向鏈表鏈接的，大致如下圖所示：上圖可以看能出頁號并非連續(xù)的，也并不一定是連續(xù)的內(nèi)存空間（記住這句話后面會說到）。

假設(shè)每頁能存放3條記錄，現(xiàn)在有10w條記錄需要保存，則需要3w多個數(shù)據(jù)頁，此時會面對和單頁數(shù)據(jù)過多一樣的查詢問題，總不能逐個遍歷吧。此時也需要一個能快速快查詢的目錄，這個目錄就是“索引”。

在上圖所示的數(shù)據(jù)頁基礎(chǔ)上，可以形成如下的索引結(jié)構(gòu)：這種就是常說的聚簇索引，葉子即數(shù)據(jù)。這里要注意的一點(diǎn)，“頁30”中存放的是主鍵以及其所在的頁號。 如果說單個索引頁滿了，則會進(jìn)行分裂。產(chǎn)生如下所示的樹形結(jié)構(gòu)。不過上圖為了標(biāo)識方便，是不完全準(zhǔn)確的。應(yīng)該是先產(chǎn)生一個根節(jié)點(diǎn)，當(dāng)根節(jié)點(diǎn)滿了，則會進(jìn)行分裂。根節(jié)點(diǎn)則記錄分裂后的索引頁信息。

簡單的來說就跟樹木成長一樣，先從根再到樹干、樹枝、樹葉等。

二級索引與聚簇索引的思路是一樣的，差別在于二級索引的葉子節(jié)點(diǎn)不是真實(shí)數(shù)據(jù)，而是數(shù)據(jù)的主鍵。需要進(jìn)行回表操作才能獲取真實(shí)數(shù)據(jù)。

表空間

到目前為止，已經(jīng)知道單條數(shù)據(jù)的存儲結(jié)構(gòu)，以及最小的存儲數(shù)據(jù)單元頁。數(shù)據(jù)頁之間通過雙向鏈表進(jìn)行連接，并且數(shù)據(jù)頁之間是不一定連續(xù)的。

此時，產(chǎn)生了一個問題，同一個表的記錄，如果所在的頁在內(nèi)存地址上相距過遠(yuǎn)怎么辦？ 設(shè)想一下為了找3個人，他們分別再北京、紐約、倫敦。你要挨個去找，中間要浪費(fèi)大量的時間在旅途中。如果把他們聚集在一個國家，甚至一個城市，那就要快很多。

于是區(qū)的概念誕生了。區(qū)是由連續(xù)的64個頁組成，默認(rèn)情況下一個區(qū)占用1M的內(nèi)存。在申請內(nèi)存的時候，一次性占用1M的空間，其中的數(shù)據(jù)頁都是相鄰的，一定程度上解決了隨機(jī)IO的問題。

在區(qū)的基礎(chǔ)上，為了更有效的提升查詢效率，將B+樹的葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)記錄在不同的區(qū)中，這些區(qū)的集合被成為“段（segment）”。 在此概念下，插入第一條記錄，就需要申請2個區(qū)空間，一個聚簇索引根節(jié)點(diǎn)，一個數(shù)據(jù)頁，這一次就需要申請2M的空間！ 啥也沒干呢，2M空間就沒了，這合理嗎？顯然，這很不合理。

因此又搞出一個"碎片區(qū)"的概念。碎片區(qū)直屬于表空間，不屬于任何一個段。分配內(nèi)存的流程轉(zhuǎn)變成：

1）剛開始插入數(shù)據(jù)時，從碎片區(qū)以單個頁面來分配存儲空間。

2）當(dāng)某個段已經(jīng)占用了32個碎片區(qū)頁面后，就會以完整的區(qū)來分配空間。

表空間還分為：系統(tǒng)表空間和獨(dú)立表空間，此外還有區(qū)的XDES Entry數(shù)據(jù)結(jié)構(gòu)。內(nèi)容過多且復(fù)雜，需要了解的可以去看原書。

思考

1）索引越多越好嗎？多了會有 什么影響？

那肯定不是越多越好，上面可以知道，索引的記錄也是需要內(nèi)存損耗的。每個索引都會對應(yīng)一個B+樹，每個樹有需要2個段分別記錄葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)。這么下來會帶來很多內(nèi)存的浪費(fèi)。 僅僅是這樣的話也不是不能接受，畢竟索引本身的意義就是用空間換時間。但我們要知道，數(shù)據(jù)的增刪改，會導(dǎo)致索引的變化，需要索引重新分配節(jié)點(diǎn)，以及頁內(nèi)存的回收分配。這些都是IO操作，若索引過多，勢必導(dǎo)致性能的降低。

因此合理的利用聯(lián)合索引，可以解決單個索引過多的問題。此外索引有長度限制，過長的字段不適合作為索引。

2）索引為何查詢效率這么高？

這個其實(shí)屬于算法問題，以聚簇索引為例，假設(shè)非葉子節(jié)點(diǎn)的索引頁，每個能記錄1000條數(shù)據(jù)，葉子節(jié)點(diǎn)每個能記錄500條數(shù)據(jù)，一個3層的B+樹（不算根節(jié)點(diǎn)），能存放10001000500條記錄。一個3層結(jié)構(gòu)的索引能存放這么多記錄，每次只需幾次查詢就能定位數(shù)據(jù)，效率自然也就高了。

實(shí)際上單個索引頁所能記錄的數(shù)據(jù)要比這大的多。

同樣的這里可以思考一個問題，若葉子節(jié)點(diǎn)中的單條數(shù)據(jù)非常大，大到一個數(shù)據(jù)頁只能存放3條記錄，這時B+樹的深度就會增加，因此合理的減少表中單條記錄的大小，也是一種優(yōu)化。

3）數(shù)據(jù)量大，sql會執(zhí)行緩慢？

其實(shí)這個問題真的很想吐槽，動不動就百萬數(shù)據(jù)查詢效率xx秒，太慢了。不否認(rèn)mysql的性能的確弱于一些數(shù)據(jù)庫，但是百萬的數(shù)據(jù)量就慢的，想想自己的SQL和表結(jié)構(gòu)設(shè)計是否合理。別說百萬級，就是千萬級的也能實(shí)現(xiàn)毫秒級的查詢。 只談數(shù)量都是扯淡，要實(shí)際看看鎖占用的內(nèi)存大小，若你的表中有上百個字段，或者存在字符超長的字段。那么神仙也救不了你。

總結(jié)

文章主要是介紹MySql數(shù)據(jù)結(jié)構(gòu)的概念，大部分內(nèi)容都來自于《從根上理解Mysql》一書。做了很多簡化，可以作為基礎(chǔ)了解一些概念。

如有錯漏，感謝指正。

【