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

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

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

3、表中的每個數(shù)據(jù)行看它是否與目標值相匹配。這會導致一次完全的數(shù)據(jù)表掃描 ,這個過程會很慢 ,如果這個表很大 ,但是只包含少量的符合條件的記錄 ,那么效率會非常低。圖 1:無索引的 ad 表圖 2 是同樣的一張數(shù)據(jù)表 ,但是增加了對 ad表的 company_num數(shù)據(jù)列的索引。這個索引包含了 ad 表中的每個數(shù)據(jù)行的條目 ,但是索引的條目是按照 company_num值排序的?，F(xiàn)在 ,我們不是逐行查看以搜尋匹配的數(shù)據(jù)項,而是使用索引。假設我們查找公司 13 的所有數(shù)據(jù)行。我們開始掃描索引并找到了該公司的三個值。接著我們碰到了公司 14 的索引值 ,它比我們正在搜尋的值大。索引值是排過序的 ,因此

4、當我們讀取了包含 14 的索引記錄的時候 ,我們就知道再也不會有更多的匹配記錄 ,可以結束查詢操作了。因此使用索引獲得的功效是 :我們找到了匹配的數(shù)據(jù)行在哪兒終止 ,并能夠忽略其它的數(shù)據(jù)行。另一個功效來自使用定位算法查找第一條匹配的條目 , 而不需要從索引頭開始執(zhí)行線性掃描 (例如 ,二分搜索就比線性掃描要快一些。通過使用這種方法 ,我們可以快速地定位第一個匹配的值 ,節(jié)省了大量的搜索時間。數(shù)據(jù)庫使用了多種技術來快速地定位索引值 ,但是在本文中我們不關心這些技術。重點是它們能夠實現(xiàn) ,并且索引是個好東西。圖 2:索引后的 ad 表你可能要問 ,我們?yōu)槭裁床粚?shù)據(jù)行進行排序從而省掉索引 ?這樣不

