MySQL查詢索引的正確使用

圖1：無索引的ad表

圖2：索引後的ad表<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPC90ZD4KPC90cj4KPC90Ym9keT4KPC90YWJsZT4KPC9jZW50ZXI+CjxwIGNsYXNzPQ=="western" align="left">
　　你可能要問，我們為什麼不對數據行進行排序從而省掉索引？這樣不是也能實現同樣的搜索速度的改善嗎？是的，如果表只有一個索引，這樣做也可能達到相同的效果。但是你可能添加第二個索引，那麼就無法一次使用兩種不同方法對數據行進行排序了（例如，你可能希望在顧客名稱上建立一個索引，在顧客ID號或電話號碼上建立另外一個索引）。把與數據行相分離的條目作為索引解決了這個問題，允許我們創建多個索引。此外，索引中的行一般也比數據行短一些。當你插入或刪除新的值的時候，移動較短的索引值比移動較長數據行的排序次序更加容易。

　　不同的MySQL存儲引擎的索引實現的具體細節信息是不同的。例如，對於MyISAM數據表，該表的數據行保存在一個數據文件中，索引值保存在索引文件中。一個數據表上可能有多個索引，但是它們都被存儲在同一個索引文件中。索引文件中的每個索引都包含一個排序的鍵記錄（它用於快速地訪問數據文件）數組。

　　與此形成對照的是，BDB和InnoDB存儲引擎沒有使用這種方法來分離數據行和索引值，盡管它們也把索引作為排序後的值集合進行操作。在默認情況下，BDB引擎使用單個文件存儲數據和索引值。InnoDB使用單個數據表空間（tablespace），在表空間中管理所有InnoDB表的數據和索引存儲。我們可以把InnoDB配置為每個表都在自己的表空間中創建，但是即使是這樣，數據表的數據和索引也存儲在同一個表空間文件中。
前面的討論描述了單個表查詢環境下的索引的優點，在這種情況下，通過減少對整個表的掃描，使用索引明顯地提高了搜索的速度。當你運行涉及多表聯結（jion）查詢的時候，索引的價值就更高了。在單表查詢中，你需要在每個數據列上檢查的值的數量是表中數據行的數量。在多表查詢中，這個數量可能大幅度上升，因為這個數量是這些表中數據行的數量所產生的。

　　假設你擁有三個未索引的表t1、t2和t3，每個表都分別包含數據列i1、i2和i3，並且每個表都包含了1000條數據行，其序號從1到1000。查找某些值匹配的數據行組合的查詢可能如下所示：

　　這個查詢的結果應該是1000行，每個數據行包含三個相等的值。如果在沒有索引的情況下處理這個查詢，那麼如果我們不對這些表進行全部地掃描，我們是沒有辦法知道哪些數據行含有哪些值的。因此你必須嘗試所有的組合來查找符合WHERE條件的記錄。可能的組合的數量是1000x 1000 x1000（10億！），它是匹配記錄的數量的一百萬倍。這就浪費了大量的工作。這個例子顯示，如果沒有使用索引，隨著表的記錄不斷增長，處理這些表的聯結所花費的時間增長得更快，導致性能很差。我們可以通過索引這些數據表來顯著地提高速度，因為索引讓查詢采用如下所示的方式來處理：

　　1．選擇表t1中的第一行並查看該數據行的值。

　　2．使用表t2上的索引，直接定位到與t1的值匹配的數據行。類似地，使用表t3上的索引，直接定位到與表t2的值匹配的數據行。

　　3．處理表t1的下一行並重復前面的過程。執行這樣的操作直到t1中的所有數據行都被檢查過。

　　在這種情況下，我們仍然對表t1執行了完整的掃描，但是我們可以在t2和t3上執行索引查找，從這些表中直接地獲取數據行。理論上采用這種方式運行上面的查詢會快一百萬倍。當然這個例子是為了得出結論來人為建立的。然而，它解決的問題卻是現實的，給沒有索引的表添加索引通常會獲得驚人的性能提高。

　　MySQL有幾種使用索引的方式：

　　·如上所述，索引被用於提高WHERE條件的數據行匹配或者執行聯結操作時匹配其它表的數據行的搜索速度。

　　·對於使用了MIN()或MAX()函數的查詢，索引數據列中最小或最大值可以很快地找到，不用檢查每個數據行。

　　·MySQL利用索引來快速地執行ORDERBY和GROUPBY語句的排序和分組操作。

　　·有時候MySQL會利用索引來讀取查詢得到的所有信息。假設你選擇了MyISAM表中的被索引的數值列，那麼就不需要從該數據表中選擇其它的數據列。在這種情況下，MySQL從索引文件中讀取索引值，它所得到的值與讀取數據文件得到的值是相同的。沒有必要兩次讀取相同的值，因此沒有必要考慮數據文件。

　索引的代價

