一窄潭、 什么是索引

在關(guān)系數(shù)據(jù)庫中，索引是一種單獨的硬纤、物理的數(shù)對數(shù)據(jù)庫表中一列或多列的值進行排序的一種存儲結(jié)構(gòu)，它是某個表中一列或若干列值的集合和相應(yīng)的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針清單。索引的作用相當(dāng)于圖書的目錄循帐，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容。
索引提供指向存儲在表的指定列中的數(shù)據(jù)值的指針舀武，然后根據(jù)您指定的排序順序?qū)@些指針排序拄养。數(shù)據(jù)庫使用索引以找到特定值，然后順指針找到包含該值的行银舱。這樣可以使對應(yīng)于表的SQL語句執(zhí)行得更快瘪匿，可快速訪問數(shù)據(jù)庫表中的特定信息。

索引圖示

二寻馏、 為什么需要建立索引

當(dāng)數(shù)據(jù)保存在磁盤類存儲介質(zhì)上時棋弥，它是作為數(shù)據(jù)塊存放。這些數(shù)據(jù)塊是被當(dāng)做一個整體來訪問的诚欠，這樣是為了保證操作的原子性顽染。磁盤數(shù)據(jù)塊存儲結(jié)構(gòu)類似于鏈表存儲結(jié)構(gòu)，每個數(shù)據(jù)塊包含數(shù)據(jù)部分轰绵，以及指向下一個數(shù)據(jù)塊(節(jié)點)的指針粉寞，不要連續(xù)的數(shù)據(jù)塊進行存儲。

記錄集只能在某一個關(guān)鍵字段上排序唧垦，因此如果想要在一個無序字段上搜索數(shù)據(jù)，就要執(zhí)行一個線性搜索的過程翔悠，平均需要訪問N/2個數(shù)據(jù)塊业崖，N表示數(shù)據(jù)占用的數(shù)據(jù)塊數(shù)目。

如果這個字段是一個非主鍵字段(也就是說蓄愁，不包含唯一訪問入口)双炕，那么需要訪問整個數(shù)據(jù)存儲的數(shù)據(jù)塊。
而如果數(shù)據(jù)是有序的撮抓，則可以使用二分法查找妇斤，這樣只需要訪問log2(N)的數(shù)據(jù)塊，這也就是我們?yōu)槭裁从行虻淖侄尾樵兯俣雀斓脑颉?/p>

三、索引類型

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處理术辐，進程將被挾持，很浪費時間施无。
全文索引的使用方法并不復(fù)雜：
創(chuàng)建索引

ALTER TABLE table 
ADD INDEX `FULLINDEX`
USING 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
  含表達式的自然語言模式岸售，就是先用自然語言模式處理践樱，對返回的結(jié)果，再進行表達式匹配凸丸。

對搜索引擎稍微有點了解的同學(xué)拷邢，肯定知道分詞這個概念，F(xiàn)ULLTEXT索引也是按照分詞原理建立索引的屎慢。西文中解孙，大部分為字母文字，分詞可以很方便的按照空格進行分割抛人。但很明顯弛姜，中文不能按照這種方式進行分詞。那又怎么辦呢妖枚？這個向大家介紹一個Mysql的中文分詞插件Mysqlcft廷臼，有了它，就可以對中文進行分詞绝页，想了解的同學(xué)請移步Mysqlcft荠商，當(dāng)然還有其他的分詞插件可以使用。

HASH

