本篇文章給大家?guī)砹岁P(guān)于mysql的相關(guān)知識(shí)，其中主要介紹了關(guān)于從二進(jìn)制內(nèi)容看InnoDB行格式的相關(guān)問題，InnoDB是一個(gè)將表中的數(shù)據(jù)存儲(chǔ)到磁盤上的存儲(chǔ)引擎，所以即使關(guān)機(jī)后重啟我們的數(shù)據(jù)還是存在的，希望對(duì)大家有幫助。

推薦學(xué)習(xí)：mysql視頻教程

InnoDB是一個(gè)將表中的數(shù)據(jù)存儲(chǔ)到磁盤上的存儲(chǔ)引擎，所以即使關(guān)機(jī)后重啟我們的數(shù)據(jù)還是存在的。而真正處理數(shù)據(jù)的過程是發(fā)生在內(nèi)存中的，所以需要把磁盤中的數(shù)據(jù)加載到內(nèi)存中，如果是處理寫入或修改請(qǐng)求的話，還需要把內(nèi)存中的內(nèi)容刷新到磁盤上。而我們知道讀寫磁盤的速度非常慢，和內(nèi)存讀寫差了幾個(gè)數(shù)量級(jí)，所以當(dāng)我們想從表中獲取某些記錄時(shí)，InnoDB存儲(chǔ)引擎需要一條一條的把記錄從磁盤上讀出來么？

InnoDB采取的方式是：將數(shù)據(jù)劃分為若干個(gè)頁，以頁作為磁盤和內(nèi)存之間交互的基本單位，InnoDB中頁的大小一般為16KB。也就是在一般情況下，一次最少從磁盤中讀取16KB的內(nèi)容到內(nèi)存中，一次最少把內(nèi)存中的16KB內(nèi)容刷新到磁盤中。

mysql> show variables like '%innodb_page_size%'; +------------------+-------+ | Variable_name    | Value | +------------------+-------+ | innodb_page_size | 16384 | +------------------+-------+ 1 row in set (0.00 sec)

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

行記錄格式的分類和介紹

在早期的InnoDB版本中，由于文件格式只有一種，因此不需要為此文件格式命名。隨著InnoDB引擎的發(fā)展，開發(fā)出了不兼容早期版本的新文件格式，用于支持新的功能。為了在升級(jí)和降級(jí)情況下幫助管理系統(tǒng)的兼容性，以及運(yùn)行不同的MySQL版本，InnoDB開始使用命名的文件格式。

在msyql 5.7.9及以后版本，默認(rèn)行格式由innodb_default_row_format變量決定，它的默認(rèn)值是dynamic：

mysql> show variables like "innodb_file_format"; +--------------------+-----------+ | Variable_name      | Value     | +--------------------+-----------+ | innodb_file_format | Barracuda | +--------------------+-----------+ 1 row in set (0.01 sec)  mysql> show variables like "innodb_default_row_format"; +---------------------------+---------+ | Variable_name             | Value   | +---------------------------+---------+ | innodb_default_row_format | dynamic | +---------------------------+---------+ 1 row in set (0.00 sec)

查看當(dāng)前表使用的行格式：

mysql> show table status like 'dept_emp'G*************************** 1. row ***************************            Name: dept_emp         Engine: InnoDB         Version: 10      Row_format: Dynamic           Rows: 331570  Avg_row_length: 36     Data_length: 12075008Max_data_length: 0    Index_length: 5783552       Data_free: 0  Auto_increment: NULL     Create_time: 2021-08-11 09:04:36     Update_time: NULL      Check_time: NULL       Collation: latin1_swedish_ci        Checksum: NULL  Create_options:        Comment:1 row in set (0.00 sec)

指定表的行格式：

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

如果要修改現(xiàn)有表的行模式為compressed或dynamic，必須先將文件格式設(shè)置成Barracuda：set global innodb_file_format=Barracuda;，再用ALTER TABLE tablename ROW_FORMAT=COMPRESSED;去修改才能生效。

