数据库版本: mysql 5.0
表结构:
create table A(
id int(11),
bid int(11),
KEY `id` (`id`),
KEY `bid` (`bid`)
)
create table B(
id int(11),
cid int(11),
KEY `id` (`id`),
KEY `cid` (`cid`)
)
初始化:
A表有200w数据
B表有1000条数据
select * from B where cid = 1 的结果集是(1,3,5,10) ,不管cid=*,结果集都在10以内。
对比以下三个sql的执行效率:
SQL1:select a.* from A,B where a.bid = b.id and b.cid = 1
SQL2:select * from A where a.bid in (select * from B where cid = 1)
SQL3:select * from A where a.bid in (1,3,5,10)
我现在测试的情况是 SQL1 和 SQL 3 数据相当,SQL2最慢. explain 看到的SQL1和SQL3都走了索引,但是SQL2没走.
很想知道两个表关联的sql 应该如何写最佳。
还要分别考虑以下2种情况:
1.B表也是大表,但是select * from B where cid = 1 的结果集还是(1,3,5,10)
2.B表也是大表,但是select * from B where cid = 1 的结果集很大
http://dev.mysql.com/doc/refman/5.5/en/subquery-restrictions.html
头一条:
in 子查询会被优化为exists
explain extended select * from A where a.bid in (select * from B where cid = 1);
show warnings;
note: 这里明显楼主给错了sql, in里应该是 select id from B...
你可以看到, 此sql被优化为(手写, 未实际验证):
select * from A where exists (select 1 from B where a.bid =b.id and cid = 1)
这样本来应该是 从in子查询中拿到10条数据, 去a表走索引查询, 变为:
遍历a表, 对每条记录去 过exists
按你的表记录数, 由 10(in子查询结果集) X 1(a表过索引), 变为: 200w(遍历a表) X 1(b表过索引)