7.2.2 估算查詢性能

在大多數(shù)情況下，可以通過統(tǒng)計(jì)磁盤搜索次數(shù)來估算查詢的性能。對小表來說，通常情況下只需要搜索一次磁盤就能找到對應(yīng)的記錄（因?yàn)樗饕赡芤呀?jīng)緩存起來了）。對大表來說，大致可以這么估算，它使用B樹做索引，想要找到一條記錄大概需要搜索的次數(shù)為：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1。

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

這個表的索引大概需要 500,000 * 7 * 3/2 = 5.2MB的存儲空間（假定典型的索引緩沖區(qū)的2/3），因此應(yīng)該會有更多的索引在內(nèi)存中，并且可能只需要1到2次調(diào)用就能找到對應(yīng)的記錄。

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

請注意，前面的討論中并沒有提到應(yīng)用程序的性能會因?yàn)閘og N的值越大而下降。只要所有的東西都能由操作系統(tǒng)或者SQL服務(wù)器緩存起來，那么性能只會因?yàn)閿?shù)據(jù)表越大而稍微下降。當(dāng)數(shù)據(jù)越來越大之后，就不能全部放到緩存中去了，就會越來越慢了，除非應(yīng)用程序是被磁盤搜索約束的（它跟隨著的log N值增加而增加）。為了避免這種情況，可以在數(shù)據(jù)量增大以后也隨著增大索引緩存容量。對 MyISAM 類型表來說，索引緩存容量是由系統(tǒng)變量 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將查詢優(yōu)化的速度更快些，可以在數(shù)據(jù)表已經(jīng)加載完全部數(shù)據(jù)后執(zhí)行行 ANALYZE TABLE 或運(yùn)行 myisamchk --analyze 命令。它更新了每個索引部分的值，這個值意味著相同記錄的平均值（對于唯一索引來說，這個值則一直都是 1）。MySQL就會在當(dāng)你使用基于一個非恒量表達(dá)式的兩表連接時，根據(jù)這個值來決定使用哪個索引。想要查看結(jié)果，可以在分析完數(shù)據(jù)表后運(yùn)行 SHOW INDEX FROM tbl_name 查看 Cardinality 字段的值。myisamchk --description --verbose 顯示了索引的分布信息。

   

   

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

7.2.4 MySQL如何優(yōu)化 WHERE 子句

這個章節(jié)講述了優(yōu)化程序如何處理 WHERE 子句。例子中使用了 SELECT 語句，但是在 DELETE 和 UPDATE 語句中對 WHERE 子句的優(yōu)化是一樣的。

注意，關(guān)于MySQL優(yōu)化的工作還在繼續(xù)，因此本章節(jié)還沒結(jié)束。MySQL做了很多優(yōu)化工作，而不僅僅是文檔中提到的這些。

MySQL的一些優(yōu)化做法如下：

 

 

• 去除不必要的括號：

   

  ((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

   

• 常量表達(dá)示在索引中只計(jì)算一次

   

   

• 在單獨(dú)一個表上做 COUNT(*) 而不使用 WHERE 時， 對于 MyISAM 和 HEAP 表就會直接從表信息中檢索結(jié)果。在單獨(dú)一個表上做任何表 NOT NULL 達(dá)式查詢時也是這樣做。

   

   

• 預(yù)先探測無效的常量表達(dá)式。MySQL會快速探測一些不可能的 SELECT 語句并且不返回任何記錄。

   

   

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

   

   

• 為表連接中的每個表構(gòu)造一個簡潔的 WHERE 語句，以得到更快的 WHERE 計(jì)算值并且盡快跳過記錄。

   

   

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

     

     

  • 與在一個 UNIQUE 索引、或一個 PRIMARY KEY 的 WHERE 子句一起使用的表，這里所有的索引部分和常數(shù)表達(dá)式做比較并且索引部分被定義為 NOT NULL。
  
  以下的幾個表都會被當(dāng)成常量表：

   

  SELECT * FROM t WHERE primary_key=1;    SELECT * FROM t1,t2    WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

   

• MySQL會進(jìn)各種可能找到表連接最好的連接方法。 如果在 ORDER BY 和 GROUP BY 子句中的所有字段都來自同一個表的話，那么在連接時這個表就會優(yōu)先處理。

   

   

• 如果有 ORDER BY 子句和一個不同的 GROUP BY 子句，或者如果 ORDER BY 或 GROUP BY 中的字段都來自其他的表而非連接順序中的第一個表的話，就會創(chuàng)建一個臨時表了。

   

   

• 如果使用 SQL_SMALL_RESULT，MySQL就會使用內(nèi)存臨時表了。

   

   

• 所有的表索引都會查詢，最好的情況就是所有的索引都會被用到，除非優(yōu)化程序認(rèn)為全表掃描的效率更高。同時，數(shù)據(jù)表掃描是基于判斷最好的索引范圍超過數(shù)據(jù)表的30%。 現(xiàn)在，優(yōu)化程序復(fù)雜多了，它基于對一些附加因素的估計(jì)，例如表大小，記錄總數(shù)，I/O塊大小，因此就不能根據(jù)一個固定的百分比來決定是選擇使用索引還是直接掃描數(shù)據(jù)表。

   

   

• 在某些情況下，MySQL可以直接從索引中取得記錄而無需查詢數(shù)據(jù)文件。如果所有在索引中使用的字段都是數(shù)字類型的話，只需要用索引樹就能完成查詢。

   

   

• 每條記錄輸出之前，那些沒有匹配 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;

以下幾個查詢都是使用索引樹，假使那些索引字段都是數(shù)字型：

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;

以下幾個查詢使用索引來取得經(jīng)過順序排序后的記錄而無需經(jīng)過獨(dú)立的排序步驟：

SELECT ... FROM tbl_nameORDER BY key_part1,key_part2,... ;SELECT ... FROM tbl_nameORDER BY key_part1 DESC, key_part2 DESC, ... ;

7.2.5 MySQL 如何優(yōu)化 OR 子句

Index Merge 方法用于使用 ref, ref_or_null, 或 range 掃描取得的記錄合并起來放到一起作為結(jié)果。這種方法在表?xiàng)l件是或條件 ref, ref_or_null, 或 range ，并且這些條件可以用不同的鍵時采用。
"join"類型的優(yōu)化是從 MySQL 5.0.0 開始才有的，代表者在索引的性能上有著標(biāo)志性的改進(jìn)，因?yàn)槭褂美弦?guī)則的話，數(shù)據(jù)庫最多只能對每個引用表使用一個索引。
在 EXPLAIN 的結(jié)果中，這種方法在 type 字段中表現(xiàn)為 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 如何優(yōu)化 IS NULL

