且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事
您所在的位置：首页 > MySQL前缀索引上限案例分析

MySQL前缀索引上限案例分析

更新时间：2022-01-16 01:46:00

一、案例分享

1.1 问题描述

以下一例报错是开发同学通过框架初始化创建一些表结构时出现的报错，我们需要重点关注“1071 Specified key was too long; max key length is 7671071 Specified key was too long; max key length is 767 bytes”这个提示。该报错告诉我们索引长度超过的额767，因过长而无法创建索引。这也是为什么经常在一些MySQL的SQL审批中，DBA同学会经常要求某个表的字段长度尽量不要超过191、某些表的字段长度不要超过255。

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes in /var/www/html/includes/vendor/aura/sql/src/ExtendedPdo.php:748 Stack trace: #0 /var/www/html/includes/vendor/aura/sql/src/ExtendedPdo.php(748): PDOStatement->execute() #1 /var/www/html/includes/functions-install.php(252): Aura\Sql\ExtendedPdo->perform('CREATE TABLE IF...') #2 /var/www/html/admin/install.php(46): yourls_create_sql_tables() #3 {main} thrown in /var/www/html/includes/vendor/aura/sql/src/ExtendedPdo.php on line 748

MySQL中索引长度主要受什么的限制呢？是否可以把767的限制进行放宽？

1.2 问题处理

1、查看数据库innodb_file_format、innodb_large_prefix参数

innodb_file_format=Barracuda
innodb_large_prefix=OFF

2、查看innodb_default_row_format参数

innodb_default_row_format=Compact
由于业务方使用的MySQL是5.6版本，所有表默认ROW_FORMAT=Compact。

3、通过以上3个参数，况基本明了，目前的解决办法有3种：

1.改表结构，字段长度可以缩小的话就缩小，utf8255,utf8mbe191.
2.建表语句添加ROW_FORMAT=DYNAMIC
3.线上使用5.7版本的数据库

二、MySQL前缀索引上的一些限制

MySQL前缀索引上限案例分析

2.1 Redundant

1、row_format=redundant要求innodb_file_format=Barracuda/Antelope。

2、该行模式下，对于可变长字段，innodb会存储其起始的768字节在B-tree的节点中，超出部分存储在溢出页中。对于长度小于768字节的字段，其信息全部存储在B-tree节点中。这对于长度较小的大字段来说是比较有利的，可以减小数据查询时的IO消耗。若表有有较多的大字段，就会造成b-tree中存储大量的数据，每页存储的行数也就相应的变少，从而导致我们索引查询效率变低。

3、redundant行模式下，字段索引长度需小于768字节，不支持对前缀索引长度的扩大。对于utf8字符集格式的字段，每3个字节为一个字符；对于utf8mb4字符集格式的字段，每4个字节为一个字符；对于latin1字符集格式的字段，每1个字节为一个字符

+--------------+---------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name          | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+---------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_redundant_latin1  | InnoDB | Redundant  | latin1             | name        |                      768 |                    768 |
| db1          | t_redundant_utf8    | InnoDB | Redundant  | utf8               | name        |                      256 |                    768 |
| db1          | t_redundant_utf8mb4 | InnoDB | Redundant  | utf8mb4            | name        |                      192 |                    768 |
+--------------+---------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
sansi@mysql 11:41:  [db1]> alter table t_redundant_utf8 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
sansi@mysql 11:41:  [db1]> alter table t_redundant_utf8mb4 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
sansi@mysql 12:09:  [db1]> alter table t_redundant_latin1 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes


