我們平時(shí)的數(shù)據(jù)以行為單位來(lái)想表中插入數(shù)據(jù)，這些記錄在磁盤(pán)上的存放方式也被稱(chēng)為行格式或者記錄格式。InnoDB存儲(chǔ)引擎設(shè)計(jì)了 4 種不同類(lèi)型的行格式，分別是Compact、Redundant、Dynamic 和 Compressed行格式

查看MySQL8的默認(rèn)行格式：

SELECT @@innodb_default_row_format;
或
SHOW TABLE STATUS like 'emp';

1、指定行格式的語(yǔ)法

在創(chuàng)建或修改表的語(yǔ)句中指定行格式：

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名稱(chēng)
ALTER TABLE 表名 ROW_FORMAT=行格式名稱(chēng)

CREATE TABLE record_test_table
(
col1 VARCHAR(8),
col2 VARCHAR(8) NOT NULL,
col3 CHAR(8),
col4 VARCHAR(8)
) CHARSET = ascii
  ROW_FORMAT = COMPACT;
INSERT INTO record_test_table(col1, col2, col3, col4)
VALUES ('zhangsan', 'lisi', 'wangwu', 'songhk'),
   ('tong', 'chen', NULL, NULL);

2、COMPACT行格式

COMPACT行格式，一條完整的記錄其實(shí)可以被分為記錄的額外信息和記錄的真實(shí)數(shù)據(jù)兩大部分

2.1 變長(zhǎng)字段長(zhǎng)度列表

MySQL支持一些變長(zhǎng)的數(shù)據(jù)類(lèi)型，比如VARCHAR(M)、VARBINARY(M)、TEXT類(lèi)型，BLOB類(lèi)型，這些數(shù)據(jù)類(lèi)型修飾列稱(chēng)為變長(zhǎng)字段，變長(zhǎng)字段中存儲(chǔ)多少字節(jié)的數(shù)據(jù)不是固定的，所以我們?cè)诖鎯?chǔ)真實(shí)數(shù)據(jù)的時(shí)候需要順便把這些數(shù)據(jù)占用的字節(jié)數(shù)也存起來(lái)。

在Compact行格式中，把所有變長(zhǎng)字段的真實(shí)數(shù)據(jù)占用的字節(jié)長(zhǎng)度都存放在記錄的開(kāi)頭部位，從而形成一個(gè)變長(zhǎng)字段長(zhǎng)度列表。

變長(zhǎng)字段長(zhǎng)度列表示意圖

2.2 NULL值列表

Compact行格式會(huì)把可以為NULL的列統(tǒng)一管理起來(lái)，存在一個(gè)標(biāo)記為NULL值列表中。如果表中沒(méi)有允許存儲(chǔ) NULL 的列，則 NULL值列表也不存在了。

為什么定義NULL值列表？

之所以要存儲(chǔ)NULL是因?yàn)閿?shù)據(jù)都是需要對(duì)齊的，如果沒(méi)有標(biāo)注出來(lái)NULL值的位置，就有可能在查詢(xún)數(shù)據(jù)的時(shí)候出現(xiàn)混亂。如果使用一個(gè)特定的符號(hào)放到相應(yīng)的數(shù)據(jù)位表示空置的話(huà)，雖然能達(dá)到效果，但是這樣很浪費(fèi)空間，所以直接就在行數(shù)據(jù)得頭部開(kāi)辟出一塊空間專(zhuān)門(mén)用來(lái)記錄該行數(shù)據(jù)哪些是非空數(shù)據(jù)，哪些是空數(shù)據(jù)。

格式如下：

1.二進(jìn)制位的值為1時(shí)，代表該列的值為NULL。

2.二進(jìn)制位的值為0時(shí)，代表該列的值不為NULL。

