面試官問(wèn)我 MySQL 的行格式是怎樣的

行格式(row format)決定了我們插入的一行數(shù)據(jù)，是如何存儲(chǔ)在數(shù)據(jù)庫(kù)中的，MySQL有4種行格式，分別是REDUNDANT，COMPACT，DYNAMIC，COMPRESSED。

不同行格式區(qū)別:

MySQL 5.7默認(rèn)使用的是Dynamic的行格式。

我們可以在創(chuàng)建表的時(shí)候指定字符集和行格式。

字符集表示我們插入的字符是用幾個(gè)字節(jié)編碼的，比如ASCII用一個(gè)字節(jié)，GB2312用2個(gè)字節(jié),utf8使用3個(gè)字節(jié),utf8mb4用4個(gè)字節(jié)(如果存儲(chǔ)emoj表情就要用這個(gè)字符集)

CREATE?TABLE?`test`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`price`?int(11)?NOT?NULL,
??`code`?int(11)?NOT?NULL,
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?ROW_FORMAT=Dynamic;

在講下面行格式的時(shí)候，我們使用這個(gè)表進(jìn)行講解

CREATE?TABLE?record_format_demo?(
?c1?VARCHAR(10),
?c2?VARCHAR(10)?NOT?NULL,
?c3?CHAR(10),
?c4?VARCHAR(10)
)?CHARSET=ascii?ROW_FORMAT=Redundant;

INSERT?INTO?record_format_demo(c1,?c2,?c3,?c4)?VALUES
??('aaaa',?'bbb',?'cc',?'d'),?
??('eeee',?'fff',?NULL,?NULL);

Redundant行格式

Redundant行格式是一個(gè)比較老的行格式了,現(xiàn)在也就只有MySQL的一些系統(tǒng)表會(huì)使用它了，平常咱們一般不用，但是這個(gè)行格式我覺(jué)得是能搞明白其他行格式的基礎(chǔ)。

行格式結(jié)構(gòu)

如上圖所示，整個(gè)行格式分為記錄的額外信息和記錄的真實(shí)數(shù)據(jù)兩部分，其中記錄的額外信息又分為字段長(zhǎng)度偏移列表和記錄頭信息兩部分。

1. 字段長(zhǎng)度偏移列表

在Redundant行格式中,會(huì)把所有字段的真實(shí)數(shù)據(jù)占?的字節(jié)長(zhǎng)度都存放在記錄的開(kāi)頭部位，從?形成?個(gè)字段長(zhǎng)度偏移列表，字段長(zhǎng)度占?的字節(jié)數(shù)按照列的順序逆序存放,逆序存放,逆序存放！

2. 記錄頭信息 Redundant?格式的記錄頭信息占?6字節(jié)，48個(gè)?進(jìn)制位，這些?進(jìn)制位代表的意思如下

3. 真實(shí)數(shù)據(jù)

對(duì)于record_format_demo表來(lái)說(shuō)，記錄的真實(shí)數(shù)據(jù)除了c1、c2、c3、c4這?個(gè)我們??定義的列的數(shù)據(jù)以外，MySQL會(huì)為每個(gè)記錄默認(rèn)的添加?些列（也稱(chēng)為隱藏列），具體的列如下

1. DB_ROW_ID(row_id) : 當(dāng)表沒(méi)有定義主鍵，則選擇unique鍵作為主鍵，如果仍沒(méi)有，則默認(rèn)添加一個(gè)名為DB_ROW_ID的隱藏列作為主鍵，占用6個(gè)字節(jié)。也就是說(shuō)這個(gè)列只有當(dāng)沒(méi)有主鍵也沒(méi)有唯一索引時(shí)才存在
2. DB_TRX_ID(transaction_id): 事務(wù)id,占用6字節(jié)
3. DB_ROLL_PTR(roll_pointer): 占用7個(gè)字節(jié),回滾指針(后面MVCC的時(shí)候會(huì)用到)

所以，對(duì)我們的數(shù)據(jù)來(lái)講，其行格式數(shù)據(jù)如下

列長(zhǎng)度如何計(jì)算

?如第?條記錄的字段?度偏移列表就是：

25 24 1A 17 13 0C 06

因?yàn)樗悄嫘蚺欧诺?，所以按照列的順序排列就是?/p>

