索引對於良好的性能非常關鍵，尤其是當表的數據量越來越大時，索引對性能（查詢）的影響愈發重要。 ...
索引（在MySQL中也叫做“鍵（key）”）是存儲引擎用於快速找到記錄的一種數據結構。
索引對於良好的性能非常關鍵，尤其是當表的數據量越來越大時，索引對性能（查詢）的影響愈發重要。
- 索引的類型
- 相關的說明請參考之前寫的一篇文章： MySQL索引 - 索引的類型。
- 索引的優點（大致分為以下三點）
- 索引大大減少了伺服器需要掃描的數據量。
- 索引可以幫助伺服器避免排序和臨時表。
- 索引可以將隨機I/O變為順序I/O。
- 如何創建高性能的索引
- 索引列不能是表達式的一部分，也不能是函數的參數，如下是不恰當的寫法：
1 mysql> SELECT * FROM TB1 WHERE num + 1 = 5;
1 mysql> SELECT * FROM TB1 WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(ctime) < 10;
- 在多個列上建立獨立的單列索引大部分情況下並不能提高MySQL的查詢性能，如下是不恰當的寫法：
1 mysql> CREATE TABLE TB4( 2 -> c1 INT, 3 -> c2 INT, 4 -> c3 INT, 5 -> key(c1), 6 -> key(c2), 7 -> key(c3));
1 mysql> EXPLAIN SELECT * FROM TB4 WHERE c1=1 OR c2=1 OR c3=1\G 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: TB4 6 partitions: NULL 7 type: ALL 8 possible_keys: c1,c2,c3 9 key: NULL 10 key_len: NULL 11 ref: NULL 12 rows: 6 13 filtered: 42.13 14 Extra: Using where
- 當不需要考慮排序和分組時，將選擇性最高的列放在最左邊通常是最好的，可以使用以下方法來查看基數和選擇性（這裡對比TB1表中的name和num欄位）：
1 mysql> SELECT COUNT(DISTINCT name)/COUNT(*) AS name_selectivity, 2 -> COUNT(DISTINCT num)/COUNT(*) AS num_selectivity, 3 -> COUNT(*) 4 -> FROM TB1\G 5 *************************** 1. row *************************** 6 name_selectivity: 0.3479 7 num_selectivity: 0.0000 8 COUNT(*): 1750001
可以看出name欄位的選擇性更高，所以將其作為索引列的第一列
1 mysql> ALTER TABLE TB1 ADD KEY(name, num);
- 使用覆蓋索引（查詢列要被所建的索引覆蓋）。索引條目通常遠小於數據行大小，所以如果只需要讀取索引，可以極大地減少數據訪問量，如下（TB1表中有一個多列索引name和num）：
1 mysql> EXPLAIN SELECT name,num FROM TB1\G 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: TB1 6 partitions: NULL 7 type: ref 8 possible_keys: IDX 9 key: IDX 10 key_len: 66 11 ref: const,const 12 rows: 15 13 filtered: 100.00 14 Extra: Using index
上面的語句會使用到覆蓋索引，Extra列可以看到Using index的信息。下麵的例子沒有任何索引能夠覆蓋這個查詢，有兩個原因，一是查詢從表中選擇了 所有列（*），二是MySQL不能再索引中執行LIKE操作：
1 mysql> EXPLAIN SELECT * FROM TB1 WHERE name='test1' AND nk LIKE '%a%'\G 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: TB1 6 partitions: NULL 7 type: ref 8 possible_keys: IDX 9 key: IDX 10 key_len: 66 11 ref: const,const 12 rows: 15 13 filtered: 100.00 14 Extra: Using index condition # 在MySQL的5.6以下版本會顯示Using index where
我們可以通過重寫查詢設計索引來解決上面的查詢語句，先將索引拓展至覆蓋三個數據列（id，name，nk），然後按照如下方式重寫查詢（延遲關聯）：
1 mysql> EXPLAIN SELECT TB1.* FROM TB1 JOIN 2 -> ( 3 -> SELECT id FROM TB1 WHERE name='test1' AND nk LIKE '%a%' 4 -> ) AS t1 ON 5 -> t1.id = TB1.id\G 6 *************************** 1. row *************************** 7 id: 1 8 select_type: SIMPLE 9 table: TB1 10 partitions: NULL 11 type: ref 12 possible_keys: PRIMARY,IDX,IDX1 13 key: IDX 14 key_len: 62 15 ref: const 16 rows: 15 17 filtered: 11.11 18 Extra: Using where; Using index
還有一種情況，在name欄位有二級索引（除了聚簇索引，如果表上有主鍵，該主鍵索引就是聚簇索引。如果未定義主鍵，則取第一個唯一索引而且只含非空列作為主鍵，並使用它作為聚簇索引。如果沒有這樣的列，InnoDB就自己產生一個這樣的ID值，它有六個位元組，而且是隱藏的，使其作為聚簇索引，其他索引都為二級索引），雖然該索引列不包括主鍵id列，但也能夠對id列進行覆蓋查詢，如下：
1 mysql> EXPLAIN SELECT id,name FROM TB1 WHERE name='test2'\G 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: TB1 6 partitions: NULL 7 type: ref 8 possible_keys: IDX,IDX1 9 key: IDX 10 key_len: 62 11 ref: const 12 rows: 15 13 filtered: 100.00 14 Extra: Using index
- 使用索引對結果做排序（當索引的列順序和ORDER BY子句的順序一致，並且所有列的排序方向（倒序或順序）都一樣時，可以使用索引對結果做排序），如下（假設TB1表中有一個多列索引（num,name,nk））：
1 mysql> EXPLAIN SELECT * FROM TB1 WHERE num='1' ORDER BY name, nk\G # 即使ORDER BY子句不滿足作引的最左首碼要求，也可用於查詢排序，因為索引的第一列（num）被指定為一個常數 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: TB1 6 partitions: NULL 7 type: ref 8 possible_keys: IDX2 9 key: IDX2 10 key_len: 4 11 ref: const 12 rows: 872985 13 filtered: 100.00 14 Extra: Using index condition
1 mysql> EXPLAIN SELECT * FROM TB1 WHERE num>'1' ORDER BY num, name\G # 這個查詢也沒問題，ORDER BY使用的兩列就是索引的最左首碼 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: TB1 6 partitions: NULL 7 type: range 8 possible_keys: IDX2 9 key: IDX2 10 key_len: 4 11 ref: NULL 12 rows: 1 13 filtered: 100.00 14 Extra: Using index condition
下麵是一些反例：
1 mysql> EXPLAIN SELECT * FROM TB1 WHERE num>'1' ORDER BY name, nk\G # 這個查詢是錯誤的，索引的第一列（num）被指定為一個範圍，且ORDER BY不滿足最左首碼 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: TB1 6 partitions: NULL 7 type: range 8 possible_keys: IDX2 9 key: IDX2 10 key_len: 4 11 ref: NULL 12 rows: 1 13 filtered: 100.00 14 Extra: Using index condition; Using filesort
1 mysql> EXPLAIN SELECT * FROM TB1 WHERE num='1' ORDER BY name DESC, nk ASC\G # 這個查詢使用了兩種不同的排序方向，但索引是順序排序的 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: TB1 6 partitions: NULL 7 type: ref 8 possible_keys: IDX2 9 key: IDX2 10 key_len: 4 11 ref: const 12 rows: 872985 13 filtered: 100.00 14 Extra: Using index condition; Using filesort
1 mysql> EXPLAIN SELECT * FROM TB1 WHERE num='1' ORDER BY name, ctime\G # 這個查詢的ORDER BY 引用了一個不再索引的中列（ctime） 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: TB1 6 partitions: NULL 7 type: ref 8 possible_keys: IDX2 9 key: IDX2 10 key_len: 4 11 ref: const 12 rows: 872985 13 filtered: 100.00 14 Extra: Using index condition; Using filesort
1 mysql> EXPLAIN SELECT * FROM TB1 WHERE num='1' ORDER BY nk\G # 這個查詢的WHERE 和ORDER BY 中的列無法組合成索引的最左首碼 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: TB1 6 partitions: NULL 7 type: ref 8 possible_keys: IDX2 9 key: IDX2 10 key_len: 4 11 ref: const 12 rows: 872985 13 filtered: 100.00 14 Extra: Using index condition; Using filesort
1 mysql> EXPLAIN SELECT * FROM TB1 WHERE num='1' AND name IN('test1','test2') ORDER BY nk\G # 這個查詢在name列上有多個等於條件，對於排序來說，也是範圍查詢 2 *************************** 1. row *************************** 3 id: 1 4 select_type: SIMPLE 5 table: TB1 6 partitions: NULL 7 type: range 8 possible_keys: IDX2 9 key: IDX2 10 key_len: 66 11 ref: NULL 12 rows: 30 13 filtered: 100.00 14 Extra: Using index condition; Using filesort
- MyISAM使用首碼壓縮來減少索引的大小，從而讓更多的索引可以放入記憶體中，在某些情況能極大的提高性能。MyISAM壓縮每個索引塊的方法是：先保存索引塊中的一個值，然後將其他值和第一個值進行比較得到相同首碼的位元組數和剩餘的不同尾碼部分，把這部分存儲起來即可。例如，索引塊中的第一個值是“test”，第二個值是“test88888”，那麼第二個值的首碼壓縮後存儲的是類似“4,88888”這樣的形式。壓縮塊使用更少空間，但代價是某些操作可能更慢。因為每個值的壓縮首碼都依賴前面的值，所以MyISAM查找時無法在索引塊使用二分查找而只能從頭開始掃描，順序的掃描速度不錯，但如果是倒序掃描（DESC）就不是很好了，所以在塊中查找某一行的操作平均都需要掃描半個索引塊。
- 避免重覆索引（在相同的列上按照相同的順序創建的相同類型的索引），如下：
1 mysql> CREATE TABLE TB5( 2 -> ID INT NOT NULL PRIMARY KEY, 3 -> A INT NOT NULL, 4 -> B INT NOT NULL, 5 -> UNIQUE(ID), 6 -> INDEX(ID) 7 -> ) ENGINE=InnoDB;
PS：MySQL的唯一限制和主鍵限制都是通過索引實現的，因此上面的寫法實際上在相同的列上創建了三個重覆的索引。通常沒有理由這樣做，除非是在同一列上創建不同類型的索引來滿足不同的查詢需求。
- 避免冗餘索引（創建了索引（A，B），在創建索引（A）就是冗餘索引），如下：
1 mysql> CREATE TABLE TB6( 2 -> A INT NOT NULL, 3 -> B INT NOT NULL, 4 -> INDEX IDX(A,B), 5 -> INDEX IDX1(A) 6 -> ) ENGINE=InnoDB;
PS：對於B-Tree索引來說，索引（A，B）也可以當作索引（A）來使用，但如果在創建索引（B，A）則不是冗餘索引。
- 索引列不能是表達式的一部分，也不能是函數的參數，如下是不恰當的寫法：
