概述
有的時候我們在操作資料庫時會将兩個或多個資料表關聯起來通過一些條件篩選資料，在關聯表時我們要遵循一些原則，這樣會使我們編寫的SQL 語句在效率上快很多。
一、需要優化的查詢
使用explain常見的掃描類型有：system>const>eq_ref>ref>range>index>ALL 其掃描速度由快到慢；
如果Extra出現了
- Using temporary；
- 有分頁時出現了Using filesort則表示使用不了索引
- rows過多，或者幾乎是全表的記錄數；
- key 是 (NULL)；
- possible_keys 出現過多(待選)索引
都需要做優化。
二、驅動表的含義
MySQL 表關聯的算法是 Nest Loop Join，是通過驅動表的結果集作為循環基礎資料，然後一條一條地通過該結果集中的資料作為過濾條件到下一個表中查詢資料，然後合并結果：
- EXPLAIN 結果中，第一行出現的表就是驅動表
- 對驅動表可以直接排序，對非驅動表(的字段排序)需要對循環查詢的合并結果(臨時表)進行排序(Important!)，即using temporary;
- [驅動表] 的定義為：1)滿足查詢條件的記錄行數少的表為[驅動表]；2)未指定查詢條件時，行數少的表為[驅動表](Important!)。
- 優化的目标是盡可能減少JOIN中Nested Loop的循環次數，以此保證：永遠用小結果集驅動大結果集(Important!)！：A JOIN B，A為驅動，A中每一行和B進行循環JOIN，看是否滿足條件，是以當A為小結果集時，越快。
- NestedLoopJoin實際上就是通過驅動表的結果集作為循環基礎資料，然後一條一條的通過該結果集中的資料作為過濾條件到下一個表中查詢資料，然後合并結果。如果還有第三個參與Join，則再通過前兩個表的Join結果集作為循環基礎資料，再一次通過循環查詢條件到第三個表中查詢資料，如此往複。
如果還有第三個參與 Join，則再通過前兩個表的 Join 結果集作為循環基礎資料，再一次通過循環查詢條件到第三個表中查詢資料，如此往複，基本上MySQL采用的是最容易了解的算法來實作join。是以驅動表的選擇非常重要，驅動表的資料小可以顯著降低掃描的行數。
MySQL的 Join 都是通過嵌套循環來實作的。驅動結果集越大，所需要循環就越多，那麼被驅動表的通路次數自然也就越多，而每次通路被驅動表，即使需要的邏輯 IO 很少，循環次數多了，總量也不可能小，而且每次循環都不能避免消耗CPU，是以 CPU 運算量也會跟着增加。
當有order by條件時，如select * from a inner join b where 1=1 and other condition order by a.col；使用explain解釋語句；
- 如果第一行的驅動表為a，則效率會非常高，無需優化；
- 否則，因為隻能對驅動表字段直接排序的緣故，會出現using temporary，是以此時需要使用STRAIGHT_JOIN明确a為驅動表，來達到使用a.col上index的優化目的；或者使用left join，而STRAIGHT_JOIN為inner join且使用a作為驅動表。
三、Mysql的STRAIGHT_JOIN
STRAIGHT_JOIN，在資料量大的聯表查詢中靈活運用的話，能大大縮短查詢時間。
首先來解釋下STRAIGHT_JOIN到底是用做什麼的：
STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.
意思就是說STRAIGHT_JOIN功能同join類似，但能讓左邊的表來驅動右邊的表，能改表優化器對于聯表查詢的執行順序。
需要注意的是：
STRAIGHT_JOIN隻适用于inner join，并不适用于left join，right join。(因為left join，right join已經代表指定了表的執行順序)
盡可能讓優化器去判斷，因為大部分情況下mysql優化器是比人要聰明的。而且你可能不能确定未來表的大小變化。使用STRAIGHT_JOIN一定要慎重。
四、in、exists、join與驅動表的選擇關系
以下兩張表的dept_id字段應建立索引。
1、in
SELECT * FROM t_emp WHERE dept_id IN (SELECT dept_id FROM t_dept);// 這條SQL 語句相當于：for SELECT dept_id FROM t_dept for SELECT * FROM t_emp WHERE t_emp.dept_id = t_dept.dept_id
雖然SQL 語句是主查詢員工資訊，子查詢部門id ，但是MySql 的執行順序會先執行子查詢，再執行主查詢，然後獲得我們要查詢的資料。
通過上面IN 操作的執行順序，可以看到是先查詢部門表再根據部門表查出來的id 資訊查詢員工資訊。一般情況下員工表肯定會有很多的員工資訊，但是部門表隻會有很少的資料資訊，如果先通過查詢部門表資訊查詢員工資訊，以小表(t_dept)的查詢結果，去驅動大表(t_emp)，這種查詢方式是效率很高的
2、exists
SELECT * FROM t_emp WHERE EXISTS (SELECT 1 FROM t_dept WHERE t_dept.dept_id = t_emp.dept_id);// 這條SQL 語句相當于： for SELECT * FROM t_emp for SELECT * FROM t_dept WHERE t_dept.dept_id = t_emp.dept_id
将主查詢的資料放在子查詢中做條件驗證，根據結果TRUE 和 FALSE 來決定主查詢中的資料是否需要保留。EXISTS 子查詢隻傳回TRUE 或 FALSE ，是以子查詢中的SELECT * 可以是SELECT 1 或者其他，MySql 的官方說在實際執行時會忽略SELECT 清單，是以是沒有 什麼差別的。EXISTS 子查詢其實在執行時，MySql 已經對它做了一些優化并不是對每條資料進行對比。
可以看出使用EXISTS 查詢時，首先查詢員工表，然後根據部門表的查詢條件傳回的TRUE 或者 FALSE ，再決定員工表中的資訊是否需要保留。這不就是用大的資料表(t_emp) 去驅動小的資料表小的資料表(t_dept)了嗎？雖然這種方式也可以查出想要的資料，但是這種查詢方式是不值得提倡的。
3、join
3.1、join選擇驅動表
為什麼一般情況下join的效率要高于left join很多？一般情況下參與聯合查詢的兩張表都會一大一小，如果是join，在沒有其他過濾條件的情況下MySQL會選擇小表作為驅動表，但是left join一般用作大表去join小表，而left join本身的特性決定了MySQL會用大表去做驅動表，這樣下來效率就差了不少，
總之：
1).當使用left join時，左表是驅動表，右表是被驅動表
2).當使用right join時，右表時驅動表，左表是驅動表
3).當使用join時，mysql會選擇資料量比較小的表作為驅動表，大表作為被驅動表
考慮到查詢效率，能用join就不要用leftight join 使用外連接配接非常影響查詢效率，就算要用也要用資料量最小的表作為驅動表來驅動大表。
3.2、join查詢在有索引條件下
驅動表有索引不會使用到索引被驅動表建立索引會使用到索引
在以小表驅動大表的情況下，再給大表建立索引會大大提高執行速度
3.3、left join 的通用法則
凡是不符合 WHERE 子句中條件的記錄都不會參與連接配接。隻要在搜尋條件中指定關于被驅動表相關列的值不為 NULL，那麼外連接配接中在被驅動表中找不到符合 ON 子句條件的驅動表記錄也就被排除出最後的結果集了，也就是說：在這種情況下：外連接配接和内連接配接也就沒有什麼差別了
即：如果where條件中含有右表的非空條件(除開is null)，則left join語句等同于join語句，可直接改寫成join語句。
3.4、如何優化left join：
1)、條件中盡量能夠過濾一些行将驅動表變得小一點，用小表去驅動大表
2)、右表的條件列一定要加上索引(主鍵、唯一索引、字首索引等)，最好能夠使type達到range及以上(ref,eq_ref,const,system)
後面會分享更多devops和DBA方面的内容，感興趣的朋友可以關注下~