《MySQL实战45讲》——学习笔记10 “优化器如何选索引、选错索引“

亦凉 2023-09-24 12:51 116阅读 0赞

#### MYSQL优化器 ####

![ec0380f95d084874b54a0c78e25a16a2.png][]

优化器位于MySQL的Server层，位于分析器与执行器之间，其主要作用是执行计划的生成以及索引选择；

在表里面有多个索引的时候，优化器决定**使用哪个索引**；或者在一个语句有多表关联（join）的时候，**决定各个表的连接顺序**，尽量扫描更少的数据；

#### 索引选错的例子 ####

先建一个简单的表，表里有 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)；

接下来，分析一条SQL语句的执行计划：

explain select * from t where a between 10000 and 20000;

![0d8abb50141aeee18efb3e7333afc048.png][]

**这条查询语句的执行也确实符合预期**，索引选择上，key这个字段值是a，表示优化器选择了索引a；

接下来看下**"索引选错"**的场景，在已经准备好的包含了10万行数据的表上，我们再做如下操作：

![980962a3f7d34b428f0b3f84fa5155c5.png][]

sessionA就是真正开启了一个事务（一致性视图），在sessionA提交之前，sessionB把表里当前全部数据都删除后，又重新插入了之前的10万行数据，随后执行一条跟上面一样的查询语句；**我们的预期是依然走索引a，但实际却走的全表扫描；**

为方便对比，这里的查询语句分别使用下面Q1和Q2，分别通过慢日志分析下2种语句的执行情况；解释下语句：  
第一句，是将慢查询日志的阈值设置为0，表示这个线程接下来的语句都会被记录入慢查询日志中；  
第二句，Q1是sessionB原来的查询；  
第三句，Q2是加了force index(a)来和sessionB原来的查询语句执行情况对比；

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

慢查询日志如下：

![0a6077f7ec347cfedb94f763b458495d.png][]

可以看到：

1. Q1扫描了10万行，显然是走了全表扫描，执行时间是40毫秒；  
2. Q2扫描了10001行，执行了21毫秒；

也就是说，我们在**没有使用force index的时候，MySQL用错了索引，导致了更长的执行时间**；这个例子对应的是我们平常不断地删除历史数据和新增数据的场景；这时，**MySQL竟然会选错索引，是不是有点奇怪呢？**下面就来看下MYSQL的优化器选索引的逻辑；

#### 选择索引 ####

选择索引是优化器的工作；而优化器选择索引的目的，是**找到一个最优的执行方案，并用最小的代价去执行语句**；在数据库里面，扫描行数是影响执行代价的因素之一；扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少；

当然，扫描行数并不是唯一的判断标准，总的来说优化器会通过以下几个因素进行综合判断选择索引：

> **1. 扫描行数** 
> 
> **2. 索引基数** 
> 
> **3. 是否使用临时表**
> 
> **4. 是否排序**

上面的例子中，那个简单的查询语句并没有涉及到临时表和排序，所以MySQL选错索引肯定是在判断扫描行数或索引基数的时候出问题了；扫描行数很好理解，就是查询满足条件的记录需要扫描的记录数；而索引基数（cardinality）就是一个索引上不同的值的个数；

#### 索引的基数 cardinality ####

显然，一个索引上不同的值越多，这个索引的区分度就越好；例如，使用一个"性别"索引，索引等值查询时，区分度只有50%，意味着使用当前索引只帮你筛除了50%的记录，接下来你要对剩下50%的数据全部扫描一遍；而如使用"身份证号"索引时，记录可以被"精准定位"，因为最多只有一条记录；

**MySQL如何得到索引基数呢？**

把整张表取出来逐行统计，虽然结果精确，但代价太高；因此MySQL通过采样统计估算索引的基数：

> （1）InnoDB默认会选择N个数据页，统计这些页面上索引的不同值，得到一个平均值，然后乘以这个索引的总页面数，就得到了这个索引的基数；
> 
> （2）由于数据表是会持续更新的，索引统计信息也需要更新，所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计；
> 
> （3）以上M和N的值通过设置MySQL参数innodb\_stats\_persistent的值确定；

![b141e8acd194e5fc5626da93a7d62577.png][]

通过 **show index from t** 命令，看到表t的索引的基数；这次的索引统计值（cardinality列）虽然不够精确（三个索引的基数的准确值是10万），但大体上还是差不多的；所以**本次选错索引与索引基数无关**；

#### 扫描行数 ####

再看下优化器预估的这两个语句的扫描行数，图中rows列表示的是预计扫描行数；

![d5a35403f8ed1a646b59f0b083b8dd80.png][]

由图可知，Q1的结果还是符合预期的（全表扫描10W行数据），rows的值是104620；但是Q2的rows值是37116，偏差就大了，预期走索引a的rows值为10001；

现在有2个问题：

> （1）为什么优化器放着扫描 37000 行的执行计划不用，却选择了扫描行数是 100000 的执行计划呢？
> 
> （2）为什么强制走索引a时的预估扫描行数rows的值不是预期的1W行？

**问题一**：如果使用索引a，每次从索引a上拿到一个值，都要回到主键索引上查出整行数据（二级索引需要回表查完整数据），这个代价优化器也要算进去的；而如果选择全表扫描10万行，是直接在主键索引上扫描的，没有额外的回表代价；从结果看来，优化器认为直接扫描主键索引更快，尽管这个选择事实上并不是最优的；

**问题二**：解释完问题一，可知关键点还是在于走索引a时估计的扫描行数不准确；为什么会不准确呢？——下面来分析下：

