目錄

一、索引分類

1.1 聚簇索引結構

1.2 非聚簇索引(二級索引)

1.3 主鍵索引

1.4 唯一索引

1.5 普通索引

1.6 前綴索引

1.7 聯合索引

1.8 索引下推

1.9 索引區分度

二、優化索引的方法

2.1 索引的特點

2.2 適合創建索引的情況

2.3 不適合創建索引的情況

2.4?優化索引的方法

---

慢查詢的原因之一就是索引使用不當，本文對索引的本質和結構來剖析如何使用索引

一、索引分類

索引是數據的目錄，是一種數據結構，為了快速檢索與查找數據

索引分類：

維度	類別
數據結構	B+tree索引、Hash索引、Full-text索引
物理存儲	聚簇索引（主鍵索引）、二級索引（輔助索引）
字段特性	主鍵索引、唯一索引、普通索引、前綴索引
字段個數	單列索引、聯合索引

MySQL 存儲引擎默認是InnoDB，在InnoDB中必須有聚簇索引，這是由于InnoDB存儲引擎的索引結構B+tree有關系。

在創建表時，會創建一個聚簇索引：

• 如果有主鍵，默認會使用主鍵作為聚簇索引的索引鍵（key）；
• 如果沒有主鍵，就選擇第一個不包含 NULL 值的唯一列作為聚簇索引的索引鍵（key）
• 如果上面兩個都沒有，InnoDB 將自動生成一個隱式自增 id 列作為聚簇索引的索引鍵（key）

1.1 聚簇索引結構

如圖：B+樹是一個多叉樹，葉子節點存放數據，非葉子節點存放索引，每個節點都是按照主鍵順序存放，葉子節點中的數據包括主鍵索引、數據、上一個節點的指針和下一個結點的指針

根據上面的圖分析 select * from product where id=5

第一步：將id=5與根節點索引(1,10,20)比較，5在(1,10)之間，索引搜索找到第二層節點

第二步：將id=5與第二層節點(1,4,7)比較，5在(4,7)之間，找到第三層索引

第三步：在(4,5,6)之間查找，找到索引值=5的行數據

InnoDB的索引和數據都是存儲在硬盤中的，每次讀取數據都要進行一次硬盤IO操作，上述步驟一共進行了3個IO操作。

1.2 非聚簇索引(二級索引)

非聚簇索引，例如product值是二級索引，則用product創建的順序索引結構如下：

其中非葉子節點是product索引值，而葉子節點放的是product索引值與主鍵數據

分析：select * from product where product = '0002';?

第一步：在非聚簇索引中知道product=0002的主鍵索引值，id=2

第二步：回表查詢，在聚簇索引中根據id=2查詢行數據

回表：當查詢使用非聚集索引（secondary index）時，需要先通過索引找到對應的數據行主鍵，然后再根據主鍵去聚集索引（primary index）中獲取完整數據行的過程。

非回表查詢：

select id?from product where product = '0002';?

查詢的id就在非聚簇索引中，不需要回表去主鍵索引中查詢，這種在二級索引中就可以查到結果的過程叫覆蓋索引，查一個B+樹就可以找到。

1.3 主鍵索引

建立在主鍵字段上，創建表的時候一起創建，一個表只有一個主鍵索引，主鍵索引列不能為空值

CREATE TABLE table_name{...PRIMARY KEY(id) USING BTREE
}

1.4 唯一索引

與主鍵索引不同的地方，一個表可以有多個唯一索引，唯一索引允許索引列有空值，但是唯一索引列的值必須唯一。

//創建表時創建索引
CREATE TABLE table_name(...UNIQUE KEY(column1,column2)
);//建表后增加索引
CREATE UNIQUE INDEX index_name 
ON table_name(column1,column2)或者
ALTER TABLE table_name 
ADD UNIQUE (column1, column2);

1.5 普通索引

建立在普通的字段上，不要求字段值非空，也不要求字段值唯一

