隐式字符集转换导致索引失效问题怎么避免和解决-群英

上一篇：数据库服务器如何工作的，常见问题有哪些下一篇：数据库出现可疑什么原因，该怎样处理

这篇文章主要讲解了“隐式字符集转换导致索引失效问题怎么避免和解决”，文中的讲解内容简单、清晰、详细，对大家学习或是工作可能会有一定的帮助，希望大家阅读完这篇文章能有所收获。下面就请大家跟着小编的思路一起来学习一下吧。

在索引优化时，经常会看到的一句话：如果索引字段出现隐式字符集转换的话，那么索引将失效，进而转为全表扫描，查询效率将大大降低，要避免出现隐式字符集转换；

在此我想问问同学们：

大家知道为什么隐式字符集转换会导致索引失效吗？

实际场景中有没有遇到过隐式字符集转换导致索引失效的场景，具体排查的过程；

本文主线：

由上面的两个问题牵引出了本文的主线；

简单描述下隐式字符集转换导致索引失效的原因

然后模拟实际场景排查隐式字符集转换导致索引失效的过程

隐式字符集转换导致索引失效的原因

MySQL索引的数据结构是 B+Tree，想要走索引查询必须要满足其最左前缀原则，否则无法通过索引树进行查找，只能进行全表扫描；

例如：下面的这个SQL由于在索引字段上使用函数进行运算，导致索引失效

se le ct *
f r o m t_user W H E RE SU BS TR(name, 1, 2) = '李彤'

上面的这个SQL怎么改造才能使索引生效呢？如下所示：

se le ct * f r o m t_user W H E RE name like '李彤%'

通过上面的小例子可以知道，如果在索引字段上使用函数运算，则会导致索引失效，而索引字段的隐式字符集转换由于MySQL会自动的在索引字段上加上转换函数，进而会导致索引失效；

那接下来我们就通过模拟的实际场景来具体看看是不是由于MySQL自动给加上了转换函数而导致索引失效的；

模拟场景 + 问题排查

由于导致索引失效的原因有很多，如果自己写的SQL怎么看都没问题，但是通过查看执行计划发现就是没有走索引查询，此时就会让很多人陷入困境，这到底是怎么导致的呢？

此时本文重点将要讲述的工具就要闪亮登场啦： expl ain EX TE ND ED + show warnings ；

使用这个工具可以将执行的SQL语句的一些扩展信息展示出来，这些扩展信息就包括：MySQL优化时可能会添加上字符集转换函数，使得字符集不匹配的SQL可以正确执行下去；

下面就来具体聊聊 expl ain EX TE ND ED + show warnings 的使用；

模拟隐式字符集转换的场景：

首先创建两个字符集不一样的表：

然后使用存储过程构造数据：

注意：在构造数据时，记得将 t_employees 表中的 de_no 字段值构造的离散些，因为如果索引字段值的区分度很低的话，那么MyQSL优化器通过采样统计分析时，发现索引查询和全表扫描性能差不多，就会直接进行全表扫描了；

索引失效的查询SQL语句：

将表和数据构造完后，我们使用SQL语句进行查询下，然后再看看其执行计划；

expl ain se le ct * f r o m t_department a LE FT JO IN t_employees b on a.de_no = b.de_no W H E RE a.id = 16

其执行计划如下：

发现 t_employees 表中的 de_no 字段有索引，但是没有走索引查询，type=ALL 走的全表扫描，但是通过查看SQL语句发现其没有问题呀，表面看上去都是满足走索引查询的条件呀，排查到这发现遇到了困境，苦恼啊！

还好，通过在网络世界上遨游，最终发现了 expl ain EX TE ND ED + show warnings 利器，利用它快速发现了索引失效的根本原因，然后快速找到了解决方案；

下面就来聊聊这个利器的具体使用，开森！

使用利器快速排查问题：

注意：expl ain 后面跟的关键字 EX TE ND ED（扩展信息）在MySQL5.7及之后的版本中废弃了，但是该语法仍被识别为向后兼容，所以在5.7版本及后续版本中，可以不用在 expl ain 后面添加 EX TE ND ED 了；

具体使用方法如下：

①、首先在MySQL的可视化工具中打开一个命令列介面：工具 --> 命令列介面

②、然后输入下面的SQL并按回车：

expl ain EX TE ND ED se le ct * f r o m t_department a LE FT JO IN t_employees b on a.de_no = b.de_no W H E RE a.id = 4019;

③、然后紧接着输入命令 show warnings; 并回车，会出现如下图所示内容：

通过展示出的执行SQL扩展信息，发现MySQL在字符集不一致时自动添加上字符集转换函数，因为是在索引字段 de_no 上添加的转换函数，所以就导致了索引失效；

而如果我们没看扩展信息的话，那么可能直到我们查看表结构的时候才会发现是由于字符集不一致导致的，这样就会花费很多的时间；

扩展：隐式类型转换

咱们聊完上面的隐式字符集转换导致索引失效的情况，再来简单聊聊另一种隐式类型转换导致索引失效的情况；

隐式类型转换：简单的说就是字段的类型与其赋值的类型不一致时会进行隐式的转换；

小例如下：

se le ct * f r o m t_employees W H E RE em_name = 123;

上面的SQL中 em_name 为索引字段，字段类型是 varchar，为其赋 int 类型的值时，会发现索引失效，这里也可以通过 expl ain EX TE ND ED + show warnings 查看

以上就是关于“隐式字符集转换导致索引失效问题怎么避免和解决”的介绍了，感谢各位的阅读，希望这篇文章能帮助大家解决问题。如果想要了解更多知识，欢迎关注群英网络，小编每天都会为大家更新不同的知识。

标签：隐式字符集转换

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：mmqy2019@163.com进行举报，并提供相关证据，查实之后，将立刻删除涉嫌侵权内容。

上一篇：数据库服务器如何工作的，常见问题有哪些下一篇：数据库出现可疑什么原因，该怎样处理