MySQL查詢優(yōu)化系列講座之查詢優(yōu)化器

2024-07-24 12:40:00

字體：大中小

供稿：網(wǎng)友

　　當(dāng)你提交一個(gè)查詢的時(shí)候，MySQL會(huì)分析它，看是否可以做一些優(yōu)化使處理該查詢的速度更快。這一部分將介紹查詢優(yōu)化器是如何工作的。如果你想知道MySQL采用的優(yōu)化手段，可以查看MySQL參考手冊(cè)。

　　當(dāng)然，MySQL查詢優(yōu)化器也利用了索引，但是它也使用了其它一些信息。例如，如果你提交如下所示的查詢，那么無論數(shù)據(jù)表有多大，MySQL執(zhí)行它的速度都會(huì)非常快：

SELECT * FROM tbl_name WHERE 0;

　　在這個(gè)例子中，MySQL查看WHERE子句，認(rèn)識(shí)到?jīng)]有符合查詢條件的數(shù)據(jù)行，因此根本就不考慮搜索數(shù)據(jù)表。你可以通過提供一個(gè)EXPLAIN語句看到這種情況，這個(gè)語句讓MySQL顯示自己執(zhí)行的但實(shí)際上沒有真正地執(zhí)行的SELECT查詢的一些信息。如果要使用EXPLAIN，只需要在EXPLAIN單詞放在SELECT語句的前面：

mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0G
*************************** 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

　　通常情況下，EXPLAIN返回的信息比上面的信息要多一些，還包括用于掃描數(shù)據(jù)表的索引、使用的聯(lián)結(jié)類型、每張數(shù)據(jù)表中估計(jì)需要檢查的數(shù)據(jù)行數(shù)量等非空（NULL）信息。

　　優(yōu)化器是如何工作的

　　MySQL查詢優(yōu)化器有幾個(gè)目標(biāo)，但是其中最主要的目標(biāo)是盡可能地使用索引，并且使用最嚴(yán)格的索引來消除盡可能多的數(shù)據(jù)行。你的最終目標(biāo)是提交SELECT語句查找數(shù)據(jù)行，而不是排除數(shù)據(jù)行。優(yōu)化器試圖排除數(shù)據(jù)行的原因在于它排除數(shù)據(jù)行的速度越快，那么找到與條件匹配的數(shù)據(jù)行也就越快。如果能夠首先進(jìn)行最嚴(yán)格的測(cè)試，查詢就可以執(zhí)行地更快。假設(shè)你的查詢檢驗(yàn)了兩個(gè)數(shù)據(jù)列，每個(gè)列上都有索引：

SELECT col3 FROM mytable
WHERE col1 = ’some value’ AND col2 = ’some other value’;

　　假設(shè)col1上的測(cè)試匹配了900個(gè)數(shù)據(jù)行，col2上的測(cè)試匹配了300個(gè)數(shù)據(jù)行，而同時(shí)進(jìn)行的測(cè)試只得到了30個(gè)數(shù)據(jù)行。先測(cè)試Col1會(huì)有900個(gè)數(shù)據(jù)行，需要檢查它們找到其中的30個(gè)與col2中的值匹配記錄，其中就有870次是失敗了。先測(cè)試col2會(huì)有300個(gè)數(shù)據(jù)行，需要檢查它們找到其中的30個(gè)與col1中的值匹配的記錄，只有270次是失敗的，因此需要的計(jì)算和磁盤I/O更少。其結(jié)果是，優(yōu)化器會(huì)先測(cè)試col2，因?yàn)檫@樣做開銷更小。

　　你可以通過下面一個(gè)指導(dǎo)幫助優(yōu)化器更好地利用索引：

　　盡量比較數(shù)據(jù)類型相同的數(shù)據(jù)列。當(dāng)你在比較操作中使用索引數(shù)據(jù)列的時(shí)候，請(qǐng)使用數(shù)據(jù)類型相同的列。相同的數(shù)據(jù)類型比不同類型的性能要高一些。例如，INT與BIGINT是不同的。CHAR(10)被認(rèn)為是CHAR(10)或VARCHAR(10)，但是與CHAR(12)或VARCHAR(12)不同。如果你所比較的數(shù)據(jù)列的類型不同，那么可以使用ALTER TABLE來修改其中一個(gè)，使它們的類型相匹配。

