来源：自学PHP网 时间：2020-09-21 11:37 作者：小飞侠 阅读:次
[导读] MySQL之select in 子查询优化的实现...
|
今天带来MySQL之select in 子查询优化的实现教程详解
下面的演示基于MySQL5.7.27版本 一、关于MySQL子查询的优化策略介绍: 子查询优化策略 对于不同类型的子查询，优化器会选择不同的策略。 1. 对于 IN、=ANY 子查询，优化器有如下策略选择：
2. 对于 NOT IN、<>ALL 子查询，优化器有如下策略选择：
3. 对于 derived 派生表，优化器有如下策略选择： 二、创建数据进行模拟演示 为了方便分析问题先建两张表并插入模拟数据： 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 | 发现优化器使用到了策略MATERIALIZED。于是对此策略进行了资料查询和学习。 原因是从MySQL5.6版本之后包括MySQL5.6版本，优化器引入了新的优化策略:materialization=[off|on],semijoin=[off|on],(off代表关闭此策略，on代表开启此策略) 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 字段，所以子查询是没法先执行的。执行流程为：
总的扫描行数为 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; +----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+ | 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 | Using where | | 1 | SIMPLE | test01 | NULL | ref | a | a | 5 | dbtest01.test02.b | 1 | 100.00 | NULL | +----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec) 这样优化可以让 t2 表做驱动表，t1 表关联字段有索引，查找效率非常高。 但这里会有个问题，join 是有可能得到重复结果的，而 in(select ...) 子查询语义则不会得到重复值。 经过 semijoin 优化后的 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 | select `test01`.`id`,`test01`.`a`,`test01`.`b` from `test01` semi join `test02` where ((`test01`.`a` = ` ##注意这是优化器改写的SQL，客户端上是不能用 semi join 语法的 semijoin 优化实现比较复杂，其中又分 FirstMatch、Materialize 等策略,上面的执行计划中 select_type=MATERIALIZED 就是代表使用了 Materialize 策略来实现的 semijoin 先执行子查询，把结果保存到一个临时表中，这个临时表有个主键用来去重； MySQL 5.6 版本中加入的另一种优化特性 materialization，就是把子查询结果物化成临时表，然后代入到外查询中进行查找，来加快查询的执行速度。内存临时表包含主键（hash 索引），消除重复行，使表更小。 semijoin 和 materialization 的开启是通过 optimizer_switch 参数中的 semijoin={on|off}、materialization={on|off} 标志来控制的。 下面举一个delete相关的子查询例子： 把上面的2张测试表分别填充350万数据和50万数据来测试delete语句 root@localhost [dbtest01]>select count(*) from test02; +----------+ | count(*) | +----------+ | 3532986 | +----------+ 1 row in set (0.64 sec) root@localhost [dbtest01]>create table test01 like test02; Query OK, 0 rows affected (0.01 sec) root@localhost [dbtest01]>insert into test01 (select * from test02 where id<=500000) root@localhost [dbtest01]>select count(*) from test01; +----------+ | count(*) | +----------+ | 500000 | 执行delete删除语句执行了4s root@localhost [dbtest01]>delete FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 10); Query OK, 9 rows affected (4.86 sec) 查看 执行计划，对test01表进行了几乎全表扫描: root@localhost [dbtest01]>desc delete 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 | DELETE | test01 | NULL | ALL | NULL | NULL | NULL | NULL | 499343 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | test02 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 9 | 10.00 | Using where | +----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ 2 rows in set (0.00 sec) 于是修改上面的delete SQL语句伪join语句 root@localhost [dbtest01]>desc delete test01.* from test01 join test02 on test01.a=test02.b and test02.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 | Using where | | 1 | DELETE | test01 | NULL | ref | a | a | 5 | dbtest01.test02.b | 1 | 100.00 | NULL | +----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+ 2 rows in set (0.01 sec) 执行非常的快 root@localhost [dbtest01]>delete test01.* from test01 join test02 on test01.a=test02.b and test02.id<10; Query OK, 9 rows affected (0.01 sec) root@localhost [dbtest01]>select test01.* from test01 join test02 on test01.a=test02.b and test02.id<10; Empty set (0.00 sec) 下面的这个表执行要全表扫描，非常慢，基本对表test01进行了全表扫描: root@lcalhost [dbtest01]>desc delete FROM test01 WHERE id IN (SELECT id FROM test02 WHERE id='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 | Using where | | 2 | DEPENDENT SUBQUERY | test02 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+--------------------+--------+------------+-------+---------------+---------+---------+-------+--------+----------+-------------+ 2 rows in set (0.00 sec) 然而采用join的话，效率非常的高: root@localhost [dbtest01]>desc delete test01.* FROM test01 inner join test02 WHERE test01.id=test02.id and test02.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 | Using index | +----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 2 rows in set (0.01 sec) root@localhost [dbtest01]> desc delete test01.* from test01 join test02 on test01.a=test02.b and test02.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 | +----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 2 rows in set (0.00 sec) 参考文档: https://www.cnblogs.com/zhengyun_ustc/p/slowquery1.html 到此这篇关于MySQL之select in 子查询优化的实现的文章就介绍到这了,更多相关MySQL select in 子查询优化内容请搜索自学php网以前的文章或继续浏览下面的相关文章希望大家以后多多支持自学php网！ 以上就是关于MySQL之select in 子查询优化的实现全部内容,感谢大家支持自学php网。 |
自学PHP网专注网站建设学习,PHP程序学习,平面设计学习，以及操作系统学习
京ICP备14009008号-1@版权所有www.zixuephp.com
网站声明：本站所有视频，教程都由网友上传，站长收集和分享给大家学习使用，如由牵扯版权问题请联系站长邮箱904561283@qq.com
