1.什麼是索引？為什麼要用索引？

1.1索引的含義

資料庫索引,是資料庫管理系統中一個排序的資料結構,以協助快速查詢,更新資料庫中表的資料.索引的實作通常使用B樹和變種的B+樹(mysql常用的索引就是B+樹)。除了資料之外,資料庫系統還維護為滿足特定查找算法的資料結構,這些資料結構以某種方式引用資料.這種資料結構就是索引！

簡言之，索引就類似于書本，字典的目錄！

1.2為什麼用？

打個比方，如果正确合理設計并且使用索引的MySQL是一輛蘭博基尼的話，那麼沒有設計和使用索引的MySQL就是一個人力三輪車。對于沒有索引的表，單表查詢可能幾十萬資料就是瓶頸，而通常大型網站單日就可能會産生幾十萬甚至幾百萬的資料，沒有索引查詢會變的非常緩慢。

一言以蔽之，合理使用索引，可以加快資料庫的查詢效率和提升程式性能！

2.索引的作用與缺點

2.1作用

①通過建立索引,可以在查詢的過程中,提高系統的性能

②通過建立唯一性索引,可以保證資料庫表中每一行資料的唯一性

③在使用分組和排序子句進行資料檢索時,可以減少查詢中分組和排序的時間

2.2缺點

①建立索引和維護索引要耗費時間,而且時間随着資料量的增加而增大

②索引需要占用實體空間,如果要建立聚簇索引,所需要的空間會更大

③在對表中的資料進行增加删除和修改時需要耗費較多的時間,因為索引也要動态地維護

3.索引的使用場景

3.1應建立索引的場景

①經常需要搜尋的列上

②作為主鍵的列上

③經常用在連接配接的列上,這些列主要是一些外鍵,可以加快連接配接的速度

④經常需要根據範圍進行搜尋的列上

⑤經常需要排序的列上

⑥經常使用在where子句上面的列上

3.2不應建立索引的場景

①查詢中很少用到的列

②對于那些具有很少資料值的列.比如資料表中的性别列,bit資料類型的列

③對于那些定義為text,image的列.因為這些列的資料量相當大

④當對修改性能的要求遠遠大于搜尋性能時.因為當增加索引時,會提高搜尋性能,但是會降低修改性能

4.索引的分類與說明

4.1主鍵索引

設定為主鍵後資料庫會自動建立索引，innodb為聚簇索引

#随表一起建索引：

CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),

PRIMARY KEY(id)

);

#使用AUTO_INCREMENT關鍵字的列必須有索引(隻要有索引就行)。

CREATE TABLE customer2 (id INT(10) UNSIGNED,customer_no VARCHAR(200),customer_name VARCHAR(200),

PRIMARY KEY(id)

);

#單獨建主鍵索引：

ALTER TABLE customer add PRIMARY KEY customer(customer_no);

#删除建主鍵索引：

ALTER TABLE customer drop PRIMARY KEY ;

#修改建主鍵索引：

#必須先删除掉(drop)原索引，再建立(add)索引

4.2單列索引

一個索引隻包含單個列，一個表可以有多個單列索引

#随表一起建索引：

CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),

PRIMARY KEY(id),

KEY (customer_name)

);

#随表一起建立的索引索引名同列名(customer_name)

#單獨建單值索引：

CREATE INDEX idx_customer_name ON customer(customer_name);

#删除索引：

DROP INDEX idx_customer_name ;

4.3唯一索引

索引列的值必須唯一，但允許有空值

#随表一起建索引：

CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),

PRIMARY KEY(id),

KEY (customer_name),

UNIQUE (customer_no)

);

#建立唯一索引時必須保證所有的值是唯一的（除了null），若有重複資料，會報錯。

#單獨建唯一索引：

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

#删除索引：

DROP INDEX idx_customer_no on customer ;

4.4複合索引

一個索引包含多個列，在資料庫操作期間，複合索引比單值索引所需要的開銷更小(對于相同的多個列建索引)

如果一個表中的資料在查詢時有多個字段總是同時出現則這些字段就可以作為複合索引,形成索引覆寫可以提高查詢的效率！

#随表一起建索引：

CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),

PRIMARY KEY(id),

KEY (customer_name),

UNIQUE (customer_name),

KEY (customer_no,customer_name)

);

#單獨建索引：

CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

#删除索引：

DROP INDEX idx_no_name on customer ;

4.5聚集索引與非聚集索引

4.5.1聚集索引

聚集索引：指索引項的排序方式和表中資料記錄排序方式一緻的索引。它會根據聚集索引鍵的順序來存儲表中的資料，即對表的資料按索引鍵的順序進行排序，然後重新存儲到磁盤上。因為資料在實體存放時隻能有一種排列方式，是以一個表隻能有一個聚集索引。比如字典中，用‘拼音’查漢字，就是聚集索引。因為正文中字都是按照拼音排序的。而用‘偏旁部首’查漢字，就是非聚集索引，因為正文中的字并不是按照偏旁部首排序的，我們通過檢字表得到正文中的字在索引中的映射，然後通過映射找到所需要的字。

