您现在的位置是：网站首页> 编程资料编程资料

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

2023-05-27 501人已围观

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

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

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

子查询优化策略

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

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

  • semijoin
  • Materialization
  • exists

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

  • Materialization
  • exists

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

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

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

 CREATE TABLE `test02` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB; drop procedure idata; delimiter ;; create procedure idata() begin declare i int; set i=1; while(i<=10000)do insert into test02 values(i, i, i); set i=i+1; end while; end;; delimiter ; call idata(); create table test01 like test02; insert into test01 (select * from test02 where id<=1000)

三、举例分析SQL实例

子查询示例：

 SELECT * FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 10)

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

 SELECT test02.b FROM test02 WHERE id < 10

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

 SELECT * FROM test01 WHERE test01.a IN (1,2,3,4,5,6,7,8,9);

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

 select * from test01 where exists(select b from test02 where id < 10 and test01.a=test02.b);

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

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

 root@localhost [dbtest01]>desc select * from test01 where exists(select b from test02 where id < 10 and test01.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 | Using where | | 2 | DEPENDENT SUBQUERY | test02 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 9 | 10.00 | Using where | +----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec)

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

 SELECT * FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 10)

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

 root@localhost [dbtest01]>desc SELECT * FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 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 | Using where | | 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 | Using where | +----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec)

发现优化器使用到了策略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代表开启此策略)
可以采用show variables like 'optimizer_switch'; 来查看MySQL采用的优化器策略。当然这些策略都是可以在线进行动态修改的
set global optimizer_switch='materialization=on,semijoin=on';代表开启优化策略materialization和semijoin

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

 root@localhost [dbtest01]>show variables like '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 * FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 10)

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

 set global optimizer_switch='materialization=off,semijoin=off';

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

 root@localhost [dbtest01]>desc SELECT * FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 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 | Using where | | 2 | DEPENDENT SUBQUERY | test02 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 9 | 10.00 | Using where | +----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)

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

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

 root@localhost [dbtest01]>desc select * from test01 where exists(select b from test02 where id < 10 and test01.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 | Using where | | 2 | DEPENDENT SUBQUERY | test02 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 9 | 10.00 | Using where | +----+--------------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec)

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

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

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

Semi-join优化器:

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

 select test01.* from test01 join test02 on test01.a=test02.b and test02.id<10;

# 查看此SQL的执行计划:

 desc select test01.* from test01 join test02 on test01.a=test02.b and test02.id<10; root@localhost [dbtest01]>EXPLAIN extended select test01.* from test01 join test02 on test01.a=test02.b and test02.id<10; +----+-------------+--------+------------+-------+---------------+---------+---------+------------
                
                

                

                                            
上一篇：简单了解mysql存储字段类型查询效率_Mysql_

                                                                
下一篇：Mysql NULL导致的神坑_Mysql_

相关内容

点击排行

本栏推荐

猜你喜欢

-六神源码网