mysql 的一行記錄是怎么存儲(chǔ)的？

時(shí)間：2021-09-03 10:08:22

關(guān)鍵字： mysql

手機(jī)看文章

掃描二維碼
隨時(shí)隨地手機(jī)看文章

[導(dǎo)讀]mysql的一行記錄，最終肯定是存儲(chǔ)在磁盤(pán)上，也就是肉眼可見(jiàn)的文件上，今天我們的目標(biāo)很簡(jiǎn)單，就是看看它到底是怎么存的。當(dāng)然還有一個(gè)更重要的目標(biāo)，就是以這個(gè)為引子，帶大家完全通過(guò)一手資料，來(lái)揭秘這個(gè)問(wèn)題的答案。所以大家閱讀時(shí)，不要完全奔著尋找這個(gè)答案去，如果很少通過(guò)一手資料去研究問(wèn)...

mysql 的一行記錄，最終肯定是存儲(chǔ)在磁盤(pán)上，也就是肉眼可見(jiàn)的文件上，今天我們的目標(biāo)很簡(jiǎn)單，就是看看它到底是怎么存的。當(dāng)然還有一個(gè)更重要的目標(biāo)，就是以這個(gè)為引子，帶大家完全通過(guò)一手資料，來(lái)揭秘這個(gè)問(wèn)題的答案。所以大家閱讀時(shí)，不要完全奔著尋找這個(gè)答案去，如果很少通過(guò)一手資料去研究問(wèn)題，那么相信完整讀過(guò)本文，你會(huì)有收獲和啟發(fā)的。我們先準(zhǔn)備好三樣?xùn)|西。1. 準(zhǔn)備好一個(gè) mysql 5.7 并將其啟動(dòng)。2. 準(zhǔn)備好 mysql 的官方文檔放在旁邊：
????https://dev.mysql.com3. 準(zhǔn)備好 mysql 的源碼，萬(wàn)一要用呢，別怕：
????https://dev.mysql.com/downloads/mysql/5.7.html一手資料，就是官方文檔 ?源碼 ?二進(jìn)制文件，其中二進(jìn)制文件是我們自己去磁盤(pán)中找的，一會(huì)就知道了。Let's Go！mysql 會(huì)把文件存在哪里呢？先找到他。

mysql>?SHOW?VARIABLES?LIKE?'datadir';
 --------------- --------------------------------------------- 
|?Variable_name?|?Value???????????????????????????????????????|
 --------------- --------------------------------------------- 
|?datadir???????|?C:\ProgramData\MySQL\MySQL?Server?5.7\Data\?|
 --------------- --------------------------------------------- 
1?row?in?set,?1?warning?(0.00?sec)

我是 windows，就在這里了，進(jìn)入這個(gè)目錄。這些是啥先不管，盯著它看就好了，我們繼續(xù)。

第一步：創(chuàng)建數(shù)據(jù)庫(kù)

mysql>?create?database?flash;盯著剛剛的文件夾看，此時(shí)會(huì)多出一個(gè)文件夾同時(shí)這個(gè)文件夾里會(huì)多出一個(gè)文件，叫：|-- flash
? ? |-- db.opt
|--?flash
|--?performance_schema

看一眼它里面的內(nèi)容，就知道他是干嘛的了。

default-character-set=latin1
default-collation=latin1_swedish_ci

default-character-set 是默認(rèn)字符集，default-collation 是默認(rèn)字符序。字符集大家都了解，就不展開(kāi)了。字符序就是字符的排序和比較規(guī)則，一般以 _ci 結(jié)尾的表示大小寫(xiě)不敏感，_cs 結(jié)尾的表示大小寫(xiě)敏感，_bin 結(jié)尾的表示用編碼值進(jìn)行比較。含義知道了，那我們重新設(shè)置它應(yīng)該會(huì)有所變化，我們把這個(gè)數(shù)據(jù)庫(kù)設(shè)置為開(kāi)發(fā)時(shí)常用的 utf8mb4 格式。

ALTER?SCHEMA?`flash`?DEFAULT?CHARACTER?SET?utf8mb4;

再看 db.opt 文件，內(nèi)容已經(jīng)發(fā)生了變化。

default-character-set=utf8mb4
default-collation=utf8mb4_general_ci

OK，那我們現(xiàn)在對(duì)這個(gè)文件有了個(gè)初步認(rèn)識(shí)，創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)時(shí)，首先會(huì)多出一個(gè)以數(shù)據(jù)庫(kù)名為名稱(chēng)的文件夾，然后文件夾里面會(huì)多出一個(gè)描述數(shù)據(jù)庫(kù)配置的 db.opt 文件，我們繼續(xù)！

第二步：創(chuàng)建表

