一、InnoDB行格式行格式緊湊的存儲特性增強的可變長度列存儲大型索引鍵首碼支持壓縮支持支持的表空間類型 REDUNDANT N N N N system, file-per-table, general COMPACT Y N N N system, file-per-table, g ...

一、InnoDB行格式

行格式	緊湊的存儲特性	增強的可變長度列存儲	大型索引鍵首碼支持	壓縮支持	支持的表空間類型
REDUNDANT	N	N	N	N	system, file-per-table, general
COMPACT	Y	N	N	N	system, file-per-table, general
DYNAMIC	Y	Y	N	N	system, file-per-table, general
COMPRESSED	Y	Y	Y	Y	file-per-table, general

開發中常用的是DYNAMIC行格式，這裡著重對它進行介紹，其他格式說明請查閱官網。

1.1 COMPACT存儲特性

行DYNAMIC格式提供與行格式相同的存儲特性COMPACT，但增加了對長可變長度列的增強存儲功能，並支持大索引鍵首碼

每個索引記錄包含一個 5 位元組的標頭，並且可能會有一部分的可變長度，用於將連續的記錄鏈接在一起，並用於行級鎖定;
記錄頭的可變長度部分包含一個用於指向NULL列的位向量。若索引中可以為 NULL的列數為N，則位向量占用N/8個位元組數。（例如，如果有 9 到 16 列可以是，則位向量使用兩個位元組。）除了此向量中的位之外，別的為NULL的列並不占用空間。標頭的可變長度部分還包含可變長度列的長度。每個長度占用一個或兩個位元組，具體取決於列的最大長度。如果索引中的所有列都是NOT NULL並且具有固定長度，那麼記錄頭就沒有可變長度部分;
對於每個非NULL可變長度欄位，記錄頭包含一或兩個位元組的列長度。僅當部分列存儲在溢出頁外部或最大長度超過 255 位元組且實際長度超過 127 位元組時，才需要兩個位元組。對於外部存儲的列，2位元組長度表示內部存儲部分的長度加上指向外部存儲部分的20位元組指針。內部部分是768位元組，所以長度是768+20。20 位元組的指針存儲列的真實長度;
記錄頭之後是非NULL列的數據內容;
聚集索引中的記錄包含所有用戶定義列的欄位。此外，還有一個 6 位元組的事務 ID 欄位和一個 7 位元組的滾動指針欄位;
如果沒有為表定義主鍵，則每個聚簇索引記錄還包含一個 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;

1.2 DYNAMIC存儲特性

DYNAMIC 行格式提供與 COMPACT 行格式相同的存儲特性，但增強了可變長度的存儲功能，並支持大索引鍵首碼;
當使用 ROW_FORMAT=DYNAMIC 創建表時，InnoDB 可以存儲長的可變長度列值（ VARCHAR、VARBINARY、BLOB 、 TEXT ）完全離頁，聚簇索引記錄僅包含一個 20 位元組的指針指向溢出頁。大於或等於 768 位元組的固定長度欄位被編碼為可變長度欄位;
列是否存儲在頁外取決於頁大小和行的總大小。當一行太長時，選擇最長的列進行頁外存儲，直到聚集索引記錄適合 B 樹頁。小於或等於 40 位元組的 TEXT 和 BLOB 列按行存儲;
DYNAMIC 行格式保持了將整行存儲在索引節點中的效率（如果適合）（COMPACT 和 REDUNDANT 格式也是如此），但是 DYNAMIC 行格式避免了用大量數據位元組填充 B 樹節點的問題的長列。 DYNAMIC 行格式基於這樣的想法，即如果長數據值的一部分存儲在頁外，則通常將整個值存儲在頁外是最有效的。使用 DYNAMIC 格式，較短的列可能會保留在 B 樹節點中，從而最大限度地減少給定行所需的溢出頁數;
DYNAMIC 行格式支持最多 3072 位元組的索引鍵首碼;
使用 DYNAMIC 行格式的表可以存儲在system tablespace、file-per-table tablespace和general tablespace中。要在system tablespace中存儲動態表，請禁用 innodb_file_per_table 並使用常規 CREATE TABLE 或 ALTER TABLE 語句，也可以搭配 TABLESPACE [=] innodb_system 。 innodb_file_per_table 變數不適用於一般表空間，在使用 TABLESPACE [=] innodb_system 表選項在system tablespace中存儲 DYNAMIC 表時也不適用;

