Mysql索引概述
所有MySQL列類型可以被索引。對相關列使用索引是提高SELECT操作性能的最佳途徑。根據存儲引擎定義每個表的最大索引數和最大索引長度。所有存儲引擎支持每個表至少16個索引，總索引長度至少為256字節。大多數存儲引擎有更高的限制。
在MySQL 5.1中，對於MyISAM和InnoDB表，前綴可以達到1000字節長。請注意前綴的限制應以字節為單位進行測量，而CREATE TABLE語句中的前綴長度解釋為字符數。當為使用多字節字符集的列指定前綴長度時一定要加以考慮。
還可以創建FULLTEXT索引。該索引可以用於全文搜索。只有MyISAM存儲引擎支持FULLTEXT索引，並且只為CHAR、VARCHAR和TEXT列。索引總是對整個列進行，不支持局部(前綴)索引。也可以為空間列類型創建索引。只有MyISAM存儲引擎支持空間類型。空間索引使用R-樹。默認情況MEMORY(HEAP)存儲引擎使用hash索引，但也支持B-樹索引。
設計索引的原則
1) 搜索的索引列，不一定是所要選擇的列。
換句話說，最適合索引的列是出現在WHERE 子句中的列，或連接子句中指定的列，而不是出現在SELECT 關鍵字後的選擇列表中的列。
2) 使用惟一索引。
考慮某列中值的分布。對於惟一值的列，索引的效果最好，而具有多個重復值的列，其索引效果最差。例如，存放年齡的列具有不同值，很容易區分 各行。而用來記錄性別的列，只含有“ M”和“F”，則對此列進行索引沒有多大用處（不管搜索哪個值，都會得出大約一半的行）。
3) 使用短索引。
如果對串列進行索引，應該指定一個前綴長度，只要有可能就應該這樣做。例如，如果有一個CHAR(200) 列，如果在前10 個或20 個字符內，多數值是惟一的，那麼就不要對整個列進行索引。對前10 個或20 個字符進行索引能夠節省大量索引空間，也可能會使查詢更快。較小的索引涉及的磁盤I/O 較少，較短的值比較起來更快。更為重要的是，對於較短的鍵值，索引高速緩存中的塊能容納更多的鍵值，因此，MySQL也可以在內存中容納更多的值。這增加 了找到行而不用讀取索引中較多塊的可能性。（當然，應該利用一些常識。如僅用列值的第一個字符進行索引是不可能有多大好處的，因為這個索引中不會有許多不 同的值。）
4) 利用最左前綴。
在創建一個n 列的索引時，實際是創建了MySQL可利用的n 個索引。多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左前綴。（這與索引一個列的前綴不同，索引一個列的前綴是利用該的前n 個字符作為索引值。）
5) 不要過度索引。
不要以為索引“越多越好”，什麼東西都用索引是錯的。每個額外的索引都要占用額外的磁盤空間，並降低寫操作的性能，這一點我們前面已經介紹 過。在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。如果有一個索引很少利用或從不使用，那麼會不必要地減緩表 的修改速度。此外，MySQL在生成一個執行計劃時，要考慮各個索引，這也要費時間。創建多余的索引給查詢優化帶來了更多的工作。索引太多，也可能會使 MySQL選擇不到所要使用的最好索引。只保持所需的索引有利於查詢優化。如果想給已索引的表增加索引，應該考慮所要增加的索引是否是現有多列索引的最左 索引。如果是，則就不要費力去增加這個索引了，因為已經有了。
6) 考慮在列上進行的比較類型。
索引可用於“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和BETWEEN 運算。在模式具有一個直接量前綴時，索引也用於LIKE 運算。如果只將某個列用於其他類型的運算時（如STRCMP( )），對其進行索引沒有價值。
btree索引與hash索引
對於BTREE和HASH索引，當使用=、<=>、IN、IS NULL或者IS NOT NULL操作符時，關鍵元素與常量值的比較關系對應一個范圍條件。Hash索引還有一些其它特征：它們只用於使用=或<=>操作符的等式比較(但很快)。優化器不能使用hash索引來加速ORDER BY操作。(該類索引不能用來按順序搜索下一個條目）。MySQL不能確定在兩個值之間大約有多少行(這被范圍優化器用來確定使用哪個索引)。如果你將一個MyISAM表改為hash-索引的MEMORY表，會影響一些查詢。只能使用整個關鍵字來搜索一行。(用B-樹索引，任何關鍵字的最左面的前綴可用來找到行)。
對於BTREE索引，當使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE 'pattern'(其中 'pattern'不以通配符開始)操作符時，關鍵元素與常量值的比較關系對應一個范圍條件。“常量值”系指：查詢字符串中的常量、同一聯接中的const或system表中的列、無關聯子查詢的結果、完全從前面類型的子表達式組成的表達式。
下面是一些WHERE子句中有范圍條件的查詢的例子。
下列范圍查詢適用於 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 'bar' AND 'foo';
Mysql如何使用索引
索引用於快速找出在某個列中有一特定值的行。不使用索引，MySQL必須從第1條記錄開始然後讀完整個表直到找出相關的行。表越大，花費的時間越多。如果表中查詢的列有一個索引，MySQL能快速到達一個位置去搜尋到數據文件的中間，沒有必要看所有數據。如果一個表有1000行，這比順序讀取至少快100倍。注意如果你需要訪問大部分行，順序讀取要快得多，因為此時我們避免磁盤搜索。
大多數MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B樹中存儲。只是空間列類型的索引使用R-樹，並且MEMORY表還支持hash索引。
關於什麼情況下數據庫會使用索引以及什麼情況下數據庫不會使用索引的詳細解釋請看優化篇的相關章節，這裡就不再累述。
索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索包含多個列。
MySQL索引類型包括：
(1)普通索引
這是最基本的索引，它沒有任何限制。它有以下幾種創建方式：
◆創建索引
CREATE INDEX indexName ON mytable(username(length));
如果是 CHAR，VARCHAR類型，length可以小於字段實際長度;如果是BLOB和TEXT類型，必須指定 length，下同。
◆修改表結構
ALTER mytable ADD INDEX [indexName] ON (username(length))
◆ 創建表的時候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );
刪除索引的語法：
DROP INDEX [indexName] ON mytable;
(2)唯一索引
它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創建方式：
◆創建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
◆修改表結構
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
◆創建表的時候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
(3)主鍵索引
它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創建主鍵索引：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );
當然也可以用 ALTER 命令。記住：一個表只能有一個主鍵。
(4)組合索引
為了形象地對比單列索引和組合索引，為表添加多個字段：
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );
為了進一步搾取MySQL的效率，就要考慮建立組合索引。就是將 name, city, age建到一個索引裡：
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
建表時，usernname長度為 16，這裡用 10。這是因為一般情況下名字的長度不會超過10，這樣會加速索引查詢速度，還會減少索引文件的大小，提高INSERT的更新速度。
如果分別在 usernname，city，age上建立單列索引，讓該表有3個單列索引，查詢時和上述的組合索引效率也會大不一樣，遠遠低於我們的組合索引。雖然......余下全文>>
二級索引？？
mysql中每個表都有一個聚簇索引（clustered index ），除此之外的表上的每個非聚簇索引都是二級索引，又叫輔助索引（secondary indexes）。
以InnoDB來說，每個InnoDB表具有一個特殊的索引稱為聚集索引。如果您的表上定義有主鍵，該主鍵索引是聚集索引。如果你不定義為您的表的主鍵時，MySQL取第一個唯一索引（unique）而且只含非空列（NOT NULL）作為主鍵，InnoDB使用它作為聚集索引。如果沒有這樣的列，InnoDB就自己產生一個這樣的ID值，它有六個字節，而且是隱藏的，使其作為聚簇索引。
聚簇索引主要是為了方便存儲。。所以二級索引應該都是對聚簇索引的索引。
下面是Mysql Manual上的原話，也可能我理解有誤。
Every InnoDB table has a special index called the clustered index where the data for the rows is stored. If you define a PRIMARY KEY on your table, the index of the primary key is the clustered index.
If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.
Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index recor......余下全文>>