　　盡可能地讓索引列在比較表達(dá)式中獨(dú)立。如果你在函數(shù)調(diào)用或者更復(fù)雜的算術(shù)表達(dá)式條件中使用了某個(gè)數(shù)據(jù)列，MySQL就不會(huì)使用索引，因?yàn)樗仨氂?jì)算出每個(gè)數(shù)據(jù)行的表達(dá)式值。有時(shí)候這種情況無法避免，但是很多情況下你可以重新編寫一個(gè)查詢讓索引列獨(dú)立地出現(xiàn)。

　　下面的WHERE子句顯示了這種情況。它們的功能相同，但是對(duì)于優(yōu)化目標(biāo)來說就有很大差異了：

WHERE mycol < 4 / 2
WHERE mycol * 2 < 4

　　對(duì)于第一行，優(yōu)化器把表達(dá)式4/2簡(jiǎn)化為2，接著使用mycol上的索引來快速地查找小于2的值。對(duì)于第二個(gè)表達(dá)式，MySQL必須檢索出每個(gè)數(shù)據(jù)行的mycol值，乘以2，接著把結(jié)果與4進(jìn)行比較。在這種情況下，不會(huì)使用索引。數(shù)據(jù)列中的每個(gè)值都必須被檢索到，這樣才能計(jì)算出比較表達(dá)式左邊的值。

　　我們看另外一個(gè)例子。假設(shè)你對(duì)date_col列進(jìn)行了索引。如果你提交一條如下所示的查詢，就不會(huì)使用這個(gè)索引：

SELECT * FROM mytbl WHERE YEAR(date_col) < 1990;

　　這個(gè)表達(dá)式不會(huì)把1990與索引列進(jìn)行比較；它會(huì)把1990與該數(shù)據(jù)列計(jì)算出來的值比較，而每個(gè)數(shù)據(jù)行都必須計(jì)算出這個(gè)值。其結(jié)果是，沒有使用date_col上的索引，因?yàn)閳?zhí)行這樣的查詢需要全表掃描。怎么解決這個(gè)問題呢？只需要使用文本日期，接著就可以使用date_col上的索引來查找列中匹配的值了：

WHERE date_col < ’1990-01-01’

　　但是，假設(shè)你沒有特定的日期。你可能希望找到一些與今天相隔固定的幾天的日期的記錄。表達(dá)這種類型的比較有很多種方法--它們的效率并不同。下面就有三種：

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)

　　對(duì)于第一行，不會(huì)用到索引，因?yàn)槊總€(gè)數(shù)據(jù)行都必須檢索以計(jì)算出TO_DAYS(date_col)的值。第二行要好一些。Cutoff和TO_DAYS(CURDATE())都是常量，因此在處理查詢之前，比較表達(dá)式的右邊可以被優(yōu)化器一次性計(jì)算出來，而不需要每個(gè)數(shù)據(jù)行都計(jì)算一次。但是date_col列仍然出現(xiàn)在函數(shù)調(diào)用中，它阻止了索引的使用。第三行是這幾個(gè)中最好的。同樣，在執(zhí)行查詢之前，比較表達(dá)式的右邊可以作為常量一次性計(jì)算出來，但是現(xiàn)在它的值是一個(gè)日期。這個(gè)值可以直接與date_col值進(jìn)行比較，再也不需要轉(zhuǎn)換成天數(shù)了。在這種情況下，會(huì)使用索引。

　　在LIKE模式的開頭不要使用通配符。有些字符串搜索使用如下所示的WHERE子句：

WHERE col_name LIKE ’%string%’

　　如果你希望找到那些出現(xiàn)在數(shù)據(jù)列的任何位置的字符串，這個(gè)語句就是對(duì)的。但是不要因?yàn)榱?xí)慣而簡(jiǎn)單地把"%"放在字符串的兩邊。如果你在查找出現(xiàn)在數(shù)據(jù)列開頭的字符串，就刪掉前面的"%"。假設(shè)你要查找那些類似MacGregor或MacDougall等以"Mac"開頭的名字。在這種情況下，WHERE子句如下所示：

WHERE last_name LIKE ’Mac%’

　　優(yōu)化器查看該模式中詞首的文本，并使用索引找到那些與下面的表達(dá)式匹配的數(shù)據(jù)行。下面的表達(dá)式是使用last_name索引的另一種形式：

WHERE last_name >= ’Mac’ AND last_name < ’Mad’

