mysql - 讨论一个sql执行效率的问题

Question

数据库版本: 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 的结果集很大

Answer 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表过索引)