MySQL之select in 子查询优化的实现

2023-07-16 10:38:03 66

下面的演示基于MySQL5.7.27版本

一、关于MySQL子查询的优化策略介绍:

子查询优化策略

对于不同类型的子查询，优化器会选择不同的策略。

1.对于IN、=ANY子查询，优化器有如下策略选择：

semijoin
Materialization
exists

2.对于NOTIN、<>ALL子查询，优化器有如下策略选择：

Materialization
exists

3.对于derived派生表，优化器有如下策略选择：
derived_merge，将派生表合并到外部查询中（5.7引入）；
将派生表物化为内部临时表，再用于外部查询。
注意：update和delete语句中子查询不能使用semijoin、materialization优化策略

二、创建数据进行模拟演示

为了方便分析问题先建两张表并插入模拟数据：

CREATETABLE`test02`(
`id`int(11)NOTNULL,
`a`int(11)DEFAULTNULL,
`b`int(11)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`a`(`a`)
)ENGINE=InnoDB;

dropprocedureidata;
delimiter;;
createprocedureidata()
begin
declareiint;
seti=1;
while(i<=10000)do
insertintotest02values(i,i,i);
seti=i+1;
endwhile;
end;;
delimiter;
callidata();

createtabletest01liketest02;
insertintotest01(select*fromtest02whereid<=1000)

三、举例分析SQL实例

子查询示例：

SELECT*FROMtest01WHEREtest01.aIN(SELECTtest02.bFROMtest02WHEREid<10)

大部分人可定会简单的认为这个SQL会这样执行：

SELECTtest02.bFROMtest02WHEREid<10

结果:1,2,3,4,5,6,7,8,9

SELECT*FROMtest01WHEREtest01.aIN(1,2,3,4,5,6,7,8,9);

但实际上MySQL并不是这样做的。MySQL会将相关的外层表压到子查询中，优化器认为这样效率更高。也就是说，优化器会将上面的SQL改写成这样：

select*fromtest01whereexists(selectbfromtest02whereid<10andtest01.a=test02.b);

提示：针对mysql5.5以及之前的版本

查看执行计划如下，发现这条SQL对表test01进行了全表扫描1000，效率低下：

root@localhost[dbtest01]>descselect*fromtest01whereexists(selectbfromtest02whereid<10andtest01.a=test02.b);
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|1|PRIMARY|test01|NULL|ALL|NULL|NULL|NULL|NULL|1000|100.00|Usingwhere|
|2|DEPENDENTSUBQUERY|test02|NULL|range|PRIMARY|PRIMARY|4|NULL|9|10.00|Usingwhere|
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
2rowsinset,2warnings(0.00sec)

但是此时实际执行下面的SQL，发现也不慢啊,这不是自相矛盾嘛，别急，咱们继续往下分析:

SELECT*FROMtest01WHEREtest01.aIN(SELECTtest02.bFROMtest02WHEREid<10)

查看此条SQL的执行计划如下：

root@localhost[dbtest01]>descSELECT*FROMtest01WHEREtest01.aIN(SELECTtest02.bFROMtest02WHEREid<10);
+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
|1|SIMPLE||NULL|ALL|NULL|NULL|NULL|NULL|NULL|100.00|Usingwhere|
|1|SIMPLE|test01|NULL|ref|a|a|5|.b|1|100.00|NULL|
|2|MATERIALIZED|test02|NULL|range|PRIMARY|PRIMARY|4|NULL|9|100.00|Usingwhere|
+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
3rowsinset,1warning(0.00sec)

发现优化器使用到了策略MATERIALIZED。于是对此策略进行了资料查询和学习。
https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html

原因是从MySQL5.6版本之后包括MySQL5.6版本，优化器引入了新的优化策略:materialization=[off|on],semijoin=[off|on],(off代表关闭此策略，on代表开启此策略)
可以采用showvariableslike'optimizer_switch';来查看MySQL采用的优化器策略。当然这些策略都是可以在线进行动态修改的
setglobaloptimizer_switch='materialization=on,semijoin=on';代表开启优化策略materialization和semijoin

MySQL5.7.27默认的优化器策略：

root@localhost[dbtest01]>showvariableslike'optimizer_switch';
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Variable_name|Value|
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|optimizer_switch|index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on|
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

所以在MySQL5.6及以上版本时

执行下面的SQL是不会慢的。因为MySQL的优化器策略materialization和semijoin对此SQL进行了优化

SELECT*FROMtest01WHEREtest01.aIN(SELECTtest02.bFROMtest02WHEREid<10)

然而咱们把mysql的优化器策略materialization和semijoin关闭掉测试，发现SQL确实对test01进行了全表的扫描(1000):

setglobaloptimizer_switch='materialization=off,semijoin=off';

执行计划如下test01表确实进行了全表扫描:

