10 | MySQL为什么有时分会选错索引?

在MySQL中一张表是能够支撑多个索引的。可是写SQL句子的时分,并没有主动指定运用哪个索引。也便是说,运用哪个索引是由MySQL来确认的

有时会出现这种情况,一条本来能够履行得很快的句子,却因为MySQL选错了索引,导致履行速度变得很慢?

举个比如,建立一个简略的表,表里有a,b两个字段,分别建上索引。

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;

咱们往表t中插入10万行记载,取值按整数递增,即:(1,1,1),(2,2,2),(3,3,3) 直到 (100000,100000,100000)。

delimiter ;; 
create procedure idata() 
begin 
  declare i int; 
  set i=1; 
  while(i<=100000)do 
  insert into t values(i, i, i); 
  set i=i+1; 
  end while; 
end;; 
delimiter ; 
call idata();

接下来剖析一条SQL句子:

mysql> select * from t where a between 10000 and 20000;

这时你会觉得a上有索引,直接运用索引a就好了,现实也的确如此,咱们运用explain指令能够看到这条句子履行情况,key这个字段值是’a’,表明优化器挑选了索引a。

咱们在包含了10万行数据的表上,再做如下操作。

MySQL实战45讲_10 | MySQL为什么有时候会选错索引?

session A的操作便是敞开了一个事务。随后,session B把数据都删去后,又调用了 idata这个存储进程,插入了10万行数据。

这时分,session B的查询句子select * fromt where a between 10000 and 20000就不会再挑选索引a了。咱们能够经过慢查询日志(slow log)来查看一下详细的履行情况。

为了阐明优化器挑选的成果是否正确,增加了一个对照,即:运用force index(a)来让优化器强制运用索引a。

下面的三条SQL句子,便是这个试验进程。

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/
  1. 第一句,是将慢查询日志的阈值设置为0,表明这个线程接下来的句子都会被记载入慢查询日志中;
  2. 第二句,Q1是session B本来的查询;
  3. 第三句,Q2是加了force index(a)来和session B本来的查询句子履行情况比照。

MySQL实战45讲_10 | MySQL为什么有时候会选错索引?

如图所示是这三条SQL句子履行完成后的慢查询日志。能够看到,Q1扫描了10万行,明显是走了全表扫描,履行时间是40毫秒。Q2扫描了10001行,履行了21毫秒。也便是说,咱们在没有运用force index的时分,MySQL用错了索引,导致了更长的履行时间。

这个比如对应的是咱们平常不断地删去历史数据和新增数据的场景。这时,MySQL竟然会选错索引,是不是有点古怪呢?下面从优化器的逻辑进行剖析。

优化器的逻辑

01曾提到过优化器能够挑选索引,意图是找到一个最优的计划,以最小的价值履行句子。对于数据库,扫描行数越少,意味着拜访磁盘数据的次数越少,消耗CPU资源越少。当然优化器还会结合是否运用暂时表、是否排序等因素进行综合判别。

扫描行数怎样判其他?

MySQL在真正开端履行句子之前,并不能精确地知道满足这个条件的记载有多少条,而只能依据计算信息来预算记载数。

这个计算信息便是索引的“区分度”。明显,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,咱们称之为“基数”(cardinality)。也便是说,这个基数越大,索引的区分度越好

咱们能够运用showindex方法,看到一个索引的基数。如图所示,便是表t的showindex的成果。尽管这个表的每一行的三个字段值都是一样的,可是在计算信息中,这三个索引的基数值并不同,并且其实都禁绝确。

MySQL实战45讲_10 | MySQL为什么有时候会选错索引?

MySQL是怎样得到索引的基数的呢?

一般挑选“采样计算”,把整张表取出来一行行计算尽管能够得到精确的成果,可是价值太高。采样计算的时分,InnoDB默许会挑选N个数据页,计算这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数,这个基数都是很容易禁绝的。

此外,索引计算仅仅一个输入,对于一个详细的句子来说,优化器还要判别,履行这个句子自身要扫描多少行。接下来,咱们再一起看看优化器预估的,这两个句子的扫描行数是多少。

MySQL实战45讲_10 | MySQL为什么有时候会选错索引?

rows这个字段表明的是预计扫描行数。其间,Q1的成果仍是契合预期的,rows的值是104620;可是Q2的rows值是37116,偏差就大了。而图1中咱们用explain指令看到的rows是只有10001行,是这个偏差误导了优化器的判别。

