mysql 为什么会选错索引

前言

在写 sql 语句时，如果你没有指定使用哪个索引，那么索引是由mysql来决定的。
在一次开发中，我遇到了一个问题，sql 语句并没有执行我新建立的索引，而是使用了以前建立的索引，导致查询的效率很低。
那为什么mysql会为我们的sql选错索引呢？

我建立一个表测试一下:

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；

分别在 a, b 字段上建立索引。

使用存储过程插入数据：

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

开始分析

explain select * from t where a between 10000 and 20000;

可以看到起使用 a 字段的索引.
但是在平常的不断的删除或者新增数据的场景，mysql 就可能选错索引。

优化器逻辑

在之前的mysql 的基础架构中提到 mysql 的索引是由优化器来进行选择的。
而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。

扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

扫描行数是怎么判断
mysql 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。

这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

SHOW INDEX FROM t;

mysql 是怎样得到索引的基数

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

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

show variables like 'innodb_stats_persistent';

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

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

可以上面的查询 扫描了 10001 行。

如果统计信息不对，我们可以使用

ANALYZE TABLE t;

重新统计。

但是优化器不只看扫描行数，看下这个 sql

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

但是这条语句明显先去查询 索引 a 的效率会更高。

索引选择异常和处理

1. 使用 force index ，但是这样代码写起来不优雅
2. 修改 sql 语句

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

之前优化器选择使用索引 b，是因为它认为使用索引 b 可以避免排序（b 本身是索引，已经是有序的了，如果选择索引 b 的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小。

或者

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;

3. 删除误用的索引，新建合适的索引