索引

索引分為主鍵索引、唯一索引、普通索引、聚集索引、全文索引幾種，而索引其實就是在無序的數據中建立索引，每次查詢可以根據索引迅速查到我們想要的數據（就像字典的目錄a-z一樣）

• 優點

  • 提高數據查找速度
  • 提高group by、order by分組與排序的時間

• 缺點

  • 每增加數據都需要更新索引，隨者數據量增大，索引維護成本會增加
  • 占用一定的存儲空間，.myi后綴的文件存儲的就是索引文件。

索引類型

主鍵索引

數據列不允許重復，不允許為NULL，可以被引用為外鍵，一個表只能有一個主鍵索引

唯一索引

數據列不允許重復，允許為NULL值，不可以被引用為外鍵，一個表允許多個列創建唯一索引

普通索引

基本的索引類型，沒有唯一性限制，允許為NULL值，不可以被引用為外鍵，一個表可以有多個普通索引

主鍵索引、唯一索引、普通索引區別：

索引類型數據是否允許重復是否允許NULL是否可以當作外鍵索引個數限制

主鍵索引	否	否	是	僅有一個
唯一索引	否	是	否	允許多個
普通索引	是	是	否	允許多個

表中可以看出約束是從高到低，對比表種內容然后依據不同場景進行使用

聚集索引（聚簇索引）

在聚集索引中，表中數據行的物理位置與邏輯值（索引和數據為同一個文件）的順序相同，一個表中只能包含一個聚集索引，因為物理順序只能有一個。聚集索引通常提供更快的數據訪問速度。
其中 InnoDB采用的就是聚簇索引，數據和索引文件為一個idb文件，表數據文件本身就是主索引，相鄰的索引臨近存儲。 葉節點data域保存了完整的數據記錄(數據[除主鍵id外其他列data]+主索引[索引key:表主鍵id])。 葉子節點直接存儲數據記錄，以主鍵id為key,葉子節點中直接存儲數據記錄。(底層存儲結構: frm -表定義、 ibd: innoDB數據&索引文件)

（1）如果表定義了主鍵，則PK就是聚集索引；
（2）如果表沒有定義主鍵，則第一個非空唯一索引（not NULL unique）列是聚集索引；
（3）否則，InnoDB會創建一個隱藏的row-id作為聚集索引；

非聚簇索引

索引和數據分開的索引。
其中MyISAM底層采用的就是非聚簇索引，使用myi索引文件和myd數據文件分離，索引文件僅保存數據記錄的指針地址。葉子節點data域存儲指向數據記錄的指針地址。(底層存儲結構： frm -表定義、 myi -myisam索引、 myd-myisam數據)

覆蓋索引

所謂覆蓋索引就是指索引中包含了查詢中的所有字段，這種情況下就不需要再進行回表查詢了
MySQL 中只能使用 B-Tree 索引做覆蓋索引，因為哈希索引等都不存儲索引的列的值，覆蓋索引對于 MyISAM 和 InnoDB 都非常有效，可以減少系統調用和數據拷貝等時間

組合索引

使用多個列來組成一個索引，比如B-Tree的方式

全文索引

主要用于海量數據的搜索，比如淘寶、京東對商品的搜索就可以建立全文索引（不可能用like模糊匹配吧）,這個類型在mysql5.6開始支持InnoDB引擎的全文索引，功能沒有專業搜索引擎比如solr、es豐富，如果需求簡單，可以使用全文索引

適用場景：適用于海量數據的關鍵字模糊搜索，比如簡易版的搜索引擎

索引的實現方式

B-Tree索引

InnoDB使用的是B-Tree算法，即每個葉子節點包含指向下一個葉子節點的指針，就像一個樹一樣

適用場景：最常用的一個索引類型，可以適用于多種場景

工作原理：B-Tree索引中，聯合索引中的索引項會先根據第一個索引列進行排序，第一個索引列相同的情況下，會再按照第二個索引列進行排序，依次類推。

可以應用到B-Tree索引的情況：

全值匹配：查找條件和索引中的所有列相匹配

匹配最左前綴：查找條件只有索引中的第一列

