【MySQL精通之路】InnoDB(10)-行格式

1.表數據結構

1.1 聚集索引數據結構

1.2 輔助索引數據結構

2.行格式

2.1 REDUNDANT行格式

2.2 REDUNDANT存儲特性

2.3 COMPACT行格式

2.4 COMPACT存儲特性

2.5 DYNAMIC行格式

2.6 DYNAMIC存儲特性

2.7 COMPRESSED行格式

2.8 COMPRESSED存儲特性

3.定義表格的行格式

4.確定表的行格式

5.總結

1.表數據結構

表的行格式決定了其行的物理存儲方式，這反過來又會影響查詢和DML操作的性能。

多個行數據在單個磁盤頁面時，查詢和索引查找可以更快地工作，緩沖池中所需的緩存更少，寫入更新值所需的I/O也更少。

博主PS:

為了減少對磁盤的IO，數據庫加載數據的時候都是以頁為單位加載數據的，一般情況，頁的大小是16KB，比如有一行數據在某一頁中，就算這一行數據只有1B，那么數據庫也會將這一頁16KB加載到內存中來。

每個表中的數據被劃分為多頁。組成每個表的頁面被排列在一個稱為B樹（Innodb中為B+樹）索引的樹數據結構中。表數據和二級索引都使用這種類型的結構。

表示整個表的B樹索引稱為聚集索引，它是根據主鍵列組織的。

博主PS:

也就是說很多行數據和和很多索引key共同存在一個頁上，這樣的一堆頁又共同組成了一張表。

1.1 聚集索引數據結構

聚集索引數據結構的節點包含行中所有列的值。

1.2 輔助索引數據結構

輔助索引數據結構的節點包含索引列和聚集索引列的值。

可變長度列是列值存儲在B樹索引節點規則的例外。

太長而無法容納在B樹頁面上的可變長度列存儲在單獨分配的稱為溢出頁面的磁盤頁面上。這樣的列被稱為頁外列。頁外列的值存儲在溢出頁的單鏈接列表中，每個這樣的列都有自己的一個或多個溢出頁的列表。根據列長度的不同，可變長度列值的全部或一個前綴都存儲在B樹中，以避免浪費存儲空間而不得不讀取單獨的頁面。

InnoDB存儲引擎支持四種行格式：

REDUNDANT、COMPACT、DYNAMIC和COMPRESED。

Table?17.15?InnoDB Row Format Overview

行格式	緊湊型存儲特性	增強的可變長度的列存儲	大索引key前綴支持	支持壓縮	支持的表空間類型
`REDUNDANT` `(`冗余`)`	No	No	No	No	system, file-per-table, general
`COMPACT` `(`袖珍`)`	Yes	No	No	No	system, file-per-table, general
`DYNAMIC` `(`動態`)`	Yes	Yes	Yes	No	system, file-per-table, general
`COMPRESSED` `(`壓縮的`)`	Yes	Yes	Yes	Yes	file-per-table, general

?這里兩種索引的數據結構有所不同，比如聚簇索引，他的結構是由一堆主鍵構成一顆樹，然后葉子節點上放置了每行的數據，（當然也有指向下一頁的指針。別忘了Innodb中頁是順序存儲，且頭尾相連的一個鏈表結構）

2.行格式

2.1 REDUNDANT行格式

REDUNDANT格式提供了與舊版本MySQL的兼容性。

使用REDUNDANT行格式的表將可變長度列值

（VARCHAR、VARBINARY、BLOB和TEXT類型）的前768個字節存儲在B樹節點內的索引記錄中，其余部分存儲在溢出頁上。

大于或等于768字節的固定長度列被編碼為可變長度列，可變長度列可以頁外存儲。

例如：

如果字符集的最大字節長度大于3，則CHAR（255）列可以超過768字節，就像utf8mb4一樣。

如果列的值為768字節或更少，則不使用溢出頁，并且可能會節省一些I/O，因為該值完全存儲在B樹節點中。

這適用于相對較短的BLOB列值，但可能會導致B樹節點填充數據而不是鍵值，從而降低其效率。具有許多BLOB列的表可能會導致B樹節點過滿，并且包含的行過少，從而使整個索引的效率低于行較短或列值存儲在頁外的情況。

2.2 REDUNDANT存儲特性

REDUNDANT行格式具有以下存儲特性：

每個索引記錄都包含一個6字節的頭。

頭用于將連續記錄鏈接在一起，并用于行級鎖定。

聚集索引中的記錄包含所有用戶定義列的字段。

