一、优化器选错索引

MySQL 选错索引，导致执行速度慢。表里 a、b 两个字段，分别建索引：

插入 10 万行记录，递增，即：(1,1,1)，(2,2,2)，(3,3,3) 到(100000,100000,100000)。

存储过程插入数据

分析SQL：mysql> select  * from t where a between 10000 and 20000;

a 上有索引，肯定要用。explain 执行情况。

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

符合预期，优化器选择了索引 a。10 万行数据表，再做如下操作。

图 2 session A 和 session B 的执行流程

session A 开启事务。session B 数据删除，又调用idata 存储过程，插入10 万行数据。

这时，session B 的查询语句 select * from t where a between 10000 and 20000 不再选索引 a 。慢查询日志（slow log）看具体执行情况。

增加对照用 force index(a) 让优化器强制用索引 a。实验过程：

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

图 3 slow log 结果

Q1 扫描10 万行(全表)，40 毫秒。Q2 扫描 10001 行，21 毫秒。没用 force index 时候，MySQL 用错索引，导致执行时间长。

例子对应删除历史数据和新增数据。

二、优化器的逻辑

选择索引是优化器的工作。

扫描行数越少，访问磁盘越少，消耗 CPU 资源越少。

扫描行数不是唯一判断标准，临时表、是否排序等因素

2.1扫描行数是怎么判断的？

执行语句前，不能精确知道，根据统计估算。

统计信息就是索引“区分度”。索引不同值越多（“基数”cardinality），区分度越好。

show index看索引基数。三个字段值一样，基数值不同，其实都不准。

图 4 表 t 的 show index 结果

2.2怎样得到索的基数？

MySQL 采样统计方法：InnoDB 默认选择 N 个数据页，统计不同值，得平均值 * 索引页面数 = 索引基数。

表持续更新，变更数据行 > 1/M：重新索引统计（自动触发）。

两种存储索引统计的方式，innodb_stats_persistent ：

on ，持久化存储。默认的 N 是 20，M 是 10。

off ，只在内存中。默认的 N 是 8，M 是 16。

不精确，大体差不的，选错索引还有别的原因。优化器判断语句本身要扫描多少行。

图 5 意外的 explain 结果

rows 预计扫描行数。Q1符合预期：104620；Q2 ： 37116，偏差大了。图 1 看到10001 行，偏差误导优化器

优化器 37000 不用，选择100000 执行计划？

用索引 a，从索引 a 拿到值，回主键索引上查出整行（也是代价）。

扫描 10 万行，主键索引上扫描的，没额外代价。

选择并不是最优的。普通索引需要把回表代价算进去，图 1 选择是对的。

MySQL 选错索引，还是没准确判断出扫描行数。

三、修正统计信息analyze table t

图 6 执行 analyze table t 命令恢复的 explain 结果

explain结果预估rows 值跟实际差距大，只是索引统计， analyze 可解决

mysql> select  * from t where (a between 1 and 1000) 

  and (b between 50000 and 100000) order by b limit 1; 返回空集合。

选择哪索引？

图 7 a、b 索引的结构图

a（ 快） ，扫描前 1000 个，取到对应id，再到主键索引查每行，根据b 过滤。

b，扫描最后 50001 个值，回到主键索引上取值再判断

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

图 8 使用 explain 方法查看执行计划 2  

优化器选择 b：估计值依然不准确；MySQL 又选错了索引（小概率）。

四、索引选择异常和处理

优化器选错怎么办？

（1）force index 强行选择索引

图 9 使用不同索引的语句执行耗时

 force index，不优美，索引改名字，语句也改。迁移库，语法不兼容。

变更及时性。通常不会先写上 force index。修改还要测试和发布，不够敏捷。

最好还是在数据库内部解决

（2）引导 MySQL 用期望索引

把“order by b limit 1” 改成 “order by b,a limit 1” 优化器选索引 a。

图 10 order by b,a limit 1 执行结果  

优化器索引 b 可以避免排序，代价小。

不是通用手段，只是刚好有 limit 1， order by b limit 1 和 order by b,a limit 1 返回 b 都是最小行，逻辑一致。

mysql> 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;

图 11 改写 SQL 的 explain

（3）新建索引，或删掉误用索引

新增索引：情况少，尤其DBA 索引优化过库，找更合适索引难。

删掉索引 b，业务如果不需要

小结

优化器可能选错索引

索引统计信息不准确，analyze table 解决。

优化器误判解决办法：1.应用端用 force index 强行指定索引，2.修改语句引导优化器，3.增加或者删除索引避开。

例子没说明原理。因为面对的是 MySQL 的 bug。知道解决方法有思路就ok。

思考题

平时处理 MySQL 优化器 bug 的时候有什么别的方法？

例子1中，索引由a变成b的原因？

通过 session A 的配合，让 session B 删除后又重新插入了一遍数据，explain 结果中，rows 字段从 10001 变成 37000 多。

没有 session A 配合，只是单独执行 delete from t 、call idata()、explain 这三句话， rows 字段其实还是 10000 左右。

A 事务没提交，数据不能删除的。索引 a 上数据有两份，旧版本delete 之前，新版本 deleted。

主键直接按照行数估计。行数，优化器用show table status 值。

 IO 能力差，做验证时，可以把innodb_flush_log_at_trx_commit 和 sync_binlog 都设置成 0。

评论1

1.a改为(50001,51000),扫描行数没变。优化器给扫描行数有问题？执行器没结束循环？(必须执行器上过滤数据,不能在索引上过滤数据)

作者回复: 1. 加了limit 1 减少扫描多少行，优化器也不确定，执行才知道，按照“最多可能扫多少行”来显示。

评论2

500万表 分页查询慢。

select * from table where

    create_time and create_time>=时间戳 

    and create_time<=时间戳 

    and subtype='xx' 

    and type='xx' 

    and company_id =x 

order by create_time limited 90,30 ;

建立组合索引 union_index包括 create_time ，subtype， type， company_id

explain 走了create_time索引

语句里加了一个use index(union_index) ，立马好了，真正的解决了客户的实际问题啊。