4.2 MySQL查詢優化程序
在發布一個選擇行的查詢時， MySQL進行分析，看是否能夠對它進行優化，使它執行更快。本節中，我們將研究查詢優化程序怎樣工作。更詳細的信息，可參閱MySQL參考指南中的“Getting Maximum Performance from MySQL”，該章描述了MySQL采用的各種優化措
施。該章中的信息會不斷變化，因為MySQL的開發者不斷對優化程序進行改進，因此，有必要經常拜訪一下該章，看看是否有可供利用的新技巧。（http://www.mysql.com/ 處的MySQL聯機參考指南在不斷地更新。）
MySQL查詢優化程序利用了索引。當然，它也利用了其他信息。例如，如果發布下列查詢，MySQL將非常快地執行它，不管相應的表有多大：
SELECT * FROM tb1_name WHERE 1= 0
在此情形中，MySQL考察WHERE 子句，如果認識到不可能有滿足該查詢的行，就不會對該表進行搜索。可利用EXPLAIN 語句知道這一點，EXPLAIN 語句要求MySQL顯示某些有關它應該執行一條SELECT 查詢，而實際沒有執行的信息。為了使用E X P L A I N，只需要SELECT 語句前放置EXPLAIN 即可，如下所示：
EXPLAIN SELECT * FROM tb1_name WHERE 1= 0
通常，EXPLAIN 返回的信息比這個多，包括將用來掃描表的索引、將要使用的連接類型以及需要在每個表中掃描的行數估計等等。
4.2.1優化程序怎樣工作
MySQL查詢優化程序有幾個目標，但其主要目標是盡量利用索引，而且盡量使用最具有限制性的索引以排除盡可能多的行。這樣做可能會適得其反，因為發布一條SELECT 語句的目的是尋找行，而不是拒絕它們。優化程序這樣工作的原因是從要考慮的行中排除行越快，那麼找到確實符合給出標准的行就越快。如果能夠首先進行最具限制性的測試，則查詢可以進行得更快。假如有一個測試兩列的查詢，每列上都有一個索引：
WHERE coll = "some value" AND col2 = "some other value"
還假定，與col1上的測試相符的有900 行，與col2 上的測試相符的有300 行，而兩個測試都通過的有30 行。如果首先測試c o l 1，必須檢查900 行以找到也與col2 值相符的30 行。那麼測試中有870 將失敗。如果首先測試c o l 2，要找到也與col1值相符的30 行，只需檢查300 行。測試中有失敗270 次，這樣所涉及的計算較少，磁盤I/O 也較少。遵循下列准則，有助於優化程序利用索引：
■ 比較具有相同類型的列。在比較中利用索引列時，應該使用那些類型相同的列。例如，CHAR(10) 被視為與CHAR(10) 或VARCHAR(10) 相同，但不同於CHAR(12) 和VARCHAR( 12 )。INT 與BIGINT 不同。在MySQL3.23 版以前，要求使用相同類型的
列，否則列上的索引將不起作用。自3.23 版後，不嚴格要求這樣做，但相同的列類型比不同類型提供更好的性能。如果所比較的兩列類型不同，可使用ALTER TABLE語句修改其中之一使它們的類型相配。
■ 比較中應盡量使索引列獨立。如果在函數調用或算術表達式中使用一個列，則MySQL不能使用這樣的索引，因為它必須對每行計算表達式的值。有時，這是不可避免的，但很多時候，可以重新編寫只取索引列本身的查詢。下面的WHERE 子句說明了怎樣進行這項工作。第一行中，優化程序將簡化表達式4/2 為值2，然後使用my_col 上的索引快速地找到小於2 的值。而在第二個表達式中，MySQL必須檢索出每行的my_col 值，乘以2，然後將結果與4 比較。沒索引可用，因為列中的每個值都要檢索，以便能對左邊的表達式求值：
WHERE my_col < 4/2
WHERE my_col * 2 < 4
讓我們考慮另一個例子。假如有一個索引列date _ c o l。如果發布如下的查詢，相應的索引未被使用：
SELECT * FROM my_tb1WHERE YEAR(date_col) < 1990
其中表達式並不將索引列與1990 比較，而是將從列值計算出的值用於比較，而且必須計算每行的這個值。結果是， date_col 上的索引不可能得到使用。怎樣解決？使用一個文字日期即可，這時將會使用date_col 上的索引：
WHERE date_col < "1990-01-01"
但是假如沒有特定的日期值，那麼可能會對找到具有出現在距今一定天數內的日期的記錄感興趣。有幾種方法來編寫這樣的查詢，但並非所有方法都很好。三種可能的方法如下:
其中第一行不能利用索引， 因為必須為每行檢索列， 以便能夠計算TO _ DAYS(date_col) 的值。第二行要好一些。c ut o ff 和TO _ DAY S ( CURRENT _ DATE) 兩者都是常量，因此比較表達式的右邊可在查詢處理前由優化程序一次計算出來，而不是每行計算一次。但date_col 列仍然出現在一個函數調用中，因此，沒有使用索引。第三行是最好的方法。比較表達式的右邊可在執行查詢前作為常量一次計算出來，但現在其值是一個日期。這個值可直接與date_col 的值進行比較，不再需要轉換為天數，可以利用索引。
■ 在LIKE 模式的起始處不要使用通配符。有時，有的人會用下列形式的WHERE 子句來搜索串：
WHERE col_name LIKE "%string%"
如果希望找到s t r i n g，不管它出現在列中任何位置，那麼這樣做是對的。但不要出於習慣在串的兩邊加“ %”。如果實際要查找的只是出現在列的開始處的串，則不應該要第一個“%”號。例如，如果在一個包含姓的列中查找“ M a c”起始的姓，應該編寫如下的WHERE 子句：
WHERE last_name LIKE "Mac%"
優化程序考慮模式中的開始的文字部分，然後利用索引找到相符合的行。不過寧可寫成如下的表達式，它允許使用last_name 上的索引：
WHERE last_name >= "Mac" AND last_name < "Mad"
這種優化對使用REGEXP 操作符的模式匹配不起作用。
■ 幫助優化程序更好地評估索引的有效性。缺省時，如果將索引列中的值與常量進行比較，優化程序將假定鍵字是均勻地分布在索引中的。優化程序還將對索引進行一個快速的檢查，以估計在確定相應的索引是否應該用於常量的比較時要使用多少條目。可利用myisamchk 或isamchk 的--analyze 選項給優化程序提供更好的信息，以便分析鍵值的分布。myisamchk 用於MyISAM 表，isamchk 用於ISAM 表。為了完成鍵值分析，必須能夠登錄到MySQL服務器主機中，而且必須對表文件具有寫訪問權限。
■ 利用EXPLAIN 檢驗優化程序操作。檢查用於查詢中的索引是否能很快地排除行。如果不能，那麼應該試一下利用STRAIGHT_JOIN 強制按特定次序使用表來完成一個連接。查詢的執行方式不那麼顯然；MySQL可能會有很多理由不以您認為最好的次序使用索引。
■ 測試查詢的其他形式，而且不止一次地運行它們。在測試一個查詢的其他形式時，應該每種方法運行幾次。如果對兩個不同方法中的每種只運行查詢一次，通常會發現第二個查詢更快，因為來自第一個查詢的信息在磁盤高速緩存中，不需要實際從磁盤上讀出。還應該盡量在系統負載相對平穩的時候運行查詢，以避免受系統中其他活動的影響。
4.2.2 忽略優化
這可能聽起來有點奇怪，但在以下情況中，要廢除MySQL的優化功能：
■ 強迫MySQL慢慢地刪除表的內容。在需要完全刪空一個表時，利用無WHERE 子句的DELETE 語句刪除整個表的內容是最快的，如下所示：
DELETE FROM tb1_name
MySQL對這種特殊情況的DELETE 進行優化；它利用表信息文件中的表說明從頭開始創建空數據文件和索引文件。這種優化使DELETE 操作極快，因為MySQL無需單獨地刪除每一行。但在某些情況下，這樣做會產生一些不必要的負作用：
■ MySQL報告所涉及的行數為零，即使表不為空也是如此。很多時候這沒有關系（雖然，如果事先沒有思想准備，會感到困惑不解），但對於那些確實需要知道真實行數的應用程序來說，這是不恰當的。
■ 如果表含有一個AUTO_INCREMENT 列，則該列的順序編號會以1從頭開始。這是真實的事情，即使在MySQL3.23 中對AUTO_INCREMENT 的處理進行了改進後也是這樣。關於這個改進的介紹請參閱第2章中的“使用序列”小節。可增加WHERE 1> 0 子句對DELETE 語句“不優化”。
DELETE FROM tb1_name WHERE 1> 0
這迫使MySQL進行逐行的刪除。相應的查詢執行要慢得多，但將返回真正刪除的行數。它還將保持當前的AUTO_INCREMENT 序列的編號，不過只對MyISAM 表（MySQL3.23 以上的版本可用）有效。而對於ISAM 表，序列仍將重置。
■ 避免更新循環不終止。如果更新一個索引列，如果該列用於WHERE 子句且更新將索引值移入至今尚未出超的取值范圍內時，有可能對所更新的行進行不終止的更新。假如表my_tbl 有一個索引了的整數列key _ c o l。下列的查詢會產生問題：
這個問題的解決方法是在WHERE 子句中將key_col 用於一個表達式，使MySQL不能使用索引：
實際上，還有另外的方法，即升級到MySQL3.23.2 或更高的版本，它們已經解決了這樣的問題。
■ 以隨機次序檢索結果。自MySQL3.23.3 以來，可使用ORDER BY RAND( ) 隨機地對結果進行排序。另一技術對MySQL更舊的版本很有用處，那就是選擇一個隨機數列，然後在該列上進行排序。但是，如果按如下編寫查詢，優化程序將會讓您的願望落空：
這裡的問題是MySQL認為該列是一個函數調用，將認為相應的列值是一個常數，而對ORDER BY 子句進行優化，使此查詢失效。可在表達式中引用某個表列來蒙騙優化程序。例如，如果表中有一個名為age 的列，可編寫如下查詢：
■ 忽略優化程序的表連接次序。可利用STRIGHT_JOIN 強迫優化程序以特定的次序使用表。如果這樣做，應該規定表的次序，使第一個表為從中選擇的行數最少的表。（如果不能肯定哪個表滿足這個要求，可將行數最多的表作為第一個表。）換句話說，應盡量規定表的次序，使最有限制性的選擇先出現。排除可能的候選行越早，查詢執行得就越快。要保證測試相應的查詢兩次；可能會有某些原因使優化程序不以您所想像的方式對表進行連接，並且STRAIGHT_JOIN 也可能實際上不起作用。