聚集索引的使用場合為：

a.查詢指令的回傳結果是以該字段為排序依據的；
b.查詢的結果傳回一個區間的值；
c.查詢的結果傳回某值相同的大量結果集。

聚集索引會降低 insert，和update操作的性能，是以，是否使用聚集索引要全面衡量。

4.5.2非聚集索引

非聚集索引：與聚集索引相反，索引順序與實體存儲順序不一緻。

非聚集索引的使用場合為：

a.查詢所獲資料量較少時；
b.某字段中的資料的唯一性比較高時；

非聚集索引必須是稠密索引

4.5.3使用及文法

create [unique] [clustered] [nonclustered] index index_name on {tabel/view} (column[dese/asc][....n])

注： [unique] [clustered] [nonclustered]表示要建立索引的類型，以此為唯一索引，聚集索引，和非聚集索引，當省略unique選項時，建立非唯一索引.當省略clustered,nonclustered選項時.建立聚集索引，省略nonclustered選項時，建立唯一聚集索引。

4.5.4使用場景對比

動作描述	使用聚集索引	使用非聚集索引
列經常被分組排序	使用	使用
傳回某範圍内的資料	使用	不使用
一個或極少不同值	不使用	不使用
小數目的不同值	使用	不使用
大數目的不同值	不使用	使用
頻繁更新的列	不使用	使用
外鍵列	使用	使用
主鍵列	使用	使用
頻繁修改索引列	不使用	使用

4.6聚簇索引與非聚簇索引

4.6.1聚簇索引

聚簇索引并不是一種單獨的索引類型，而是一種資料存儲方式。将資料存儲與索引放到了一塊，索引結構的葉子節點儲存了行資料。

聚簇索引的特點：

①聚簇索引具有唯一性，由于聚簇索引是将資料跟索引結構放到一塊，是以一個表僅有一個聚簇索引。

②表中行的實體順序和索引中行的實體順序是相同的，在建立任何非聚簇索引之前建立聚簇索引，這是因為聚簇索引改變了表中行的實體順序，資料行按照一定的順序排列，并且自動維護這個順序；

③聚簇索引預設是主鍵，如果表中沒有定義主鍵，InnoDB 會選擇一個唯一且非空的索引代替。如果沒有這樣的索引，InnoDB 會隐式定義一個主鍵（類似oracle中的RowId）來作為聚簇索引。如果已經設定了主鍵為聚簇索引又希望再單獨設定聚簇索引，必須先删除主鍵，然後添加我們想要的聚簇索引，最後恢複設定主鍵即可。

4.6.2非聚簇索引

不是聚簇索引的二級索引，也叫輔助索引，都稱為非聚簇索引。将資料與索引分開存儲，索引結構的葉子節點指向了資料對應的位置。

4.6.3Mysql的MYISAM和INNODB引擎

因為這兩種引擎對非聚簇索引和聚簇索引的使用，就是他們之間很大的一個差別。是以結合這兩個引擎，再對這兩種索引展開些描述就更明了了。

在innodb中，在聚簇索引之上建立的索引稱之為輔助索引，非聚簇索引都是輔助索引，像複合索引、字首索引、唯一索引。輔助索引葉子節點存儲的不再是行的實體位置，而是主鍵值，輔助索引通路資料總是需要二次查找。

【資料庫索引Index系列】資料庫索引，這一篇就夠了熱門系列：目錄1.什麼是索引？為什麼要用索引？2.索引的作用與缺點3.索引的使用場景4.索引的分類與說明5.索引的底層原理6.總結

InnoDB使用的是聚簇索引，将主鍵組織到一棵B+樹中，而行資料就儲存在葉子節點上，若使用"where id = 14"這樣的條件查找主鍵，則按照B+樹的檢索算法即可查找到對應的葉節點，之後獲得行資料。

若對Name列進行條件搜尋，則需要兩個步驟：第一步在輔助索引B+樹中檢索Name，到達其葉子節點擷取對應的主鍵。第二步使用主鍵在主索引B+樹種再執行一次B+樹檢索操作，最終到達葉子節點即可擷取整行資料。（重點在于通過其他鍵需要建立輔助索引）

MyISAM使用的是非聚簇索引，非聚簇索引的兩棵B+樹看上去沒什麼不同，節點的結構完全一緻隻是存儲的内容不同而已，主鍵索引B+樹的節點存儲了主鍵，輔助鍵索引B+樹存儲了輔助鍵。表資料存儲在獨立的地方，這兩顆B+樹的葉子節點都使用一個位址指向真正的表資料，對于表資料來說，這兩個鍵沒有任何差别。由于索引樹是獨立的，通過輔助鍵檢索無需通路主鍵的索引樹。