//創建表時創建索引
CREATE TABLE table_name(...INDEX(column1,column2)
);//建表后增加索引
CREATE INDEX index_name 
ON table_name(column1,column2)或者
ALTER TABLE table_name 
ADD INDEX (column1, column2);

1.6 前綴索引

對字符類型字段的前幾個字符創建索引，目的是為了節省索引所占的存儲空間，提高查詢效率

1.7 聯合索引

由多個字段組成的索引為聯合索引，例如productId和productName兩個字段創建的索引，其索引結構如下：

聯合索引的非葉子節點是用productId和name做值，索引先按照productId排序，productId相同時按照name排序，所以聯合索引需要滿足最左匹配原則，如果不滿足，則該聯合索引就會失效，無法利用索引提高查詢效率。

如果語句where name=ipad8，則無法使用聯合索引(productId,name)，因為name字段是局部有序，全局無序的，利用索引的前提是索引是全局有序的。

舉例說明，哪些情況聯合索引生效，聯合索引(a,b)

語句	索引	說明
select * from table where a>1 and b=2	a字段用到了聯合索引，b字段沒有用到	a字段是有序的，可以定位到符合a>1的紀錄，但是b是無序的
select * from table where a>=1 and b=2	a,b字段都用到了聯合索引	a>=1索引可以很快定位到a=1的紀錄，b=2可以將索引定位到a=1,b=2的紀錄，然后往后掃描
select * from table where a between 2 and 8 and b=2	a,b字段都用到了聯合索引	between and相當于a>=2 and a<=8,b=2可以進一步縮小索引掃描的行數
select * from table where a like 'j%' and b=2	a,b字段都用到了聯合索引	當索引a字段值為‘j%’時，b=2可以減少掃描的二級索引紀錄行數

1.8 索引下推

select * from table where a>1 and b=2，對于聯合索引(a,b)，只有a字段用到了索引，那b字段是回表查詢后進行判斷呢還是在二級索引中判斷呢？

mysql5.6后，引入了索引下推，可以在二級索引中之間判斷其它字段是否滿足條件，減少回表的次數，例如在二級索引中查詢出a>2的紀錄之后，會在二級索引中包含的字段b進行判斷是否滿足b=2

1.9 索引區分度

建立聯合索引時，需要將區分度大的字段排在前面，區分度小的排在后面，區分度越大過濾的數據越多，區分度=一個字段值去重后的個數/該字段總數

二、優化索引的方法

2.1 索引的特點

優點：提高查詢效率

缺點：占用物理空間，數量越大占用越多；創建和維護索引占用時間，索引越大占用時間越多；增加表增刪改的效率，因為每次更新都要動態維護索引數據。

2.2 適合創建索引的情況

1. 字段有唯一性限制；
2. where中的字段，如果查詢條件是多個字段，可以創建聯合索引；
3. group by與order by中的字段，因為索引是排好序的，不需要重新排序；

2.3 不適合創建索引的情況

1. 字段區分度低的，例如性別，優化器會在區分度很低時，放棄索引，采用全表掃描；
2. where 、group by、order by中用不到的字段，索引的作用是快速定位，否則只會占用空間；
3. 經常更新的字段不適合創建索引，會增加維護索引的成本；
4. 數據量很少時不適合創建索引，數據量很少時全表掃描會更快；

2.4?優化索引的方法

1. 前綴索引優化：大字符做索引時，用前綴索引，減少索引字段大小，提高索引頁中存儲的索引數量
2. 覆蓋索引優化：對于查詢的字段可以創建聯合索引，直接在二級索引中查詢出所有的數據，不需要回表查詢主鍵索引，減少IO操作
3. 主鍵索引自增：自增索引在增加數據時，直接開辟新的頁存儲數據，不需要移動其他的數據，否則會導致頁分裂，造成內存碎片，索引結構不緊湊，影響查詢效率。
4. 防止索引失效：

• 不符合最左匹配原則：例如like '%xx'或者like '%xx%'
• 對索引列進行計算、函數、類型轉換
• OR條件，只有一個條件有索引時，也會索引失效

參考：索引常見面試題 | 小林coding