Mysql實例淺析Mysql Join語法以及性能優(yōu)化
《Mysql實例淺析Mysql Join語法以及性能優(yōu)化》要點：
本文介紹了Mysql實例淺析Mysql Join語法以及性能優(yōu)化，希望對您有用。如果有疑問，可以聯(lián)系我們。
一．Join語法概述MYSQL學(xué)習(xí)
join 用于多表中字段之間的聯(lián)系,語法如下：MYSQL學(xué)習(xí)
... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona
table1:左表；table2:右表.MYSQL學(xué)習(xí)
JOIN 按照功能大致分為如下三類：MYSQL學(xué)習(xí)
INNER JOIN(內(nèi)連接,或等值連接)：取得兩個表中存在連接匹配關(guān)系的記錄.MYSQL學(xué)習(xí)
LEFT JOIN(左連接)：取得左表(table1)完全記錄,即是右表(table2)并無對應(yīng)匹配記錄.MYSQL學(xué)習(xí)
RIGHT JOIN(右連接)：與 LEFT JOIN 相反,取得右表(table2)完全記錄,即是左表(table1)并無匹配對應(yīng)記錄.MYSQL學(xué)習(xí)
注意：mysql不支持Full join,不過可以通過UNION 關(guān)鍵字來合并 LEFT JOIN 與 RIGHT JOIN來模擬FULL join.MYSQL學(xué)習(xí)
接下來給出一個列子用于解釋下面幾種分類.如下兩個表(A,B)MYSQL學(xué)習(xí)
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學(xué)習(xí)
內(nèi)連接,也叫等值連接,inner join產(chǎn)生同時符合A和B的一組數(shù)據(jù).MYSQL學(xué)習(xí)
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學(xué)習(xí)
三.Left joinMYSQL學(xué)習(xí)
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學(xué)習(xí)
left join,(或left outer join:在Mysql中兩者等價,推薦使用left join.)左連接從左表(A)產(chǎn)生一套完整的記錄,與匹配的記錄(右表(B)) .如果沒有匹配,右側(cè)將包含null.MYSQL學(xué)習(xí)
MYSQL學(xué)習(xí)
如果想只從左表(A)中產(chǎn)生一套記錄,但不包含右表(B)的記錄,可以通過設(shè)置where語句來執(zhí)行,如下：MYSQL學(xué)習(xí)
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學(xué)習(xí)
同理,還可以模擬inner join. 如下：MYSQL學(xué)習(xí)
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學(xué)習(xí)
根據(jù)上面的例子可以求差集,如下：MYSQL學(xué)習(xí)
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;
# 結(jié)果
??? +------+-----------+------+-------------+
| id?? | name????? | id?? | name??????? |
+------+-----------+------+-------------+
|??? 2 | Monkey??? | NULL | NULL??????? |
|??? 4 | Spaghetti | NULL | NULL??????? |
| NULL | NULL????? |??? 1 | Rutabaga??? |
| NULL | NULL????? |??? 3 | Darth Vader |
+------+-----------+------+-------------+
MYSQL學(xué)習(xí)
四.Right joinMYSQL學(xué)習(xí)
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學(xué)習(xí)
五.Cross joinMYSQL學(xué)習(xí)
cross join：交叉連接,得到的結(jié)果是兩個表的乘積,即笛卡爾積MYSQL學(xué)習(xí)
笛卡爾(Descartes)乘積又叫直積.假設(shè)集合A={a,b},集合B={0,1,2},則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}.可以擴(kuò)展到多個集合的情況.類似的例子有,如果A表示某學(xué)校學(xué)生的集合,B表示該學(xué)校所有課程的集合,則A與B的笛卡爾積表示所有可能的選課情況.MYSQL學(xué)習(xí)
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)
#再執(zhí)行：mysql> select * from A inner join B; 試一試MYSQL學(xué)習(xí)
#在執(zhí)行mysql> select * from A cross join B on A.name = B.name; 試一試
MYSQL學(xué)習(xí)
實際上,在 MySQL 中(僅限于 MySQL) CROSS JOIN 與 INNER JOIN 的表現(xiàn)是一樣的,在不指定 ON 條件得到的結(jié)果都是笛卡爾積,反之取得兩個表完全匹配的結(jié)果. INNER JOIN 與 CROSS JOIN 可以省略 INNER 或 CROSS 關(guān)鍵字,因此下面的 SQL 效果是一樣的：MYSQL學(xué)習(xí)
... FROM table1 INNER JOIN table2
... FROM table1 CROSS JOIN table2
... FROM table1 JOIN table2
六.Full joinMYSQL學(xué)習(xí)
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)
全連接產(chǎn)生的所有記錄(雙方匹配記錄)在表A和表B.如果沒有匹配,則對面將包含null.MYSQL學(xué)習(xí)
MYSQL學(xué)習(xí)
七.性能優(yōu)化
1.顯示(explicit) inner join VS 隱式(implicit) inner joinMYSQL學(xué)習(xí)
如：MYSQL學(xué)習(xí)
select * from
table a inner join table b
on a.id = b.id;
VSMYSQL學(xué)習(xí)
select a.*, b.*
from table a, table b
where a.id = b.id;
我在數(shù)據(jù)庫中比較(10w數(shù)據(jù))得之,它們用時幾乎相同,第一個是顯示的inner join,后一個是隱式的inner join.MYSQL學(xué)習(xí)
2.left join/right join VS inner joinMYSQL學(xué)習(xí)
盡量用inner join.避免 LEFT JOIN 和 NULL.MYSQL學(xué)習(xí)
在使用left join(或right join)時,應(yīng)該清楚的知道以下幾點：
(1). on與 where的執(zhí)行順序MYSQL學(xué)習(xí)
??? ON 條件(“A LEFT JOIN B ON 條件表達(dá)式”中的ON)用來決定如何從 B 表中檢索數(shù)據(jù)行.如果 B 表中沒有任何一行數(shù)據(jù)匹配 ON 的條件,將會額外生成一行所有列為 NULL 的數(shù)據(jù),在匹配階段 WHERE 子句的條件都不會被使用.僅在匹配階段完成以后,WHERE 子句條件才會被使用.它將從匹配階段產(chǎn)生的數(shù)據(jù)中檢索過濾.MYSQL學(xué)習(xí)
所以我們要注意：在使用Left (right) join的時候,一定要在先給出盡可能多的匹配滿足條件,減少Where的執(zhí)行.如：MYSQL學(xué)習(xí)
PASSMYSQL學(xué)習(xí)
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學(xué)習(xí)
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的條件.從執(zhí)行性能來看第二個顯然更加省時.MYSQL學(xué)習(xí)
(2).注意ON 子句和 WHERE 子句的不同MYSQL學(xué)習(xí)
如作者舉了一個列子：MYSQL學(xué)習(xí)
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學(xué)習(xí)
從上可知,第一條查詢使用 ON 條件決定了從 LEFT JOIN的 product_details表中檢索符合的所有數(shù)據(jù)行.第二條查詢做了簡單的LEFT JOIN,然后使用 WHERE 子句從 LEFT JOIN的數(shù)據(jù)中過濾掉不符合條件的數(shù)據(jù)行.MYSQL學(xué)習(xí)
(3).盡量避免子查詢,而用joinMYSQL學(xué)習(xí)
往往性能這玩意兒,更多時候體現(xiàn)在數(shù)據(jù)量比較大的時候,此時,我們應(yīng)該避免復(fù)雜的子查詢.如下：MYSQL學(xué)習(xí)
PASSMYSQL學(xué)習(xí)
insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);
GreatMYSQL學(xué)習(xí)
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;?
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/6430.html
同類教程排行
- Mysql實例mysql報錯：Deadl
- MYSQL數(shù)據(jù)庫mysql導(dǎo)入sql文件
- MYSQL的UTF8MB4編碼排序要用u
- MYSQL教程mysql自定義split
- 如何打造MySQL高可用平臺
- MYSQL創(chuàng)建表出錯Tablespace
- Mysql必讀MySQL中CLIENT_
- MYSQL數(shù)據(jù)庫mysql常用字典表(完
- Mysql應(yīng)用MySql的Communi
- Mysql入門解決MySQL Sendi
- Mysql必讀關(guān)于skip_name_r
- MYSQL數(shù)據(jù)庫MySQL實現(xiàn)兩張表數(shù)據(jù)
- Mysql實例使用dreamhost空間
- MYSQL數(shù)據(jù)庫mysql 查詢表中平均
- MYSQL教程mysql 跨表查詢、更新