到这里,可能你的第一个疑问不是为什么禁绝,而是优化器为什么放着扫描37000行的履行计划不用,却挑选了扫描行数是100000的履行计划呢?

优化器会预算这两个挑选的价值,从成果看来,优化器以为直接扫描主键索引更快。当然,从履行时间看来,这个挑选并不是最优的。运用一般索引需求把回表的价值算进去,在履行explain的时分,也考虑了这个战略的价值,但挑选是对的。也便是说,这个战略并没有问题。

所以MySQL选错索引,这件事儿还得归咎到没能精确地判别出扫描行数。运用analyze table t指令,能够用来重新计算索引信息。

MySQL实战45讲_10 | MySQL为什么有时候会选错索引?

假如你发现explain的成果预估的rows值跟实际情况差距比较大,能够选用这个方法来处理。其实,假如仅仅索引计算禁绝确,经过analyze指令能够处理很多问题,可是优化器可不止是看扫描行数。

仍然是依据这个表t,咱们看看另外一个句子:

mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by id

从条件上看,这个查询没有契合条件的记载,因而会返回空集合。假如你来挑选索引,会挑选哪一个呢?为了便于剖析,咱们先来看一下a、b这两个索引的结构图。

MySQL实战45讲_10 | MySQL为什么有时候会选错索引?

假如运用索引a进行查询,那么便是扫描索引a的前1000个值,然后取到对应的id,再到主键索引上去查出每一行,然后依据字段b来过滤。明显这样需求扫描1000行。

假如运用索引b进行查询,那么便是扫描索引b的最终50001个值,与上面的履行进程相同,也是需求回到主键索引上取值再判别,所以需求扫描50001行。

假如运用索引a的话,履行速度明显会快很多。那么运用explain指令就来看看到底是不是这么一回事儿。

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1

MySQL实战45讲_10 | MySQL为什么有时候会选错索引?

能够看到,返回成果中key字段显现,这次优化器挑选了索引b,而rows字段显现需求扫描的行数是50198。从这个成果中,你能够得到两个定论:

  1. 扫描行数的估计值仍然禁绝确;
  2. 这个比如里MySQL又选错了索引。

索引挑选异常和处理

其实大多数时分优化器都能找到正确的索引,但偶尔你仍是会碰到咱们上面举例的这两种情况:本来能够履行得很快的SQL句子,履行速度却比你预期的慢很多,你应该怎样办呢?

第一种方法是,选用force index强行挑选一个索引。MySQL会依据词法解析的成果剖析出可能能够运用的索引作为候选项,然后在候选列表中顺次判别每个索引需求扫描多少行。假如force index指定的索引在候选索引列表中,就直接挑选这个索引,不再评估其他索引的履行价值。

咱们来看看第二个比如。刚开端剖析时,咱们以为挑选索引a会更好。本来句子需求履行2.23秒,而当你运用force index(a)的时分,只用了0.05秒,比优化器的挑选快了40多倍。也便是说,优化器没有挑选正确的索引,force index起到了“矫正”的效果。

不过运用force index也有缺陷,假如索引改了名字,这个句子也得改,显得很麻烦。并且假如以后迁移到其他数据库的话,这个语法还可能会不兼容。所以,数据库的问题最好仍是在数据库内部来处理。那么,在数据库里面该怎样处理呢?

既然优化器抛弃了运用索引a,阐明a还不够适宜。第二种方法便是,咱们能够考虑修改句子,引导MySQL运用咱们期望的索引。比如,在这个比如里,明显把“order by b limit 1” 改成 “order by b,a limit 1”,语义的逻辑是相同的。

之前优化器挑选运用索引b,是因为它以为运用索引b能够避免排序(b自身是索引,已经是有序的了,假如挑选索引b的话,不需求再做排序,只需求遍历),所以即便扫描行数多,也判定为价值更小。

现在order by b,a这种写法,要求按照b,a排序,就意味着运用这两个索引都需求排序。因而,扫描行数成了影响决策的首要条件,所以此刻优化器选了只需求扫描1000行的索引a。

第三种方法是,在有些场景下,咱们能够新建一个更适宜的索引,来提供给优化器做挑选,或删掉误用的索引。