InnoDB表多字段添加索引失败：提示键长度超出限制

问题场景 #

执行以下查询与索引创建语句：

select
    *
from
    A
where
    a = 1755
    and
    b = 11
    and
    c = 50
    and
    d = 11
    and
    response != '';

create index idx_test on A (a, b, c, d, response );

触发错误：

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

涉及表结构：

DROP TABLE IF EXISTS A;

CREATE TABLE A (
    id       int unsigned           NOT NULL AUTO_INCREMENT,
    a        int unsigned           NOT NULL,
    b        int unsigned  DEFAULT      NULL,
    c        int unsigned           NOT NULL,
    d        int unsigned           NOT NULL,
    response varchar(5000) DEFAULT      NULL,

    PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

原因分析 #

InnoDB默认单索引键的总长度上限为3072字节。当前表使用latin1字符集，每个字符占1字节：

• 四个int类型字段a,b,c,d各占4字节，总计16字节
• response是varchar(5000)，最多占5000字节
  两者总长度5016字节，远超3072字节限制，导致创建索引失败。

解决方案 #

方案1：仅创建核心过滤字段索引 #

去掉索引中的response字段，仅保留等值匹配的a,b,c,d：

create index idx_test on A (a, b, c, d);

该索引总长度仅16字节，完全符合限制。查询时通过索引快速定位符合a,b,c,d条件的行，再过滤response != ''的记录，仅需少量回表操作，适合对性能要求不是极致的场景。

方案2：对response使用前缀索引 #

如果希望保留索引覆盖（避免回表），可截取response的前缀部分加入索引，控制总长度在限制内：

create index idx_test on A (a, b, c, d, response(3000));

取response前3000字节后，总长度为16+3000=3016字节，符合限制。对于response != ''的过滤逻辑，前缀非空即可判定原字段非空，完全满足查询需求。

方案3：缩短response字段长度（业务允许时） #

如果业务场景不需要5000字符的长度，可修改字段长度使索引总长度合规：

ALTER TABLE A MODIFY COLUMN response varchar(3000) DEFAULT NULL;
create index idx_test on A (a, b, c, d, response);

修改前需确认现有数据不会被截断，避免业务影响。

方案4：启用大索引前缀支持（MySQL 5.7+） #

在MySQL 5.7及以上版本，可通过开启参数扩展索引长度上限至7670字节（需配合DYNAMIC/COMPRESSED行格式）：

1. 修改全局参数：

SET GLOBAL innodb_large_prefix = ON;
SET GLOBAL innodb_file_per_table = ON;
SET GLOBAL innodb_file_format = Barracuda;

2. 修改表的行格式：

ALTER TABLE A ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

3. 创建原索引：

create index idx_test on A (a, b, c, d, response);

MySQL 8.0及以上版本默认开启该参数，且行格式默认是DYNAMIC，只需执行修改表行格式的步骤即可。

内容的提问来源于stack exchange，提问作者anil