MySQL 最佳化（三）

文章目錄

• 7.2.2 估算查詢效能

7.2.2 估算查詢效能

 

 

在大多數情況下，可以通過統計磁碟搜尋次數來估算查詢的效能。對小表來說，通常情況下只需要搜尋一次磁碟就能找到對應的記錄（因為索引可能已經緩衝起來了）。對大表來說，大致可以這麼估算，它使用B樹做索引，想要找到一條記錄大概需要搜尋的次數為：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1。

在MySQL中，一個索引塊通常是1024bytes，資料指標通常是4bytes。對於一個有500,000條記錄、索引長度為3bytes（medium integer）的表來說，根據上面的公式計算得到需要做 log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 次搜尋。

這個表的索引大概需要 500,000 * 7 * 3/2 = 5.2MB的儲存空間（假定典型的索引緩衝的2/3），因此應該會有更多的索引在記憶體中，並且可能只需要1到2次調用就能找到對應的記錄。

對於寫來說，大概需要4次（甚至更多）搜尋才能找到新的索引位置，更新記錄時通常需要2次搜尋。

請注意，前面的討論中並沒有提到應用程式的效能會因為log N的值越大而下降。只要所有的東西都能由作業系統或者SQL伺服器緩衝起來，那麼效能只會因為資料表越大而稍微下降。當資料越來越大之後，就不能全部放到緩衝中去了，就會越來越慢了，除非應用程式是被磁碟搜尋約束的（它跟隨著的log N值增加而增加）。為了避免這種情況，可以在資料量增大以後也隨著增大索引緩衝容量。對 MyISAM 類型表來說，索引緩衝容量是由系統變數 key_buffer_size 控制的。詳情請看"7.5.2 Tuning Server Parameters"。

 

7.2.3 Select 查詢的速度

 

通常情況下，想要讓一個比較慢的 Select ... Where 查詢變得更快的第一件事就是，先檢查看看是否可以增加索引。所有對不同表的訪問都通常使用索引。可以使用 EXPLAIN 語句來判斷 Select 使用了哪些索引。詳情請看"7.4.5 How MySQL Uses Indexes"和"7.2.1 EXPLAIN Syntax (Get Information About a Select)"。

以下是幾個常用的提高 MyISAM 表查詢速度的忠告：

 

 

• 想要讓MySQL將查詢最佳化的速度更快些，可以在資料表已經載入完全部資料後執行行 ANALYZE TABLE 或運行 myisamchk --analyze 命令。它更新了每個索引部分的值，這個值意味著相同記錄的平均值（對於唯一索引來說，這個值則一直都是 1）。MySQL就會在當你使用基於一個非恒量運算式的兩表串連時，根據這個值來決定使用哪個索引。想要查看結果，可以在分析完資料表後運行 SHOW INDEX FROM tbl_name 查看 Cardinality 欄位的值。myisamchk --description --verbose 顯示了索引的分布資訊。

   

   

• 想要根據一個索引來排序資料，可以運行 myisamchk --sort-index --sort-records=1 （如果想要在索引 1 上做排序）。這對於有一個唯一索引並且想根據這個索引的順序依次讀取記錄的話來說是一個提高查詢速度的好辦法。不過要注意的是，第一次在一個大表上做排序的話將會耗費很長時間。

7.2.4 MySQL如何最佳化 Where 子句

 

這個章節講述了最佳化程式如何處理 Where 子句。例子中使用了 Select 語句，但是在 Delete 和 Update 語句中對 Where 子句的最佳化是一樣的。

注意，關於MySQL最佳化的工作還在繼續，因此本章節還沒結束。MySQL做了很多最佳化工作，而不僅僅是文檔中提到的這些。

MySQL的一些最佳化做法如下：

 

 

• 去除不必要的括弧：

   

     ((a AND b) AND c or (((a AND b) AND (c AND d))))    -> (a AND b AND c) or (a AND b AND c AND d)    

   

• 展開常量：

   

     (a<b AND b=c) AND a=5    -> b>5 AND b=c AND a=5    

   

• 去除常量條件（在展開常量時需要）：

   

     (B>=5 AND B=5) or (B=6 AND 5=5) or (B=7 AND 5=6)    -> B=5 or B=6    

   

• 常量表達示在索引中只計算一次

   

   

• 在單獨一個表上做 COUNT(*) 而不使用 Where 時， 對於 MyISAM 和 HEAP 表就會直接從表資訊中檢索結果。在單獨一個表上做任何錶 NOT NULL 達式查詢時也是這樣做。

   

   

• 預先探測無效的常量運算式。MySQL會快速探測一些不可能的 Select 語句並且不返回任何記錄。

   

   

• 當沒用 GROUP BY 或分組函數時，HAVING 和 Where 合并（COUNT(), MIN() 等也是如此）。

   

   

• 為表串連中的每個表構造一個簡潔的 Where 語句，以得到更快的 Where 計算值並且儘快跳過記錄。

   

   

