MySQL查詢最佳化工具工作原理解析

標籤：

手冊上查詢最佳化工具概述

查詢最佳化工具的任務是發現執行SQL查詢的最佳方案。大多數查詢最佳化工具，包括MySQL的查詢最佳化工具，總或多或少地在所有可能的查詢評估方案中搜尋最佳方案。對於聯結查詢，MySQL最佳化器所調查的可能的方案數隨查詢中所引用的表的數目呈指數增長。對於小數量的表(典型小於7-10)，這不是一個問題。然而，當提交的查詢更大時，查詢最佳化所花的時間會很容易地成為伺服器效能的主要瓶頸。
查詢最佳化的一個更加靈活的方法是允許使用者控制最佳化器詳盡地搜尋最佳查詢評估方案。一般思想是最佳化器調查的方案越少，它編譯一個查詢所花費的時間越少。另一方面，因為最佳化器跳過了一些方案，它可能錯過一個最佳方案。
最佳化器關於方案數量評估的行為可以通過兩個系統變數來控制：

• optimizer_prune_level變數告訴最佳化器根據對每個表訪問的行數的估計跳過某些方案。我們的實驗顯示該類“有根據的猜測”很少錯過最佳方案，並且可以大大降低查詢編輯次數。這就是為什麼預設情況該選項為on(optimizer_prune_level=1)。然而，如果你認為最佳化器錯過了一個更好的查詢方案，則該選項可以關閉(optimizer_prune_level=0)，風險是查詢編輯花費的時間更長。請注意即使使用該啟發，最佳化器仍然可以探測呈指數數目的方案。

• ptimizer_search_depth變數告訴最佳化器對於每個未完成的“未來的”方案，應查看多深，以評估是否應對它進一步擴大。optimizer_search_depth值較小會使查詢編輯次數大大減小。例如，如果optimizer_search_depth接近於查詢中表的數量，對12、13或更多表的查詢很可能需要幾小時甚至幾天的時間來編譯。同時，如果用optimizer_search_depth等於3或4編輯，對於同一個查詢，編譯器編譯時間可以少於1分鐘。如果不能確定合理的optimizer_search_depth值，該變數可以設定為0，告訴最佳化器自動確定該值。
  我們可以通過show variables 來查看這些參數
  
  備忘（手冊網址：http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter）

個人理解

從官方手冊上看，可以理解為，MySQL採用了基於開銷的最佳化器，以確定處理查詢的最解方式，也就是說執行查詢之前，都會先選擇一條自以為最優的方案，然後執行這個方案來擷取結果。在很多情況下，MySQL能夠計算最佳的可能查詢計劃，但在某些情況下，MySQL沒有關於資料的足夠資訊，或者是提供太多的相關資料資訊，估測就不那麼友好了。
但是感覺手冊上，並沒有說MySQL怎麼去尋找最優方案呢？
通過查詢相應的資料，個人理解如下
MySQL最佳化器中，一個主要的目標是只要可能就是用索引，而且使用條件最嚴格的索引來儘可能多、儘可能快地排除那些不符合索引條件的資料行，說白了就是選擇怎樣使用索引，當然最佳化器還受其他的影響。為了更直觀，下面將通過例子來說明。
建立一個表：

CREATE TABLE t8(id1 INT NOT NULL ,id2 INT NOT NULL,KEY id1_key(`id1`),KEY id2_key(`id2`)) ENGINE=MYISAM DEFAULT CHARSET=utf8;

插入幾行資料如下：

當我執行如下查詢語句時候，查詢最佳化工具會怎樣進行最佳化呢？

select * from t8 where id1=1 and id2=0;

當然，MySQL不會傻到，從t8表中的一行開始，然後一行行的去比較，id1與id2。最佳化器會先分析資料表，得知有索引id1_key與id2_key，如果先判斷id1_key的話，然後需要從4行資料中排除3行資料；如果先判斷id2_key的話，然後需要從2行中排除1行。對人來說，這兩種方式沒有什麼區別，但是對於程式而言，先判斷id2_key需要較少的計算和磁碟輸入輸出。因此，查詢最佳化工具會規定程式，先去檢驗id2_key索引，然後在從中挑出id2為0的資料行。
通過，我們可以看出，可以選擇的索引有id1_key與id2_key，但是實際用到的索引只有id2_key

如果將SQL語句改為 select * from t8 where id1=1 and id2=0;執行情況也是一樣的，不區分前後。如：

當然，如果將程式，修改為如下

select * from t8 where id1=5 and id2=0;

也可以分析得出，會使用id1_key索引

當然，如果在建立一個複合索引

ALTER TABLE t8 ADD KEY id1_id2_key(`id1`,`id2`)

此時，在此執行select * from t8 where id1=1 and id2=0; 當然會考慮使用id1_id2_key索引。

通過上面的例子，可以理解查詢最佳化工具在查詢的時候，是選擇哪一個索引作為最合適的索引。除此，也提示我們，要謹慎選擇建立索引。如，上面建立了三個索引（id1_key、id1_key、id1_id2_key），但是最佳化器最佳化程式時候，每次只能從中選擇一個最合適的，如果建立過多，不僅僅是給資料的更新和插入帶來了壓力，同時也增加了最佳化器的壓力。

