一次MySQL索引失效引发的思考

最近公司做了一个千万数量级的项目，由于要求性能比较高，每一个相对慢的查询都需要优化，项目经理是一个比较有经验的开发人员，基本上遇到问题都会先自行处理：或自己分析原因或网络寻求帮助。

但是项目平稳运行一个多月后，他突然说有个SQL。怎么也不走索引，索引怎么建都不走，他查找了网络上可以找到的原因，都无法解决，最后找到我这个兼职dba。

我接到问题也是一脸懵逼，MySQL数据库，是一个表连接的SQL，查询条件列有索引，执行计划中显示索引不使用，强制使用索引也无效。多方面研究发现现象是，单表查询索引列判断等于某个字符串的查询可以使用索引，但是和其他表连接就不使用索引，最后判断是排序规则错误。

大部分索引失效原因是索引列被显式计算或隐式类型转换，其中隐式类型转换包括排序规则冲突，所谓排序规则请自行网络了解，最后解决方案是统一列的排序规则。

总结：

问题原因：排序规则主要针对字符串列，创建表时，列不指定默认表的排序规则，表不指定默认库的排序规则，所以如果建表时，列的排序规则没有指定，则使用逻辑上级的排序规则，一般都是在建库时指定排序规则，如utf8mb4_general_ci或MySQL8默认的utf8mb4_0900_ai_ci。

例如CREATE SCHEMA `库名` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

如果建表时列指定了排序规则，以指定的为准。这种东西一般不会人为指定，多数情况，是在别的项目中复制的表结构，因原库和新库的排序规则不同，原排序规则被带过来了。当表连接时，使用字符串类型的列作为表连接键时，排序规则不同引起隐式转换，导致索引失效。当使用单表查询索引列判断等于某个字符串例如【列名='abc'】时不需要统一排序规则，不会引发隐式转换，无法排查出此问题

解决方案：统一排序规则

项目上线之前执行以下SQL，会查询出指定库下的所有列的排序规则和字符集，一定要统一后，再上线

select table_name,column_name,character_set_name,collation_name
from information_schema.columns where table_schema = '库名' and data_type = 'varchar'

相关信息请查看MySQL官网对排序规则的描述

巴特西

一次MySQL索引失效引发的思考

最新文章

热门文章