MySQL 優化器可選開關詳解

MySQL 從5.1開始，提供了關于優化器選項的開關選項。 這樣，我們可以強制在優化器糊涂的時候，關閉一些我們認為不該做的事情；或者是當我們想查看優化器為什么要這么選擇的時候，可以手動關閉或者打開這些選項來進行優化策略的改變。
MySQL 從5.5 和 5.6 開始，增加了更多的開關選項。 以便使查詢更加優化。 下面我來一個一個解釋下這些優化開關的含義以及用途。


MySQL 5.1 增加以下開關：
index_merge 
1). index_merge_intersection
如果有兩個單獨的索引都可用，但是其中任何一個都不是最優化的，那么優化器選擇合并兩個索引并且在他倆的結果集中做一個交集，然后根據這個交集對磁盤數據進行匹配。
2). index_merge_union
用于OR，把所有相關索引連接起來，找到記錄對應的ROWID，然后根據ROWID獲取磁盤上的數據。
3). index_merge_sort_union
用于OR，把所有相關索引連接起來，找到記錄對應的ROWID，并且好順序，然后根據ROWID獲取磁盤上的數據。


MySQL 5.5 新增以下開關：
engine_condition_pushdown 
只用于NDB引擎。
不開啟： 所有數據節點的數據都發送到SQL節點來處理。
開啟后： 按照WHERE條件過濾后的數據發送到SQL節點來處理。


MySQL 5.6 增加以下開關：
1. mrr
   mrr_cost_based
       這個是從MySQL 5.6 開始增加的開關。  意思是針對普通索引把資源利用最大化，主要是針對多列索引，也叫組合索引來做基本掃描，然后對匹配的記錄按照主鍵排序，這樣按照有序的主鍵順序從磁盤上掃描需要的全部記錄。 根本功能是把對磁盤的隨機掃描轉化為順序掃描。
主要針對的是數據太大，放不到CACHE里面（比如INNODB，或者MYISAM）。  目前對全索引掃描意義不大。
2. batched_key_access
（在JOIN BUFFER POOL里）當內表被掃描后，針對掃描到的記錄，找到對應的主鍵值并且一次性交給MRR接口。省掉了頻繁跟磁盤交互的IO部分。
3. block_nested_loop
（在JOIN BUFFER POOL里）當內表被掃描后，針對原來的記錄加上一個是否匹配標記，生成新的記錄。這樣下次外表的值如果再來遍歷內表，就只掃描不匹配的即可。省掉部分CPU資源。
4. index_condition_pushdown
不開啟： 對于組合索引（A,B）來說，如果A可以用到索引，B不能用到，那么只能在索引樹上找到A，然后再用A對應的指針到磁盤上遍歷數據。
開啟后： 同樣對于組合索引（A,B）來說，如果A可以用到索引，B不能用到，那么在索引樹上找到A，然后再通過B的過濾條件在索引樹上進行過濾，刪選后的指針到磁盤上遍歷數據。
但是ICP并不能把隨機掃描變為順序掃描,只是減少了對磁盤交互的IO部分。
5. use_index_extensions
主要用于INNODB的第二索引，也就是普通的索引,把索引中包含的主鍵值利用到。比如主鍵為(a,b),索引為(c). 如果用到了索引c，那么把索引變成(c,a,b) 這樣，就可以用到新的組合索引了。
不過這種場合用的也比較少，一般是根據組合主鍵中的第一個字段和普通索引一起來做檢索的時候。


6. semijoin
表示在連接的時候內表去重的過程 。
比如對以下的查詢，a.class_id 就只需要掃描一次和b.id相同的記錄就可以不在繼續了。因為不需要關心b表里面是否有多少相同的class_id，只需要知道有還是沒有就行了。
所以一般用在對子查詢的優化居多。

    mysql> select b.class_name from class as b, student as a where a.class_id = b.id;                                                              
    +------------+  
    | class_name |  
    +------------+  
    | xinxi      |  
    | xinxi      |  
    +------------+  
    2 rows in set (0.00 sec)  

7. firstmatch
只選用內表的第一條與外表匹配的記錄。
8. loosescan
把內表的數據基于索引分組，取每組第一條數據即可。
9. materialization
    subquery_materialization_cost_based
把內表去重然后生成有對應索引的臨時表（有點類似其他數據中的物化視圖），然后通過外表的對應鍵值遍歷這張臨時表。
主要針對不能抓華為半連接的檢索類型。

來自：http://blog.csdn.net/yueliangdao0608/article/details/21175641