mysql 5.6 子查询_mysql子查询的缺陷以及5.6的优化

分类

子查询分为from clause和where clause，在执行计划的select_type分别显示为derived和[dependent] subquery;

根据和外部查询的依赖关系又可分为两种，

相关子查询：子查询依赖外层连接的返回值；

非相关子查询：子查询不依赖外层连接的返回值；

缺陷

pre-5.6通常会将非相关子查询改为相关子查询，即先遍历outer table，对于其返回的每一条记录都执行一次subquery；

注：mysql目前只实现nested-loop join，所以dependent subquery才会如此消耗资源，如果是oracle则可进行semi/antihash join

SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

有可能被重写为

SELECT * FROM t1 WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

Pre-5.6优化

Mysql可以将from clause的子查询进行物化，此时先执行inner query并将结果存于临时表

select * from subcategory

where id in (

select c.id

from subcategory as c

inner join item as i on i.subcategory = c.id

where c.category = 14

group by c.id

having count(*) > 2000

);

此时mysql optimizer自作聪明的将非相关子查询改写为相关子查询，执行计划如下：

因为subquery被转化为相关子查询，即先遍历subcategory表(outer table)，对于每条记录都执行一次subquery(总计300783次)

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: subcategory

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 300783

Extra: Using where

*************************** 2. row ***************************

id: 2

select_type:DEPENDENT SUBQUERY

table: c

type: ref

possible_keys: PRIMARY,category

key: category

key_len: 4

ref: const

rows: 100

Extra: Using where; Using index; Using temporary; Using filesort

*************************** 3. row ***************************

id: 2

select_type: DEPENDENT SUBQUERY

table: i

type: ref

possible_keys: subcategory

key: subcategory

key_len: 4

ref: c.id

rows: 28

Extra: Using index

优化：采用物化子查询，所做的就是将子查询改为from clause，即添加一对括号即可；

select * from subcategory

where id in (

select id from(

select c.id

from subcategory as c

inner join item as i on i.subcategory = c.id

where c.category = 14

group by c.id

having count(*) > 2000

)as x

);

5.6优化

1引入物化子查询(针对where clause的subquery)

5.6.5引入此功能，在此之前，优化器有时会把非关联子查询重写为相关子查询，导致效率变差；

子查询物化将子查询结果存入临时表，确保子查询只执行一次，该表不记录重复数据且采用哈希索引查找;

Optimizer_switch需设置materialization=on

2优化derived table

以前的版本在explain时就会对from clause的subquery进行物化，引发了部分执行，5.6消除了这个问题；

另外，优化器可能会为derived table添加索引以加速执行

SELECT * FROM t1 JOIN (SELECT * FROM t2) AS derived_t2 ON t1.f1=derived_t2.f1;

优化器可以为derived_t2的f1添加索引以采用ref