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万行数据的表上，再做如下操作。
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*/
- 第一句，是将慢查询日志的阈值设置为0，表明这个线程接下来的句子都会被记载入慢查询日志中；
- 第二句，Q1是session B本来的查询；
- 第三句，Q2是加了force index(a)来和session B本来的查询句子履行情况比照。
如图所示是这三条SQL句子履行完成后的慢查询日志。能够看到，Q1扫描了10万行，明显是走了全表扫描，履行时间是40毫秒。Q2扫描了10001行，履行了21毫秒。也便是说，咱们在没有运用force index的时分，MySQL用错了索引，导致了更长的履行时间。
这个比如对应的是咱们平常不断地删去历史数据和新增数据的场景。这时，MySQL竟然会选错索引，是不是有点古怪呢？下面从优化器的逻辑进行剖析。
优化器的逻辑
01曾提到过优化器能够挑选索引，意图是找到一个最优的计划，以最小的价值履行句子。对于数据库，扫描行数越少，意味着拜访磁盘数据的次数越少，消耗CPU资源越少。当然优化器还会结合是否运用暂时表、是否排序等因素进行综合判别。
扫描行数怎样判其他？
MySQL在真正开端履行句子之前，并不能精确地知道满足这个条件的记载有多少条，而只能依据计算信息来预算记载数。
这个计算信息便是索引的“区分度”。明显，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，咱们称之为“基数”（cardinality）。也便是说，这个基数越大，索引的区分度越好。
咱们能够运用showindex方法，看到一个索引的基数。如图所示，便是表t的showindex的成果。尽管这个表的每一行的三个字段值都是一样的，可是在计算信息中，这三个索引的基数值并不同，并且其实都禁绝确。
MySQL是怎样得到索引的基数的呢？
一般挑选“采样计算”，把整张表取出来一行行计算尽管能够得到精确的成果，可是价值太高。采样计算的时分，InnoDB默许会挑选N个数据页，计算这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数，这个基数都是很容易禁绝的。
此外，索引计算仅仅一个输入，对于一个详细的句子来说，优化器还要判别，履行这个句子自身要扫描多少行。接下来，咱们再一起看看优化器预估的，这两个句子的扫描行数是多少。
rows这个字段表明的是预计扫描行数。其间，Q1的成果仍是契合预期的，rows的值是104620；可是Q2的rows值是37116，偏差就大了。而图1中咱们用explain指令看到的rows是只有10001行，是这个偏差误导了优化器的判别。
到这里，可能你的第一个疑问不是为什么禁绝，而是优化器为什么放着扫描37000行的履行计划不用，却挑选了扫描行数是100000的履行计划呢？
优化器会预算这两个挑选的价值，从成果看来，优化器以为直接扫描主键索引更快。当然，从履行时间看来，这个挑选并不是最优的。运用一般索引需求把回表的价值算进去，在履行explain的时分，也考虑了这个战略的价值，但挑选是对的。也便是说，这个战略并没有问题。
所以MySQL选错索引，这件事儿还得归咎到没能精确地判别出扫描行数。运用analyze table t指令，能够用来重新计算索引信息。
假如你发现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这两个索引的结构图。
假如运用索引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
能够看到，返回成果中key字段显现，这次优化器挑选了索引b，而rows字段显现需求扫描的行数是50198。从这个成果中，你能够得到两个定论：
- 扫描行数的估计值仍然禁绝确；
- 这个比如里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。
第三种方法是，在有些场景下，咱们能够新建一个更适宜的索引，来提供给优化器做挑选，或删掉误用的索引。
