mysql自带查询优化_MySQL之selectin子查询优化的实现--688IT编程网

mysql⾃带查询优化_MySQL之selectin⼦查询优化的实现下⾯的演⽰基于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,join和in哪个查询更快

`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);

+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

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);

+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+

+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+

| 1 | SIMPLE | test01 | NULL | ref | a | a | 5 | .b | 1 | 100.00 | NULL |

+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+

3 rows in set, 1 warning (0.00 sec)

原因是从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 |

+------------------+---------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------

所以在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);

+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

+----+--------------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

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);

+----+--------------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

+----+--------------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

+----+--------------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

2 rows in set, 2 warnings (0.00 sec)

不相关⼦查询变成了关联⼦查询(select_type:DEPENDENT SUBQUERY)，⼦查询需要根据 b 来关联外表 test01，因为需要外表的

test01 字段，所以⼦查询是没法先执⾏的。执⾏流程为：

扫描 test01，从 test01 取出⼀⾏数据 R；

从数据⾏ R 中，取出字段 a 执⾏⼦查询，如果得到结果为 TRUE，则把这⾏数据 R 放到结果集；

重复 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;

+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+

+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+

| 1 | SIMPLE | test01 | NULL | ref | a | a | 5 | st02.b | 1 | 100.00 | NULL |

+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+

2 rows in set, 2 warnings (0.00 sec)

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

但这⾥会有个问题，join 是有可能得到重复结果的，⽽ in(select ...) ⼦查询语义则不会得到重复值。

⽽ semijoin 正是解决重复值问题的⼀种特殊联接。

在⼦查询中，优化器可以识别出 in ⼦句中每组只需要返回⼀个值，在这种情况下，可以使⽤ semijoin

来优化⼦查询，提升查询效率。

这是 MySQL 5.6 加⼊的新特性，MySQL 5.6 以前优化器只有 exists ⼀种策略来“优化”⼦查询。

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

root@localhost [dbtest01]>desc SELECT * FROM test01 WHERE test01.a IN (SELECT test02.b FROM test02 WHERE id < 10);

+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+

+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+

| 1 | SIMPLE | test01 | NULL | ref | a | a | 5 | .b | 1 | 100.00 | NULL |

+----+--------------+-------------+------------+-------+---------------+---------+---------+---------------+------+----------+-------------+

3 rows in set, 1 warning (0.00 sec)

select

`test01`.`id`,`test01`.`a`,`test01`.`b`

from `test01` semi join `test02`

where

((`test01`.`a` = ``.`b`)

and (`test02`.`id` < 10));

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

semijoin 优化实现⽐较复杂，其中⼜分 FirstMatch、Materialize 等策略，上⾯的执⾏计划中 select_type=MATERIALIZED 就是代表使⽤了 Materialize 策略来实现的 semijoin

这⾥ semijoin 优化后的执⾏流程为：

先执⾏⼦查询，把结果保存到⼀个临时表中，这个临时表有个主键⽤来去重；

从临时表中取出⼀⾏数据 R；

从数据⾏ R 中，取出字段 b 到被驱动表 t1 中去查，满⾜条件则放到结果集；

重复执⾏ 2、3，直到结束。

这样⼀来，⼦查询结果有 9 ⾏，即临时表也有 9 ⾏(这⾥没有重复值)，总的扫描⾏数为 9+9+9*1=27 ⾏，⽐原来的 10000 ⾏少了很多。

MySQL 5.6 版本中加⼊的另⼀种优化特性 materialization，就是把⼦查询结果物化成临时表，然后代⼊到外查询中进⾏查，来加快查询的执⾏速度。内存临时表包含主键(hash 索引)，消除重复⾏，使表更⼩。

如果⼦查询结果太⼤，超过 tmp_table_size ⼤⼩，会退化成磁盘临时表。这样⼦查询只需要执⾏⼀次，⽽不是对于外层查询的每⼀⾏都得执⾏⼀遍。

688IT编程网

mysql自带查询优化_MySQL之selectin子查询优化的实现

发表评论

推荐文章

negotiable certificates of deposits -回复

trandate翻译 -回复

tenoke翻译 -回复

避免合同倒签的方法

cut down the transaction cost

热门文章

Truncate Table

truncate table audit_result_temp; -回复

truncate 语法

truncated incorrect double value 21分钟

truncate()函数

统计学专业辞汇英语翻译

铁塔专业术语英语

大数据英语翻译

A MEANS FOR SAWING ELONGATE UNITS FROM A TREE-TRUN

外文翻译--拥塞控制中的算法

TOC术语中英文对照

conditionalty贸易术语 -回复

termination_date翻译

determined 翻译

truncatedsvd函数作用

tangential翻译

表面处理方面的缺陷术语(Defect Terms)

exterminate翻译

delamination翻译

antiquated翻译

最新文章

negotiable certificates of deposits -回复

teams中presenting duplicate -回复

老同志独断专行处理流程详解

quota share reinsurance contract -回复

attempted to resolve relative to -回复

concurrent modification during iteration dart

标签列表

688IT编程网

mysql自带查询优化_MySQL之selectin子查询优化的实现

发表评论

推荐文章

negotiable certificates of deposits -回复

trandate翻译 -回复

tenoke翻译 -回复

避免合同倒签的方法

cut down the transaction cost

热门文章

Truncate Table

truncate table audit_result_temp; -回复

truncate 语法

truncated incorrect double value 21分钟

truncate()函数

统计学专业辞汇英语翻译

铁塔专业术语 英语

大数据英语翻译

A MEANS FOR SAWING ELONGATE UNITS FROM A TREE-TRUN

外文翻译--拥塞控制中的算法

TOC术语中英文对照

conditionalty贸易术语 -回复

termination_date翻译

determined 翻译

truncatedsvd函数作用

tangential翻译

表面处理方面的缺陷术语(Defect Terms)

exterminate翻译

delamination翻译

antiquated翻译

最新文章

negotiable certificates of deposits -回复

teams中presenting duplicate -回复

老同志独断专行处理流程详解

quota share reinsurance contract -回复

attempted to resolve relative to -回复

concurrent modification during iteration dart

标签列表

铁塔专业术语英语