06 0C 13 17 1A 24 25

計(jì)算各個(gè)列長(zhǎng)度則按照以下方法(字符集是ascii,一個(gè)字符占用1個(gè)字節(jié))：第?列(row_id)的?度就是 0x06個(gè)字節(jié)，也就是6個(gè)字節(jié)。

第?列(transaction_id)的?度就是 (0x0C - 0x06)個(gè)字節(jié)，也就是6個(gè)字節(jié)。

第三列(roll_pointer)的?度就是 (0x13 - 0x0C)個(gè)字節(jié)，也就是7個(gè)字節(jié)。第四列(c1)的?度就是 (0x17 - 0x13)個(gè)字節(jié)，也就是4個(gè)字節(jié)。

第五列(c2)的?度就是 (0x1A - 0x17)個(gè)字節(jié)，也就是3個(gè)字節(jié)。

第六列(c3)的?度就是 (0x24 - 0x1A)個(gè)字節(jié)，也就是10個(gè)字節(jié)。

第七列(c4)的?度就是 (0x25 - 0x24)個(gè)字節(jié)，也就是1個(gè)字節(jié)。

在記錄頭信息中的1byte_offs_flag用于表示 字段長(zhǎng)度偏移列表中每個(gè)列對(duì)應(yīng)的偏移量是使?1字節(jié)還是2字節(jié)表?的 ，這個(gè)值是如何計(jì)算的呢？

1. 當(dāng)記錄的真實(shí)數(shù)據(jù)占?的字節(jié)數(shù)不?于127（?六進(jìn)制0x7F，?進(jìn)制01111111）時(shí)，每個(gè)列對(duì)應(yīng)的偏移量占?1個(gè)字節(jié)。
2. 當(dāng)記錄的真實(shí)數(shù)據(jù)占?的字節(jié)數(shù)?于127，但不?于32767（?六進(jìn)制0x7FFF，?進(jìn)制0111111111111111）時(shí)，每個(gè)列對(duì)應(yīng)的偏移量占?2個(gè)字節(jié)。
3. 當(dāng)記錄大于32767的時(shí)候，此時(shí)的記錄已經(jīng)存放到了溢出頁(yè)中，在本頁(yè)中只保留前768個(gè)字節(jié)和20個(gè)字節(jié)的溢出頁(yè)?地址（當(dāng)然這20個(gè)字節(jié)中還記錄了?些別的信息）。因?yàn)樽侄?度偏移列表處只需要記錄每個(gè)列在本頁(yè)?中的偏移就好了，所以每個(gè)列使?2個(gè)字節(jié)來(lái)存儲(chǔ)偏移量就夠了。

我們的第一條記錄真實(shí)數(shù)據(jù)總長(zhǎng)度 = 37(6+6+7+4+3+10+1),小于127，所以采用1字節(jié)記錄偏移量。

為了在解析記錄的時(shí)候知道列偏移量是采用1字節(jié)還是2字節(jié)表示，因此使用1byte_offs_flag來(lái)決定，當(dāng)它的值為1時(shí)，表明使用1個(gè)字節(jié)存儲(chǔ)，當(dāng)值為0時(shí)，表明使用2字節(jié)存儲(chǔ)。

需要注意下記錄頭信息的next_record,你可以把它理解為指針,通過(guò)它我們可以指向下一條記錄的位置(多條記錄是如何連接的會(huì)在下一篇文章講到哈)，當(dāng)我們指針在這個(gè)位置的時(shí)候往后讀是真實(shí)數(shù)據(jù)的位置，往前讀就是字段的長(zhǎng)度列表，所以我們長(zhǎng)度列表逆序存放就能和真實(shí)數(shù)據(jù)一一對(duì)應(yīng)。

Redundant行格式對(duì)NULL值的處理

列對(duì)應(yīng)偏移量值的第一個(gè)比特位作為列值是否為NULL的依據(jù)，當(dāng)解析一條記錄某個(gè)列時(shí)，首先查看這個(gè)比特位的值是否為1，如果是1，那么該列的值就是NULL，否則則不是NULL。(現(xiàn)在你知道為什么記錄數(shù)據(jù)長(zhǎng)度為什么會(huì)有127和32767這兩個(gè)臨界點(diǎn)了吧)

