inner join on 加條件和where加條件_MySQL實戰：Join的使用技巧和優化

join用于多表中字段之間的聯系，在資料庫的DML (資料操作語言，即各種增删改查操作)中有着重要的作用。

合理使用Join語句優化SQL有利于：

1. 增加資料庫的處理效率，減少響應時間；
2. 減少資料庫伺服器負載，增加伺服器穩定性；
3. 減少伺服器通訊的網絡流量；

1. Join的分類：

• 内連接配接 Inner Join
• 全外連接配接 FULL Outer Join
• 左外連接配接 Left Outer Join
• 右外連接配接 Right Outer Join
• 交叉連接配接 Cross Join

連接配接的分類

每種連接配接的差別作為基礎内容，這裡就不再展開說明，請讀者自己參看其他文章了解，比如Mysql Join文法以及性能優化

需要說明的是，目前MySQL不支援全連接配接，需要使用UNION關鍵字進行聯合。

Union：對兩個結果集進行并集操作，不包括重複行，同時進行預設規則的排序；

Union All：對兩個結果集進行并集操作，包括重複行，不進行排序；

3. Join使用的注意事項

下面進行本文重點，Join的使用注意事項和技巧，首先給出要使用的表結構：

-- auto-generated definitionCREATE TABLE customer( id INT AUTO_INCREMENT PRIMARY KEY, cust_name VARCHAR(50) NOT NULL CHARSET utf8, over VARCHAR(100) NULL CHARSET utf8, CONSTRAINT customer_id_uindex UNIQUE (id)) ENGINE = InnoDB; -- auto-generated definitionCREATE TABLE faculty( id INT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(50) NOT NULL CHARSET utf8, over VARCHAR(200) NULL CHARSET utf8, CONSTRAINT faculty_id_uindex UNIQUE (id)) ENGINE = InnoDB;           

customer表中資料，代表客戶的資訊

faculty表中的資料，代表職工的資訊

2.1 顯式連接配接 VS 隐式連接配接

所謂顯式連接配接，即如上顯示使用inner Join關鍵字連接配接兩個表，

select * fromtable a inner join table bon a.id = b.id;           

而隐式連接配接即不顯示使用inner Join關鍵字，如：

select a.*, b.*from table a, table bwhere a.id = b.id;           

二者在功能上沒有差别，實作的性能上也幾乎一樣。隻不過隐式連接配接是SQL92中的标準内容，而在SQL99中顯式連接配接為标準，雖然很多人還在用隐私連接配接，但是它已經從标準中被移除。從使用的角度來說，還是推薦使用顯示連接配接，這樣可以更清楚的顯示出多個表之間的連接配接關系和連接配接依賴的屬性。

2.2 On VS Where

ON 條件(“A LEFT JOIN B ON 條件表達式”中的ON)用來決定如何從 B 表中檢索資料行。如果 B 表中沒有任何一行資料比對 ON 的條件,将會額外生成一行所有列為 NULL 的資料,在比對階段 WHERE 子句的條件都不會被使用。僅在比對階段完成以後，WHERE 子句條件才會被使用。ON将從比對階段産生的資料中檢索過濾。

是以我們要注意：在使用Left (right) join的時候，一定要在先給出盡可能多的比對滿足條件，減少Where的執行。盡可能滿足ON的條件，而少用Where的條件，從執行性能來看也更加高效。

3 Join的技巧

3.1 如何更新使用過慮條件中包括自身的表

假設現在要将是職工中的消費者的“over”屬性設定為"優惠"，直接如下更新會報錯：

這是由于Mysql不支援這種查詢後更新(這其實是标準SQL中一項要求，Oracle、SQL Server中都是可以的)。

為了解決這種更新的過慮條件中包含要更新的表的情況，可以把帶過濾條件的查詢結果當做一個新表，在新表上，執行更新操作。

UPDATE (faculty f INNER JOIN customer c on user_name=cust_name)set c.over = "優惠";           

更新成功

3.2 Join優化子查詢

嵌套的子查詢是比較低效地，因為每一條記錄都要進行比對，如果記錄長度比較大的話，那麼我們的查詢就有可能非常的耗時。我們應該盡量避免使用子查詢，而用表連接配接。如下面的這個子查詢就可以轉化為等價的連接配接查詢

SELECT user_name, over ,(SELECT over FROM customer c where user_name=cust_name) as over2from faculty f;SELECT user_name, f.over , c.over as over2from faculty f LEFT JOIN customer c ON cust_name=user_name;           

3.3 使用Join優化聚合查詢

為了說明這個問題 ，我們在添加一個工作量的表，記錄每個職工每天的工作量

-- auto-generated definitionCREATE TABLE tasks( id SMALLINT(5) UNSIGNED AUTO_INCREMENT PRIMARY KEY, facult_id SMALLINT(5) UNSIGNED NULL, timestr TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, workload SMALLINT(5) UNSIGNED NULL) ENGINE = InnoDB CHARSET = utf8;           

