索引的分類

從存儲結構上來劃分：

BTree索引（B-Tree或B+Tree索引），Hash索引溶推，full-index全文索引宪拥，R-Tree索引扁位。

這里所描述的是索引存儲時保存的形式

從應用層次來分：

普通索引，唯一索引者铜，復合索引

• 普通索引：即一個索引只包含單個列腔丧，一個表可以有多個單列索引

• 唯一索引：索引列的值必須唯一，但允許有空值作烟。如果能確定某個數(shù)據(jù)列只包含彼此各不相同的值愉粤，在為這個數(shù)據(jù)列創(chuàng)建索引的時候就應該用關鍵字 UNIQUE 把它定義為一個唯一性索引。
  創(chuàng)建唯一性索引的目的往往不是為了提高訪問速度拿撩，而是為了避免數(shù)據(jù)出現(xiàn)重復衣厘。

• 復合索引：多列值組成一個索引，專門用于組合搜索压恒，其效率大于索引合并

根據(jù)數(shù)據(jù)的物理順序與鍵值的邏輯（索引）順序關系：

聚集索引头滔，非聚集索引

• 聚簇索引(聚集索引)：并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式涎显。具體細節(jié)取決于不同的實現(xiàn)坤检，InnoDB的聚簇索引其實就是在同一個結構中保存了B-Tree索引(技術上來說是B+Tree)和數(shù)據(jù)行。

• 非聚簇索引：不是聚簇索引期吓，就是非聚簇索引

存儲結構劃分

如大家所知道的早歇，Mysql目前主要有以下幾種索引類型：FULLTEXT倾芝，HASH，BTREE箭跳，RTREE晨另。

那么，這幾種索引有什么功能和性能上的不同呢谱姓？

FULLTEXT(全文檢索)

目前只有MyISAM引擎支持借尿。其可以在CREATE TABLE ，ALTER TABLE 屉来，CREATE INDEX 使用路翻，不過目前只有 CHAR、VARCHAR 茄靠，TEXT 列上可以創(chuàng)建全文索引茂契。值得一提的是，在數(shù)據(jù)量較大時候慨绳，現(xiàn)將數(shù)據(jù)放入一個沒有全局索引的表中掉冶，然后再用CREATE INDEX創(chuàng)建FULLTEXT索引，要比先為一張表建立FULLTEXT然后再將數(shù)據(jù)寫入的速度快很多脐雪。

全文索引并不是和MyISAM一起誕生的厌小，它的出現(xiàn)是為了解決WHERE name LIKE “%word%"這類針對文本的模糊查詢效率較低的問題。在沒有全文索引之前战秋，這樣一個查詢語句是要進行遍歷數(shù)據(jù)表操作的召锈，可見，在數(shù)據(jù)量較大時是極其的耗時的获询，如果沒有異步IO處理涨岁，進程將被挾持，很浪費時間吉嚣，當然這里不對異步IO作進一步講解梢薪，想了解的童鞋，自行谷哥尝哆。

全文索引的使用方法并不復雜：

創(chuàng)建

ALTER TABLE table ADD INDEXFULLINDEXUSING FULLTEXT(cname1[,cname2…]);

使用

SELECT * FROM table WHERE MATCH(cname1[,cname2…]) AGAINST ('word' MODE );

其中秉撇， MODE為搜尋方式，可選值如下

• IN BOOLEAN MODE

布爾模式秋泄，允許word里含一些特殊字符用于標記一些具體的要求琐馆，如+表示一定要有，-表示一定沒有恒序，*表示通用匹配符瘦麸，是不是想起了正則，類似吧歧胁；

• IN NATURAL LANGUAGE MODE

自然語言模式滋饲，就是簡單的單詞匹配厉碟；

• IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION / WITH QUERY EXPANSION

對搜索引擎稍微有點了解的同學，肯定知道分詞這個概念屠缭，F(xiàn)ULLTEXT索引也是按照分詞原理建立索引的箍鼓。西文中，大部分為字母文字呵曹，分詞可以很方便的按照空格進行分割款咖。但很明顯，中文不能按照這種方式進行分詞奄喂。那又怎么辦呢铐殃？這個向大家介紹一個Mysql的中文分詞插件**Mysqlcft**，有了它砍聊，就可以對中文進行分詞背稼，當然還有其他的分詞插件可以使用贰军。

HASH