這個(gè)bit位也可以稱(chēng)為NULL比特位

對(duì)于值為NULL的列，如果是定長(zhǎng)類(lèi)型，NULL值也將占用記錄的真實(shí)數(shù)據(jù)部分，數(shù)據(jù)采用0x00字節(jié)填充。如果是變長(zhǎng)數(shù)據(jù)類(lèi)型，則不在記錄的真實(shí)數(shù)據(jù)處占用任何存儲(chǔ)空間。

如上圖我們的第二條數(shù)據(jù)， C3列的值是NULL,類(lèi)型是CHAR(10),占?記錄的真實(shí)數(shù)據(jù)部分10字節(jié)(，所以我們看到在Redundant?格式中使?0x00000000000000000000來(lái)表?NULL值。

C3列長(zhǎng)度偏移量是0xA4，二進(jìn)制是 10100100，最高位是1，表明該列值是NULL,將高位去掉變成 0100100(十進(jìn)制的36)， C2列對(duì)應(yīng)偏移量是0x1A(十進(jìn)制的26),因此其長(zhǎng)度是36-26=10

C4列是Varchar類(lèi)型，對(duì)應(yīng)偏移量是0xA4,C3列偏移量也是0XA4,表明其長(zhǎng)度是0(不占用真實(shí)數(shù)據(jù)存儲(chǔ)空間),而其二進(jìn)制高位是1,表明該列值是NULL。

為什么定長(zhǎng)類(lèi)型NULL值也要占用固定空間呢？官方文檔告訴我對(duì)于一個(gè)固定長(zhǎng)度的列，該列的固定長(zhǎng)度被保留在記錄的數(shù)據(jù)部分。為NULL值保留的固定空間允許列從NULL值更新到非NULL值，而不會(huì)引起索引頁(yè)的碎片化。

Compact行格式

Compact行格式是Dynamic和Compressed兩種行格式的基礎(chǔ)，了解了它就了解了其他兩種結(jié)構(gòu)

行格式結(jié)構(gòu)

如上圖,Compact行格式中記錄額外信息分為變長(zhǎng)字段長(zhǎng)度列表，NULL值列表，記錄頭信息。

變長(zhǎng)字段列表中存儲(chǔ)的是非空的變長(zhǎng)字段的數(shù)據(jù)長(zhǎng)度,變長(zhǎng)字段存儲(chǔ)的數(shù)據(jù)是不固定的，所以我們需要將數(shù)據(jù)占用的字節(jié)數(shù)也存起來(lái)。同樣的，這里占用的長(zhǎng)度也是逆序存放，逆序存放，逆序存放的。

varchar(M),VARBINARY(M),各種TEXT以及各種BLOB類(lèi)型，mysql把擁有這些數(shù)據(jù)類(lèi)型的列稱(chēng)為變長(zhǎng)字段

對(duì)NULL值的處理

Redundant是將列對(duì)應(yīng)偏移量值的第一個(gè)比特位作為列值是否為NULL的依據(jù)，但是在Compact中是單獨(dú)有一個(gè)NULL值列表來(lái)存儲(chǔ)值為NULL的字段。NULL值列表是如何確認(rèn)的呢？

1. 首先統(tǒng)計(jì)表接口中允許為NULL值的列(主鍵和unique key是不允許為NULL的)
2. 如果表中沒(méi)有允許存儲(chǔ) NULL 的列，則 NULL值列表 也不存在了，否則將每個(gè)允許存儲(chǔ)NULL的列對(duì)應(yīng)?個(gè)?進(jìn)制位，?進(jìn)制位按照列的順序逆序排列,逆序排列,逆序排列

• ?進(jìn)制位的值為1時(shí)，代表該列的值為NULL。
• ?進(jìn)制位的值為0時(shí)，代表該列的值不為NULL。

3. MySQL規(guī)定NULL值列表必須?整數(shù)個(gè)字節(jié)的位表?，如果使?的?進(jìn)制位個(gè)數(shù)不是整數(shù)個(gè)字節(jié)，則在字節(jié)的?位補(bǔ)0。

如果一個(gè)表中有9個(gè)允許為NULL的列,那么就需要用2個(gè)字節(jié)表示

對(duì)于我們上面的兩條數(shù)據(jù)來(lái)說(shuō)(c1,c3,c4允許為NULL)

('aaaa',?'bbb',?'cc',?'d'),?
('eeee',?'fff',?NULL,?NULL);

第一條數(shù)據(jù)NULL值列表為 00000000(都不為空) 第二條數(shù)據(jù)NULL值列表為 00000110,c1不為null，所以是0,c3為null,所以是1，c4是null，所以是1，其倒序結(jié)果就是00000110

記錄頭

和Redundant不同,Compact的記錄頭信息使用了5個(gè)字節(jié)(40bit)來(lái)表示記錄頭信息,其具體信息如下

可以看到相比Redundant,Compact多了一個(gè)record_type的字段，少了n_field和1byte_offs_flag兩個(gè)字段。?

我們之前提到過(guò) 1byte_offs_flag 是用來(lái)表示 字段長(zhǎng)度偏移列表中每個(gè)列對(duì)應(yīng)的偏移量是使?1字節(jié)還是2字節(jié)表?的， 但是Compact卻沒(méi)有，那變長(zhǎng)字段長(zhǎng)度列表中字段長(zhǎng)度到底是用1個(gè)字節(jié)表示還是2個(gè)字節(jié)表示呢？

列長(zhǎng)度如何計(jì)算

還記得Redundant將列對(duì)應(yīng)偏移量值的第一個(gè)比特位作為列值是否為NULL的依據(jù)嗎？Compact思路也是類(lèi)似的，它使用字節(jié)的第一位來(lái)表示.

1. 假設(shè)某個(gè)字符集中表??個(gè)字符最多需要使?的字節(jié)數(shù)為W，也就是使?SHOW CHARSET語(yǔ)句的結(jié)果中的Maxlen列，??說(shuō)utf8字符集中的W就是3，gbk字符集中的W就是2，ascii字符集中的W就是1。
2. 對(duì)于變長(zhǎng)類(lèi)型VARCHAR(M)來(lái)說(shuō)，這種類(lèi)型表?能存儲(chǔ)最多M個(gè)字符（注意是字符不是字節(jié)），所以這個(gè)類(lèi)型能表?的字符串最多占?的字節(jié)數(shù)就是M×W。
3. 假設(shè)它實(shí)際存儲(chǔ)的字符串占?的字節(jié)數(shù)是L。所以確定使?1個(gè)字節(jié)還是2個(gè)字節(jié)表?真正字符串占?的字節(jié)數(shù)的規(guī)則就是這樣：

• 如果M×W <= 255，那么使?1個(gè)字節(jié)來(lái)表?真正字符串占?的字節(jié)數(shù)。也就是說(shuō)InnoDB在讀記錄的變長(zhǎng)字段長(zhǎng)度列表時(shí)先查看表結(jié)構(gòu)，如果某個(gè)變長(zhǎng)字段允許存儲(chǔ)的最?字節(jié)數(shù)不?于255時(shí)，可以認(rèn)為只使?1個(gè)字節(jié)來(lái)表?真正字符串占?的字節(jié)數(shù)。
• 如果M×W > 255，則分為兩種情況：
• 如果L <= 127，則?1個(gè)字節(jié)來(lái)表?真正字符串占?的字節(jié)數(shù)。
• 如果L > 127，則?2個(gè)字節(jié)來(lái)表?真正字符串占?的字節(jié)數(shù)。
  InnoDB在讀記錄的變長(zhǎng)字段長(zhǎng)度列表時(shí)先查看表結(jié)構(gòu)，如果某個(gè)變長(zhǎng)字段允許存儲(chǔ)的最?字節(jié)數(shù)?于255時(shí)，該怎么區(qū)分它正在讀的某個(gè)字節(jié)是?個(gè)單獨(dú)的字段長(zhǎng)度還是半個(gè)字段長(zhǎng)度 呢？
  該字節(jié)的第?個(gè)?進(jìn)制位作為標(biāo)志位：如果該字節(jié)的第?個(gè)位為0，那該字節(jié)就是?個(gè)單獨(dú)的字段長(zhǎng)度（使??個(gè)字節(jié)表?不?于127(01111111)的?進(jìn)制的第?個(gè)位都 為0），如果該字節(jié)的第?個(gè)位為1，那該字節(jié)就是半個(gè)字段長(zhǎng)度。
  對(duì)于?些占?字節(jié)數(shù)?常多的字段，??說(shuō)某個(gè)字段長(zhǎng)度?于了16KB，那么如果該記錄在單個(gè)頁(yè)?中?法存儲(chǔ) 時(shí)，InnoDB會(huì)把?部分?jǐn)?shù)據(jù)存放到所謂的溢出頁(yè)中，在變長(zhǎng)字段長(zhǎng)度列表處只存儲(chǔ)留在本頁(yè)?中的長(zhǎng)度，所以使?兩個(gè)字節(jié)也可以存放下來(lái)。
  總結(jié)?下就是說(shuō)：如果該可變字段允許存儲(chǔ)的最?字節(jié)數(shù)（M×W）超過(guò)255字節(jié)并且真實(shí)存儲(chǔ)的字節(jié)數(shù)（L）超過(guò)127字節(jié)，則使?2個(gè)字節(jié)，否則使?1個(gè)字節(jié)。

