MySQL 表字符集不同导致索引失效问题

开发反馈sql表关联字段无法使用索引，加上hint之后还是不走，想知道原因。记录一下排查思路和遇到的问题。

一、 排查过程

explain
select t1.*
from t6
    inner join t2  on t6.id = t2.product_id
    inner join t1 FORCE INDEX(notice_desk_batch_code_ndp_id) on t1.ndp_id  = t2.id
    left join t3 on t2.od_id = t3.id
    left join t4 on t3.createdby = t4.id
    left join t5 on t5.line_id = t1.erp_line_id
where
  t5.id is null
  and t2.product_form = 1
  and t2.care_auto_insure = 0
  and t6.category_id in (1784, 2308, 2491, 2489, 2492, 2490, 2510)
  and t2.deletedby = 0;

首先检查了下 t1.ndp_id 和 t2.id 字段确实有索引，选择率也很高。

show index from 表名;

看到这个where条件猜了下会不会是返回数据量太大，检查t1表有9万行数据，查询返回结果只有60行，可以排除。

检查t1.ndp_id 和 t2.id 字段数据类型，发现都是varchar，不存在隐式转换问题。

检查t1和t2表字符集，如果字符集不同，转字符集要在字段上用cast函数，索引也会失效。

show create table 表名;

t1表

t2表

发现字符集确实是不一样的，其实认真看前面的执行计划，你会发现t2的ref部分是func。

根据官方文档的解释，func意思是该值是一个函数的返回结果。如果你没有显式用函数，很可能是MySQL有隐式的调用。5.7 可以通过show warnings查看是什么函数（5.6试了下不行）。

If the value is func, the value used is the result of some function. To see which function, use SHOW WARNINGS following EXPLAIN to see the extended EXPLAIN output. The function might actually be an operator such as an arithmetic operator.

看到这里，顺便检查了下其他表的字符集，结果如下：

可以看到t2和t6，t3和t4字符集也不相同，但为什么它们能走索引？因为关联字段是int类型的，与字符集无关。

二、 解决方法

既然知道了是字符集的问题，解决方法就是找时间重建表，调整字符集。由于业务新表均使用utf8mb4字符集，我们需要把t1表从utf8转为utf8mb4，对于5.6版本，这里还有个问题，5.6单个索引最长只支持到767 bytes。t1.ndp_id字段类型是varchar(255)，对于utf8，每个字符占3 bytes，没有超过最大限制，但是utf8mb4每个字符占4 bytes，4*255明显就超了767 bytes，因此转换会遇到以下报错。

这个报错跟 innodb_large_prefix 参数有关，设置为off时（5.6默认），索引最大长度为767 bytes；设置为on时（5.7默认），索引最大长度为3072 bytes。另外该参数在新版本已经是个过时的参数，未来有可能移除。

When this option is enabled, index key prefixes longer than 767 bytes (up to 3072 bytes) are allowed for InnoDB tables that use DYNAMIC or COMPRESSED row format.

所以解决方法有两个：

• 如果可以，改短ndp_id字段后再重建表修改字符集，导入数据
• 修改innodb_large_prefix参数后再重建表修改字符集，导入数据

修改方法：在/etc/my.cnf设置innodb_large_prefix=on（重启生效），如果不想重启，可以在命令行设置 set  global innodb_large_prefix=on;（重启失效），两者结合保证停机时间最短。

参考

MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format

MySQL :: MySQL 5.7 Reference Manual :: 8.8.3 Extended EXPLAIN Output Format

MySQL :: MySQL 5.7 Reference Manual :: 14.15 InnoDB Startup Options and System Variables

关于InnoDB索引长度限制的问题 - python-yzw - OSCHINA - 中文开源技术交流社区