此外，還有一個6字節的事務ID字段和一個7字節的回滾指針字段。這個回滾指針主要用在MVCC中

如果沒有為表定義主鍵，則每個聚集索引記錄還包含一個6字節的行ID字段。

每個輔助索引記錄都包含為聚集索引鍵定義的、不在輔助索引中的所有主鍵列。

記錄包含指向該記錄的每個字段的指針(pointer)。如果記錄中字段的總長度小于128字節，則指針為一個字節；否則為兩個字節。指針數組稱為記錄目錄。指針指向的區域是記錄的數據部分。

在內部，固定長度字符列（如CHAR（10））以固定長度格式存儲。VARCHAR列的尾部空格不會被截斷。

大于或等于768字節的固定長度列被編碼為可變長度列，可變長度列可以頁外存儲。

例如，如果字符集的最大字節長度大于3，則CHAR（255）列可以超過768字節，就像utf8mb4一樣。

SQL NULL值在記錄目錄中保留一個或兩個字節。
如果存儲在可變長度列中，SQL NULL值將在記錄的數據部分保留零個字節。
對于固定長度的列，該null列的固定長度保留在記錄的數據部分中。

為NULL值保留固定空間允許將列從NULL值更新為非NULL值，而不會導致索引頁碎片。

博主來畫圖你們就懂了：

這是根據官方文檔的解釋畫出的圖。與其他博客有差異的地方在于記錄目錄，其他博客說的是偏移量，官方文檔說的是指針。這里存在爭議。而且很多博客抄來抄去存在錯誤。這里希望大家仔細甄別

2.3 COMPACT行格式

COMPACT行格式與REDUNDANT行格式相比，以增加某些操作的CPU使用為代價，將行存儲空間減少了約20%。

如果您的工作負載是受緩存命中率和磁盤速度限制的典型工作負載，COMPACT格式可能會更快。如果工作負載受到CPU速度的限制，那么緊湊格式可能會更慢。

使用COMPACT行格式的表將可變長度列值

（VARCHAR、VARBINARY、BLOB和TEXT類型）

的前768個字節存儲在B樹節點內的索引記錄中，其余部分存儲在溢出頁上。

大于或等于768字節的固定長度列被編碼為可變長度列，可變長度列可以頁外存儲。

例如，如果字符集的最大字節長度大于3，則CHAR（255）列可以超過768字節，就像utf8mb4一樣。

如果列的值為768字節或更少，則不使用溢出頁，并且可能會節省一些I/O，因為該值完全存儲在B樹節點中。這適用于相對較短的BLOB列值，但可能會導致B樹節點填充數據而不是鍵值，從而降低其效率。具有許多BLOB列的表可能會導致B樹節點過滿，并且包含的行過少，從而使整個索引的效率低于行較短或列值存儲在頁外的情況。

2.4 COMPACT存儲特性

COMPACT行格式具有以下存儲特性：

每個索引記錄都包含一個5字節的標頭，其前面可能有一個可變長度的標頭。標頭用于將連續記錄鏈接在一起，并用于行級鎖定。

記錄頭的可變長度部分包含一個用于指示NULL列的位向量。

如果索引中可以為NULL的列數為N，則位向量占用CEILING（N/8）字節

（例如，如果有9到16列中的任何一列可以為NULL，則位向量使用兩個字節。）

為NULL的列不占用此向量中位以外的空間。

標頭的可變長度部分還包含可變長度列的長度。

每個長度占用一個或兩個字節，具體取決于列的最大長度。如果索引中的所有列都不是NULL并且具有固定長度，則記錄頭沒有可變長度部分。

對于每個非NULL可變長度字段，記錄頭包含一個或兩個字節的列長度。只有當列的一部分存儲在溢出頁的外部，或者最大長度超過255字節而實際長度超過127字節時，才需要兩個字節。

對于外部存儲的列，2字節長度表示內部存儲部分的長度加上指向外部存儲部分的20字節指針。內部是768字節，所以長度是768+20。20字節的指針存儲列的真實長度。

記錄頭后面跟著非NULL列的數據內容。

聚集索引中的記錄包含所有用戶定義列的字段。此外，還有一個6字節的事務ID字段和一個7字節的回滾指針字段。用于MVCC版本鏈回滾

MVCC：

如果沒有為表定義主鍵，則每個聚集索引記錄還包含一個6字節的行ID字段。

每個輔助索引記錄都包含為聚集索引鍵定義的、不在輔助索引中的所有主鍵列。如果任何主鍵列是可變長度的，則每個輔助索引的記錄頭都有一個可變長度部分來記錄它們的長度，即使輔助索引是在固定長度列上定義的。