例如：字段 a、b、c，其中a是主鍵，在某一行中存儲(chǔ)的數(shù)依次是 a=1、b=null、c=2。那么Compact行格式中的NULL值列表中存儲(chǔ)：01。第一個(gè)0表示c不為null，第二個(gè)1表示b是null。這里之所以沒(méi)有a是因?yàn)閿?shù)據(jù)庫(kù)會(huì)自動(dòng)跳過(guò)主鍵，因?yàn)橹麈I肯定是非NULL且唯一的，在NULL值列表的數(shù)據(jù)中就會(huì)自動(dòng)跳過(guò)主鍵。

record_test_table的兩條記錄的NULL值列表就如下：

第一條記錄

第二條記錄

2.3 記錄頭信息

見(jiàn) Inonodb頁(yè)  4、User Records (用戶(hù)記錄)

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

記錄的真實(shí)數(shù)據(jù)除了我們自己定義的列的數(shù)據(jù)以外，還會(huì)有三個(gè)隱藏列

實(shí)際上這幾個(gè)列的真正名稱(chēng)其實(shí)是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。

一個(gè)表沒(méi)有手動(dòng)定義主鍵，則會(huì)選取一個(gè)Unique鍵作為主鍵，如果連Unique鍵都沒(méi)有定義的話(huà)，則會(huì)為表默認(rèn)添加一個(gè)名為row_id的隱藏列作為主鍵。

2.5 分析Compact行記錄的內(nèi)部結(jié)構(gòu)

1：創(chuàng)建表

CREATE TABLE mytest
(
col1 VARCHAR(10),
col2 VARCHAR(10),
col3 CHAR(10),
col4 VARCHAR(10)
) ENGINE = INNODB
  CHARSET = latin1
  ROW_FORMAT = COMPACT;

2：插入數(shù)據(jù)

INSERT INTO mytest
VALUES ('a', 'bb', 'bb', 'ccc');
INSERT INTO mytest
VALUES ('d', 'ee', 'ee', 'fff');
INSERT INTO mytest
VALUES ('d', NULL, NULL, 'fff');

表空間下文件mytest.ibd這個(gè)二進(jìn)制文件。內(nèi)容如下

分析：

1、該行記錄從0000c070開(kāi)始
2、03 02 01：變長(zhǎng)字段長(zhǎng)度列表，逆序
3、00：*NULL標(biāo)志位，第一行沒(méi)有NULL值
4、00 00 10 00 2c：Record Header，固定5字節(jié)長(zhǎng)度
5、00 00 00 2b 68 00：RowID InnoDB自動(dòng)創(chuàng)建，6字節(jié)
6、00 00 00 00 06 05：TransactionID
7、80 00 00 00 32 01 10：Roll Pointer
8、61：列1數(shù)據(jù)'a'
9、62 62：列2數(shù)據(jù)'bb'
10、62 62 20 20 20 20 20 20 20 20：列3數(shù)據(jù)'bb'
11、63 63 63：列4數(shù)據(jù)'ccc'
注意1：InnoDB每行有隱藏列TransactionID和Roll Pointer。
注意2：固定長(zhǎng)度CHAR字段在未能完全占用其長(zhǎng)度空間時(shí)，會(huì)用0x20來(lái)進(jìn)行填充。
Record Header的最后兩個(gè)字節(jié)，這兩個(gè)字節(jié)代表next_recorder，0x2c代表下一個(gè)記錄的偏移量，即當(dāng)前記錄的位置加上偏移量0x2c就是下條記錄的起始位置。
第二行將不做整理，除了RowID不同外，它和第一行大同小異，現(xiàn)在來(lái)分析有NULL值的第三行
03 01：變長(zhǎng)字段長(zhǎng)度列表，逆序
06：NULL標(biāo)志位，第三行有NULL值
00 00 20 ff 98：Record Header
00 00 00 2b 68 02：RowID
00 00 00 00 06 07：TransactionID
80 00 00 00 32 01 10：Roll Pointer
64：列1數(shù)據(jù)'d'
66 66 66：*列4數(shù)據(jù)'fff'
第三行有NULL值，因此NULL標(biāo)志位不再是00而是06，轉(zhuǎn)換成二進(jìn)制為00000110，為1的值代表第2列和第3列的數(shù)據(jù)為NULL。在其后存儲(chǔ)列數(shù)據(jù)的部分，用戶(hù)會(huì)發(fā)現(xiàn)沒(méi)有存儲(chǔ)NULL列，而只存儲(chǔ)了第1列和第4列非NULL的值。因此這個(gè)例子很好地說(shuō)明了：不管是CHAR類(lèi)型還是VARCHAR類(lèi)型，在compact格式下NULL值都不占用任何存儲(chǔ)空間。

