SQL中多表联结查询的效率笔记

多表联结查询效率问题一直是我们开发人员讨论的一个问题,下面我把我在使用多表查询时的一些测试也分享给各位朋友,希望对大家所有帮助.

最近做网站的时候遇到一个问题,我需要输出B表和C表中的两个字段,不过纠结的是,程序所得到的参数是A表中的id,而A表与B表及C表关联的是另一个字段,如此一来,我就需要从3个表中读取数据,刚开始我想用SQL的多表联结查询,可是又担心效率太低,如果我先读出A表中与B、C关联的字段的值,再运行另一条SQL语句使用该值读取B、C表中的值效率是否会高一些？思索良久,最终决定写一个程序来测试.

以下代码以Discuz!X2.5的数据库为例,查询tid为1的主题发表者的username,代码如下:

1. <?php
2. $start=time();
3. for($i=1;$i<10000;$i++){
4. $writerInfo=DB::fetch_first("SELECTa.usernameFROM
5. pre_common_memberASa,pre_forum_threadASb
6. WHEREb.tid='1'ANDa.uid=b.authorid");
7. }//phpfensi.com
8. echo(time()-$start);
9. ?>

这段代码在执行SQL查询前先保存当前时间戳,然后执行一万次多表联结查询,然后用执行完毕后的时间戳减去开始前的时间戳,得到运行时间.

然后将循环块中代码改为如下:

1. $tid=DB::fetch_first("SELECTauthoridFROMpre_forum_threadWHEREtid='1'");
2. $user=DB::fetch_first("SELECTusernameFROMpre_common_memberWHEREuid='$tid[authorid]'");

这次把SQL语句分为两次进行执行.

为了让结果准确，两段代码分别执行了三次,结果第一段代码的执行时间分别为13、13和15秒,而第二段代码执行时间分别为23、21、22.

可以看出,将多表联结的SQL分为多次执行,时间将比一次多表联结要长大概80%左右,结论是直接执行一行多表连接效率更高.

下面讲一些题外话了,不过也是关于多表联合查询的.

inner join,full outer join,left join,right jion

内部连接 inner join 两表都满足的组合.

full outer 全连 两表相同的组合在一起,A表有,B表没有的数据（显示为null）,同样B表有.

A表没有的显示为(null)

A表 left join B表 左连,以A表为基础，A表的全部数据，B表有的组合。没有的为null

A表 right join B表 右连,以B表为基础，B表的全部数据，A表的有的组合。没有的为null

查询分析器中执行:

1. --建表table1,table2：
2. createtabletable1(idint,namevarchar(10))
3. createtabletable2(idint,scoreint)
4. insertintotable1select1,'lee'
5. insertintotable1select2,'zhang'
6. insertintotable1select4,'wang'
7. insertintotable2select1,90
8. insertintotable2select2,100
9. insertintotable2select3,70
10. 如表
11. -------------------------------------------------
12. table1|table2|
13. -------------------------------------------------
14. idname|idscore|
15. 1lee|190|
16. 2zhang|2100|
17. 4wang|370|
18. -------------------------------------------------

以下均在查询分析器中执行.

一、外连接

1.概念:包括左向外联接、右向外联接或完整外部联接

2.左连接:left join 或 left outer join

(1)左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值(null)。

(2)sql语句,代码如下:

1. select*fromtable1leftjointable2ontable1.id=table2.id
2. -------------结果-------------
3. idnameidscore
4. ------------------------------
5. 1lee190
6. 2zhang2100
7. 4wangNULLNULL
8. ------------------------------

注释:包含table1的所有子句,根据指定条件返回table2相应的字段,不符合的以null显示.

3.右连接:right join 或 right outer join

(1)右向外联接是左向外联接的反向联接,将返回右表的所有行,如果右表的某行在左表中没有匹配行,则将为左表返回空值.

(2)sql语句,代码如下:

1. select*fromtable1rightjointable2ontable1.id=table2.id
2. -------------结果-------------
3. idnameidscore
4. ------------------------------
5. 1lee190
6. 2zhang2100
7. NULLNULL370
8. ------------------------------

注释:包含table2的所有子句,根据指定条件返回table1相应的字段,不符合的以null显示.

4.完整外部联接:full join 或 full outer join

(1)完整外部联接返回左表和右表中的所有行,当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值,如果表之间有匹配行,则整个结果集行包含基表的数据值.

(2)sql语句,代码如下:

1. select*fromtable1fulljointable2ontable1.id=table2.id
2. -------------结果-------------
3. idnameidscore
4. ------------------------------
5. 1lee190
6. 2zhang2100
7. 4wangNULLNULL
8. NULLNULL370
9. ------------------------------

注释:返回左右连接的和（见上左、右连接）

二、内连接

1.概念:内联接是用比较运算符比较要联接列的值的联接

2.内连接:join 或 inner join

3.sql语句,代码如下:

1. select*fromtable1jointable2ontable1.id=table2.id
2. -------------结果-------------
3. idnameidscore
4. ------------------------------
5. 1lee190
6. 2zhang2100
7. ------------------------------

注释:只返回符合条件的table1和table2的列.

4.等价,与下列执行效果相同,代码如下:

A:select a.*,b.* from table1 a,table2 b where a.id=b.id

B:select * from table1 cross join table2 where table1.id=table2.id

注:cross join后加条件只能用where,不能用on

三、交叉连接(完全)

1.概念:没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积,第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小,table1和table2交叉连接产生3*3=9条记录.

2.交叉连接:cross join,不带条件where...

3.sql语句,代码如下:

1. select*fromtable1crossjointable2
2. -------------结果-------------
3. idnameidscore
4. ------------------------------
5. 1lee190
6. 2zhang190
7. 4wang190
8. 1lee2100
9. 2zhang2100
10. 4wang2100
11. 1lee370
12. 2zhang370
13. 4wang370
14. ------------------------------

注释:返回3*3=9条记录,即笛卡尔积.

4.等价,与下列执行效果相同,代码如下:

A:select * from table1,table2