Mysql兩種索引結(jié)構(gòu)：B+Tree索引和Hash索引的區(qū)別和使用場景

• Hash索引：使用hash散列的形式，已KV格式存數(shù)，查找單條數(shù)據(jù)的時候速度很快，但是范圍查找和排序的時候效率慢，目前就只有MEMORY引擎顯式地支持這種索引，底層會維護(hù)一個類似KV結(jié)構(gòu)的文件

• B+tree索引 :是mysql使用最頻繁的一個索引數(shù)據(jù)結(jié)構(gòu)，是Inodb和Myisam存儲引擎模式的索引類型。相對Hash索引，B+樹在查找單條記錄的速度比不上Hash索引，但是因為更適合排序等操作。Myisam 和 Innodb默認(rèn)都是使用B+Tree實現(xiàn)的

B+Tree作為mysql數(shù)據(jù)庫索引原理

• 與普通的二叉樹不同，B-Tree允許每個節(jié)點有更多的子節(jié)點，將每個節(jié)點的數(shù)據(jù)量增大，這樣設(shè)計很復(fù)合磁盤的設(shè)計原則，讓每個節(jié)點的數(shù)據(jù)大小正好放在磁盤的一個頁上，這樣從而減少磁盤的IO的次數(shù)，從而減少尋址的時間

• 而mysql使用的是B+Tree，除了擁有B-Tree的優(yōu)先，相對于B-Tree,他更適合外部存儲(一般指磁盤存儲),由于內(nèi)節(jié)點(非葉子節(jié)點)不存儲data，所以一個節(jié)點可以存儲更多的內(nèi)節(jié)點，每個節(jié)點能索引的范圍更大更精確。也就是說使用B+樹單次磁盤IO的信息量相比較B樹更大，IO效率更高

• mysql是關(guān)系型數(shù)據(jù)庫，經(jīng)常會按照區(qū)間來訪問某個索引列，B+樹的葉子節(jié)點間按順序建立了鏈指針，加強了區(qū)間訪問性，所以B+樹對索引列上的區(qū)間范圍查詢很友好。而B樹每個節(jié)點的key和data在一起，無法進(jìn)行區(qū)間查找。

MYISAM和INNODB索引B+TREE的區(qū)別

• MYISAM引擎中--非聚集索引

  • 使用B+TREE的時候，索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址，又叫做非聚集索引

  • 主鍵索引和非主鍵索引都保存在.myi文件中，然后指向.myd中的內(nèi)存

• INNODB引擎中--聚集索引

  • 使用B+TREE的時候，表數(shù)據(jù)文件本身就是按照B+TREE組織的索引接口，葉子節(jié)點data域保存了完成的數(shù)據(jù)記錄，這個索引key是數(shù)據(jù)表的主鍵，因此INNODB表數(shù)據(jù)文件本身就是主索引，又叫做聚集索引

  • 索引文件（就是本身的文件）保存在.ibd文件中的，副索引文件也是保存在.ibd中的但是指向的地址是主索引的地址，所以是執(zhí)行了兩次查詢

索引覆蓋

在上圖中可以看到，如果使用name作為的索引，屬于二級索引，查詢條件為name的時候先查詢到name索引中指定的主鍵，然后再回表查詢，這樣"回表"查詢是需要消耗IO性能的，所以我們在設(shè)計查詢的時候最好能夠避免這種回表查詢。

概念：當(dāng)能通過檢索索引就可以讀取想要的數(shù)據(jù)，那就不需要再到數(shù)據(jù)表中讀取行了。如果一個索引包含了（或覆蓋了）滿足查詢語句中字段與條件的數(shù)據(jù)就叫做覆蓋索引，簡單解釋來說就是查詢的正好是name字段的時候查詢條件也使用的name，這樣的話就無需回表查詢，增加查詢速度

顯示效果：當(dāng)發(fā)起一個被索引覆蓋的查詢（也叫索引覆蓋查詢）時，在EXPLAIN的Extra列可以看到“Using index”的信息。

索引下推

MySQL 5.6引入了索引下推優(yōu)化，默認(rèn)開啟，使用以下命令可以將其關(guān)閉。

SET optimizer_switch = 'index_condition_pushdown=off';

官方文檔中給的例子和解釋如下：
people表中（zipcode，lastname，firstname）構(gòu)成一個索引
SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

如果沒有使用索引下推技術(shù)，則MySQL會通過zipcode='95054'從存儲引擎中查詢對應(yīng)的數(shù)據(jù)，返回到MySQL服務(wù)端，然后MySQL服務(wù)端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'來判斷數(shù)據(jù)是否符合條件。 如果使用了索引下推技術(shù)，則MYSQL首先會返回符合zipcode='95054'的索引，然后根據(jù)lastname LIKE '%etrunia%'和address LIKE '%Main Street%'來判斷索引是否符合條件。如果符合條件，則根據(jù)該索引來定位對應(yīng)的數(shù)據(jù)，如果不符合，則直接reject掉。 有了索引下推優(yōu)化，可以在有l(wèi)ike條件查詢的情況下，減少回表次數(shù)。

• innodb引擎的表，索引下推只能用于二級索引。就像之前提到的，innodb的主鍵索引樹葉子結(jié)點上保存的是全行數(shù)據(jù)，所以這個時候索引下推并不會起到減少查詢?nèi)袛?shù)據(jù)的效果。

• 索引下推一般可用于所求查詢字段（select列）不是/不全是聯(lián)合索引的字段，查詢條件為多條件查詢且查詢條件子句（where/order by）字段全是聯(lián)合索引。假設(shè)表t有聯(lián)合索引（a,b）,下面語句可以使用索引下推提高效率 select * from t where a > 2 and b > 10;

索引的最左匹配原則

非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

索引使用原則

• =和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式

• 盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0，那可能有人會問，這個比例有什么經(jīng)驗值嗎？使用場景不同，這個值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄

• 索引列不能參與計算，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。所以語句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’);

• 盡量的擴展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可

普通索引，唯一索引的選擇

在普通索引和唯一索引的使用成本上，主要在于更新操作的時候，普通索引數(shù)據(jù)的更新，直接將數(shù)據(jù)更新到change buffer中，然后change buffer會定期的刷新到磁盤即可，而唯一索引需要去對比索引是否是唯一的，如果更新的數(shù)據(jù)在change buffer中，則對比的過程可以直接在內(nèi)存中，這樣時間成不不大，但是如果更新的數(shù)據(jù)不在change buffer中，那么更新就需要去IO磁盤進(jìn)行查詢對比，這樣就會導(dǎo)致更新過程緩慢。所以如果業(yè)務(wù)上沒有要求必須唯一的化，盡量使用普通索引，這樣的成本會更低