关于mysql:必须知道的SQL语句不走索引时的排查利器

前言：

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

在此我想问问同学们：

• 大家晓得为什么隐式字符集转换会导致索引生效吗？
• 理论场景中有没有遇到过隐式字符集转换导致索引生效的场景，具体排查的过程；

本文主线：

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

• 简略形容下隐式字符集转换导致索引生效的起因
• 而后模仿理论场景排查隐式字符集转换导致索引生效的过程

隐式字符集转换导致索引生效的起因

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

例如：上面的这个 SQL 因为在 索引字段 上应用函数进行运算，导致索引生效

select * from t_user where SUBSTR(name, 1, 2) = '李彤'

下面的这个 SQL 怎么革新能力使索引失效呢？如下所示：

select * from t_user where name like '李彤 %'

通过下面的小例子能够晓得，如果在索引字段上应用函数运算，则会导致索引生效，而索引字段的 隐式字符集转换 因为 MySQL 会主动的在索引字段上加上 转换函数，进而会导致索引生效；

那接下来咱们就通过模仿的理论场景来具体看看是不是因为 MySQL 主动给加上了转换函数而导致索引生效的；

模仿场景 + 问题排查

因为导致索引生效的起因有很多，如果本人写的 SQL 怎么看都没问题，然而通过查看执行打算发现就是没有走索引查问，此时就会让很多人陷入困境，这到底是怎么导致的呢？

此时本文重点将要讲述的工具就要闪亮退场啦：explain extended + show warnings；

应用这个工具能够将执行的 SQL 语句的一些扩大信息展现进去，这些扩大信息就包含：MySQL 优化时可能会增加上字符集转换函数，使得字符集不匹配的 SQL 能够正确执行上来；

上面就来具体聊聊 explain extended + show warnings 的应用；

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

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

CREATE TABLE `t_department` (`id` int(11) NOT NULL AUTO_INCREMENT,
  `de_no` varchar(32) NOT NULL,
  `info` varchar(200) DEFAULT NULL,
  `de_name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_de_no` (`de_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;


CREATE TABLE `t_employees` (`id` int(11) NOT NULL AUTO_INCREMENT,
  `em_no` varchar(32) NOT NULL,
  `de_no` varchar(32) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `info` varchar(200) DEFAULT NULL,
  `em_name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_em_no` (`de_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

而后应用存储过程结构数据：

# 如果存储过程存在则删除 
DROP PROCEDURE IF EXISTS proc_initData;

DELIMITER $
# 创立存储过程
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT  DEFAULT 1;
    WHILE i<=30 DO
        # 新增数据
        INSERT INTO t_employees (em_no, de_no, info, em_name , age) VALUES (CONCAT('001', i), '003', 'test11', 'test2', i ); #执行的 sql 语句
        SET i = i+1;
    END WHILE;
END $

# 调用存储过程
CALL proc_initData();

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

索引生效的查问 SQL 语句：

将表和数据结构完后，咱们应用 SQL 语句进行查问下，而后再看看其执行打算；

explain 
select * from t_department a LEFT JOIN  t_employees b on a.de_no = b.de_no where a.id = 16

其执行打算如下：

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

还好，通过在网络世界上漫游，最终发现了 explain extended + show warnings 利器，利用它疾速发现了索引生效的根本原因，而后疾速找到了解决方案；

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

应用利器疾速排查问题：

留神：explain 前面跟的关键字 EXTENDED（扩大信息）在 MySQL5.7 及之后的版本中废除了，然而该语法仍被辨认为向后兼容，所以在 5.7 版本及后续版本中，能够不必在 explain 前面增加 EXTENDED 了；

EXTENDED 关键字的具体查阅材料：https://dev.mysql.com/doc/ref…

具体应用办法如下：

①、首先在 MySQL 的可视化工具中关上一个 命令列介面：工具 –> 命令列介面

②、而后输出上面的 SQL 并按回车：

explain EXTENDED
select * from t_department a LEFT JOIN  t_employees b on a.de_no = b.de_no where a.id = 4019;

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

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

而如果咱们没看扩大信息的话，那么可能直到咱们查看表构造的时候才会发现是因为字符集不统一导致的，这样就会破费很多的工夫；

扩大：隐式类型转换

咱们聊完下面的隐式字符集转换导致索引生效的状况，再来简略聊聊另一种 隐式类型转换 导致索引生效的状况；

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

小例如下：

select * from t_employees where em_name = 123;

下面的 SQL 中 em_name 为索引字段，字段类型是 varchar，为其赋 int 类型的值时，会发现索引生效，这里也能够通过 explain extended + show warnings 查看，会发现如下图所示内容：

至此本文进入结尾，在此再阐明下，上文中测试时应用的 MySQL 版本都是 5.7；

❤ 点赞 + 评论 + 转发 哟

如果本文对您有帮忙的话，请挥动下您爱发财的小手点下赞呀，您的反对就是我一直创作的能源，谢谢啦！

您能够微信搜寻 【木子雷】 公众号，大量 Java 学习干货文章，您能够来瞧一瞧哟！