当前位置：首页 > Mysql > 正文内容

98 MySQL是如何基于各种规则去优化执行计划的？（中）

CarlZhou3年前 (2021-05-11)Mysql871

今天我们来讲一下子查询是如何执行的，以及他的执行计划是如何优化的。比如说类似于下面的SQL语句：

select * from t1 where x1 = (select x1 from t2 where id=xxx)

这就是一个典型的子查询

也就是说上面的SQL语句在执行的时候，其实会被拆分为两个步骤：第一个步骤先执行子查询，也就是：select x1 from t2 where id=xxx，直接根据主键定位出一条数据的x1字段的值。接着再执行select * from t1 where x1=子查询的结果值，这个SQL语句。

这个第二个SQL执行，其实也无非就是跟之前讲的单表查询的方式是一样的，其实大家看到最后会发现，这个SQL语句最核心的就是单表查询的几种执行方式，其他的多表关联，子查询，这些都是差不多这个意思。

最多就是在排序、分组聚合的时候，可能有的时候会直接用上索引，有的时候用不上索引就会基于内存或者临时磁盘文件执行。

另外还有一种子查询，就是：

select * from t1 where x1 = (select x1 from t2 where t1.x2=t2.x2)

这种时候，你会发现子查询里的where条件依赖于t1表的字段值，所以这种查询就会效率很低下，他需要遍历t1表里每一条数据，对每一条数据取出x2字段的值，放到子查询里去执行，找出t2表的某条数据的x1字段的值，再放到外层去判断，是否符合跟t1表的x1字段匹配。

其实大家只要理解透彻了前面的内容，现在看这些SQL语句的执行原理都是比较简单的，并没有什么新意，那么接着我们就重点来讲讲这个子查询执行的时候，执行计划上会有哪些优化的规则。

今天我们重点来讲一下IN语句结合子查询的一个优化手段，假设有如下的一个SQL语句：

select * from t1 where x1 in (select x2 from t2 where x3=xxx)

这个SQL语句就是典型的一个子查询运用，子查询查一波结果，然后判断t1表哪些数据的x1值在这个结果集里。

这个可能大家会想当然的认为先执行子查询，然后对t1表再进行全表扫描，判断每条数据是否在这个子查询的结果集里，但是这种方式其实效率是非常低下的。

所以其实对于上述的子查询，执行计划会被优化为，先执行子查询，也就是select x2 from t2 where x3=xxx这条SQL语句，把查出来的数据都写入一个临时表里，也可以叫做物化表，意思就是说，把这个中间结果集进行物化。

这个物化表可能会基于memory存储引擎来通过内存存放，如果结果集太大，则可能采用普通的b+树聚簇索引的方式放在磁盘里。但是无论如何，这个物化表都会建立索引，所以大家要清楚，这波中间结果数据写入物化表是有索引的。

接着大家可能会想，此时是不是全表扫描t1表，对每条数据的x1值都去物化表里根据索引快速查找一下是否在这个物化表里？如果是的话，那么就符合条件了。但是这里还有一个优化的点，那就是他可以反过来思考。

也就是说，假设t1表的数据量是10万条，而物化表的数据量只有500条，那么此时完全可以改成全表扫描物化表，对每个数据值都到t1表里根据x1这个字段的索引进行查找，查找物化表的这个值是否在t1表的x1索引树里，如果在的话，那么就符合条件了。

所以基于IN语句的子查询执行方式，实际上会在底层被优化成如上所述。

扫描二维码推送至手机访问。

版权声明：本文由周飞的博客发布，如需转载请注明出处。

本文链接：http://szv5.cn/?id=115

返回列表

上一篇：97 MySQL是如何基于各种规则去优化执行计划的？（上）

下一篇：99 MySQL是如何基于各种规则去优化执行计划的？（下）

相关文章

20 对于LRU链表中尾部的缓存页，是如何淘汰他们刷入磁盘的？

20 对于LRU链表中尾部的缓存页，是如何淘汰他们刷入磁盘的？

1、Buffer Pool的缓存页以及几个链表的使用回顾接着我们来讲讲，你的Buffer Pool在运行中被使用的时候，实际上会频繁的从磁盘上加载数据页到他的缓存页里去，然后free链表、flush链表、lru链表都会在使用的时候同时被使用...

23 生产经验：在生产环境中，如何基于机器配置来合理设置Buffer Pool？

1、生产环境中应该给buffer pool设置多少内存？今天这篇文章我们接着上一次讲解的Buffer Pool的一些内存划分的原理，来给大家最后总结一下，在生产环境中到底应该如何设置Buffer Pool的大小呢。首先考虑第一个问题...

33 MySQL数据库的日志顺序读写以及数据文件随机读写的原理

33 MySQL数据库的日志顺序读写以及数据文件随机读写的原理

之前我们花了很多篇幅去讲解MySQL的底层数据存储结构，其实那些知识是极为枯燥的，因为大部分时候，MySQL在底层如何存储数据的一些细节，比如什么数据头、附加信息之类的极为复杂，大家直接那么研究是很痛苦的。所以我之前也就初步的给大家介绍...

37 案例实战：RAID锂电池充放电导致的MySQL数据库性能抖动的优化

37 案例实战：RAID锂电池充放电导致的MySQL数据库性能抖动的优化

前面经过了几天的生产经验的一些铺垫，包括MySQL磁盘读写的机制，Linux存储系统的原理，RAID磁盘阵列的介绍，RAID锂电池定时充放电的原理，今天终于可以切入真正的生产案例的讲解了！其实只要大家理解了之前的内容，今天真正讲解案例的...

40 重新回顾redo日志对于事务提交后，数据绝对不会丢失的意义

40 重新回顾redo日志对于事务提交后，数据绝对不会丢失的意义

之前我们在给大家介绍了大量的MySQL底层原理知识之后，理论结合实践，给大家讲解了两个真实的生产环境的数据库优化案例，一个是数据库所在服务器的RAID存储系统的锂电池充放电导致的性能抖动问题，一个是数据库底层的linux操作系统的文件句柄限...

01 天天写CRUD，你知道你的系统是如何跟MySQL打交道的吗？

01 天天写CRUD，你知道你的系统是如何跟MySQL打交道的吗？

1、Java工程师眼中的数据库是什么东西？从今天开始，我们将要开始一个MySQL的专栏，一起来研究MySQL数据库的底层原理和各种实践案例，以及互联网公司的技术方案。现在我们先来看看，在一个Java工程师眼中的数据库是什么东西？...

发表评论