　　一般來說，如果MySQL能夠找到方法，利用索引來更快地處理查詢，它就會這樣做。這意味著，對於大多數情況，如果你沒有對表進行索引，就會使性能受到損害。這就是我所描繪的索引優點的美景。但是它有缺點嗎？有的，它在時間和空間上都有開銷。在實踐中，索引的優點的價值一般會超過這些缺點，但是你也應該知道到底有一些什麼缺點。

　　首先，索引加快了檢索的速度，但是減慢了插入和刪除的速度，同時還減慢了更新被索引的數據列中的值的速度。也就是說，索引減慢了大多數涉及寫操作的速度。發生這種現象的原因在於寫入一條記錄的時候不但需要寫入數據行，還需要改變所有的索引。數據表帶有的索引越多，需要做出的修改就越多，平均性能的降低程度也就越大。在本文的"高效率載入數據"部分中，我們將更細致地了解這些現象並找出處理方法。

　　其次，索引會花費磁盤空間，多個索引相應地花費更多的磁盤空間。這可能導致更快地到達數據表的大小限制：

　　·對於MyISAM表，頻繁地索引可能引起索引文件比數據文件更快地達到最大限制。

　　·對於BDB表，它把數據和索引值一起存儲在同一個文件中，添加索引引起這種表更快地達到最大文件限制。

　　·在InnoDB的共享表空間中分配的所有表都競爭使用相同的公共空間池，因此添加索引會更快地耗盡表空間中的存儲。但是，與MyISAM和BDB表使用的文件不同，InnoDB共享表空間並不受操作系統的文件大小限制，因為我們可以把它配置成使用多個文件。只要有額外的磁盤空間，你就可以通過添加新組件來擴展表空間。

　　使用單獨表空間的InnoDB表與BDB表受到的約束是一樣的，因為它的數據和索引值都存儲在單個文件中。

　　這些要素的實際含義是：如果你不需要使用特殊的索引幫助查詢執行得更快，就不要建立索引。

　　選擇索引

　　假設你已經知道了建立索引的語法，但是語法不會告訴你數據表應該如何索引。這要求我們考慮數據表的使用方式。這一部分指導你如何識別出用於索引的備選數據列，以及如何最好地建立索引：

　　用於搜索、排序和分組的索引數據列並不僅僅是用於輸出顯示的。換句話說，用於索引的最好的備選數據列是那些出現在WHERE子句、join子句、ORDERBY或GROUPBY子句中的列。僅僅出現在SELECT關鍵字後面的輸出數據列列表中的數據列不是很好的備選列：

　　當然，顯示的數據列與WHERE子句中使用的數據列也可能相同。我們的觀點是輸出列表中的數據列本質上不是用於索引的很好的備選列。

　　Join子句或WHERE子句中類似col1=col2形式的表達式中的數據列都是特別好的索引備選列。前面顯示的查詢中的col_b和col_c就是這樣的例子。如果MySQL能夠利用聯結列來優化查詢，它一定會通過減少整表掃描來大幅度減少潛在的表－行組合。

　　考慮數據列的基數（cardinality）。基數是數據列所包含的不同值的數量。例如，某個數據列包含值1、3、7、4、7、3，那麼它的基數就是4。索引的基數相對於數據表行數較高（也就是說，列中包含很多不同的值，重復的值很少）的時候，它的工作效果最好。如果某數據列含有很多不同的年齡，索引會很快地分辨數據行。如果某個數據列用於記錄性別（只有"M"和"F"兩種值），那麼索引的用處就不大。如果值出現的幾率幾乎相等，那麼無論搜索哪個值都可能得到一半的數據行。在這些情況下，最好根本不要使用索引，因為查詢優化器發現某個值出現在表的數據行中的百分比很高的時候，它一般會忽略索引，進行全表掃描。慣用的百分比界線是"30%"。現在查詢優化器更加復雜，把其它一些因素也考慮進去了，因此這個百分比並不是MySQL決定選擇使用掃描還是索引的唯一因素。

　　索引較短的值。盡可能地使用較小的數據類型。例如，如果MEDIUMINT足夠保存你需要存儲的值，就不要使用BIGINT數據列。如果你的值不會長於25個字符，就不要使用CHAR(100)。較小的值通過幾個方面改善了索引的處理速度：

　　·較短的值可以更快地進行比較，因此索引的查找速度更快了。

　　·較小的值導致較小的索引，需要更少的磁盤I/O。

　　·使用較短的鍵值的時候，鍵緩存中的索引塊（block）可以保存更多的鍵值。MySQL可以在內存中一次保持更多的鍵，在不需要從磁盤讀取額外的索引塊的情況下，提高鍵值定位的可能性。

　　對於InnoDB和BDB等使用聚簇索引（clusteredindex）的存儲引擎來說，保持主鍵（primarykey）短小的優勢更突出。聚簇索引中數據行和主鍵值存儲在一起（聚簇在一起）。其它的索引都是次級索引；它們存儲主鍵值和次級索引值。次級索引屈從主鍵值，它們被用於定位數據行。這暗示主鍵值都被復制到每個次級索引中，因此如果主鍵值很長，每個次級索引就需要更多的額外空間。

