索引的分類

從存儲(chǔ)結(jié)構(gòu)上來劃分：

BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。

這里所描述的是索引存儲(chǔ)時(shí)保存的形式

從應(yīng)用層次來分：

普通索引，唯一索引，復(fù)合索引

• 普通索引：即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引

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

• 復(fù)合索引：多列值組成一個(gè)索引，專門用于組合搜索，其效率大于索引合并

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

聚集索引，非聚集索引

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

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

存儲(chǔ)結(jié)構(gòu)劃分

如大家所知道的，Mysql目前主要有以下幾種索引類型：FULLTEXT，HASH，BTREE，RTREE。

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

FULLTEXT(全文檢索)

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

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

全文索引的使用方法并不復(fù)雜：

創(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里含一些特殊字符用于標(biāo)記一些具體的要求，如+表示一定要有，-表示一定沒有，*表示通用匹配符，是不是想起了正則，類似吧；

• IN NATURAL LANGUAGE MODE

自然語言模式，就是簡(jiǎn)單的單詞匹配；

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

對(duì)搜索引擎稍微有點(diǎn)了解的同學(xué)，肯定知道分詞這個(gè)概念，F(xiàn)ULLTEXT索引也是按照分詞原理建立索引的。西文中，大部分為字母文字，分詞可以很方便的按照空格進(jìn)行分割。但很明顯，中文不能按照這種方式進(jìn)行分詞。那又怎么辦呢？這個(gè)向大家介紹一個(gè)Mysql的中文分詞插件**Mysqlcft**，有了它，就可以對(duì)中文進(jìn)行分詞，當(dāng)然還有其他的分詞插件可以使用。

HASH

**Hash**這個(gè)詞，可以說，自打我們開始碼的那一天起，就開始不停地見到和使用到了。其實(shí)，hash就是一種（key=>value）形式的鍵值對(duì)，如數(shù)學(xué)中的函數(shù)映射，允許多個(gè)key對(duì)應(yīng)相同的value，但不允許一個(gè)key對(duì)應(yīng)多個(gè)value。正是由于這個(gè)特性，hash很適合做索引，為某一列或幾列建立hash索引，就會(huì)利用這一列或幾列的值通過一定的算法計(jì)算出一個(gè)hash值，對(duì)應(yīng)一行或幾行數(shù)據(jù)（這里在概念上和函數(shù)映射有區(qū)別，不要混淆）。在java語言中，每個(gè)類都有自己的hashcode()方法，沒有顯示定義的都繼承自object類，該方法使得每一個(gè)對(duì)象都是唯一的，在進(jìn)行對(duì)象間equal比較，和序列化傳輸中起到了很重要的作用。hash的生成方法有很多種，足可以保證hash碼的唯一性，例如在MongoDB中，每一個(gè)document都有系統(tǒng)為其生成的唯一的objectID（包含時(shí)間戳，主機(jī)散列值，進(jìn)程PID，和自增ID）也是一種hash的表現(xiàn)。額，我好像扯遠(yuǎn)了-_-!

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

由于 Hash 索引比較的是進(jìn)行 Hash 運(yùn)算之后的 Hash 值，所以它只能用于等值的過濾，不能用于基于范圍的過濾，因?yàn)榻?jīng)過相應(yīng)的 Hash 算法處理之后的 Hash 值的大小關(guān)系，并不能保證和Hash運(yùn)算前完全一樣。

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

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

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

對(duì)于組合索引，Hash 索引在計(jì)算 Hash 值的時(shí)候是組合索引鍵合并后再一起計(jì)算 Hash 值，而不是單獨(dú)計(jì)算 Hash 值，所以通過組合索引的前面一個(gè)或幾個(gè)索引鍵進(jìn)行查詢的時(shí)候，Hash 索引也無法被利用。

• Hash 索引在任何時(shí)候都不能避免表掃描。

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

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

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

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

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

hash值即為通過特定算法由指定列數(shù)據(jù)計(jì)算出來，磁盤地址即為所在數(shù)據(jù)行存儲(chǔ)在硬盤上的地址（也有可能是其他存儲(chǔ)地址，其實(shí)MEMORY會(huì)將hash表導(dǎo)入內(nèi)存）。

這樣，當(dāng)我們進(jìn)行WHERE age = 18 時(shí)，會(huì)將18通過相同的算法計(jì)算出一個(gè)hash值==>在hash表中找到對(duì)應(yīng)的儲(chǔ)存地址==>根據(jù)存儲(chǔ)地址取得數(shù)據(jù)。

所以，每次查詢時(shí)都要遍歷hash表，直到找到對(duì)應(yīng)的hash值，如（4），數(shù)據(jù)量大了之后，hash表也會(huì)變得龐大起來，性能下降，遍歷耗時(shí)增加，如（5）。

BTREE