【資料庫索引Index系列】資料庫索引，這一篇就夠了熱門系列：目錄1.什麼是索引？為什麼要用索引？2.索引的作用與缺點3.索引的使用場景4.索引的分類與說明5.索引的底層原理6.總結

4.6.4對比總結

每次使用輔助索引檢索都要經過兩次B+樹查找，看上去聚簇索引的效率明顯要低于非聚簇索引，這不是多此一舉嗎？聚簇索引的優勢在哪？

1.由于行資料和聚簇索引的葉子節點存儲在一起，同一頁中會有多條行資料，通路同一資料頁不同行記錄時，已經把頁加載到了Buffer中（緩存器），再次通路時，會在記憶體中完成通路，不必通路磁盤。這樣主鍵和行資料是一起被載入記憶體的，找到葉子節點就可以立刻将行資料傳回了，如果按照主鍵Id來組織資料，獲得資料更快。

2.輔助索引的葉子節點，存儲主鍵值，而不是資料的存放位址。好處是當行資料放生變化時，索引樹的節點也需要分裂變化；或者是我們需要查找的資料，在上一次IO讀寫的緩存中沒有，需要發生一次新的IO操作時，可以避免對輔助索引的維護工作，隻需要維護聚簇索引樹就好了。另一個好處是，因為輔助索引存放的是主鍵值，減少了輔助索引占用的存儲空間大小。

注：我們知道一次io讀寫，可以擷取到16K大小的資源，我們稱之為讀取到的資料區域為Page。而我們的B樹，B+樹的索引結構，葉子節點上存放好多個關鍵字（索引值）和對應的資料，都會在一次IO操作中被讀取到緩存中，是以在通路同一個頁中的不同記錄時，會在記憶體裡操作，而不用再次進行IO操作了。除非發生了頁的分裂，即要查詢的行資料不在上次IO操作的換村裡，才會觸發新的IO操作。

3.因為MyISAM的主索引并非聚簇索引，那麼他的資料的實體位址必然是淩亂的，拿到這些實體位址，按照合适的算法進行I/O讀取，于是開始不停的尋道不停的旋轉。聚簇索引則隻需一次I/O。（強烈的對比）

4.不過，如果涉及到大資料量的排序、全表掃描、count之類的操作的話，還是MyISAM占優勢些，因為索引所占空間小，這些操作是需要在記憶體中完成的。

5.當使用主鍵為聚簇索引時，主鍵最好不要使用uuid，因為uuid的值太過離散，不适合排序且可能出線新增加記錄的uuid，會插入在索引樹中間的位置，導緻索引樹調整複雜度變大，消耗更多的時間和資源。建議使用int類型的自增，友善排序并且預設會在索引樹的末尾增加主鍵值，對索引樹的結構影響最小。而且，主鍵值占用的存儲空間越大，輔助索引中儲存的主鍵值也會跟着變大，占用存儲空間，也會影響到IO操作讀取到的資料量。

4.7稠密索引與稀疏索引

在了解稠密索引和稀疏索引之前我們先來了解一下什麼是聚焦索引。在一個檔案中，可以有多個索引，分别基于不同的搜尋碼。如果包含資料記錄的檔案按照某個指定的順序排列，那麼該搜尋碼對應的索引就是聚焦索引。

4.7.1稠密索引

在稠密索引中，檔案中的每個搜尋碼值都對應一個索引值。也就是說，稠密索引為資料記錄檔案的每一條記錄都設一個鍵-指針對。如下圖所示，索引項包括索引值以及指向該搜尋碼的第一條資料記錄的指針，即我們所說的鍵-指針對。

【資料庫索引Index系列】資料庫索引，這一篇就夠了熱門系列：目錄1.什麼是索引？為什麼要用索引？2.索引的作用與缺點3.索引的使用場景4.索引的分類與說明5.索引的底層原理6.總結

4.7.2稀疏索引

在稀疏索引中，隻為搜尋碼的某些值建立索引項。也就是說，稀疏索引為資料記錄檔案的每個存儲塊設一個鍵-指針對，存儲塊意味着塊記憶體儲單元連續。如下圖所示。

5.索引的底層原理

此節我們抛開其他的資料庫索引實作，主講Mysql的索引底層實作。說到Mysql的索引，了解過的人應該知道，其底層是通過B+數來實作的資料結構存儲。

資料存儲結構，決定了資料查找和操作時的效率，包括時間複雜度和空間複雜度。而在取舍的時候，也無非就是時間換空間，空間換時間的權衡罷了。是以，這就很好的解釋了，為什麼Mysql在索引的底層設計上，選用了B+數，而沒有選用B-樹，或是紅黑樹，AVL樹等等其他資料結構。總之，就是使用B+樹作為索引的結構存儲，能在I/O性能上得到一個較大的優勢。

