MySQL查詢優(yōu)化技術(shù)使用索引

1、MySQL查詢優(yōu)化技術(shù)使用索引添加時(shí)間:2007-4-22索引是提高查詢速度的最重要的工具。當(dāng)然還有其它的一些技術(shù)可供使用,但是一般來(lái)說(shuō)引起最大性能差異的都是索引的正確使用。在MySQL郵件列表中,人們經(jīng)常詢問(wèn)那些讓查詢運(yùn)行得更快的方法。在大多數(shù)情況下,我們應(yīng)該懷疑數(shù)據(jù)表上有沒(méi)有索引,并且通常在添加索引之后立即解決了問(wèn)題。當(dāng)然,并不總是這樣簡(jiǎn)單就可以解決問(wèn)題的,因?yàn)閮?yōu)化技術(shù)本來(lái)就并非總是簡(jiǎn)單的。然而,如果沒(méi)有使用索引,在很多情況下,你試圖使用其它的方法來(lái)提高性能都是在浪費(fèi)時(shí)間。首先使用索引來(lái)獲取最大的性能提高,接著再看其它的技術(shù)是否有用。這一部分講述了索引是什么以及索引是怎么樣提高查詢性能的。

2、它還討論了在某些環(huán)境中索引可能降低性能,并為你明智地選擇數(shù)據(jù)表的索引提供了一些指導(dǎo)方針。在下一部分中我們將討論MySQL查詢優(yōu)化器,它試圖找到執(zhí)行查詢的效率最高的方法。了解一些優(yōu)化器的知識(shí),作為對(duì)如何建立索引的補(bǔ)充,對(duì)我們是有好處的,因?yàn)檫@樣你才能更好地利用自己所建立的索引。某些編寫查詢的方法實(shí)際上讓索引不起作用,在一般情況下你應(yīng)該避免這種情形的發(fā)生。索引的優(yōu)點(diǎn)讓我們開(kāi)始了解索引是如何工作的,首先有一個(gè)不帶索引的數(shù)據(jù)表。不帶索引的表僅僅是一個(gè)無(wú)序的數(shù)據(jù)行集合。例如,圖1顯示的ad表就是不帶索引的表,因此如果需要查找某個(gè)特定的公司,就必須檢查表中的每個(gè)數(shù)據(jù)行看它是否與目標(biāo)值相匹配。這會(huì)導(dǎo)致一次完

3、全的數(shù)據(jù)表掃描,這個(gè)過(guò)程會(huì)很慢,如果這個(gè)表很大,但是只包含少量的符合條件的記錄,那么效率會(huì)非常低。圖1:無(wú)索引的ad表圖2是同樣的一張數(shù)據(jù)表,但是增加了對(duì)ad表的company_num數(shù)據(jù)列的索引。這個(gè)索引包含了ad表中的每個(gè)數(shù)據(jù)行的條目,但是索引的條目是按照company_num值排序的?，F(xiàn)在,我們不是逐行查看以搜尋匹配的數(shù)據(jù)項(xiàng),而是使用索引。假設(shè)我們查找公司13的所有數(shù)據(jù)行。我們開(kāi)始掃描索引并找到了該公司的三個(gè)值。接著我們碰到了公司14的索引值,它比我們正在搜尋的值大。索引值是排過(guò)序的,因此當(dāng)我們讀取了包含14的索引記錄的時(shí)候,我們就知道再也不會(huì)有更多的匹配記錄,可以結(jié)束查詢操作了。因此使

