Scott's world.

MySQL-错误选择索引笔记

Word count: 1.8kReading time: 6 min
2019/11/15 Share

MySQL-错误选择索引笔记

img

当我们在开启没有sessionA之前,进行

select * from t where a between 10000 and 20000;

其用explain命令可以看到,优化器选择了索引a,且查询行数也在10000左右

img

在开始了sessionA并在未提交之前执行sessionB,其中idata的作用是往表里面插入10万行数据

这时候,sessionB的查询语句就不会在选择索引a了,我们可以通过慢查询日志(slow log)来查看一下具体的执行情况

若执行下面三个语句来进行一个实验

1
2
3
set long_query_time=0; /*将慢查询日志的阈值设置为0,表示这个线程接下来的语句都会被记录入慢查询日志中;*/
select * from t where a between 10000 and 20000; /*session B原来的查询;*/
select * from t force index(a) where a between 10000 and 20000;/*加了force index(a)来和session B原来的查询语句执行情况对比*/

则执行结果如图所示img

可以看到第二句在没有强制使用索引a时执行的是全表扫描即扫描了10万行,但强制使用索引a的语句则只扫描了1万左右

这也就是说MySQL在此场景下,选错了索引

优化器逻辑

选择索引是优化器的工作,是为了寻找一个最优的方案,并用最小的代价去执行语句

在数据库里面,扫描行数是影响执行代价的因素之一,优化器会结合是否使用临时表,是否排序等因素来进行综合判断

MySQL在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数,这个统计信息也就是索引的区分度

一个索引上不同的值的个数被称为基数(caradinality),即基数越大,索引的区分度越好

查看索引的基数,可通过show index

采样统计

为了不进行全表扫描因为其代价过高,则MySQL会通过采样统计的方法来获取索引的基数

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

数据表会持续更新,所以索引统计信息也不会固定不变.

在变更的数据行数超过1/M的时候,会自动触发重新做一次索引统计.

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

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

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

在前面我们可以看到优化器预估的两个sql语句的扫描行数

img

优化器为什么放着扫描37000行的执行计划不用,却选择了扫描行数是100000的执行计划呢?

这是因为,如果使用索引a,每次从索引a上拿到一个值,都要回到主键索引上查出整行数据,这个代价优化器也要算进去的

而如果选择扫描10万行,是直接在主键索引上扫描的,没有额外的代价

优化器会估算这两个选择的代价,从结果看来,优化器认为直接扫描主键索引更快。当然,从执行时间看来,这个选择并不是最优。

1.为什么没有session A,session B扫描的行数是1W
由于mysql是使用标记删除来删除记录的,并不从索引和数据文件中真正的删除。
如果delete和insert中间的间隔相对较小,purge线程还没有来得及清理该记录。
如果主键相同的情况下,新插入的insert会沿用之前删除的delete的记录的空间。
由于相同的数据量以及表大小,所以导致了统计信息没有变化
2.为什么开启了session A,session B使用索引a扫描行数变成3W
由于session A开启了一致性读,目的为了保证session A的可重复读,insert只能另起炉灶,不能占用delete的空间。所以出现的情况就是delete虽然删除了,但是未释放空间,insert又增加了空间。导致统计信息有误

3.为什么开启了sessionA,session B使用了主键扫描行数变成了10W

由于sessionA开启了一致性读,我们知道优化器将普通索引会每拿到一个值便会到主键索引上查出整行数据所以,这个代价在此环境下可能并没有直接使用主键的效率高或者代价低,当然从结果上来看,执行时间并不是最优

修正统计信息

既然上面说到统计信息不对,那就使用analyze table t命令,可以用来重新统计索引信息。

img

即如果发现explain的结果预估的rows值跟实际情况比较大,就可以用这个方法来进行处理。

其实,如果只是索引统计不准确,通过analyze命令可以解决很多问题,但是前面我们说了,优化器可不止是看扫描行数。

索引选择异常和处理

  • 采用force index强行选择一个索引,不再评估其他索引的执行代价。

  • 考虑修改语句,引导MySQL使用我们期待的索引。

    比如,在这个例子里,显然把“order by b limit 1” 改成 “order by b,a limit 1” ,语义的逻辑是相同的。

    我们来看看改之后的效果:

    img

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

    当然,这种修改并不是通用的优化手段,只是刚好在这个语句里面有limit 1,因此如果有满足条件的记录, order by b limit 1和order by b,a limit 1 都会返回b是最小的那一行,逻辑上一致,才可以这么做。

  • 在有些场景下,我们可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引

    不过,在这个例子中,我没有找到通过新增索引来改变优化器行为的方法。这种情况其实比较少,尤其是经过DBA索引优化过的库,再碰到这个bug,找到一个更合适的索引一般比较难。

CATALOG
  1. 1. MySQL-错误选择索引笔记
    1. 1.1. 优化器逻辑
      1. 1.1.1. 采样统计
      2. 1.1.2. 修正统计信息
    2. 1.2. 索引选择异常和处理