匹配列前綴：比如有兩個索引，一個是姓，一個是名，先找到zhang的列，然后再根據已找到的列找到名為s開頭的人，也可以應用到索引

匹配范圍值：比如查找姓在chen到zhang之間的人；或者查找姓為zhang，然后名為san到si范圍的人

只訪問索引的查詢：即要查詢的數據都在索引中包含，則只需要訪問索引就行，無需訪問數據行，比如只需要查詢姓和名兩列，而這兩列剛好又是聯合索引。這種索引被稱作覆蓋索引

索引排序：比如使用姓進行排序

無法使用到B-Tree索引的情況：

索引不是最左列開始查找，則無法使用索引。比如直接查找名為san的人，則無法應用到索引

索引不全：比如有三個字段的聯合索引，條件中只有第一個、和第三個的字段條件，跳過了中間某個列，則只能使用到索引的第一列

如果查詢中有某個列的范圍查詢，則該列右邊的所有列都無法使用索引優化查詢（范圍的話沒有具體的值）。

哈希索引

如果在列上建立索引，則針對每一行數據，存儲引擎會根據所有的索引列計算出一個哈希碼，每一個行計算出的哈希碼會組成一個哈希表，同時在哈希表中存儲了指向每個數據行的指針。
哈希表結構如下：

適用場景：僅作等值匹配且數據重復率低且對索引查找速度要求高的情況

可以應用到哈希索引的情況：

只有精確匹配全部索引行的查詢條件才能利用索引

無法使用到哈希索引的情況：

索引中不包含任何列的值

索引無法應用于排序

不支持部分索引列匹配查找（必須使用全部索引列的查詢條件才能使用哈希索引優化查詢）

無法范圍查找，只能等值比較

哈希沖突會影響性能：比如某個列的數據重復率非常高，則每次在找到匹配的哈希后還需要對這個哈希碼的所有數據進行等值比較

其中，還有一個叫做“自適應哈希索引”，是當InnoDB注意到某些索引的使用頻率很高時，會在B-Tree索引之上再建立一層哈希索引，以提高查詢效率

空間數據索引（R-Tree）

空間索引可用于地理數據存儲，它需要GIS相關函數的支持，由于MySQL的GIS支持并不完
善，所以該索引方式在MySQL中很少有人使用。

擴展

什么是回表

如果 select 所需獲得列中有大量的非索引列，索引就需要到表中找到相應的列的信息，這就叫回表

比如如下例子，先使用普通索引查詢除出ID，然后再去聚簇索引查詢具體數據的過程就叫左做回表

如何避免回表？

使用聚集索引（主鍵或第一個唯一索引）就不會回表，普通索引就會回表

比如select id, name, sex from user;，將單列索引(name)升級為聯合索引(name, sex)，即可避免回表，因為要查詢的name和sex都在索引中了

索引下推

MySQL 5.6引入了索引下推優化，可以在索引遍歷過程中，對索引中包含的字段先做判斷，過濾掉不符合條件的記錄，減少回表字數

select * from tuser where name like '張 %' and age=10 and ismale=1;

沒有索引下推：首先根據索引來查找記錄，然后再根據where條件來過濾記錄（回表）
有索引下推：MySQL會在取出索引的同時，判斷是否可以進行where條件過濾再進行索引查詢(回表)

唯一索引導致死鎖

如圖所示，有三個事務同時插入同一個記錄，導致唯一索引沖突的過程：

死鎖發生過程：

T1時刻sessionA插入會加排他鎖

T2時刻SessionB插入同id會主鍵沖突，會加上共享鎖

T3時刻SessionC插入同id會主鍵沖突，會加上共享鎖

這時，SessionA回滾釋放排他鎖，sessionB向獲得排他鎖，但發現sessionC有共享鎖存在，B和C相互等待造成死鎖

根本原因：
唯一索引導致，本質是并發請求導致一個數據重復插入或是網絡抖動造成

解決方案：

可以使用緩存將重復請求去重，確保同時只執行一個相同sql

異常捕捉，mysql有死鎖檢測和恢復，只有一個事務會成功，只需要catch異常即可

總結

以上是生活随笔為你收集整理的数据库索引类型介绍及其优缺点、区别、适用场景的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。