3、Dynamic和Compressed行格式

3.1 行溢出

很多DBA喜歡MySQL數(shù)據(jù)庫(kù)提供的VARCHAR(M)類(lèi)型，認(rèn)為可以存放65535字節(jié)。這是真的嗎？如果我們使用 ascii字符集的話(huà)，一個(gè)字符就代表一個(gè)字節(jié)，我們看看VARCHAR(65535)是否可用。

驗(yàn)證VARCHAR(M)類(lèi)型，是否可以存放65535字節(jié)

CREATE TABLE varchar_size_demo
(
c VARCHAR(65535)
) CHARSET = ascii
  ROW_FORMAT = COMPACT;

結(jié)果如下：

ERROR 1118 (42000): Row size too large. 
The maximum row size for the used table type, 
not counting BLOBs, is 65535. This includes storage overhead, 
check the manual. You have  to  change  some  columns  to  TEXT or  BLOBs

報(bào)錯(cuò)信息表達(dá)的意思是：MySQL對(duì)一條記錄占用的最大存儲(chǔ)空間是有限制的，除BLOB或者TEXT類(lèi)型的列之外， 其他所有的列（不包括隱藏列和記錄頭信息）占用的字節(jié)長(zhǎng)度加起來(lái)不能超過(guò)65535個(gè)字節(jié)。 這個(gè)65535個(gè)字節(jié)除了列本身的數(shù)據(jù)之外，還包括一些其他的數(shù)據(jù)。

如果該VARCHAR類(lèi)型的列沒(méi)有NOT NULL屬性，那最多只能存儲(chǔ)65532個(gè)字節(jié)的數(shù)據(jù)，因?yàn)樽冮L(zhǎng)字段的長(zhǎng)度占用 2個(gè)字節(jié)，NULL值標(biāo)識(shí)需要占用1個(gè)字節(jié)。

CREATE TABLE varchar_size_demo
(
c VARCHAR(65532)
) CHARSET = ascii
  ROW_FORMAT = COMPACT;

如果有not null屬性，那么就不需要NULL值標(biāo)識(shí)，也就可以多存儲(chǔ)一個(gè)字節(jié)，即65533個(gè)字節(jié)

CREATE TABLE varchar_size_demo
(
c VARCHAR(65533) NOT NULL
) CHARSET = ascii
  ROW_FORMAT = COMPACT;

通過(guò)上面的案例，我們可以知道一個(gè)頁(yè)的大小一般是16KB，也就是16384字節(jié)，而一個(gè)VARCHAR(M)類(lèi)型的列就最多可以存儲(chǔ)65533個(gè)字節(jié)，這樣就可能出現(xiàn)一個(gè)頁(yè)存放不了一條記錄，這種現(xiàn)象稱(chēng)為行溢出。

在Compact和Reduntant行格式中，對(duì)于占用存儲(chǔ)空間非常大的列，在記錄的真實(shí)數(shù)據(jù)處只會(huì)存儲(chǔ)該列的一部分?jǐn)?shù)據(jù)，把剩余的數(shù)據(jù)分散存儲(chǔ)在幾個(gè)其他的頁(yè)中進(jìn)行分頁(yè)存儲(chǔ)，然后記錄的真實(shí)數(shù)據(jù)處用20個(gè)字節(jié)存儲(chǔ)指向這些頁(yè)的地址。