二、數據類型存儲要求

https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

2.1 數據類型存儲要求

DECIMAL（和 NUMERIC）列的值使用二進位格式表示，該格式將九個十進位（基數 10）數字打包成四個位元組。每個值的整數和小數部分的分開存儲。九個十進位數字的每個倍數需要四個位元組，“剩餘”數字需要四個位元組的一部分。下表給出了多餘數字所需的存儲空間：

2.2 日期時間類型存儲要求

MySQL5.6.4 之後創建的表允許TIME、DATETIME 和 TIMESTAMP這些類型具有小數部分，這額外需要 0 到 3 個位元組，具體取決於存儲值的小數秒精度：

2.3 String類型存儲要求

M：非二進位字元串類型的聲明列長度（以字元為單位）和二進位字元串類型的位元組數；
N：給定字元串值的實際長度（以位元組為單位）；

可變長度字元串類型使用一個帶長度的首碼+數據存儲。首碼的長度根據數據類型以及首碼的值 L（字元串的位元組長度）來確定需要多少位元組(1-4)，。例如，MEDIUMTEXT 值的存儲需要 L 個位元組，加上三個位元組來存儲該值的長度；
要計算用於存儲特定 CHAR、VARCHAR 或 TEXT 列的位元組數，必須考慮該列的字元集以及是否包含多位元組字元。特別是，在使用 UTF-8 Unicode 字元集時，必須記住並非所有字元都使用相同的位元組數。 utf8mb3 和 utf8mb4 字元集可能分別要求每個字元需要三個和四個位元組；
VARCHAR、VARBINARY 、 BLOB、 TEXT是可變長度類型，存儲要求取決於以下因素：

列值的實際長度
列的最大可能長度
列使用的字元集，因為有些字元集包含多位元組字元

例如，VARCHAR(255) 列可以容納最大長度為 255 個字元的字元串。假設該列使用latin1字元集（每個字元一個位元組），實際需要存儲的是字元串的長度（L），加上一個位元組來記錄字元串的長度。對於字元串 'abcd'，L 為 4，存儲要求為 5 個位元組。如果同一列改為聲明使用 ucs2 雙位元組字元集，則存儲要求為 10 個位元組：'abcd' 的長度為八個位元組，該列需要兩個位元組來存儲長度，因為最大長度大於 255 （最多 510 個位元組）；
可以存儲在 VARCHAR 或 VARBINARY 列中的最大有效位元組數受行最大為 65535 位元組的限制，該行大小在所有列之間共用。對於存儲多位元組字元的 VARCHAR 列，有效的最大字元數更少。例如，utf8mb4 字元每個字元最多可以使用四個位元組，因此使用 utf8mb4 字元集的 VARCHAR 列最多可聲明為 16,383 個字元；
ENUM 對象的大小由不同枚舉值的數量決定。一個位元組用於具有最多 255 個可能值的枚舉。兩個位元組用於具有 256 到 65,535 個可能值的枚舉；
SET 對象的大小由不同集合成員的數量決定。如果設置大小為 N，則對象占用 (N+7)/8 個位元組，四捨五入為 1、2、3、4 或 8 個位元組。一個 SET 最多可以有 64 個成員；

三、表列數和行大小的限制

https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html

3.1 列數限制

MySQL 規定每個表只能有 4096 列，但對於給定的表，有效最大值可能會更少。確切的列限制取決於幾個因素：

表的最大行大小限制了列的數量（可能還有列大小），因為所有列的總長度不能超過此大小；
各個列的存儲要求限制了行最大固定了的列數。某些數據類型的存儲要求取決於存儲引擎、存儲格式和字元集等因素；
存儲引擎可能會施加額外的限制來限製表的列數。例如，InnoDB 的每個表有 1017 列的限制；
功能鍵部分被實現為隱藏的虛擬生成存儲列，因此表索引中的每個功能鍵部分都計入表總列限制

3.2 行大小限制

表的最大行大小由幾個因素決定：

