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 */
