這篇文章主要介紹“MySQL中的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)是什么”的相關(guān)知識(shí)，小編通過(guò)實(shí)際案例向大家展示操作過(guò)程，操作方法簡(jiǎn)單快捷，實(shí)用性強(qiáng)，希望這篇“MySQL中的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)是什么”文章能幫助大家解決問(wèn)題。

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來(lái)自于我們對(duì)這個(gè)行業(yè)的熱愛(ài)。我們立志把好的技術(shù)通過(guò)有效、簡(jiǎn)單的方式提供給客戶，將通過(guò)不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：域名申請(qǐng)、網(wǎng)絡(luò)空間、營(yíng)銷軟件、網(wǎng)站建設(shè)、城關(guān)網(wǎng)站維護(hù)、網(wǎng)站推廣。

數(shù)據(jù)庫(kù)版本： 8.0 引擎：InnoDB 

樣例表：

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ī)院信息';

InnoDB 行格式

從一行數(shù)據(jù)開(kāi)始看起，先了解一下單行數(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è)部分。

變長(zhǎng)字段列表

這個(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)字段列表”不是必定存在的，若定義的字段類型沒(méi)有“變長(zhǎng)字段”則不會(huì)有。

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

NULL值列表

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

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

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

以樣例表來(lái)分析，表中存在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用過(guò)redis的都知道，redis的中被刪除的數(shù)據(jù)不會(huì)被立刻清除，相同的mysql中也一樣，被刪除的數(shù)據(jù)不會(huì)立刻被清理，因?yàn)榍謇淼倪^(guò)程會(huì)引發(fā)IO操作，這是很影響效率的。 被刪除的數(shù)據(jù)會(huì)組成一個(gè)鏈表，想當(dāng)與一個(gè)可復(fù)用的空間。

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

這個(gè)其實(shí)沒(méi)啥好說(shuō)的，就是記錄真實(shí)的非NULL數(shù)據(jù)。

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

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

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

小結(jié)

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

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

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

頁(yè)空間劃分如下：

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

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

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

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

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

綜上，記錄在頁(yè)中的存儲(chǔ)如下：

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

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

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

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

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

有了目錄之后，查詢就比較簡(jiǎn)單了。可以使用二分法進(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ù)的描述，將頁(yè)的數(shù)據(jù)形式簡(jiǎn)化為如下圖所示的樣子。

B+樹(shù)索引

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

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

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

簡(jiǎn)單的來(lái)說(shuō)就跟樹(shù)木成長(zhǎng)一樣，先從根再到樹(shù)干、樹(shù)枝、樹(shù)葉等。

二級(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ù)單元頁(yè)。數(shù)據(jù)頁(yè)之間通過(guò)雙向鏈表進(jìn)行連接，并且數(shù)據(jù)頁(yè)之間是不一定連續(xù)的。

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

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

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

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

1）剛開(kāi)始插入數(shù)據(jù)時(shí)，從碎片區(qū)以單個(gè)頁(yè)面來(lái)分配存儲(chǔ)空間。

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

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

思考

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

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

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

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

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

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

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

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

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

關(guān)于“MySQL中的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)，可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，小編每天都會(huì)為大家更新不同的知識(shí)點(diǎn)。

標(biāo)題名稱：MySQL中的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)是什么
當(dāng)前路徑：http://vcdvsql.cn/article10/gjdcdo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App設(shè)計(jì)、企業(yè)建站、服務(wù)器托管、電子商務(wù)、響應(yīng)式網(wǎng)站、網(wǎng)站設(shè)計(jì)公司

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)