• 查詢中所有的常量表都會比其他表更早讀取。一個常量表符合以下幾個條件：
  • 空表或者只有一條記錄。

     

     

  • 與在一個 UNIQUE 索引、或一個 PRIMARY KEY 的 Where 子句一起使用的表，這裡所有的索引部分和常數運算式做比較並且索引部分被定義為 NOT NULL。

  以下的幾個表都會被當成常量表：

   

  Select * FROM t Where primary_key=1;    Select * FROM t1,t2    Where t1.primary_key=1 AND t2.primary_key=t1.id;    

   

• MySQL會進各種可能找到表串連最好的串連方法。 如果在 orDER BY 和 GROUP BY 子句中的所有欄位都來自同一個表的話，那麼在串連時這個表就會優先處理。

   

   

• 如果有 ORDER BY 子句和一個不同的 GROUP BY 子句，或者如果 ORDER BY 或 GROUP BY 中的欄位都來自其他的表而非串連順序中的第一個表的話，就會建立一個暫存資料表了。

   

   

• 如果使用 SQL_SMALL_RESULT，MySQL就會使用記憶體暫存資料表了。

   

   

• 所有的表索引都會查詢，最好的情況就是所有的索引都會被用到，除非最佳化程式認為全表掃描的效率更高。同時，資料表掃描是基於判斷最好的索引範圍超過資料表的30%。 現在，最佳化程式複雜多了，它基於對一些附加因素的估計，例如表大小，記錄總數，I/O塊大小，因此就不能根據一個固定的百分比來決定是選擇使用索引還是直接掃描資料表。

   

   

• 在某些情況下，MySQL可以直接從索引中取得記錄而無需查詢資料檔案。如果所有在索引中使用的欄位都是數字類型的話，只需要用索引樹就能完成查詢。

   

   

• 每條記錄輸出之前，那些沒有匹配 HAVING 子句的就會被跳過。

以下幾個查詢速度非常快：

 

Select COUNT(*) FROM tbl_name;Select MIN(key_part1),MAX(key_part1) FROM tbl_name;Select MAX(key_part2) FROM tbl_nameWhere key_part1=constant;Select ... FROM tbl_nameorDER BY key_part1,key_part2,... LIMIT 10;Select ... FROM tbl_nameorDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

以下幾個查詢都是使用索引樹，假使那些索引欄位都是數字型：

Select key_part1,key_part2 FROM tbl_name Where key_part1=val;Select COUNT(*) FROM tbl_nameWhere key_part1=val1 AND key_part2=val2;Select key_part2 FROM tbl_name GROUP BY key_part1;

以下幾個查詢使用索引來取得經過順序排序後的記錄而無需經過獨立的排序步驟：

Select ... FROM tbl_nameorDER BY key_part1,key_part2,... ;Select ... FROM tbl_nameorDER BY key_part1 DESC, key_part2 DESC, ... ;

 

7.2.5 MySQL 如何最佳化 OR 子句

Index Merge 方法用於使用 ref, ref_or_null, 或 range 掃描取得的記錄合并起來放到一起作為結果。這種方法在表條件是或條件 ref, ref_or_null, 或 range ，並且這些條件可以用不同的鍵時採用。
"join"類型的最佳化是從 MySQL 5.0.0 開始才有的，代表者在索引的效能上有著標誌性的改進，因為使用老規則的話，資料庫最多隻能對每個參考資料表使用一個索引。
在 EXPLAIN 的結果中，這種方法在 type 欄位中表現為 index_merge。這種情況下，key 欄位包含了所有使用的索引列表，並且 key_len 欄位包含了使用的索引的最長索引部分列表。
例如：

 

Select * FROM tbl_name Where key_part1 = 10 or key_part2 = 20;Select * FROM tbl_nameWhere (key_part1 = 10 or key_part2 = 20) AND non_key_part=30;Select * FROM t1,t2Where (t1.key1 IN (1,2) or t1.key2 LIKE 'value%')AND t2.key1=t1.some_col;Select * FROM t1,t2Where t1.key1=1AND (t2.key1=t1.some_col or t2.key2=t1.some_col2);

 

7.2.6 MySQL 如何最佳化 IS NULL

MySQL在 col_name IS NULL 時做和 col_name = constant_value 一樣的最佳化。例如，MySQL使用索引或者範圍來根據 IS NUL L搜尋 NULL。

 

Select * FROM tbl_name Where key_col IS NULL;Select * FROM tbl_name Where key_col <=> NULL;Select * FROM tbl_nameWhere key_col=const1 or key_col=const2 or key_col IS NULL;

如果一個 Where 子句包括了一個 col_name IS NULL 條件，並且這個欄位聲明為 NOT NULL，那麼這個運算式就會被最佳化。當欄位可能無論如何都會產生 NULL 值時，就不會再做最佳化了；例如，當它來自一個 LEFT JOIN 中右邊的一個表時。

MySQL 4.1.1或更高會對串連 col_name = expr AND col_name IS NULL 做額外的最佳化， 常見的就是子查詢。EXPLAIN 當最佳化起作用時會顯示 ref_or_null。

