淺析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）還要保存所在分組中有多少筆記錄。

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

有了目錄之後，查詢就比較簡單了。可以使用二分法進行快查。上圖中，知道最小槽為0，最大為4. 舉個栗子：

假設要查詢主鍵記錄為6的資料。

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

2）同理，將最大的槽設為2，即（0 2）/2 =1,槽1對應的主鍵為4，因為4

為了方便後續的描述，將頁的資料形式簡化為如下圖所示的樣子。

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影片教學】

#

以上是淺析MySQL中的資料儲存結構的詳細內容。更多資訊請關注PHP中文網其他相關文章！