　　索引字符串值的前綴（prefixe）。如果你需要索引一個字符串數據列，那麼最好在任何適當的情況下都應該指定前綴長度。例如，如果有CHAR(200)數據列，如果前面10個或20個字符都不同，就不要索引整個數據列。索引前面10個或20個字符會節省大量的空間，並且可能使你的查詢速度更快。通過索引較短的值，你可以獲得那些與比較速度和磁盤I/O節省相關的好處。當然你也需要利用常識。僅僅索引某個數據列的第一個字符串可能用處不大，因為如果這樣操作，那麼在索引中不會有太多的唯一值。

　　你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT數據列的前綴。

　　使用最左（leftmost）前綴。建立多列復合索引的時候，你實際上建立了MySQL可以使用的多個索引。復合索引可以作為多個索引使用，因為索引中最左邊的列集合都可以用於匹配數據行。這種列集合被稱為"最左前綴"（它與索引某個列的前綴不同，那種索引把某個列的前面幾個字符作為索引值）。

　　假設你在表的state、city和zip數據列上建立了復合索引。索引中的數據行按照state/city/zip次序排列，因此它們也會自動地按照state/city和state次序排列。這意味著，即使你在查詢中只指定了state值，或者指定state和city值，MySQL也可以使用這個索引。因此，這個索引可以被用於搜索如下所示的數據列組合：

　　MySQL不能利用這個索引來搜索沒有包含在最左前綴的內容。例如，如果你按照city或zip來搜索，就不會使用到這個索引。如果你搜索給定的state和具體的ZIP代碼（索引的1和3列），該索引也是不能用於這種組合值的，盡管MySQL可以利用索引來查找匹配的state從而縮小搜索的范圍。

　　不要過多地索引。不要認為"索引越多，性能越高"，不要對每個數據列都進行索引。我們在前面提到過，每個額外的索引都會花費更多的磁盤空間，並降低寫操作的性能。當你修改表的內容的時候，索引就必須被更新，甚至可能重新整理。如果你的索引很少使用或永不使用，你就沒有必要減小表的修改操作的速度。此外，為檢索操作生成執行計劃的時候，MySQL會考慮索引。建立額外的索引會給查詢優化器增加更多的工作量。如果索引太多，有可能（未必）出現MySQL選擇最優索引失敗的情況。維護自己必須的索引可以幫助查詢優化器來避免這類錯誤。

　　如果你考慮給已經索引過的表添加索引，那麼就要考慮你將增加的索引是否是已有的多列索引的最左前綴。如果是這樣的，不用增加索引，因為已經有了（例如，如果你在state、city和zip上建立了索引，那麼沒有必要再增加state的索引）。

　　讓索引類型與你所執行的比較的類型相匹配。在你建立索引的時候，大多數存儲引擎會選擇它們將使用的索引實現。例如，InnoDB通常使用B樹索引。MySQL也使用B樹索引，它只在三維數據類型上使用R樹索引。但是，MEMORY存儲引擎支持散列索引和B樹索引，並允許你選擇使用哪種索引。為了選擇索引類型，需要考慮在索引數據列上將執行的比較操作類型：

　　·對於散列（hash）索引，會在每個數據列值上應用散列函數。生成的結果散列值存儲在索引中，並用於執行查詢。散列函數實現的算法類似於為不同的輸入值生成不同的散列值。使用散列值的好處是散列值比原始值的比較效率更高。散列索引用於執行=或<=>操作等精確匹配的時候速度非常快。但是對於查詢一個值的范圍效果就非常差了：

　　·B樹索引可以用於高效率地執行精確的或者基於范圍（使用操作<、<=、=、>=、>、<>、!=和BETWEEN）的比較。B樹索引也可以用於LIKE模式匹配，前提是該模式以文字串而不是通配符開頭。

　　如果你使用的MEMORY數據表只進行精確值查詢，散列索引是很好的選擇。這是MEMORY表使用的默認的索引類型，因此你不需要特意指定。如果你希望在MEMORY表上執行基於范圍的比較，應該使用B樹索引。為了指定這種索引類型，需要給索引定義添加USINGBTREE。例如：

　　如果你希望執行的語句的類型允許，單個MEMORY表可以同時擁有散列索引和B樹索引，即使在同一個數據列上。

　　有些類型的比較不能使用索引。如果你只是通過把值傳遞到函數（例如STRCMP()）中來執行比較操作，那麼對它進行索引就沒有價值。服務器必須計算出每個數據行的函數值，它會排除數據列上索引的使用。

　　使用慢查詢（slow-query）日志來識別執行情況較差的查詢。這個日志可以幫助你找出從索引中受益的查詢。你可以直接查看日志（它是文本文件），或者使用mysqldumpslow工具來統計它的內容。如果某個給定的查詢多次出現在"慢查詢"日志中，這就是一個線索，某個查詢可能沒有優化編寫。你可以重新編寫它，使它運行得更快。你要記住，在評估"慢查詢"日志的時候，"慢"是根據實際時間測定的，在負載較大的服務器上"慢查詢"日志中出現的查詢會多一些。