必须知道的SQL语句不走索引时的排查利器 - SegmentFault 思否

标签: | 发表时间:2021-07-09 19:49 | 作者:
出处:https://segmentfault.com

前言:

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

在此我想问问同学们:

  • 大家知道为什么隐式字符集转换会导致索引失效吗?
  • 实际场景中有没有遇到过隐式字符集转换导致索引失效的场景,具体排查的过程;

本文主线:

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

  • 简单描述下隐式字符集转换导致索引失效的原因
  • 然后模拟实际场景排查隐式字符集转换导致索引失效的过程

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

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


相关 [知道 sql 语句] 推荐:

sql语句总结

- zhaoloving - 博客园-首页原创精华区
1、创建数据库语句(以部门表(department)和员工表(employee)位例). --员工表 employee.   alter table [表名] add .     constraint [键名] Primary key [主键名称].   alter table [表名] add.      constraint [键名] unique(唯一键名称).

SQL语句大全

- - SQL - 编程语言 - ITeye博客
原文地址:http://www.cnblogs.com/yubinfeng/archive/2010/11/02/1867386.html. 3、说明:备份sql server. --- 创建 备份数据的 device. A:create table tab_new like tab_old (使用旧表创建新表).

sql经典语句

- - 数据库 - ITeye博客
3、说明:备份sql server. --- 创建 备份数据的 device. table tab_new like tab_old (使用旧表创建新表). DB2中列加上后数据类型也不能改变,唯一能改变的是增加. 注:索引是不可更改的,想更改必须删除重新建. 10、说明:几个简单的基本的sql语句.

sql语句优化

- - 数据库 - ITeye博客
性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化. 为了获得稳定的执行性能,SQL语句越简单越好. 对复杂的SQL语句,要设法对之进行简化. 1)不要有超过5个以上的表连接(JOIN). 2)考虑使用临时表或表变量存放中间结果.

Java中如何解析SQL语句、格式化SQL语句、生成SQL语句?

- - 程序猿DD
昨天在群里看到有小伙伴问,Java里如何解析SQL语句然后格式化SQL,是否有现成​类库可以使用. 之前TJ没有做过这类需求,所以去研究了一下,并找到了一个不过的解决方案,今天推荐给大家,如果您正要做类似内容,那就拿来试试,如果暂时没需求,就先了解收藏(技多不压身). JSqlParser是一个用Java编写的SQL解析器,可以将SQL语句解析为Java对象,从而使开发人员能够轻松地分析、修改和重构SQL查询.

SQL查询语句(oralce)(1)

- - ITeye博客
  主要用于创建删除数据库对象和维护数据对象的属性. 主要有三个主命令:CREATE .DROP. SQL>CREATE TABLE 表1 AS SELECT * FROM 表2;. SQL>DROP TABLE 表名;. SQL>--添加.修改.删除字段. SQL> ALTER TABLE 表名 ADD/MODIFY(字段 类型);.

必须知道的SQL语句不走索引时的排查利器 - SegmentFault 思否

- -
在索引优化时,经常会看到的一句话:如果索引字段出现隐式字符集转换的话,那么索引将失效,进而转为全表扫描,查询效率将大大降低,要避免出现隐式字符集转换;. 大家知道为什么隐式字符集转换会导致索引失效吗. 实际场景中有没有遇到过隐式字符集转换导致索引失效的场景,具体排查的过程;. 由上面的两个问题牵引出了本文的主线;.

(转)经典sql查询语句大全

- - SQL - 编程语言 - ITeye博客
3、说明:备份sql server. --- 创建 备份数据的 device. A:create table tab_new like tab_old (使用旧表创建新表). DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度. 7、说明:添加主键: Alter table tabname add primary key(col).

P6SPY 2.0 发布,SQL 语句拦截

- - 开源中国社区最新新闻
8年过去了,P6SPY 发布了全新稳定版本 2.0 ,P6Spy是一个可以用来在应用程序中拦截和修改数据操作语句的开源框架. 通过P6Spy我们可以对SQL语句进行拦截,相当于一个SQL语句的记录器,这样我们可以用它来作相关的分析,比如性能分析. 下载地址: https://github.com/p6spy/p6spy/wiki/Download.

如何写出高性能SQL语句

- - Oracle - 数据库 - ITeye博客
优化SQL查询:如何写出高性能SQL语句. 1、首先要搞明白什么叫执行计划. 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生欀如一条SQL语句如果用来从一个10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,如果该表进行了归档,当前只剩下5000条记录了,那查询优化器就会改变方案,采用“全表扫描”方式.