5、是也能實現(xiàn)同樣的搜索速度的改善嗎 ?是的 ,如果表只有一個索引 ,這樣做也可能達到相同的效果。但是你可能添加第二個索引 ,那么就無法一次使用兩種不同方法對數(shù)據(jù)行進行排序了 (例如 ,你可能希望在顧客名稱上建立一個索引 ,在顧客 ID 號或電話號碼上建立另外一個索引。把與數(shù)據(jù)行相分離的條目作為索引解決了這個問題 ,允許我們創(chuàng)建多個索引。此外 ,索引中的行一般也比數(shù)據(jù)行短一些。當你插入或刪除新的值的時候 ,移動較短的索引值比移動較長數(shù)據(jù)行的排序次序更加容易。不同的 MySQL 存儲引擎的索引實現(xiàn)的具體細節(jié)信息是不同的。例如,對于MyISAM 數(shù)據(jù)表 ,該表的數(shù)據(jù)行保存在一個數(shù)據(jù)文件中,索引值保存在

6、索引文件中。一個數(shù)據(jù)表上可能有多個索引 ,但是它們都被存儲在同一個索引文件中。索引文件中的每個索引都包含一個排序的鍵記錄 (它用于快速地訪問數(shù)據(jù)文件數(shù)組。與此形成對照的是 ,BDB 和 InnoDB 存儲引擎沒有使用這種方法來分離數(shù)據(jù)行和索引值 ,盡管它們也把索引作為排序后的值集合進行操作。在默認情況下,BDB 引擎使用單個文件存儲數(shù)據(jù)和索引值。 InnoDB 使用單個數(shù)據(jù)表空間 (tablespace,在表空間中管理所有 InnoDB 表的數(shù)據(jù)和索引存儲。我們可以把 InnoDB 配置為每個表都在自己的表空間中創(chuàng)建 ,但是即使是這樣 ,數(shù)據(jù)表的數(shù)據(jù)和索引也存儲在同一個表空間文件中。前面的討論

7、描述了單個表查詢環(huán)境下的索引的優(yōu)點 ,在這種情況下 ,通過減少對整個表的掃描 ,使用索引明顯地提高了搜索的速度。當你運行涉及多表聯(lián)結 (jion 查詢的時候 ,索引的價值就更高了。在單表查詢中 ,你需要在每個數(shù)據(jù)列上檢查的值的數(shù)量是表中數(shù)據(jù)行的數(shù)量。在多表查詢中 ,這個數(shù)量可能大幅度上升 ,因為這個數(shù)量是這些表中數(shù)據(jù)行的數(shù)量所產(chǎn)生的。假設你擁有三個未索引的表 t1、t2 和 t3,每個表都分別包含數(shù)據(jù)列 i1、 i2 和 i3, 并且每個表都包含了 1000 條數(shù)據(jù)行 ,其序號從 1 到 1000。查找某些值匹配的數(shù)據(jù)行組合的查詢可能如下所示 :SELECT t1.i1, t2.i2, t3.

8、i3FROM t1, t2, t3WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;這個查詢的結果應該是1000 行 ,每個數(shù)據(jù)行包含三個相等的值。如果在沒有索引的情況下處理這個查詢,那么如果我們不對這些表進行全部地掃描,我們是沒有辦法知道哪些數(shù)據(jù)行含有哪些值的。因此你必須嘗試所有的組合來查找符合WHERE條件的記錄。可能的組合的數(shù)量是1000 x 1000 x 1000(10億!,它是匹配記錄的數(shù)量的一百萬倍。這就浪費了大量的工作。這個例子顯示,如果沒有使用索引 ,隨著表的記錄不斷增長 ,處理這些表的聯(lián)結所花費的時間增長得更快,導致性能很差。我們可以通過索引這些數(shù)據(jù)

9、表來顯著地提高速度,因為索引讓查詢采用如下所示的方式來處理 :1.選擇表 t1 中的第一行并查看該數(shù)據(jù)行的值。2.使用表 t2 上的索引 ,直接定位到與 t1 的值匹配的數(shù)據(jù)行。類似地,使用表 t3 上的索引 ,直接定位到與表t2 的值匹配的數(shù)據(jù)行。3.處理表 t1 的下一行并重復前面的過程。執(zhí)行這樣的操作直到t1 中的所有數(shù)據(jù)行都被檢查過。在這種情況下 ,我們仍然對表 t1 執(zhí)行了完整的掃描 ,但是我們可以在 t2 和 t3 上執(zhí)行索引查找 ,從這些表中直接地獲取數(shù)據(jù)行。理論上采用這種方式運行上面的查詢會快一百萬倍。當然這個例子是為了得出結論來人為建立的。然而 ,它解決的問題卻是現(xiàn)實的 ,給

10、沒有索引的表添加索引通常會獲得驚人的性能提高。MySQL 有幾種使用索引的方式:·如上所述 ,索引被用于提高 WHERE 條件的數(shù)據(jù)行匹配或者執(zhí)行聯(lián)結操作時匹配其它表的數(shù)據(jù)行的搜索速度。·對于使用了 MIN( 或 MAX( 函數(shù)的查詢 ,索引數(shù)據(jù)列中最小或最大值可以很快地找到 ,不用檢查每個數(shù)據(jù)行。·MySQL 利用索引來快速地執(zhí)行 ORDER BY 和 GROUP BY 語句的排序和分組操作。·有時候 MySQL 會利用索引來讀取查詢得到的所有信息。假設你選擇了 MyISAM 表中的被索引的數(shù)值列 ,那么就不需要從該數(shù)據(jù)表中選擇其它的數(shù)據(jù)列。在這種情況

11、下 ,MySQL 從索引文件中讀取索引值 ,它所得到的值與讀取數(shù)據(jù)文件得到的值是相同的。沒有必要兩次讀取相同的值 ,因此沒有必要考慮數(shù)據(jù)文件。索引的代價一般來說 ,如果 MySQL 能夠找到方法 ,利用索引來更快地處理查詢 ,它就會這樣做。這意味著 ,對于大多數(shù)情況 ,如果你沒有對表進行索引 ,就會使性能受到損害。這就是我所描繪的索引優(yōu)點的美景。但是它有缺點嗎 ?有的 ,它在時間和空間上都有開銷。在實踐中 ,索引的優(yōu)點的價值一般會超過這些缺點 ,但是你也應該知道到底有一些什么缺點。首先 ,索引加快了檢索的速度 ,但是減慢了插入和刪除的速度 ,同時還減慢了更新被索引的數(shù)據(jù)列中的值的速度。也就是說

12、 ,索引減慢了大多數(shù)涉及寫操作的速度。發(fā)生這種現(xiàn)象的原因在于寫入一條記錄的時候不但需要寫入數(shù)據(jù)行 ,還需要改變所有的索引。數(shù)據(jù)表帶有的索引越多 ,需要做出的修改就越多 ,平均性能的降低程度也就越大。在本文的 "高效率載入數(shù)據(jù) "部分中 ,我們將更細致地了解這些現(xiàn)象并找出處理方法。其次 ,索引會花費磁盤空間 ,多個索引相應地花費更多的磁盤空間。這可能導致更快地到達數(shù)據(jù)表的大小限制 :·對于 MyISAM 表,頻繁地索引可能引起索引文件比數(shù)據(jù)文件更快地達到最大限制。·對于 BDB 表 ,它把數(shù)據(jù)和索引值一起存儲在同一個文件中表更快地達到最大文件限制。,添加索

13、引引起這種·在 InnoDB 的共享表空間中分配的所有表都競爭使用相同的公共空間池 ,因此添加索引會更快地耗盡表空間中的存儲。但是 ,與 MyISAM 和 BDB 表使用的文件不同 ,InnoDB 共享表空間并不受操作系統(tǒng)的文件大小限制 ,因為我們可以把它配置成使用多個文件。只要有額外的磁盤空間 ,你就可以通過添加新組件來擴展表空間。使用單獨表空間的 InnoDB 表與 BDB 表受到的約束是一樣的 ,因為它的數(shù)據(jù)和索引值都存儲在單個文件中。這些要素的實際含義是 :如果你不需要使用特殊的索引幫助查詢執(zhí)行得更快 ,就不要建立索引。選擇索引假設你已經(jīng)知道了建立索引的語法 ,但是語法不會告

14、訴你數(shù)據(jù)表應該如何索引。這要求我們考慮數(shù)據(jù)表的使用方式。這一部分指導你如何識別出用于索引的備選數(shù)據(jù)列 ,以及如何最好地建立索引 :用于搜索、排序和分組的索引數(shù)據(jù)列并不僅僅是用于輸出顯示的。換句話說,用于索引的最好的備選數(shù)據(jù)列是那些出現(xiàn)在WHERE 子句、 join 子句、 ORDER BY或 GROUP BY 子句中的列。僅僅出現(xiàn)在 SELECT 關鍵字后面的輸出數(shù)據(jù)列列表中的數(shù)據(jù)列不是很好的備選列 : SELECTcol_a <- 不是備選列FROMtbl1 LEFT JOIN tbl2ON tbl1.col_b = tbl2.col_c <- 備選列WHEREcol_d = e

15、xpr; <- 備選列當然 ,顯示的數(shù)據(jù)列與WHERE 子句中使用的數(shù)據(jù)列也可能相同。我們的觀點是輸出列表中的數(shù)據(jù)列本質上不是用于索引的很好的備選列。Join 子句或 WHERE 子句中類似 col1 = col2 形式的表達式中的數(shù)據(jù)列都是特別好的索引備選列。前面顯示的查詢中的col_b 和 col_c 就是這樣的例子。如果MySQL 能夠利用聯(lián)結列來優(yōu)化查詢,它一定會通過減少整表掃描來大幅度減少潛在的表 -行組合?？紤]數(shù)據(jù)列的基數(shù) (cardinality?；鶖?shù)是數(shù)據(jù)列所包含的不同值的數(shù)量。例如 ,某個數(shù)據(jù)列包含值 1、 3、 7、 4、 7、 3,那么它的基數(shù)就是 4。索引的基數(shù)相

16、對于數(shù)據(jù)表行數(shù)較高 (也就是說 ,列中包含很多不同的值 ,重復的值很少的時候 ,它的工作效果最好。如果某數(shù)據(jù)列含有很多不同的年齡,索引會很快地分辨數(shù)據(jù)行。如果某個數(shù)據(jù)列用于記錄性別 (只有 "M" 和"F" 兩種值 ,那么索引的用處就不大。如果值出現(xiàn)的幾率幾乎相等 ,那么無論搜索哪個值都可能得到一半的數(shù)據(jù)行。在這些情況下 ,最好根本不要使用索引 ,因為查詢優(yōu)化器發(fā)現(xiàn)某個值出現(xiàn)在表的數(shù)據(jù)行中的百分比很高的時候 ,它一般會忽略索引 ,進行全表掃描。慣用的百分比界線是 "30%"?，F(xiàn)在查詢優(yōu)化器更加復雜 ,把其它一些因素也考慮進去了 ,因此

17、這個百分比并不是 MySQL 決定選擇使用掃描還是索引的唯一因素。索引較短的值。盡可能地使用較小的數(shù)據(jù)類型。例如 ,如果 MEDIUMINT 足夠保存你需要存儲的值 ,就不要使用 BIGINT 數(shù)據(jù)列。如果你的值不會長于 25 個字符 , 就不要使用 CHAR(100 。較小的值通過幾個方面改善了索引的處理速度 :·較短的值可以更快地進行比較,因此索引的查找速度更快了。·較小的值導致較小的索引,需要更少的磁盤I/O。·使用較短的鍵值的時候 ,鍵緩存中的索引塊 (block 可以保存更多的鍵值。 MySQL 可以在內存中一次保持更多的鍵 ,在不需要從磁盤讀取額外的索

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

19、 10 個或 20 個字符都不同 ,就不要索引整個數(shù)據(jù)列。索引前面 10 個或 20 個字符會節(jié)省大量的空間 ,并且可能使你的查詢速度更快。通過索引較短的值 ,你可以獲得那些與比較速度和磁盤 I/O 節(jié)省相關的好處。當然你也需要利用常識。僅僅索引某個數(shù)據(jù)列的第一個字符串可能用處不大 ,因為如果這樣操作 ,那么在索引中不會有太多的唯一值。你可以索引 CHAR 、 VARCHAR 、BINARY 、 VARBINARY 、BLOB 和 TEXT 數(shù)據(jù)列的前綴。使用最左 (leftmost 前綴。建立多列復合索引的時候 ,你實際上建立了 MySQL 可以使用的多個索引。復合索引可以作為多個索引使用

20、,因為索引中最左邊的列集合都可以用于匹配數(shù)據(jù)行。這種列集合被稱為 "最左前綴 "(它與索引某個列的前綴不同 ,那種索引把某個列的前面幾個字符作為索引值。假設你在表的 state、city 和 zip 數(shù)據(jù)列上建立了復合索引。索引中的數(shù)據(jù)行按照 state/city/zip 次序排列 ,因此它們也會自動地按照 state/city 和 state次序排列。這意味著 ,即使你在查詢中只指定了 state值,或者指定 state和 city 值,MySQL 也可以使用這個索引。因此 ,這個索引可以被用于搜索如下所示的數(shù)據(jù)列組合 :state, city, zipstate, ci

21、tystateMySQL不能利用這個索引來搜索沒有包含在最左前綴的內容。例如,如果你按照 city 或 zip 來搜索 ,就不會使用到這個索引。如果你搜索給定的state和具體的 ZIP代碼 (索引的 1 和 3 列,該索引也是不能用于這種組合值的,盡管MySQL 可以利用索引來查找匹配的state 從而縮小搜索的范圍。不要過多地索引。不要認為 "索引越多 ,性能越高 ",不要對每個數(shù)據(jù)列都進行索引。我們在前面提到過 ,每個額外的索引都會花費更多的磁盤空間 ,并降低寫操作的性能。當你修改表的內容的時候，索引就必須被更新，甚至可能重新整理。如果你的索引很少使用或永不 使用，

22、你就沒有必要減小表的修改操作的速度。 此外， 為檢索操作生成執(zhí)行計劃的時候， MySQL 會考慮索引。 建立額外的索引會給查詢優(yōu)化器增加更多的工作量。 如果索引太多， 有可能 （未 必）出現(xiàn) MySQL 選擇最優(yōu)索引失敗的情況。維護自己必須的索引可以幫助查詢優(yōu)化器來避免這類錯誤。 如果你考慮給已經(jīng)索引過的表添加索引，那么就要考慮你將增加的索引是否是已有的多 列索引的最左前綴。如果是這樣的，不用增加索引，因為已經(jīng)有了（例如，如果你在state、city和 zip 上建立了索引，那么沒有必要再增加state的索引） 。 讓索引類型與你所執(zhí)行的比較的類型相匹配。在你建立索引的時候，大多數(shù)存儲引擎會選

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