行格式

COMPACT

變長(zhǎng)字段列表

MySQL支持一些變長(zhǎng)的數(shù)據(jù)類型，比如VARCHAR(M)、VARBINARY(M)、各種TEXT類型，各種BLOB類型，我們也可以把擁有這些數(shù)據(jù)類型的列稱為變長(zhǎng)字段，變長(zhǎng)字段中存儲(chǔ)多少字節(jié)的數(shù)據(jù)是不固定的，所以我們?cè)诖鎯?chǔ)真實(shí)數(shù)據(jù)的時(shí)候需要順便把這些數(shù)據(jù)占用的字節(jié)數(shù)也存起來。如果該可變字段允許存儲(chǔ)的最大字節(jié)數(shù)（M×W）超過255字節(jié)并且真實(shí)存儲(chǔ)的字節(jié)數(shù)（L）超過127字節(jié)，則使用2個(gè)字節(jié)記錄，否則使用1個(gè)字節(jié)記錄。

問題一：那么為什么用128作為分界線呢？ 一個(gè)字節(jié)可以最多表示255，但是MySQL設(shè)計(jì)長(zhǎng)度表示時(shí)，為了區(qū)分是否是一個(gè)字節(jié)表示長(zhǎng)度，規(guī)定，如果最高位為1，那么就是兩個(gè)字節(jié)表示長(zhǎng)度，否則就是一個(gè)字節(jié)。例如，01111111，這個(gè)就代表長(zhǎng)度為127，而如果長(zhǎng)度是128，就需要兩個(gè)字節(jié)，就是10000000 10000000，首個(gè)字節(jié)的最高位為1，那么這就是兩個(gè)字節(jié)表示長(zhǎng)度的開頭，第二個(gè)字節(jié)可以用所有位表示長(zhǎng)度，并且需要注意的是，MySQL采取Little Endian的計(jì)數(shù)方式，低位在前，高位在后，所以129就是10000001 10000000。同時(shí)，這種標(biāo)識(shí)方式，最大長(zhǎng)度就是 2^15-1=32767，也就是32KB。

問題二：如果兩個(gè)字節(jié)也不夠表示的長(zhǎng)度，該怎么辦？ innoDB頁大小默認(rèn)為16KB，對(duì)于一些占用字節(jié)數(shù)非常多的字段，比方說某個(gè)字段長(zhǎng)度大于了16KB，那么如果該記錄在單個(gè)頁面中無法存儲(chǔ)時(shí)，InnoDB會(huì)把一部分?jǐn)?shù)據(jù)存放到所謂的溢出頁中，在變長(zhǎng)字段長(zhǎng)度列表處只存儲(chǔ)留在本頁面中的長(zhǎng)度，所以使用兩個(gè)字節(jié)也可以存放下來。這個(gè)溢出頁機(jī)制參考后面的數(shù)據(jù)溢出。

NULL值列表

表中的某些列可能存儲(chǔ)NULL值，如果把這些NULL值都放到記錄的真實(shí)數(shù)據(jù)中存儲(chǔ)會(huì)很占地方，所以Compact行格式把這些值為NULL的列統(tǒng)一管理起來，存儲(chǔ)到NULL值列表。每個(gè)允許存儲(chǔ)NULL的列對(duì)應(yīng)一個(gè)二進(jìn)制位，二進(jìn)制位的值為1時(shí)，代表該列的值為NULL。二進(jìn)制位的值為0時(shí)，代表該列的值不為NULL。

記錄頭信息

用于描述記錄的記錄頭信息，它是由固定的5個(gè)字節(jié)組成。5個(gè)字節(jié)也就是40個(gè)二進(jìn)制位，不同的位代表不同的意思。

隱藏列