分析最佳化器最佳化過程中的資訊

其實，在上面已經查看過最佳化器最佳化過程中的資訊，無非就是使用explain。在這裡，在集中說說，裡面的參數意義。如

id： MySQL Query Optimizer 選定的執行計畫中查詢的序號。表示查詢中執行 select 子句或動作表的順序,id值越大優先順序越高,越先被執行。id 相同,執行順序由上至下。
select_type：查詢類型，SIMPLE、PRIMARY、UNION、DEPENDENT UNION等。
table：顯示這一行的資料是關於哪張表的
type：這是重要的列，顯示串連使用了何種類型。從最好到最差的連線類型為const、eq_reg、ref、range、indexhe和all
possible_keys：顯示可能應用在這張表中的索引。如果為空白，沒有可能的索引。可以為相關的域從where語句中選擇一個合適的語句
key： 實際使用的索引。如果為null，則沒有使用索引。很少的情況下，mysql會選擇最佳化不足的索引。這種情況下，可以在select語句中使用use index（indexname）來強制使用一個索引或者用ignore index（indexname）來強制mysql忽略索引
key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好
ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數
rows：mysql認為必須檢查的用來返回請求資料的行數
extra：關於mysql如何解析查詢的額外資訊。

調節MySQL最佳化器的最佳化影響索引的選擇

當我們在執行select * from t8 where id1=1 and id2=0; 語句的時候，最佳化器會id1_id2_key索引，但我們可以通過IGNORE INDEX、 IGNORE INDEX來影響索引的選擇

強制索引

通過FORCE INDEX(索引1[，索引2])或者使用USE INDEX(索引1[，索引2])，來指定使用哪個索引，也可以指定多個索引，讓最佳化器從中挑選。

忽略索引

可以使用IGNORE INDEX(索引1[，索引2])來忽略一些索引，這樣最佳化器，就不會考慮使用這些所有，減少最佳化器最佳化時間。

影響最佳化器使用資料表的順序

一般情況下，MySQL最佳化器會自行決定按照哪種順序掃描資料表才能最快地檢索出資料，但是我們可以通過STRAGHT_JOIN強制最佳化器按特定的順序使用資料表，畢竟最佳化器做的判斷不一定都是最優的。使用原則是，讓限制最強的選取操作最先執行。STRAIGHT_JOIN可以放在SELECT後面，也可以放在FROM子句中。
如

可以看出，無論from t8,t6還是from t6，t8，都是先檢索t6中的表。但是使用STRAIGHT_JOIN的話，就會按照SQL中順序。

為什麼最佳化器要選擇先判斷t6中的資料呢？一個主要的原因，因為t6中資料更少。

如果將t8中資料刪除幾行後，很明顯MySQL最佳化器選擇順序資料表的順序就會發生變化。

控制SQL語句的優先權

在高並發的網站中，因為MySQL預設的是寫優先，有可能導致一些讀操作有效時間內得不到執行機會，HIGH_PRIORITY可以使用在select和insert操作中，讓MYSQL知道，這個操作優先進行。

LOW_PRIORITY可以使用在insert和update操作中，讓mysql知道，這個操作將優先權將降低。

INSERT DELAYED告訴MySQL，這個操作將會延時插入。
INSERT DELAYED INTO，是用戶端提交資料給MySQL，MySQL返回OK狀態給用戶端。而這是並不是已經將資料插入表，而是儲存在記憶體裡面等待排隊。當mysql有空餘時，再插入。另一個重要的好處是，來自許多用戶端的插入被集中在一起，並被編寫入一個塊。這比執行許多獨立的插入要快很多，因為它較少了I/O操作。壞處是，不能返回自動遞增的ID，以及系統崩潰時，MySQL還沒有來得及插入資料的話，這些資料將會丟失。

控制查詢緩衝

在實際開發中，一些資料對即時性要求特別高，或者並不經常使用（可能幾天就執行一次或兩次）,這樣就需要把緩衝關了,不管這條SQL語句是否被執行過，伺服器都不會在緩衝區中尋找該資料，每次都會從磁碟中讀取。因為如果即時性要求特別高，緩衝中資料可能和磁碟中的就不同步，如果資料不經常使用，被緩衝起來，就會佔用記憶體。
在my.ini中的query_cache_type，使用來控製表緩衝的。這個變數有三個取值：0,1,2，分別代表了off、on、demand。
0：表示query cache 是關閉。
1：表示查詢總是先到查詢快取中尋找，即使使用了sql_no_cache仍然查詢快取，因為sql_no_cache只是不緩衝查詢結果，而不是不使用查詢結果。
2：表示只有在使用了SQL_CACHE後，才先從緩衝中查詢資料，仍然將查詢結果緩衝起來。
我本機快取是關閉的，，如。

關於MySQL緩衝可以參考這裡
（http://blog.csdn.net/hsd2012/article/details/51526707）

MySQL查詢最佳化工具工作原理解析