MySql Join 文法 效能 最佳化

標籤：

連接的文法:

　...　from table1 inner|left|right join table2 on condition

內外連接的區別: 內連接將去除所有不符合條件condition的記錄，外連接將保留部分不符合condition的記錄；

　　　　           左連接將保留左邊表table1的記錄，此時右邊表table2隻返回符合condition的記錄。

1，join概述

　... from table1 inner|left|right join table2 on condition

inner join : 內連接，等值連接，取得兩個表中符合condition的記錄id。

left join : 左連接，取得table1的所有記錄(table1中condition中的欄位可能為空白)，和table2符合condition的記錄，

right join : 右連接，取得table2的所有的記錄(table2中condition中的欄位可能為空白)，和table1符合condition的記錄，

2，Inner join

內連接

　　select * from A inner join B on A.mobile = B.mobile and andCondition;

將會返回 A.id 不為空白，B.id 不為空白，且A.mobile = B.mobile 和符合 andCondition的資料

3,left join

select * from A left join B on A.mobile = B.mobile and andCondition;

將會返回A的所有記錄和 B.mobile = A.mobile的所有B的記錄。

 

如果想取得A表中不滿足condition的資料

select * from A

left join B on A.mobile = B.mobile 

where B.is is null

得到

用left join 類比 inner join

　　-> select * from A left join B on A.mobile = B.mobile where B.id is not null;

求A B 表中不符合condition條件的各自資料的集合

　　 -> select * from A left join B on A.mobile = B.mobile where B.id is null

　　　　union

　　　　select * from A right join B on A.mobile = B.mobile where A.id is null

得到差異資料(不符合condition的兩個表的資料)

　　

 

4,right join

-> select * from A right B on A.mobile = B.mobile ;

將得到B表的所有資料和A表中滿足condition的資料

5,cross join

交叉連接，得到的是兩個表的乘積

在mysql中(僅限mysql) cross join 和 inner join 的表現是一樣的。在不指定on條件得到的都是笛卡爾積。

所以下面的三個語句效果一樣

->...from A inner join B

->...from A cross join B

->...from A join B

6，full join

->  select * from A left join B on A.mobile = B.mobile;

　　union

　　select * from A right join B on A.mobile = B.mobile;

得到

 

7,效能最佳化

(1)顯示inner join 和 隱式inner join

顯示 --> select * from A inner join B on A.mobile = B.mobile;

隱式 --> select * from A inner join B where A.mobile = B.mobile;

10萬資料的查詢用時幾乎相等。

(2)left join / right join 和 inner join

盡量用inner join 避免 外連接 和 null

在使用外連接的時候，如 -> select * from A left join B on A.mobile = B.mobile where whereCondition;

如果B中沒有滿足on condition的條件，則會產生一行所有列為null的資料。

　　在 on condition 匹配階段，where 條件不會被使用。在on condition結束後，where將會被使用，where條件將會從滿足on condition的資料中再檢索一次。

所以，在使用外連接市，我們要盡量給出儘可能多的匹配滿足條件(即 on condition)，減少where字句的檢索。

不建議sql -> select * from A 

　　　　　　　　　　left join B on A.mobile = B.mobile

　　　　　　　　　　left join C on A.name = C.name

　　　　　　　　　　where A.status = 1 and C.status = 1

建議的sql -> select * from A

　　　　　　　　　　left join B on A.mobile = B.mobile and A.status = 1

　　　　　　　　　　left join C on A.name = C.name and C.status = 1

盡量滿足on condition，而少使用where的條件。

(3)on 條件 和 where 條件的不同

->select * from A left join B on A.mobile = B.mobile on A.name is not null;

將會返回A表的所有記錄 和 B表中滿足 (A.mobile = B.mobile on A.name is not null) 的記錄;

->select * from A left join B on A.mobile = B.mobile where A.name is not null;

將會返回A表中所有記錄 和 B表中滿足 (A.mobile = B.mobile)的記錄，然後 再通過where條件(A.name is not null)對結果進行篩選。

第一條sql語句返回的結果集條數 >= 第二條sql

(4)盡量避免子查詢，而用join

 

MySql Join 文法 效能 最佳化