**Hash**這個詞玻蝌，可以說，自打我們開始碼的那一天起词疼，就開始不停地見到和使用到了俯树。其實，hash就是一種（key=>value）形式的鍵值對贰盗，如數(shù)學中的函數(shù)映射许饿，允許多個key對應相同的value，但不允許一個key對應多個value舵盈。正是由于這個特性陋率，hash很適合做索引，為某一列或幾列建立hash索引秽晚，就會利用這一列或幾列的值通過一定的算法計算出一個hash值瓦糟，對應一行或幾行數(shù)據(jù)（這里在概念上和函數(shù)映射有區(qū)別，不要混淆）赴蝇。在java語言中菩浙，每個類都有自己的hashcode()方法，沒有顯示定義的都繼承自object類句伶，該方法使得每一個對象都是唯一的劲蜻，在進行對象間equal比較，和序列化傳輸中起到了很重要的作用考余。hash的生成方法有很多種先嬉，足可以保證hash碼的唯一性，例如在MongoDB中楚堤，每一個document都有系統(tǒng)為其生成的唯一的objectID（包含時間戳坝初，主機散列值浸剩，進程PID，和自增ID）也是一種hash的表現(xiàn)鳄袍。額绢要，我好像扯遠了-_-!

• Hash 索引僅僅能滿足"=","IN"和"<=>"查詢，不能使用范圍查詢拗小。

由于 Hash 索引比較的是進行 Hash 運算之后的 Hash 值重罪，所以它只能用于等值的過濾，不能用于基于范圍的過濾哀九，因為經(jīng)過相應的 Hash 算法處理之后的 Hash 值的大小關系剿配，并不能保證和Hash運算前完全一樣。

• Hash 索引無法被用來避免數(shù)據(jù)的排序操作阅束。

由于 Hash 索引中存放的是經(jīng)過 Hash 計算之后的 Hash 值呼胚，而且Hash值的大小關系并不一定和 Hash 運算前的鍵值完全一樣，所以數(shù)據(jù)庫無法利用索引的數(shù)據(jù)來避免任何排序運算息裸；

• Hash 索引不能利用部分索引鍵查詢蝇更。

對于組合索引，Hash 索引在計算 Hash 值的時候是組合索引鍵合并后再一起計算 Hash 值呼盆，而不是單獨計算 Hash 值年扩，所以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash 索引也無法被利用访圃。

• Hash 索引在任何時候都不能避免表掃描厨幻。

前面已經(jīng)知道，Hash 索引是將索引鍵通過 Hash 運算之后腿时，將 Hash運算結果的 Hash 值和所對應的行指針信息存放于一個 Hash 表中况脆，由于不同索引鍵存在相同 Hash 值，所以即使取滿足某個 Hash 鍵值的數(shù)據(jù)的記錄條數(shù)批糟，也無法從 Hash 索引中直接完成查詢格了，還是要通過訪問表中的實際數(shù)據(jù)進行相應的比較，并得到相應的結果跃赚。

• Hash 索引遇到大量Hash值相等的情況后性能并不一定就會比B-Tree索引高笆搓。

對于選擇性比較低的索引鍵，如果創(chuàng)建 Hash 索引纬傲，那么將會存在大量記錄指針信息存于同一個 Hash 值相關聯(lián)满败。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表數(shù)據(jù)的訪問叹括，而造成整體性能低下算墨。

稍作補充，講一下HASH索引的過程汁雷，順便解釋下上面的第4,5條：

當我們?yōu)槟骋涣谢蚰硯琢薪ash索引時（目前就只有MEMORY引擎顯式地支持這種索引）净嘀，會在硬盤上生成類似如下的文件：

hash值即為通過特定算法由指定列數(shù)據(jù)計算出來报咳，磁盤地址即為所在數(shù)據(jù)行存儲在硬盤上的地址（也有可能是其他存儲地址，其實MEMORY會將hash表導入內存）挖藏。

這樣暑刃，當我們進行WHERE age = 18 時，會將18通過相同的算法計算出一個hash值==>在hash表中找到對應的儲存地址==>根據(jù)存儲地址取得數(shù)據(jù)膜眠。

所以岩臣，每次查詢時都要遍歷hash表，直到找到對應的hash值宵膨，如（4）架谎，數(shù)據(jù)量大了之后，hash表也會變得龐大起來辟躏，性能下降谷扣，遍歷耗時增加，如（5）捎琐。

BTREE

BTREE索引就是一種將索引值按一定的算法会涎，存入一個樹形的數(shù)據(jù)結構中，相信學過數(shù)據(jù)結構的童鞋都對當初學習二叉樹這種數(shù)據(jù)結構的經(jīng)歷記憶猶新野哭，反正愚安我當時為了軟考可是被這玩意兒好好地折騰了一番在塔，不過那次考試好像沒怎么考這個幻件。如二叉樹一樣拨黔，每次查詢都是從樹的入口root開始，依次遍歷node绰沥，獲取leaf篱蝇。

BTREE在MyISAM里的形式和Innodb稍有不同

在 Innodb里，有兩種形態(tài)：

