10-01 | MySQL为什么有时候会选错索引？

一、引人入胜的问题

不知道你有没有碰到过这种情况，一条本来可以执行得很快的语句，却由于 MySQL 选错了索引，而导致执行速度变得很慢？
PS：如果这里回答没有遇见过，那也正常，毕竟刚看到这里我也没遇见过，打工人何必难为打工人～

我们先来创建一个表，看如何复现这个问题，过程如下⬇️

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)。
PS：别问我如何插入？两种方式：1，撸代码。2，存储过程。

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();

–据说这个存储过程需要，编辑 my.conf将thread_stack 改为256k才能执行这个存储过程, 再重启 mysql。

分析如下SQL会走a索引？
mysql> select * from t where a between 10000 and 20000;
explain结果：

这条查询语句的执行也确实符合预期，key 这个字段值是’a’，表示优化器选择了索引 a。
PS：如果仅仅只是这样就完了么，肯定不是，我们接下来才是要放大招了。
在我们已经准备好的包含了 10 万行数据的表上，我们再做如下操作。

🏁：为了说明优化器选择的结果是否正确，我增加了一个对照，即：使用 force index(a) 来让优化器强制使用索引 a（这部分内容，我还会在这篇文章的后半部分中提到）。
SQL如下：
set long_query_time=0;
// 将慢查询日志的阈值设置为 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 扫描了 10 万行，显然是走了全表扫描，执行时间是 40 毫秒。Q2 扫描了 10001 行，执行了 21 毫秒

这个例子对应的是我们平常不断地删除历史数据和新增数据的场景。这时，MySQL 竟然会选错索引，是不是有点奇怪呢？