　　這種優(yōu)化不能應(yīng)用于使用了REGEXP操作符的模式匹配。REGEXP表達(dá)式永遠(yuǎn)不會(huì)被優(yōu)化。

　　幫助優(yōu)化器更好的判斷索引的效率。在默認(rèn)情況下，當(dāng)你把索引列的值與常量進(jìn)行比較的時(shí)候，優(yōu)化器會(huì)假設(shè)鍵值在索引內(nèi)部是均勻分布的。在決定進(jìn)行常量比較是否使用索引的時(shí)候，優(yōu)化器會(huì)快速地檢查索引，估計(jì)出會(huì)用到多少個(gè)實(shí)體（entry）。對(duì)應(yīng)MyISAM、InnoDB和BDB數(shù)據(jù)表來說，你可以使用ANALYZE TABLE讓服務(wù)器執(zhí)行對(duì)鍵值的分析。它會(huì)為優(yōu)化器提供更好的信息。

　　使用EXPLAIN驗(yàn)證優(yōu)化器的操作。EXPLAIN語句可以告訴你是否使用了索引。當(dāng)你試圖用另外的方式編寫語句或檢查添加索引是否會(huì)提高查詢執(zhí)行效率的時(shí)候，這些信息對(duì)你是有幫助的。

　　在必要的時(shí)候給優(yōu)化器一些提示。正常情況下，MySQL優(yōu)化器自由地決定掃描數(shù)據(jù)表的次序來最快地檢索數(shù)據(jù)行。在有些場(chǎng)合中優(yōu)化器沒有作出最佳選擇。如果你察覺這種現(xiàn)象發(fā)生了，就可以使用STRAIGHT_JOIN關(guān)鍵字來重載優(yōu)化器的選擇。帶有STRAIGHT_JOIN的聯(lián)結(jié)類似于交叉聯(lián)結(jié)，但是強(qiáng)迫數(shù)據(jù)表按照FROM子句中指定的次序來聯(lián)結(jié)。

　　在SELECT語句中有兩個(gè)地方可以指定STRAIGHT_JOIN。你可以在SELECT關(guān)鍵字和選擇列表之間的位置指定，這樣會(huì)對(duì)語句中所有的交叉聯(lián)結(jié)產(chǎn)生影響；你也可以在FROM子句中指定。下面的兩個(gè)語句功能相同：

SELECT STRAIGHT_JOIN ... FROM t1, t2, t3 ... ;
SELECT ... FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3 ... ;

　　分別在帶有STRAIGHT_JOIN和不帶STRAIGHT_JOIN的情況下運(yùn)行這個(gè)查詢；MySQL可能因?yàn)槭裁丛驔]有按照你認(rèn)為最好的次序使用索引（你可以使用EXPLAIN來檢查MySQL處理每個(gè)語句的執(zhí)行計(jì)劃）。

　　你還可以使用FORCE INDEX、USE INDEX或IGNORE INDEX來指導(dǎo)服務(wù)器如何使用索引。

　　利用優(yōu)化器更加完善的區(qū)域。MySQL可以執(zhí)行聯(lián)結(jié)和子查詢，但是子查詢是最近才支持的，是在MySQL 4.1中添加的。因而在很多情況下，優(yōu)化器對(duì)聯(lián)結(jié)操作的調(diào)整比對(duì)子查詢的調(diào)整要好一些。當(dāng)你的子查詢執(zhí)行地很慢的時(shí)候，這就是一條實(shí)際的提示。有一些子查詢可以使用邏輯上相等的聯(lián)結(jié)來重新表達(dá)。在可行的情況下，你可以把子查詢重新改寫為聯(lián)結(jié)，看是否執(zhí)行地快一些。

　　測(cè)試查詢的備用形式，多次運(yùn)行。當(dāng)你測(cè)試查詢的備用形式的時(shí)候（例如，子查詢與等同的聯(lián)結(jié)操作對(duì)比），每種方式都應(yīng)該多次運(yùn)行。如果兩種形式都只運(yùn)行了一次，那么你通常會(huì)發(fā)現(xiàn)第二個(gè)查詢比第一個(gè)快，這是因?yàn)榈谝粋€(gè)查詢得到的信息仍然保留在緩存中，以至于第二個(gè)查詢沒有真正地從磁盤上讀取數(shù)據(jù)。你還應(yīng)該在系統(tǒng)負(fù)載相對(duì)平穩(wěn)的時(shí)候運(yùn)行查詢，以避免系統(tǒng)中其它的事務(wù)影響結(jié)果。