root@localhost[dbtest01]>descSELECT*FROMtest01WHEREtest01.aIN(SELECTtest02.bFROMtest02WHEREid<10);
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|1|PRIMARY|test01|NULL|ALL|NULL|NULL|NULL|NULL|1000|100.00|Usingwhere|
|2|DEPENDENTSUBQUERY|test02|NULL|range|PRIMARY|PRIMARY|4|NULL|9|10.00|Usingwhere|
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
2rowsinset,1warning(0.00sec)

下面咱们分析下这个执行计划:

！！！！再次提示:如果是mysql5.5以及之前的版本，或者是mysql5.6以及之后的版本关闭掉优化器策略materialization=off,semijoin=off，得到的SQL执行计划和下面的是相同的

root@localhost[dbtest01]>descselect*fromtest01whereexists(selectbfromtest02whereid<10andtest01.a=test02.b);
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|1|PRIMARY|test01|NULL|ALL|NULL|NULL|NULL|NULL|1000|100.00|Usingwhere|
|2|DEPENDENTSUBQUERY|test02|NULL|range|PRIMARY|PRIMARY|4|NULL|9|10.00|Usingwhere|
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
2rowsinset,2warnings(0.00sec)

不相关子查询变成了关联子查询（select_type:DEPENDENTSUBQUERY），子查询需要根据b来关联外表test01，因为需要外表的test01字段，所以子查询是没法先执行的。执行流程为：

扫描test01，从test01取出一行数据R；
从数据行R中，取出字段a执行子查询，如果得到结果为TRUE，则把这行数据R放到结果集；
重复1、2直到结束。

总的扫描行数为1000+1000*9=10000（这是理论值，但是实际值比10000还少，怎么来的一直没想明白，看规律是子查询结果集每多一行，总扫描行数就会少几行）。

Semi-join优化器:

这样会有个问题，如果外层表是一个非常大的表，对于外层查询的每一行，子查询都得执行一次，这个查询的性能会非常差。我们很容易想到将其改写成join来提升效率：

selecttest01.*fromtest01jointest02ontest01.a=test02.bandtest02.id<10;

#查看此SQL的执行计划:

descselecttest01.*fromtest01jointest02ontest01.a=test02.bandtest02.id<10;

root@localhost[dbtest01]>EXPLAINextendedselecttest01.*fromtest01jointest02ontest01.a=test02.bandtest02.id<10;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
|1|SIMPLE|test02|NULL|range|PRIMARY|PRIMARY|4|NULL|9|100.00|Usingwhere|
|1|SIMPLE|test01|NULL|ref|a|a|5|dbtest01.test02.b|1|100.00|NULL|
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
2rowsinset,2warnings(0.00sec)

这样优化可以让t2表做驱动表，t1表关联字段有索引，查找效率非常高。

但这里会有个问题，join是有可能得到重复结果的，而in(select...)子查询语义则不会得到重复值。
而semijoin正是解决重复值问题的一种特殊联接。
在子查询中，优化器可以识别出in子句中每组只需要返回一个值，在这种情况下，可以使用semijoin来优化子查询，提升查询效率。
这是MySQL5.6加入的新特性，MySQL5.6以前优化器只有exists一种策略来“优化”子查询。

经过semijoin优化后的SQL和执行计划分为：

root@localhost[dbtest01]>descSELECT*FROMtest01WHEREtest01.aIN(SELECTtest02.bFROMtest02WHEREid<10);
+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
|1|SIMPLE||NULL|ALL|NULL|NULL|NULL|NULL|NULL|100.00|Usingwhere|
|1|SIMPLE|test01|NULL|ref|a|a|5|.b|1|100.00|NULL|
|2|MATERIALIZED|test02|NULL|range|PRIMARY|PRIMARY|4|NULL|9|100.00|Usingwhere|
+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+
3rowsinset,1warning(0.00sec)

select
`test01`.`id`,`test01`.`a`,`test01`.`b`
from`test01`semijoin`test02`
where
((`test01`.`a`=``.`b`)
and(`test02`.`id`<10));

##注意这是优化器改写的SQL，客户端上是不能用semijoin语法的

semijoin优化实现比较复杂，其中又分FirstMatch、Materialize等策略,上面的执行计划中select_type=MATERIALIZED就是代表使用了Materialize策略来实现的semijoin
这里semijoin优化后的执行流程为：

先执行子查询，把结果保存到一个临时表中，这个临时表有个主键用来去重；
从临时表中取出一行数据R；
从数据行R中，取出字段b到被驱动表t1中去查找，满足条件则放到结果集；
重复执行2、3，直到结束。
这样一来，子查询结果有9行，即临时表也有9行（这里没有重复值），总的扫描行数为9+9+9*1=27行，比原来的10000行少了很多。

