专栏首页洁癖是一只狗Mysql索引-不会使用索引的场景

Mysql索引-不会使用索引的场景

在日常开发中,我们经常会发现,Mysql中一些逻辑上一样的sql,往往性能差异很大,至于为什么会发生这样的问题,今天我们就看看几个常见的案例

案例一:条件字段函数操作

假设我们有一张表如下建表语句

mysql> CREATE TABLE `tradelog` (
`id` int(11) NOT NULL,
`tradeid` varchar(32) DEFAULT NULL,
`operator` int(11) DEFAULT NULL,
`t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

现在我们的需求就是获取年份中7月份的数据,逻辑上并不复杂,我们也回想很快想到下面语句

select count(*)  from tradelog where month(t_modified)=7

但是我们发现在数据量大的时候,这条语句返回的时间非常长,但是我们的确建立了t_modified的索引,为什么还是这么慢呢

此时我们可以看看t_modified的索引树,如下图

上面的数字是函数计算的值,此时我们发现,如果我们使用下面语句会快速的根据上图的绿色箭头找到对应的值

select *  from  tradelog where t_modified='2018-7-1'

实际上,B+树提供了快速定位的能力,来源于同一层节点的有序性。

但是,如果计算month()函数的话,你会看到传入7的时候,在树的第一层就不知道该怎么办了,也就是说,对索引做函数操作,会破坏索引值的有序性,因此优化器就决定放弃走树的搜索。

但事实上,优化器并没有放弃使用索引,但是优化器可以遍历索引,可以选择主键索引和t_modified字段索引,优化器发现t_modified索引树比较小,最终还是选择了字段索引,

可以看到extra的值是Using index,说明使用了覆盖索引。虽然使用了索引,但是也是全索引扫描。

此时我们可以使用下面语句,实现我们需要的结果,下面语句也用上了索引的快速定位能力,但是比较繁琐

mysql> select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

总结:发现如果我们对字段使用函数,会破坏索引的有序性,是无法使用索引快速定位的功能,而只能全索引扫描,需要注意的是,函数没有破坏索引的有序性,优化器也不会考虑使用索引的快速定位能力的,

案例二:隐式类型转换

我们先看看下面语句

mysql> select * from tradelog where tradeid=110717;

我们发现上面虽然我们的tradeid有索引,但是他还是会走全表扫描,这个又是为什么呢,

其实我们发现tradeid他的类型是varchar(32),而输入的参数值确实int,索引要做类型转换。在mysql中,字符串和数字做比较的话,是将字符串转换成数字.

上面的语句实际上在优化器里面就如下面语句

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

从上一个案例我们知道使用了函数是不会走索引的,优化器会放弃索引的快速定位能力。

案例三:隐式字符编码转换

我们建立下面表,且插入一些数据

mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步骤*/
  `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());

insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

我们在执行下面插叙语句

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/

看看是否使用了索引

我们发现第一行使用索引,这个索引是建立在主键索引上的,并且扫描了一行,但是第二行,我们发现没有使用索引,进行了全表扫描。

在这个执行计划中,是从tradelog表中取tradeid字段,在到trade_detail表查询匹配字段,因此我们称tradeLog叫驱动表,而trade_detail为被驱动表.把tradeid称为关联字段

他的具体执行步骤如下

  1. 根据id在tradelog表找到一行
  2. 获取到tradeid字段的值
  3. 在根据tradeid的值到trade_detail表中匹配符合的行,而在explain中第二行我们发现key=null,说明是按照遍历主键索引的方式,一个个判断tradeid的值是否符合

正常按照我们的理解,第二行的tradeid它也是有索引的,应该也是使用索引才对,为什么没有使用索引的

其实第三步,相当是下面语句

mysql> select * from trade_detail where tradeid=$L2.tradeid.value;

其中,$L2.tradeid.value的字符集是utf8mb4。

此时我们可以想到字符集uft8mb4是uft8的超集,索引当两个字段比较的时候,会把utf8装成utf8mb4字符集,再做比较.

因此在执行上面这个语句的时候,需要被驱动表的字段一个个转换成uft8mb4,再去比较。

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 