tasks記錄職工的工作量

比如我們想查詢每個員工工作量最多是哪一天，通過子查詢可以這樣實作：

select a.user_name ,b.timestr,b.workloadfrom faculty a join tasks b on a.id = b.facult_idwhere b.workload = ( select max(c.workload) from tasks c where c.facult_id = b.facult_id)           

查詢結果

使用表連接配接優化之後：

SELECT user_name, t.timestr, t.workloadFROM faculty f JOIN tasks t ON f.id = t.facult_id JOIN tasks t2 ON t2.facult_id = t.facult_idGROUP BY user_name,t.timestr，t.workloadHAVING t.workload = max(t2.workload);           

這裡額外的再連接配接了一個task表中内容，在這個“額外表”中通過聚合計算出工作量的最大值，然後再過慮(HAVING)出工作量最大的日期。

因為聚合函數通過作用于一組資料而隻傳回一個單個值，是以，在SELECT語句中出現的元素要麼為一個聚合函數的輸入值，要麼為GROUP BY語句的參數，否則會出錯。

但是mysql的group by做過擴充了，select之後的列允許其不出現在group by之後，MySQL在執行這類查詢語句時，它會預設了解為，沒寫到GROUP BY子句的列，其列值是唯一的，如果GROUP BY省略的列值其實并不唯一，将會預設取第一個獲得的值，這樣就會指代不明，那麼最好不要使用這項功能。

3.4 如何實作分組查詢

要擷取每個員工完成工作量最多的兩天。這個也可以通過Join來完成。

select d.user_name,c.timestr,workloadFROM ( select facult_id,timestr,workload, (SELECT COUNT(*) FROM tasks b WHERE b.facult_id=a.facult_id AND a.workload<=b.workload) AS cnt FROM tasks a GROUP BY facult_id,timestr,workload) c JOIN faculty d ON c.facult_id=d.idWHERE cnt <= 2;           

其中，内部的查詢結果cnt表示對于tasks表中某個給定記錄，相同員工的工作裡記錄比其大的數量有多少。

内部查詢的結果如下：

select facult_id,timestr,workload, (SELECT COUNT(*) FROM tasks b WHERE b.facult_id=a.facult_id AND a.workload<=b.workload) AS cnt FROM tasks a GROUP BY facult_id,timestr,workload;           

内部查詢的結果

即每個工作量記錄資訊和同一員工的工作量排名。

cnt <= 2就代表該記錄是某位員工的工作量最大兩天之一。

每個員工完成工作量最多的兩天

4. join的實作原理

join的實作是采用Nested Loop Join算法，就是通過驅動表的結果集作為循環基礎資料，然後一條一條的通過該結果集中的資料作為過濾條件到下一個表中查詢資料，然後合并結果。如果有多個join，則将前面的結果集作為循環資料，再一次作為循環條件到後一個表中查詢資料。

比如我們以如下SQL語句為例：

EXPLAIN SELECT C.id, cust_name,T.workloadFROM customer C INNER JOIN faculty F ON C.cust_name = F.user_name INNER JOIN tasks T ON T.facult_id = F.id ;           

EXPLAIN 連接配接查詢

從explain的輸出看出，MySQL選擇C作為驅動表，

首先通過Using Where和Using join buffer來比對F中的内容，然後在其結果的基礎上通過主鍵的索引PRIMARY,faculty_id_uindex比對到T表中的内容。

其過程類似于三次次嵌套的循環。

需要說明的是，C作為驅動表，通過Using Where和Using join buffer來比對F，是因為C.cust_name ，F.user_name都沒有加索引，要擷取具體的内容隻能通過對全表的資料進行where過濾才能擷取，而Using join buffer是指使用到了Cache(隻有當join類型為ALL，index，rang或者是index_merge的時候才會使用join buffer)，記錄已經查詢的結果，提高效率。

而對于T和F之間通過T的主鍵T.id連接配接，是以join類型為eq_ref，也不用使用Using join buffer。

5. join語句的優化原則

1. 用小結果集驅動大結果集，将篩選結果小的表首先連接配接，再去連接配接結果集比較大的表，盡量減少join語句中的Nested Loop的循環總次數；
2. 優先優化Nested Loop的内層循環(也就是最外層的Join連接配接)，因為内層循環是循環中執行次數最多的，每次循環提升很小的性能都能在整個循環中提升很大的性能；
3. 對被驅動表的join字段上建立索引；
4. 當被驅動表的join字段上無法建立索引的時候，設定足夠的Join Buffer Size。

參考文章

1. MySQL資料庫對GROUP BY子句的功能擴充(1)
2. SQL中GROUP BY語句與HAVING語句的使用
3. Mysql Join文法以及性能優化
4. mysql join的實作原理及優化思路
5. Explicit vs implicit SQL joins
6. Deprecation of "Old Style" JOIN Syntax: Only A Partial Thing