MySQL 表內部有 65535 位元組的最大行大小限制，即使存儲引擎能夠支持更大的行也是65535。 BLOB 和 TEXT 雖然是大文本，但是由於它們的內容與行的其餘部分分開存儲，因此它們的列僅占9 到 12 個位元組；
InnoDB 表的最大行大小適用於本地存儲在資料庫頁面中的數據，在對 4KB、8KB、16KB 和 32KB innodb_page_size 設置中略小於半頁。例如，對於預設的 16KB InnoDB 頁面大小，最大行大小略小於 8KB。對於 64KB 頁面，最大行大小略小於 16KB；
如果包含可變長度列的行超過 InnoDB 最大行大小，則 InnoDB 選擇外部頁外存儲的可變長度列，直到該行符合 InnoDB 行大小限制。對於頁外存儲的可變長度列，本地存儲的數據量因行格式而異；
不同的存儲格式使用不同數量的頁眉和頁尾數據，會影響可用於行的存儲量；

3.3 行大小限制案例

1）在以下 InnoDB 和 MyISAM 示例中演示了 65,535 位元組的 MySQL 最大行大小限制。無論存儲引擎如何，都會強制執行該限制，即使存儲引擎可能能夠支持更大的行：

-- InnoDB引擎
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
                       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
                       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
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

-- MyISAM 引擎
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;
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

列更改為 TEXT 可避免 65535 位元組的行大小限制，因為 BLOB 和 TEXT 列僅占 9 到 12 位元組，並且 InnoDB 變長列的頁外存儲避免了 InnoDB 行大小限制：

-- MyISAM中TEXT的使用
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

-- InnoDB中TEXT的使用
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

2) 可變長度列的存儲包括計入行大小的長度位元組。例如，VARCHAR(255) CHARACTER SET utf8mb3 列需要兩個位元組來存儲值的長度，因此每個值最多可以占用 767 個位元組:

latin1+InnaDB:
mysql> CREATE TABLE t1
       (c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

使用的是Latin1，一個字元一個位元組，因此列需要 32765+2 + 32766+2 位元組 <  65535；

mysql> CREATE TABLE t2
       (c1 VARCHAR(65535) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
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

65535 + 2 > 65535，因此創建失敗;

mysql> CREATE TABLE t2
       (c1 VARCHAR(65533) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

65533 + 2 = 65535，創建成功

3）對於 MyISAM 表，NULL 列需要在行中額外的空間來記錄它們的值是否為 NULL。每個 NULL 列多占用一位，四捨五入到最接近的位元組

mysql> CREATE TABLE t3
       (c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL)
       ENGINE = MyISAM CHARACTER SET latin1;
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

32765 + 2 + 32766 + 2 = 65535，但是對於MyISAM還需要額外的空間來存儲null值，因此創建失敗

4）對於 4KB、8KB、16KB 和 32KB innodb_page_size 設置，InnoDB 將行大小（對於本地存儲在資料庫頁面中的數據）限製為略小於資料庫頁面的一半，而對於 64KB 頁面限製為略小於 16KB

tip: 對於64KB的一半為什麼是16，目前也沒在官方文檔找到較為詳細的解釋，只有下麵的解釋：

mysql> CREATE TABLE t4 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
       ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.
In current row format, BLOB prefix of 0 bytes is stored inline.

以上例子使用InnoDB的預設頁大小16KB，因此最大行大小會限制在8KB以下，即 8 * 1024 = 8192，由於是略小於，從報錯信息中也可以發現，真正的行大小是8126
而 33 * 255 = 8415 > 8126，因此創建失敗。

結語

這塊內容是我在MySQL官網上翻譯總結而來，內容比較枯燥，並且實際開發中也並不會註意這麼多，因為大多數業務表並不會離譜道到超出欄位數量及行大小限制。不過在認真閱讀的時候也確實對MySQL有了更深的認識，在以後創建表的時候也會有意識無意識的想到相關的規約，這對於之後的開發或糾錯或多或少都會有幫助，等真正出現該類問題時至少不會手足無措。如果你不想看官網的全英文文檔，就簡單看看這裡的介紹，當然，更建議仔細閱讀官網，其內容會更加詳盡。

MySQL的欄位數量以及長度限制