索引是數據庫中用來提高性能的最常用工具。
索引概述
所有 MySQL 列類型都可以被索引，對相關列使用索引是提高 SELECT 操作性能的最佳途徑。 根據存儲引擎可以定義每個表的最大索引數和最大索引長度， 每種存儲引擎 （如 MyISAM、InnoDB、BDB、MEMORY 等）對每個表至少支持 16 個索引，總索引長度至少為 256 字節。
大多數存儲引擎有更高的限制。
MyISAM 和 InnoDB 存儲引擎的表默認創建的都是 BTREE 索引。MySQL 目前還不支持函數索引，但是支持前綴索引，即對索引字段的前 N 個字符創建索引。前綴索引的長度跟存儲引擎相關，對於 MyISAM 存儲引擎的表，索引的前綴長度可以達到 1000 字節長，而對於InnoDB 存儲引擎的表，索引的前綴長度最長是 767 字節。請注意前綴的限制應以字節為單位進行測量，而 CREATE TABLE 語句中的前綴長度解釋為字符數。在為使用多字節字符集的列指定前綴長度時一定要加以考慮。
默認情況下，MEMORY 存儲引擎使用 HASH 索引，但也支持 BTREE 索引。
索引在創建表的時候可以同時創建，也可以隨時增加新的索引。
索引語法
創建索引：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name (index_col_name,...)
index_col_name:
col_name [(length)] [ASC | DESC]
也可以使用 ALTER TABLE 的語法來增加索引，語法可 CREATE INDEX 類似
例如，要為 city 表創建了 10 個字節的前綴索引，語法是：
mysql> create index cityname on city (city(10));
Query OK, 600 rows affected (0.26 sec)
Records: 600 Duplicates: 0 Warnings: 0
索引的刪除語法為：
DROP INDEX index_name ON tbl_name
例如，想要刪除 city 表上的索引 cityname，可以操作如下：
mysql> drop index cityname on city;
Query OK, 600 rows affected (0.23 sec)
Records: 600 Duplicates: 0 Warnings: 0
設計索引的原則
搜索的索引列， 不一定是所要選擇的列。 換句話說， 最適合索引的列是出現在 WHERE子句中的列，或連接子句中指定的列，而不是出現在 SELECT 關鍵字後的選擇列表中的列。
使用惟一索引。考慮某列中值的分佈。索引的列的基數越大，索引的效果越好。例如，存放出生日期的列具有不同值，很容易區分各行。而用來記錄性別的列，只含有“ M”和“F”，則對此列進行索引沒有多大用處，因為不管搜索哪個值，都會得出大約一半的行。
使用短索引。如果對字符串列進行索引，應該指定一個前綴長度，只要有可能就應該這樣做。例如，如果有一個 CHAR(200)列，如果在前 10 個或 20 個字符內，多數值是惟一的， 那麼就不要對整個列進行索引。 對前 10個或 20 個字符進行索引能夠節省大量索引空間，也可能會使查詢更快。較小的索引涉及的磁盤 IO 較少，較短的值比較起來更快。更為重要的是，對於較短的鍵值，索引高速緩存中的塊能容納更多的鍵值，因此，MySQL 也可以在內存中容納更多的值。這樣就增加了找到行而不用讀取索引中較多塊的可能性。
利用最左前綴。 在創建一個 n 列的索引時， 實際是創建了 MySQL 可利用的 n 個索引。多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左前綴。
不要過度索引。不要以為索引“越多越好” ，什麼東西都用索引是錯誤的。每個額外的索引都要佔用額外的磁盤空間，並降低寫操作的性能。在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。如果有一個索引很少利用或從不使用，那麼會不必要地減緩表的修改速度。此外，MySQL 在生成一個執行計劃時，
要考慮各個索引，這也要花費時間。創建多餘的索引給查詢優化帶來了更多的工作。索引太多， 也可能會使 MySQL 選擇不到所要使用的最好索引。 只保持所需的索引有利於查詢優化。
對於 InnoDB 存儲引擎的表， 記錄默認會按照一定的順序保存， 如果有明確定義的主鍵，則按照主鍵順序保存。如果沒有主鍵，但是有唯一索引，那麼就是按照唯一索引的順序保存。如果既沒有主鍵又沒有唯一索引，那麼表中會自動生成一個內部列，按照這個列的順序保存。按照主鍵或者內部列進行的訪問是最快的，所以 InnoDB 表儘量自己指定主鍵，當表中同時有幾個列都是唯一的，都可以作為主鍵的時候，要選擇最常作為訪問條件的列作為主鍵，提高查詢的效率。另外，還需要注意，InnoDB 表的普通索引都會保存主鍵的鍵值，所以主鍵要儘可能選擇較短的數據類型，可以有效地減少索引的磁盤佔用，提高索引的緩存效果。
BTREE 索引與 HASH 索引
MEMORY 存儲引擎的表可以選擇使用 BTREE 索引或者 HASH 索引， 兩種不同類型的索引各有其不同的適用範圍。HASH 索引有一些重要的特徵需要在使用的時候特別注意，如下所示。
只用於使用=或<=>操作符的等式比較。
優化器不能使用 HASH 索引來加速 ORDER BY 操作。
MySQL 不能確定在兩個值之間大約有多少行。 如果將一個 MyISAM 表改為 HASH 索引的 MEMORY 表，會影響一些查詢的執行效率。
只能使用整個關鍵字來搜索一行。
而對於 BTREE 索引，當使用>、=、<=、BETWEEN、!=或者<>，或者 LIKE 'pattern'（其中'pattern'不以通配符開始）操作符時，都可以使用相關列上的索引。
下列範圍查詢適用於 BTREE 索引和 HASH 索引：
SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);
下列範圍查詢只適用於 BTREE 索引：
SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'lisa' AND 'simon';
瞭解了 BTREE 索引和 HASH 索引不同後， 當使用 MEMORY 表的時候， 如果是默認創建的 HASH索引，就要注意 SQL 語句的編寫，確保可以使用上索引，如果一定要使用範圍查詢，那麼在創建索引的時候，就應該選擇創建成 BTREE 索引。
總結
索引用於快速找出在某個列中有一特定值的行。如果不使用索引，MySQL 必須從第 1條記錄開始然後讀完整個表直到找出相關的行。表越大，花費的時間越多。如果表中查詢的列有一個索引， MySQL 能快速到達一個位置去搜尋數據文件的中間， 沒有必要看所有數據。
如果一個表有 1000 行，這比順序讀取至少快 100 倍。注意如果需要訪問大部分行，順序讀取要快得多，因為此時應避免磁盤搜索。
大多數 MySQL 索引（如 PRIMARY KEY、UNIQUE、INDEX 和 FULLTEXT 等）在 BTREE 中存儲。只是空間列類型的索引使用 RTREE，並且 MEMORY 表還支持 HASH 索引。
索引的世界博大精深，特別是oracle上的索引，大家後面可以多多研究。掌握索引對提高你性能優化很有必要。有用的話走波關注唄~
閱讀更多 波波說運維 的文章
