MySQL实战——为什么MySQL有时候会选错索引

最新推荐文章于 2024-07-31 10:22:17 发布

PnJg?

最新推荐文章于 2024-07-31 10:22:17 发布

阅读量180

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/pnjght/article/details/127295967

版权

MySQL 专栏收录该内容

19 篇文章 1 订阅

订阅专栏

已知MySQL中一张表是可以支持多个索引的，但是我们写SQL语句的时候并没有主动指定使用哪个索引，也就是说使用哪个索引是由MySQL来确定的。所以可能会出现这种情况：本来一句可以执行的很快的SQL语句却由于MySQL选错了索引导致执行的很慢。

现在有如下的例子：先建一个简单的表，表里有 a、b 两个字段，并分别建上索引：

情况一：

接着往表t中插入10万条记录，取值按整数递增，接下来分析SQL语句：

select * from t where a between 10000 and 20000;

这个语句很简单，a上有索引，使用索引a，而实质上优化器选择的确实也是索引a。扫描的行数是10001行

再做如下操作：session A 开启了一个事务。随后，session B 把数据都删除后，又调用了 idata 这个存储过程，插入了 10 万行数据。

这时候sessionB查询语句select * from t where a between 10000 and 20000就不会选择索引a了，我们可以通过force index (a)来做一个对比

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

通过结果可以看到Q1 扫描了 10 万行，显然是走了全表扫描，执行时间是 40 毫秒。Q2扫描了 10001 行，执行了 21 毫秒。也就是说，我们在没有使用 force index 的时候，MySQL 用错了索引，导致了更长的执行时间。

优化器的逻辑

选择索引是优化器的工作。优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然还会结合是否使用临时表、是否排序等因素。我们上面的例子影响因素仅仅与扫描行数有关。

所以现在的问题是扫描行数是怎么判断的？

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。在上面的例子中，我们通过show index可以发现虽然三个字段的值都一样，但是基数值并不相同且都不准确

那么MySQL是怎么得到索引基数的呢？MySQL采用的是采样统计的方法

为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。

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

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

由于是采样统计，所以不管 N 是 20 还是 8，这个基数都是很容易不准的。

上面得到的基数结果虽然不够精确，但大体上还是差不多，那么索引选错了一定还有别的原因。其实索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句本身要扫描多少行。对于上面执行的两个语句，优化器的预估分别是执行104620和37116行。

Q1的104620是符合预期，而Q2的37116就比一开始执行的那条SQL语句预计的10001偏大了，这个误差导致了优化器的误判。

那就算误差偏大了，为什么游乎其不去扫描行数是37000的执行计划而是去执行100000的计划呢？

这是因为，如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。而如果选择扫描 10 万行，是直接在主键索引上扫描的，没有额外的代价。优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。当然，从执行时间看来，这个选择并不是最优的。

使用普通索引需要把回表的代价算进去，在图 1 执行 explain 的时候，也考虑了这个策略的代价，但图 1 的选择是对的。也就是说，这个策略并没有问题。所以，导致选错了索引，是因为没能准确的判断出扫描行数。

为什么会导致错误的判断呢？

1.为什么没有session A,session B扫描的行数是1W

由于mysql是使用标记删除来删除记录的,并不从索引和数据文件中真正的删除。如果delete和insert中间的间隔相对较小,purge线程还没有来得及清理该记录。如果主键相同的情况下,新插入的insert会沿用之前删除的delete的记录的空间。由于相同的数据量以及表大小,所以导致了统计信息没有变化

2.为什么开启了session A,session B扫描行数变成3W

由于session A开启了一致性读,目的为了保证session A的可重复读,insert只能另起炉灶,不能占用delete的空间。所以出现的情况就是delete虽然删除了,但是未释放空间,insert又增加了空间。导致统计信息有误

可以用analyze table t来重新统计索引信息，这时候结果就对了

情况二：

执行

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

从条件上看，这个查询没有符合条件的记录，因此会返回空集合。a、b两个索引的结构图如下：

如果使用索引 a 进行查询，那么就是扫描索引 a 的前 1000 个值，然后取到对应的 id，再到主键索引上去查出每一行，然后根据字段 b 来过滤。显然这样需要扫描 1000 行。

如果使用索引 b 进行查询，那么就是扫描索引 b 的最后 50001 个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描 50001 行。

执行 explain 的结果如下：

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

于是我们可以知道：扫描行数的估计值依然不准确、这个例子里 MySQL 又选错了索引。

索引选择异常和处理

一种方法是，像我们第一个例子一样，采用 force index 强行选择一个索引。MySQL 会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行。如果 force index 指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。

第二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引。比如，在这个例子里，显然把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。