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>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_name
WHERE key_part1=constant;
SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... LIMIT 10;
SELECT ... FROM tbl_name
ORDER 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_name
WHERE key_part1=val1 AND key_part2=val2;
SELECT key_part2 FROM tbl_name GROUP BY key_part1;
以下幾個查詢使用索引來取得經過順序排序後的記錄而無需經過獨立的排序步驟：
SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... ;
SELECT ... FROM tbl_name
ORDER 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_name
WHERE (key_part1 = 10 OR key_part2 = 20) AND non_key_part=30;
SELECT * FROM t1,t2
WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
AND t2.key1=t1.some_col;
SELECT * FROM t1,t2
WHERE t1.key1=1
AND (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_name
WHERE 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,t2
WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;
SELECT * FROM t1,t2
WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);
SELECT * FROM t1,t2
WHERE (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,t2
WHERE (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。