Hash這個詞续誉，可以說莱没，自打我們開始碼的那一天起，就開始不停地見到和使用到了酷鸦。其實饰躲，hash就是一種（key=>value）形式的鍵值對，如數(shù)學(xué)中的函數(shù)映射臼隔，允許多個key對應(yīng)相同的value嘹裂，但不允許一個key對應(yīng)多個value。正是由于這個特性摔握，hash很適合做索引寄狼，為某一列或幾列建立hash索引，就會利用這一列或幾列的值通過一定的算法計算出一個hash值氨淌，對應(yīng)一行或幾行數(shù)據(jù)（這里在概念上和函數(shù)映射有區(qū)別泊愧，不要混淆）。在java語言中盛正，每個類都有自己的hashcode()方法删咱，沒有顯示定義的都繼承自object類，該方法使得每一個對象都是唯一的蛮艰，在進行對象間equal比較腋腮，和序列化傳輸中起到了很重要的作用雀彼。
hash的生成方法有很多種，足可以保證hash碼的唯一性即寡，例如在MongoDB中徊哑，每一個document都有系統(tǒng)為其生成的唯一的objectID（包含時間戳，主機散列值聪富，進程PID莺丑，和自增ID）也是一種hash的表現(xiàn)。
由于hash索引可以一次定位墩蔓，不需要像樹形索引那樣逐層查找,因此具有極高的效率梢莽。那為什么還需要其他的樹形索引呢？引用下園子里其他大神的文章：來自 14的路 的MySQL的btree索引和hash索引的區(qū)別

1. Hash 索引僅僅能滿足"=","IN"和"<=>"查詢奸披，不能使用范圍查詢昏名。
   由于 Hash 索引比較的是進行 Hash 運算之后的 Hash 值，所以它只能用于等值的過濾阵面，不能用于基于范圍的過濾轻局，因為經(jīng)過相應(yīng)的 Hash 算法處理之后的 Hash 值的大小關(guān)系，并不能保證和Hash運算前完全一樣样刷。

• Hash 索引無法被用來避免數(shù)據(jù)的排序操作仑扑。
  由于 Hash 索引中存放的是經(jīng)過 Hash 計算之后的 Hash 值，而且Hash值的大小關(guān)系并不一定和 Hash 運算前的鍵值完全一樣置鼻，所以數(shù)據(jù)庫無法利用索引的數(shù)據(jù)來避免任何排序運算镇饮；
• Hash 索引不能利用部分索引鍵查詢。
  對于組合索引箕母，Hash 索引在計算 Hash 值的時候是組合索引鍵合并后再一起計算 Hash 值储藐，而不是單獨計算 Hash 值，所以通過組合索引的前面一個或幾個索引鍵進行查詢的時候司蔬，Hash 索引也無法被利用邑茄。
• Hash 索引在任何時候都不能避免表掃描。
  前面已經(jīng)知道俊啼，Hash 索引是將索引鍵通過 Hash 運算之后，將 Hash運算結(jié)果的 Hash 值和所對應(yīng)的行指針信息存放于一個 Hash 表中左医，由于不同索引鍵存在相同 Hash 值授帕，所以即使取滿足某個 Hash 鍵值的數(shù)據(jù)的記錄條數(shù)，也無法從 Hash 索引中直接完成查詢浮梢，還是要通過訪問表中的實際數(shù)據(jù)進行相應(yīng)的比較跛十，并得到相應(yīng)的結(jié)果。
• Hash 索引遇到大量Hash值相等的情況后性能并不一定就會比B-Tree索引高秕硝。
  對于選擇性比較低的索引鍵芥映，如果創(chuàng)建 Hash 索引，那么將會存在大量記錄指針信息存于同一個 Hash 值相關(guān)聯(lián)。這樣要定位某一條記錄時就會非常麻煩奈偏，會浪費多次表數(shù)據(jù)的訪問坞嘀，而造成整體性能低下。

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

hash值 
存儲地址    

1db54bc745a1
77#45b5 

4bca452157d4
76#4556,77#45cc…
…

hash值即為通過特定算法由指定列數(shù)據(jù)計算出來丽涩，磁盤地址即為所在數(shù)據(jù)行存儲在硬盤上的地址（也有可能是其他存儲地址，其實MEMORY會將hash表導(dǎo)入內(nèi)存）裁蚁。
這樣矢渊，當(dāng)我們進行WHERE age = 18 時，會將18通過相同的算法計算出一個hash值==>在hash表中找到對應(yīng)的儲存地址==>根據(jù)存儲地址取得數(shù)據(jù)枉证。
所以矮男，每次查詢時都要遍歷hash表，直到找到對應(yīng)的hash值室谚，如（4）昂灵，數(shù)據(jù)量大了之后，hash表也會變得龐大起來舞萄，性能下降眨补，遍歷耗時增加，如（5）倒脓。

