SQL中多表聯結查詢的效率筆記

多表聯結查詢效率問題一直是我們開發人員討論的一個問題，下面我把我在使用多表查詢時的一些測試也分享給各位朋友，希望對大家所有幫助。

最近做網站的時候遇到一個問題，我需要輸出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("SELECT a.username FROM
     pre_common_member AS a, pre_forum_thread AS b
     WHERE b.tid='1' AND a.uid=b.authorid");
    }
    echo(time() - $start);
    ?>

這段代碼在執行SQL查詢前先保存當前時間戳，然後執行一萬次多表聯結查詢，然後用執行完畢後的時間戳減去開始前的時間戳，得到運行時間。
    然後將循環塊中代碼改為如下：

 代碼如下 復制代碼

$tid = DB::fetch_first("SELECT authorid FROM pre_forum_thread WHERE tid='1'");

$user = DB::fetch_first("SELECT username FROM pre_common_member WHERE uid='$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：
create table table1(id int,name varchar(10))
create table table2(id int,score int)
insert into table1 select 1,'lee'
insert into table1 select 2,'zhang'
insert into table1 select 4,'wang'
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,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 * from table1 left join table2 on table1.id=table2.id
-------------結果-------------
idnameidscore
------------------------------
1lee190
2zhang2100
4wangNULLNULL
------------------------------

注釋：包含table1的所有子句，根據指定條件返回table2相應的字段，不符合的以null顯示

3.右連接：right join 或 right outer join
(1)右向外聯接是左向外聯接的反向聯接。將返回右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值。
(2)sql語句

 代碼如下 復制代碼 select * from table1 right join table2 on table1.id=table2.id
-------------結果-------------
idnameidscore
------------------------------
1lee190
2zhang2100
NULLNULL370

------------------------------
注釋：包含table2的所有子句，根據指定條件返回table1相應的字段，不符合的以null顯示

4.完整外部聯接:full join 或 full outer join
(1)完整外部聯接返回左表和右表中的所有行。當某行在另一個表中沒有匹配行時，則另一個表的選擇列表列包含空值。如果表之間有匹配行，則整個結果集行包含基表的數據值。
(2)sql語句

 代碼如下 復制代碼 select * from table1 full join table2 on table1.id=table2.id
-------------結果-------------
idnameidscore
------------------------------
1lee190
2zhang2100
4wangNULLNULL
NULLNULL370

------------------------------
注釋：返回左右連接的和（見上左、右連接）

二、內連接
1.概念：內聯接是用比較運算符比較要聯接列的值的聯接

2.內連接：join 或 inner join

3.sql語句

 代碼如下 復制代碼 select * from table1 join table2 on table1.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 * from table1 cross join table2
-------------結果-------------
idnameidscore
------------------------------
1lee190
2zhang190
4wang190
1lee2100
2zhang2100
4wang2100
1lee370
2zhang370
4wang370

------------------------------
注釋：返回3*3=9條記錄，即笛卡爾積

4.等價（與下列執行效果相同）

 代碼如下 復制代碼 A:select * from table1,table2