MySQL：MySQL是如何通过各种规则去优化执行计划的

最新推荐文章于 2024-01-02 11:28:00 发布

OceanStar的学习笔记

最新推荐文章于 2024-01-02 11:28:00 发布

阅读量207

点赞数

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

原文链接：https://time.geekbang.org/

版权

289 篇文章 20 订阅

订阅专栏

MySQL在生成具体执行计划的时候，会根据成本计算去选择最优的执行计划，因为每个查询执行的时候实际都可能有多种执行计划可以选择，必须要选择成本最低的那种

MySQL在执行一些相对较为复杂的SQL语句的时候会对查询进行重写来优化具体的执行计划，因为它有可能可能会觉得你的SQL写的一点都不好，直接按你的SQL生成的执行计划效率不够高，需要自动帮你改改

常量替换

首先，要是MySQL觉得你的SQL里有很多括号，那么无关紧要的括号它会给你删除了。
其次，比如你有类似于i = 5 and j > i这样的SQL，就会改为i = 5 and j > 5，做一个常量替换
还有比如x = y and y = k and k = 3,这样的SQL，都会给你优化成x=3 and y = 3 and k = 3，本质也是做常量替换。或者类似什么b=b and a=a这样一看就是乱写的SQL，一看就没有意义的，就直接删了

这些SQL的改写，本质都是在优化SQL语句的清晰语句，方便后继在索引和数据页里进行查找。

还有一些比较有意思的改写

比如select * from t1 join t2 on t1.x1=t2.x1 and t1.id=1，这个SQL明显是针对t1表的id主键进行了查询，同时还要把t2表进行关联。
其实这个SQL语句就可能在执行前就先查询t1表的id=1的数据，然后直接做一个替换，把SQL替换为：select t1表中id=1的那行数据的各个字段的常量值, t2.* from t1 join t2 on t1表里x1字段的常量值=t2.x1
上面的SQL就是直接把t1相关的字段都替换成了提前查出来的id=1那行数据的字段常量值了

类似:

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

这是一个典型的子查询。

也就是说上面的SQL语句在执行的时候，其实会拆分成两个步骤：

这个SQL语句最核心的就是单表查询的几种执行方式，其他的多表关联，子查询，这些都是差不多这个意思。最多就是在排序、分组聚合的时候，可能有的时候会直接用上索引，有的时候用不上索引就会基于内存或者临时磁盘文件执行。

还有一种子查询是：

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

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

接下来我们来重点分析一下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，把查出来的数据都写入一个临时表里，也叫做物化表。
这个物化表可能会基于memory存储引擎来通过内存存放，如果结果集太大，则可能采用普通的B+树聚簇索引的方式放在磁盘里。但是无论如何，这个物化表都会建立索引，所以这波中间结果数据写入物化表是有索引的。
接着大家可能会想，此时是不是全表扫描t1表，对每条数据的x1值都去物化表里根据索引快速查找一下是否在这个物化表里？如果是的话，那么就符合条件了。但是这里还有一个优化的点，那就是他可以反过来思考。
也就是说，假设t1表的数据量是10万条，而物化表的数据量只有500条，那么此时完全可以改成全表扫描物化表，对每个数据值都到t1表里根据x1这个字段的索引进行查找，查找物化表的这个值是否在t1表的x1索引树里，如果在的话，那么就符合条件了。

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

MySQL对子查询的执行计划还有另外一种优化方式，就是semi join，也就是半连接

就是假设你有一个子查询语句：select * from t1 where x1 in (select x2 from t2 where x3=xxx)，此时其实可能会在底层把他转化为一个半连接，有点类似于下面的样子：

select t1.* from t1 semi join t2 on t1.x1=t2.x2 and t2.x3=xxx

当然，其实并没有提供semi join这种语法，这是MySQL内核里面使用的一种方式，上面就是给大家说那么个意思，其实上面的semi join的语义，是和IN语句+子查询的语义完全一样的，他的意思就是说，对于t1表而言，只要在t2表里有符合t1.x1=t2.x2和t2.x3=xxx两个条件的数据就可以了，就可以把t1表的数据筛选出来了。