多表联结查询效率问题一直是我们开发人员讨论的一个问题,下面我把我在使用多表查询时的一些测试也分享给各位朋友,希望对大家所有帮助.
最近做网站的时候遇到一个问题,我需要输出B表和C表中的两个字段,不过纠结的是,程序所得到的参数是A表中的id,而A表与B表及C表关联的是另一个字段,如此一来,我就需要从3个表中读取数据,刚开始我想用SQL的多表联结查询,可是又担心效率太低,如果我先读出A表中与B、C关联的字段的值,再运行另一条SQL语句使用该值读取B、C表中的值效率是否会高一些？思索良久,最终决定写一个程序来测试.
以下代码以Discuz!X2.5的数据库为例,查询tid为1的主题发表者的username,代码如下:
- <?php
- $start=time();
- for($i=1;$i<10000;$i++){
- $writerInfo=DB::fetch_first("SELECTa.usernameFROM
- pre_common_memberASa,pre_forum_threadASb
- WHEREb.tid='1'ANDa.uid=b.authorid");
- }//phpfensi.com
- echo(time()-$start);
- ?>
这段代码在执行SQL查询前先保存当前时间戳,然后执行一万次多表联结查询,然后用执行完毕后的时间戳减去开始前的时间戳,得到运行时间.
然后将循环块中代码改为如下:
- $tid=DB::fetch_first("SELECTauthoridFROMpre_forum_threadWHEREtid='1'");
- $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
查询分析器中执行:
- --建表table1,table2：
- createtabletable1(idint,namevarchar(10))
- createtabletable2(idint,scoreint)
- insertintotable1select1,'lee'
- insertintotable1select2,'zhang'
- insertintotable1select4,'wang'
- insertintotable2select1,90
- insertintotable2select2,100
- insertintotable2select3,70
- 如表
- -------------------------------------------------
- table1|table2|
- -------------------------------------------------
- idname|idscore|
- 1lee|190|
- 2zhang|2100|
- 4wang|370|
- -------------------------------------------------
以下均在查询分析器中执行.
一、外连接
1.概念:包括左向外联接、右向外联接或完整外部联接
2.左连接:left join 或 left outer join
(1)左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值(null)。
(2)sql语句,代码如下:
- select*fromtable1leftjointable2ontable1.id=table2.id
- -------------结果-------------
- idnameidscore
- ------------------------------
- 1lee190
- 2zhang2100
- 4wangNULLNULL
- ------------------------------
注释:包含table1的所有子句,根据指定条件返回table2相应的字段,不符合的以null显示.
3.右连接:right join 或 right outer join
(1)右向外联接是左向外联接的反向联接,将返回右表的所有行,如果右表的某行在左表中没有匹配行,则将为左表返回空值.
(2)sql语句,代码如下:
- select*fromtable1rightjointable2ontable1.id=table2.id
- -------------结果-------------
- idnameidscore
- ------------------------------
- 1lee190
- 2zhang2100
- NULLNULL370
- ------------------------------
注释:包含table2的所有子句,根据指定条件返回table1相应的字段,不符合的以null显示.
4.完整外部联接:full join 或 full outer join
(1)完整外部联接返回左表和右表中的所有行,当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值,如果表之间有匹配行,则整个结果集行包含基表的数据值.
(2)sql语句,代码如下:
- select*fromtable1fulljointable2ontable1.id=table2.id
- -------------结果-------------
- idnameidscore
- ------------------------------
- 1lee190
- 2zhang2100
- 4wangNULLNULL
- NULLNULL370
- ------------------------------
注释:返回左右连接的和（见上左、右连接）
二、内连接
1.概念:内联接是用比较运算符比较要联接列的值的联接
2.内连接:join 或 inner join
3.sql语句,代码如下:
- select*fromtable1jointable2ontable1.id=table2.id
- -------------结果-------------
- idnameidscore
- ------------------------------
- 1lee190
- 2zhang2100
- ------------------------------
注释:只返回符合条件的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语句,代码如下:
- select*fromtable1crossjointable2
- -------------结果-------------
- idnameidscore
- ------------------------------
- 1lee190
- 2zhang190
- 4wang190
- 1lee2100
- 2zhang2100
- 4wang2100
- 1lee370
- 2zhang370
- 4wang370
- ------------------------------
注释:返回3*3=9条记录,即笛卡尔积.
4.等价,与下列执行效果相同,代码如下:
A:select * from table1,table2
