先模拟一下数据，做一些准备工作, 创建表，插入10万条数据
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；
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索引
图 1 使用 explain 命令查看语句执行情况
从图1看，结果是没问题的，也和我们理想中的是一致的。
接下来，我们执行一下以下SQL
图 2 session A 和 session B 的执行流程
在模拟的时候可以开两个会话，这里就是sessionA使用一致快照启动事务，sessionB删除数据+重新插入数据+查询执行计划，最后sessionA再提交事务。
这时候，session B 的查询语句 select * from t where a between 10000 and 20000 就不会再选择索引 a 了。我们可以通过慢查询日志（slow log）来查看一下具体的执行情况。
为了验证我们猜测的准确性。
- 首先修改慢查询日志的时间 当前的意思就是0秒之后的所有查询都将记入慢查询日志中。这里有个坑设置完之后必须重启会话，否则无效
set long_query_time=0; - 查询当前设置是否成功
show variables like 'long%'; - 查询确认当前慢查询日志是否正常开启
show variables like '%slow_query_log%';
以上就是准备工作了。我们采用了一组对照操作。然后观察执行结果。
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 毫秒。也就是说，我们在没有使用 force index 的时候，MySQL 用错了索引，导致了更长的执行时间。
这个例子对应的是我们平常不断地删除历史数据和新增数据的场景。这时，MySQL 竟然会选错索引，是不是有点奇怪呢？今天，我们就从这个奇怪的结果说起吧。
优化器干了哪些事
上述文章中我们了解过一条SQL的执行流程，介绍过优化器主要负责选择索引的操作。那么优化器这里到底是怎么做的呢？我们一步一步深挖底层原理。
优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。
当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
我们这个简单的查询语句并没有涉及到临时表和排序，所以 MySQL 选错索引肯定是在判断扫描行数的时候出问题了。
MySQL的扫描行数是怎么判断的？
数据库在真正的执行之前是不可能知道有多少行数据的，而且也不可能把数据都查一遍然后根据数量选择对应的索引。那么到底是怎么实现的呢？
索引的区分度
显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。我们可以通过show index from 表名 看到一个索引的基数。
以下Cardinality列的值并不代表行数。
索引的基数
上面简单的介绍了基数是啥，基数能影响哪些东西。以下着重介绍一下MySQL内部是怎么得到索引基数的。采样统计
采样统计的时候，innodb默认会选择N个数据页，统计这些界面上的不同值，得到一个平均值，然后乘以这个索引的页面数。这个数就是索引的基数！
数据表会持续更新的，索引的统计信息也不会固定一变。所以当变更的数据行数超过1/M的时候，会自动触发重新做一下索引统计。也就类似于上文提到的 analyze table 表名
介绍一下索引统计，索引统计在MySQL中有两种存储方式，可以通过以下代码查看。
show variables like 'innodb_stats_persistent';
- 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10
- 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。
SQL影响
图3 explain执行计划
根据图3，我们可以分析出扫描行数差距非常大。行数的原因影响已经分析过了。下面从另一个方面介绍一下。
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
根据以上两个提示得出，可能使用的索引与实际使用的索引不一致，导致索引失效，无效增大查询性能。
那么为什么会这样呢。上文的测试数据，通过sessionA和sessionB的两个会话的数据操作。导致统计数据不准。为什么放着扫描 37000 行的执行计划不用，却选择了扫描行数是 100000 的执行计划呢？
这是因为，如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。
而如果选择扫描 10 万行，是直接在主键索引上扫描的，没有额外的代价。
优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。
使用普通索引，把回表的代价也算了进去。根据图1我们得到我们这样的SQL策略并没有 什么问题。所以冤有头债有主
选错索引的原因有哪些
- 索引的区分度判断有误 (基数)
- 回表操作导致索引选择有误
解决方案
- 利用force index 强行选择一个索引
- 利用analyze table t重新统计索引信息
- 新增一个更合适的索引
- 编写SQL，引导优化器选择索引
为什么必须依靠sessionA的事务
为什么没有session A,session B扫描的行数是1W
顶部的准备工作，我相信很多小伙伴都非常疑惑。我当初一开始接触的时候也是碰壁好久。为什么session的删除，插入，执行的时候要依靠sessionA的一致性事务呢？
如果没有sessionA。sessionB一个会话是否能完成这篇文章的所有工作呢？错误，完成不了
这里要了解delete的实现原理。
delete： 这里的删除并不是真正意义上从磁盘中删除，而是直接删除了当前的引用标记。
因为delete删除的特性。如果delete与存储过程中的insert语句间隔时间较短，purge线程还没有来得及清理该记录数据。如果主键相同的情况下，MySQL会自动使用之前的记录空间。
由于相同的数据量，以及表大小，索引的统计信息没有变化，最终导致扫描行数等一系列的问题的出现。
为什么开启了session A,session B扫描行数变成3W
由于是一致性事务也可以说是一致性读，目的是保证sessionA的可重复读。insert只能另起炉灶，把数据插入到其他地方。然后时间一到，purge就会清空之前的删除数据。
purge还没有执行的时候，就会出现表大小不一致，空间不一致等一系列问题，最终导致索引统计信息有误。
purge线程干了啥
innodb_purge_batch_size
set innodb_purge_batch_size=X
用来设置每次purge操作需要清理的undo log page的数量。【默认300，表示每次清理300个page，支持动态修改】
设置的越大，表示每次回收的页也就越多，可供重用的undo page也就越多，就能减少磁盘存储空间与分配的开销。不过该参数设置得太大，则每次需要purge处理更多的undo page，从而导致CPU和磁盘IO过于集中于对undo log的处理，使性能下降。普通用户不建议调整这个参数
innodb_purge_threads
set innodb_purge_threads=X
当有很多的表进行DML操作时候， 增大 innodb_purge_threads 能提高purge的效率(清理掉MVCC机制导致的老旧数据)。
现在的MySQL版本中。purge线程已经从master线程中独立出来,使用单独的线程提高了可伸缩性。
从MySQL5.7.8开始，这个参数默认是4，最大可以设置为32.【老版本里面这个值默认是1】
这两个一般不需要修改，这里就不过多阐述了
set innodb_max_purge_lag = X set innodb_max_purge_lag_delay = X
作用
- 清理del flag标签的记录
- 清理undo的历史版本
- 如果需要进行undo tablespace截断。