　　避免過度地使用MySQL自動(dòng)類型轉(zhuǎn)換。MySQL會(huì)執(zhí)行自動(dòng)的類型轉(zhuǎn)換，但是如果你能夠避免這種轉(zhuǎn)換操作，你得到的性能就更好了。例如，如果num_col是整型數(shù)據(jù)列，那么下面這些查詢將返回相同的結(jié)果：

SELECT * FROM mytbl WHERE num_col = 4;
SELECT * FROM mytbl WHERE num_col = ’4’;

　　但是第二個(gè)查詢涉及到了類型轉(zhuǎn)換。轉(zhuǎn)換操作本身為了把整型和字符串型轉(zhuǎn)換為雙精度型進(jìn)行比較，使性能惡化了。更嚴(yán)重的情況是，如果num_col是索引的，那么涉及到類型轉(zhuǎn)換的比較操作不會(huì)使用索引。

　　相反類型的比較操作（把字符串列與數(shù)值比較）也會(huì)阻止索引的使用。假設(shè)你編寫了如下所示的查詢：

SELECT * FROM mytbl WHERE str_col = 4;

　　在這個(gè)例子中，不會(huì)使用str_col上的索引，因?yàn)樵诎裺tr_col中的字符串值轉(zhuǎn)換成數(shù)值的時(shí)候，可能有很多值等于4（例如’4’、’4.0’和’4th’）。分辨哪些值符合要求的唯一辦法是讀取每個(gè)數(shù)據(jù)行并執(zhí)行比較操作。

　　使用EXPLAIN來檢查優(yōu)化器的操作

　　EXPLAIN對(duì)于了解優(yōu)化器生成的、用于處理語句的執(zhí)行計(jì)劃的內(nèi)部信息是很有幫助的。在這一部分中，我們將解釋EXPLAIN的兩種用途：

　　? 查看采用不同的方式編寫的查詢是否影響了索引的使用。

　　? 查看向數(shù)據(jù)表添加索引對(duì)優(yōu)化器生成高效率執(zhí)行計(jì)劃的能力的影響。

　　這一部分只討論與示例相關(guān)的EXPLAIN輸入字段。

　　前面，在"優(yōu)化器是如何工作的"部分中我們得出的觀點(diǎn)是，你編寫表達(dá)式的方式將決定優(yōu)化器是否能使用可用的索引。特別是上面的討論使用了下面三個(gè)邏輯相等的WHERE子句的例子，只有第三個(gè)允許使用索引：

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允許你查看編寫表達(dá)式的某種方式是否比另外的方式好一些。為了看到結(jié)果，讓我們分別用這三個(gè)WHERE子句搜索成員表中過期的數(shù)據(jù)列值，把cutoff值設(shè)為30天。為了看到索引的使用和表達(dá)式編寫方式之間的關(guān)系，我們首先對(duì)expiration列進(jìn)行索引：

mysql> ALTER TABLE member ADD INDEX (expiration);

　　接著在每個(gè)表達(dá)式形式上使用EXPLAIN，看優(yōu)化器生成了什么樣的執(zhí)行計(jì)劃：

mysql> EXPLAIN SELECT * FROM MEMBER
-> WHERE TO_DAYS(expiration) - TO_DAYS(CURDATE()) < 30G
*************************** 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

　　上面的結(jié)果顯示，前面兩個(gè)語句沒有使用索引。類型（type）值表明了將如何從數(shù)據(jù)表中讀取信息。ALL意味著"將檢查所有的記錄"。也就是說，它會(huì)執(zhí)行全表掃描，沒有利用索引。每個(gè)與鍵相關(guān)的列都是NULL也表明沒有使用索引。

　　與此形成對(duì)比的是，第三個(gè)語句的結(jié)果顯示，采用這種方式編寫的WHERE子句，優(yōu)化器可以使用expiration列上的索引：

　　? 類型（type）值表明它可以使用索引來搜索特定范圍的值（小于右邊表達(dá)式給定的值）。

　　? 可能鍵（possible_keys）和鍵（key）值顯示expiration上的索引已經(jīng)被考慮作為備選索引，并且它也是真正使用的索引。

