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)。

1）10万条记录是怎么插进去的？

存储过程

2）什么是存储过程？

其实就是java中的函数

3）插入10万条记录的存储过程怎么写？

delimiter ;; //告诉mysql，该段命令是否结束
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();

4）执行select * from t where a between 10000 and 20000语句，选择是索引是谁？

5）执行下面图中的流程，那又是选择谁做索引？

不会再选择a做索引了。

5.1）为什么mysql会选错索引，不选择a做索引了？

MySQL 在判断扫描行数的时候出问题了

5.1.1）扫描行数是怎么判断的？

根据统计信息来估算。

5.1.2）那么这个统计信息是啥呢？

索引的“区分度“：一个索引上不同的值越多，这个索引的区分度就越好。一个索引上不同的值的个数，我们称之为“基数”。

5.1.3）MySQL 是怎样得到索引的基数的呢？

采样统计（选N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数）

5.1.4）为什么要采样统计？

整张表取出来一行行统计，代价太高

5.1.5）索引统计信息是固定的吗？

不是，数据表变，他也跟着变，1/M就变，比如10万条，M就是10，那更新了10000条数据的时候就重新统计索引信息。

5.1.6）索引信息是放在哪里的？

innodb_stats_persistent=on，放在磁盘。N为20，M为10.
innodb_stats_persistent=off，放在内存。N为8，M为16。

索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句本身要扫描多少行。

5.1.7）优化器预估的，Q1和Q2的扫描行数是多少？

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*/

Q1 的结果还是符合预期的，rows 的值是 104620。Q2 的 rows 值是 37116，而图 1 中我们用 explain 命令看到的 rows 是只有 10001 行，是这个偏差误导了优化器的判断。

6）我们上面说不再选择a作为索引，优化器为什么放着扫描 37000 行的执行计划不用，却选择了扫描行数是 100000 的执行计划呢？

用索引a我每次都得回表一次。
如果选择扫描 10 万行，是直接在主键索引上扫描的，没有额外的代价
优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。

7）mysql选错索引的根本原因是什么？

没能准确地判断出扫描行数

8）当发现mysql选错索引了我们应该怎么办？

使用analyze table t 命令，可以用来重新统计索引信息

9）假设现在有如下语句，假如你来选择索引，你会怎样选择？

第一直觉当然是选a

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

先来看一下 a、b 这两个索引的结构图

如果使用索引 a 进行查询，那么就是扫描索引 a 的前 1000 个值，然后取到对应的 id，再到主键索引上去查出每一行，然后根据字段 b 来过滤。显然这样需要扫描 1000 行。
如果使用索引 b 进行查询，那么就是扫描索引 b 的最后 50001 个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描 50001 行。

10）实际上上面的语句mysql选择谁做索引？

竟然选择了b做索引。

11）为什么会选择b来做索引？

又选错了

12）为什么又选错了？

扫描行数的估计值依然不准确；

大多数时候优化器都能找到正确的索引，但偶尔你还是会碰到我们上面举例的这两种情况：原本可以执行得很快的 SQL 语句，执行速度却比你预期的慢很多

13）选错索引我们应该怎么办呢？

采用 force index 强行选择一个索引
在数据库内部来解决

14）用 force index有什么缺点？

不优美
如果索引改了名字，这个语句也得改
如果以后迁移到别的数据库的话，这个语法还可能会不兼容。

15）在数据库里面该怎样解决呢？

15.1）改变自己：

优化器放弃了使用索引 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。

15.2）麻痹优化器

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

用 limit 100 让优化器意识到，使用 b 索引代价是很高的。其实是我们根据数据特征诱导了一下优化器，也不具备通用性

15.3）新建一个更合适的索引，或删掉误用的索引。

16）前面我们在构造第一个例子的过程中，通过 session A 的配合，让 session B 删除数据后又重新插入了一遍数据，然后就发现 explain 结果中，rows 字段从 10001 变成 37000 多。而如果没有 session A 的配合，只是单独执行 delete from t 、call idata()、explain 这三句话，会看到 rows 字段其实还是 10000 左右。你可以自己验证一下这个结果。这是什么原因呢？