关键字
索引选择
0.错误情况
首先，建立一个简单的表，表中有 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();
接下来，我们分析一条语句：
mysql> select * from t where a between 10000 and 20000;
10-正常执行情况.png
没错，这条语句的执行是符合预期的。下面，我们来看一个会出错的情况，我们在这个已经有数据的表中，再做如下操作：
10-出错执行流程.png
这时，session B 的 select * from t where a between 10000 and 20000 语句就不会选择 a 索引了，而是直接使用了全表扫描。
执行下面三条语句：
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 原来的查询语句执行情况对比。
10-慢查询日志.png
可以看到，Q1 扫描了 10万 行，进行了全表扫描，Q2 使用了 a 索引扫描了 10001行。
为什么会这样呢？实际上，索引的选择是由优化器决定的，但是在某些特殊情况下，优化器会选择并非最优的方案。一般我们很难事先防范这种 bug ，但是你有必要理解其中的逻辑。
1.优化器的逻辑
优化器判断语句执行方式，会用到 扫描行数、数据排序 等。
1.1扫描行数
优化器选择索引的逻辑之一，就是计算需要扫描的行数。需要执行的行数越少，意味着消耗资源越少，优化器也就更加倾向选择这种操作。
在上面的例子中，影响优化器选择的最主要的原因就是扫描行数。实际上，MySQL 在执行一个语句之前是不可能知道它需要扫描多少行的，所以它需要使用表的统计信息来估算记录数。
1.1.1索引基数
一个索引上有多少个不同的值，这称之为“基数”。一个索引的基数越大，索引越容易被区分。在 MySQL 中，使用抽样统计的方法获取索引的基数值。而索引的基数，可以通过 show index 查看：
10-show index.png
你会发现，即使抽样统计有误差，但是三个索引的索引基数是差不多的，所以，其实选错索引这件事，和索引基数的关系并不太大。
1.1.2预估扫描行数
既然索引错误与索引基数关系不大，那么我们不妨分析一下 Q1 和 Q2 这两条语句：
10-预估扫描行数.png
在图中，row 表示预计扫描的行数：
- Q1 的结果是符合预期的，预估将扫描 10w+ 数据。
- Q2 的结果就不对了，我们已经在最开始试过，该命令当时的 row 只有 10001 ，是这个误差误导了优化器的判断。
你可能会疑问，即使 Q2 的 row 错误，但是 3w+ 的值依然小于 10w+ 啊，为什么优化器依然选择了 10w+ 的索引方式呢？这里简单说一下：因为使用 a 索引涉及到回表操作，优化器将这部分内容也算了进去，综合来看，优化器认为使用主键索引更快。
所以，归根结底，MySQL 选错索引，主要原因是它错误的判断了扫描行数，至于为什么会有错误的扫描行数，这留作今天的思考题。
既然问题出在统计信息，那么我们可以使用 analyze table t ，重新统计 t 表的索引信息，执行效果如下：
10-analyze table.png
这次就对了。
1.2数据排序
同样是这个表，我们执行下面的语句：
mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
很明显，
- 我们知道 t 表中所有数据的内容，使用 a 索引是最快的，只需要扫描 a 的前 1000 个值，然后进行回表。
- 而如果使用 b 索引，需要扫描 5w 行，再进行回表。
然而，MySQL 又选错了索引，如果不使用强制索引，MySQL 会使用 b 作为索引，而它的执行花费非常高。
为什么会这样呢？因为语句的末尾有 order by b，所以优化器认为使用 b 索引可以避免排序，而使用 a 的化还需要根据 b 再进行一次排序。这就导致了错误选择。
2.如何处理索引选择异常 & 总结
之前已经说过，索引选择异常非常复杂。实际上，在大多数情况下，优化器的选择都是正确的，只有在非常特殊的时候，才会遇到上面我们说的情况。因此，因为一个较小概率的事重建优化器的选择方案，是得不偿失的。
所以，在出现索引选择异常的时候，给你一些小的处理方法：
- 第一种方法，使用force index强行选择索引：在你非常确定使用某个索引是最优选择的时候，你可以为 MySQL 决定选择哪个索引。
- 第二种方法，使用analyze table重建表的统计信息。
- 第三种方法，修改语句，引导 MySQL 使用我们期望的索引：在排序的例子中，把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。但是在这种引导下，MySQL 会使用 a 作为索引。但是，这样的方法并不通用，因为你无法确定 a和b 之间的关系。
- 第四种方法，新建一个更适合的索引，来给优化器做选择，或者删掉误用的索引：当然，你要确定这个索引可以删除。
思考题
前面我们在构造第一个例子的过程中，通过 session A 的配合，让 session B 删除数据后又重新插入了一遍数据，然后就发现 explain 结果中，rows 字段从 10001 变成 37000 多。
而如果没有 session A 的配合，只是单独执行 delete from t 、call idata()、explain 这三句话，会看到 rows 字段其实还是 10000 左右。你可以自己验证一下这个结果。
这是什么原因？请你分析一下。
上期答案
上一篇文章的问题是，如果某次写入使用了 change buffer 机制，之后主机异常重启，是否会丢失 change buffer 和数据。
答案是不会丢失，虽然是只更新内存，但是在事务提交的时候，我们把 change buffer 的操作也记录到 redo log 里了，所以崩溃恢复的时候，change buffer 也能找回来。
以上就是本节内容，希望在人生路上，你能获得正确索引的指引。
注：本文章的主要内容来自我对极客时间app的《MySQL实战45讲》专栏的总结，我使用了大量的原文、代码和截图，如果想要了解具体内容，可以前往极客时间
网友评论