> session B的delete语句删掉了所有的数据，然后再通过callidata()重新插入了10W行数据，看上去是覆盖了原来的10W行；但是session A在此之前开启了事务并没有提交，**所以之前的10W行数据是不能删除的，即之前的10W行数据每一行数据都有两个版本**，旧版本是delete之前的数据，新版本是标记为deleted的数据；
> 
> ——**这样，索引a上的数据实际就有两份**，因此索引a的扫描行数变成2W；再考虑非顺序插入导致的**页分裂**的情况，**页利用率减小，按照页采样统计就不准**；综上，优化器估算出的3.7W行与我们的预期1W行扫描行数相去甚远；

关于上述"索引a上的数据实际就有两份"的描述，可能存在疑问，既然a索引有2分，按道理主键上的数据也不能删，为什么走全表扫面时，优化器估算的扫描行数是准确的10W呢？原因是主键是直接按照表的行数来估计的，而非采样统计；而表的行数，优化器直接用的是 **show table status 命令**的值，这里不做详细展开；下面是 **show table status** 的结果：

![ece0ab59111827836448aa87522f2ad3.png][]

#### 执行计划 explain 的结果预估的 rows 值跟实际情况差距比较大该如何处理？ ####

> **就结论而言，上述示例中MySQL选错索引是因为优化器没能准确地判断出扫描行数****；**

既然是统计信息不对，那就修正统计信息；使用 **analyze table t 命令**，可以用来重新统计索引信息；（这里不展开说明 analyze table t 命令的原理，仅介绍解决方案）

![e27392962b8c103a14b0c253d8fd5193.png][]

看一下执行效果：走索引a时估计的扫描行数现在正确了；所以在**实践中，如果发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用analyze tablet命令来处理**；

#### 避免排序 ####

除了索引基数和预估扫描行数，优化器选择索引还会关注**排序**和临时表；下面举个例子——由于加了排序命令导致的优化器选错索引的情况；

同样的这张10W数据(1,1) (2,2)...(100000,100000)的表t，执行下面的SQL：

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

我们的预期是，如果走索引a需要扫描1000行数据并回表判断条件b，如果走索引b需要扫描50000行数据并回表判断条件a；因此选索引a的执行效率更高；但是MYSQL优化器再次选错了索引——

![e633192843956ce86171ba68e6257449.png][]

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

分析下原因，问题就出在这个按照b字段排序上，因为优化器认为使用索引b可以避免排序（b本身是索引，已经是有序的了，如果选择索引b的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小；但是，这个代价真的更小吗？看看下面分别走索引a和b的慢日志：

![bc284a4ba1da5dd4871f2be372bb6858.png][]

可以看到，原本语句需要执行2.23秒，而当使用 force index (a) 的时候，只用了0.05秒，比优化器的选择快了40多倍；也就是说，优化器没有选择正确的索引，**force index 命令起到了“矫正”的作用**；

#### 索引选择异常的处理方法 ####

其实大多数时候优化器都能找到正确的索引，但偶尔由于一些查询条件排序、索引统计信息不准等原因，还是会碰到我们上面举例的情况：原本可以执行得很快的SQL语句，执行速度却比你预期的慢很多，原因是MYSQL优化器选错了索引；下面是优化器索引选择异常的处理方案：

（1）对于由于索引统计信息不准确导致的问题，你可以用 **analyze table** 来解决；

（2）由于临时表，排序字段，导致优化器误判，此种情况可用 **force index** 来强行指定索引，也可以通过修改语句引导优化器，还可以通过增加或者删除索引来绕过这个问题；当然，**force index** 也存在弊端，很多开发者不喜欢使用 force index，一来这么写不优美，二来如果索引改了名字，这个语句也得改，显得很麻烦，而且如果以后迁移到别的类型数据库中间件的话，这个语法还可能会不兼容；

下篇文章：[《MySQL实战45讲》——学习笔记11 “字符串加索引、前缀索引“][MySQL_45_11]

本章参考：[10 | MySQL为什么有时候会选错索引？-极客时间][10 _ MySQL_-]

[ec0380f95d084874b54a0c78e25a16a2.png]: https://img-blog.csdnimg.cn/ec0380f95d084874b54a0c78e25a16a2.png
[0d8abb50141aeee18efb3e7333afc048.png]: https://img-blog.csdnimg.cn/img_convert/0d8abb50141aeee18efb3e7333afc048.png
[980962a3f7d34b428f0b3f84fa5155c5.png]: https://img-blog.csdnimg.cn/980962a3f7d34b428f0b3f84fa5155c5.png
[0a6077f7ec347cfedb94f763b458495d.png]: https://img-blog.csdnimg.cn/img_convert/0a6077f7ec347cfedb94f763b458495d.png
[b141e8acd194e5fc5626da93a7d62577.png]: https://img-blog.csdnimg.cn/img_convert/b141e8acd194e5fc5626da93a7d62577.png
[d5a35403f8ed1a646b59f0b083b8dd80.png]: https://img-blog.csdnimg.cn/img_convert/d5a35403f8ed1a646b59f0b083b8dd80.png
[ece0ab59111827836448aa87522f2ad3.png]: https://img-blog.csdnimg.cn/img_convert/ece0ab59111827836448aa87522f2ad3.png
[e27392962b8c103a14b0c253d8fd5193.png]: https://img-blog.csdnimg.cn/img_convert/e27392962b8c103a14b0c253d8fd5193.png
[e633192843956ce86171ba68e6257449.png]: https://img-blog.csdnimg.cn/img_convert/e633192843956ce86171ba68e6257449.png
[bc284a4ba1da5dd4871f2be372bb6858.png]: https://img-blog.csdnimg.cn/img_convert/bc284a4ba1da5dd4871f2be372bb6858.png
[MySQL_45_11]: https://blog.csdn.net/minghao0508/article/details/127602979
[10 _ MySQL_-]: https://time.geekbang.org/column/article/71173