MySQL5.6版本中加入的另一种优化特性materialization，就是把子查询结果物化成临时表，然后代入到外查询中进行查找，来加快查询的执行速度。内存临时表包含主键（hash索引），消除重复行，使表更小。
如果子查询结果太大，超过tmp_table_size大小，会退化成磁盘临时表。这样子查询只需要执行一次，而不是对于外层查询的每一行都得执行一遍。
不过要注意的是，这样外查询依旧无法通过索引快速查找到符合条件的数据，只能通过全表扫描或者全索引扫描，

semijoin和materialization的开启是通过optimizer_switch参数中的semijoin={on|off}、materialization={on|off}标志来控制的。
上文中不同的执行计划就是对semijoin和materialization进行开/关产生的
总的来说对于子查询，先检查是否满足各种优化策略的条件（比如子查询中有union则无法使用semijoin优化）
然后优化器会按成本进行选择，实在没得选就会用exists策略来“优化”子查询，exists策略是没有参数来开启或者关闭的。

下面举一个delete相关的子查询例子：

把上面的2张测试表分别填充350万数据和50万数据来测试delete语句

root@localhost[dbtest01]>selectcount(*)fromtest02;
+----------+
|count(*)|
+----------+
|3532986|
+----------+
1rowinset(0.64sec)
root@localhost[dbtest01]>createtabletest01liketest02;
QueryOK,0rowsaffected(0.01sec)

root@localhost[dbtest01]>insertintotest01(select*fromtest02whereid<=500000)

root@localhost[dbtest01]>selectcount(*)fromtest01;
+----------+
|count(*)|
+----------+
|500000|

执行delete删除语句执行了4s

root@localhost[dbtest01]>deleteFROMtest01WHEREtest01.aIN(SELECTtest02.bFROMtest02WHEREid<10);
QueryOK,9rowsaffected(4.86sec)

查看执行计划，对test01表进行了几乎全表扫描:

root@localhost[dbtest01]>descdeleteFROMtest01WHEREtest01.aIN(SELECTtest02.bFROMtest02WHEREid<10);
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|1|DELETE|test01|NULL|ALL|NULL|NULL|NULL|NULL|499343|100.00|Usingwhere|
|2|DEPENDENTSUBQUERY|test02|NULL|range|PRIMARY|PRIMARY|4|NULL|9|10.00|Usingwhere|
+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
2rowsinset(0.00sec)

于是修改上面的deleteSQL语句伪join语句

root@localhost[dbtest01]>descdeletetest01.*fromtest01jointest02ontest01.a=test02.bandtest02.id<10;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
|1|SIMPLE|test02|NULL|range|PRIMARY|PRIMARY|4|NULL|9|100.00|Usingwhere|
|1|DELETE|test01|NULL|ref|a|a|5|dbtest01.test02.b|1|100.00|NULL|
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
2rowsinset(0.01sec)

执行非常的快
root@localhost[dbtest01]>deletetest01.*fromtest01jointest02ontest01.a=test02.bandtest02.id<10;
QueryOK,9rowsaffected(0.01sec)

root@localhost[dbtest01]>selecttest01.*fromtest01jointest02ontest01.a=test02.bandtest02.id<10;
Emptyset(0.00sec)

下面的这个表执行要全表扫描，非常慢，基本对表test01进行了全表扫描:

root@lcalhost[dbtest01]>descdeleteFROMtest01WHEREidIN(SELECTidFROMtest02WHEREid='350000');
+----+--------------------+--------+------------+-------+---------------+---------+---------+-------+--------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+--------------------+--------+------------+-------+---------------+---------+---------+-------+--------+----------+-------------+
|1|DELETE|test01|NULL|ALL|NULL|NULL|NULL|NULL|499343|100.00|Usingwhere|
|2|DEPENDENTSUBQUERY|test02|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|Usingindex|
+----+--------------------+--------+------------+-------+---------------+---------+---------+-------+--------+----------+-------------+
2rowsinset(0.00sec)

然而采用join的话，效率非常的高:

root@localhost[dbtest01]>descdeletetest01.*FROMtest01innerjointest02WHEREtest01.id=test02.idandtest02.id=350000;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|1|DELETE|test01|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|NULL|
|1|SIMPLE|test02|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|Usingindex|
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2rowsinset(0.01sec)


root@localhost[dbtest01]>descdeletetest01.*fromtest01jointest02ontest01.a=test02.bandtest02.id=350000;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|1|SIMPLE|test02|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|NULL|
|1|DELETE|test01|NULL|ref|a|a|5|const|1|100.00|NULL|
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2rowsinset(0.00sec)

参考文档:

https://www.cnblogs.com/zhengyun_ustc/p/slowquery1.html
https://www.jianshu.com/p/3989222f7084
https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html

到此这篇关于MySQL之selectin子查询优化的实现的文章就介绍到这了,更多相关MySQLselectin子查询优化内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

MySQL之select in 子查询优化的实现

热门推荐

随机推荐