MySQL为什么有时候会选错索引

我们先建一个简单的表，表里有 a、b 两个字段，并分别建上索引：

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

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语句：

select * from t where a between 10000 and 20000;

这一看就知道用索引a

那再模拟一下实际场景中会遇到的：一个线程在查询数据，另一个线程在更新数据。

这里session A先开启了一个事物，然后session B把表t的数据全都删掉之后又重新添加了数据

为了说明优化器选择的结果是否正确，我增加了一个对照，即：使用 force index(a) 来让优化器强制使用索引 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*/

总结一下：

MySQL中索引的选择是由优化器来决定的，而决定用什么索引的原因有很多，比如 区分度、扫描行数、是否使用了临时表、是否需要排序、是否需要回表等。

MySQL是知道这个索引区分度好不好的（数据重复少—>区分度大）

首先从磁盘中随机加载N份数据页，然后统计不同的值，取得平均值后乘以索引页面的数量，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

• 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。

• 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。