• primary key（主鍵索引）徽曲，其leaf node里存放的是數(shù)據(jù)零截，而且不僅存放了索引鍵的數(shù)據(jù)，還存放了其他字段的數(shù)據(jù)秃臣。

• secondary index（二次索引）涧衙，其leaf node和普通的BTREE差不多，只是還存放了指向主鍵的信息.

而在MyISAM里奥此，主鍵和其他的并沒有太大區(qū)別弧哎。不過和Innodb不太一樣的地方是在MyISAM里，leaf node里存放的不是主鍵的信息稚虎，而是指向數(shù)據(jù)文件里的對應數(shù)據(jù)行的信息.

RTREE

RTREE在mysql很少使用撤嫩，僅支持geometry數(shù)據(jù)類型，支持該類型的存儲引擎只有MyISAM蠢终、BDb序攘、InnoDb茴她、NDb、Archive幾種程奠。

相對于BTREE丈牢，RTREE的優(yōu)勢在于范圍查找.

數(shù)據(jù)的物理順序與鍵值的邏輯（索引）順序關系分類

創(chuàng)建一個名為user的表，其包括id瞄沙，name赡麦，age，sex等字段信息帕识。此外泛粹，id為主鍵聚簇索引，idx_name為非聚簇索引肮疗。

CREATE TABLE `user` (  `id` varchar(10) NOT NULL DEFAULT '',  `name` varchar(10) DEFAULT NULL,  `age` int(11) DEFAULT NULL,  `sex` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `idx_name` (`name`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我們將其設置10條數(shù)據(jù)晶姊，便于下面的索引的理解。

INSERT INTO `user` VALUES ('1', 'andy', '20', '女');INSERT INTO `user` VALUES ('10', 'baby', '12', '女');INSERT INTO `user` VALUES ('2', 'kat', '12', '女');INSERT INTO `user` VALUES ('3', 'lili', '20', '男');INSERT INTO `user` VALUES ('4', 'lucy', '22', '女');INSERT INTO `user` VALUES ('5', 'bill', '20', '男');INSERT INTO `user` VALUES ('6', 'zoe', '20', '男');INSERT INTO `user` VALUES ('7', 'hay', '20', '女');INSERT INTO `user` VALUES ('8', 'tony', '20', '男');INSERT INTO `user` VALUES ('9', 'rose', '21', '男');

聚簇索引（主鍵索引）

image

它包含兩個特點：

1.使用記錄主鍵值的大小來進行記錄和頁的排序伪货。

頁內的記錄是按照主鍵的大小順序排成一個單項鏈表们衙。

各個存放用戶記錄的頁也是根據(jù)頁中用戶記錄的主鍵大小順序排成一個雙向鏈表。

2.葉子節(jié)點存儲的是完整的用戶記錄碱呼。

注：聚簇索引不需要我們顯式的創(chuàng)建蒙挑，他是由InnoDB存儲引擎自動為我們創(chuàng)建的。如果沒有主鍵愚臀，其也會默認創(chuàng)建一個忆蚀。

非聚簇索引（二級索引）

上面的聚簇索引只能在搜索條件是主鍵時才能發(fā)揮作用，因為聚簇索引可以根據(jù)主鍵進行排序的姑裂。如果搜索條件是name馋袜，在剛才的聚簇索引上，我們可能遍歷舶斧，挨個找到符合條件的記錄欣鳖，但是，這樣真的是太蠢了茴厉，MySQL不會這樣做的泽台。

如果我們想讓搜索條件是name的時候，也能使用索引矾缓，那可以多創(chuàng)建一個基于name的二叉樹怀酷。如下圖

image

它與聚簇索引的不同：

1.葉子節(jié)點內部使用name字段排序，葉子節(jié)點之間也是使用name字段排序而账。

2.葉子節(jié)點不再是完整的數(shù)據(jù)記錄胰坟，而是name和主鍵值。

為什么不再是完整信息？

MySQL只讓聚簇索引的葉子節(jié)點存放完整的記錄信息笔横，因為如果有好幾個非聚簇索引竞滓，他們的葉子節(jié)點也存放完整的記錄績效，那就不浪費空間啦吹缔。

如果我搜索條件是基于name商佑，需要查詢所有字段的信息，那查詢過程是啥厢塘？

• 根據(jù)查詢條件茶没，采用name的非聚簇索引，先定位到該非聚簇索引某些記錄行晚碾。

• 根據(jù)記錄行找到相應的id抓半，再根據(jù)id到聚簇索引中找到相關記錄。這個過程叫做回表格嘁。

MySQL數(shù)據(jù)庫索引優(yōu)化策略

索引列上不能使用表達式或者函數(shù)

例子：select ...... from product where to_days(out_date) - to_days(current_data)<=30 to_days就是使用了函數(shù)笛求，out_date就是索引列
優(yōu)化后：select ...... from product where out_date<=data_add(current_data,interval 30 day) 這樣對out_date索引列就沒有使用函數(shù)

mysql支持對字符串的前綴建立索引寇壳，前綴索引和索引列的選擇性

create index index_name on table(col_name(n));
索引的選擇性是不重復的索引值和表的記錄數(shù)的比值

聯(lián)合索引灭必，如何選擇索引的順序

經(jīng)常會被使用到的列優(yōu)先
選擇性高的列優(yōu)先
寬度小的列優(yōu)先

覆蓋索引

索引覆蓋是指 如果查詢的列恰好是索引的一部分，那么查詢只需要在索引文件上進行琢蛤，不需要回行到磁盤再找數(shù)據(jù)懂诗，這種查詢速度非撤渌裕快,稱為”索引覆蓋”
優(yōu)點：

• 優(yōu)化緩存，減少磁盤IO操作
• 可以減少隨機IO殃恒，變隨機IO操作變?yōu)轫樞騃O操作
• 可以避免對Innodb主鍵索引的二次查詢
• 可以避免MyISAM表進行系統(tǒng)調用
  無法使用覆蓋索引的場景：
• 不是所有的存儲引擎都支持覆蓋索引
• 查詢中使用了太多的列
• 使用了雙%號的like查詢

索引不會包含有NULL值的列

只要列中包含有NULL值都將不會被包含在索引中植旧，復合索引中只要有一列含有NULL值，那么這一列對于此復合索引就是無效的芋类。所以我們在數(shù)據(jù)庫設計時不要讓字段的默認值為NULL隆嗅。

索引列排序

MySQL查詢只使用一個索引界阁，因此如果where子句中已經(jīng)使用了索引的話侯繁，那么order by中的列是不會使用索引的。因此數(shù)據(jù)庫默認排序可以符合要求的情況下不要使用排序操作泡躯；盡量不要包含多個列的排序贮竟，如果需要最好給這些列創(chuàng)建復合索引。

使用短索引

對串列進行索引较剃，如果可能應該指定一個前綴長度咕别。例如，如果有一個CHAR(255)的列写穴，如果在前10個或20個字符內惰拱，多數(shù)值是惟一的，那么就不要對整個列進行索引啊送。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作偿短。
CREATE INDEX index_name ON table(column(10 or 20))欣孤；

like語句操作

一般情況下不鼓勵使用like操作，如果非使用不可昔逗，如何使用也是一個問題降传。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

不要在列上進行運算

select * from users where YEAR(adddate)<2007
將在每個行上進行運算勾怒，這將導致索引失效而進行全表掃描婆排，因此我們可以改成：
select * from users where adddate<’2007-01-01′
關于這一點可以圍觀：一個單引號引發(fā)的MYSQL性能損失。

innodb的主索引文件上 直接存放該行數(shù)據(jù),稱為聚簇索引,次索引指向對主鍵的引用

myisam中, 主索引和次索引,都指向物理行(磁盤位置).
注意：對innodb來說
1笔链、主鍵索引既存儲索引值段只，又在葉子中存儲行的數(shù)據(jù)
2、如果沒有主鍵, 則會Unique key做主鍵
3鉴扫、如果沒有unique,則系統(tǒng)生成一個內部的rowid做主鍵.
4翼悴、像innodb中,主鍵的索引結構中,既存儲了主鍵值,又存儲了行數(shù)據(jù),這種結構稱為”聚簇索引”
聚簇索引
優(yōu)勢：根據(jù)主鍵查詢條目比較少時,不用回行(數(shù)據(jù)就在主鍵節(jié)點下)
劣勢：如果碰到不規(guī)則數(shù)據(jù)插入時,造成頻繁的頁分裂.
高性能索引策略：
對于innodb而言，因為節(jié)點下有數(shù)據(jù)文件幔妨，因此節(jié)點的分裂將會比較慢
對于innodb的主鍵鹦赎，盡量用整型。而且是遞增的整型
如果是無規(guī)律的數(shù)據(jù)误堡，將會產(chǎn)生的頁的分裂古话，影響速度

最后說一下：MySQL只對一下操作符才使用索引：<、<=锁施、=陪踩、>、>=悉抵、between肩狂、in以及某些時候的like(不以通配符%或_開頭的情形)。而理論上每張表里面最多可創(chuàng)建16個索引姥饰。但不要超過4個傻谁，超過后會影響效率，因為Mysql執(zhí)行索引查詢時有內置機制會計算哪種方式更快速高效列粪，索引過多這部分工作會影響效率审磁。