SELECT * FROM tbl_name WHERE 0; MySQL> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE SELECT col3 FROM mytable
WHERE col1 = ’some value’ AND col2 = ’some other value’; WHERE mycol < 4 / 2
WHERE mycol * 2 < 4 SELECT * FROM mytbl WHERE YEAR(date_col) < 1990; WHERE date_col < ’1990-01-01’ WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff
WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE())
WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY) WHERE col_name LIKE ’%string%’ WHERE last_name LIKE ’Mac%’ WHERE last_name >= ’Mac’ AND last_name < ’Mad’
這種優化不能應用於使用了REGEXP操作符的模式匹配。REGEXP表達式永遠不會被優化。
幫助優化器更好的判斷索引的效率。在默認情況下，當你把索引列的值與常量進行比較的時候，優化器會假設鍵值在索引內部是均勻分布的。在決定進行常量比較是否使用索引的時候，優化器會快速地檢查索引，估計出會用到多少個實體（entry）。對應MyISAM、InnoDB和BDB數據表來說，你可以使用ANALYZE TABLE讓服務器執行對鍵值的分析。它會為優化器提供更好的信息。
使用EXPLAIN驗證優化器的操作。EXPLAIN語句可以告訴你是否使用了索引。當你試圖用另外的方式編寫語句或檢查添加索引是否會提高查詢執行效率的時候，這些信息對你是有幫助的。
在必要的時候給優化器一些提示。正常情況下，MySQL優化器自由地決定掃描數據表的次序來最快地檢索數據行。在有些場合中優化器沒有作出最佳選擇。如果你察覺這種現象發生了，就可以使用STRAIGHT_JOIN關鍵字來重載優化器的選擇。帶有STRAIGHT_JOIN的聯結類似於交叉聯結，但是強迫數據表按照FROM子句中指定的次序來聯結。
在SELECT語句中有兩個地方可以指定STRAIGHT_JOIN。你可以在SELECT關鍵字和選擇列表之間的位置指定，這樣會對語句中所有的交叉聯結產生影響；你也可以在FROM子句中指定。下面的兩個語句功能相同：SELECT STRAIGHT_JOIN ... FROM t1, t2, t3 ... ;
SELECT ... FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3 ... ;
分別在帶有STRAIGHT_JOIN和不帶STRAIGHT_JOIN的情況下運行這個查詢；MySQL可能因為什麼原因沒有按照你認為最好的次序使用索引（你可以使用EXPLAIN來檢查MySQL處理每個語句的執行計劃）。
你還可以使用FORCE INDEX、USE INDEX或IGNORE INDEX來指導服務器如何使用索引。
利用優化器更加完善的區域。MySQL可以執行聯結和子查詢，但是子查詢是最近才支持的，是在MySQL 4.1中添加的。因而在很多情況下，優化器對聯結操作的調整比對子查詢的調整要好一些。當你的子查詢執行地很慢的時候，這就是一條實際的提示。有一些子查詢可以使用邏輯上相等的聯結來重新表達。在可行的情況下，你可以把子查詢重新改寫為聯結，看是否執行地快一些。
測試查詢的備用形式，多次運行。當你測試查詢的備用形式的時候（例如，子查詢與等同的聯結操作對比），每種方式都應該多次運行。如果兩種形式都只運行了一次，那麼你通常會發現第二個查詢比第一個快，這是因為第一個查詢得到的信息仍然保留在緩存中，以至於第二個查詢沒有真正地從磁盤上讀取數據。你還應該在系統負載相對平穩的時候運行查詢，以避免系統中其它的事務影響結果。
避免過度地使用MySQL自動類型轉換。MySQL會執行自動的類型轉換，但是如果你能夠避免這種轉換操作，你得到的性能就更好了。例如，如果num_col是整型數據列，那麼下面這些查詢將返回相同的結果：SELECT * FROM mytbl WHERE num_col = 4;
SELECT * FROM mytbl WHERE num_col = ’4’;
但是第二個查詢涉及到了類型轉換。轉換操作本身為了把整型和字符串型轉換為雙精度型進行比較，使性能惡化了。更嚴重的情況是，如果num_col是索引的，那麼涉及到類型轉換的比較操作不會使用索引。
相反類型的比較操作（把字符串列與數值比較）也會阻止索引的使用。假設你編寫了如下所示的查詢：SELECT * FROM mytbl WHERE str_col = 4;
在這個例子中，不會使用str_col上的索引，因為在把str_col中的字符串值轉換成數值的時候，可能有很多值等於4（例如’4’、’4.0’和’4th’）。分辨哪些值符合要求的唯一辦法是讀取每個數據行並執行比較操作。
使用EXPLAIN來檢查優化器的操作
EXPLAIN對於了解優化器生成的、用於處理語句的執行計劃的內部信息是很有幫助的。在這一部分中，我們將解釋EXPLAIN的兩種用途：
· 查看采用不同的方式編寫的查詢是否影響了索引的使用。
· 查看向數據表添加索引對優化器生成高效率執行計劃的能力的影響。
這一部分只討論與示例相關的EXPLAIN輸入字段。
前面，在"優化器是如何工作的"部分中我們得出的觀點是，你編寫表達式的方式將決定優化器是否能使用可用的索引。特別是上面的討論使用了下面三個邏輯相等的WHERE子句的例子，只有第三個允許使用索引：WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff
WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE())
WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)
EXPLAIN允許你查看編寫表達式的某種方式是否比另外的方式好一些。為了看到結果，讓我們分別用這三個WHERE子句搜索成員表中過期的數據列值，把cutoff值設為30天。為了看到索引的使用和表達式編寫方式之間的關系，我們首先對expiration列進行索引：MySQL> ALTER TABLE member ADD INDEX (expiration);
接著在每個表達式形式上使用EXPLAIN，看優化器生成了什麼樣的執行計劃：MySQL> EXPLAIN SELECT * FROM MEMBER
-> WHERE TO_DAYS(expiration) - TO_DAYS(CURDATE()) < 30\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MEMBER
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 102
Extra: Using where
MySQL> EXPLAIN SELECT * FROM MEMBER
-> WHERE TO_DAYS(expiration) < 30 + TO_DAYS(CURDATE())\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MEMBER
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 102
Extra: Using where
MySQL> EXPLAIN SELECT * FROM MEMBER
-> WHERE expiration < DATE_ADD(CURDATE(), INTERVAL 30 DAY)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MEMBER
type: range
possible_keys: expiration
key: expiration
key_len: 4
ref: NULL
rows: 6
Extra: Using where
上面的結果顯示，前面兩個語句沒有使用索引。類型（type）值表明了將如何從數據表中讀取信息。ALL意味著"將檢查所有的記錄"。也就是說，它會執行全表掃描，沒有利用索引。每個與鍵相關的列都是NULL也表明沒有使用索引。
與此形成對比的是，第三個語句的結果顯示，采用這種方式編寫的WHERE子句，優化器可以使用expiration列上的索引：
· 類型（type）值表明它可以使用索引來搜索特定范圍的值（小於右邊表達式給定的值）。
· 可能鍵（possible_keys）和鍵（key）值顯示expiration上的索引已經被考慮作為備選索引，並且它也是真正使用的索引。
· 行數（rows）值顯示優化器估計自己需要檢查6個數據行來處理該查詢。這比前面兩個執行計劃的102小很多。
EXPLAIN的第二種用途是查看添加索引是否能幫助優化器更高效率地執行語句。我將使用兩個未被索引的數據表。它足夠顯示建立索引的效率。相同的規則可以應用於涉及多表的更加復雜的聯結操作。
假設我們有兩個數據表t1和t2，每個有1000行，包含的值從1到1000。下面的查詢查找出兩個表中值相同的數據行：MySQL> SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2;
+------+------+
| i1 | i2 |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
...
兩個表都沒有索引的時候，EXPLAIN產生下面的結果：MySQL> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
類型列中的ALL表明要進行檢查所有數據行的全表掃描。可能鍵列中的NULL表明沒有找到用於提高查詢速度的備選索引（鍵、鍵長度和參考列都是NULL也是因為缺少合適的索引）。Using where表明使用WHERE子句中的信息來識別合格的數據行。
這段信息告訴我們，優化器沒有為提高執行查詢的效率找到任何有用的信息：
· 它將對t1表進行全表掃描。
· 對於t1中的每一行，它將執行t2的全表掃描，使用WHERE子句中的信息識別出合格的行。
行數值顯示了優化器估計的每個階段查詢需要檢查的行數。T1的估計值是1000，因為1000可以完成全表掃描。相似地，t2的估計值也是1000，但是這個值是對於t1的每一行的。換句話說，優化器所估計的處理該查詢所需要檢查的數據行組合的數量是1000×1000，也就是一百萬。這會造成很大的浪費，因為實際上只有1000個組合符合WHERE子句的條件。
為了使這個查詢的效率更高，給其中一個聯結列添加索引並重新執行EXPLAIN語句：MySQL> ALTER TABLE t2 ADD INDEX (i2);
MySQL> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: i2
key: i2
key_len: 5
ref: sampdb.t1.i1
rows: 10
Extra: Using where; Using index
我們可以看到性能提高了。T1的輸出沒有改變（表明還是需要進行全表掃描），但是優化器處理t2的方式就有所不同了：
· 類型從ALL改變為ref，意味著可以使用參考值（來自t1的值）來執行索引查找，定位t2中合格的數據行。
· 參考值在參考（ref）字段中給出了：sampdb.t1.i1。
· 行數值從1000降低到了10，顯示出優化器相信對於t1中的每一行，它只需要檢查t2中的10行（這是一個悲觀的估計值。實際上，在t2中只有一行與t1中數據行匹配。我們在後面會看到如何幫助優化器改善這個估計值）。數據行組合的全部估計值使1000×10＝10000。它比前面的沒有索引的時候估計出來的一百萬好多了。
對t1進行索引有價值嗎？實際上，對於這個特定的聯結操作，掃描一張表是必要的，因此沒有必要對t1建立索引。如果你想看到效果，可以索引t1.i1並再次運行EXPLAIN：MySQL> ALTER TABLE t1 ADD INDEX (i1);
MySQL> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: i1
key: i1
key_len: 5
ref: NULL
rows: 1000
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: i2
key: i2
key_len: 5
ref: sampdb.t1.i1
rows: 10
Extra: Using where; Using index
上面的輸出與前面的EXPLAIN的輸出相似，但是添加索引對t1的輸出有一些改變。類型從NULL改成了index，附加（Extra）從空的改成了Using index。這些改變表明，盡管對索引的值仍然需要執行全表掃描，但是優化器還是可以直接從索引文件中讀取值，根據不需要使用數據文件。你可以從MyISAM表中看到這類結果，在這種情況下，優化器知道自己只詢問索引文件就能夠得到所有需要的信息。對於InnoDB 和BDB表也有這樣的結果，在這種情況下優化器可以單獨使用索引中的信息而不用搜索數據行。
我們可以運行ANALYZE TABLE使優化器進一步優化估計值。這會引起服務器生成鍵值的靜態分布。分析上面的表並再次運行EXPLAIN得到了更好的估計值：MySQL> ANALYZE TABLE t1, t2;
MySQL> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: i1
key: i1
key_len: 5
ref: NULL
rows: 1000
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: i2
key: i2
key_len: 5
ref: sampdb.t1.i1
rows: 1
Extra: Using where; Using index
在這種情況下，優化器估計在t2中與t1的每個值匹配的數據行只有一個。
重載優化過程
這個過程聽起來多余，但是有時候你還是希望去掉某些MySQL優化行為的：
重載優化器的表聯結次序。使用STRAIGHT_JOIN強迫優化器按照特定的次序使用數據表。在這樣操作的時候，你必須對數據表進行排序，這樣才能保證第一張表是被選擇的行數最少的表。如果你不能確定被選擇行數最少的是哪一張表，那麼就把行數最多的放到第一的位置。換句話說，試著對表進行排序，使最有約束力的選擇出現在最前面。你對可能的備選數據行縮小地越早，執行查詢的性能就越好。請確保在帶有STRAIGHT_JOIN和不帶STRAIGHT_JOIN的時候分別執行該查詢。有時候由於某些原因的存在，優化器沒有按照你認定的方式聯結數據表，STRAIGHT_JOIN也可能沒有實際的幫助作用。
另一個可能性是在聯結的數據表列表中的某個表的後面使用FORCE INDEX、USE INDEX和IGNORE INDEX調節符來告訴MySQL如何使用索引。這在優化器沒有做出正確選擇的時候是有用處的。
以最小的代價清空一張表。當需要完全地清空一張MyISAM數據表的時候，最快的方法是刪除它並利用它的.frm文件中存儲的腳本來重新建立它。使用TRUNCATE TABLE語句實現：TRUNCATE TABLE tbl_name;
通過重新建立MyISAM數據表來清空它的這種服務器優化措施使該操作非常快，因為不需要單獨地逐行刪除。
但是TRUNCATE TABLE也帶來了一些副作用，在某些環境中是不符合要求的：
· TRUNCATE TABLE不一定能夠計算出被刪除的數據列的精確數量。如果你需要這個數值，請使用不帶WHERE子句的DELETE語句：DELETE FROM tbl_name;
· 但是，通過重新建立來清空數據表，它可能會把序號的起始值設置為1。為了避免這種情況，請使用"不優化的"全表DELETE語句，它帶有一個恆為真的WHERE子句：DELETE FROM tbl_name WHERE 1;
添加WHERE子句會強迫MySQL進行逐行刪除，因為它必須計算出每一行的值來判斷是否能夠刪除它。這個語句執行的速度很慢，但是它卻保留了當前的AUTO_INCREMENT序號。