4、用索引獲得的功效是:我們找到了匹配的數(shù)據(jù)行在哪兒終止,并能夠忽略其它的數(shù)據(jù)行。另一個(gè)功效來(lái)自使用定位算法查找第一條匹配的條目,而不需要從索引頭開(kāi)始執(zhí)行線性掃描(例如,二分搜索就比線性掃描要快一些。通過(guò)使用這種方法,我們可以快速地定位第一個(gè)匹配的值,節(jié)省了大量的搜索時(shí)間。數(shù)據(jù)庫(kù)使用了多種技術(shù)來(lái)快速地定位索引值,但是在本文中我們不關(guān)心這些技術(shù)。重點(diǎn)是它們能夠?qū)崿F(xiàn),并且索引是個(gè)好東西。圖2:索引后的ad表你可能要問(wèn),我們?yōu)槭裁床粚?duì)數(shù)據(jù)行進(jìn)行排序從而省掉索引?這樣不是也能實(shí)現(xiàn)同樣的搜索速度的改善嗎?是的,如果表只有一個(gè)索引,這樣做也可能達(dá)到相同的效果。但是你可能添加第二個(gè)索引,那么就無(wú)法一次使用兩種不

5、同方法對(duì)數(shù)據(jù)行進(jìn)行排序了(例如,你可能希望在顧客名稱上建立一個(gè)索引,在顧客ID號(hào)或電話號(hào)碼上建立另外一個(gè)索引。把與數(shù)據(jù)行相分離的條目作為索引解決了這個(gè)問(wèn)題,允許我們創(chuàng)建多個(gè)索引。此外,索引中的行一般也比數(shù)據(jù)行短一些。當(dāng)你插入或刪除新的值的時(shí)候,移動(dòng)較短的索引值比移動(dòng)較長(zhǎng)數(shù)據(jù)行的排序次序更加容易。不同的MySQL存儲(chǔ)引擎的索引實(shí)現(xiàn)的具體細(xì)節(jié)信息是不同的。例如,對(duì)于MyISAM數(shù)據(jù)表,該表的數(shù)據(jù)行保存在一個(gè)數(shù)據(jù)文件中,索引值保存在索引文件中。一個(gè)數(shù)據(jù)表上可能有多個(gè)索引,但是它們都被存儲(chǔ)在同一個(gè)索引文件中。索引文件中的每個(gè)索引都包含一個(gè)排序的鍵記錄(它用于快速地訪問(wèn)數(shù)據(jù)文件數(shù)組。與此形成對(duì)照的是,B

6、DB和InnoDB存儲(chǔ)引擎沒(méi)有使用這種方法來(lái)分離數(shù)據(jù)行和索引值,盡管它們也把索引作為排序后的值集合進(jìn)行操作。在默認(rèn)情況下,BDB引擎使用單個(gè)文件存儲(chǔ)數(shù)據(jù)和索引值。InnoDB使用單個(gè)數(shù)據(jù)表空間(tablespace,在表空間中管理所有InnoDB 表的數(shù)據(jù)和索引存儲(chǔ)。我們可以把InnoDB配置為每個(gè)表都在自己的表空間中創(chuàng)建,但是即使是這樣,數(shù)據(jù)表的數(shù)據(jù)和索引也存儲(chǔ)在同一個(gè)表空間文件中。前面的討論描述了單個(gè)表查詢環(huán)境下的索引的優(yōu)點(diǎn),在這種情況下,通過(guò)減少對(duì)整個(gè)表的掃描,使用索引明顯地提高了搜索的速度。當(dāng)你運(yùn)行涉及多表聯(lián)結(jié)(jion查詢的時(shí)候,索引的價(jià)值就更高了。在單表查詢中,你需要在每個(gè)數(shù)據(jù)列上

7、檢查的值的數(shù)量是表中數(shù)據(jù)行的數(shù)量。在多表查詢中,這個(gè)數(shù)量可能大幅度上升,因?yàn)檫@個(gè)數(shù)量是這些表中數(shù)據(jù)行的數(shù)量所產(chǎn)生的。假設(shè)你擁有三個(gè)未索引的表t1、t2和t3,每個(gè)表都分別包含數(shù)據(jù)列i1、i2和i3,并且每個(gè)表都包含了1000條數(shù)據(jù)行,其序號(hào)從1到1000。查找某些值匹配的數(shù)據(jù)行組合的查詢可能如下所示:SELECT t1.i1, t2.i2, t3.i3FROM t1, t2, t3WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;這個(gè)查詢的結(jié)果應(yīng)該是1000行,每個(gè)數(shù)據(jù)行包含三個(gè)相等的值。如果在沒(méi)有索引的情況下處理這個(gè)查詢,那么如果我們不對(duì)這些表進(jìn)行全部地掃描,我們是

8、沒(méi)有辦法知道哪些數(shù)據(jù)行含有哪些值的。因此你必須嘗試所有的組合來(lái)查找符合WHERE條件的記錄?？赡艿慕M合的數(shù)量是1000 x 1000 x 1000(10億!,它是匹配記錄的數(shù)量的一百萬(wàn)倍。這就浪費(fèi)了大量的工作。這個(gè)例子顯示,如果沒(méi)有使用索引,隨著表的記錄不斷增長(zhǎng),處理這些表的聯(lián)結(jié)所花費(fèi)的時(shí)間增長(zhǎng)得更快,導(dǎo)致性能很差。我們可以通過(guò)索引這些數(shù)據(jù)表來(lái)顯著地提高速度,因?yàn)樗饕尣樵儾捎萌缦滤镜姆绞絹?lái)處理:1.選擇表t1中的第一行并查看該數(shù)據(jù)行的值。2.使用表t2上的索引,直接定位到與t1的值匹配的數(shù)據(jù)行。類似地,使用表t3上的索引,直接定位到與表t2的值匹配的數(shù)據(jù)行。3.處理表t1的下一行并重復(fù)前面

9、的過(guò)程。執(zhí)行這樣的操作直到t1中的所有數(shù)據(jù)行都被檢查過(guò)。在這種情況下,我們?nèi)匀粚?duì)表t1執(zhí)行了完整的掃描,但是我們可以在t2和t3上執(zhí)行索引查找,從這些表中直接地獲取數(shù)據(jù)行。理論上采用這種方式運(yùn)行上面的查詢會(huì)快一百萬(wàn)倍。當(dāng)然這個(gè)例子是為了得出結(jié)論來(lái)人為建立的。然而,它解決的問(wèn)題卻是現(xiàn)實(shí)的,給沒(méi)有索引的表添加索引通常會(huì)獲得驚人的性能提高。MySQL有幾種使用索引的方式:·如上所述,索引被用于提高WHERE條件的數(shù)據(jù)行匹配或者執(zhí)行聯(lián)結(jié)操作時(shí)匹配其它表的數(shù)據(jù)行的搜索速度。·對(duì)于使用了MIN(或MAX(函數(shù)的查詢,索引數(shù)據(jù)列中最小或最大值可以很快地找到,不用檢查每個(gè)數(shù)據(jù)行。·

10、; MySQL利用索引來(lái)快速地執(zhí)行ORDER BY和GROUP BY語(yǔ)句的排序和分組操作。·有時(shí)候MySQL會(huì)利用索引來(lái)讀取查詢得到的所有信息。假設(shè)你選擇了MyISAM表中的被索引的數(shù)值列,那么就不需要從該數(shù)據(jù)表中選擇其它的數(shù)據(jù)列。在這種情況下,MySQL從索引文件中讀取索引值,它所得到的值與讀取數(shù)據(jù)文件得到的值是相同的。沒(méi)有必要兩次讀取相同的值,因此沒(méi)有必要考慮數(shù)據(jù)文件。索引的代價(jià)一般來(lái)說(shuō),如果MySQL能夠找到方法,利用索引來(lái)更快地處理查詢,它就會(huì)這樣做。這意味著,對(duì)于大多數(shù)情況,如果你沒(méi)有對(duì)表進(jìn)行索引,就會(huì)使性能受到損害。這就是我所描繪的索引優(yōu)點(diǎn)的美景。但是它有缺點(diǎn)嗎?有的,它

11、在時(shí)間和空間上都有開(kāi)銷。在實(shí)踐中,索引的優(yōu)點(diǎn)的價(jià)值一般會(huì)超過(guò)這些缺點(diǎn),但是你也應(yīng)該知道到底有一些什么缺點(diǎn)。首先,索引加快了檢索的速度,但是減慢了插入和刪除的速度,同時(shí)還減慢了更新被索引的數(shù)據(jù)列中的值的速度。也就是說(shuō),索引減慢了大多數(shù)涉及寫操作的速度。發(fā)生這種現(xiàn)象的原因在于寫入一條記錄的時(shí)候不但需要寫入數(shù)據(jù)行,還需要改變所有的索引。數(shù)據(jù)表帶有的索引越多,需要做出的修改就越多,平均性能的降低程度也就越大。在本文的"高效率載入數(shù)據(jù)"部分中,我們將更細(xì)致地了解這些現(xiàn)象并找出處理方法。其次,索引會(huì)花費(fèi)磁盤空間,多個(gè)索引相應(yīng)地花費(fèi)更多的磁盤空間。這可能導(dǎo)致更快地到達(dá)數(shù)據(jù)表的大小限制:&

12、#183;對(duì)于MyISAM表,頻繁地索引可能引起索引文件比數(shù)據(jù)文件更快地達(dá)到最大限制。·對(duì)于BDB表,它把數(shù)據(jù)和索引值一起存儲(chǔ)在同一個(gè)文件中,添加索引引起這種表更快地達(dá)到最大文件限制。·在InnoDB的共享表空間中分配的所有表都競(jìng)爭(zhēng)使用相同的公共空間池,因此添加索引會(huì)更快地耗盡表空間中的存儲(chǔ)。但是,與MyISAM和BDB表使用的文件不同,InnoDB共享表空間并不受操作系統(tǒng)的文件大小限制,因?yàn)槲覀兛梢园阉渲贸墒褂枚鄠€(gè)文件。只要有額外的磁盤空間,你就可以通過(guò)添加新組件來(lái)擴(kuò)展表空間。使用單獨(dú)表空間的InnoDB表與BDB表受到的約束是一樣的,因?yàn)樗臄?shù)據(jù)和索引值都存儲(chǔ)在單個(gè)文

13、件中。這些要素的實(shí)際含義是:如果你不需要使用特殊的索引幫助查詢執(zhí)行得更快,就不要建立索引。選擇索引假設(shè)你已經(jīng)知道了建立索引的語(yǔ)法,但是語(yǔ)法不會(huì)告訴你數(shù)據(jù)表應(yīng)該如何索引。這要求我們考慮數(shù)據(jù)表的使用方式。這一部分指導(dǎo)你如何識(shí)別出用于索引的備選數(shù)據(jù)列,以及如何最好地建立索引:用于搜索、排序和分組的索引數(shù)據(jù)列并不僅僅是用于輸出顯示的。換句話說(shuō),用于索引的最好的備選數(shù)據(jù)列是那些出現(xiàn)在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。僅僅出現(xiàn)在SELECT關(guān)鍵字后面的輸出數(shù)據(jù)列列表中的數(shù)據(jù)列不是很好的備選列: SELECTcol_a <- 不是備選列FROMtbl1 LEF

14、T JOIN tbl2ON tbl1.col_b = tbl2.col_c <- 備選列WHEREcol_d = expr; <- 備選列當(dāng)然,顯示的數(shù)據(jù)列與WHERE子句中使用的數(shù)據(jù)列也可能相同。我們的觀點(diǎn)是輸出列表中的數(shù)據(jù)列本質(zhì)上不是用于索引的很好的備選列。Join子句或WHERE子句中類似col1 = col2形式的表達(dá)式中的數(shù)據(jù)列都是特別好的索引備選列。前面顯示的查詢中的col_b和col_c就是這樣的例子。如果MySQL能夠利用聯(lián)結(jié)列來(lái)優(yōu)化查詢,它一定會(huì)通過(guò)減少整表掃描來(lái)大幅度減少潛在的表-行組合?？紤]數(shù)據(jù)列的基數(shù)(cardinality?；鶖?shù)是數(shù)據(jù)列所包含的不同值的數(shù)量

15、。例如,某個(gè)數(shù)據(jù)列包含值1、3、7、4、7、3,那么它的基數(shù)就是4。索引的基數(shù)相對(duì)于數(shù)據(jù)表行數(shù)較高(也就是說(shuō),列中包含很多不同的值,重復(fù)的值很少的時(shí)候,它的工作效果最好。如果某數(shù)據(jù)列含有很多不同的年齡,索引會(huì)很快地分辨數(shù)據(jù)行。如果某個(gè)數(shù)據(jù)列用于記錄性別(只有"M"和"F"兩種值,那么索引的用處就不大。如果值出現(xiàn)的幾率幾乎相等,那么無(wú)論搜索哪個(gè)值都可能得到一半的數(shù)據(jù)行。在這些情況下,最好根本不要使用索引,因?yàn)椴樵儍?yōu)化器發(fā)現(xiàn)某個(gè)值出現(xiàn)在表的數(shù)據(jù)行中的百分比很高的時(shí)候,它一般會(huì)忽略索引,進(jìn)行全表掃描。慣用的百分比界線是"30%"。現(xiàn)在查詢優(yōu)

16、化器更加復(fù)雜,把其它一些因素也考慮進(jìn)去了,因此這個(gè)百分比并不是MySQL決定選擇使用掃描還是索引的唯一因素。索引較短的值。盡可能地使用較小的數(shù)據(jù)類型。例如,如果MEDIUMINT足夠保存你需要存儲(chǔ)的值,就不要使用BIGINT數(shù)據(jù)列。如果你的值不會(huì)長(zhǎng)于25個(gè)字符,就不要使用CHAR(100。較小的值通過(guò)幾個(gè)方面改善了索引的處理速度:·較短的值可以更快地進(jìn)行比較,因此索引的查找速度更快了。·較小的值導(dǎo)致較小的索引,需要更少的磁盤I/O。·使用較短的鍵值的時(shí)候,鍵緩存中的索引塊(block可以保存更多的鍵值。MySQL 可以在內(nèi)存中一次保持更多的鍵,在不需要從磁盤讀取額

17、外的索引塊的情況下,提高鍵值定位的可能性。對(duì)于InnoDB和BDB等使用聚簇索引(clustered index的存儲(chǔ)引擎來(lái)說(shuō),保持主鍵(primary key短小的優(yōu)勢(shì)更突出。聚簇索引中數(shù)據(jù)行和主鍵值存儲(chǔ)在一起(聚簇在一起。其它的索引都是次級(jí)索引;它們存儲(chǔ)主鍵值和次級(jí)索引值。次級(jí)索引屈從主鍵值,它們被用于定位數(shù)據(jù)行。這暗示主鍵值都被復(fù)制到每個(gè)次級(jí)索引中,因此如果主鍵值很長(zhǎng),每個(gè)次級(jí)索引就需要更多的額外空間。索引字符串值的前綴(prefixe。如果你需要索引一個(gè)字符串?dāng)?shù)據(jù)列,那么最好在任何適當(dāng)?shù)那闆r下都應(yīng)該指定前綴長(zhǎng)度。例如,如果有CHAR(200數(shù)據(jù)列,如果前面10個(gè)或20個(gè)字符都不同,就不

18、要索引整個(gè)數(shù)據(jù)列。索引前面10個(gè)或20個(gè)字符會(huì)節(jié)省大量的空間,并且可能使你的查詢速度更快。通過(guò)索引較短的值,你可以獲得那些與比較速度和磁盤I/O節(jié)省相關(guān)的好處。當(dāng)然你也需要利用常識(shí)。僅僅索引某個(gè)數(shù)據(jù)列的第一個(gè)字符串可能用處不大,因?yàn)槿绻@樣操作,那么在索引中不會(huì)有太多的唯一值。你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT數(shù)據(jù)列的前綴。使用最左(leftmost前綴。建立多列復(fù)合索引的時(shí)候,你實(shí)際上建立了MySQL可以使用的多個(gè)索引。復(fù)合索引可以作為多個(gè)索引使用,因?yàn)樗饕凶钭筮叺牧屑隙伎梢杂糜谄ヅ鋽?shù)據(jù)行。這種列集合被稱為"最左前綴&quo

19、t;(它與索引某個(gè)列的前綴不同,那種索引把某個(gè)列的前面幾個(gè)字符作為索引值。假設(shè)你在表的state、city和zip數(shù)據(jù)列上建立了復(fù)合索引。索引中的數(shù)據(jù)行按照state/city/zip次序排列,因此它們也會(huì)自動(dòng)地按照state/city和state次序排列。這意味著,即使你在查詢中只指定了state值,或者指定state和city值,MySQL也可以使用這個(gè)索引。因此,這個(gè)索引可以被用于搜索如下所示的數(shù)據(jù)列組合:state, city, zipstate, citystateMySQL不能利用這個(gè)索引來(lái)搜索沒(méi)有包含在最左前綴的內(nèi)容。例如,如果你按照city或zip來(lái)搜索,就不會(huì)使用到這個(gè)索引。

20、如果你搜索給定的state和具體的ZIP代碼(索引的1和3列,該索引也是不能用于這種組合值的,盡管MySQL可以利用索引來(lái)查找匹配的state 從而縮小搜索的范圍。不要過(guò)多地索引。不要認(rèn)為"索引越多,性能越高",不要對(duì)每個(gè)數(shù)據(jù)列都進(jìn)行索引。我們?cè)谇懊嫣岬竭^(guò),每個(gè)額外的索引都會(huì)花費(fèi)更多的磁盤空間,并降低寫操作的性能。當(dāng)你修改表的內(nèi)容的時(shí)候，索引就必須被更新，甚至可能重新整理。如果你的索引很少使用或永不 使用， 你就沒(méi)有必要減小表的修改操作的速度。 此外， 為檢索操作生成執(zhí)行計(jì)劃的時(shí)候， MySQL 會(huì)考慮索引。 建立額外的索引會(huì)給查詢優(yōu)化器增加更多的工作量。 如果索引太多，

21、有可能 （未 必）出現(xiàn) MySQL 選擇最優(yōu)索引失敗的情況。維護(hù)自己必須的索引可以幫助查詢優(yōu)化器來(lái)避免 這類錯(cuò)誤。 如果你考慮給已經(jīng)索引過(guò)的表添加索引，那么就要考慮你將增加的索引是否是已有的多 列索引的最左前綴。如果是這樣的，不用增加索引，因?yàn)橐呀?jīng)有了（例如，如果你在 state、 city 和 zip 上建立了索引，那么沒(méi)有必要再增加 state 的索引） 。 讓索引類型與你所執(zhí)行的比較的類型相匹配。在你建立索引的時(shí)候，大多數(shù)存儲(chǔ)引擎會(huì) 選擇它們將使用的索引實(shí)現(xiàn)。例如，InnoDB 通常使用 B 樹(shù)索引。MySQL 也使用 B 樹(shù)索引，它 只在三維數(shù)據(jù)類型上使用 R 樹(shù)索引。但是，MEMOR

22、Y 存儲(chǔ)引擎支持散列索引和 B 樹(shù)索引，并允 許你選擇使用哪種索引。為了選擇索引類型，需要考慮在索引數(shù)據(jù)列上將執(zhí)行的比較操作類 型： · 對(duì)于散列（hash）索引，會(huì)在每個(gè)數(shù)據(jù)列值上應(yīng)用散列函數(shù)。生成的結(jié)果散列值存儲(chǔ) 在索引中， 并用于執(zhí)行查詢。 散列函數(shù)實(shí)現(xiàn)的算法類似于為不同的輸入值生成不同的散列值。 使用散列值的好處是散列值比原始值的比較效率更高。散列索引用于執(zhí)行=或<=>操作等精確 匹配的時(shí)候速度非常快。但是對(duì)于查詢一個(gè)值的范圍效果就非常差了： id < 30 weight BETWEEN 100 AND 150 · B 樹(shù)索引可以用于高效率地執(zhí)行精確的或者基于范圍（使用操作<、<=、=、>=、>、