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

2020-11-03 11:35:50 浏览数 (1)

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

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

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

代码语言：javascript复制
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月份的数据，逻辑上并不复杂，我们也回想很快想到下面语句

代码语言：javascript复制
select count(*)  from tradelog where month(t_modified)=7

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

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

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

代码语言：javascript复制
select *  from  tradelog where t_modified='2018-7-1'

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

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

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

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

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

代码语言：javascript复制
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');

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

案例二：隐式类型转换

我们先看看下面语句

代码语言：javascript复制
mysql> select * from tradelog where tradeid=110717;

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

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

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

代码语言：javascript复制
mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

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

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

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

代码语言：javascript复制
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');

我们在执行下面插叙语句

代码语言：javascript复制
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它也是有索引的，应该也是使用索引才对，为什么没有使用索引的

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

代码语言：javascript复制
mysql> select * from trade_detail where tradeid=$L2.tradeid.value;

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

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

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

代码语言：javascript复制
select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

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

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

代码语言：javascript复制
mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

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

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

代码语言：javascript复制
select operator from tradelog  where traideid =$R4.tradeid.value;

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

代码语言：javascript复制
select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4);

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

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

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

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

utf8

0 人点赞