最佳化程式會為任何索引部分處理 IS NULL。

以下幾個例子中都做最佳化了，假使欄位 a 和 表 t2 中 b 有索引了：

Select * FROM t1 Where t1.a=expr or t1.a IS NULL;Select * FROM t1,t2 Where t1.a=t2.a or t2.a IS NULL;Select * FROM t1,t2Where (t1.a=t2.a or t2.a IS NULL) AND t2.b=t1.b;Select * FROM t1,t2Where t1.a=t2.a AND (t2.b=t1.b or t2.b IS NULL);Select * FROM t1,t2Where (t1.a=t2.a AND t2.a IS NULL AND ...)or (t1.a=t2.a AND t2.a IS NULL AND ...);

ref_or_null 首先讀取引用鍵，然後獨立掃描索引值為 NULL 的記錄。

請注意，最佳化程式只會處理一個 IS NULL 層級。下面的查詢中，MySQL只會使用鍵來查詢運算式 (t1.a=t2.a AND t2.a IS NULL) 而無法使在 b 上使用索引部分：

Select * FROM t1,t2Where (t1.a=t2.a AND t2.a IS NULL)or (t1.b=t2.b AND t2.b IS NULL);

 

7.2.7 MySQL 如何最佳化 DISTINCT

 

在很多情況下，DISTINCT 和 ORDER BY 一起使用時就會建立一個暫存資料表。

注意，由於 DISTINCT 可能需要用到 GROUP BY，就需要明白MySQL在 ORDER BY 或 HAVING 子句裡的欄位不在選中的欄位列表中時是怎麼處理的。詳情請看"13.9.3 GROUP BY with Hidden Fields"。

當 LIMIT row_count 和 DISTINCT 一起使用時，MySQL在找到 row_count 不同記錄後就會立刻停止搜尋了。

如果沒有用到來自查詢中任何錶的欄位時，MySQL在找到第一個匹配記錄後就會停止搜尋這些沒沒用到的表了。在下面的情況中，假使 t1 在 t2 前就使用了（可以通過 EXPLAIN 分析知道），MySQL就會在從 t2 中找到第一條記錄後就不再讀 t2 了（為了能和中 t1 的任何特定記錄匹配）：

Select DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

 

7.2.8 MySQL 如何最佳化 LEFT JOIN 和 RIGHT JOIN

A LEFT JOIN B join_condition 在MySQL中實現如下：

 

 

• 表 B 依賴於表 A 以及其依賴的所有表。

   

   

• 表 A 依賴於在 LEFT JOIN 條件中的所有表（除了 B）。

   

   

• LEFT JOIN 條件用於決定如何從表 B 中讀取記錄了（換句話說，Where 子句中的任何條件都對此不起作用）。

   

   

• 所有標準的串連最佳化都會執行，例外的情況是有一個表總是在它依賴的所有表之後被讀取。如果這是一個迴圈的依賴關係，那麼MySQL會認為這是錯誤的。

   

   

• 所有的標準 Where 最佳化都會執行。

   

   

• 如果 A 中有一條記錄匹配了 Where 子句，但是 B 中沒有任何記錄匹配 ON 條件，那麼就會產生一條 B 記錄，它的欄位值全都被置為 NULL。

   

   

• 如果使用 LEFT JOIN 來搜尋在一些表中不存在的記錄，並且 Where 部分中有檢測條件：col_name IS NULL，col_name 欄位定義成 NOT NULL 的話，MySQL就會在找到一條匹配 LEFT JOIN 條件的記錄（用於和特定的索引鍵做聯合）後停止搜尋了。

RIGHT JOIN 的實現和 LEFT JOIN 類似，不過表的角色倒過來了。
串連最佳化程式計算了表串連的次序。表讀取的順序是由 LEFT JOIN 強行指定的，而且使用 STRAIGHT_JOIN 能協助串連最佳化程式更快地執行，因為這就會有更少的表排隊檢查了。注意，這裡是指如果你執行下面這種類型的查詢後，MySQL就會對 b 做一次全表掃描，因為 LEFT JOIN 強制要求了必須在讀 d 之前這麼做：

 

Select *FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)Where b.key=d.key;

解決這種情況的方法是按照如下方式重寫查詢：

Select *FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)Where b.key=d.key;

從4.0.14開始，MySQL做如下 LEFT JOIN 最佳化：如果對產生的 NULL 記錄 Where 條件總是 假，那麼 LEFT JOIN 就會變成一個普通的串連。
例如，下面的查詢中如果 t2.column1 的值是 NULL 的話，Where 子句的結果就是假了：

Select * FROM t1 LEFT JOIN t2 ON (column1) Where t2.column2=5;

因此，這就可以安全的轉換成一個普通的串連查詢：

Select * FROM t1,t2 Where t2.column2=5 AND t1.column1=t2.column1;

這查詢起來就更快了，因為如果能有一個更好的查詢計劃的話，MySQL就會在 t1 之前就用到 t2 了。想要強行指定表順序的話，可以使用 STRAIGHT_JOIN。