摘要：如何優化數據庫系統的性能，使其盡可能快地處理各種查詢，是本文將要介紹的。索引在優化查詢中的作用，包括了索引優化查詢的原理，索引是優化查詢的最常用也是最有效的的方法，一個數據表，尤其是容量很大的表，建立合適的索引，會使查詢的速度提高很大。本文將試圖解釋並給出MySQL索引的各種功能的一些例子。
MySQL索引的使用
我們首先討論索引，因為它是加快查詢的最重要的工具。還有其他加快查詢的技術，但是最有效的莫過於恰當地使用索引了。在 MySQL 的郵件清單上，人們通常詢問關於使查詢更快的問題。在大量的案例中，都是因為表上沒有索引，一般只要加上索引就可以立即解決問題。但這樣也並非總是有效，因為優化並非總是那樣簡單。然而，如果不使用索引，在許多情形下，用其他手段改善性能只會是浪費時間。應該首先考慮使用索引取得最大的性能改善，然後再尋求其他可能有幫助的技術。
本節介紹索引是什麼、它怎樣改善查詢性能、索引在什麼情況下可能會降低性能，以及怎樣為表選擇索引。下一節，我們將討論 MySQL 的查詢優化程序。除了知道怎樣創建索引外，了解一些優化程序的知識也是有好處的，因為這樣可以更好地利用所創建的索引。某些編寫查詢的方法實際上會妨礙索引的效果，應該避免這種情況出現。（雖然並非總會這樣。有時也會希望忽略優化程序的作用。我們也將介紹這些情況。）
索引對單個表查詢的影響
索引被用來快速找出在一個列上用一特定值的行。沒有索引，MySQL不得不首先以第一條記錄開始並然後讀完整個表直到它找出相關的行。表越大，花費時間越多。如果表對於查詢的列有一個索引，MySQL能快速到達一個位置去搜尋到數據文件的中間，沒有必要考慮所有數據。如果一個表有1000 行，這比順序讀取至少快100倍。注意你需要存取幾乎所有1000行，它較快的順序讀取，因為此時我們避免磁盤尋道。
例如對下面這樣的一個student表：
mysql>SELECT * FROM student
+------+---------+---------+---------+---------+
| id | name | english | chinese | history |
+------+---------+---------+---------+---------+
| 12 | Tom | 66 | 93 | 67 |
| 56 | Paul | 78 | 52 | 75 |
| 10 | Marry | 54 | 89 | 74 |
| 4 | Tina | 99 | 83 | 48 |
| 39 | William | 43 | 96 | 52 |
| 74 | Stone | 42 | 40 | 61 |
| 86 | Smith | 49 | 85 | 78 |
| 37 | Black | 49 | 63 | 47 |
| 89 | White | 94 | 31 | 52 |
+------+---------+---------+---------+---------+
這樣，我們試圖對它進行一個特定查詢時，就不得不做一個全表的掃描，速度很慢。例如，我們查找出所有english成績不及格的學生：
mysql>SELECT name,english FROM student WHERE english<60;
+---------+---------+
| name | english |
+---------+---------+
| Marry | 54 |
| William | 43 |
| Stone | 42 |
| Smith | 49 |
| Black | 49 |
+---------+---------+
其中，WHERE從句不得不匹配每個記錄，以檢查是否符合條件。對於這個較小的表也許感覺不到太多的影響。但是對於一個較大的表，例如一個非常大的學校，我們可能需要存儲成千上萬的記錄，這樣一個檢索的所花的時間是十分可觀的。
如果，我們為english列創建一個索引：
mysql>ALTER TABLE student ADD INDEX (english) ;
+-------------------+
| index for english |
+-------------------+
| 42 |
| 43 |
| 49 |
| 49 |
| 54 |
| 66 |
| 78 |
| 94 |
| 99 |
+-------------------+
如上表，此索引存儲在索引文件中，包含表中每行的english列值，但此索引是在 english的基礎上排序的。現在，不需要逐行搜索全表查找匹配的條款，而是可以利用索引進行查找。假如我們要查找分數小於60的所有行，那麼可以掃描索引，結果得出5行。然後到達分數為66的行，及Tom的記錄，這是一個比我們正在查找的要大的值。索引值是排序的，因此在讀到包含Tom的記錄時，我們知道不會再有匹配的記錄，可以退出了。如果查找一個值，它在索引表中某個中間點以前不會出現，那麼也有找到其第一個匹配索引項的定位算法，而不用進行表的順序掃描（如二分查找法）。這樣，可以快速定位到第一個匹配的值，以節省大量搜索時間。數據庫利用了各種各樣的快速定位索引值的技術，這些技術是什麼並不重要，重要的是它們工作正常，索引技術是個好東西。
因此在執行下述查詢
mysql>SELECT name,english FROM user WHERE english<60;
其結果為：
+---------+---------+
| name | english |
+---------+---------+
| Stone | 42 |
| William | 43 |
| Smith | 49 |
| Black | 49 |
| Marry | 54 |
+---------+---------+
你應該可以發現，這個結果與未索引english列之前的不同，它是排序的，原因正式如上所述。
索引對多個表查詢的影響
前面的討論描述了單表查詢中索引的好處，其中使用索引消除了全表掃描，極大地加快了搜索的速度。在執行涉及多個表的連接查詢時，索引甚至會更有價值。在單個表的查詢中，每列需要查看的值的數目就是表中行的數目。而在多個表的查詢中，可能的組合數目極大，因為這個數目為各表中行數之積。
假如有三個未索引的表 t1、t2、t3，分別只包含列 c1、c2、c3，每個表分別由含有數值 1 到 1000 的 1000 行組成。查找對應值相等的表行組合的查詢如下所示：
此查詢的結果應該為 1000 行，每個組合包含 3 個相等的值。如果我們在無索引的情況下處理此查詢，則不可能知道哪些行包含那些值。因此，必須尋找出所有組合以便得出與 WHERE 子句相配的那些組合。可能的組合數目為 1000×1000×1000（十億），比匹配數目多一百萬倍。很多工作都浪費了，並且這個查詢將會非常慢，即使在如像 MySQL 這樣快的數據庫中執行也會很慢。而這還是每個表中只有 1000 行的情形。如果每個表中有一百萬行時，將會怎樣？很顯然，這樣將會產生性能極為低下的結果。如果對每個表進行索引，就能極大地加速查詢進程，因為利用索引的查詢處理如下：
1) 如下從表 t1 中選擇第一行，查看此行所包含的值。
2) 使用表 t2 上的索引，直接跳到 t2 中與來自 t1 的值匹配的行。類似，利用表 t3 上的索引，直接跳到 t3 中與來自 t1 的值匹配的行。
3) 進到表 t1 的下一行並重復前面的過程直到 t1 中所有的行已經查過。
在此情形下，我們仍然對表 t1 執行了一個完全掃描，但能夠在表 t2 和 t3 上進行索引查找直接取出這些表中的行。從道理上說，這時的查詢比未用索引時要快一百萬倍。
如上所述，MySQL 利用索引加速了 WHERE 子句中與條件相配的行的搜索，或者說在執行連接時加快了與其他表中的行匹配的行的搜索。
多列索引對查詢的影響
假定你發出下列SELECT語句：
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一個多列索引存在於col1和col2上，適當的行可以直接被取出。如果分開的單行列索引存在於col1和col2上，優化器試圖通過決定哪個索引將找到更少的行並來找出更具限制性的索引並且使用該索引取行。
你可以這樣創建一個多列索引：
mysql>ALTER TABLE tbl_name ADD INDEX(col1,col2);
而你應該這樣創建分開的單行列索引：
mysql>ALTER TABLE tble_name ADD INDEX(col1); mysql>ALTER TABLE tble_name ADD INDEX(col1);
如果表有一個多列索引，任何最左面的索引前綴能被優化器使用以找出行。例如，如果你有一個3行列索引(col1,col2,col3)，你已經索引了在(col1)、(col1,col2)和(col1,col2,col3)上的搜索能力。
如果列不構成索引的最左面前綴，MySQL不能使用一個部分的索引。假定你下面顯示的SELECT語句：
mysql> SELECT * FROM tbl_name WHERE col1=val1; mysql> SELECT * FROM tbl_name WHERE col2=val2; mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
如果一個索引存在於(col1、col2、col3)上，只有上面顯示的第一個查詢使用索引。第二個和第三個查詢確實包含索引的列，但是(col2)和(col2、col3)不是(col1、col2、col3)的最左面前綴。
如果LIKE參數是一個不以一個通配符字符起始的一個常數字符串，MySQL也為LIKE比較使用索引。例如，下列SELECT語句使用索引：
mysql> select * from tbl_name where key_col LIKE "Patrick%"; mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";
在第一條語句中，只考慮有"Patrick" <= key_col < "Patricl"的行。在第二條語句中，只考慮有"Pat" <= key_col < "Pau"的行。
下列SELECT語句將不使用索引：
mysql> select * from tbl_name where key_col LIKE "%Patrick%"; mysql> select * from tbl_name where key_col LIKE other_col;
在第一條語句中，LIKE值以一個通配符字符開始。在第二條語句中，LIKE值不是一個常數。
如果 column_name 是一個索引，使用column_name IS NULL的搜索將使用索引。
MySQL通常使用找出最少數量的行的索引。一個索引被用於你與下列操作符作比較的列：=、>、>=、<、<=、BETWEEN和一個有一個非通配符前綴象'something%'的LIKE的列。
對於一個多列索引，如果在WHERE子句的所有AND層次使用索引，將不使用來索引優化查詢。為了能夠使用索引優化查詢，必須把一個多列索引的前綴使用在一個AND條件組中。
下列WHERE子句使用索引：
... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
/* optimized like "index_part1='hello'" */
這些WHERE子句不使用索引：
... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */
... WHERE index=1 OR A=10 /* No index */
... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */
MySQL索引的作用
所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B樹中存儲。字符串是自動地壓縮前綴和結尾空間。CREATE INDEX句法。
索引用於：
快速找出匹配一個WHERE子句的行。
在多個表的查詢時，執行連接時加快了與其他表中的行匹配的行的搜索。
對特定的索引列找出MAX()或MIN()值。
如果排序或分組在一個可用索引的最左面前綴上進行(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有鍵值部分跟隨DESC，鍵以倒序被讀取。
在一些情況中，一個查詢能被優化來檢索值，不用咨詢數據文件。如果對某些表的所有使用的列是數字型的並且構成某些鍵的最左面前綴，為了更快，值可以從索引樹被檢索出來。
索引的弊端
一般情況下，如果 MySQL 能夠知道怎樣用索引來更快地處理查詢，它就會這樣做。這表示，在大多數情況下，如果您不對表進行索引，則損害的是您自己的利益。可以看出，作者描繪了索引的諸多好處。但有不利之處嗎？是的，有。實際上，這些缺點被優點所掩蓋了，但應該對它們有所了解。
首先，索引文件要占磁盤空間。如果有大量的索引，索引文件可能會比數據文件更快地達到最大的文件尺寸。其次，索引文件加快了檢索，但增加了插入和刪除，以及更新索引列中的值的時間（即，降低了大多數涉及寫入的操作的時間），因為寫操作不僅涉及數據行，而且還常常涉及索引。一個表擁有的索引越多，則寫操作的平均性能下降就越大。在8.4.4節記錄裝載和修改的速度中，我們將更為詳細地介紹這些性能問題，並討論怎樣解決。
選擇索引的准則
創建索引的語法已經在4.5索引屬性中進行了介紹。這裡，我們假定您已經閱讀過該節。但是知道語法並不能幫助確定表怎樣進行索引。要決定表怎樣進行索引需要考慮表的使用方式。本節介紹一些關於怎樣確定和挑選索引列的准則：
1、搜索的索引列，不一定是所要選擇的列
換句話說，最適合索引的列是出現在 WHERE 子句中的列，或連接子句中指定的列，而不是出現在 SELECT 關鍵字後的選擇列表中的列，例如：
SELECT col_a ←不適合作索引列 FROM Tbl1 LEFT JOIN tbl2 ON tbl1.col_b = tbl2.col_c ←適合作索引列 WHERE col_d = expr ←適合作索引列
當然，所選擇的列和用於 WHERE 子句的列也可能是相同的。關鍵是，列出現在選擇列表中不是該列應該索引的標志。
出現在連接子句中的列或出現在形如 col1 = col2 的表達式中的列是很適合索引的列。查詢中的 col_b 和 col_c 就是這樣的例子。如果 MySQL 能利用連接列來優化一個查詢，表示它通過消除全表掃描相當可觀地減少了表行的組合。
2、使用惟一索引
考慮某列中值的分布。對於惟一值的列，索引的效果最好，而具有多個重復值的列，其索引效果最差。例如，存放年齡的列具有不同值，很容易區分各行。而用來記錄性別的列，只含有“M”和“F”，則對此列進行索引沒有多大用處（不管搜索哪個值，都會得出大約一半的行）。
3、使用短索引
如果對串列進行索引，應該指定一個前綴長度，只要有可能就應該這樣做。例如，如果有一個 CHAR(200) 列，如果在前 10 個或 20 個字符內，多數值是惟一的，那麼就不要對整個列進行索引。對前 10 個或 20 個字符進行索引能夠節省大量索引空間，也可能會使查詢更快。較小的索引涉及的磁盤 I/O 較少，較短的值比較起來更快。更為重要的是，對於較短的鍵值，索引高速緩存中的塊能容納更多的鍵值，因此，MySQL 也可以在內存中容納更多的值。這增加了找到行而不用讀取索引中較多塊的可能性。（當然，應該利用一些常識。如僅用列值的第一個字符進行索引是不可能有多大好處的，因為這個索引中不會有許多不同的值。）
4、利用最左前綴
在創建一個 n 列的索引時，實際是創建了 MySQL 可利用的 n 個索引。多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左前綴。（這與索引一個列的前綴不同，索引一個列的前綴是利用該的前 n 個字符作為索引值。）
假如一個表在分別名為 state、city 和 zip 的三個列上有一個索引。索引中的行是按 state/city/zip 的次序存放的，因此，索引中的行也會自動按 state/city 的順序和 state 的順序存放。這表示，即使在查詢中只指定 state 值或只指定 state 和 city 的值，MySQL 也可以利用索引。因此，此索引可用來搜索下列的列組合：
MySQL 不能使用不涉及左前綴的搜索。例如，如果按 city 或 zip 進行搜索，則不能使用該索引。如果要搜索某個州以及某個 zip 代碼（索引中的列1和列3），則此索引不能用於相應值的組合。但是，可利用索引來尋找與該州相符的行，以減少搜索范圍。
5、不要過度索引
不要以為索引“越多越好”，什麼東西都用索引是錯的。每個額外的索引都要占用額外的磁盤空間，並降低寫操作的性能，這一點我們前面已經介紹過。在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。如果有一個索引很少利用或從不使用，那麼會不必要地減緩表的修改速度。此外，MySQL 在生成一個執行計劃時，要考慮各個索引，這也要費時間。創建多余的索引給查詢優化帶來了更多的工作。索引太多，也可能會使 MySQL 選擇不到所要使用的最好索引。只保持所需的索引有利於查詢優化。
如果想給已索引的表增加索引，應該考慮所要增加的索引是否是現有多列索引的最左索引。如果是，則就不要費力去增加這個索引了，因為已經有了。
6、考慮在列上進行的比較類型
索引可用於“<”、“<=”、“=”、“>=”、“>”和 BETWEEN 運算。在模式具有一個直接量前綴時，索引也用於 LIKE 運算。如果只將某個列用於其他類型的運算時（如 STRCMP( )），對其進行索引沒有價值。
總結
本節介紹了索引在優化查詢中的作用，包括了索引優化查詢的原理，索引在各種情況的檢索中的益處，也包括索引的的弊端：增加了存儲的空間，使裝載數據變慢。
索引是優化查詢的最常用也是最有效的的方法，一個數據表，尤其是容量很大的表，建立合適的索引，會使查詢的速度提高很大。