記錄的真實(shí)數(shù)據(jù)除了我們自己定義的列的數(shù)據(jù)以外，MySQL會(huì)為每個(gè)記錄默認(rèn)的添加一些列（也稱為隱藏列），包括：

• DB_ROW_ID(row_id)：非必須，6字節(jié)，表示行ID，唯一標(biāo)識(shí)一條記錄

• DB_TRX_ID：必須，6字節(jié)，表示事務(wù)ID

• DB_ROLL_PTR：必須，7字節(jié)，表示回滾指針

InnoDB表對(duì)主鍵的生成策略是：優(yōu)先使用用戶自定義主鍵作為主鍵，如果用戶沒有定義主鍵，則選取一個(gè)Unique鍵作為主鍵，如果表中連Unique 鍵都沒有定義的話，則InnoDB會(huì)為表默認(rèn)添加一個(gè)名為row_id的隱藏列作為主鍵。

DB_TRX_ID（也可以稱為trx_id） 和DB_ROLL_PTR（也可以稱為roll_ptr） 這兩個(gè)列是必有的，但是row_id是可選的（在沒有自定義主鍵以及Unique 鍵的情況下才會(huì)添加該列）。

其他的行格式和Compact行格式差別不大。

Redundant行格式

Redundant行格式是MySQL5.0之前用的一種行格式，不予深究。

Dynamic行格式

MySQL5.7的默認(rèn)行格式就是Dynamic，Dynamic行格式和Compact行格式挺像，只不過在處理行溢出數(shù)據(jù)時(shí)有所不同。

Compressed行格式

Compressed行格式在Dynamic行格式的基礎(chǔ)上會(huì)采用壓縮算法對(duì)頁面進(jìn)行壓縮，以節(jié)省空間。以zlib的算法進(jìn)行壓縮，因此對(duì)于BLOB、TEXT、VARCHAR這類大長(zhǎng)度數(shù)據(jù)能夠進(jìn)行有效的存儲(chǔ)（減少40%，但對(duì)CPU要求更高）。

數(shù)據(jù)溢出

如果我們定義一個(gè)表，表中只有一個(gè)VARCHAR字段，如下：

CREATE TABLE test_varchar( c VARCHAR(60000))

然后往這個(gè)字段插入60000個(gè)字符，會(huì)發(fā)生什么？前邊說過，MySQL中磁盤和內(nèi)存交互的基本單位是頁，也就是說MySQL是以頁為基本單位來管理存儲(chǔ)空間的，我們的記錄都會(huì)被分配到某個(gè)頁中存儲(chǔ)。而一個(gè)頁的大小一般是16KB，也就是16384字節(jié)，而一個(gè)VARCHAR(M)類型的列就最多可以存儲(chǔ)65532個(gè)字節(jié)，這樣就可能造成一個(gè)頁存放不了一條記錄的情況。

在Compact和Redundant行格式中，對(duì)于占用存儲(chǔ)空間非常大的列，在記錄的真實(shí)數(shù)據(jù)處只會(huì)存儲(chǔ)該列的該列的前768個(gè)字節(jié)的數(shù)據(jù)，然后把剩余的數(shù)據(jù)分散存儲(chǔ)在幾個(gè)其他的頁中，記錄的真實(shí)數(shù)據(jù)處用20個(gè)字節(jié)（768字節(jié)后20個(gè)字節(jié)）存儲(chǔ)指向這些頁的地址。這個(gè)過程也叫做行溢出，存儲(chǔ)超出768字節(jié)的那些頁面也被稱為溢出頁。

Dynamic和Compressed行格式，不會(huì)在記錄的真實(shí)數(shù)據(jù)處存儲(chǔ)字段真實(shí)數(shù)據(jù)的前768個(gè)字節(jié)，而是把所有的字節(jié)都存儲(chǔ)到其他頁面中，只在記錄的真實(shí)數(shù)據(jù)處存儲(chǔ)其他頁面的地址。

實(shí)戰(zhàn)分析行格式

