消息关闭
    暂无新消息!

麻烦看下explain的结果??

问题作者 : 荷花2017-05-19发布

下面两条语句得出的结果相同,写法不同:

语句1

select distinct s.sid , s.name from student as s where
 
exists 
(select * from course where cid in 
    (select cid from achievement where sid = s.sid)
     &&
    name = '数学' 
) 

&& 

exists 
(select * from course where cid in 
    (select cid from achievement where sid = s.sid) 
    && 
    name = '语文' 
) 
order by s.sid asc

explain 分析结果(图片较长,分成两部分了):


语句2

select distinct s.sid , s.name from 
student as s , achievement as a , course as c 
where
s.sid = a.sid && a.cid = c.cid && c.name = '数学'  && 
exists 
(select * from achievement as a1 , course as c1 where
     a1.sid = a.sid && a1.cid = c1.cid && c1.name = '语文'
)
order by s.sid asc

explain 分析结果:

请问 语句1 还是 语句2 性能更好??


1个回答

︿ 0

单单从explain讲,语句2优于语句1,但是这个语句太复杂,如果数据量不大的话,也能运行,如果数据量大,结果很悲剧,建议业务逻辑分拆