MySQL索引前缀长度超限怎么办？这种方法帮你搞定

MySQL索引前缀长度超限怎么办？这种方法帮你搞定

前言

在使用 MySQL 数据库时，创建索引时可能会遇到索引前缀长度超限的问题。本文将通过案例演示，分析常见的错误原因，并提供相应的解决方案。

1 案例演示

-- 查看数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.38 |
+-----------+
1 row in set (0.00 sec)
-- 建库
mysql> CREATE DATABASE trexdb;
Query OK, 1 row affected (0.00 sec)
-- 切库
mysql> use trexdb;
Database changed
-- 建表
mysql> CREATE TABLE test (
a INT,
b VARCHAR ( 512 ),
c VARCHAR ( 256 ),
d VARCHAR ( 128 ),
PRIMARY KEY ( a )
);
Query OK, 0 rows affected (0.05 sec)

-- 插入数据
-- 此处插入的数据都比较简单，仅用于演示
INSERT INTO test VALUES (1, '希可系统有限责任公司', '希腊', '资讯科技业');
INSERT INTO test VALUES (2, '凌冬将至滑雪股份有限责任公司', '法国', '旅游业');
INSERT INTO test VALUES (3, '叶问武术指导咨询有限责任公司', '阿塞拜疆', '咨询业');
INSERT INTO test VALUES (4, '向记永不过期食品股份有限责任公司', '乌拉圭', '饮食业');
INSERT INTO test VALUES (5, '云熙电子设计科技有限责任公司', '中国', '电子行业');
INSERT INTO test VALUES (6, '杰宏电脑有限责任公司', '洪都拉斯', '电子行业');
INSERT INTO test VALUES (7, '震南有限责任公司', '越南', '电子行业');
INSERT INTO test VALUES (8, '晓明有限责任公司', '几内亚比绍', '咨询业');
INSERT INTO test VALUES (9, '子涵有限责任公司', '布基纳法索', '');
INSERT INTO test VALUES (10, '子韬发展贸易股份有限责任公司', '沙特阿拉伯', '贸易行业');
INSERT INTO test VALUES (11, '尹贸易有限责任公司', '新西兰', '金融服务业');
INSERT INTO test VALUES (12, '龚記通讯有限责任公司', '索马里', '电讯');
INSERT INTO test VALUES (13, '潘記通讯有限责任公司', '玻利维亚', '电讯');
INSERT INTO test VALUES (14, '武有限责任公司', '日本', '饮食业');
INSERT INTO test VALUES (15, '谭玩有限责任公司', '柬埔寨', '制造业');
INSERT INTO test VALUES (16, '杰宏有限责任公司', '巴布亚新几内亚', '饮食业');
INSERT INTO test VALUES (17, '武汉华夏大秦精工编译贸易科技股份有限责任公司', '中国', '贸易行业');
INSERT INTO test VALUES (18, '华阳有限责任公司', '佐治亚州', '工业');
INSERT INTO test VALUES (19, '孙子房产物业代理有限责任公司', '巴西', '房地产业');
INSERT INTO test VALUES (20, '震南工程有限责任公司', '泰国', '工程业');

-- 创建索引时遇到如下错误
mysql> ALTER TABLE test ADD INDEX idx_b_c_d(b, c, d);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes


2 错误原因分析

• 对于使用 DYNAMIC 或 COMPRESSED 行格式的 InnoDB 表，索引键前缀长度限制为 3072 字节。

• 对于使用 REDUNDANT 或 COMPACT 行格式的 InnoDB 表，索引键前缀长度限制为 767 个字节。

如何查看表的存储引擎和行格式呢？

执行如下命令，可看到test表的存储引擎是InnoDB，行格式是DYNAMIC。

mysql> SHOW TABLE STATUS LIKE 'test';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| test | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2024-07-12 06:51:04 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.01 sec)


mysql> SHOW CREATE TABLE test;
+-------+---------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------+
| test | CREATE TABLE test (
a int NOT NULL,
b varchar(128) DEFAULT NULL,
c varchar(256) DEFAULT NULL,
d varchar(512) DEFAULT NULL,
PRIMARY KEY (a)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------+
1 row in set (0.00 sec)


其实，在MySQL8.0中，默认的存储引擎是InnoDB，默认的行格式是DYNAMIC，默认的字符集是 utf8mb4，默认的排序规则是 utf8mb4_0900_ai_ci.

创建索引时涉及到的列分别是 b,c,d，对于 utf8mb4 字符集，每个字符最大占用4个字节，这些长度合计大小如下：

（512+ 256 + 128）* 4 = 3584 字节

3584 字节超过了 InnoDB 允许的最大索引长度 3072 字节，因此会导致错误。

简而言是，无论如何都不应该为如此长的 VARCHAR 列创建索引，因为索引将非常笨重且效率低下。

3 解决方案

3.1 方法一，缩小索引前缀长度

最佳做法是使用前缀索引，以便仅索引数据的左侧子字符串。

在定义索引时，可以声明每列的前缀长度。

mysql> ALTER TABLE test ADD INDEX idx_b_c_d(b(30), c(10), d(10));
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0


实际上，大部分数据都少于 128 个字符。

但是，给定列的最佳前缀长度是多少？这里有一种方法可以参考一下。下面以字段b为例：

mysql> SELECT
-> ROUND(SUM(CHAR_LENGTH(b) ROUND(SUM(CHAR_LENGTH(b) ROUND(SUM(CHAR_LENGTH(b) ROUND(SUM(CHAR_LENGTH(b) ROUND(SUM(CHAR_LENGTH(b) FROM test;


可以看到95%的字符串少于 20 个字符，并且所有字符串都少于 30 个字符。因此，没有必要索引超过 30 个字符的前缀长度，当然更不需要索引 128 个字符的全长。

3.2 方法二，修改字段定义的长度

比如，在评估业务需求和数据特性后，了解到业务上写入字符的上限如下：

• 字段b： 30 字符
• 字段c： 10 字符
• 字段d： 10 字符

那么，就可以通过如下方式来处理

-- 修改字段长度
ALTER TABLE test
MODIFY COLUMN b varchar(30),
MODIFY COLUMN c varchar(10),
MODIFY COLUMN d varchar(10);
-- 增加索引
ALTER TABLE trexdb.test ADD INDEX idx_b_c_d(b, c, d);


3.3 方法三，使用占用较小的字符集

如果字符集中的字符实际不需要 utf8mb4（例如，它们是 ASCII 字符），你可以考虑将字符集改为 utf8，这样每个字符最大只占用 3 个字节。

（128 + 256 + 512）* 3 = 2388 字节 < 3072 字节。

这种方式不推荐。此处就不写例子了，因为混合字符集数据库不仅在使用关联查询时会导致问题，还会将数据置于非常规格式，并且后期难以更正。

4 总结

在表设计阶段应该结合业务特性，考虑字段长度以及索引前缀长度上限的问题。前期没有做好规划和评审，迟早是一个隐患，给生产环境带来不必要的风险。如果已经遇到 MySQL 索引前缀长度超限的问题，推荐采用方法一，这种方法对表的侵入性小，在很多情况下可以显著减少索引的长度，同时仍然保持索引的有效性。