索引是提高查詢速度的最重要的工具。當然還有其它的一些技術可供使用，但是一般來說引起最大性能差異的都是索引的正確使用。在mysql郵件列表中，人們經常詢問那些讓查詢運行得更快的方法。在大多數情況下，我們應該懷疑數據表上有沒有索引，並且通常在添加索引之後立即解決了問題。當然，並不總是這樣簡單就可以解決問題的，因為優化技術本來就並非總是簡單的。然而，如果沒有使用索引，在很多情況下，你試圖使用其它的方法來提高性能都是在浪費時間。首先使用索引來獲取最大的性能提高，接著再看其它的技術是否有用。 這一部分講述了索引是什麼以及索引是怎麼樣提高查詢性能的。它還討論了在某些環境中索引可能降低性能，並為你明智地選擇數據表的索引提供了一些指導方針。在下一部分中我們將討論MySQL查詢優化器，它試圖找到執行查詢的效率最高的方法。了解一些優化器的知識，作為對如何建立索引的補充，對我們是有好處的，因為這樣你才能更好地利用自己所建立的索引。某些編寫查詢的方法實際上讓索引不起作用，在一般情況下你應該避免這種情形的發生。
索引的優點 讓我們開始了解索引是如何工作的，首先有一個不帶索引的數據表。不帶索引的表僅僅是一個無序的數據行集合。例如，圖1顯示的ad表就是不帶索引的表，因此如果需要查找某個特定的公司，就必須檢查表中的每個數據行看它是否與目標值相匹配。這會導致一次完全的數據表掃描，這個過程會很慢，如果這個表很大，但是只包含少量的符合條件的記錄，那麼效率會非常低。 [[The No.1 Picture.]] 圖1：無索引的ad表 圖2是同樣的一張數據表，但是增加了對ad表的company_num數據列的索引。這個索引包含了ad表中的每個數據行的條目，但是索引的條目是按照company_num值排序的。現在，我們不是逐行查看以搜尋匹配的數據項，而是使用索引。假設我們查找公司13的所有數據行。我們開始掃描索引並找到了該公司的三個值。接著我們碰到了公司14的索引值，它比我們正在搜尋的值大。索引值是排過序的，因此當我們讀取了包含14的索引記錄的時候，我們就知道再也不會有更多的匹配記錄，可以結束查詢操作了。因此使用索引獲得的功效是：我們找到了匹配的數據行在哪兒終止，並能夠忽略其它的數據行。另一個功效來自使用定位算法查找第一條匹配的條目，而不需要從索引頭開始執行線性掃描（例如，二分搜索就比線性掃描要快一些）。通過使用這種方法，我們可以快速地定位第一個匹配的值，節省了大量的搜索時間。數據庫使用了多種技術來快速地定位索引值，但是在本文中我們不關心這些技術。重點是它們能夠實現，並且索引是個好東西。 [[The No.2 Picture.]] 圖2：索引後的ad表 你可能要問，我們為什麼不對數據行進行排序從而省掉索引？這樣不是也能實現同樣的搜索速度的改善嗎？是的，如果表只有一個索引，這樣做也可能達到相同的效果。但是你可能添加第二個索引，那麼就無法一次使用兩種不同方法對數據行進行排序了（例如，你可能希望在顧客名稱上建立一個索引，在顧客ID號或電話號碼上建立另外一個索引）。把與數據行相分離的條目作為索引解決了這個問題，允許我們創建多個索引。此外，索引中的行一般也比數據行短一些。當你插入或刪除新的值的時候，移動較短的索引值比移動較長數據行的排序次序更加容易。 不同的MySQL存儲引擎的索引實現的具體細節信息是不同的。例如，對於MyISAM數據表，該表的數據行保存在一個數據文件中，索引值保存在索引文件中。一個數據表上可能有多個索引，但是它們都被存儲在同一個索引文件中。索引文件中的每個索引都包含一個排序的鍵記錄（它用於快速地訪問數據文件）數組。 與此形成對照的是，BDB和InnoDB存儲引擎沒有使用這種方法來分離數據行和索引值，盡管它們也把索引作為排序後的值集合進行操作。在默認情況下，BDB引擎使用單個文件存儲數據和索引值。InnoDB使用單個數據表空間（tablespace），在表空間中管理所有InnoDB表的數據和索引存儲。我們可以把InnoDB配置為每個表都在自己的表空間中創建，但是即使是這樣，數據表的數據和索引也存儲在同一個表空間文件中。 前面的討論描述了單個表查詢環境下的索引的優點，在這種情況下，通過減少對整個表的掃描，使用索引明顯地提高了搜索的速度。當你運行涉及多表聯結（jion）查詢的時候，索引的價值就更高了。在單表查詢中，你需要在每個數據列上檢查的值的數量是表中數據行的數量。在多表查詢中，這個數量可能大幅度上升，因為這個數量是這些表中數據行的數量所產生的。 假設你擁有三個未索引的表t1、t2和t3，每個表都分別包含數據列i1、i2和i3，並且每個表都包含了1000條數據行，其序號從1到1000。查找某些值匹配的數據行組合的查詢可能如下所示： SELECT t1.i1, t2.i2, t3.i3 FROM t1, t2, t3 WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3; 這個查詢的結果應該是1000行，每個數據行包含三個相等的值。如果在沒有索引的情況下處理這個查詢，那麼如果我們不對這些表進行全部地掃描，我們是沒有辦法知道哪些數據行含有哪些值的。因此你必須嘗試所有的組合來查找符合WHERE條件的記錄。可能的組合的數量是1000 x 1000 x 1000（10億！），它是匹配記錄的數量的一百萬倍。這就浪費了大量的工作。這個例子顯示，如果沒有使用索引，隨著表的記錄不斷增長，處理這些表的聯結所花費的時間增長得更快，導致性能很差。我們可以通過索引這些數據表來顯著地提高速度，因為索引讓查詢采用如下所示的方式來處理： 1．選擇表t1中的第一行並查看該數據行的值。 2．使用表t2上的索引，直接定位到與t1的值匹配的數據行。類似地，使用表t3上的索引，直接定位到與表t2的值匹配的數據行。 3．處理表t1的下一行並重復前面的過程。執行這樣的操作直到t1中的所有數據行都被檢查過。 在這種情況下，我們仍然對表t1執行了完整的掃描，但是我們可以在t2和t3上執行索引查找，從這些表中直接地獲取數據行。理論上采用這種方式運行上面的查詢會快一百萬倍。當然這個例子是為了得出結論來人為建立的。然而，它解決的問題卻是現實的，給沒有索引的表添加索引通常會獲得驚人的性能提高。
MySQL有幾種使用索引的方式： · 如上所述，索引被用於提高WHERE條件的數據行匹配或者執行聯結操作時匹配其它表的數據行的搜索速度。 · 對於使用了MIN()或MAX()函數的查詢，索引數據列中最小或最大值可以很快地找到，不用檢查每個數據行。 · MySQL利用索引來快速地執行ORDER BY和GROUP BY語句的排序和分組操作。 · 有時候MySQL會利用索引來讀取查詢得到的所有信息。假設你選擇了MyISAM表中的被索引的數值列，那麼就不需要從該數據表中選擇其它的數據列。在這種情況下，MySQL從索引文件中讀取索引值，它所得到的值與讀取數據文件得到的值是相同的。沒有必要兩次讀取相同的值，因此沒有必要考慮數據文件。
索引的代價 一般來說，如果MySQL能夠找到方法，利用索引來更快地處理查詢，它就會這樣做。這意味著，對於大多數情況，如果你沒有對表進行索引，就會使性能受到損害。這就是我所描繪的索引優點的美景。但是它有缺點嗎？有的，它在時間和空間上都有開銷。在實踐中，索引的優點的價值一般會超過這些缺點，但是你也應該知道到底有一些什麼缺點。 首先，索引加快了檢索的速度，但是減慢了插入和刪除的速度，同時還減慢了更新被索引的數據列中的值的速度。也就是說，索引減慢了大多數涉及寫操作的速度。發生這種現象的原因在於寫入一條記錄的時候不但需要寫入數據行，還需要改變所有的索引。數據表帶有的索引越多，需要做出的修改就越多，平均性能的降低程度也就越大。在本文的"高效率載入數據"部分中，我們將更細致地了解這些現象並找出處理方法。 其次，索引會花費磁盤空間，多個索引相應地花費更多的磁盤空間。這可能導致更快地到達數據表的大小限制： · 對於MyISAM表，頻繁地索引可能引起索引文件比數據文件更快地達到最大限制。 · 對於BDB表，它把數據和索引值一起存儲在同一個文件中，添加索引引起這種表更快地達到最大文件限制。 · 在InnoDB的共享表空間中分配的所有表都競爭使用相同的公共空間池，因此添加索引會更快地耗盡表空間中的存儲。但是，與MyISAM和BDB表使用的文件不同，InnoDB共享表空間並不受操作系統的文件大小限制，因為我們可以把它配置成使用多個文件。只要有額外的磁盤空間，你就可以通過添加新組件來擴展表空間。 使用單獨表空間的InnoDB表與BDB表受到的約束是一樣的，因為它的數據和索引值都存儲在單個文件中。 這些要素的實際含義是：如果你不需要使用特殊的索引幫助查詢執行得更快，就不要建立索引。
選擇索引 假設你已經知道了建立索引的語法，但是語法不會告訴你數據表應該如何索引。這要求我們考慮數據表的使用方式。這一部分指導你如何識別出用於索引的備選數據列，以及如何最好地建立索引： 用於搜索、排序和分組的索引數據列並不僅僅是用於輸出顯示的。換句話說，用於索引的最好的備選數據列是那些出現在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。僅僅出現在SELECT關鍵字後面的輸出數據列列表中的數據列不是很好的備選列： SELECT col_a <- 不是備選列 FROM tbl1 LEFT JOIN tbl2 ON tbl1.col_b = tbl2.col_c <- 備選列 WHERE
