7.2.2 估算查詢性能

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

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

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

對(duì)于寫(xiě)來(lái)說(shuō)，大概需要4次（甚至更多）搜索才能找到新的索引位置，更新記錄時(shí)通常需要2次搜索。

請(qǐng)注意，前面的討論中并沒(méi)有提到應(yīng)用程序的性能會(huì)因?yàn)閘og N的值越大而下降。只要所有的東西都能由操作系統(tǒng)或者SQL服務(wù)器緩存起來(lái)，那么性能只會(huì)因?yàn)閿?shù)據(jù)表越大而稍微下降。當(dāng)數(shù)據(jù)越來(lái)越大之后，就不能全部放到緩存中去了，就會(huì)越來(lái)越慢了，除非應(yīng)用程序是被磁盤(pán)搜索約束的（它跟隨著的log N值增加而增加）。為了避免這種情況，可以在數(shù)據(jù)量增大以后也隨著增大索引緩存容量。對(duì) MyISAM 類(lèi)型表來(lái)說(shuō)，索引緩存容量是由系統(tǒng)變量 key_buffer_size 控制的。詳情請(qǐng)看"7.5.2 Tuning Server Parameters"。

7.2.3 SELECT 查詢的速度

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

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

 

 

• 想要讓MySQL將查詢優(yōu)化的速度更快些，可以在數(shù)據(jù)表已經(jīng)加載完全部數(shù)據(jù)后執(zhí)行行 ANALYZE TABLE 或運(yùn)行 myisamchk --analyze 命令。它更新了每個(gè)索引部分的值，這個(gè)值意味著相同記錄的平均值（對(duì)于唯一索引來(lái)說(shuō)，這個(gè)值則一直都是 1）。MySQL就會(huì)在當(dāng)你使用基于一個(gè)非恒量表達(dá)式的兩表連接時(shí)，根據(jù)這個(gè)值來(lái)決定使用哪個(gè)索引。想要查看結(jié)果，可以在分析完數(shù)據(jù)表后運(yùn)行 SHOW INDEX FROM tbl_name 查看 Cardinality 字段的值。myisamchk --description --verbose 顯示了索引的分布信息。

   

   

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

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

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

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

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

 

 

• 去除不必要的括號(hào)：

   

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

   

• 展開(kāi)常量：

   

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

   

• 去除常量條件（在展開(kāi)常量時(shí)需要）：

   

  (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ú)一個(gè)表上做 COUNT(*) 而不使用 WHERE 時(shí)， 對(duì)于 MyISAM 和 HEAP 表就會(huì)直接從表信息中檢索結(jié)果。在單獨(dú)一個(gè)表上做任何表 NOT NULL 達(dá)式查詢時(shí)也是這樣做。

   

   

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

   

   

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

   

   

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

   

   

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

     

     

  • 與在一個(gè) UNIQUE 索引、或一個(gè) PRIMARY KEY 的 WHERE 子句一起使用的表，這里所有的索引部分和常數(shù)表達(dá)式做比較并且索引部分被定義為 NOT NULL。
  
  以下的幾個(gè)表都會(huì)被當(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會(huì)進(jìn)各種可能找到表連接最好的連接方法。 如果在 ORDER BY 和 GROUP BY 子句中的所有字段都來(lái)自同一個(gè)表的話，那么在連接時(shí)這個(gè)表就會(huì)優(yōu)先處理。

   

   

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

   

   

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

   

   

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

   

   

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

   

   

• 每條記錄輸出之前，那些沒(méi)有匹配 HAVING 子句的就會(huì)被跳過(guò)。

以下幾個(gè)查詢速度非?？欤?/p>

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;

以下幾個(gè)查詢都是使用索引樹(shù)，假使那些索引字段都是數(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;

以下幾個(gè)查詢使用索引來(lái)取得經(jīng)過(guò)順序排序后的記錄而無(wú)需經(jīng)過(guò)獨(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 掃描取得的記錄合并起來(lái)放到一起作為結(jié)果。這種方法在表?xiàng)l件是或條件 ref, ref_or_null, 或 range ，并且這些條件可以用不同的鍵時(shí)采用。
"join"類(lèi)型的優(yōu)化是從 MySQL 5.0.0 開(kāi)始才有的，代表者在索引的性能上有著標(biāo)志性的改進(jìn)，因?yàn)槭褂美弦?guī)則的話，數(shù)據(jù)庫(kù)最多只能對(duì)每個(gè)引用表使用一個(gè)索引。
在 EXPLAIN 的結(jié)果中，這種方法在 type 字段中表現(xiàn)為 index_merge。這種情況下，key 字段包含了所有使用的索引列表，并且 key_len 字段包含了使用的索引的最長(zhǎng)索引部分列表。
例如：

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 時(shí)做和 col_name = constant_value 一樣的優(yōu)化。例如，MySQL使用索引或者范圍來(lái)根據(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;

如果一個(gè) WHERE 子句包括了一個(gè) col_name IS NULL 條件，并且這個(gè)字段聲明為 NOT NULL，那么這個(gè)表達(dá)式就會(huì)被優(yōu)化。當(dāng)字段可能無(wú)論如何都會(huì)產(chǎn)生 NULL 值時(shí)，就不會(huì)再做優(yōu)化了；例如，當(dāng)它來(lái)自一個(gè) LEFT JOIN 中右邊的一個(gè)表時(shí)。

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

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

以下幾個(gè)例子中都做優(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 的記錄。

請(qǐng)注意，優(yōu)化程序只會(huì)處理一個(gè) IS NULL 級(jí)別。下面的查詢中，MySQL只會(huì)使用鍵來(lái)查詢表達(dá)式 (t1.a=t2.a AND t2.a IS NULL) 而無(wú)法使在 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 一起使用時(shí)就會(huì)創(chuàng)建一個(gè)臨時(shí)表。

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

當(dāng) LIMIT row_count 和 DISTINCT 一起使用時(shí)，MySQL在找到 row_count 不同記錄后就會(huì)立刻停止搜索了。

如果沒(méi)有用到來(lái)自查詢中任何表的字段時(shí)，MySQL在找到第一個(gè)匹配記錄后就會(huì)停止搜索這些沒(méi)沒(méi)用到的表了。在下面的情況中，假使 t1 在 t2 前就使用了（可以通過(guò) EXPLAIN 分析知道），MySQL就會(huì)在從 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 中讀取記錄了（換句話說(shuō)，WHERE 子句中的任何條件都對(duì)此不起作用）。

   

   

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

   

   

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

   

   

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

   

   

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

RIGHT JOIN 的實(shí)現(xiàn)和 LEFT JOIN 類(lèi)似，不過(guò)表的角色倒過(guò)來(lái)了。
連接優(yōu)化程序計(jì)算了表連接的次序。表讀取的順序是由 LEFT JOIN 強(qiáng)行指定的，而且使用 STRAIGHT_JOIN 能幫助連接優(yōu)化程序更快地執(zhí)行，因?yàn)檫@就會(huì)有更少的表排隊(duì)檢查了。注意，這里是指如果你執(zhí)行下面這種類(lèi)型的查詢后，MySQL就會(huì)對(duì) 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;

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

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開(kāi)始，MySQL做如下 LEFT JOIN 優(yōu)化：如果對(duì)產(chǎn)生的 NULL 記錄 WHERE 條件總是 假，那么 LEFT JOIN 就會(huì)變成一個(gè)普通的連接。
例如，下面的查詢中如果 t2.column1 的值是 NULL 的話，WHERE 子句的結(jié)果就是假了：

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

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

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

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