MySQL EXPLAIN 独立子查询dependent subquery 优化示例

(本例创建表脚本在文章底部)

对于mysql的出现的子查询语句,大部分都是不太好的,尤其 in() 的子查询语句,如下:

select * from test.tabname where id in(select id from test.tabname2 where name='love');

一般会认为mysql会先执行子查询,返回所有包含 test.tabname2 中所有符合条件的 id,外层查询则搜索内层的结果集。一般认为是这样执行的。

select group_concat(id) from test.tabname2 where name='love';
--内层查询结果:1,3,5,7,9,11,13,15,17,1

select * from test.tabname where id in(1,3,5,7,9,11,13,15,17,19);

很不幸,MySQL 不是这样做的。MySQL 会将相关的外层表压缩到子查询中,它认为这样可以更高效地查找到数据行。也就是说,MySQL 会将查询改写成下面的样子:

select * from test.tabname 
where exists(
	select * from test.tabname2 
	where tabname.id=tabname2.id 
	and tabname2.name='love');

这时,子查询先根据 id 来关联外部表 tabname ,因为需要 id 字段,所以mysql 无法先执行这个子查询,通过 explain 我们可以看到子查询是一个相关子查询(DEPENDENT SUBQUERY )。

mysql> explain select * from test.tabname where id in(select id from test.tabname2 where name='love');
+----+--------------------+----------+-----------------+---------------+---------+---------+------+------+-------------+
| id | select_type        | table    | type            | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+--------------------+----------+-----------------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | tabname  | ALL             | NULL          | NULL    | NULL    | NULL |   30 | Using where | 
|  2 | DEPENDENT SUBQUERY | tabname2 | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 | Using where | 
+----+--------------------+----------+-----------------+---------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> explain select * from test.tabname 
    -> where exists(select * from test.tabname2 where tabname.id=tabname2.id and tabname2.name='love');
+----+--------------------+----------+--------+---------------+---------+---------+-----------------+------+-------------+
| id | select_type        | table    | type   | possible_keys | key     | key_len | ref             | rows | Extra       |
+----+--------------------+----------+--------+---------------+---------+---------+-----------------+------+-------------+
|  1 | PRIMARY            | tabname  | ALL    | NULL          | NULL    | NULL    | NULL            |   30 | Using where | 
|  2 | DEPENDENT SUBQUERY | tabname2 | eq_ref | PRIMARY       | PRIMARY | 4       | test.tabname.id |    1 | Using where | 
+----+--------------------+----------+--------+---------------+---------+---------+-----------------+------+-------------+
2 rows in set (0.00 sec)

根据explain 的输出我们可以看到,mysql 先选择对  tabname 表进行全表扫描 30 次,然后根据返回的 id 逐个执行子查询。如果是一个很小的表,这个查询糟糕的性能可能还不会引起注意。如果是一个非常大的表,这个查询的性能会非常糟糕。


解决方法:使用连接查询

select tabname.* from test.tabname,test.tabname2 where tabname.id=tabname2.id and tabname2.name='love';
select tabname. * from test.tabname inner join test.tabname2 using(id) where tabname2.name='love';

执行计划:

mysql> explain select tabname.* from test.tabname,test.tabname2 where tabname.id=tabname2.id and tabname2.name='love';
mysql> explain select tabname. * from test.tabname inner join test.tabname2 using(id) where tabname2.name='love';
+----+-------------+----------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table    | type   | possible_keys | key     | key_len | ref              | rows | Extra       |
+----+-------------+----------+--------+---------------+---------+---------+------------------+------+-------------+
|  1 | SIMPLE      | tabname2 | ALL    | PRIMARY       | NULL    | NULL    | NULL             |   20 | Using where | 
|  1 | SIMPLE      | tabname  | eq_ref | PRIMARY       | PRIMARY | 4       | test.tabname2.id |    1 |             | 
+----+-------------+----------+--------+---------------+---------+---------+------------------+------+-------------+
2 rows in set (0.00 sec)

这次可以看到,select_type 变为 简单查询。首先访问的是 tabname2 ,因为表 tabname2 的记录比较少,只需该表全表扫描,再查询子查询,这省去了较多的IO。




环境脚本:

drop table if exists tabname,tabname2;

create table tabname (  
id int auto_increment not null primary key,  
name varchar(10) null,  
indate datetime null,  
tid int null,  
key(tid),  
key(indate)  
)engine=innodb;  
  
  
create table tabname2 (  
id int auto_increment not null primary key,  
name varchar(10) null,  
indate datetime null,  
tid int null,  
key(tid),  
key(indate)  
)engine=innodb;  
  


drop procedure if exists inserttab;

delimiter //
create procedure inserttab()
begin
declare i int default 1 ;
while i<= 10 do
insert into tabname(name,indate,tid) values('love',now(),2),('lucky',now(),3),('passion',now(),4);  
insert into tabname2(name,indate,tid) values('love',now(),2),('lucky',now(),3);
set i= i + 1;
end while;
end;//
delimiter ;

call inserttab;



select count(*) from test.tabname;
select count(*) from test.tabname2;



参考:《高性能 MySQL》


评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值