MySQL前缀索引上限案例分析

一、案例分享

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前缀索引上的一些限制

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 |