MySQL 查詢語句最佳化思路

標籤：range   down   after   optimizer   不包含   列印   group   base   條件   

query 語句的最佳化思路和原則主要提現在以下幾個方面：
1. 最佳化更需要最佳化的Query；
2. 定位最佳化對象的效能瓶頸；
3. 明確的最佳化目標；
4. 從 Explain 入手；
5. 多使用profile
6. 永遠用小結果集驅動大的結果集；
7. 儘可能在索引中完成排序；
8. 只取出自己需要的Columns；
9. 僅僅使用最有效過濾條件；
10. 儘可能避免複雜的Join和子查詢

關於explain

用法：explain select * from tables1 where 1 ...

先看一下在 MySQL Explain功能中給我們展示的各種資訊的解釋：
◆ ID： Query Optimizer 所選定的執行計畫中查詢的序號；
◆ Select_type：所使用的查詢類型，主要有以下這幾種查詢類型
　　◇ DEPENDENT SUBQUERY：子查詢中內層的第一個SELECT，依賴於外部查詢的結果集；
　　◇ DEPENDENT UNION：子查詢中的UNION，且為UNION中從第二個SELECT開始的後面所有SELECT，同樣依賴於外部查詢的結果集；
　　◇ PRIMARY：子查詢中的最外層查詢，注意並不是主鍵查詢；
　　◇ SIMPLE：除子查詢或者UNION之外的其他查詢；
　　◇ SUBQUERY：子查詢內層查詢的第一個SELECT，結果不依賴於外部查詢結果集；
　　◇ UNCACHEABLE SUBQUERY：結果集無法緩衝的子查詢；
　　◇ UNION： UNION語句中第二個SELECT開始的後面所有SELECT，第一個SELECT為PRIMARY
　　◇ UNION RESULT： UNION 中的合并結果；
◆ Table：顯示這一步所訪問的資料庫中的表的名稱；
◆ Type：告訴我們對錶所使用的訪問方式，主要包含如下集中類型；
　　◇ all：全表掃描
　　◇ const： 讀常量，且最多隻會有一條記錄匹配，由於是常量，所以實際上只需要讀一次；
　　◇ eq_ref： 最多隻會有一條匹配結果，一般是通過主鍵或者唯一鍵索引來訪問；
　　◇ fulltext：
　　◇ index：全索引掃描；
　　◇ index_merge：查詢中同時使用兩個（或更多）索引，然後對索引結果進行merge之後再讀取表資料；
　　◇ index_subquery：子查詢中的返回結果欄位組合是一個索引（或索引組合），但不是一個主鍵或者唯一索引；
　　◇ rang：索引範圍掃描；
　　◇ ref： Join語句中被驅動表索引引用查詢；
　　◇ ref_or_null：與ref的唯一區別就是在使用索引引用查詢之外再增加一個空值的查詢；
　　◇ system：系統資料表，表中只有一行資料；
　　◇ unique_subquery：子查詢中的返回結果欄位組合是主鍵或者唯一約束；
◆ Possible_keys： 該查詢可以利用的索引. 如果沒有任何索引可以使用，就會顯示成null，這一項內容對於最佳化時候索引的調整非常重要；
◆ Key： MySQL Query Optimizer 從 possible_keys 中所選擇使用的索引；
◆ Key_len：被選中使用索引的索引鍵長度；
◆ Ref： 列出是通過常量（ const），還是某個表的某個欄位（如果是join）來過濾（通過key）
的；
◆ Rows： MySQL Query Optimizer 通過系統收集到的統計資訊估算出來的結果集記錄條數；
◆ Extra：查詢中每一步實現的額外細節資訊，主要可能會是以下內容：
　　◇ Distinct：尋找distinct 值，所以當mysql找到了第一條匹配的結果後，將停止該值的查詢而轉為後面其他值的查詢；
　　◇ Full scan on NULL key：子查詢中的一種最佳化方式，主要在遇到無法通過索引訪問null值的使用使用；
　　◇ Impossible WHERE noticed after reading const tables： MySQL Query Optimizer 通過收集到的統計資訊判斷出不可能存在結果；
　　◇ No tables： Query 語句中使用 FROM DUAL 或者不包含任何 FROM子句；
　　◇ Not exists：在某些左串連中 MySQL Query Optimizer 所通過改變原有 Query 的組成而使用的最佳化方法，可以部分減少資料訪問次數；
　　◇ Range checked for each record (index map: N)：通過 MySQL 官方手冊的描述，當MySQL Query Optimizer 沒有發現好的可以使用的索引的時候，如果發現如果來自前面的表的列值已知，可能部分索引可以使用。對前面的表的每個行組合， MySQL檢查是否可以使用range或index_merge存取方法來索取行。
　　◇ Select tables optimized away：當我們使用某些彙總函式來訪問存在索引的某個欄位的時候， MySQL Query Optimizer 會通過索引而直接一次定位到所需的資料行完成整個查
詢。當然，前提是在 Query 中不能有 GROUP BY 操作。如使用MIN()或者MAX（）的時候；
　　◇ Using filesort：當我們的 Query 中包含 ORDER BY 操作，而且無法利用索引完成排序操作的時候， MySQL Query Optimizer 不得不選擇相應的排序演算法來實現。
　　◇ Using index：所需要的資料只需要在 Index 即可全部獲得而不需要再到表中取資料；
　　◇ Using index for group-by：資料訪問和 Using index 一樣，所需資料只需要讀取索引即可，而當 Query 中使用了 GROUP BY 或者 DISTINCT 子句的時候，如果分組欄位也在索引中， Extra中的資訊就會是 Using index for group-by；
　　◇ Using temporary：當 MySQL 在某些操作中必須使用暫存資料表的時候，在 Extra 資訊中就會出現Using temporary 。主要常見於 GROUP BY 和 ORDER BY 等操作中。
　　◇ Using where：如果我們不是讀取表的所有資料，或者不是僅僅通過索引就可以擷取所有需要的資料，則會出現 Using where 資訊；
　　◇ Using where with pushed condition：這是一個僅僅在 NDBCluster儲存引擎中才會出現的資訊，而且還需要通過開啟 Condition Pushdown 最佳化功能才可能會被使用。控制參數
為 engine_condition_pushdown 

關於 profiling

用法：

　　開啟：

　　set profiling=1;//開啟

　　select * from tables1 where 1;//隨意查詢

　　show profiles;//列印查詢語句的概要資訊

　　//如下

　　----------+------------+------------------------------------------------------------+
　　| Query_ID | Duration | Query |
　　+----------+------------+------------------------------------------------------------+
　　| 1 | 0.00183100 | show databases |
　　| 2 | 0.00007000 | SELECT DATABASE() |
　　| 3 | 0.00099300 | desc test |
　　| 4 | 0.00048800 | show tables |
　　| 5 | 0.00430400 | desc test_profiling |
　　| 6 | 1.90115800 | select status,count(*) from test_profiling group by status |
　　+----------+------------+------------------------------------------------------------

　　show profile cpu, block io for query 4;//顯示以上show profiles內容的ID4的具體資訊

　　更多功能，還需摸索，以上僅是拋磚引玉

　　

MySQL 查詢語句最佳化思路