BTREE

BTREE索引就是一種將索引值按一定的算法撑螺，存入一個樹形的數(shù)據(jù)結(jié)構(gòu)中。如二叉樹一樣崎弃，每次查詢都是從樹的入口root開始甘晤，依次遍歷node，獲取leaf饲做。
BTREE在MyISAM里的形式和Innodb稍有不同
在 Innodb里线婚，有兩種形態(tài)：一是primary key形態(tài)，其leaf node里存放的是數(shù)據(jù)盆均，而且不僅存放了索引鍵的數(shù)據(jù)塞弊，還存放了其他字段的數(shù)據(jù)。二是secondary index泪姨，其leaf node和普通的BTREE差不多游沿，只是還存放了指向主鍵的信息.
而在MyISAM里，主鍵和其他的并沒有太大區(qū)別肮砾。不過和Innodb不太一樣的地方是在MyISAM里诀黍，leaf node里存放的不是主鍵的信息，而是指向數(shù)據(jù)文件里的對應(yīng)數(shù)據(jù)行的信息仗处。

RTREE

RTREE在mysql很少使用眯勾，僅支持geometry數(shù)據(jù)類型枣宫，支持該類型的存儲引擎只有MyISAM、BDb吃环、InnoDb也颤、NDb、Archive幾種模叙。
相對于BTREE歇拆，RTREE的優(yōu)勢在于范圍查找.

各種索引的使用情況

1. 對于BTREE這種Mysql默認的索引類型，具有普遍的適用性

• 由于FULLTEXT對中文支持不是很好范咨，在沒有插件的情況下故觅，最好不要使用。其實渠啊，一些小的博客應(yīng)用输吏，只需要在數(shù)據(jù)采集時，為其建立關(guān)鍵字列表替蛉，通過關(guān)鍵字索引贯溅，也是一個不錯的方法。
• 對于一些搜索引擎級別的應(yīng)用來說躲查，F(xiàn)ULLTEXT同樣不是一個好的處理方法它浅，Mysql的全文索引建立的文件還是比較大的，而且效率不是很高镣煮，即便是使用了中文分詞插件姐霍，對中文分詞支持也只是一般。真要碰到這種問題典唇，Apache的Lucene或許是你的選擇镊折。
• 正是因為hash表在處理較小數(shù)據(jù)量時具有無可比擬的素的優(yōu)勢，所以hash索引很適合做緩存（內(nèi)存數(shù)據(jù)庫）介衔。如mysql數(shù)據(jù)庫的內(nèi)存版本Memsql恨胚，使用量很廣泛的緩存工具Mencached，NoSql數(shù)據(jù)庫redis等炎咖，都使用了hash索引這種形式赃泡。當(dāng)然，不想學(xué)習(xí)這些東西的話Mysql的MEMORY引擎也是可以滿足這種需求的塘装。
• 至于RTREE急迂，使用較少。

mysql里sql語句值得注意的地方

1. myisam里所有鍵的長度僅支持1000字節(jié)蹦肴，innodb是767.

• blob和text字段僅支持前綴索引.
• 使用!=以及<>不等于的時候，mysql不使用索引.
• 當(dāng)在字段時候函數(shù)的時候猴娩，mysql無法使用索引阴幌；在join時條件字段類型不一致的時候勺阐，mysql無法使用索引；在組合索引里使用非第一個索引時也不使用索引.
• 在使用like的時候矛双，以%開頭渊抽，即"%***"的時候無法使用索引；在使用or的時候议忽，要求or前后字段都有索引.

有時候mysql query optimizer會認為使用索引并不是最優(yōu)計劃,所以不使用索引懒闷。可以在sql語句里可以用use,force index,當(dāng)然有時候使用也不會比不用快,所以需要忽略掉index方法是ignore index.

參考：
MySQL:索引工作原理
Mysql幾種索引類型的區(qū)別及適用情況
MySQL的btree索引和hash索引的區(qū)別