淺析MySQL中的數據存儲結構-當前焦點

本篇文章主要從InnoDB數據存儲結構的角度分析，在何種情況下，SQL查詢效率會降低。經常在網上看到一些文章在吐槽，數據量大的情況下，查詢效率會降低很多。表關聯的多的時候，查詢效率會降低。單表數據量不要超過百萬等等。

(相關資料圖)

數據庫版本： 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 "醫院編碼",  `hospital_name` varchar(36)  NOT NULL COMMENT "醫院名稱",  `is_deleted` tinyint DEFAULT NULL COMMENT "是否刪除 0否 1是",  `gmt_created` datetime DEFAULT NULL COMMENT "創建時間",  `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="醫院信息";

InnoDB 行格式

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

如上圖所示。分為“額外信息”和“真實數據”兩個部分。

變長字段列表

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

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

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

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

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

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

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

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

NULL值列表

在實際保存數據的時候，某些列可能存儲的是NULL值，如果這些值都記錄在真實的數據中，則會浪費存儲空間。在Compact格式中，會把這些值為NULL的列統一管理，存儲到NULL值列表中。

若一行數據中，沒有字段為NULL則不會產生此列。

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

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

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

記錄頭信息

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

先作為一個了解，這里有一個比較有意思標識：delete_mask用過redis的都知道，redis的中被刪除的數據不會被立刻清除，相同的mysql中也一樣，被刪除的數據不會立刻被清理，因為清理的過程會引發IO操作，這是很影響效率的。被刪除的數據會組成一個鏈表，想當與一個可復用的空間。

記錄真實數據數據

這個其實沒啥好說的，就是記錄真實的非NULL數據。

有一個網上經常能看到的問題：若沒有設置主鍵會怎樣？

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

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

小結

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

InnoDB數據頁

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

頁空間劃分如下：

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

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

File Trailer是校驗頁數據完整性的，當頁數據從內存重新寫入磁盤的時候需要校驗，防止數據頁損壞。

重點關注下User Records（已用空間）和Free Space（剩余空間），這里是保存真實的數據記錄。

此外 Infimum和 Supremum，分別標識最小記錄和最大記錄。即一個頁產生的時候，就默認包含這兩條記錄，不過不用擔心這兩條記錄只是作為數據鏈表的頭和尾，不影響真實數據。

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

簡單的來說，就是Free Space到User Records的轉化，當Free Space耗盡時則視為數據頁已經滿了。

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

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

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

頁目錄是由一個個的槽組成的。整體結構圖如下：

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

假設要查詢主鍵記錄為6的數據。

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

2）同理，將最大的槽設置為2，即（0+2）/2 =1,槽1對應的主鍵為4，因為 4 < 6, 所以可以確定數據就在槽2中。

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

B+樹索引

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

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

在上圖所示的數據頁基礎上，可以形成如下的索引結構：這種就是常說的聚簇索引，葉子即數據。這里要注意的一點，“頁30”中存放的是主鍵以及其所在的頁號。如果說單個索引頁滿了，則會進行分裂。產生如下所示的樹形結構。不過上圖為了標識方便，是不完全準確的。應該是先產生一個根節點，當根節點滿了，則會進行分裂。根節點則記錄分裂后的索引頁信息。

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

二級索引與聚簇索引的思路是一樣的，差別在于二級索引的葉子節點不是真實數據，而是數據的主鍵。需要進行回表操作才能獲取真實數據。

表空間

到目前為止，已經知道單條數據的存儲結構，以及最小的存儲數據單元頁。數據頁之間通過雙向鏈表進行連接，并且數據頁之間是不一定連續的。

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

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

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

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

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

2）當某個段已經占用了32個碎片區頁面后，就會以完整的區來分配空間。

表空間還分為：系統表空間和獨立表空間，此外還有區的XDES Entry數據結構。內容過多且復雜，需要了解的可以去看原書。

思考

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

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

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

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

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

實際上單個索引頁所能記錄的數據要比這大的多。

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

3）數據量大，sql會執行緩慢？

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

總結

文章主要是介紹MySql數據結構的概念，大部分內容都來自于《從根上理解Mysql》一書。做了很多簡化，可以作為基礎了解一些概念。

如有錯漏，感謝指正。

以上就是淺析MySQL中的數據存儲結構的詳細內容，更多請關注php中文網其它相關文章！

關鍵詞： MySQL