準(zhǔn)備表及數(shù)據(jù)：

create table row_test ( 　　t1 varchar(10), 　　t2 varchar(10), 　　t3 char(10), 　　t4 varchar(10) ) engine=innodb charset=latin1 row_format=compact;  insert into row_test values('a','bb','bb','ccc');  insert into row_test values('d','ee','ee','fff');  insert into row_test values('d',NULL,NULL,'fff');

在Linux環(huán)境下，使用hexdump -C -v mytest.ibd＞mytest.txt，打開mytest.txt文件，找到如下內(nèi)容：

0000c070  73 75 70 72 65 6d 75 6d  03 02 01 00 00 00 10 00  |supremum........| 0000c080  2c 00 00 00 00 02 00 00  00 00 00 0f 61 c8 00 00  |,...........a...| 0000c090  01 d4 01 10 61 62 62 62  62 20 20 20 20 20 20 20  |....abbbb       | 0000c0a0  20 63 63 63 03 02 01 00  00 00 18 00 2b 00 00 00  | ccc........+...| 0000c0b0  00 02 01 00 00 00 00 0f  62 c9 00 00 01 b2 01 10  |........b.......| 0000c0c0  64 65 65 65 65 20 20 20  20 20 20 20 20 66 66 66  |deeee        fff| 0000c0d0  03 01 06 00 00 20 ff 98  00 00 00 00 02 02 00 00  |..... ..........| 0000c0e0  00 00 0f 67 cc 00 00 01  b6 01 10 64 66 66 66 00  |...g.......dfff.|

該行記錄從0000c078開始，第一行整理如下：

03 02 01 // 變長(zhǎng)字段長(zhǎng)度列表，逆序，t4列長(zhǎng)度為3，t2列長(zhǎng)度為2，t1列長(zhǎng)度為1 00 // NULL標(biāo)志位，第一行沒有NULL值 00 00 10 00 2c // 記錄頭信息，固定5字節(jié)長(zhǎng)度 00 00 00 2b 68 00 // RowID我們建的表沒有主鍵，因此會(huì)有RowID，固定6字節(jié)長(zhǎng)度 00 00 00 00 06 05 // 事務(wù)ID，固定6個(gè)字節(jié)80 00 00 00 32 01 10 // 回滾指針，固定7個(gè)字節(jié)61  // t1數(shù)據(jù)'a'62 62  // t2'bb'62 62 20 20 20 20 20 20 20 20 // t3數(shù)據(jù)'bb'63 63 63 // t4數(shù)據(jù)'ccc'

第二行整理如下：

03 02 01 // 變長(zhǎng)字段長(zhǎng)度列表，逆序，t4列長(zhǎng)度為3，t2列長(zhǎng)度為2，t1列長(zhǎng)度為1 00 // NULL標(biāo)志位，第二行沒有NULL值 00 00 18 00 2b // 記錄頭信息，固定5字節(jié)長(zhǎng)度 00 00 00 00 02 01 // RowID我們建的表沒有主鍵，因此會(huì)有RowID，固定6字節(jié)長(zhǎng)度 00 00 00 00 0f 62 // 事務(wù)ID，固定6個(gè)字節(jié) c9 00 00 01 b2 01 10 // 回滾指針，固定7個(gè)字節(jié)64 // t1數(shù)據(jù)'d'65 65  // t2數(shù)據(jù)'ee'65 65 20 20 20 20 20 20 20 20 // t3數(shù)據(jù)'ee'66 66 66  // t4數(shù)據(jù)'fff'

第三行整理如下：

