rds for mysql添加索引时报错specified key was too long; max key length is 767 bytes-j9九游会登录

场景描述

创建表或者添加索引时，当索引列长度超出限制报error 1071或error 1709错误。

error 1071: specified key was too long; max key length is 767 bytes

error 1709 (hy000): index column size too large. the maximum column size is 767 bytes

原因分析

mysql索引的最大长度受多方面限制，包括：

• 版本限制
  • rds mysql 5.6：参数“innodb_large_prefix”默认值为off，最大索引长度767字节。
  • rds mysql 5.7：参数“innodb_large_prefix”默认值为on，最大索引长度3072字节。
  • rds mysql 8.0：大索引功能为内置标准，无“innodb_large_prefix”参数，默认支持3072字节索引。
• 行格式（row_format）
  • compact和redundant：最大索引长度767字节。
  • dynamic和compressed：最大索引长度3072字节。
• 字符集

  多字节字符集会导致索引占用更多空间，容易超出限制。

j9九游会登录的解决方案

1. 在rds界面查看mysql引擎版本，如果是5.6、5.7版本，确保参数“innodb_large_prefix”值为on。

   更多操作，请参见修改rds for mysql实例参数。

2. 查看表的行格式，确保行格式为dynamic。
   • 对于已存在的表，确认表的row_format。

     select row_format from information_schema.tables where table_name = 'table_name';

     如果表的行格式不是dynamic，需要先重建表为dynamic格式后再添加索引。

     alter table `table_name` row_format=dynamic;

     

     alter table ... row_format=dynamic;会重构整个表数据，对于大表，它将消耗大量i/o资源和时间并可能锁表。请务必在业务低峰期或维护时间窗执行，并在执行前做好数据备份。

   • 对于新建的表，确认“innodb_default_row_format”参数是否为dynamic。
     如果不是，则需要在建表时指定row_format为dynamic。

     create table `new_table` ( 
       -- ... 表结构定义 ...
     ) engine=innodb row_format=dynamic;