本篇文章給大家帶來了關于mysql的相關知識，其中主要介紹了關于從二進制內容看innodb行格式的相關問題，innodb是一個將表中的數據存儲到磁盤上的存儲引擎，所以即使關機后重啟我們的數據還是存在的，希望對大家有幫助。
推薦學習：mysql
InnoDB是一個將表中的數據存儲到磁盤上的存儲引擎，所以即使關機后重啟我們的數據還是存在的。而真正處理數據的過程是發生在內存中的，所以需要把磁盤中的數據加載到內存中，如果是處理寫入或修改請求的話，還需要把內存中的內容刷新到磁盤上。而我們知道讀寫磁盤的速度非常慢，和內存讀寫差了幾個數量級，所以當我們想從表中獲取某些記錄時，InnoDB存儲引擎需要一條一條的把記錄從磁盤上讀出來么？
InnoDB采取的方式是：將數據劃分為若干個頁，以頁作為磁盤和內存之間交互的基本單位，InnoDB中頁的大小一般為16KB。也就是在一般情況下，一次最少從磁盤中讀取16KB的內容到內存中，一次最少把內存中的16KB內容刷新到磁盤中。
mysql> show variables like '%innodb_page_size%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_page_size | 16384 | +------------------+-------+ 1 row in set (0.00 sec)
我們平時是以記錄為單位來向表中插入數據的，這些記錄在磁盤上的存放方式也被稱為行格式或者記錄格式。InnoDB存儲引擎設計了4種不同類型的行格式，分別是Compact、Redundant、Dynamic和Compressed行格式。
行記錄格式的分類和介紹
在早期的InnoDB版本中，由于文件格式只有一種，因此不需要為此文件格式命名。隨著InnoDB引擎的發展，開發出了不兼容早期版本的新文件格式，用于支持新的功能。為了在升級和降級情況下幫助管理系統的兼容性，以及運行不同的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=行格式名稱;
如果要修改現有表的行模式為compressed或dynamic，必須先將文件格式設置成Barracuda：set global innodb_file_format=Barracuda;，再用ALTER TABLE tablename ROW_FORMAT=COMPRESSED;去修改才能生效。
行格式
COMPACT
變長字段列表
MySQL支持一些變長的數據類型，比如VARCHAR(M)、VARBINARY(M)、各種TEXT類型，各種BLOB類型，我們也可以把擁有這些數據類型的列稱為變長字段，變長字段中存儲多少字節的數據是不固定的，所以我們在存儲真實數據的時候需要順便把這些數據占用的字節數也存起來。如果該可變字段允許存儲的最大字節數（M×W）超過255字節并且真實存儲的字節數（L）超過127字節，則使用2個字節記錄，否則使用1個字節記錄。
問題一：那么為什么用128作為分界線呢？ 一個字節可以最多表示255，但是MySQL設計長度表示時，為了區分是否是一個字節表示長度，規定，如果最高位為1，那么就是兩個字節表示長度，否則就是一個字節。例如，01111111，這個就代表長度為127，而如果長度是128，就需要兩個字節，就是10000000 10000000，首個字節的最高位為1，那么這就是兩個字節表示長度的開頭，第二個字節可以用所有位表示長度，并且需要注意的是，MySQL采取Little Endian的計數方式，低位在前，高位在后，所以129就是10000001 10000000。同時，這種標識方式，最大長度就是 2^15-1=32767，也就是32KB。
問題二：如果兩個字節也不夠表示的長度，該怎么辦？ innoDB頁大小默認為16KB，對于一些占用字節數非常多的字段，比方說某個字段長度大于了16KB，那么如果該記錄在單個頁面中無法存儲時，InnoDB會把一部分數據存放到所謂的溢出頁中，在變長字段長度列表處只存儲留在本頁面中的長度，所以使用兩個字節也可以存放下來。這個溢出頁機制參考后面的數據溢出。
NULL值列表
表中的某些列可能存儲NULL值，如果把這些NULL值都放到記錄的真實數據中存儲會很占地方，所以Compact行格式把這些值為NULL的列統一管理起來，存儲到NULL值列表。每個允許存儲NULL的列對應一個二進制位，二進制位的值為1時，代表該列的值為NULL。二進制位的值為0時，代表該列的值不為NULL。
記錄頭信息
用于描述記錄的記錄頭信息，它是由固定的5個字節組成。5個字節也就是40個二進制位，不同的位代表不同的意思。
| 字段 | 長度（bit） | 說明 |
|---|---|---|
| 預留位1 | 1 | 沒有使用 |
| 預留位2 | 1 | 沒有使用 |
| delete_mask | 1 | 標記該記錄是否被刪除 |
| min_rec_mask | 1 | B+樹的每層非葉子節點中的最小記錄都會添加該標記 |
| n_owned | 4 | 表示當前記錄擁有的記錄數 |
| heap_no | 13 | 表示當前記錄在頁的位置信息 |
| record_type | 3 | 表示當前記錄的類型，0 表示普通記錄，1 表示B+樹非葉子節點記錄，2 表示最小記錄，3 表示最大記錄 |
| next_record | 16 | 表示下一條記錄的相對位置 |
隱藏列
記錄的真實數據除了我們自己定義的列的數據以外，MySQL會為每個記錄默認的添加一些列（也稱為隱藏列），包括：
-
DB_ROW_ID(row_id)：非必須，6字節，表示行ID，唯一標識一條記錄
-
DB_TRX_ID：必須，6字節，表示事務ID
-
DB_ROLL_PTR：必須，7字節，表示回滾指針
InnoDB表對主鍵的生成策略是：優先使用用戶自定義主鍵作為主鍵，如果用戶沒有定義主鍵，則選取一個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行格式挺像，只不過在處理行溢出數據時有所不同。
Compressed行格式
Compressed行格式在Dynamic行格式的基礎上會采用壓縮算法對頁面進行壓縮，以節省空間。以zlib的算法進行壓縮，因此對于BLOB、TEXT、VARCHAR這類大長度數據能夠進行有效的存儲（減少40%，但對CPU要求更高）。
數據溢出
如果我們定義一個表，表中只有一個VARCHAR字段，如下：
CREATE TABLE test_varchar( c VARCHAR(60000))
然后往這個字段插入60000個字符，會發生什么？前邊說過，MySQL中磁盤和內存交互的基本單位是頁，也就是說MySQL是以頁為基本單位來管理存儲空間的，我們的記錄都會被分配到某個頁中存儲。而一個頁的大小一般是16KB，也就是16384字節，而一個VARCHAR(M)類型的列就最多可以存儲65532個字節，這樣就可能造成一個頁存放不了一條記錄的情況。
在Compact和Redundant行格式中，對于占用存儲空間非常大的列，在記錄的真實數據處只會存儲該列的該列的前768個字節的數據，然后把剩余的數據分散存儲在幾個其他的頁中，記錄的真實數據處用20個字節（768字節后20個字節）存儲指向這些頁的地址。這個過程也叫做行溢出，存儲超出768字節的那些頁面也被稱為溢出頁。
Dynamic和Compressed行格式，不會在記錄的真實數據處存儲字段真實數據的前768個字節，而是把所有的字節都存儲到其他頁面中，只在記錄的真實數據處存儲其他頁面的地址。
實戰分析行格式
準備表及數據：
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環境下，使用hexdump -C -v mytest.ibd＞mytest.txt，打開mytest.txt文件，找到如下內容：
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字節長度 00 00 00 2b 68 00 // RowID我們建的表沒有主鍵，因此會有RowID，固定6字節長度 00 00 00 00 06 05 // 事務ID，固定6個字節80 00 00 00 32 01 10 // 回滾指針，固定7個字節61 // t1數據'a'62 62 // t2'bb'62 62 20 20 20 20 20 20 20 20 // t3數據'bb'63 63 63 // t4數據'ccc'
第二行整理如下：
03 02 01 // 變長字段長度列表，逆序，t4列長度為3，t2列長度為2，t1列長度為1 00 // NULL標志位，第二行沒有NULL值 00 00 18 00 2b // 記錄頭信息，固定5字節長度 00 00 00 00 02 01 // RowID我們建的表沒有主鍵，因此會有RowID，固定6字節長度 00 00 00 00 0f 62 // 事務ID，固定6個字節 c9 00 00 01 b2 01 10 // 回滾指針，固定7個字節64 // t1數據'd'65 65 // t2數據'ee'65 65 20 20 20 20 20 20 20 20 // t3數據'ee'66 66 66 // t4數據'fff'
第三行整理如下：
03 01 // 變長字段長度列表，逆序，t4列長度為3，t1列長度為1 06 // 00000110 NULL標志位，t2和t3列為空 00 00 20 ff 98 // 記錄頭信息，固定5字節長度 00 00 00 00 02 02 // RowID我們建的表沒有主鍵，因此會有RowID，固定6字節長度 00 00 00 00 0f 67 // 事務ID，固定6個字節 cc 00 00 01 b6 01 10 // 回滾指針，固定7個字節64 // t1數據'd'66 66 66 // t4數據'fff'
接下來更新下數據：
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)
查看二進制內容（需要等一會，有可能只寫入了緩存，磁盤上的文件并沒有更新）：
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字節長度 00 00 00 00 02 00 // RowID我們建的表沒有主鍵，因此會有RowID，固定6字節長度 00 00 00 00 0f 68 // 事務ID，固定6個字節 4d 00 00 01 9e 04 a9 // 回滾指針，固定7個字節61 // t1數據'a'62 62 20 20 20 20 20 20 20 20 // t3數據'bb'63 63 63 // t4數據'ccc'
第二行整理如下：
03 02 01 // 變長字段長度列表，逆序，t4列長度為3，t2列長度為2，t1列長度為1 00 // NULL標志位，第二行沒有NULL值20 00 18 00 00 // 0010 delete_mask=1 標記該記錄是否被刪除 記錄頭信息，固定5字節長度 00 00 00 00 02 01 // RowID我們建的表沒有主鍵，因此會有RowID，固定6字節長度 00 00 00 00 0f 6a // 事務ID，固定6個字節 4e 00 00 01 9f 10 c0 // 回滾指針，固定7個字節64 // t1數據'd'65 65 // t2數據'ee'65 65 20 20 20 20 20 20 20 20 // t3數據'ee'66 66 66 // t4數據'fff'
第三行數據未發生變化。
推薦學習：mysql
