4.2 MySQL 查詢優(yōu)化程序
    在發(fā)布一個(gè)選擇行的查詢時(shí)， MySQL 進(jìn)行分析，看是否能夠?qū)λM(jìn)行優(yōu)化，使它執(zhí)行更快。本節(jié)中，我們將研究查詢優(yōu)化程序怎樣工作。更詳細(xì)的信息，可參閱MySQL 參考指南中的“Getting Maximum Performance from MySQL”，該章描述了MySQL 采用的各種優(yōu)化措
施。該章中的信息會(huì)不斷變化，因?yàn)镸ySQL 的開發(fā)者不斷對(duì)優(yōu)化程序進(jìn)行改進(jìn)，因此，有必要經(jīng)常拜訪一下該章，看看是否有可供利用的新技巧。（h t t p : / / w w w.mysql.com/ 處的MySQL 聯(lián)機(jī)參考指南在不斷地更新。）
    MySQL 查詢優(yōu)化程序利用了索引。當(dāng)然，它也利用了其他信息。例如，如果發(fā)布下列查詢，MySQL 將非?？斓貓?zhí)行它，不管相應(yīng)的表有多大：
    SELECT * FROM tb1_name WHERE 1 = 0
    在此情形中，MySQL 考察WHERE 子句，如果認(rèn)識(shí)到不可能有滿足該查詢的行，就不會(huì)對(duì)該表進(jìn)行搜索。可利用EXPLAIN 語(yǔ)句知道這一點(diǎn)，EXPLAIN 語(yǔ)句要求MySQL 顯示某些有關(guān)它應(yīng)該執(zhí)行一條SELECT 查詢，而實(shí)際沒(méi)有執(zhí)行的信息。為了使用E X P L A I N，只需要SELECT 語(yǔ)句前放置EXPLAIN 即可，如下所示：
    EXPLAIN SELECT * FROM tb1_name WHERE 1 = 0

　   通常，EXPLAIN 返回的信息比這個(gè)多，包括將用來(lái)掃描表的索引、將要使用的連接類型以及需要在每個(gè)表中掃描的行數(shù)估計(jì)等等。
　   4.2.1 優(yōu)化程序怎樣工作
　   MySQL 查詢優(yōu)化程序有幾個(gè)目標(biāo)，但其主要目標(biāo)是盡量利用索引，而且盡量使用最具有限制性的索引以排除盡可能多的行。這樣做可能會(huì)適得其反，因?yàn)榘l(fā)布一條SELECT 語(yǔ)句的目的是尋找行，而不是拒絕它們。優(yōu)化程序這樣工作的原因是從要考慮的行中排除行越快，那么找到確實(shí)符合給出標(biāo)準(zhǔn)的行就越快。如果能夠首先進(jìn)行最具限制性的測(cè)試，則查詢可以進(jìn)行得更快。假如有一個(gè)測(cè)試兩列的查詢，每列上都有一個(gè)索引：
    WHERE coll = "some value" AND col2 = "some other value"
    還假定，與col1 上的測(cè)試相符的有900 行，與col2 上的測(cè)試相符的有300 行，而兩個(gè)測(cè)試都通過(guò)的有30 行。如果首先測(cè)試c o l 1，必須檢查900 行以找到也與col2 值相符的30 行。那么測(cè)試中有870 將失敗。如果首先測(cè)試c o l 2，要找到也與col1 值相符的30 行，只需檢查300 行。測(cè)試中有失敗270 次，這樣所涉及的計(jì)算較少，磁盤I/O 也較少。遵循下列準(zhǔn)則，有助于優(yōu)化程序利用索引：
    ■ 比較具有相同類型的列。在比較中利用索引列時(shí)，應(yīng)該使用那些類型相同的列。例如，CHAR(10) 被視為與CHAR(10) 或VARCHAR(10) 相同，但不同于CHAR(12) 和VA R C H A R ( 1 2 )。INT 與BIGINT 不同。在MySQL 3.23 版以前，要求使用相同類型的