03 01 // 變長(zhǎng)字段長(zhǎng)度列表，逆序，t4列長(zhǎng)度為3，t1列長(zhǎng)度為1 06 // 00000110 NULL標(biāo)志位，t2和t3列為空 00 00 20 ff 98  // 記錄頭信息，固定5字節(jié)長(zhǎng)度 00 00 00 00 02 02 // RowID我們建的表沒有主鍵，因此會(huì)有RowID，固定6字節(jié)長(zhǎng)度 00 00 00 00 0f 67 // 事務(wù)ID，固定6個(gè)字節(jié) cc 00 00 01 b6 01 10 // 回滾指針，固定7個(gè)字節(jié)64 // t1數(shù)據(jù)'d'66 66 66 // t4數(shù)據(jù)'fff'

接下來更新下數(shù)據(jù)：

mysql> update row_test set t2=null where t1='a'; Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 0  mysql> delete from row_test where t2='ee'; Query OK, 1 row affected (0.01 sec)

查看二進(jìn)制內(nèi)容（需要等一會(huì)，有可能只寫入了緩存，磁盤上的文件并沒有更新）：

0000c070  73 75 70 72 65 6d 75 6d  03 01 02 00 00 10 00 58  |supremum.......X| 0000c080  00 00 00 00 02 00 00 00  00 00 0f 68 4d 00 00 01  |...........hM...| 0000c090  9e 04 a9 61 62 62 20 20  20 20 20 20 20 20 63 63  |...abb        cc| 0000c0a0  63 63 63 63 03 02 01 00  20 00 18 00 00 00 00 00  |cccc.... .......| 0000c0b0  00 02 01 00 00 00 00 0f  6a 4e 00 00 01 9f 10 c0  |........jN......| 0000c0c0  64 65 65 65 65 20 20 20  20 20 20 20 20 66 66 66  |deeee        fff| 0000c0d0  03 01 06 00 00 20 ff 98  00 00 00 00 02 02 00 00  |..... ..........| 0000c0e0  00 00 0f 67 cc 00 00 01  b6 01 10 64 66 66 66 00  |...g.......dfff.|

該行記錄從0000c078開始，第一行整理如下：

03 01 // 變長(zhǎng)字段長(zhǎng)度列表，逆序，t4列長(zhǎng)度為3，t1列長(zhǎng)度為1 02 // 0000 0010 NULL標(biāo)志位，表示t2為null 00 00 10 00 58 // 記錄頭信息，固定5字節(jié)長(zhǎng)度 00 00 00 00 02 00 // RowID我們建的表沒有主鍵，因此會(huì)有RowID，固定6字節(jié)長(zhǎng)度 00 00 00 00 0f 68 // 事務(wù)ID，固定6個(gè)字節(jié) 4d 00 00 01 9e 04 a9 // 回滾指針，固定7個(gè)字節(jié)61 // t1數(shù)據(jù)'a'62 62 20 20 20 20 20 20 20 20 // t3數(shù)據(jù)'bb'63 63 63 // t4數(shù)據(jù)'ccc'

第二行整理如下：

03 02 01 // 變長(zhǎng)字段長(zhǎng)度列表，逆序，t4列長(zhǎng)度為3，t2列長(zhǎng)度為2，t1列長(zhǎng)度為1 00 // NULL標(biāo)志位，第二行沒有NULL值20 00 18 00 00 // 0010 delete_mask=1 標(biāo)記該記錄是否被刪除  記錄頭信息，固定5字節(jié)長(zhǎng)度 00 00 00 00 02 01 // RowID我們建的表沒有主鍵，因此會(huì)有RowID，固定6字節(jié)長(zhǎng)度 00 00 00 00 0f 6a // 事務(wù)ID，固定6個(gè)字節(jié) 4e 00 00 01 9f 10 c0 // 回滾指針，固定7個(gè)字節(jié)64 // t1數(shù)據(jù)'d'65 65 // t2數(shù)據(jù)'ee'65 65 20 20 20 20 20 20 20 20 // t3數(shù)據(jù)'ee'66 66 66 // t4數(shù)據(jù)'fff'

第三行數(shù)據(jù)未發(fā)生變化。

推薦學(xué)習(xí)：mysql視頻教程