MySQL在 col_name IS NULL 時做和 col_name = constant_value 一樣的優(yōu)化。例如，MySQL使用索引或者范圍來根據(jù) 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，那么這個表達(dá)式就會被優(yōu)化。當(dāng)字段可能無論如何都會產(chǎn)生 NULL 值時，就不會再做優(yōu)化了；例如，當(dāng)它來自一個 LEFT JOIN 中右邊的一個表時。

MySQL 4.1.1或更高會對連接 col_name = expr AND col_name IS NULL 做額外的優(yōu)化， 常見的就是子查詢。EXPLAIN 當(dāng)優(yōu)化起作用時會顯示 ref_or_null。

優(yōu)化程序會為任何索引部分處理 IS NULL。

以下幾個例子中都做優(yōu)化了，假使字段 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 首先讀取引用鍵，然后獨(dú)立掃描鍵值為 NULL 的記錄。

請注意，優(yōu)化程序只會處理一個 IS NULL 級別。下面的查詢中，MySQL只會使用鍵來查詢表達(dá)式 (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 如何優(yōu)化 DISTINCT

在很多情況下，DISTINCT 和 ORDER BY 一起使用時就會創(chuàng)建一個臨時表。

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

當(dāng) 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 如何優(yōu)化 LEFT JOIN 和 RIGHT JOIN

A LEFT JOIN B join_condition 在MySQL中實(shí)現(xiàn)如下：

 

 

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

   

   

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

   

   

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

   

   

• 所有標(biāo)準(zhǔn)的連接優(yōu)化都會執(zhí)行，例外的情況是有一個表總是在它依賴的所有表之后被讀取。如果這是一個循環(huán)的依賴關(guān)系，那么MySQL會認(rèn)為這是錯誤的。

   

   

• 所有的標(biāo)準(zhǔn) WHERE 優(yōu)化都會執(zhí)行。

   

   

• 如果 A 中有一條記錄匹配了 WHERE 子句，但是 B 中沒有任何記錄匹配 ON 條件，那么就會產(chǎn)生一條 B 記錄，它的字段值全都被置為 NULL。

   

   

• 如果使用 LEFT JOIN 來搜索在一些表中不存在的記錄，并且 WHERE 部分中有檢測條件：col_name IS NULL，col_name 字段定義成 NOT NULL 的話，MySQL就會在找到一條匹配 LEFT JOIN 條件的記錄（用于和特定的索引鍵做聯(lián)合）后停止搜索了。

RIGHT JOIN 的實(shí)現(xiàn)和 LEFT JOIN 類似，不過表的角色倒過來了。
連接優(yōu)化程序計(jì)算了表連接的次序。表讀取的順序是由 LEFT JOIN 強(qiáng)行指定的，而且使用 STRAIGHT_JOIN 能幫助連接優(yōu)化程序更快地執(zhí)行，因?yàn)檫@就會有更少的表排隊(duì)檢查了。注意，這里是指如果你執(zhí)行下面這種類型的查詢后，MySQL就會對 b 做一次全表掃描，因?yàn)?LEFT JOIN 強(qiáng)制要求了必須在讀 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 優(yōu)化：如果對產(chǎn)生的 NULL 記錄 WHERE 條件總是 假，那么 LEFT JOIN 就會變成一個普通的連接。
例如，下面的查詢中如果 t2.column1 的值是 NULL 的話，WHERE 子句的結(jié)果就是假了：

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

因此，這就可以安全的轉(zhuǎn)換成一個普通的連接查詢：

SELECT * FROM t1,t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

這查詢起來就更快了，因?yàn)槿绻苡幸粋€更好的查詢計(jì)劃的話，MySQL就會在 t1 之前就用到 t2 了。想要強(qiáng)行指定表順序的話，可以使用 STRAIGHT_JOIN。