MySQL InnoDB引擎索引长度受限怎么办

mysql>CREATE TABLE `tb` (

-> `a` varchar(255) DEFAULT NULL,

-> `b` varchar(255) DEFAULT NULL,

-> `c` varchar(255) DEFAULT NULL,

-> `d` varchar(255) DEFAULT NULL,

-> `e` varchar(255) DEFAULT NULL,

-> KEY `a` (`a`,`b`,`c`,`d`,`e`)

->) ENGINE=InnoDB DEFAULT CHARSET=utf8

ERROR 1071 (42000): Specified key was too longmax key length is 3072 bytes

可以看到，由于每个字段占用255*3, 因此这个索引的大小是3825>3072，报错。

为什么3072

我们知道InnoDB一个page的默认大小是16k。由于是Btree组织，要求叶子节点上一个page至少要包含两条记录（否则就退化链表了）。

所以一个记录最多不能超过8k。

又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过4k （极端情况，pk和某个二级索引都达到这个限制）。

由于需要预留和辅助空间，扣掉后不能超过3500，取个“整数”就是(1024*3)。

单列索引限制

上面有提到单列索引限制767，起因是256×3-1。这个3是字符最大占用空间（utf8）。但是在5.5以后，开始支持4个字节的uutf8。255×4>767, 于是增加了一个参数叫做 innodb_large_prefix。

这个参数默认值是OFF。当改为ON时，允许列索引最大达到3072。

可以看到默认行为是建表成功，报一个warning，并且将长度阶段为255。

注意要生效需要加row_format=compressed或者dynamic 。

如果确实需要在单个很大的列上创建索引，或者需要在多个很大的列上创建联合索引，而又超过了索引的长度限制，解决办法是在建索引时限制索引prefix的大小：

例如：create index yarn_app_result_i4 on yarn_app_result (flow_exec_id(100), another_column(50))

这样，在创建索引时就会限制使用的每个列的最大长度。如上的例子中，在创建联合索引时，最多使用列flow_exec_id中前100个字符创建索引，最多使用another_column中前

50个字符创建索引。这样子，就可以避免索引长度过大的问题。

最后，我想说一句。我们在设计数据库时，最好不要在一个可能包含很长字符串的列上创建索引，尤其是当这个列中的字符串都很长时。如果在这类列上创建了索引，那么在创建索引时以及根据索引查询时，都会浪费很多时间在计算和存储上。有经验的设计人员应该不会这样设计数据库。

1. 执行计划中明明有使用到索引，为什么执行还是这么慢？

2. 执行计划中显示扫描行数为 644，为什么 slow log 中显示 100 多万行？

a. 我们先看执行计划，选择的索引 “INDX_BIOM_ELOCK_TASK3(TASK_ID)”。结合 sql 来看，因为有 "ORDER BY TASK_ID DESC" 子句，排序通常很慢，如果使用了文件排序性能会更差，优化器选择这个索引避免了排序。

那为什么不选 possible_keys:INDX_BIOM_ELOCK_TASK 呢？原因也很简单，TASK_DATE 字段区分度太低了，走这个索引需要扫描的行数很大，而且还要进行额外的排序，优化器综合判断代价更大，所以就不选这个索引了。不过如果我们强制选择这个索引（用 force index 语法），会看到 SQL 执行速度更快少于 10s，那是因为优化器基于代价的原则并不等价于执行速度的快慢；

b. 再看执行计划中的 type:index，"index" 代表 “全索引扫描”，其实和全表扫描差不多，只是扫描的时候是按照索引次序进行而不是行，主要优点就是避免了排序，但是开销仍然非常大。

Extra:Using where 也意味着扫描完索引后还需要回表进行筛选。一般来说，得保证 type 至少达到 range 级别，最好能达到 ref。

在第 2 点中提到的“慢日志记录Rows_examined: 1161559，看起来是全表扫描”，这里更正为“全索引扫描”，扫描行数确实等于表的行数；

c. 关于执行计划中：“rows：644”，其实这个只是估算值，并不准确，我们分析慢 SQL 时判断准确的扫描行数应该以 slow log 中的 Rows_examined 为准。

