《Mysql實例淺析Mysql Join語法以及性能優化》要點:
本文介紹了Mysql實例淺析Mysql Join語法以及性能優化,希望對您有用。如果有疑問,可以聯系我們。
一.Join語法概述MYSQL學習
join 用于多表中字段之間的聯系,語法如下:MYSQL學習
代碼如下:
... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona
table1:左表;table2:右表.MYSQL學習
JOIN 按照功能大致分為如下三類:MYSQL學習
INNER JOIN(內連接,或等值連接):取得兩個表中存在連接匹配關系的記錄.MYSQL學習
LEFT JOIN(左連接):取得左表(table1)完全記錄,即是右表(table2)并無對應匹配記錄.MYSQL學習
RIGHT JOIN(右連接):與 LEFT JOIN 相反,取得右表(table2)完全記錄,即是左表(table1)并無匹配對應記錄.MYSQL學習
注意:mysql不支持Full join,不過可以通過UNION 關鍵字來合并 LEFT JOIN 與 RIGHT JOIN來模擬FULL join.MYSQL學習
接下來給出一個列子用于解釋下面幾種分類.如下兩個表(A,B)MYSQL學習
代碼如下:
mysql> select A.id,A.name,B.name from A,B where A.id=B.id;
+----+-----------+-------------+
| id | name?????? | name???????????? |
+----+-----------+-------------+
|? 1 | Pirate?????? | Rutabaga????? |
|? 2 | Monkey??? | Pirate??????????? |
|? 3 | Ninja???????? | Darth Vader |
|? 4 | Spaghetti? | Ninja???????????? |
+----+-----------+-------------+
4 rows in set (0.00 sec)
二.Inner joinMYSQL學習
內連接,也叫等值連接,inner join產生同時符合A和B的一組數據.MYSQL學習
代碼如下:
mysql> select * from A inner join B on A.name = B.name;
+----+--------+----+--------+
| id | name?? | id | name?? |
+----+--------+----+--------+
|? 1 | Pirate |? 2 | Pirate |
|? 3 | Ninja? |? 4 | Ninja? |
+----+--------+----+--------+
MYSQL學習
三.Left joinMYSQL學習
代碼如下:
mysql> select * from A left join B on A.name = B.name;
#或者:select * from A left outer join B on A.name = B.name;
+----+-----------+------+--------+
| id | name????? | id?? | name?? |
+----+-----------+------+--------+
|? 1 | Pirate??? |??? 2 | Pirate |
|? 2 | Monkey??? | NULL | NULL?? |
|? 3 | Ninja???? |??? 4 | Ninja? |
|? 4 | Spaghetti | NULL | NULL?? |
+----+-----------+------+--------+
4 rows in set (0.00 sec)
MYSQL學習
left join,(或left outer join:在Mysql中兩者等價,推薦使用left join.)左連接從左表(A)產生一套完整的記錄,與匹配的記錄(右表(B)) .如果沒有匹配,右側將包含null.MYSQL學習
MYSQL學習
如果想只從左表(A)中產生一套記錄,但不包含右表(B)的記錄,可以通過設置where語句來執行,如下:MYSQL學習
代碼如下:
mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null;
+----+-----------+------+------+
| id | name????? | id?? | name |
+----+-----------+------+------+
|? 2 | Monkey??? | NULL | NULL |
|? 4 | Spaghetti | NULL | NULL |
+----+-----------+------+------+
2 rows in set (0.00 sec)
MYSQL學習
同理,還可以模擬inner join. 如下:MYSQL學習
代碼如下:
mysql> select * from A left join B on A.name=B.name where A.id is not null and B.id is not null;
+----+--------+------+--------+
| id | name?? | id?? | name?? |
+----+--------+------+--------+
|? 1 | Pirate |??? 2 | Pirate |
|? 3 | Ninja? |??? 4 | Ninja? |
+----+--------+------+--------+
2 rows in set (0.00 sec)
求差集:MYSQL學習
根據上面的例子可以求差集,如下:MYSQL學習
代碼如下:
SELECT * FROM A LEFT JOIN B ON A.name = B.name
WHERE B.id IS NULL
union
SELECT * FROM A right JOIN B ON A.name = B.name
WHERE A.id IS NULL;
# 結果
??? +------+-----------+------+-------------+
| id?? | name????? | id?? | name??????? |
+------+-----------+------+-------------+
|??? 2 | Monkey??? | NULL | NULL??????? |
|??? 4 | Spaghetti | NULL | NULL??????? |
| NULL | NULL????? |??? 1 | Rutabaga??? |
| NULL | NULL????? |??? 3 | Darth Vader |
+------+-----------+------+-------------+
MYSQL學習
四.Right joinMYSQL學習
代碼如下:
mysql> select * from A right join B on A.name = B.name;
+------+--------+----+-------------+
| id?? | name?? | id | name??????? |
+------+--------+----+-------------+
| NULL | NULL?? |? 1 | Rutabaga??? |
|??? 1 | Pirate |? 2 | Pirate????? |
| NULL | NULL?? |? 3 | Darth Vader |
|??? 3 | Ninja? |? 4 | Ninja?????? |
+------+--------+----+-------------+
4 rows in set (0.00 sec)
同left join.MYSQL學習
五.Cross joinMYSQL學習
cross join:交叉連接,得到的結果是兩個表的乘積,即笛卡爾積MYSQL學習
笛卡爾(Descartes)乘積又叫直積.假設集合A={a,b},集合B={0,1,2},則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}.可以擴展到多個集合的情況.類似的例子有,如果A表示某學校學生的集合,B表示該學校所有課程的集合,則A與B的笛卡爾積表示所有可能的選課情況.MYSQL學習
代碼如下:
mysql> select * from A cross join B;
+----+-----------+----+-------------+
| id | name????? | id | name??????? |
+----+-----------+----+-------------+
|? 1 | Pirate??? |? 1 | Rutabaga??? |
|? 2 | Monkey??? |? 1 | Rutabaga??? |
|? 3 | Ninja???? |? 1 | Rutabaga??? |
|? 4 | Spaghetti |? 1 | Rutabaga??? |
|? 1 | Pirate??? |? 2 | Pirate????? |
|? 2 | Monkey??? |? 2 | Pirate????? |
|? 3 | Ninja???? |? 2 | Pirate????? |
|? 4 | Spaghetti |? 2 | Pirate????? |
|? 1 | Pirate??? |? 3 | Darth Vader |
|? 2 | Monkey??? |? 3 | Darth Vader |
|? 3 | Ninja???? |? 3 | Darth Vader |
|? 4 | Spaghetti |? 3 | Darth Vader |
|? 1 | Pirate??? |? 4 | Ninja?????? |
|? 2 | Monkey??? |? 4 | Ninja?????? |
|? 3 | Ninja???? |? 4 | Ninja?????? |
|? 4 | Spaghetti |? 4 | Ninja?????? |
+----+-----------+----+-------------+
16 rows in set (0.00 sec)
#再執行:mysql> select * from A inner join B; 試一試MYSQL學習
#在執行mysql> select * from A cross join B on A.name = B.name; 試一試
MYSQL學習
實際上,在 MySQL 中(僅限于 MySQL) CROSS JOIN 與 INNER JOIN 的表現是一樣的,在不指定 ON 條件得到的結果都是笛卡爾積,反之取得兩個表完全匹配的結果. INNER JOIN 與 CROSS JOIN 可以省略 INNER 或 CROSS 關鍵字,因此下面的 SQL 效果是一樣的:MYSQL學習
代碼如下:
... FROM table1 INNER JOIN table2
... FROM table1 CROSS JOIN table2
... FROM table1 JOIN table2
六.Full joinMYSQL學習
代碼如下:
mysql> select * from A left join B on B.name = A.name
??? -> union
??? -> select * from A right join B on B.name = A.name;
+------+-----------+------+-------------+
| id?? | name????? | id?? | name??????? |
+------+-----------+------+-------------+
|??? 1 | Pirate??? |??? 2 | Pirate????? |
|??? 2 | Monkey??? | NULL | NULL??????? |
|??? 3 | Ninja???? |??? 4 | Ninja?????? |
|??? 4 | Spaghetti | NULL | NULL??????? |
| NULL | NULL????? |??? 1 | Rutabaga??? |
| NULL | NULL????? |??? 3 | Darth Vader |
+------+-----------+------+-------------+
6 rows in set (0.00 sec)
全連接產生的所有記錄(雙方匹配記錄)在表A和表B.如果沒有匹配,則對面將包含null.MYSQL學習
MYSQL學習
七.性能優化
1.顯示(explicit) inner join VS 隱式(implicit) inner joinMYSQL學習
如:MYSQL學習
代碼如下:
select * from
table a inner join table b
on a.id = b.id;
VSMYSQL學習
代碼如下:
select a.*, b.*
from table a, table b
where a.id = b.id;
我在數據庫中比較(10w數據)得之,它們用時幾乎相同,第一個是顯示的inner join,后一個是隱式的inner join.MYSQL學習
2.left join/right join VS inner joinMYSQL學習
盡量用inner join.避免 LEFT JOIN 和 NULL.MYSQL學習
在使用left join(或right join)時,應該清楚的知道以下幾點:
(1). on與 where的執行順序MYSQL學習
??? ON 條件(“A LEFT JOIN B ON 條件表達式”中的ON)用來決定如何從 B 表中檢索數據行.如果 B 表中沒有任何一行數據匹配 ON 的條件,將會額外生成一行所有列為 NULL 的數據,在匹配階段 WHERE 子句的條件都不會被使用.僅在匹配階段完成以后,WHERE 子句條件才會被使用.它將從匹配階段產生的數據中檢索過濾.MYSQL學習
所以我們要注意:在使用Left (right) join的時候,一定要在先給出盡可能多的匹配滿足條件,減少Where的執行.如:MYSQL學習
PASSMYSQL學習
代碼如下:
select * from A
inner join B on B.name = A.name
left join C on C.name = B.name
left join D on D.id = C.id
where C.status>1 and D.status=1;
GreatMYSQL學習
代碼如下:
select * from A
inner join B on B.name = A.name
left join C on C.name = B.name and C.status>1
left join D on D.id = C.id and D.status=1
從上面例子可以看出,盡可能滿足ON的條件,而少用Where的條件.從執行性能來看第二個顯然更加省時.MYSQL學習
(2).注意ON 子句和 WHERE 子句的不同MYSQL學習
如作者舉了一個列子:MYSQL學習
代碼如下:
mysql> SELECT * FROM product LEFT JOIN product_details
?????? ON (product.id = product_details.id)
?????? AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id?? | weight | exist |
+----+--------+------+--------+-------+
|? 1 |??? 100 | NULL |?? NULL |? NULL |
|? 2 |??? 200 |??? 2 |???? 22 |???? 0 |
|? 3 |??? 300 | NULL |?? NULL |? NULL |
|? 4 |??? 400 | NULL |?? NULL |? NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM product LEFT JOIN product_details
?????? ON (product.id = product_details.id)
?????? WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|? 2 |??? 200 |? 2 |???? 22 |???? 0 |
+----+--------+----+--------+-------+
1 row in set (0.01 sec)
MYSQL學習
從上可知,第一條查詢使用 ON 條件決定了從 LEFT JOIN的 product_details表中檢索符合的所有數據行.第二條查詢做了簡單的LEFT JOIN,然后使用 WHERE 子句從 LEFT JOIN的數據中過濾掉不符合條件的數據行.MYSQL學習
(3).盡量避免子查詢,而用joinMYSQL學習
往往性能這玩意兒,更多時候體現在數據量比較大的時候,此時,我們應該避免復雜的子查詢.如下:MYSQL學習
PASSMYSQL學習
代碼如下:
insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);
GreatMYSQL學習
代碼如下:
insert into t1(a1)?
select b1 from t2?
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id??
where t1.id is null;?
轉載請注明本頁網址:
http://www.snjht.com/jiaocheng/6430.html