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

查看MySQL8的默認行格式：

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

1、指定行格式的語法

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

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

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ù)據(jù)兩大部分

2.1 變長字段長度列表

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

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

變長字段長度列表示意圖

2.2 NULL值列表

Compact行格式會把可以為NULL的列統(tǒng)一管理起來，存在一個標記為NULL值列表中。如果表中沒有允許存儲 NULL 的列，則 NULL值列表也不存在了。

為什么定義NULL值列表？

之所以要存儲NULL是因為數(shù)據(jù)都是需要對齊的，如果沒有標注出來NULL值的位置，就有可能在查詢數(shù)據(jù)的時候出現(xiàn)混亂。如果使用一個特定的符號放到相應(yīng)的數(shù)據(jù)位表示空置的話，雖然能達到效果，但是這樣很浪費空間，所以直接就在行數(shù)據(jù)得頭部開辟出一塊空間專門用來記錄該行數(shù)據(jù)哪些是非空數(shù)據(jù)，哪些是空數(shù)據(jù)。

格式如下：

1.二進制位的值為1時，代表該列的值為NULL。

2.二進制位的值為0時，代表該列的值不為NULL。

例如：字段 a、b、c，其中a是主鍵，在某一行中存儲的數(shù)依次是 a=1、b=null、c=2。那么Compact行格式中的NULL值列表中存儲：01。第一個0表示c不為null，第二個1表示b是null。這里之所以沒有a是因為數(shù)據(jù)庫會自動跳過主鍵，因為主鍵肯定是非NULL且唯一的，在NULL值列表的數(shù)據(jù)中就會自動跳過主鍵。

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

第一條記錄

第二條記錄

2.3 記錄頭信息

見 Inonodb頁  4、User Records (用戶記錄)

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

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

實際上這幾個列的真正名稱其實是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。

一個表沒有手動定義主鍵，則會選取一個Unique鍵作為主鍵，如果連Unique鍵都沒有定義的話，則會為表默認添加一個名為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這個二進制文件。內(nèi)容如下

分析：

1、該行記錄從0000c070開始
2、03 02 01：變長字段長度列表，逆序
3、00：*NULL標志位，第一行沒有NULL值
4、00 00 10 00 2c：Record Header，固定5字節(jié)長度
5、00 00 00 2b 68 00：RowID InnoDB自動創(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：固定長度CHAR字段在未能完全占用其長度空間時，會用0x20來進行填充。
Record Header的最后兩個字節(jié)，這兩個字節(jié)代表next_recorder，0x2c代表下一個記錄的偏移量，即當(dāng)前記錄的位置加上偏移量0x2c就是下條記錄的起始位置。
第二行將不做整理，除了RowID不同外，它和第一行大同小異，現(xiàn)在來分析有NULL值的第三行
03 01：變長字段長度列表，逆序
06：NULL標志位，第三行有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標志位不再是00而是06，轉(zhuǎn)換成二進制為00000110，為1的值代表第2列和第3列的數(shù)據(jù)為NULL。在其后存儲列數(shù)據(jù)的部分，用戶會發(fā)現(xiàn)沒有存儲NULL列，而只存儲了第1列和第4列非NULL的值。因此這個例子很好地說明了：不管是CHAR類型還是VARCHAR類型，在compact格式下NULL值都不占用任何存儲空間。

3、Dynamic和Compressed行格式

3.1 行溢出

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

驗證VARCHAR(M)類型，是否可以存放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

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

如果該VARCHAR類型的列沒有NOT NULL屬性，那最多只能存儲65532個字節(jié)的數(shù)據(jù)，因為變長字段的長度占用 2個字節(jié)，NULL值標識需要占用1個字節(jié)。

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

如果有not null屬性，那么就不需要NULL值標識，也就可以多存儲一個字節(jié)，即65533個字節(jié)

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

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

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

3.2、Dynamic和Compressed行格式

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

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

Compact和Redundant兩種格式會在記錄的真實數(shù)據(jù)處存儲一部分數(shù)據(jù)（存放768個前綴字節(jié)）。

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

4、Redundant行格式

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

把表record_test_table的行格式修改為Redundant

ALTER TABLE record_test_table ROW_FORMAT=Redundant;

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

4.1 字段長度偏移列表

注意Compact行格式的開頭是變長字段長度列表，而Redundant行格式的開頭是字段長度偏移列表，與變長字段長度列表有兩處不同：

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

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

4.2 記錄頭信息（record header）

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

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

4.3 Redundant行格式中NULL值的處理

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

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

最新評論