3.2、Dynamic和Compressed行格式

在MySQL 8.0中，默認(rèn)行格式就是Dynamic。Dynamic、Compressed行格式和Compact行格式挺像，只不過(guò)在處理行溢出數(shù)據(jù)時(shí)有分歧。

Compressed和Dynamic兩種記錄格式對(duì)于存放在BLOB中的數(shù)據(jù)采用了完全的行溢出的方式。如圖，在數(shù)據(jù)頁(yè)中只存放20個(gè)字節(jié)的指針（溢出頁(yè)的地址），實(shí)際的數(shù)據(jù)都存放在Off Page（溢出頁(yè)）中。

Compact和Redundant兩種格式會(huì)在記錄的真實(shí)數(shù)據(jù)處存儲(chǔ)一部分?jǐn)?shù)據(jù)（存放768個(gè)前綴字節(jié)）。

Compressed行記錄格式的另一個(gè)功能就是，存儲(chǔ)在其中的行數(shù)據(jù)會(huì)以zlib的算法進(jìn)行壓縮，因此對(duì)于BLOB、TEXT、VARCHAR這類(lèi)大長(zhǎng)度類(lèi)型的數(shù)據(jù)能夠進(jìn)行非常有效的存儲(chǔ)。

4、Redundant行格式

Redundant是MySQL 5.0版本之前InnoDB的行記錄存儲(chǔ)方式，MySQL 5.0支持Redundant是為了兼容之前版本的頁(yè)格式。

把表record_test_table的行格式修改為Redundant

ALTER TABLE record_test_table ROW_FORMAT=Redundant;

從上圖可以看到，不同于Compact行記錄格式，Redundant行格式的首部是一個(gè)字段長(zhǎng)度偏移列表，同樣是按照列的順序逆序放置的。

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

注意Compact行格式的開(kāi)頭是變長(zhǎng)字段長(zhǎng)度列表，而Redundant行格式的開(kāi)頭是字段長(zhǎng)度偏移列表，與變長(zhǎng)字段長(zhǎng)度列表有兩處不同：

①少了“變長(zhǎng)”兩個(gè)字：Redundant行格式會(huì)把該條記錄中所有列（包括隱藏列）的長(zhǎng)度信息都按照逆序存儲(chǔ)到字段長(zhǎng)度偏移列表。

②多了“偏移”兩個(gè)字：這意味著計(jì)算列值長(zhǎng)度的方式不像Compact行格式那么直觀，它是采用兩個(gè)相鄰數(shù)值的差值來(lái)計(jì)算各個(gè)列值的長(zhǎng)度。

4.2 記錄頭信息（record header）

不同于Compact行格式，Redundant行格式中的記錄頭信息固定占用6個(gè)字節(jié)（48位），每位的含義見(jiàn)下表。

• 與Compact行格式的記錄頭信息對(duì)比來(lái)看，有兩處不同：
• 1.Redundant行格式多了n_field和1byte_offs_flag這兩個(gè)屬性。
• 2.Redundant行格式?jīng)]有record_type這個(gè)屬性。

4.3 Redundant行格式中NULL值的處理

因?yàn)镽edundant行格式并沒(méi)有NULL值列表，所以Redundant行格式在字段長(zhǎng)度偏移列表中的各個(gè)列對(duì)應(yīng)的偏移量處做了一些特殊處理 —— 將列對(duì)應(yīng)的偏移量值的第一個(gè)比特位作為是否為NULL的依據(jù)，該比特位也可以被稱(chēng)之為NULL比特位。也就是說(shuō)在解析一條記錄的某個(gè)列時(shí)，首先看一下該列對(duì)應(yīng)的偏移量的NULL比特位是不是為1。如果為1，那么該列的值就是NULL，否則不是NULL。

到此這篇關(guān)于MySQL Innodb行格式的文章就介紹到這了,更多相關(guān)MySQL Innodb行格式內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論