BTREE索引就是一種將索引值按一定的算法，存入一個(gè)樹形的數(shù)據(jù)結(jié)構(gòu)中，相信學(xué)過數(shù)據(jù)結(jié)構(gòu)的童鞋都對(duì)當(dāng)初學(xué)習(xí)二叉樹這種數(shù)據(jù)結(jié)構(gòu)的經(jīng)歷記憶猶新，反正愚安我當(dāng)時(shí)為了軟考可是被這玩意兒好好地折騰了一番，不過那次考試好像沒怎么考這個(gè)。如二叉樹一樣，每次查詢都是從樹的入口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ù)文件里的對(duì)應(yīng)數(shù)據(jù)行的信息.

RTREE

RTREE在mysql很少使用，僅支持geometry數(shù)據(jù)類型，支持該類型的存儲(chǔ)引擎只有MyISAM、BDb、InnoDb、NDb、Archive幾種。

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

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

創(chuàng)建一個(gè)名為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;

我們將其設(shè)置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

它包含兩個(gè)特點(diǎn)：

1.使用記錄主鍵值的大小來進(jìn)行記錄和頁的排序。

頁內(nèi)的記錄是按照主鍵的大小順序排成一個(gè)單項(xiàng)鏈表。

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

2.葉子節(jié)點(diǎn)存儲(chǔ)的是完整的用戶記錄。

注：聚簇索引不需要我們顯式的創(chuàng)建，他是由InnoDB存儲(chǔ)引擎自動(dòng)為我們創(chuàng)建的。如果沒有主鍵，其也會(huì)默認(rèn)創(chuàng)建一個(gè)。

非聚簇索引（二級(jí)索引）

上面的聚簇索引只能在搜索條件是主鍵時(shí)才能發(fā)揮作用，因?yàn)榫鄞厮饕梢愿鶕?jù)主鍵進(jìn)行排序的。如果搜索條件是name，在剛才的聚簇索引上，我們可能遍歷，挨個(gè)找到符合條件的記錄，但是，這樣真的是太蠢了，MySQL不會(huì)這樣做的。

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

image

它與聚簇索引的不同：

1.葉子節(jié)點(diǎn)內(nèi)部使用name字段排序，葉子節(jié)點(diǎn)之間也是使用name字段排序。

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

為什么不再是完整信息？

MySQL只讓聚簇索引的葉子節(jié)點(diǎn)存放完整的記錄信息，因?yàn)槿绻泻脦讉€(gè)非聚簇索引，他們的葉子節(jié)點(diǎn)也存放完整的記錄績(jī)效，那就不浪費(fèi)空間啦。

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

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

• 根據(jù)記錄行找到相應(yīng)的id，再根據(jù)id到聚簇索引中找到相關(guān)記錄。這個(gè)過程叫做回表。

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

索引列上不能使用表達(dá)式或者函數(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) 這樣對(duì)out_date索引列就沒有使用函數(shù)

mysql支持對(duì)字符串的前綴建立索引，前綴索引和索引列的選擇性

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

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

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

覆蓋索引

索引覆蓋是指 如果查詢的列恰好是索引的一部分，那么查詢只需要在索引文件上進(jìn)行，不需要回行到磁盤再找數(shù)據(jù)，這種查詢速度非?？?稱為”索引覆蓋”
優(yōu)點(diǎn)：

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

索引不會(huì)包含有NULL值的列

只要列中包含有NULL值都將不會(huì)被包含在索引中，復(fù)合索引中只要有一列含有NULL值，那么這一列對(duì)于此復(fù)合索引就是無效的。所以我們?cè)跀?shù)據(jù)庫(kù)設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為NULL。

索引列排序

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

使用短索引

對(duì)串列進(jìn)行索引，如果可能應(yīng)該指定一個(gè)前綴長(zhǎng)度。例如，如果有一個(gè)CHAR(255)的列，如果在前10個(gè)或20個(gè)字符內(nèi)，多數(shù)值是惟一的，那么就不要對(duì)整個(gè)列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。
CREATE INDEX index_name ON table(column(10 or 20))；

like語句操作

一般情況下不鼓勵(lì)使用like操作，如果非使用不可，如何使用也是一個(gè)問題。like “%aaa%” 不會(huì)使用索引而like “aaa%”可以使用索引。

不要在列上進(jìn)行運(yùn)算

select * from users where YEAR(adddate)<2007
將在每個(gè)行上進(jìn)行運(yùn)算，這將導(dǎo)致索引失效而進(jìn)行全表掃描，因此我們可以改成：
select * from users where adddate<’2007-01-01′
關(guān)于這一點(diǎn)可以圍觀：一個(gè)單引號(hào)引發(fā)的MYSQL性能損失。

innodb的主索引文件上 直接存放該行數(shù)據(jù),稱為聚簇索引,次索引指向?qū)χ麈I的引用

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

最后說一下：MySQL只對(duì)一下操作符才使用索引：<、<=、=、>、>=、between、in以及某些時(shí)候的like(不以通配符%或_開頭的情形)。而理論上每張表里面最多可創(chuàng)建16個(gè)索引。但不要超過4個(gè)，超過后會(huì)影響效率，因?yàn)镸ysql執(zhí)行索引查詢時(shí)有內(nèi)置機(jī)制會(huì)計(jì)算哪種方式更快速高效，索引過多這部分工作會(huì)影響效率。