那麼具體優勢在哪裡呢？咱們繼續往下看。

本章我們以B-樹與B+樹的對比，來闡述具體差異和B+樹的優勢。

5.1 B-Tree

B-樹是一種多路自平衡的搜尋樹它類似普通的平衡二叉樹，不同的一點是B-樹允許每個節點有更多的子節點。B-Tree 相對于 AVLTree 縮減了節點個數，使每次磁盤 I/O 取到記憶體的資料都發揮了作用，進而提高了查詢效率。

注：B-Tree就是我們常說的B樹

那麼m階 B-Tree 是滿足下列條件的資料結構：

所有鍵值分布在整顆樹中
搜尋有可能在非葉子結點結束，在關鍵字全集内做一次查找,性能逼近二分查找
每個節點最多擁有m個子樹
根節點至少有2個子樹
分支節點至少擁有m/2顆子樹（除根節點和葉子節點外都是分支節點）
所有葉子節點都在同一層、每個節點最多可以有m-1個key，并且以升序排列

每個節點占用一個磁盤塊，一個節點上有兩個升序排序的關鍵字和三個指向子樹根節點的指針，指針存儲的是子節點所在磁盤塊的位址。兩個關鍵詞劃分成的三個範圍域對應三個指針指向的子樹的資料的範圍域。以根節點為例，關鍵字為 17 和 35，P1 指針指向的子樹的資料範圍為小于 17，P2 指針指向的子樹的資料範圍為 17~35，P3 指針指向的子樹的資料範圍為大于 35。

模拟查找關鍵字 29 的過程：

根據根節點找到磁盤塊 1，讀入記憶體。【磁盤 I/O 操作第 1 次】
比較關鍵字 29 在區間（17,35），找到磁盤塊 1 的指針 P2。
根據 P2 指針找到磁盤塊 3，讀入記憶體。【磁盤 I/O 操作第 2 次】
比較關鍵字 29 在區間（26,30），找到磁盤塊 3 的指針 P2。
根據 P2 指針找到磁盤塊 8，讀入記憶體。【磁盤 I/O 操作第 3 次】
在磁盤塊 8 中的關鍵字清單中找到關鍵字 29。
分析上面過程，發現需要 3 次磁盤 I/O 操作，和 3 次記憶體查找操作。由于記憶體中的關鍵字是一個有序表結構，可以利用二分法查找提高效率。而 3 次磁盤 I/O 操作是影響整個 B-Tree 查找效率的決定因素。

但同時B-Tree也存在問題：

每個節點中有key，也有data，而每一個頁的存儲空間是有限的，如果data資料較大時将會導緻每個節點（即一個頁）能存儲的 key 的數量很小。
當存儲的資料量很大時同樣會導緻 B-Tree 的深度較大，增大查詢時的磁盤 I/O 次數，進而影響查詢效率

5.2 B+Tree

B+Tree 是在 B-Tree 基礎上的一種優化，InnoDB 存儲引擎就是用 B+Tree 實作其索引結構。它帶來的變化點：

B+樹每個節點可以包含更多的節點，這樣做有兩個原因,一個是降低樹的高度。另外一個是将資料範圍變為多個區間，區間越多，資料檢索越快
非葉子節點存儲key，葉子節點存儲key和資料
葉子節點兩兩指針互相連結（符合磁盤的預讀特性），順序查詢性能更高

注：MySQL 的 InnoDB 存儲引擎在設計時是将根節點常駐記憶體的，是以力求達到樹的深度不超過 3，也就是說 I/O 不需要超過 3 次。

通常在B+Tree上有兩個頭指針，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點（即資料節點）之間是一種鍊式環結構。是以可以對 B+Tree 進行兩種查找運算：一種是對于主鍵的範圍查找和分頁查找，另一種是從根節點開始，進行随機查找。

5.3 B-樹和B+樹的差別

B+樹内節點不存儲資料，所有資料存儲在葉節點導緻查詢時間複雜度固定為 log n
B-樹查詢時間複雜度不固定，與 key 在樹中的位置有關，最好為O(1)
B+樹葉節點兩兩相連可大大增加區間通路性，可使用在範圍查詢等
B+樹更适合外部存儲(存儲磁盤資料)。由于内節點無 data 域，每個節點能索引的範圍更大更精确。

6.總結

本章内容其實是我個人對索引這塊的知識點的鞏固與梳理。之前有很多總結，比較零散，趁當下有點時間，是以整理了出來。其中還有很多不足，或是有瑕疵的地方，若有不對之處，盡情拍磚指正。最後，也希望能夠幫助到，正在學習的你，加油！

本部落格皆為學習、分享、探讨為本，歡迎各位朋友評論、點贊、收藏、關注，一起加油！