关于嵌套查询和连接查询的效率问题

2014/03/03 16:27
阅读数 2.6W

这几天给部门写讲稿,总是讲到连接和嵌套的问题,连接好像大多数用得多一点,感觉和我们的思维很接近,不容易接受嵌套的思路,但实际上,在相同的问题上,我们用嵌套,会获得比连接高十倍的效率。       

下面我们来做一个例子说明   blog上贴图真不舒服,就把图都省略了。。。。   

现在设教学数据库中有三个基本表

S (S#Sname,Age,Sex)     学号,姓名,年龄,性别

SC (S#C#Grade)       学号 ,课程号,分数

C(C#Cname,Teacher)     课程号,课程名,任课老师  

要求:要求检索出学习课程号为C2的学生学号与姓名

下面我们用连接和嵌套二种方法并执行并跟踪,看它们的执行效果,  

方法一:连接查询  

SELECT S.S#,SNAME 
FROM S,SC 
WHERE S.S# = SC.S#  AND   C# = 'C2'

我们来分析方法一的查询过程:先对ssc做笛卡尔积,得到一个S的行数+SC行数的二维表,然后对二该表进行逐行扫描,本例中也就是对一个9+21 =30 行的表进行扫描。   从查询分析器我们看到,在数据库中的逻辑处理是Inner join ,实际上数据库进行了哈希匹配的操作,在进行这项操作的时候预计成本达到0.017847(这个cpu成本究竟指什么我还不是很清楚,但可以肯定的是它是个资源消耗指标),预计子树成本为0.0931  

方法二:嵌套查询

SELECT S#,SNAME
FROM S  
WHERE S# IN (SELECT S# FROM SC WHERE C# = 'C2')

我们来分析方法二的查询过程,数据库先检索选修出课程为C2的学生,得到一个6行的二维表,再对该6行数据和S表进行扫描检索。

从上图可以看出进行物理上的嵌套循环操作,cpu成本仅需要0.00038,执行成本仅需要0.000131,预计子树成本减小到0.0769      

从以上分析可以看出,方法二和方法一同样可以达到检索出选修了课程C2的学生姓名和学号,但是方法二消耗资源要要精减得多,速度要快,成本比方法降低非常多。 因为方法二先进行子结果选择操作,再对子结果进行查询,这样对于时间和空间的开销都要小得多,所以我们可以看到,连接的消耗是很大的。

从这个例子,我们重申上节课提出的优化策略的第一条:

在关系代数表达式中尽可能早地执行选择操作  

题外: 此题还可以有二种写法:

select s#,sname
 from s 
where  exists (select * from sc where sc.s# = s.s# and c# = 'c2')


Select s#,sname 
from s 
where ‘c2’ in (select c# from sc where s# = s.s#)

这二种写法的效率和方法二是一模一样的,在sql内部执行的时候,它们会被优化成方法二的语句去执行。

展开阅读全文
打赏
1
10 收藏
分享
加载中
SELECT S.S#,SNAME
FROM S,SC
WHERE S.S# = SC.S# AND C# = 'C2'
改成
SELECT S.S#,SNAME
FROM S,SC
WHERE C# = 'C2' AND S.S# = SC.S 跟第二种效率不是一样的么?
2014/12/12 13:46
回复
举报
更多评论
打赏
1 评论
10 收藏
1
分享
返回顶部
顶部