創(chuàng)建一張 student 表，三列，其中 id 是主鍵。CREATE?TABLE?`flash`.`student`?( ??`id`?INT?NOT?NULL, ??`name`?VARCHAR(10)?NOT?NULL, ??`age`?INT?NULL, ??PRIMARY?KEY?(`id`) ENGINE?=?InnoDB DEFAULT?CHARACTER?SET?=?utf8mb4;

再點(diǎn)開(kāi) student.frm，壞了，亂碼了。說(shuō)明這個(gè)文件不是文本文件，用二進(jìn)制方式打開(kāi)它。我把一些關(guān)鍵的地方都標(biāo)上了含義，那這個(gè)文件的作用大家就一目了然了，就是記錄表結(jié)構(gòu)嘛，具體的格式可以看 frm 文件結(jié)構(gòu)的官方文檔（寫(xiě)得太復(fù)雜了...我反正是沒(méi)看）：https://dev.mysql.com/doc/internals/en/frm-file-format.htmldb.opt 記錄了數(shù)據(jù)庫(kù)信息，student.frm 記錄了表結(jié)構(gòu)信息，那重頭戲自然就在那個(gè) student.ibd 文件了，這里一定存著具體的數(shù)據(jù)呀，索引呀等信息吧。打開(kāi)它！果不其然還是亂碼，那還是二進(jìn)制打開(kāi)它！截取了中間信息較為豐富的某部分。發(fā)現(xiàn)一點(diǎn)也看不懂。

第三步：插入數(shù)據(jù)

我們加幾條數(shù)據(jù)看看。

INSERT?INTO?`flash`.`student`?(`id`,?`name`,?`age`)?VALUES?('1',?'dibingfa2',?'2');
INSERT?INTO?`flash`.`student`?(`id`,?`name`,?`age`)?VALUES?('2',?'dibingfa2',?'2');
INSERT?INTO?`flash`.`student`?(`id`,?`name`,?`age`)?VALUES?('3',?'dibingfa3',?'2');
INSERT?INTO?`flash`.`student`?(`id`,?`name`,?`age`)?VALUES?('4',?'dibingfa4',?'2');
INSERT?INTO?`flash`.`student`?(`id`,?`name`,?`age`)?VALUES?('5',?'dibingfa5',?'2');
INSERT?INTO?`flash`.`student`?(`id`,?`name`,?`age`)?VALUES?('6',?'dibingfa6',?'2');
INSERT?INTO?`flash`.`student`?(`id`,?`name`,?`age`)?VALUES?('7',?'dibingfa7',?'2');

再二進(jìn)制打開(kāi)它！發(fā)現(xiàn)有些東西我們可以看出點(diǎn)端倪了！猜測(cè)下這部分就是每一行的記錄信息吧。我們插入了七條數(shù)據(jù)，我發(fā)現(xiàn)這些二進(jìn)制串有一段可以分割成七對(duì)，我把他單獨(dú)拿出來(lái)，并且按行分割。我們將第一行記錄拆解，第一行記錄的表數(shù)據(jù)是這樣的。1 dibingfa 2在 ibd 文件中是這樣的。08 00 00 00 10 00 24 80 00 00 01 00 00 00 00 0A 07 A7 00 00 01 1B 01 10 64 69 62 69 6E 67 66 61 80 00 00 02這串?dāng)?shù)據(jù)代表啥意思呢？由于本文只能參考官方文檔，我們看這里，即 Innodb 行格式。https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html看這部分（我們的行格式是 DYNAMIC 類(lèi)型，不過(guò)參考 COMPACT 類(lèi)型描述也行，因?yàn)閹缀跻粯?，之后再說(shuō)）：不要看這么一大長(zhǎng)串就害怕，我們一點(diǎn)點(diǎn)來(lái)，別著急。這個(gè)文檔，我可能沒(méi)資格評(píng)價(jià)，但我個(gè)人覺(jué)得寫(xiě)的很爛，一大堆廢話(huà)也沒(méi)說(shuō)明白格式是什么樣子，每個(gè)字節(jié)表示什么。不過(guò)也可能作用并非如此。好的官方文檔應(yīng)該是能把每個(gè)字節(jié)和每一位都解釋清楚的，無(wú)奈這個(gè)文檔不行，那我們就去找更接近一手資料的源碼。我找到了源碼，還是很清晰的，注釋上就寫(xiě)明了每一行記錄的磁盤(pán)數(shù)據(jù)格式，太好了，不用看代碼了。再貼上剛剛的第一行記錄。08 00 00 00 10 00 24 80 00 00 01 00 00 00 00 0A 07 A7 00 00 01 1B 01 10 64 69 62 69 6E 67 66 61 80 00 00 02來(lái)一點(diǎn)點(diǎn)看，第一部分。|?length?of?the?last?non-null?variable-length?field?of?data ... ...|

