上节我们讲了mysql的整体架构，知道了优化器的作用是优化sql，选择索引，生成执行计划。索引是优化器阶段自己选择的，优化器大部分情况下索引的选择都是比较合理的，但是也有特殊情况下，优化器选择的索引并不是最优的。下面我们通过实验来说明这个问题，并给出优化方案（需要说明的是本实验的环境是mysql版本是5.7,事务隔离级别是RR,事务自动提交）。
首先我们来建一个表，表里面有id、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(a,b) values(i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
这里要注意的是我的mysql默认是自动提交，且
sync_binlog=1,innodb_flush_log_at_trx_commit=1，所以说上面的存储过程每次插入数据，在提交的时候binlog和redolog都会写到磁盘，这大大增加了IO交互，导致整个插入过程非常慢，在我的电脑上运行了30分钟才插入完成，解决方法有很多可以设置sync_binlog=0,innodb_flush_log_at_trx_commit=0，sync_binlog=0表示每次提交只write(从binlog cache 写到page cache)不fsync(写到磁盘)，innodb_flush_log_at_trx_commit=0表示每次提交只写到redo log buffer(就是不做任何操作，因为redo log最开始生成的时候就在redo log buffer 中，Innodb 后台有一个线程，每1s就会将redo log buffer里面的数据调用write写到page cache 然后再调用 fsync 写到磁盘)，还需注意的是binlog cache 是线程私有的，redo log buffer是公共的，所以处以prepare阶段的redo log是可能被持久化的。
插入数据后，我们分析一下sql 语句的执行计划
EXPLAIN SELECT * from t where a BETWEEN 10000 and 20000;
你肯定会想这还不简单吗?字段a上有索引肯定走的索引a，事实也是这样的，下面是执行计划的截图
正常索引.png
我们再做如下操作，看看情况怎样
| sessionA | sessionB |
|---|---|
| start transaction with consistent snapshot; | |
| delete from t; | |
| call idata(); | |
| EXPLAIN SELECT * from t where a BETWEEN 10000 and 20000; | |
| commit; |
start transaction with consistent snapshot;代表的意思是执行这句话就启动了事务，就生成一致性视图，如果只是start transaction只有在第一次查询的时候才会生成一致性视图，需要说明的是start transaction并不是事务的起点，在执行到start transaction之后的第一个操作才启动事务，第一个查询语句生成的trx_id 是虚假的，是为显示用的，只有事务性操作生成的trx_id 才会存于一致性视图数组中。这个一致性视图数组是mvcc实现的基础。
然后，我们现在再来看一下sessionB 的 EXPLAIN SELECT * from t where a BETWEEN 10000 and 20000;
有误的索引.png
发现此时是全表扫描，key处为空，rows为100015行，没有走索引a，和我们预想的可能不太一样，为了更准确的说明，是mysql选错了索引，我们再来做个对比。
set GLOBAL slow_query_log = 1;
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**/
- 第一条语句是打开慢日志的开关 ，这是个全局的开关
- 第二条语句是慢日志的标准，就是说什么样的语句是慢日志，才会被慢日志记录下来。这里的意思是只要操作时间超过0s就会记录下来，所以这里是记录所有的操作语句（增删改查都会记录）。
- 第三条是在sessionB下的查询语句(Q1)
- 第四条是在sessionB下的查询语句(Q2)，但是强制使用的索引a
我们来看看Q1与Q2在慢日志中的具体查询信息
# Time: 2021-05-03T02:17:03.047811Z
# User@Host: root[root] @ localhost [::1] Id: 20
# Query_time: 0.035942 Lock_time: 0.000000 Rows_sent: 10001 Rows_examined: 100000
SET timestamp=1620008223;
SELECT * from t where a BETWEEN 10000 and 20000;
# Time: 2021-05-03T02:18:08.055825Z
# User@Host: root[root] @ localhost [::1] Id: 20
# Query_time: 0.021942 Lock_time: 0.000000 Rows_sent: 10001 Rows_examined: 10001
SET timestamp=1620008288;
SELECT * from t FORCE INDEX(a) where a BETWEEN 10000 and 20000;
可以看到没有使用强制索引a的Q1语句确实是全表扫描了Rows_examined: 100000,Query_time: 0.035942,强制使用了索引a的Q2语句Rows_examined: 10001,Query_time: 0.021942，到这里我们可以肯定说，mysql没有使用最优的方案来查询，下面我们就来具体分析分析为什么！
首先我们要明白的是优化器是怎么选择索引的，索引的选择有很多因素影响包括是否排序、是否使用到临时表、扫描行数等因素，优化器是综合考虑各个因素选择的最优方案，这里我们的语句SELECT * from t where a BETWEEN 10000 and 20000;，没有使用到临时表，也没有排序，所以这里最关键的影响就是扫描行数，扫描行数越少，访问磁盘的次数越少，需要消耗的CPU资源就越少。接下来问题又来了，扫描行数是怎么判断的呢？
我们在优化器阶段就判断了扫描的行数（执行计划中的Rows），但这个扫描的行数是预估的，不是准确的，为什么要预估呢？因为扫描所有数据需要消耗大量的资源，比如CPU等等，那是怎么预估的呢。这里有个基数的概念，基数是什么呢？基数是一个索引上不同值的个数，索引的基数越大说明索引的区分度越好，扫描的行数可能就越少，索引的基数不是实时变化的，它也是一个统计的结果，那它是怎么统计的呢？
show index from t来查看表t上所有索引的基数
基数的统计 ：基数的统计是采用统计的方法计算的，在统计的时候默认随机取N页数据，统计这N页数据所有不同的数值的个数得到平均值，然后再乘以这个索引的总页数，最后得到基数。数据表里面的数据在不断变更，当变更的数据超过总行数的1/M时会再次触发统计，修正原先统计的基数。至于M和N 到底是多少？在mysql中有两种存储索引的方式，可以通过设置参数innodb_stats_persistent 的值来选择：
设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16
通过基数，优化器可以判断会不会选择该索引(这里猜测有一个阈值)，如果区分度实在是太小则放弃该索引（这就是建议我们不要在区分度不高的列上加索引，因为没啥用），经过第一层过滤，优化器还要判断扫描行数，就是执行计划的rows，这个rows也是一个统计的结果(根据where条件在索引上统计)，看rows是不是太大，如果太大，优化器想我还不如直接全表扫描算了，免得还要回表，所以这里还有一个扫描行数的权衡，到这里我们基本讲完了索引是怎么选择的了。
回到本文的示例，sessionB中的查询语句为什么不走索引a呢？那就是mysql的统计的rows太大，优化器觉得使用索引a不划算 。那为什么mysql的统计的rows太大，是因为 sessionA语句来了就start transaction with consistent snapshot; ，这个语句一执行就创建了视图，根据mvcc规则，它要看见它能看见的数据，sessionB执行删除操作，只是把每行的数据标记为已删除，实际并没有删除(ibd文件大小并没有发生改变)，新插入的数据因为id是递增的不能复用被标记删除的位置，所以通过页分裂的形式把新增的数据放入对应的数据页中，这样在统计rows的时候因为连带被标记删除的行一起统计，导致rows统计的行数偏大，从而进一步导致优化器选择了全表扫描。
这里要补充一下页的知识：
Innodb与磁盘交互的最小单位是页，所以说加载数据到内存是按照页来加载的而不是一行一行加载，索引只能定位到页不能定位到具体哪一行，哪一行只能在页中通过二分法来进行查找
页与页之间是通过双向链表来链接的，页内部的数据行是通过单向链表来链接的。
插入数据可能会导致页的分裂，尤其是非自增主键，从而导致页空洞（就是页的利用率不高）；删除数据会导致数据的空洞，也可能会导致页的合并
页的结构 如附件表所示
那我们知道了原因，如何进行优化呢。主要有以下几种解决方法
-
analyze table，通过该命令重新统计基数信息
analyze.png
-
通过
force index,强制使用某个索引，告诉优化器你不用选择索引了，我就用这个，但是该方案十分的不优雅MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项，我们经常可以看到执行计划的possible_key有几个候选项，然后在候选列表中依次判断每个索引需要扫描多少行。基本上是选择扫描行数最少的作为最后的索引，如果 force index 指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。
-
在不改变语义的情况下，修改sql语句，引导执行计划选择我们想要的索引
-
新建一个更合适的索引或者删除误用或没有的索引
小结
本篇文章我们具体分析了mysql是怎么选择索引的，以及为什么会选错索引，并给出了四种优化方案，谢谢。
附件
页结构表：
页结构.png
网友评论