一般在數(shù)據(jù)中，很多朋友很喜歡用索引來對數(shù)據(jù)庫優(yōu)化。通過索引通?？梢詭椭覀兘鉀Q大多數(shù)的SQL性能問題。
1. 索引的存儲分類 MyISAM存儲引擎的表的數(shù)據(jù)和索引時自動分開存儲的，各自是獨立的一個文件；InnoDB存儲引擎的表的數(shù)據(jù)和索引時存儲在同一表空間里面，但可以有多個文件組成。
MySQL中索引的存儲類型目前只有兩種(BTREE和HASH),具體和表的存儲引擎相關；MyISAM和InnoDB存儲引擎都只支持BTREE索引；MEMORY/HEAP存儲引擎可以支持HASH和BTREE索引。
MySQL目前不支持函數(shù)索引，但是能對列的前面某一部分進行索引，例如name字段，可以以只取name的前4個字符進行索引，這個特征可以大大縮小索引文件的大小。在設計表結構的時候也可以對文本列根據(jù)此特性進行靈活設計。例如
引用
create index ind_company2_name on company2(name(4))
2. MySQL如何使用索引 索引用于快速找出在某個列中有一特定值的行。對相關列使用索引時提高SELECT操作性能的最佳途徑。
查詢要使用索引最主要的條件是查詢條件中需要使用索引關鍵字，如果是多列索引，那么只有查詢條件使用了多列關鍵字最左邊的前綴時，才可以使用索引，否則將不能使用索引。
1. 使用索引
在MySQL中，下列幾種情況下可能使用索引。
對于創(chuàng)建的多列索引，只要查詢的條件中用到了最左邊的列，索引一般就會使用。 例如：
引用
我們首先按company_id ,Moneys的順序創(chuàng)建一個復合索引
create index ind_sales2_companyid_moneys on sales2(company_id,moneys)
如果按company_id進行表查詢
引用
使用explain來分析下
explain select * from sales2 where company_id =2000 \G;
explain select * from sales2 where moneys = 1\G;
通過上面你可以發(fā)現(xiàn)即便where條件中不是用company_id 和 moneys的組合條件，索引仍然能用到，這就是索引的前綴特性。但是如果只按照moneys條件查詢表，那么索引就不會被用到。
對于使用like的查詢，后面如果是常量并且只有%號不在第一字符，索引才能會被使用例如
引用
explain select * from company2 where name like "%3"\G;
explain select * from company2 where name like "3%"\G;
以上兩句你可以認為是一樣的。其實是不一樣的。第一句其實沒有用到索引，而第二句才能夠利用到索引。另外如果like后面跟的是一個列的名字，那么索引也不會被使用。
如果對大是文本進行搜索，使用全文索引而不用使用like"%..%" 如果列名是索引，使用column_name is null 將使用索引如
查詢name為nll的記錄就用到了索引
引用
explain select * from company2 where name is null \G;
2. 下面一些情況存在索引但不使用索引，你可能認為它會用，但是實際上它就是沒用。
引用
1. 如果Mysql估計使用索引比全表掃描更慢，則不使用索引。
例如列key_part1均勻分布在1和100之間，下列查詢中使用索引就不是很好
select * from table_name where key_part1 > 1 and key_part1 < 90;
2. 如果使用MEMORY/HEAP表并且where條件中不使用"="進行索引列，那么不會用到索引。heap表只有在" ="的條件下才會使用索引
3. 用or分割開的條件，如果or前的條件中的列有索引，而后面的列中沒用索引，那么涉及的索引都不會被用到
4. 如果不是索引列的第一部分，那么也不會使用。
5. 如果like是以"%"開始
6. 如果列類型是字符串，那么一定記得在where條件中把字符常量值用引號引起來，否則即便這個列上有索引，Mysql也不會使用。因為MYSQL默認把輸入的常量值進行轉換以后才進行檢索。
最后查看索引使用情況 如果索引正在工作，Handler_read_key的值將很高，這個值代表了一個行被索引值讀的次數(shù)，很低的值表明增加索引得到的性能改善不高，因為索引經常不被使用到。Handler_read_rnd_next的值高則說明查詢運行低效，并且應該建立索引補救。這個值的含義是在數(shù)據(jù)文件中讀取下一行的請求數(shù)。如果正進行大量的表掃描，Handler_read_rnd_next的值較高，則通常說明表索引不正確或者寫入的查詢沒有利用索引。
還記得怎么看Handler_read_rnd_next 嗎？
使用show statuts like 'Handler_read_%';
很多開發(fā)者對Mysql還是有深厚的感情，雖然現(xiàn)在已經被收購。至于MySQL的前途到底是否會被開源組織接手還是怎么樣？這個就暫時無解了。不過我想至少MySQL的確有它的優(yōu)勢。廢話就不說了。
下面討論下MySQL5.0中的索引的設計和使用。任何東西設計的好，那么使用起來就順手。不過很多時候給出設計什么規(guī)則這些都是相對的。做任何的事情，最重要的是能否根據(jù)當時情況就合理的調整你的設計。如果你只會看著書本或者權威來死套什么設計理念來進行實際的開發(fā)和設計的話，那么我請你還是少讀書為妙。
索引是數(shù)據(jù)庫中用來
提高性能的常用工具。（注意如果要優(yōu)化數(shù)據(jù)庫的性能，這是一個點）。
所有MySQL列類型都可以被索引，對相關列使用索引是提高SELECT操作性能的最佳途徑。根據(jù)存儲引擎可以定義每個表的最大索引數(shù)和最大索引長度，每種存儲引擎(MyISAM、InnoDB、BDB、MEMORY等)對每個表至少支持16個索引，總索引長度至少為256字節(jié)。大多數(shù)存儲引擎有更高的限制。
MyISAM和InnoDB存儲引擎的表默認創(chuàng)建的都是BTREE索引。MySQL目前還不支持函數(shù)索引，但是支持前綴索引，即對索引字段的前N個字符創(chuàng)建索引。前綴索引的長度跟存儲引擎相關，對于MyISAM存儲引擎的表，索引的前綴長度可以達到1000字節(jié)長，而對于Inn0DB存儲引擎的表，索引的前綴長度最長是767字節(jié)。請注意前綴的限制應以字節(jié)為單位進行測量，而CREATE TABLE語句中的前綴長度解釋為字符數(shù)。在為使用多字節(jié)字符集的列指定前綴長度時一定要加以考慮。
MySQL中還支持全文本索引（FULLTEXT），該索引可以用于全文搜索。但是在Mysql5.0中只有MyISAM存儲引擎支持全文本索引，并且僅僅局限于CHAR、VARCHAR和TEXT列。索引總是對整個列進行的，不支持局部索引。也可以為空間類型創(chuàng)建索引，但是只要MyISAM存儲引擎支持空間類型索引，而且索引的字段必須是非空。
創(chuàng)建索引的語法如下：
引用
CREATE [UNIQUE | FULLTEXT | SPATIAL] IDEX index_name
[USING index_type]
ON tb1_name (index_col_name,....)
index_col_name:
col_name [(length)][ASC | DESC]
索引的創(chuàng)建可以在創(chuàng)建表的時候就創(chuàng)建，也可以隨時增加新的索引。
下面給出一個例子：
引用
create index personname on person (person(10))
查詢,可以發(fā)現(xiàn)索引personname被使用
引用
explain select * from person where person='hi' \G
索引的刪除語法為：
引用
DROP INDEX index_name ON tb1_name
如果想刪除person表中的personname索引如下操作
引用
drop index personname on person
上面簡單的演示了索引的創(chuàng)建和刪除。
接下來討論下索引的設計原則：
索引的設計可以遵循一些已有的原則，創(chuàng)建索引的時候請
盡量考慮符合這些原則，便于提升索引的使用效率，更高效地使用索引。
1. 搜索的索引列，不一定是所要選擇的列。最合適索引的列式出現(xiàn)在WHERE子句中的列，或連接子句中指定的列，而不是出現(xiàn)在SELECT關鍵字后的選擇列表中的列。
2. 使用唯一索引?？紤]某列中值的分布。索引的列的基數(shù)越大，索引的效果越好。例如數(shù)據(jù)庫中有好幾年的資料，這些資料中有日期這個字段，而且查詢中常常要區(qū)分日期。那么針對日期設索引就很容易區(qū)分。
3. 使用短索引。如果對字符串列進行索引，應該指定應該前綴長度，只要有可能就應該這樣做。例如：如果有一個CHAR(200)的列，如果在前10個或20個字符內，多數(shù)值是唯一的。那么就不要對整個列進行索引。對前10個或者20個字符進行索引能夠節(jié)省大量索引空間，也可能會是查詢更快。較小的索引涉及的磁盤IO較少，較短的值比較起來更快。更為重要的是，對于較短的鍵值，索引高速緩存中的塊能容納更多的鍵值，因此,MySQL也可以在內存中容納更多的值。這樣就增加了找到行而不用讀取索引中較多塊的可能性。
4.利用最左前綴。在創(chuàng)建一個n列的索引時，實際是創(chuàng)建了MySQL可利用的n個索引。多列索引起幾個索引的作用，因為可利用索引中最左的列集來匹配行。這樣的列集稱最左前綴。
5. 不要過度索引。不要以為索引“越多越好”，什么東西都用索引時錯誤的。每個額外的索引都要占用額外的磁盤空間，并降低寫操作的性能。在修改表的內容時，索引必須進行更新，有時可能需要重構，因此，索引越多，所花的時間越長。如果有一個索引很少利用或者從不使用，那么會不必要地減緩表的修改速度。此為MySQL在生成一個執(zhí)行計劃時，要考慮各個索引，這也要花費時間。創(chuàng)建多余的索引給查詢優(yōu)化帶來了更多的工作。索引太多，也可能會使MySQL選擇不到所要使用的最好索引。值保持所需的索引有利于查詢優(yōu)化。
6. 對于InnoDB存儲引擎的表，記錄默認會按照一個的順序保存，如果有明確定義的主鍵，則按照主鍵順序保存。如果沒有主鍵，但是有唯一索引，那么就是按照唯一索引的順序保存。如果既沒有主鍵有沒有唯一索引，那么表中會自動生成一個內部列，按照這個列的順序保存。按照主鍵或者內部列進行的訪問是最快的，所以InnoDB表盡量自己指定主鍵，當表中同時有幾個列都是唯一的，都可以作為主鍵的時候，要選擇最常作為訪問條件的列作為主鍵，提高查詢的效率。另外還需要注意，InnoDB表的普通索引都會保存主鍵的鍵值，所以主鍵要盡可能選擇較短的數(shù)據(jù)類型，可以有效減少索引的磁盤占用，提高索引的緩存效果。
BTREE索引與HASH索引 MEMORY存儲引擎的表可以選擇使用BTREE或者HASH索引，兩種不同類型的索引各有其不同的適用范圍。HASH索引有些重要的特征需要在使用的時候特別注意，如下所示：
1. 只用于使用=或<=>操作符的等式比較。
2. 優(yōu)化器不能使用HASH索引來加速ORDER BY操作
3. MySQL不能確定在兩個值之間大約有多少行。如果將一個MyISAM表改為HASH索引的MEMORY表，會影響一些查詢的執(zhí)行效率。
4. 只能使用整個關鍵字來搜索一行。
而對于BTREE索引，當使用 > 、< 、>= 、<= 、BETWEEN 、!= 或者 <> ,或者LINKE'pattern'（其中'pattern'不以通配符開始）操作符時，都可以使用相關列上的索引。
