SQL連接查詢優化[姊妹篇.第五彈]
上篇的sql優化篇章，更多偏向於優化的思想概念，先前拋出的4個優化問題中，篇幅過長，只對前兩個問題進行了解析。
接下來我們一起來談談sql的連接查詢優化，更偏向於實際運用，並對如下兩個問題進行探討。篇幅過長，請耐心看完。
1.嵌套查詢、HASH連接、排序合併連接、笛卡爾連接等怎樣玩能達到最優？
2. IN 與 EXISTS 誰快誰慢？
嵌套循環(NESTED LOOPS)
嵌套循環的演算法：驅動表返回一行數據，通過連接列傳值給被驅動表，驅動表返回多少行，被驅動表就要被掃描多少次。
這裡我補充一下驅動表和被驅動表：理解驅動表和被驅動表的本質，需要理解順序讀取和隨機讀取的差異，記憶體適合隨機讀取，硬碟則順序讀取的效率比較好。
驅動表，作為外層循環，若進行一次IO將所有數據讀取，則適合順序讀取，一次性批量的把數據讀取出來，不考慮快取情況下。
被驅動表，即裡層循環，由於需要不斷的拿外層循環傳進來的每條記錄去匹配，所以如果是適合隨機讀取的，那麼效率就會比較高。如果表上有索引，實際上就意味著這個表是適合隨機讀取的。
1.嵌套循環可以快速返回兩表關聯的前幾條數據，如果SQL中添加了HINT：FIRST_ROWS，在兩表關聯的時候，優化器更傾向於嵌套循環。
2.嵌套循環驅動表應該返回少量數據。如果驅動表返回了100萬行，那麼被驅動表 就會被掃描100萬次。這個時候SQL會執行很久，被驅動表會被誤認為熱點表，被驅動表連接列的索引也會被誤認為熱點索引。
3.嵌套循環被驅動表必須走索引。如果嵌套循環被驅動表的連接列沒包含在索引中，那麼被驅動表就只能走全表掃描，而且是反覆多次全表掃描。當被驅動表很大的 時候，SQL就執行不出結果。
4.嵌套循環被驅動表的連接列基數應該很高。如果被驅動表連接列的基數很低，那麼被驅動表就不應該走索引，這樣一來被驅動表就只能進行全表掃描了，但是被驅動表也不能走全表掃描。
5.兩表關聯返回少量數據才能走嵌套循環。前面提到，嵌套循環被驅動表必須走索引，如果兩表關聯，返回100萬行數據，那麼被驅動表走索引就會產生100萬次回表。回表一般是單塊讀，這個時候SQL性能極差，所以兩表關聯返回少量數據才能走嵌套 循環。
兩表關聯走不走NL(嵌套循環)是看兩個表關聯之後返回的數據量多少?還是看驅動表返回的數據量多少？
如果兩個表是1∶N關係，驅動表為1，被驅動表為N並且N很大，這時即使驅動表返回數據量很少，也不能走嵌套循環，因為兩表關聯之後返回的數據量會很多。
所以判斷兩表關聯是否應該走NL應該直接查看兩表關聯之後返回的數據量，如果 兩表關聯之後返回的數據量少，可以走NL；返回的數據量多，應該走HASH連接。
SELECT * FROM t1,t2 WHERE t1.id = t2.id; 如果t1有200條數據，t2有200萬行數據，t1與t2是1∶N關係，N很低，應該怎麼優化SQL?
因為t1與t2是1∶N關係，N很低，我們可以在b的連接列（id）上創建索引，讓 t1與t2走嵌套循環（t1 nl t2），這樣t2表會被掃描100次，但是每次掃描表的時候走的 是id列的索引（範圍掃描）。
如果讓t1和t2進行HASH連接，t2表會被全表掃描（因為沒有過濾條件），需要查詢表中100萬行數據，而如果讓t1和t2進行嵌套循環，t2表只需要 查詢出表中最多幾百行數據（100*N）。
一般情況下，一個小表與一個大表關聯，我們可以考慮讓小表NL大表，大表走連接列索引（如果大表有過濾條件，需要將過濾條件與連接列組合起來創建組合索引），從而避免大表被全表掃描。
HASH連接(HASH JOIN )
HASH連接的演算法：兩表等值關聯，返回大量數據，將較小的表選為驅動表，將驅動表的「select列和join列」讀入PGA中的work area，然後對驅動表的連接列進行hash 運算生成hash table，當驅動表的所有數據完全讀入PGA中的work area之後，再讀取被驅動表（被驅動表不需要讀入PGA中的work area），對被驅動表的連接列也進行 hash運算，然後到PGA中的work area去探測hash table，找到數據就關聯上，沒找到 數據就沒關聯上。哈希連接只支援等值連接。
如何優化HASH連接？
因為HASH連接需要將驅動表的select列和join列放入PGA中，所以，我們 應該盡量避免書寫select * from….語句，將需要的列放在select list中，這樣可以減少驅動表對PGA的佔用，避免驅動表被溢出到臨時表空間，從而提升查詢性能。
如果無法避免驅動表被溢出到臨時表空間，我們可以將臨時表空間創建在SSD上或者RAID 0上，加快臨時數據的交換速度。
當PGA採用自動管理，單個進程的work area被限制在1G以內，如果PGA採用手動管理，單個進程的work area不能超過2GB。如果驅動表比較大，比如驅動表有 4GB，可以開啟並行查詢至少parallel(4)，將表拆分為至少4份，這樣每個並行進程中 的work area能夠容納1GB數據，從而避免驅動表被溢出到臨時表空間。
排序合併連接(SORT MERGE JOIN)
前文提到HASH連接主要用於處理兩表等值關聯返回大量數據。排序合併連接主要用於處理兩表非等值關聯，比如>，>=，<，<=，<>，但是不能用於instr、substr、like、regexp_like關聯，instr、substr、like、regexp_like關聯只能走嵌套循環。
排序合併連接的演算法：兩表關聯，先對兩個表根據連接列進行排序，將較小的表作為驅動表，然後從驅動表中取出連接列的值，到已經排好序的被驅動表中匹配數據，如果匹配上數據，就關聯成功。驅動表返回多少行，被驅動表就要被匹配多少次，這個匹配的過程類似嵌套循環，但是嵌套循環是從被驅動表的索引中匹配數據，而排序合併連接是在記憶體中（PGA中的work area）匹配數據。
怎麼優化排序合併連接？
如果兩表關聯是等值關聯，走的是排序合併連接，我們可以將表連接方式改為HASH連接。如果兩表關聯是非等值關聯，比如>，>=，<，<=，<>，這時我們應該先從業務上入手，嘗試將非等值關聯改寫為等值關聯，因為非等值關聯返回的結果 集「類似」於笛卡兒積，當兩個表都比較大的時候，非等值關聯返回的數據量相當大。如果沒有辦法將非等值關聯改寫為等值關聯，我們可以考慮增加兩表的限制條件，將兩個表數據量縮小，最後可以考慮開啟並行查詢加快SQL執行速度。
笛卡爾連接(CARTESIAN JOIN)
兩個表關聯沒有連接條件的時候會產生笛卡兒積，這種表連接方式就叫笛卡兒連接。在多表關聯的時候，兩個表沒有直接關聯條件，但是優化器錯誤地把某個表返回的Rows算為1行（注意必是1行），這個時候也可能發生笛卡兒連接。
標量子查詢(SCALAR SUBQURY)
當一個子查詢介於select與from之間，這種子查詢就叫標量子查詢。
標量子查詢類似一個天然的嵌套循環，而且驅動表固定為主表。大家是否還記得：嵌套循環被驅動表的連接列必須包含在索引中。同理，標量子查詢中子查詢的表連接列也必須包含在索引中。
我們建議在工作中，盡量避免使用標量子查詢，假如主表返回大量數據，主表的連接列基數很高，那麼子查詢中的表會被多次掃描，從而嚴重影響SQL性能。如果主表數據量小，或者主表的連接列基數很低，那麼這個時候我們也可以使用標量子查詢，但是記得要給子查詢中表的連接列建立索引。
當SQL裡面有標量子查詢，我們可以將標量子查詢等價改寫為外連接，從而使它 們可以進行HASH連接。
為什麼要將標量子查詢改寫為外連接而不是內連接呢？因為標量子查詢是一個傳值的過程，如果主表傳值給子查詢，子查詢沒有查詢到數據，這個時候會顯示NULL。如果將標量子查詢改寫為內連接，會丟失沒有關聯上的數據。
半連接：兩表關聯只返回一個表的數據就叫半連接。半連接一般就是指的in和exists。在 SQL優化實戰中，半連接的優化是最為複雜的。in和exists一般情況下都可以進行等價改寫。
反連接：兩表關聯只返回主表的數據，而且只返回主表與子表沒關聯上的數據，這種連接就叫反連接。反連接一般就是指的not in和not exists。
需要注意的是，not in裡面如果有null，整個查詢會返回空，而in裡面有null，查詢不受null影響。所以在將not exists等價改寫為not in的時候，要注意null。一般情況下，如果反連接採用not in寫法，我們需要在where條件中剔除null。
FILTER：如果子查詢（in/exists/not in/not exists）沒能展開（unnest），在執行計劃中就會產生FILTER，FILTER類似嵌套循環，FILTER的演算法與標量子查詢一模一樣。
IN 與EXISTS 誰快誰慢？
如果執行計劃中沒有產生FILTER，那麼我們可以參考以下思路：in與exists是半連接，半連接也屬於表連接，那麼既然是表連接，我們需要關心兩表的大小以及兩表之間究竟走什麼連接方式，還要控制兩表的連接方式，才能隨心所欲優化SQL，而不是去記什麼時候in跑得快，什麼時候exists跑得快。
SQL 語句的本質：標量子查詢可以改寫為外連接（需要注意表與表之間關係，去重），半連接可以改寫為內連接（需要注意表與表之間關係，去重），反連接可以改寫為外連接（不需要注意表與表之間關係，也不需要去重）。
SQL語句中幾乎所有的子查詢都能改寫為表連接的方式，所以我們提出這個觀點：SQL語句其本質就是表連接（內連接與外連接），以及表與表之間是幾比幾 關係再加上GROPU BY。