+--------------+---------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name          | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+---------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_redundant_latin1  | InnoDB | Redundant  | latin1             | name        |                      767 |                    767 |
| db1          | t_redundant_utf8    | InnoDB | Redundant  | utf8               | name        |                      255 |                    765 |
| db1          | t_redundant_utf8mb4 | InnoDB | Redundant  | utf8mb4            | name        |                      191 |                    764 |
+--------------+---------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
sansi@mysql 11:43:  [db1]> alter table t_redundant_utf8 add index idx_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 11:43:  [db1]> alter table t_redundant_utf8mb4 add index idx_name(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 12:15:  [db1]> alter table t_redundant_latin1 add index idx_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.2 compact

1、compact行模式相对于redundant来说可以节省20%的空间，但是会增加一些CPU的消耗。MySQL5.6的默认行模式，row_format=compact要求innodb_file_format=Barracuda/Antelope。

2、该行模式下，对于可变长字段，innodb会存储其起始的768字节在B-tree的节点中，超出部分存储在溢出页中。对于长度小于768字节的字段，其信息全部存储在B-tree节点中。这对于长度较小的大字段来说是比较有利的，可以减小数据查询时的IO消耗。若表有有较多的大字段，就会造成b-tree中存储大量的数据，每页存储的行数也就相应的变少，从而导致我们索引查询效率变低。

3、compact行模式下，字段索引长度需小于768字节，不支持对前缀索引长度的扩大。对于utf8字符集格式的字段，每3个字节为一个字符；对于utf8mb4字符集格式的字段，每4个字节为一个字符；对于latin1字符集格式的字段，每1个字节为一个字符

+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name        | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_compact_latin1  | InnoDB | Compact    | latin1             | name        |                      768 |                    768 |
| db1          | t_compact_utf8    | InnoDB | Compact    | utf8               | name        |                      256 |                    768 |
| db1          | t_compact_utf8mb4 | InnoDB | Compact    | utf8mb4            | name        |                      192 |                    768 |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
sansi@mysql 12:26:  [db1]> alter table t_compact_latin1 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
sansi@mysql 12:26:  [db1]> alter table t_compact_utf8 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
sansi@mysql 12:26:  [db1]> alter table t_compact_utf8mb4 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes


+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name        | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_compact_latin1  | InnoDB | Compact    | latin1             | name        |                      767 |                    767 |
| db1          | t_compact_utf8    | InnoDB | Compact    | utf8               | name        |                      255 |                    765 |
| db1          | t_compact_utf8mb4 | InnoDB | Compact    | utf8mb4            | name        |                      191 |                    764 |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
sansi@mysql 12:24:  [db1]> alter table t_compact_latin1 add index idx_name(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 12:25:  [db1]> alter table t_compact_utf8 add index idx_name(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 12:25:  [db1]> alter table t_compact_utf8mb4 add index idx_name(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.3 dynamic

1、dynamic在空间存储上与compact格式相同，额外增加的一个特性是针对可变长度的字段的溢出页存储技术，dynamic模式下使用的是完全溢出页存储。row_format=dynamic是MySQL5.7的默认值。要求innodb_file_format=Barracuda

2、该行模式下，使用完全溢出页进行存储，其聚集索引仅仅保存一个20字节的指针指向溢出页。其存储是否需要使用溢出页取决于数据页大小和行大小，较短的字段字节存储在b-tree中，较长的字段将整个值存储在溢出页，并使用指针进行关联。

3、dynamic行模式下，可以将索引前缀限制由之前的768提升至3072。对于utf8字符集格式的字段，每3个字节为一个字符；对于utf8mb4字符集格式的字段，每4个字节为一个字符；对于latin1字符集格式的字段，每1个字节为一个字符

1）满足索引前缀提升至3072需要关注以下几个参数

innodb_file_format=Barracuda
row_format=dynamic
innodb_file_per_table=on
innodb_large_prefix=on

2）示例

+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name        | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_dynamic_latin1  | InnoDB | Dynamic    | latin1             | name        |                     3073 |                   3073 |
| db1          | t_dynamic_utf8    | InnoDB | Dynamic    | utf8               | name        |                     1025 |                   3075 |
| db1          | t_dynamic_utf8mb4 | InnoDB | Dynamic    | utf8mb4            | name        |                      769 |                   3076 |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
3 rows in set (0.03 sec)
sansi@mysql 15:20:  [db1]> alter table t_dynamic_utf8 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
sansi@mysql 15:20:  [db1]> alter table t_dynamic_utf8mb4 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
sansi@mysql 15:20:  [db1]> alter table t_dynamic_latin1 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name        | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_dynamic_latin1  | InnoDB | Dynamic    | latin1             | name        |                     3072 |                   3072 |
| db1          | t_dynamic_utf8    | InnoDB | Dynamic    | utf8               | name        |                     1024 |                   3072 |
| db1          | t_dynamic_utf8mb4 | InnoDB | Dynamic    | utf8mb4            | name        |                      768 |                   3072 |
+--------------+-------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+

sansi@mysql 15:17:  [db1]> alter table t_dynamic_latin1 add index idx_name(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 15:18:  [db1]> alter table t_dynamic_utf8mb4 add index idx_name(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 15:19:  [db1]> alter table t_dynamic_utf8 add index idx_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.4 compressed

1、compressed是在dynamic的基础上，额外增强了对索引和数据的压缩，row_format=comPRESSED要求innodb_file_format=Barracuda

2、该行模式下，使用完全溢出页进行存储，其聚集索引仅仅保存一个20字节的指针指向溢出页。其存储是否需要使用溢出页取决于数据页大小和行大小，较短的字段字节存储在b-tree中，较长的字段将整个值存储在溢出页，并使用指针进行关联。

3、dynamic行模式下，可以将索引前缀限制由之前的768提升至3072。对于utf8字符集格式的字段，每3个字节为一个字符；对于utf8mb4字符集格式的字段，每4个字节为一个字符；对于latin1字符集格式的字段，每1个字节为一个字符

1）满足索引前缀提升至3072需要关注以下几个参数

innodb_file_format=Barracuda
row_format=dynamic
innodb_file_per_table=on
innodb_large_prefix=on

2）示例

+--------------+----------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name           | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+----------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_compressed_latin1  | InnoDB | Compressed | latin1             | name        |                     3073 |                   3073 |
| db1          | t_compressed_utf8    | InnoDB | Compressed | utf8               | name        |                     1025 |                   3075 |
| db1          | t_compressed_utf8mb4 | InnoDB | Compressed | utf8mb4            | name        |                      769 |                   3076 |
+--------------+----------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
sansi@mysql 15:33:  [db1]> alter table t_compressed_utf8mb4 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
sansi@mysql 15:35:  [db1]> alter table t_compressed_utf8 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
sansi@mysql 15:35:  [db1]> alter table t_compressed_latin1 add index idx_name(name);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

+--------------+----------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| table_schema | table_name           | engine | row_format | CHARACTER_SET_NAME | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |
+--------------+----------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
| db1          | t_compressed_latin1  | InnoDB | Compressed | latin1             | name        |                     3071 |                   3071 |
| db1          | t_compressed_utf8    | InnoDB | Compressed | utf8               | name        |                     1024 |                   3072 |
| db1          | t_compressed_utf8mb4 | InnoDB | Compressed | utf8mb4            | name        |                      768 |                   3072 |
+--------------+----------------------+--------+------------+--------------------+-------------+--------------------------+------------------------+
sansi@mysql 15:36:  [db1]>
sansi@mysql 15:36:  [db1]> alter table t_compressed_utf8mb4 add index idx_name(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 15:36:  [db1]> alter table t_compressed_utf8 modify name varchar(1024);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
sansi@mysql 15:36:  [db1]> alter table t_compressed_latin1 modify name varchar(3071);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

三、MySQL表字段上的一些限制

3.1、列与数据类型间的限制

1） char

char为定长字段，可存储长度为0～255，当sql_mode为STRICT_TRANS_TABLES时，N大于255时直接返回报错

sansi@mysql 15:46:  [db1]> create table t1 (c1 char(256));
ERROR 1074 (42000): Column length too big for column 'c1' (max = 255); use BLOB or TEXT instead

2） varchar

varchar为可变长字段，可存储的长度为0～65535字节，N具体大小限制需要根据列字符集格式进行判断:lantin1=1字节，utf8=3字节,utf8mb4=4字节。

65535/3=21845
sansi@mysql 15:46:  [db1]> create table t1 (c1 varchar(65535) not null) character set utf8;
ERROR 1074 (42000): Column length too big for column 'c1' (max = 21845); use BLOB or TEXT instead   
65535/4=16383
sansi@mysql 15:47:  [db1]> create table t1 (c1 varchar(65535) not null) character set utf8mb4;
ERROR 1074 (42000): Column length too big for column 'c1' (max = 16383); use BLOB or TEXT instead
65533 + 2
sansi@mysql 15:47:  [db1]> create table t1 (c1 varchar(65533) not null) character set latin1;
Query OK, 0 rows affected (0.02 sec)

3.2、列总数限制

MySQL表中所有的列总和不能超过4096，当然一些别的条件的限制下，表的列总数一般不会达到该限制条件

1）受到表的最大row size的限制

2）存储引擎的限制

3）每个表的.frm文件的限制

3.3、row size限制

1）MySQL默认一个表的row size最大为65535,即使存储引擎支持更大的row size。

sansi@mysql 15:49:  [db1]> 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

sansi@mysql 15:49:  [db1]> 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

2）bolb和text列仅占row size的9～12 bytes，因为blob和text使用off-page进行存储。对于Innodb存储引擎来讲，可变长列的存储超过767 bytes是利用溢出页来存储的。

sansi@mysql 15:49:  [db1]>  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.01 sec)

sansi@mysql 15:53:  [db1]>  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.03 sec)

对于可变长列的存储，Innodb除了行记录的存储外，还需要额外的空间来存储该行记录的实际大小。767 bytes以下只需一个字节，767 bytes以上需要两个字节来存储。

1、c1列 65535 + 2 = 65537 > 65535    执行报错
sansi@mysql 15:59:  [db1]> CREATE TABLE t1
    -> (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

2、c1列 65533 + 2 = 65535 <= 65535    执行成功
sansi@mysql 15:59:  [db1]> CREATE TABLE t1
    -> (c1 VARCHAR(65533) NOT NULL)
    -> ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.03 sec)
上一篇 : ：北京大学2016年高等代数与解析几何考研试题下一篇 : 正确理解Left join

相关阅读

推荐文章

网站首页网站地图Copyright © 2021-2022 且购网

免责声明：本站文章部分来源于互联网,如有侵权请联系站长,我们将在72小时内删除。