上面的內(nèi)容參考了小孩子大佬的<>,大家可以在掘金購(gòu)買(mǎi)它的小冊(cè)或者對(duì)應(yīng)的實(shí)體書(shū)。他是從Compact講到Redundant的，但是我覺(jué)得從Redundant的格式到Compact 格式其實(shí)更容易理解,過(guò)度更容易。這也是我的一個(gè)理解，供大家參考。

行溢出

在Compact和Reduntant?格式中，對(duì)于占?存儲(chǔ)空間?常?的列，在記錄的真實(shí)數(shù)據(jù)處只會(huì)存儲(chǔ)該列的?部分?jǐn)?shù)據(jù)，把剩余的數(shù)據(jù)分散存儲(chǔ)在?個(gè)其他的頁(yè)中，然后記錄的真實(shí)數(shù)據(jù)處?20個(gè)字節(jié)存儲(chǔ)指向這些頁(yè)的地址（當(dāng)然這20個(gè)字節(jié)中還包括這些分散在其他頁(yè)?中的數(shù)據(jù)的占?的字節(jié)數(shù)），從?可以找到剩余數(shù)據(jù)所在的頁(yè)。

對(duì)于Compact和Reduntant?格式來(lái)說(shuō)，如果某?列中的數(shù)據(jù)?常多的話(huà)，在本記錄的真實(shí)數(shù)據(jù)處只會(huì)存儲(chǔ)該列的前768個(gè)字節(jié)的數(shù)據(jù)和?個(gè)指向其他頁(yè)的地址(如果一個(gè)頁(yè)都放不下,那么就會(huì)使用鏈表將多個(gè)頁(yè)鏈接起來(lái))，然后把剩下的數(shù)據(jù)存放 到其他頁(yè)中，這個(gè)過(guò)程也叫做?溢出，存儲(chǔ)超出768字節(jié)的那些頁(yè)?也被稱(chēng)為溢出?。