在內部，對于非可變長度字符集，固定長度字符列（如CHAR（10））以固定長度格式存儲。
VARCHAR列的尾部空格不會被截斷。

在內部，對于utf8mb3和utf8mb4等可變長度字符集，InnoDB試圖通過修剪尾部空格將CHAR（N）存儲在N個字節中。如果CHAR（N）列值的字節長度超過N個字節，則尾部空格將修剪為列值字節長度的最大值。CHAR（N）列的最大長度是最大字符字節長度×N。

至少為CHAR（N）保留N個字節。在許多情況下，保留最小空間N可以在不導致索引頁碎片的情況下就地執行列更新。相比之下，當使用REDUNDANT行格式時，CHAR（N）列占據最大字符字節長度×N。

大于或等于768字節的固定長度列被編碼為可變長度字段，這些字段可以頁外存儲。例如，如果字符集的最大字節長度大于3，則CHAR（255）列可以超過768字節，就像utf8mb4一樣。

2.5 DYNAMIC行格式

DYNAMIC行格式提供了與COMPACT行格式相同的存儲特性，但為長可變長度列添加了增強的存儲功能，并支持大索引鍵前綴。

當用ROW_FORMAT=DYNAMIC創建表時，InnoDB可以完全離頁存儲長可變長度列值（對于VARCHAR、VARBINARY、BLOB和TEXT類型），聚集索引記錄只包含一個指向溢出頁的20字節指針。

大于或等于768字節的固定長度字段被編碼為可變長度字段。例如，如果字符集的最大字節長度大于3，則CHAR（255）列可以超過768字節，就像utf8mb4一樣。

列是否存儲在頁外取決于頁大小和行的總大小。

當一行太長時，會選擇最長的列進行頁外存儲，直到聚集索引記錄適合B樹頁。

小于或等于40字節的TEXT和BLOB列存儲在行中。

如果適合的話，DYNAMIC行格式可以保持將整行存儲在索引節點中的效率（COMPACT和REDUNDANT格式也是如此），但DYNAMIC列格式避免了用大量長列數據字節填充B樹節點的問題。

DYNAMIC行格式基于這樣一種思想，即如果長數據值的一部分存儲在頁外，則通常最有效的方法是將整個值存儲在頁內。

使用DYNAMIC格式，較短的列可能會保留在B樹節點中，從而最大限度地減少給定行所需的溢出頁數。

DYNAMIC行格式支持最多3072個字節的索引鍵前綴。

使用DYNAMIC行格式的表可以存儲在system表空間、file-per-table表空間和general表空間中

要在系統表空間中存儲DYNAMIC表，請禁用innodb_file_per_table并使用常規的CREATE TABLE或ALTER TABLE語句，或者將tablespace[=]innodb_system table選項與CREATE TABLE或ALTERTABLE一起使用。

innodb_file_per_table變量不適用于general表空間

TABLESPACE[=]innodb_system table選項在系統表空間中存儲DYNAMIC表時也不適用。

2.6 DYNAMIC存儲特性

DYNAMIC行格式是COMPACT行格式的變體。有關存儲特性，請參閱COMPACT行格式存儲特性。

2.7 COMPRESSED行格式

COMPRESSED行格式提供了與DYNAMIC行格式相同的存儲特性和功能，但增加了對表和索引數據壓縮的支持。

COMPRESSED行格式使用與DYNAMIC行格式類似的頁外存儲內部細節，表和索引數據被壓縮

并使用較小的頁大小，因此需要考慮額外的存儲和性能因素。

對于COMPRESSED行格式，KEY_BLOCK_SIZE選項控制聚集索引中存儲了多少列數據，以及在溢出頁上放置了多少。

有關COMPRESSED行格式的更多信息，請參閱第17.9節“InnoDB表和頁面壓縮”

COMPRESSED行格式最多支持3072個字節的索引鍵前綴。

使用COMPRESSED行格式的表可以在file-per-table表空間和general表空間創建創建。

system表空間不支持COMPRESED行格式。

要在每個表表空間的文件中存儲COMPRESED表，必須啟用innodb_file_per_table變

innodb_file_per_table變量不適用于常規表空間。

通用表空間支持所有行格式，但需要注意的是，由于物理頁面大小不同，壓縮表和未壓縮表不能共存于同一通用表空間中。

有關更多信息，請參閱第17.6.3.3節“通用表空間”。

2.8 COMPRESSED存儲特性