列，否則列上的索引將不起作用。自3.23 版后，不嚴(yán)格要求這樣做，但相同的列類型比不同類型提供更好的性能。如果所比較的兩列類型不同，可使用A LTER TA B L E語(yǔ)句修改其中之一使它們的類型相配。
    ■ 比較中應(yīng)盡量使索引列獨(dú)立。如果在函數(shù)調(diào)用或算術(shù)表達(dá)式中使用一個(gè)列，則M y S Q L不能使用這樣的索引，因?yàn)樗仨殞?duì)每行計(jì)算表達(dá)式的值。有時(shí)，這是不可避免的，但很多時(shí)候，可以重新編寫只取索引列本身的查詢。下面的WHERE 子句說(shuō)明了怎樣進(jìn)行這項(xiàng)工作。第一行中，優(yōu)化程序?qū)⒑?jiǎn)化表達(dá)式4/2 為值2，然后使用my_col 上的索引快速地找到小于2 的值。而在第二個(gè)表達(dá)式中，MySQL 必須檢索出每行的my_col 值，乘以2，然后將結(jié)果與4 比較。沒(méi)索引可用，因?yàn)榱兄械拿總€(gè)值都要檢索，以便能對(duì)左邊的表達(dá)式求值：
    WHERE my_col < 4/2
    WHERE my_col * 2 < 4
    讓我們考慮另一個(gè)例子。假如有一個(gè)索引列d a t e _ c o l。如果發(fā)布如下的查詢，相應(yīng)的索引未被使用：
    SELECT * FROM my_tb1 WHERE YEAR(date_col) < 1990
    其中表達(dá)式并不將索引列與1990 比較，而是將從列值計(jì)算出的值用于比較，而且必須計(jì)算每行的這個(gè)值。結(jié)果是， date_col 上的索引不可能得到使用。怎樣解決？使用一個(gè)文字日期即可，這時(shí)將會(huì)使用date_col 上的索引：
    WHERE date_col < "1990-01-01"
    但是假如沒(méi)有特定的日期值，那么可能會(huì)對(duì)找到具有出現(xiàn)在距今一定天數(shù)內(nèi)的日期的記錄感興趣。有幾種方法來(lái)編寫這樣的查詢，但并非所有方法都很好。三種可能的方法如下:

    其中第一行不能利用索引， 因?yàn)楸仨殲槊啃袡z索列， 以便能夠計(jì)算TO _ D AYS(date_col) 的值。第二行要好一些。c u t o ff 和TO _ DAY S ( CURRENT _ DATE) 兩者都是常量，因此比較表達(dá)式的右邊可在查詢處理前由優(yōu)化程序一次計(jì)算出來(lái)，而不是每行計(jì)算一次。但date_col 列仍然出現(xiàn)在一個(gè)函數(shù)調(diào)用中，因此，沒(méi)有使用索引。第三行是最好的方法。比較表達(dá)式的右邊可在執(zhí)行查詢前作為常量一次計(jì)算出來(lái)，但現(xiàn)在其值是一個(gè)日期。這個(gè)值可直接與date_col 的值進(jìn)行比較，不再需要轉(zhuǎn)換為天數(shù)，可以利用索引。
    ■ 在LIKE 模式的起始處不要使用通配符。有時(shí)，有的人會(huì)用下列形式的WHERE 子句來(lái)搜索串：
    WHERE col_name LIKE "%string%"
    如果希望找到s t r i n g，不管它出現(xiàn)在列中任何位置，那么這樣做是對(duì)的。但不要出于習(xí)慣在串的兩邊加“ %”。如果實(shí)際要查找的只是出現(xiàn)在列的開始處的串，則不應(yīng)該要第一個(gè)“%”號(hào)。例如，如果在一個(gè)包含姓的列中查找“ M a c”起始的姓，應(yīng)該編寫如下的WHERE 子句：
    WHERE last_name LIKE "Mac%"
    優(yōu)化程序考慮模式中的開始的文字部分，然后利用索引找到相符合的行。不過(guò)寧可寫成如下的表達(dá)式，它允許使用last_name 上的索引：
    WHERE last_name >= "Mac" AND last_name < "Mad"
    這種優(yōu)化對(duì)使用REGEXP 操作符的模式匹配不起作用。
    ■ 幫助優(yōu)化程序更好地評(píng)估索引的有效性。缺省時(shí)，如果將索引列中的值與常量進(jìn)行比較，優(yōu)化程序?qū)⒓俣ㄦI字是均勻地分布在索引中的。優(yōu)化程序還將對(duì)索引進(jìn)行一個(gè)快速的檢查，以估計(jì)在確定相應(yīng)的索引是否應(yīng)該用于常量的比較時(shí)要使用多少條目?？衫胢yisamchk 或isamchk 的--analyze 選項(xiàng)給優(yōu)化程序提供更好的信息，以便分析鍵值的分布。myisamchk 用于MyISAM 表，isamchk 用于ISAM 表。為了完成鍵值分析，必須能夠登錄到MySQL 服務(wù)器主機(jī)中，而且必須對(duì)表文件具有寫訪問(wèn)權(quán)限。
    ■ 利用EXPLAIN 檢驗(yàn)優(yōu)化程序操作。檢查用于查詢中的索引是否能很快地排除行。如果不能，那么應(yīng)該試一下利用STRAIGHT_JOIN 強(qiáng)制按特定次序使用表來(lái)完成一個(gè)連接。查詢的執(zhí)行方式不那么顯然；MySQL 可能會(huì)有很多理由不以您認(rèn)為最好的次序使用索引。
    ■ 測(cè)試查詢的其他形式，而且不止一次地運(yùn)行它們。在測(cè)試一個(gè)查詢的其他形式時(shí)，應(yīng)該每種方法運(yùn)行幾次。如果對(duì)兩個(gè)不同方法中的每種只運(yùn)行查詢一次，通常會(huì)發(fā)現(xiàn)第二個(gè)查詢更快，因?yàn)閬?lái)自第一個(gè)查詢的信息在磁盤高速緩存中，不需要實(shí)際從磁盤上讀出。還應(yīng)該盡量在系統(tǒng)負(fù)載相對(duì)平穩(wěn)的時(shí)候運(yùn)行查詢，以避免受系統(tǒng)中其他活動(dòng)的影響。
    4.2.2 忽略優(yōu)化
    這可能聽起來(lái)有點(diǎn)奇怪，但在以下情況中，要廢除MySQL 的優(yōu)化功能：
    ■ 強(qiáng)迫MySQL 慢慢地刪除表的內(nèi)容。在需要完全刪空一個(gè)表時(shí)，利用無(wú)WHERE 子句的DELETE 語(yǔ)句刪除整個(gè)表的內(nèi)容是最快的，如下所示：
    DELETE FROM tb1_name
    MySQL 對(duì)這種特殊情況的DELETE 進(jìn)行優(yōu)化；它利用表信息文件中的表說(shuō)明從頭開始創(chuàng)建空數(shù)據(jù)文件和索引文件。這種優(yōu)化使DELETE 操作極快，因?yàn)镸ySQL 無(wú)需單獨(dú)地刪除每一行。但在某些情況下，這樣做會(huì)產(chǎn)生一些不必要的負(fù)作用：
    ■ MySQL 報(bào)告所涉及的行數(shù)為零，即使表不為空也是如此。很多時(shí)候這沒(méi)有關(guān)系（雖然，如果事先沒(méi)有思想準(zhǔn)備，會(huì)感到困惑不解），但對(duì)于那些確實(shí)需要知道真實(shí)行數(shù)的應(yīng)用程序來(lái)說(shuō)，這是不恰當(dāng)?shù)摹?br>    ■ 如果表含有一個(gè)A U TO_INCREMENT 列，則該列的順序編號(hào)會(huì)以1 從頭開始。這是真實(shí)的事情，即使在MySQL 3.23 中對(duì)A U TO_INCREMENT 的處理進(jìn)行了改進(jìn)后也是這樣。關(guān)于這個(gè)改進(jìn)的介紹請(qǐng)參閱第2章中的“使用序列”小節(jié)。可增加WHERE 1 > 0 子句對(duì)DELETE 語(yǔ)句“不優(yōu)化”。
    DELETE FROM tb1_name WHERE 1 > 0
    這迫使MySQL 進(jìn)行逐行的刪除。相應(yīng)的查詢執(zhí)行要慢得多，但將返回真正刪除的行數(shù)。它還將保持當(dāng)前的A U TO_INCREMENT 序列的編號(hào)，不過(guò)只對(duì)MyISAM 表（MySQL 3.23 以上的版本可用）有效。而對(duì)于ISAM 表，序列仍將重置。
    ■ 避免更新循環(huán)不終止。如果更新一個(gè)索引列，如果該列用于WHERE 子句且更新將索引值移入至今尚未出超的取值范圍內(nèi)時(shí)，有可能對(duì)所更新的行進(jìn)行不終止的更新。假如表my_tbl 有一個(gè)索引了的整數(shù)列k e y _ c o l。下列的查詢會(huì)產(chǎn)生問(wèn)題：

    這個(gè)問(wèn)題的解決方法是在WHERE 子句中將key_col 用于一個(gè)表達(dá)式，使M y S Q L不能使用索引：

    實(shí)際上，還有另外的方法，即升級(jí)到MySQL 3.23.2 或更高的版本，它們已經(jīng)解決了這樣的問(wèn)題。
    ■ 以隨機(jī)次序檢索結(jié)果。自MySQL 3.23.3 以來(lái)，可使用ORDER BY RAND( ) 隨機(jī)地對(duì)結(jié)果進(jìn)行排序。另一技術(shù)對(duì)MySQL 更舊的版本很有用處，那就是選擇一個(gè)隨機(jī)數(shù)列，然后在該列上進(jìn)行排序。但是，如果按如下編寫查詢，優(yōu)化程序?qū)?huì)讓您的愿望落空：

    這里的問(wèn)題是MySQL 認(rèn)為該列是一個(gè)函數(shù)調(diào)用，將認(rèn)為相應(yīng)的列值是一個(gè)常數(shù)，而對(duì)ORDER BY 子句進(jìn)行優(yōu)化，使此查詢失效?？稍诒磉_(dá)式中引用某個(gè)表列來(lái)蒙騙優(yōu)化程序。例如，如果表中有一個(gè)名為age 的列，可編寫如下查詢：

  　■ 忽略優(yōu)化程序的表連接次序?？衫肧TRIGHT_JOIN 強(qiáng)迫優(yōu)化程序以特定的次序使用表。如果這樣做，應(yīng)該規(guī)定表的次序，使第一個(gè)表為從中選擇的行數(shù)最少的表。（如果不能肯定哪個(gè)表滿足這個(gè)要求，可將行數(shù)最多的表作為第一個(gè)表。）換句話說(shuō)，應(yīng)盡量規(guī)定表的次序，使最有限制性的選擇先出現(xiàn)。排除可能的候選行越早，查詢執(zhí)行得就越快。要保證測(cè)試相應(yīng)的查詢兩次；可能會(huì)有某些原因使優(yōu)化程序不以您所想像的方式對(duì)表進(jìn)行連接，并且STRAIGHT_JOIN 也可能實(shí)際上不起作用。