Innodb索引字段长度，数据行长度的限制

水深无声 2022-02-27 06:46 542阅读 0赞

**一、关于索引字段长度限制**  
在给大字符类型建索引时，通常会碰到如下报错：  
Specified key was too long; max key length is 767 bytes  
例如：

test是一张innodb表，字符集是utf8mb4，在name列上创建索引。由于我的环境没有设置严格的sql\_mode，所以name列上的索引还是创建成功了，但是查看warnings可知，索引列长不能超过767bytes，如果sql\_mode设置严格模式，得到的就是报错而不是warnings。  
进而查看test表上的索引可以发现，idx\_name的索引键长度只有191个字符，该表的字符集是utf8mb4，每个字符占4 bytes，所以整个索引键值的长度是191\*4=764，比767少了3 bytes，无法放下一个utf8mb4的字符，所以就取764 bytes的长度。

可以通过设置innodb\_large\_prefix=ON，row\_format=dynamic或compressed来突破767 bytes的限制，得到最长3072 bytes的拓展，例如：

mysql> show create table test2\G
    *************************** 1. row ***************************
           Table: test2
    Create Table: CREATE TABLE `test2` (
      `id` int(11) NOT NULL,
      `name` varchar(1000) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> show table status like 'test2'\G
    *************************** 1. row ***************************
               Name: test2
             Engine: InnoDB
            Version: 10
         Row_format: Dynamic
               Rows: 0
     Avg_row_length: 0
        Data_length: 16384
    Max_data_length: 0
       Index_length: 0
          Data_free: 0
     Auto_increment: NULL
        Create_time: 2019-03-26 13:45:28
        Update_time: NULL
         Check_time: NULL
          Collation: utf8mb4_general_ci
           Checksum: NULL
     Create_options: 
            Comment: 
    1 row in set (0.00 sec)
    
    mysql> show variables like 'innodb_large_prefix'; 
    +---------------------+-------+
    | Variable_name | Value |
    +---------------------+-------+
    | innodb_large_prefix | ON |
    +---------------------+-------+
    1 row in set (0.00 sec)
    
    mysql> alter table test2 add key idx_name(name);
    Query OK, 0 rows affected, 1 warning (0.02 sec)
    Records: 0 Duplicates: 0 Warnings: 1
    
    mysql> show warnings;
    +---------+------+----------------------------------------------------------+
    | Level | Code | Message |
    +---------+------+----------------------------------------------------------+
    | Warning | 1071 | Specified key was too long; max key length is 3072 bytes |
    +---------+------+----------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show index from test2;
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | test2 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
    | test2 | 1 | idx_name | 1 | name | A | 0 | 768 | NULL | YES | BTREE | | |
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    2 rows in set (0.00 sec)

做了相关设置之后，索引字段最长拓展到了3072 bytes，从idx\_name的长度来看，768\*4=3072，刚刚好。

在MySQL5.6，innodb\_large\_prefix默认是关闭的，所以索引键的长度限制是767 bytes；  
在MySQL5.7，innodb\_large\_prefix默认是打开的，所以索引键的长度限制是3072 bytes。

为什么最大可以拓展至3072 bytes？这跟数据块大小和innodb数据存储方式有关。由于innodb采用B+树的结构存储数据，所以innodb规定每个数据块必须存储至少两行数据，否则叶子层每个页只存一行数据，就变成一个数据行链表了。例如innodb\_page\_size=16384的实例，每个块存两行数据，那么每行数据最大只能到8K，而对于二级索引，不仅需要存索引键值，还需要附带主键对应的数据，而主键对应的数据，在主键索引中也是索引键值，所以二级索引的键值跟主键的长度应该是同等地位的，那么每行数据8K的大小，对半分就是各自最大占4K，而索引行中不仅包含索引键值，还有其他的一些信息数据，所以就取1024\*3=3072 bytes作为索引键值的长度上限。从下面的例子可知，主键的长度限制也是平等的3072 bytes:

mysql> create table test3(id varchar(1000) primary key,num int);
    ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

所以我们知道，3072 bytes只是针对16K的数据块来说的。如果是8K的数据块，那么就是3072/2=1536 bytes，以此类推，4K的数据块，长度限制将会是768 bytes，例如：

--innodb_large_prefix默认开启
    mysql> show variables like 'innodb_page_size';
    +------------------+-------+
    | Variable_name | Value |
    +------------------+-------+
    | innodb_page_size | 8192 |
    +------------------+-------+
    1 row in set (0.01 sec)
    
    mysql> create table test(id int primary key,name varchar(1000));
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> alter table test add key idx_name(name); 
    ERROR 1071 (42000): Specified key was too long; max key length is 1536 bytes

而对于联合索引，上述的最长限制同样适用，例如：

mysql> show variables like 'innodb_page_size';
    +------------------+-------+
    | Variable_name | Value |
    +------------------+-------+
    | innodb_page_size | 16384 |
    +------------------+-------+
    1 row in set (0.01 sec)
    mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      `home` varchar(500) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_name` (`name`(191))
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> alter table test add key idx_name_home(name,home);
    ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

可以看到，在16 K数据页的实例，联合索引同样是受到3072 bytes的限制。  
其实，innodb在16K数据页的实例下，是可以支持3500bytes的索引键长度的，但是MySQL强制性地限制在3072bytes。

**二、数据行长度的限制**  
innodb可支持的数据行长度可以超过65535 bytes，但MySQL强制性地将数据行限制在最大65535 bytes。例如：

mysql> create table test(col1 varchar(16384));
    ERROR 1074 (42000): Column length too big for column 'col1' (max = 16383); use BLOB or TEXT instead

当前实例默认字符集是utf8mb4，那么varchar(16384)的长度是16384\*4=65536>65535，所以test表创建失败。其实报错信息已经告诉我们，在该环境中，varchar字段的最大长度是16383个字符，即65532 bytes。为什么说是“该环境中”呢，因为字段的长度会因字符集的不同而变化。下面例子test表创建成功：

mysql> create table test(col1 varchar(16383));
    Query OK, 0 rows affected (0.02 sec)

交代一个前提，上面创建表的环境innodb\_page\_size=16384。  
那么细心点就会发现，col1的长度=16383\*4=65532 bytes，远大于上面索引键值分析的数据行不能超过8K的限制了。到底以哪一个限制为准呢？

可以明确地说，一个数据块必须存两行。那么，一行数据大概最大也就8K，为什么能存下65532 bytes，约64K的数据呢？  
真相是，如果变长类型的字段，比如varbinary,varchar,blob,text，这些类型在数据行里只存768 bytes或20 bytes的数据（这里跟具体的row\_format有关，可以进一步去了解，在本文中只要知道存储少量数据即可），来记录字段真实长度和转储的指针。所以，varchar(16383)实际上在数据行中只是存了少量的数据，其余数据已经在数据行之外“租房子”了。

需要注意的是，对于变成类型字段，varchar和text/blob也有区别。  
varchar类型，例如varchar(16383)，是按照16383转换成存储空间计算在数据行上的；  
而text/blob，即使是longtext，最大可存储4G的数据，你可以发现，它仍然可以在表中创建成功。原因是longtext在创建时只需要分配少许的空间来记录一下信息，绝大部分的数据都是存储在数据行以外的空间。

也就是说，varchar字段的数据内容大小是计算在数据行大小里的，varchar字段的数据大小不能超过65535 bytes；而text/blob字段的数据内容大小是不计算在数据行大小里的，即使longtext字段存了1G的数据，也不会导致数据行超过65535 bytes的异常。  
例如：

mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `col1` longtext,
      `col2` varchar(1000) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    mysql> select a.table_id,b.name as table_name,a.name as column_name,a.len from information_schema.innodb_sys_columns a,information_schema.innodb_sys_tables b where a.table_id=b.table_id and b.name='sam/test';
    +----------+------------+-------------+------+
    | table_id | table_name | column_name | len |
    +----------+------------+-------------+------+
    | 181 | sam/test | col1 | 12 |
    | 181 | sam/test | col2 | 4000 |
    +----------+------------+-------------+------+
    2 rows in set (0.00 sec)

可以看到longtext在数据行中的长度只有12 bytes  
varchar字段在数据行里，MySQL认为是占了4000 bytes，而longtext只占了12 bytes。