...

|?length?of?first?variable-length?field?of?data?|

這部分是變長(zhǎng)字段長(zhǎng)度列表，就是依次記錄所有變長(zhǎng)字段的長(zhǎng)度，由于我們只有一個(gè)變長(zhǎng)字段 varchar(10) 的 name，所以就是 08，我們存儲(chǔ)的 "dibingfa" 剛好是 8 個(gè)字節(jié)，對(duì)上了。那如果是多個(gè)，很顯然，就這樣存。錯(cuò)！應(yīng)該是這樣存，也就是逆序存放，具體為啥后面說(shuō)。OK，這就是第一個(gè)字節(jié) 08 所表示的含義。再往下。|?SQL-null?flags?(1?bit?per?nullable?field),?padded?to?full?bytes?|第二個(gè)結(jié)構(gòu)叫 NUll 值列表，用 1 位表示一個(gè) NULL 值，要填充滿(mǎn)一個(gè)字節(jié)，那往下的一個(gè)字節(jié)是 00，一看我們的記錄中也確實(shí)沒(méi)有 NULL 值，對(duì)上了。具體來(lái)說(shuō)，同樣也是逆序存放的。
繼續(xù)。|?4?bits?used?to?delete?mark?a?record,?and?mark?a?predefined

??minimum?record?in?alphabetical?order?|

|?4?bits?giving?the?number?of?records?owned?by?this?record

??(this?term?is?explained?in?page0page.h)?|

|?13?bits?giving?the?order?number?of?this?record?in?the

??heap?of?the?index?page?|

|?3?bits?record?type:?000=conventional,?001=node?pointer?(inside?B-tree),

??010=infimum,?011=supremum,?1xx=reserved?|

|?two?bytes?giving?a?relative?pointer?to?the?next?record?in?the?page?|

ORIGIN?of?the?record

這五個(gè)字節(jié)很亂，放在一塊叫記錄頭信息，00 00 10 00 24，其表示刪除狀態(tài)，記錄類(lèi)型，下一條記錄的相對(duì)位置等。這一大坨先放一放，因?yàn)樯婕暗胶枚囝~外的知識(shí)。繼續(xù)往下看。|?first?field?of?data?|

...

|?last?field?of?data?|

剩下全都是具體的列數(shù)據(jù)了，從第一列到最后一列。第一列是 ID 列，是 INT 類(lèi)型的 1，占四個(gè)字節(jié) 80 00 00 01。開(kāi)頭的 80 是因?yàn)?，正?shù)要以 1 開(kāi)頭，這是 mysql 規(guī)定的，0x80 的二進(jìn)制就是 1000 0000，所以這也對(duì)上了。第二列是 name 列，是?"dibingfa" 這樣一個(gè) varchar 類(lèi)型的字符串。可是與后面怎么也對(duì)應(yīng)不上，這是咋回事呢？還記不記得，mysql 每行記錄會(huì)有幾個(gè)隱藏列，rowid，事務(wù) ID，回滾指針？沒(méi)錯(cuò)，就是他們。其中，因?yàn)橛兄麈I，所以 rowid 就不存在了，也可以說(shuō)第一列要么是 mysql 為我們生成的 6 字節(jié)的 rowid，要么是用戶(hù)定義的主鍵或其他 Unique 鍵，優(yōu)先以用戶(hù)定義的鍵為準(zhǔn)。下面我們一塊看一下這五個(gè)列。（三個(gè)隱藏列，兩個(gè)我們定義的列）主鍵 ID：80 00 00 01

事務(wù) ID：00 00 00 00 0A 07

回滾指針：A7 00 00 01 1B 01 10

name 列（dibingfa）：64 69 62 69 6E 67 66 61

age 列（2）：80 00 00 02

其中 age 列同剛剛說(shuō)的一樣，mysql 會(huì)為正數(shù)的前面，加一個(gè) 1，所以 age 為 2，在磁盤(pán)上存儲(chǔ)的就是 80 00 00 02。事務(wù) ID 和回滾指針就涉及到事務(wù)、隔離級(jí)別和 MVCC 這一大坨八股文的知識(shí)點(diǎn)，這里不做展開(kāi)。

行記錄格式整體結(jié)構(gòu)

總結(jié)下，整個(gè)一行記錄的格式，叫做 mysql 的行記錄格式，ROW_FORMAT。這個(gè) ROW_FORMAT 可以有不同的值，代表存儲(chǔ)這一行記錄的不同數(shù)據(jù)結(jié)構(gòu)，其枚舉記錄在 remOtypes.h 文件中。