　　? 行數(shù)（rows）值顯示優(yōu)化器估計(jì)自己需要檢查6個(gè)數(shù)據(jù)行來處理該查詢。這比前面兩個(gè)執(zhí)行計(jì)劃的102小很多。

　　EXPLAIN的第二種用途是查看添加索引是否能幫助優(yōu)化器更高效率地執(zhí)行語句。我將使用兩個(gè)未被索引的數(shù)據(jù)表。它足夠顯示建立索引的效率。相同的規(guī)則可以應(yīng)用于涉及多表的更加復(fù)雜的聯(lián)結(jié)操作。

　　假設(shè)我們有兩個(gè)數(shù)據(jù)表t1和t2，每個(gè)有1000行，包含的值從1到1000。下面的查詢查找出兩個(gè)表中值相同的數(shù)據(jù)行：

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 |
...

　　兩個(gè)表都沒有索引的時(shí)候，EXPLAIN產(chǎn)生下面的結(jié)果：

mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2G
*************************** 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表明要進(jìn)行檢查所有數(shù)據(jù)行的全表掃描。可能鍵列中的NULL表明沒有找到用于提高查詢速度的備選索引（鍵、鍵長(zhǎng)度和參考列都是NULL也是因?yàn)槿鄙俸线m的索引）。Using where表明使用WHERE子句中的信息來識(shí)別合格的數(shù)據(jù)行。

　　這段信息告訴我們，優(yōu)化器沒有為提高執(zhí)行查詢的效率找到任何有用的信息：

　　? 它將對(duì)t1表進(jìn)行全表掃描。

　　? 對(duì)于t1中的每一行，它將執(zhí)行t2的全表掃描，使用WHERE子句中的信息識(shí)別出合格的行。

　　行數(shù)值顯示了優(yōu)化器估計(jì)的每個(gè)階段查詢需要檢查的行數(shù)。T1的估計(jì)值是1000，因?yàn)?000可以完成全表掃描。相似地，t2的估計(jì)值也是1000，但是這個(gè)值是對(duì)于t1的每一行的。換句話說，優(yōu)化器所估計(jì)的處理該查詢所需要檢查的數(shù)據(jù)行組合的數(shù)量是1000×1000，也就是一百萬。這會(huì)造成很大的浪費(fèi)，因?yàn)閷?shí)際上只有1000個(gè)組合符合WHERE子句的條件。

　　為了使這個(gè)查詢的效率更高，給其中一個(gè)聯(lián)結(jié)列添加索引并重新執(zhí)行EXPLAIN語句：

mysql> ALTER TABLE t2 ADD INDEX (i2);
mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2G
*************************** 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的輸出沒有改變（表明還是需要進(jìn)行全表掃描），但是優(yōu)化器處理t2的方式就有所不同了：

　　? 類型從ALL改變?yōu)閞ef，意味著可以使用參考值（來自t1的值）來執(zhí)行索引查找，定位t2中合格的數(shù)據(jù)行。

　　? 參考值在參考（ref）字段中給出了：sampdb.t1.i1。

　　? 行數(shù)值從1000降低到了10，顯示出優(yōu)化器相信對(duì)于t1中的每一行，它只需要檢查t2中的10行（這是一個(gè)悲觀的估計(jì)值。實(shí)際上，在t2中只有一行與t1中數(shù)據(jù)行匹配。我們?cè)诤竺鏁?huì)看到如何幫助優(yōu)化器改善這個(gè)估計(jì)值）。數(shù)據(jù)行組合的全部估計(jì)值使1000×10＝10000。它比前面的沒有索引的時(shí)候估計(jì)出來的一百萬好多了。

　　對(duì)t1進(jìn)行索引有價(jià)值嗎？實(shí)際上，對(duì)于這個(gè)特定的聯(lián)結(jié)操作，掃描一張表是必要的，因此沒有必要對(duì)t1建立索引。如果你想看到效果，可以索引t1.i1并再次運(yùn)行EXPLAIN：

mysql> ALTER TABLE t1 ADD INDEX (i1);
mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2G
*************************** 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的輸出相似，但是添加索引對(duì)t1的輸出有一些改變。類型從NULL改成了index，附加（Extra）從空的改成了Using index。這些改變表明，盡管對(duì)索引的值仍然需要執(zhí)行全表掃描，但是優(yōu)化器還是可以直接從索引文件中讀取值，根據(jù)不需要使用數(shù)據(jù)文件。你可以從MyISAM表中看到這類結(jié)果，在這種情況下，優(yōu)化器知道自己只詢問索引文件就能夠得到所有需要的信息。對(duì)于InnoDB 和BDB表也有這樣的結(jié)果，在這種情況下優(yōu)化器可以單獨(dú)使用索引中的信息而不用搜索數(shù)據(jù)行。