4. 优化建议：添加组合索引 IDX_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID)

优化过程：

TASK_DATE 字段存在索引，但是选择度很低，优化器不会走这个索引，建议后续可以删除这个索引：

select count(*),count(distinct TASK_DATE) from T_BIOMA_ELOCK_TASK+------------+---------------------------+| count(*) | count(distinct TASK_DATE) |+------------+---------------------------+| 1161559 | 223 |+------------+---------------------------+

在这个 sql 中 REL_DEVID 字段从命名上看选择度较高，通过下面 sql 来检验确实如此：

select count(*),count(distinct REL_DEVID) from T_BIOMA_ELOCK_TASK+----------+---------------------------+| count(*) | count(distinct REL_DEVID) |+----------+---------------------------+| 1161559 | 62235 |+----------+---------------------------+

由于有排序，所以得把 task_id 也加入到新建的索引中，REL_DEVID,task_id 组合选择度 100%：

select count(*),count(distinct REL_DEVID,task_id) from T_BIOMA_ELOCK_TASK+----------+-----------------------------------+| count(*) | count(distinct REL_DEVID,task_id) |+----------+-----------------------------------+| 1161559 | 1161559 |+----------+-----------------------------------+

在测试环境添加 REL_DEVID，TASK_ID 组合索引，测试 sql 性能：alter table T_BIOMA_ELOCK_TASK add index idx_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID)

添加索引后执行计划：

这里还要注意一点“隐式转换”：REL_DEVID 字段数据类型为 varchar，需要在 sql 中加引号：AND T.REL_DEVID = 000000025xxx >>AND T.REL_DEVID = '000000025xxx'

执行时间从 10s+ 降到 毫秒级别：

1 row in set (0.00 sec)

结论

一个典型的 order by 查询的优化，添加更合适的索引可以避免性能问题：执行计划使用索引并不意味着就能执行快。

1. 数据库版本：阿里云RDS MySQL5.1

mysql>select @@version

+-------------------------------+

| @@version |

+-------------------------------+

| 5.1.61-Alibaba-rds-201404-log |

+-------------------------------+

1 row in set (0.00 sec)

2. 测试的表结构信息

mysql>show create table tb2\G

*************************** 1. row ***************************

Table: tb2

Create Table: CREATE TABLE `tb2` (

`a` varchar(255) DEFAULT NULL,

`b` varchar(255) DEFAULT NULL,

`c` varchar(255) DEFAULT NULL,

`d` varchar(1000) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

3. 测试加索引

（1）添加单列索引，能够添加成功（报出warning），但实际添加的是前缀索引。

mysql>alter table tb2 add index idx1 (d)

Query OK, 0 rows affected, 2 warnings (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>show warnings

+---------+------+----------------------------------------------------------+

| Level | Code | Message |

+---------+------+----------------------------------------------------------+

| Warning | 1071 | Specified key was too longmax key length is 1000 bytes |

| Warning | 1071 | Specified key was too longmax key length is 1000 bytes |

+---------+------+----------------------------------------------------------+

2 rows in set (0.00 sec)

表结构信息：

mysql>show create table tb2\G

*************************** 1. row ***************************

Table: tb2

Create Table: CREATE TABLE `tb2` (

`a` varchar(255) DEFAULT NULL,

`b` varchar(255) DEFAULT NULL,

`c` varchar(255) DEFAULT NULL,

`d` varchar(1000) DEFAULT NULL,

KEY `idx1` (`d`(333))

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

（2）添加组合索引，会执行失败。

mysql>alter table tb2 add index idx1 (a,b)

ERROR 1071 (42000): Specified key was too longmax key length is 1000 bytes

4. 分析

myisam存储引擎在创建索引的时候，索引键长度是有一个较为严格的长度限制的，所有索引键最大长度总和不能超过1000，而且不是实际数据长度的总和，而是索引键字段定义长度的总和。

主要字符集的计算方式：

latin1 = 1 byte = 1 character

uft8 = 3 byte = 1 character

gbk = 2 byte = 1 character

---