本篇文章主要從InnoDB數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)的角度分析，在何種情況下，SQL查詢效率會(huì)降低。經(jīng)常在網(wǎng)上看到一些文章在吐槽，數(shù)據(jù)量大的情況下，查詢效率會(huì)降低很多。表關(guān)聯(lián)的多的時(shí)候，查詢效率會(huì)降低。單表數(shù)據(jù)量不要超過百萬等等。
(相關(guān)資料圖)
數(shù)據(jù)庫版本： 8.0引擎：InnoDB參考資料：掘金小冊(cè) 《從根上理解Mysql》，有時(shí)間的建議親自看一下。
樣例表：
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)建時(shí)間", `gmt_modified` datetime DEFAULT NULL COMMENT "gmt_modified", `gmt_deleted` datetime(3) DEFAULT "9999-12-31 23:59:59.000" COMMENT "刪除時(shí)間", PRIMARY KEY (`pk_id`), KEY `hospital_code` (`hospital_code`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT="醫(yī)院信息";
從一行數(shù)據(jù)開始看起，先了解一下單行數(shù)據(jù)的存儲(chǔ)格式。目前行格式有4種，分別是Compact、Redundant、Dynamic和Compressed行格式。在創(chuàng)建表的時(shí)候一般不需要刻意指定，5.7以上的版本會(huì)默認(rèn)Dynamic。每種行格式大同小異，這里以Compact作為一個(gè)樣例，簡(jiǎn)單的了解一下，每行數(shù)據(jù)是如何記錄的。
如上圖所示。分為“額外信息”和“真實(shí)數(shù)據(jù)”兩個(gè)部分。
這個(gè)比較有意思，一般在定義字段的時(shí)候都需要指定字段的類型和長(zhǎng)度，
比如：樣例表中的hospital_code字段定義VARCHAR(36)。在實(shí)際使用中hospital_code字段長(zhǎng)度只用了32位。
那剩下的4個(gè)字符長(zhǎng)度會(huì)怎么辦？若強(qiáng)行填充空字符，豈不是白白浪費(fèi)4個(gè)字符的內(nèi)存。若不填充，怎么判斷當(dāng)前字段到底保存了多少個(gè)字符？占用多少內(nèi)存？
此時(shí)，變長(zhǎng)字段列表就會(huì)按字段反序，用1~2個(gè)字節(jié)，記錄每個(gè)變長(zhǎng)字段實(shí)際的長(zhǎng)度。這樣可以有效的利用內(nèi)存空間。
與之類似的字段：VARBINARY、各種TEXT類型，各種BLOB類型。
相對(duì)的也存在“定長(zhǎng)字段”，比如：CHAR(10)，該類型的字段，在初始化的時(shí)候就會(huì)默認(rèn)占用指定字符長(zhǎng)度的空間，若不夠則填充空字符，因此對(duì)空間上是比較浪費(fèi)的，一般建議按需設(shè)置長(zhǎng)度。
當(dāng)然“變長(zhǎng)字段列表”不是必定存在的，若定義的字段類型沒有“變長(zhǎng)字段”則不會(huì)有。
拓展：對(duì)于TEXT或BLOB類型的字段，長(zhǎng)度很可能一頁無法存下，這時(shí)會(huì)將大部分?jǐn)?shù)據(jù)記錄在其他頁中，在當(dāng)前記錄中保留下一頁數(shù)據(jù)的地址。
在實(shí)際保存數(shù)據(jù)的時(shí)候，某些列可能存儲(chǔ)的是NULL值，如果這些值都記錄在真實(shí)的數(shù)據(jù)中，則會(huì)浪費(fèi)存儲(chǔ)空間。在Compact格式中，會(huì)把這些值為NULL的列統(tǒng)一管理，存儲(chǔ)到NULL值列表中。
若一行數(shù)據(jù)中，沒有字段為NULL則不會(huì)產(chǎn)生此列。
存儲(chǔ)的方式也比較有意思，是二進(jìn)制方式倒序記錄。
以樣例表來分析，表中存在is_deleted、gmt_created、gmt_modified三個(gè)字段可能為空。假設(shè)在一條記錄中gmt_created、gmt_modified都為空，那對(duì)應(yīng)到NULL值列表中應(yīng)該是下面的樣子。
拓展：Mysql是支持二進(jìn)制數(shù)據(jù)存儲(chǔ)的，充分利用，可以減少很大的存儲(chǔ)空間。
記錄頭信息由固定的5個(gè)字符組成，即40個(gè)二進(jìn)制位長(zhǎng)度。
先作為一個(gè)了解，這里有一個(gè)比較有意思標(biāo)識(shí)：delete_mask用過redis的都知道，redis的中被刪除的數(shù)據(jù)不會(huì)被立刻清除，相同的mysql中也一樣，被刪除的數(shù)據(jù)不會(huì)立刻被清理，因?yàn)榍謇淼倪^程會(huì)引發(fā)IO操作，這是很影響效率的。被刪除的數(shù)據(jù)會(huì)組成一個(gè)鏈表，想當(dāng)與一個(gè)可復(fù)用的空間。
這個(gè)其實(shí)沒啥好說的，就是記錄真實(shí)的非NULL數(shù)據(jù)。
有一個(gè)網(wǎng)上經(jīng)常能看到的問題：若沒有設(shè)置主鍵會(huì)怎樣？
InnoDB下，主鍵是一條記錄的唯一標(biāo)識(shí)，如果用戶沒有指定，mysql會(huì)從Unique（唯一）鍵中選取一個(gè)作為主鍵，如果沒有Unique鍵，則會(huì)添加一個(gè)名為row_id隱藏列，作為主鍵。
此外還會(huì)添加添加 transaction_id（事務(wù)ID）和 roll_pointer(回滾指針)這兩個(gè)列。
4種行格式大同小異，就不一一介紹了，都分為“額外信息”和“真實(shí)數(shù)據(jù)”兩個(gè)部分。區(qū)別主要在與“額外信息”記錄的內(nèi)容，以及變長(zhǎng)字段的保存上的些許不同。
數(shù)據(jù)頁的概念，相信已經(jīng)耳熟能詳了。它是InnoDB管理存儲(chǔ)空間的基本單位，單頁大小一般是16KB。根據(jù)不同的目的設(shè)計(jì)了許多不同類型的頁，如：存放表空間頭部信息的頁，存放Insert Buffer信息的頁，存放INODE信息的頁，存放undo日志信息的頁等等。
頁空間劃分如下：
總共7個(gè)組成部分，大致描述一下7個(gè)部分。
其中File header和Page header中的屬性非常多，這里不一一介紹，只要知道這兩個(gè)地方記錄頁的一些屬性，比如：頁號(hào)，上一頁和下一頁的頁號(hào)，頁的類型，以及頁的內(nèi)存占用等等。這里說一下，頁與頁之間是雙向鏈表進(jìn)行連接的。數(shù)據(jù)記錄是單項(xiàng)鏈表。
File Trailer是校驗(yàn)頁數(shù)據(jù)完整性的，當(dāng)頁數(shù)據(jù)從內(nèi)存重新寫入磁盤的時(shí)候需要校驗(yàn)，防止數(shù)據(jù)頁損壞。
重點(diǎn)關(guān)注下User Records（已用空間）和Free Space（剩余空間），這里是保存真實(shí)的數(shù)據(jù)記錄。
此外 Infimum和 Supremum，分別標(biāo)識(shí)最小記錄和最大記錄。即一個(gè)頁產(chǎn)生的時(shí)候，就默認(rèn)包含這兩條記錄，不過不用擔(dān)心這兩條記錄只是作為數(shù)據(jù)鏈表的頭和尾，不影響真實(shí)數(shù)據(jù)。
綜上，記錄在頁中的存儲(chǔ)如下：
簡(jiǎn)單的來說，就是Free Space到User Records的轉(zhuǎn)化，當(dāng)Free Space耗盡時(shí)則視為數(shù)據(jù)頁已經(jīng)滿了。
到此，數(shù)據(jù)已經(jīng)寫入了數(shù)據(jù)頁中。那該怎么取出呢？上面知道了數(shù)據(jù)記錄是單項(xiàng)鏈表組成的，難道要從Infimum（最小）記錄開始沿著鏈表遍歷嗎？
顯然，mysql的開發(fā)大佬不可能這么蠢，否則我上我也行，哈哈。
這里就要提到 Page Directory（頁目錄）了。在頁中，對(duì)數(shù)據(jù)進(jìn)行了分組，每組最后一條記錄的地址偏移量單獨(dú)提取出來按順序存儲(chǔ)到靠近頁尾的“頁目錄”中，頁目錄中的這些地址偏移量被稱為“槽”，此外最后一條記錄頭部（n_owned）還要保存所在分組中有多少條記錄。
頁目錄是由一個(gè)個(gè)的槽組成的。整體結(jié)構(gòu)圖如下：
有了目錄之后，查詢就比較簡(jiǎn)單了?？梢允褂?strong>二分法進(jìn)行快查。上圖中，知道最小槽為0，最大為4.舉個(gè)栗子：
假設(shè)要查詢主鍵記錄為6的數(shù)據(jù)。
1）計(jì)算中間槽位置即（0+4）/ 2 = 2。取出槽對(duì)應(yīng)的記錄主鍵為8，因?yàn)?>6。
2）同理，將最大的槽設(shè)置為2，即（0+2）/2 =1,槽1對(duì)應(yīng)的主鍵為4，因?yàn)?4 < 6, 所以可以確定數(shù)據(jù)就在槽2中。
為了方便后續(xù)的描述，將頁的數(shù)據(jù)形式簡(jiǎn)化為如下圖所示的樣子。
不妨思考一個(gè)問題，前面說了。數(shù)據(jù)頁之間使用的是雙向鏈表鏈接的，大致如下圖所示：上圖可以看能出頁號(hào)并非連續(xù)的，也并不一定是連續(xù)的內(nèi)存空間（記住這句話后面會(huì)說到）。
假設(shè)每頁能存放3條記錄，現(xiàn)在有10w條記錄需要保存，則需要3w多個(gè)數(shù)據(jù)頁，此時(shí)會(huì)面對(duì)和單頁數(shù)據(jù)過多一樣的查詢問題，總不能逐個(gè)遍歷吧。此時(shí)也需要一個(gè)能快速快查詢的目錄，這個(gè)目錄就是“索引”。
在上圖所示的數(shù)據(jù)頁基礎(chǔ)上，可以形成如下的索引結(jié)構(gòu)：這種就是常說的聚簇索引，葉子即數(shù)據(jù)。這里要注意的一點(diǎn)，“頁30”中存放的是主鍵以及其所在的頁號(hào)。如果說單個(gè)索引頁滿了，則會(huì)進(jìn)行分裂。產(chǎn)生如下所示的樹形結(jié)構(gòu)。不過上圖為了標(biāo)識(shí)方便，是不完全準(zhǔn)確的。應(yīng)該是先產(chǎn)生一個(gè)根節(jié)點(diǎn)，當(dāng)根節(jié)點(diǎn)滿了，則會(huì)進(jìn)行分裂。根節(jié)點(diǎn)則記錄分裂后的索引頁信息。
簡(jiǎn)單的來說就跟樹木成長(zhǎng)一樣，先從根再到樹干、樹枝、樹葉等。
二級(jí)索引與聚簇索引的思路是一樣的，差別在于二級(jí)索引的葉子節(jié)點(diǎn)不是真實(shí)數(shù)據(jù)，而是數(shù)據(jù)的主鍵。需要進(jìn)行回表操作才能獲取真實(shí)數(shù)據(jù)。
到目前為止，已經(jīng)知道單條數(shù)據(jù)的存儲(chǔ)結(jié)構(gòu)，以及最小的存儲(chǔ)數(shù)據(jù)單元頁。數(shù)據(jù)頁之間通過雙向鏈表進(jìn)行連接，并且數(shù)據(jù)頁之間是不一定連續(xù)的。
此時(shí)，產(chǎn)生了一個(gè)問題，同一個(gè)表的記錄，如果所在的頁在內(nèi)存地址上相距過遠(yuǎn)怎么辦？設(shè)想一下為了找3個(gè)人，他們分別再北京、紐約、倫敦。你要挨個(gè)去找，中間要浪費(fèi)大量的時(shí)間在旅途中。如果把他們聚集在一個(gè)國(guó)家，甚至一個(gè)城市，那就要快很多。
于是區(qū)的概念誕生了。區(qū)是由連續(xù)的64個(gè)頁組成，默認(rèn)情況下一個(gè)區(qū)占用1M的內(nèi)存。在申請(qǐng)內(nèi)存的時(shí)候，一次性占用1M的空間，其中的數(shù)據(jù)頁都是相鄰的，一定程度上解決了隨機(jī)IO的問題。
在區(qū)的基礎(chǔ)上，為了更有效的提升查詢效率，將B+樹的葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)記錄在不同的區(qū)中，這些區(qū)的集合被成為“段（segment）”。在此概念下，插入第一條記錄，就需要申請(qǐng)2個(gè)區(qū)空間，一個(gè)聚簇索引根節(jié)點(diǎn)，一個(gè)數(shù)據(jù)頁，這一次就需要申請(qǐng)2M的空間！啥也沒干呢，2M空間就沒了，這合理嗎？顯然，這很不合理。
因此又搞出一個(gè)"碎片區(qū)"的概念。碎片區(qū)直屬于表空間，不屬于任何一個(gè)段。分配內(nèi)存的流程轉(zhuǎn)變成：
1）剛開始插入數(shù)據(jù)時(shí)，從碎片區(qū)以單個(gè)頁面來分配存儲(chǔ)空間。
2）當(dāng)某個(gè)段已經(jīng)占用了32個(gè)碎片區(qū)頁面后，就會(huì)以完整的區(qū)來分配空間。
表空間還分為：系統(tǒng)表空間和獨(dú)立表空間，此外還有區(qū)的XDES Entry數(shù)據(jù)結(jié)構(gòu)。內(nèi)容過多且復(fù)雜，需要了解的可以去看原書。
1）索引越多越好嗎？多了會(huì)有 什么影響？
那肯定不是越多越好，上面可以知道，索引的記錄也是需要內(nèi)存損耗的。每個(gè)索引都會(huì)對(duì)應(yīng)一個(gè)B+樹，每個(gè)樹有需要2個(gè)段分別記錄葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)。這么下來會(huì)帶來很多內(nèi)存的浪費(fèi)。僅僅是這樣的話也不是不能接受，畢竟索引本身的意義就是用空間換時(shí)間。但我們要知道，數(shù)據(jù)的增刪改，會(huì)導(dǎo)致索引的變化，需要索引重新分配節(jié)點(diǎn)，以及頁內(nèi)存的回收分配。這些都是IO操作，若索引過多，勢(shì)必導(dǎo)致性能的降低。
因此合理的利用聯(lián)合索引，可以解決單個(gè)索引過多的問題。此外索引有長(zhǎng)度限制，過長(zhǎng)的字段不適合作為索引。
2）索引為何查詢效率這么高？
這個(gè)其實(shí)屬于算法問題，以聚簇索引為例，假設(shè)非葉子節(jié)點(diǎn)的索引頁，每個(gè)能記錄1000條數(shù)據(jù)，葉子節(jié)點(diǎn)每個(gè)能記錄500條數(shù)據(jù)，一個(gè)3層的B+樹（不算根節(jié)點(diǎn)），能存放10001000500條記錄。一個(gè)3層結(jié)構(gòu)的索引能存放這么多記錄，每次只需幾次查詢就能定位數(shù)據(jù)，效率自然也就高了。
實(shí)際上單個(gè)索引頁所能記錄的數(shù)據(jù)要比這大的多。
同樣的這里可以思考一個(gè)問題，若葉子節(jié)點(diǎn)中的單條數(shù)據(jù)非常大，大到一個(gè)數(shù)據(jù)頁只能存放3條記錄，這時(shí)B+樹的深度就會(huì)增加，因此合理的減少表中單條記錄的大小，也是一種優(yōu)化。
3）數(shù)據(jù)量大，sql會(huì)執(zhí)行緩慢？
其實(shí)這個(gè)問題真的很想吐槽，動(dòng)不動(dòng)就百萬數(shù)據(jù)查詢效率xx秒，太慢了。不否認(rèn)mysql的性能的確弱于一些數(shù)據(jù)庫，但是百萬的數(shù)據(jù)量就慢的，想想自己的SQL和表結(jié)構(gòu)設(shè)計(jì)是否合理。別說百萬級(jí)，就是千萬級(jí)的也能實(shí)現(xiàn)毫秒級(jí)的查詢。只談數(shù)量都是扯淡，要實(shí)際看看鎖占用的內(nèi)存大小，若你的表中有上百個(gè)字段，或者存在字符超長(zhǎng)的字段。那么神仙也救不了你。
文章主要是介紹MySql數(shù)據(jù)結(jié)構(gòu)的概念，大部分內(nèi)容都來自于《從根上理解Mysql》一書。做了很多簡(jiǎn)化，可以作為基礎(chǔ)了解一些概念。
如有錯(cuò)漏，感謝指正。
以上就是淺析MySQL中的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！
關(guān)鍵詞： MySQL
營(yíng)業(yè)執(zhí)照公示信息