　　我們可以運(yùn)行ANALYZE TABLE使優(yōu)化器進(jìn)一步優(yōu)化估計(jì)值。這會(huì)引起服務(wù)器生成鍵值的靜態(tài)分布。分析上面的表并再次運(yùn)行EXPLAIN得到了更好的估計(jì)值：

mysql> ANALYZE TABLE t1, t2;
mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2G
*************************** 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

　　在這種情況下，優(yōu)化器估計(jì)在t2中與t1的每個(gè)值匹配的數(shù)據(jù)行只有一個(gè)。

　　重載優(yōu)化過程

　　這個(gè)過程聽起來多余，但是有時(shí)候你還是希望去掉某些MySQL優(yōu)化行為的：

　　重載優(yōu)化器的表聯(lián)結(jié)次序。使用STRAIGHT_JOIN強(qiáng)迫優(yōu)化器按照特定的次序使用數(shù)據(jù)表。在這樣操作的時(shí)候，你必須對(duì)數(shù)據(jù)表進(jìn)行排序，這樣才能保證第一張表是被選擇的行數(shù)最少的表。如果你不能確定被選擇行數(shù)最少的是哪一張表，那么就把行數(shù)最多的放到第一的位置。換句話說，試著對(duì)表進(jìn)行排序，使最有約束力的選擇出現(xiàn)在最前面。你對(duì)可能的備選數(shù)據(jù)行縮小地越早，執(zhí)行查詢的性能就越好。請(qǐng)確保在帶有STRAIGHT_JOIN和不帶STRAIGHT_JOIN的時(shí)候分別執(zhí)行該查詢。有時(shí)候由于某些原因的存在，優(yōu)化器沒有按照你認(rèn)定的方式聯(lián)結(jié)數(shù)據(jù)表，STRAIGHT_JOIN也可能沒有實(shí)際的幫助作用。

　　另一個(gè)可能性是在聯(lián)結(jié)的數(shù)據(jù)表列表中的某個(gè)表的后面使用FORCE INDEX、USE INDEX和IGNORE INDEX調(diào)節(jié)符來告訴MySQL如何使用索引。這在優(yōu)化器沒有做出正確選擇的時(shí)候是有用處的。

　　以最小的代價(jià)清空一張表。當(dāng)需要完全地清空一張MyISAM數(shù)據(jù)表的時(shí)候，最快的方法是刪除它并利用它的.frm文件中存儲(chǔ)的腳本來重新建立它。使用TRUNCATE TABLE語句實(shí)現(xiàn)：

TRUNCATE TABLE tbl_name;

　　通過重新建立MyISAM數(shù)據(jù)表來清空它的這種服務(wù)器優(yōu)化措施使該操作非常快，因?yàn)椴恍枰獑为?dú)地逐行刪除。

　　但是TRUNCATE TABLE也帶來了一些副作用，在某些環(huán)境中是不符合要求的：

　　? TRUNCATE TABLE不一定能夠計(jì)算出被刪除的數(shù)據(jù)列的精確數(shù)量。如果你需要這個(gè)數(shù)值，請(qǐng)使用不帶WHERE子句的DELETE語句：

DELETE FROM tbl_name;

　　? 但是，通過重新建立來清空數(shù)據(jù)表，它可能會(huì)把序號(hào)的起始值設(shè)置為1。為了避免這種情況，請(qǐng)使用"不優(yōu)化的"全表DELETE語句，它帶有一個(gè)恒為真的WHERE子句：

DELETE FROM tbl_name WHERE 1;

　　添加WHERE子句會(huì)強(qiáng)迫MySQL進(jìn)行逐行刪除，因?yàn)樗仨氂?jì)算出每一行的值來判斷是否能夠刪除它。這個(gè)語句執(zhí)行的速度很慢，但是它卻保留了當(dāng)前的AUTO_INCREMENT序號(hào)。

上一篇：MySQL查詢優(yōu)化技術(shù)系列講座之使用索引

下一篇：MySQL查詢優(yōu)化系列講座之?dāng)?shù)據(jù)類型與效率