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

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

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

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

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

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

6、的鍵記錄（它用于快速地訪問數(shù)據(jù)文件數(shù)組。與此形成對照的是，BDB和InnoDB存儲引擎沒有使用這種方法來分離數(shù)據(jù)行 和索引值，盡管它們也把索引作為排序后的值集合進行操作。在默認情況下,BDB引擎使用單個文件存儲數(shù)據(jù)和索引值。InnoDB使用單個數(shù)據(jù)表空間（tablespace在表 空間中管理所有InnoDB表的數(shù)據(jù)和索引存儲。我們可以把InnoDB配置為每個表都在自己的表空間中創(chuàng)建，但是即使是這樣，數(shù)據(jù)表的數(shù)據(jù)和索引也存儲在同一個表 空間文件中前面的討論描述了單個表查詢環(huán)境下的索引的優(yōu)點，在這種情況下，通過減少對 整個表的掃描，使用索引明顯地提高了搜索的速度。當你運行涉及多表聯(lián)結(jié)(jion查詢

7、的時候 索引的價值就更高了。在單表查詢中，你需要在每個數(shù)據(jù)列上檢查的值的 數(shù)量是表中數(shù)據(jù)行的數(shù)量。在多表查詢中，這個數(shù)量可能大幅度上升，因為這個數(shù)量 是這些表中數(shù)據(jù)行的數(shù)量所產(chǎn)生的。假設(shè)你擁有三個未索引的表t1、t2和t3,每個表都分別包含數(shù)據(jù)列i1、i2和i3, 并且每個表都包含了 1000條數(shù)據(jù)行，其序號從1到1000。查找某些值匹配的數(shù)據(jù)行 組合的查詢可能如下所示：SELECT t1.i1, t2.i2, t3.i3FROM t1, t2, t3WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;這個查詢的結(jié)果應該是1000行，每個數(shù)據(jù)行包含三個相等的值。如果在沒

8、有索 引的情況下處理這個查詢，那么如果我們不對這些表進行全部地掃描，我們是沒有辦 法知道哪些數(shù)據(jù)行含有哪些值的。因此你必須嘗試所有的組合來查找符合WHERE條件的記錄。可能的組合的數(shù)量是 1000 x 1000 x 1000(10億!,它是匹配記錄的數(shù)量 的一百萬倍。這就浪費了大量的工作。這個例子顯示，如果沒有使用索引，隨著表的記錄不斷增長，處理這些表的聯(lián)結(jié)所花費的時間增長得更快，導致性能很差。我們可 以通過索引這些數(shù)據(jù)表來顯著地提高速度，因為索引讓查詢采用如下所示的方式來 處理：.選擇表t1中的第一行并查看該數(shù)據(jù)行的值。.使用表t2上的索引，直接定位到與t1的值匹配的數(shù)據(jù)行。類似地，使用表t

9、3上 的索引，直接定位到與表t2的值匹配的數(shù)據(jù)行。.處理表t1的下一行并重復前面的過程。執(zhí)行這樣的操作直到t1中的所有數(shù)據(jù)行都被檢查過。在這種#況下我們?nèi)匀粚Ρ韙1執(zhí)行了完整的掃描，但是我們可以在t2和t3上 執(zhí)行索引查找，從這些表中直接地獲取數(shù)據(jù)行。理論上采用這種方式運行上面的查 詢會快一百萬倍。當然這個例子是為了得出結(jié)論來人為建立的。然而，它解決的問題卻是現(xiàn)實的，給沒有索引的表添加索引通常會獲得驚人的性能提高。MySQL有幾種使用索引的方式：如上所述，索引被用于提高 WHERE條件的數(shù)據(jù)行匹配或者執(zhí)行聯(lián)結(jié)操作時匹 配其它表的數(shù)據(jù)行的搜索速度。對于使用了 MIN（或MAX（函數(shù)的查詢，索引數(shù)

10、據(jù)列中最小或最大值可以很快 地找到，不用檢查每個數(shù)據(jù)行。MySQL利用索引來快速地執(zhí)行 ORDER BY和GROUP BY語句的排序和分 組操作。有時候MySQL會利用索引來讀取查詢得到的所有信息。假設(shè)你選擇了 MylSAM表中的被索引的數(shù)值列，那么就不需要從該數(shù)據(jù)表中選擇其它的數(shù)據(jù)列。 在這種情況下，MySQL從索引文件中讀取索引值，它所得到的值與讀取數(shù)據(jù)文件得到 的值是相同的。沒有必要兩次讀取相同的值，因此沒有必要考慮數(shù)據(jù)文件。索引的代價一般來說，如果MySQL能夠找到方法，利用索引來更快地處理查詢，它就會這樣 做。這意味著，對于大多數(shù)情況，如果你沒有對表進行索引，就會使性能受到損害。這

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

12、空間。這可能導致 更快地到達數(shù)據(jù)表的大小限制：對于MylSAM表，頻繁地索引可能引起索引文件比數(shù)據(jù)文件更快地達到最大限 制。對于BDB表，它把數(shù)據(jù)和索引值一起存儲在同一個文件中，添加索引引起這種 表更快地達到最大文件限制。在InnoDB的共享表空間中分配的所有表都競爭使用相同的公共空間池 ，因此 添加索引會更快地耗盡表空間中的存儲。但是 ，與MylSAM和BDB表使用的文件 不同,InnoDB共享表空間并不受操作系統(tǒng)的文件大小限制，因為我們可以把它配置成 使用多個文件。只要有額外的磁盤空間，你就可以通過添加新組件來擴展表空問。使用單獨表空間的InnoDB表與BDB表受到的約束是一樣的，因為它的

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

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

15、所包含的不同值的數(shù)量。例如 ，某 個數(shù)據(jù)列包含值1、3、7、4、7、3,那么它的基數(shù)就是4。索引的基數(shù)相對于數(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)化器更加復雜，把其它一些因素也考

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

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

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

19、列的前面幾個字符作為索引值。假設(shè)你在表的state city和zip數(shù)據(jù)列上建立了復合索引。索引中的數(shù)據(jù)行按 照state/city/zip次序排列，因此它們也會自動地按照state/city和state次序排列。這 意味著，即使你在查詢中只指定了 state值，或者指定state和city值,MySQL也可以使 用這個索引。因此，這個索引可以被用于搜索如下所示的數(shù)據(jù)列組合 ：state, city, zipstate, citystateMySQL不能利用這個索引來搜索沒有包含在最左前綴的內(nèi)容。例如，如果你按照city或zip來搜索，就不會使用到這個索引。如果你搜索給定的state和具體的Z

20、IP代碼（索引的1和3列,該索引也是不能用于這種組合值的，盡管MySQL可以利用索 引來查找匹配的state從而縮小搜索的范圍。不要過多地索引。不要認為索引越多，性能越高”，不要對每個數(shù)據(jù)列都進行索 引。我們在前面提到過，每個額外的索引都會花費更多的磁盤空間，并降低寫操作的 性能。當你修改表的內(nèi)容的時候，索引就必須被更新，甚至可能重新整理。如果你的索引很少使用或永不 使用，你就沒有必要減小表的修改操作的速度。止匕外，為檢索操作生成執(zhí)行計劃的時候，MySQL會考慮索引。建立額外的索引會給查詢優(yōu)化器增 加更多的工作量。如果索引太多，有可能（未必）出現(xiàn)MySQL選擇最優(yōu)索引失 敗的情況。維護自己必須

21、的索引可以幫助查詢優(yōu)化器來避免這類錯誤。如果你考慮給已經(jīng)索引過的表添加索引，那么就要考慮你將增加的索引是否是已有的多列索引的最左前綴。如果是這樣的，不用增加索引，因為已經(jīng)有了（例如，如果你在state city和zip上建立了索引，那么沒有必要再增加 state的索引）。讓索引類 型與你所執(zhí)行的比較的類型相匹配。在你建立索引的時候，大多數(shù)存儲引擎會選擇它們將使用的索引實現(xiàn)。例如，InnoDB通常使用B樹索引。MySQL也使用B 樹索引，它只在三維數(shù)據(jù)類型上使用 R樹索引。但是，MEMORY存儲引擎支持 散列索引和B樹索引，并允 許你選擇使用哪種索引。為了選擇索引類型，需要考 慮在索引數(shù)據(jù)列上將執(zhí)行的比較操作類 型：對于散列（hash）索引，會在每個數(shù)據(jù)列值上應用散列函數(shù)。生成的結(jié)果散列值存儲 在索引中，并用于執(zhí)行查詢。散 列函數(shù)實現(xiàn)的算法類似于為不同的輸入值生成不同的散列值。使用散列值的好處是散列值比原始值的比較效率更高。散列索引用于執(zhí)行=或=操作等精確匹配的 時候速度非?？?。但是對于查詢一個值的范圍效果就非常差了：id 30 weightBETWEEN