MySQL学习（六）索引第二篇

前言

在之前的索引篇章中基本上讲述了MySQL中常见的几种索引，在这篇中，打算续上之前的索引的部分，讲述MySQL使用索引过程中的一些隐藏机制。

为什么MySQL会选错索引

在MySQL中一张表其实是可以支持多个索引的。但是，你写SQL语句的时候，并没有主动指定使用哪个索引。也就是说，使用哪个索引是由MySQL来确定的。因此就会存在这样一种情况，一条本来可以执行很快的SQL语句因为MySQL选错了索引，导致执行速度变得很慢。

先来看一个简单的例子，首先我们创建一张表：

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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)。

然后执行这样一条SQL语句 mysql> select * from t where a between 10000 and 20000;

使用explain命令查看语句执行情况，得到的结果如下图所示

从结果上看，语句选择了索引a，也是情理之中。之后，在这张插入10万条数据的表中，再进行如下操作：

其中，call idata()是执行一个存储过程，这个存储过程就是之前用来插入10万条数据。

这时候，session B的查询语句 select * from t 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*/

第一句，是将慢查询日志的阈值设置为0，表示这个线程接下来的语句都会被记录入慢查询日志中；

第二句，Q1是session B原来的查询；

第三句，Q2是加了force index(a)来和session B原来的查询语句执行情况对比。

实验结果如下图所示：

可以看到，Q1扫描了10万行，显然是走了全表扫描，执行时间是40毫秒。Q2扫描了10001行，执行了21毫秒。也就是说，我们在没有使用force index的时候，MySQL用错了索引，导致了更长的执行时间。

优化器的逻辑

扫描行数

这里需要知道一个前提，索引的选择是优化器的工作。

而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断，而对于上述的例子，显然没有涉及临时表，排序等问题，因此MySQL选错索引就是在判断扫描行数是出现了问题。这就引入了另一个问题：扫描行数是怎么判断的？

MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。可以使用show index方法，看到一个索引的基数。对于上述的表，使用这个命令得到的结果如下图：

MySQL索引基数的计算方式

MySQL获取索引基数使用的方式是采样统计， 采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。 而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

可以从上图中看到，这次的索引统计值（cardinality列）虽然不够精确，但大体上还是差不多的，选错索引应当还有别的原因。其实索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句本身要扫描多少行。依旧可以通过explain命令查看优化器预计扫描的行数，如下图所示（其中rows字段就是预计扫描行数）：

其中，Q1的结果还是符合预期的，rows的值是104620；但是Q2的rows值是37116，偏差就大了。而最开始使用explain命令看到的rows是只有10001行，是这个偏差误导了优化器的判断。这里可能会诞生一个疑问，为什么优化器放着3w+行的执行计划不用，反而选择了10w+行的执行计划？

这是因为，如果采用了索引a，为了能够查询到整行数据，需要回到主键索引中查询，这个代价，优化器也考虑进去了，反之，如果选择了主键索引，自然就没有这部分代价了。优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。

所以，整体来看，MySQL选错索引的原因还是在于没能准确的判断出扫描的行数。因此，在实际使用中，如果发现explain命令得到预计扫描行数和实际情况相差较大，可以使用 analyze table [table name] 这个命令来重新统计索引信息。执行效果如图：

不止于扫描行数

在上面的讲述中可以知道，如果只是索引的统计不正确，可以通过analyze命令来修正，但实际上优化器在执行选择时不仅仅取决于扫描行数。基于上述的表，观察下面的SQL语句：

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

从条件上看，这条SQL语句没有符合条件的记录，会返回空集合。这里，a、b两个索引的结构图如下：

根据上图，如果使用索引a进行查询，那么就是扫描索引a的前1000个值，然后取到对应的id，再到主键索引上去查出每一行，然后根据字段b来过滤。显然这样需要扫描1000行；如果使用索引b进行查询，那么就是扫描索引b的最后50001个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描50001行。因此直觉上可能会认为选择索引a执行的速度会比较快。但如果使用explain查看执行计划能看到，优化器选择了索引b，如下图：

显然，优化器此时又选错了索引。

索引选择异常和处理

大多数时候，优化器都能够找到正确的索引，但是偶尔也会遇上原本可以执行得很快的SQL，执行速度却比预想的慢很多。这时候就需要一些处理方法，来提高这个SQL的执行效率。

强行选择索引

可以使用force index强行选择一个索引。 MySQL会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行。如果force index指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。

当优化器没有选择正确的索引，force index能够起到“矫正”的作用，使用方法如下：

select * from [table_name] force index ([index_name]) where ...

但是使用force index也有一定的弊端，将来如果索引改了名字，那么这个语句也得跟着变动，而且迁移数据库可能会有语法不兼容的问题。

修改SQL语句

可以考虑修改语句，引导MySQL使用期望的索引。

例如，在这个例子中，可以将 order by b limit 1 更改为 order by b, a limit 1。之前优化器选择索引b，是因为它认为使用索引b可以避免排序（b本身是索引，已经是有序的了，如果选择索引b的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小。现在order by b,a 这种写法，要求按照b,a排序，就意味着使用这两个索引都需要排序。因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描1000行的索引a。

新建索引

在有些场景下，可以新建一个更合适的索引，提供给优化器选择，或者删掉误用的索引。

参考

MySQL实战45讲——MySQL为什么有时候会选错索引？