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

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

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

InnoDB采取的方式是：將數(shù)據(jù)劃分為若干個頁，以頁作為磁盤和內(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ù)據(jù)的，這些記錄在磁盤上的存放方式也被稱為行格式或者記錄格式。InnoDB存儲引擎設(shè)計了4種不同類型的行格式，分別是Compact、Redundant、Dynamic和Compressed行格式。

行記錄格式的分類和介紹

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

在msyql 5.7.9及以后版本，默認行格式由innodb_default_row_format變量決定，它的默認值是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)

查看當前表使用的行格式：

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

變長字段列表

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

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

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

NULL值列表

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

記錄頭信息

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

隱藏列

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

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

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

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

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

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

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

Redundant行格式

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

Dynamic行格式

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

Compressed行格式

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

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

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

CREATE TABLE test_varchar( c VARCHAR(60000))

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

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

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

實戰(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 // 變長字段長度列表，逆序，t4列長度為3，t2列長度為2，t1列長度為1 00 // NULL標志位，第一行沒有NULL值 00 00 10 00 2c // 記錄頭信息，固定5字節(jié)長度 00 00 00 2b 68 00 // RowID我們建的表沒有主鍵，因此會有RowID，固定6字節(jié)長度 00 00 00 00 06 05 // 事務(wù)ID，固定6個字節(jié)80 00 00 00 32 01 10 // 回滾指針，固定7個字節(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 // 變長字段長度列表，逆序，t4列長度為3，t2列長度為2，t1列長度為1 00 // NULL標志位，第二行沒有NULL值 00 00 18 00 2b // 記錄頭信息，固定5字節(jié)長度 00 00 00 00 02 01 // RowID我們建的表沒有主鍵，因此會有RowID，固定6字節(jié)長度 00 00 00 00 0f 62 // 事務(wù)ID，固定6個字節(jié) c9 00 00 01 b2 01 10 // 回滾指針，固定7個字節(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 // 變長字段長度列表，逆序，t4列長度為3，t1列長度為1 06 // 00000110 NULL標志位，t2和t3列為空 00 00 20 ff 98  // 記錄頭信息，固定5字節(jié)長度 00 00 00 00 02 02 // RowID我們建的表沒有主鍵，因此會有RowID，固定6字節(jié)長度 00 00 00 00 0f 67 // 事務(wù)ID，固定6個字節(jié) cc 00 00 01 b6 01 10 // 回滾指針，固定7個字節(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)

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

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 // 變長字段長度列表，逆序，t4列長度為3，t1列長度為1 02 // 0000 0010 NULL標志位，表示t2為null 00 00 10 00 58 // 記錄頭信息，固定5字節(jié)長度 00 00 00 00 02 00 // RowID我們建的表沒有主鍵，因此會有RowID，固定6字節(jié)長度 00 00 00 00 0f 68 // 事務(wù)ID，固定6個字節(jié) 4d 00 00 01 9e 04 a9 // 回滾指針，固定7個字節(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 // 變長字段長度列表，逆序，t4列長度為3，t2列長度為2，t1列長度為1 00 // NULL標志位，第二行沒有NULL值20 00 18 00 00 // 0010 delete_mask=1 標記該記錄是否被刪除  記錄頭信息，固定5字節(jié)長度 00 00 00 00 02 01 // RowID我們建的表沒有主鍵，因此會有RowID，固定6字節(jié)長度 00 00 00 00 0f 6a // 事務(wù)ID，固定6個字節(jié) 4e 00 00 01 9f 10 c0 // 回滾指針，固定7個字節(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視頻教程