需要注意的是并不僅僅只有變長(zhǎng)字段的列才會(huì)發(fā)生行溢出，blob,text都有可能，甚至大于或等于768字節(jié)的固定長(zhǎng)度的列也會(huì)被編碼為可變長(zhǎng)度的列，它可以被存儲(chǔ)在頁(yè)面外。例如，如果字符集的最大字節(jié)長(zhǎng)度大于3，一個(gè)CHAR(255)列可以超過(guò)768字節(jié)，正如utf8mb4那樣。

Dynamic和Compressed?格式

Dynamic和Compressed?格式，現(xiàn)在使?的MySQL版本是5.7，它的默認(rèn)?格式就是Dynamic，這倆?格式和Compact?格式挺像，只不過(guò)在處理?溢出數(shù)據(jù)時(shí)有點(diǎn)?分歧。

它們不會(huì)在記錄的真實(shí)數(shù)據(jù)處存儲(chǔ)字段真實(shí)數(shù)據(jù)的前768個(gè)字節(jié)，?是把所有的字節(jié)都存儲(chǔ)到其他頁(yè)?中，只在記錄的真實(shí)數(shù)據(jù)處存儲(chǔ)其他頁(yè)?的地

參考文檔

1. <>
2. 官方文檔: https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html
3. 官方文檔: https://dev.mysql.com/doc/internals/en/innodb-field-contents.html