Mysql索引概述

所有MySQL列類型可以被索引。對相關列使用索引是提高SELECT操作性能的最佳途徑。根據(jù)存儲引擎定義每個表的最大索引數(shù)和最大索引長度。所有存儲引擎支持每個表至少16個索引，總索引長度至少為256字節(jié)。大多數(shù)存儲引擎有更高的限制。

在MySQL 5.1中，對于MyISAM和InnoDB表，前綴可以達到1000字節(jié)長。請注意前綴的限制應以字節(jié)為單位進行測量，而CREATE TABLE語句中的前綴長度解釋為字符數(shù)。當為使用多字節(jié)字符集的列指定前綴長度時一定要加以考慮。

還可以創(chuàng)建FULLTEXT索引。該索引可以用于全文搜索。只有MyISAM存儲引擎支持FULLTEXT索引，并且只為CHAR、VARCHAR和TEXT列。索引總是對整個列進行，不支持局部(前綴)索引。也可以為空間列類型創(chuàng)建索引。只有MyISAM存儲引擎支持空間類型。空間索引使用R-樹。默認情況MEMORY(HEAP)存儲引擎使用hash索引，但也支持B-樹索引。

設計索引的原則

1) 搜索的索引列，不一定是所要選擇的列。

換句話說，最適合索引的列是出現(xiàn)在WHERE 子句中的列，或連接子句中指定的列，而不是出現(xiàn)在SELECT 關鍵字后的選擇列表中的列。

2) 使用惟一索引。

考慮某列中值的分布。對于惟一值的列，索引的效果最好，而具有多個重復值的列，其索引效果最差。例如，存放年齡的列具有不同值，很容易區(qū)分 各行。而用來記錄性別的列，只含有“ M”和“F”，則對此列進行索引沒有多大用處（不管搜索哪個值，都會得出大約一半的行）。

3) 使用短索引。

如果對串列進行索引，應該指定一個前綴長度，只要有可能就應該這樣做。例如，如果有一個CHAR(200) 列，如果在前10 個或20 個字符內(nèi)，多數(shù)值是惟一的，那么就不要對整個列進行索引。對前10 個或20 個字符進行索引能夠節(jié)省大量索引空間，也可能會使查詢更快。較小的索引涉及的磁盤I/O 較少，較短的值比較起來更快。更為重要的是，對于較短的鍵值，索引高速緩存中的塊能容納更多的鍵值，因此，MySQL也可以在內(nèi)存中容納更多的值。這增加 了找到行而不用讀取索引中較多塊的可能性。（當然，應該利用一些常識。如僅用列值的第一個字符進行索引是不可能有多大好處的，因為這個索引中不會有許多不 同的值。）

4) 利用最左前綴。

在創(chuàng)建一個n 列的索引時，實際是創(chuàng)建了MySQL可利用的n 個索引。多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左前綴。（這與索引一個列的前綴不同，索引一個列的前綴是利用該的前n 個字符作為索引值。）

5) 不要過度索引。

不要以為索引“越多越好”，什么東西都用索引是錯的。每個額外的索引都要占用額外的磁盤空間，并降低寫操作的性能，這一點我們前面已經(jīng)介紹 過。在修改表的內(nèi)容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。如果有一個索引很少利用或從不使用，那么會不必要地減緩表 的修改速度。此外，MySQL在生成一個執(zhí)行計劃時，要考慮各個索引，這也要費時間。創(chuàng)建多余的索引給查詢優(yōu)化帶來了更多的工作。索引太多，也可能會使 MySQL選擇不到所要使用的最好索引。只保持所需的索引有利于查詢優(yōu)化。如果想給已索引的表增加索引，應該考慮所要增加的索引是否是現(xiàn)有多列索引的最左 索引。如果是，則就不要費力去增加這個索引了，因為已經(jīng)有了。

6) 考慮在列上進行的比較類型。

索引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和BETWEEN 運算。在模式具有一個直接量前綴時，索引也用于LIKE 運算。如果只將某個列用于其他類型的運算時（如STRCMP( )），對其進行索引沒有價值。

btree索引與hash索引

 對于BTREE和HASH索引，當使用=、<=>、IN、IS NULL或者IS NOT NULL操作符時，關鍵元素與常量值的比較關系對應一個范圍條件。Hash索引還有一些其它特征：它們只用于使用=或<=>操作符的等式比較(但很快)。優(yōu)化器不能使用hash索引來加速ORDER BY操作。(該類索引不能用來按順序搜索下一個條目）。MySQL不能確定在兩個值之間大約有多少行(這被范圍優(yōu)化器用來確定使用哪個索引)。如果你將一個MyISAM表改為hash-索引的MEMORY表，會影響一些查詢。只能使用整個關鍵字來搜索一行。(用B-樹索引，任何關鍵字的最左面的前綴可用來找到行)。

對于BTREE索引，當使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE 'pattern'(其中 'pattern'不以通配符開始)操作符時，關鍵元素與常量值的比較關系對應一個范圍條件。“常量值”系指：查詢字符串中的常量、同一聯(lián)接中的const或system表中的列、無關聯(lián)子查詢的結果、完全從前面類型的子表達式組成的表達式。

下面是一些WHERE子句中有范圍條件的查詢的例子。

下列范圍查詢適用于 btree索引和hash索引：
   

 下列范圍查詢適用于btree索引

Mysql如何使用索引

索引用于快速找出在某個列中有一特定值的行。不使用索引，MySQL必須從第1條記錄開始然后讀完整個表直到找出相關的行。表越大，花費的時間越多。如果表中查詢的列有一個索引，MySQL能快速到達一個位置去搜尋到數(shù)據(jù)文件的中間，沒有必要看所有數(shù)據(jù)。如果一個表有1000行，這比順序讀取至少快100倍。注意如果你需要訪問大部分行，順序讀取要快得多，因為此時我們避免磁盤搜索。

大多數(shù)MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B樹中存儲。只是空間列類型的索引使用R-樹，并且MEMORY表還支持hash索引。

關于什么情況下數(shù)據(jù)庫會使用索引以及什么情況下數(shù)據(jù)庫不會使用索引的詳細解釋請看優(yōu)化篇的相關章節(jié)，這里就不再累述。