此时我们知道,如果字段使用了函数,将不会使用索引快速定位能力,这里我们就明白了,为什么被驱动表会全表扫描

作为对比我们可以下面语句看看效果

mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

我们发现为什么执行计划里面的第二句使用了索引呢,我们来分析一下

依然像上面分析的一样,我们看到下面语句

select operator from tradelog  where traideid =$R4.tradeid.value; 

然后我们发现$R4.tradeid.value的字符集是utf8,此时mysql会把这个字符转成utf8mb4,因此变成了下面语句

select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4); 

这里看到我们使用的函数在使用在了参数上,因此此时是可以使用traideid索引。

说到了这里,我们看看如何对我们开始的sql进行优化,两种方案

  • 直接把被驱动表的tradeid的字段字符改成uft8mb4
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null   2.
  • 如果数据量比较大的,可以使用下面语句,主动在驱动表转成utf8
mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 

总结:上面三个案例其实是一件事,就是对索引字段函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索能力.

本文分享自微信公众号 - 洁癖是一只狗(rookie-dog),作者:洁癖汪

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-11-02

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Mysql索引解密(下)

    上面ID索引树进行查找记录的过程叫回表,可以看出k树索引树进行了三次查询,Id索引树进行了两次查询。查询数据过程中是否可以避免回表查询呢,

    小土豆Yuki
  • 【红帽认证系列笔记之RHCE】RHCE 认证考试题库(上)

    RHCE的考试相对RHCSA来说要难很多,主要考网络配置,各种文件系统的挂载,web的配置,防火墙策略,数据库查找,脚本编写等等。一共20道题左右。rhce考试...

    小土豆Yuki
  • Mysql如何给字符串添加索引(前缀索引)

    在日常开发中,我们经常给字符串添加索引,那么给字段添加索引有什么技巧吗,我们看看下面的例子,我们给一个邮箱添加索引,应该如何添加呢

    小土豆Yuki
  • 远程协助解决重建索引的危机问题 (r8笔记第80天)

    最近在工作忙碌之余也帮几位网友查看了几个问题,有一个问题让我印象挺深,其实也可以分享出来作为一些参考,问题之外还是有一些值得借鉴的地方。 首先是在周末的一...

    jeanron100
  • 听说Mysql你很豪横?-------------深入解析mysql数据库中的索引!

    主表中的外键是另一张表的主键。 候选键:除了主键以外的都是候选键。 要想能快速查找某一条你想要的数据,必须要要创建主键(一般在开始创建表的时候就会设置)。 ...

    不吃小白菜
  • 每个数据科学家都需要知道的5种采样算法

    算法是数据科学的核心,而采样是决定项目成败的关键技术。了解有关使用的最常见采样技术的更多信息,因此您可以在处理数据时选择最佳方法。

    计算机与AI
  • 《Kotlin极简教程》第3章 Kotlin语言基础第3章 Kotlin语言基础《Kotlin极简教程》正式上架:参考资料

    学习任何东西,都是一个由表及里的过程。学习一门编程语言也一样。对于一门编程语言来说,“表” 就是基本词汇(关键字、标识符等)、句子(表达式)和语法。

    一个会写诗的程序员
  • google 分屏 横屏模式 按home键界面错乱故障分析(一)

    你确定你了解分屏的整个流程? ? 之前分析文章列表: Android 关机对话框概率没有阴影故障分析 android recent key长按事件弹起触发最近列...

    用户1263308
  • 你真的会用索引吗?来看看COUNT(*)到底能有多快

    作者简介 ? 案例说明 一个大表的COUNT,究竟能有多快?除类似物化视图的做法,我们所能做到的极限能有多快?这不是一个真实的案例,而是根据笔者在网上发的一篇帖...

    数据和云
  • Confluence 6 查看内容索引概要 原

    内容索引,通常也被称为查找索引,这个索引被用来在 Confluence 中支持查找。这个索引同时也被其他的一些功能使用,例如在归档邮件中构建邮件主题,View ...

    HoneyMoose

扫码关注云+社区

领取腾讯云代金券