COMPRESSED行格式是COMPACT行格式的變體。有關存儲特性，請參閱COMPACT行格式存儲特性。

3.定義表格的行格式

InnoDB表的默認行格式由InnoDB_default_row_format變量定義，該變量的默認值為DYNAMIC。

當row_format表選項未明確定義或指定row_format=default時，將使用默認行格式。

可以使用CREATE table或ALTER table語句中的row_format表選項顯式定義表的行格式。

例如：

CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;

明確定義的ROW_FORMAT設置將覆蓋默認的行格式。指定ROW_FORMAT=DEFAULT相當于使用隱式默認值。

innodb_default_row_format變量可以動態設置：

mysql> SET GLOBAL innodb_default_row_format=DYNAMIC;

有效的innodb_default_row_format選項包括DYNAMIC、COMPACT和REDUNDANT。COMPRESSED行格式不支持在系統表空間中使用，不能定義為默認格式。

它只能在CREATE TABLE或ALTER TABLE語句中顯式指定。試圖將innodb_default_row_format變量設置為COMPRESSED返回錯誤：

mysql> SET GLOBAL innodb_default_row_format=COMPRESSED;
ERROR 1231 (42000): Variable 'innodb_default_row_format'
can't be set to the value of 'COMPRESSED'

當未顯式指定row_format選項或使用row_format=default時，新創建的表使用innodb_default_row_format變量定義的行格式。例如，以下CREATE TABLE語句使用innodb_default_row_format變量定義的行格式。

CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT) ROW_FORMAT=DEFAULT;

如果沒有明確指定ROW_FORMAT選項，或者使用ROW_FORMAT=DEFAULT，則重新構建表的操作會將表的行格式更改為innodb_DEFAULT_ROW_FORMAT變量定義的格式。

表重建操作包括ALTER TABLE操作，這些操作在需要表重建的情況下使用ALGORITHM=COPY或ALGORITHM=INPLACE。

有關更多信息，請參閱第17.12.1節“在線DDL操作”。

OPTIMIZE TABLE也是一個表重建操作。

下面的示例演示了一個表重建操作，該操作可以無提示地更改在沒有明確定義行格式的情況下創建的表的行格式。

mysql> SELECT @@innodb_default_row_format;
+-----------------------------+
| @@innodb_default_row_format |
+-----------------------------+
| dynamic                     |
+-----------------------------+mysql> CREATE TABLE t1 (c1 INT);mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************TABLE_ID: 54NAME: test/t1FLAG: 33N_COLS: 4SPACE: 35ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0SPACE_TYPE: Singlemysql> SET GLOBAL innodb_default_row_format=COMPACT;mysql> ALTER TABLE t1 ADD COLUMN (c2 INT);mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1' \G
*************************** 1. row ***************************TABLE_ID: 55NAME: test/t1FLAG: 1N_COLS: 5SPACE: 36ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0SPACE_TYPE: Single

在將現有表的行格式從REDUNDANT或COMPACT更改為DYNAMIC之前，請考慮以下潛在問題。

REDUNDANT和COMPACT行格式支持767字節的最大索引關鍵字前綴長度，而DYNAMIC和COMPRESED行格式支持3072字節的索引關鍵字前綴長度。

在復制環境中，如果innodb_default_row_format變量在源上設置為DYNAMIC，在副本上設置為COMPACT，則以下DDL語句（未明確定義行格式）在源上成功，但在副本上失敗：

CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR(5000), KEY i1(c2(3070)));

有關相關信息，請參閱“InnoDB限制”。ps 這里可以催更

如果源服務器上的innodb_default_row_format設置與目標服務器上的設置不同，則導入未明確定義行格式的表會導致架構不匹配錯誤。

有關更多信息，請參閱“導入InnoDB表”。

4.確定表的行格式

要確定表格的行格式，請使用? SHOW TABLE STATUS: ?

mysql> SHOW TABLE STATUS IN test1\G
*************************** 1. row ***************************Name: t1Engine: InnoDBVersion: 10Row_format: DynamicRows: 0Avg_row_length: 0Data_length: 16384
Max_data_length: 0Index_length: 16384Data_free: 0Auto_increment: 1Create_time: 2016-09-14 16:29:38Update_time: NULLCheck_time: NULLCollation: utf8mb4_0900_ai_ciChecksum: NULLCreate_options:Comment:

或者，查詢Information Schema庫中的INNODB_TABLES表：

mysql> SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test1/t1';
+----------+------------+
| NAME     | ROW_FORMAT |
+----------+------------+
| test1/t1 | Dynamic    |
+----------+------------+