/**?Innodb?row?types?are?a?subset?of?the?MySQL?global?enum?row_type.
They?are?made?into?their?own?enum?so?that?switch?statements?can?account
for?each?of?them.?*/
enum?rec_format_enum?{
?REC_FORMAT_REDUNDANT?=?0,?/*!
?REC_FORMAT_COMPACT?=?1,?/*!
?REC_FORMAT_COMPRESSED?=?2,?/*!
?REC_FORMAT_DYNAMIC?=?3?/*!
};

我電腦上用的是 mysql 5.7，其默認(rèn)的行記錄格式是?DYNAMIC，這個(gè)在源碼中也可以找到答案，在 ha_innodb.cc 中。static?ulong?innodb_default_row_format?=?DEFAULT_ROW_FORMAT_DYNAMIC;當(dāng)然，可以用如下命令查詢(xún)你的行格式。show table status from flash like 'student';所以我們今天以上講述的格式，都是 DYNAMIC 格式的結(jié)構(gòu)，總結(jié)起來(lái)如下：
記錄源信息變長(zhǎng)字段列表

NULL 值列表

記錄頭信息

具體記錄的各列信息rowid 或?主鍵（隱藏列）
事務(wù) ID（隱藏列）
回滾指針（隱藏列）列 1
列 2...列 n剛剛那七條記錄，整體分析下，就如下圖。

然后多個(gè)行，一次緊密地排列，通過(guò)記錄頭中的下一條記錄的相對(duì)位置指針信息，可以快速找到下一條記錄的起始位置。再宏觀一點(diǎn)看，整個(gè) ibd 文件，劃分了很多個(gè)塊，每個(gè)塊 16 KB，我們這幾行記錄信息，在第四個(gè)塊的某個(gè)區(qū)域內(nèi)。
具體為什么是這個(gè)區(qū)域呢？因?yàn)檫@里是用戶(hù)記錄部分，前面還有文件頭、頁(yè)面頭等信息，共占用 120 個(gè)字節(jié)，咱們今天講的行記錄部分，就從第 121 個(gè)字節(jié)開(kāi)始。慢慢的，我再和大家一起把其他部分搞清楚，那 mysql 這塊我們就從最原始的磁盤(pán)數(shù)據(jù)入手，將 data 文件夾下的所有文件都搞清楚了。
最原始的數(shù)據(jù)都搞清楚了，原理還擔(dān)心么？

再聊幾句

其實(shí)，不要被行記錄格式這種名詞嚇到，它只是個(gè)協(xié)議或規(guī)定罷了。就是 mysql 規(guī)定了一種將一行記錄存儲(chǔ)在磁盤(pán)中的格式，以便于 mysql 自己的程序可以根據(jù)這個(gè)結(jié)構(gòu)認(rèn)識(shí)這一行記錄。
所以這種協(xié)議，首先要滿(mǎn)足讓 mysql?知道全部想知道的信息，比如 mysql 現(xiàn)在能僅僅通過(guò) ibd 文件里的這些二進(jìn)制數(shù)，知道每個(gè)字段的值都是什么嗎？不能，因?yàn)樗恢辣斫Y(jié)構(gòu)是什么樣子，也就沒(méi)法知道兩個(gè)字段值之間的界限在哪里。
所以不難想到，它一定利用了 frm 文件中存儲(chǔ)的表結(jié)構(gòu)信息。
其次，要讓 mysql 在知道這些信息的同時(shí)，還能更方便地利用這個(gè)結(jié)構(gòu)，占用更少的存儲(chǔ)空間，以及提升程序的便利性。
拿占用更少存儲(chǔ)空間這塊來(lái)講，NULL 值完全可以當(dāng)做普通列，也存儲(chǔ)在后面，然后規(guī)定一個(gè) NULL 值的二進(jìn)制標(biāo)識(shí)符即可。但 DYNAMIC 行記錄格式規(guī)定前面放一個(gè) NULL 值列表的結(jié)構(gòu)，并且僅僅用 1 位來(lái)表示一個(gè) NULL 值記錄，這樣就極大節(jié)省了空間。再說(shuō)便利性這塊，上面說(shuō)了變長(zhǎng)字段長(zhǎng)度列表和 NULL 值列表，都是逆序存儲(chǔ)的，看似很別扭，其實(shí)就是為了程序的便利性，這里留給大家自己探索吧。順便提一下，記得很久之前還是用 sqlyog 去連 mysql 的，其實(shí) mysql 下載并安裝后，自帶的 workbench 就很好用了。
所以非常方便本機(jī)做實(shí)驗(yàn)。大家方便的時(shí)候也自己玩一玩哦，我們下期見(jiàn)。