执行查询语句时，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；

示例数据，插入(1,1,1), (2,2,2)…(10w, 10w, 10w)

-- 存储过程
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();

分析查询语句：

select * from t where a between 10000 and 20000; -- 显然，优化器选择索引 a

使用 explain 命令查看语句执行情况：

但是当假设出现这种情况：

session A 开启一个事务，随后 session B 删除数据再插入，再次分析这里的查询语句：

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

• 1. 将慢查询日志的阈值设置为 0，表示这个线程接下来的语句都会被记录入慢查询日志中；通过慢查询日志 show log 查看具体的查询情况。
• 2. session B 的查询。
• 3. 加上 force index(a) ，作为对比。

慢查询日志如下：

结果来看，Q1 扫描了 10w 行，显然是全表扫描了，执行时间是 40ms；Q2 扫描了 10001 行，执行时间是 21ms。也就是说，当没有使用 force index 的时候，MySQL 用错了索引，导致了更长的执行时间。

优化器的逻辑

在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。

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

扫描行数？索引的区分度？

一个索引上不同的值越多，这个索引的区分度就越好。

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

查看索引的基数：show index from t;

在此统计信息中，这三个索引的基数值并不同，而且其实都不准确。

那么问题来了，MySQL 是怎样得到索引的基数的呢？

答：采样统计。

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

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

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

然后还有问题，这里**索引统计值（cardinality 列）**都差不多，那选错索引肯定还有别的原因。

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

查看优化器预估：

select * from t where a between 10000 and 20000;  /*Q1*/
select * from t force index(a) where a between 10000 and 20000;   /*Q2*/

rows 这个字段表示的是预计扫描行数。

分析：Q1 的预计扫描行数 rows 约 10w，Q2 的预计扫描行数 rows 约 3w7。而上面正常查询的预计扫描行数约 1w，是这个偏差误导了优化器的判断。

为什么优化器选择了更大的 10w 而不选择少的 3w7 ？这里 MySQL 是考虑了回表代价。 优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。虽然，从实际执行时间来看，其实还是应该选择 3w7 。所以优化器判断错了。

来修正它，重新统计索引信息 analyze table t;

所以在实践中，如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用这个方法来处理，修正后预计扫描行数 rows 值就对了。

修正后并没有完事

执行语句：

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

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

所以应该使用索引 a ？用 explain 分析下！

可以看到，返回结果中 key 字段显示，这次优化器选择了索引 b，而 rows 字段显示需要扫描的行数是 50198。

结论：

1. 扫描行数的估计值依然不准确；
2. 这个例子里 MySQL 又选错了索引。

索引选择异常和处理

使用 force index(a) 确实可以起到“矫正”的作用，但是我们还会考虑其他因素，如语句优美性、索引改名、数据库迁移兼容性等。

选错索引的情况还是比较少出现的。

然后还可以修改语句，引导 MySQL 使用期待的索引，如：

把 order by b limit 1 改成 order by b,a limit 1。

再如：

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;

再再如：

考虑删除索引 b 。