阿粉相信大家肯定都知道，在數(shù)據(jù)庫中加一定量的索引，會(huì)讓你的查詢語句，從原來的 3 秒縮短到零點(diǎn)幾秒的程度，但是很多人都不知道為什么要加索引，為什么加了索引之后，你的查詢語句就會(huì)起飛呢?今天阿粉來聊一下索引。

索引的類型(常見的)

• 主鍵索引(primary key)：主鍵索引這個(gè)阿粉從剛開始接觸開發(fā)的時(shí)候，就被各種灌輸，表的主鍵就默認(rèn)是索引，不允許出現(xiàn)空值。

• 普通索引(index/normal)：MySQL中基本索引類型，沒有什么限制，允許在定義索引的列中插入重復(fù)值和空值。

• 全文索引(fulltext)：只能在文本類型CHAR,VARCHAR,TEXT類型字段上創(chuàng)建全文索引。MyISAM和InnoDB中都可以使用全文索引。

• 唯一索引(unique)：索引列中的值必須是唯一的，但是允許為空值。

索引的類型肯定不限制于這幾項(xiàng)，既然我們知道分類了，我們接下來再來看看不同索引的創(chuàng)建方式。

不同索引的創(chuàng)建方式

其實(shí)如果你真的不會(huì)去寫 SQL 去創(chuàng)建索引，最簡(jiǎn)單的，Navicat 你總是會(huì)用的吧，圖形化的界面操作，你肯定也是了解的吧，那圖形化直接操作不就好了。

這樣子操作是不是簡(jiǎn)單明了，選擇你想要?jiǎng)?chuàng)建索引的類型，然后指名你想要?jiǎng)?chuàng)建索引的字段，最后再給他加上個(gè)注釋，完美解決，但是我們還是要寫語句來看一下的。

1. 創(chuàng)建普通的索引

ALTER TABLE table_name ADD INDEX index_name (column) 

比如我們有一張表叫做 user 我們想給 user 表中的一個(gè)叫做 phone 字段增加一個(gè)索引，應(yīng)該怎么去寫呢?

ALTER TABLE user ADD INDEX phoneIndex (phone) 

這時(shí)候我們就創(chuàng)建好了一個(gè)索引了，索引的刪除，相對(duì)來說也是非常的簡(jiǎn)單。其實(shí)說是創(chuàng)建索引，實(shí)際上就是給我們?cè)斜碇械哪硞€(gè)字段上增加一個(gè)索引，這個(gè)大家一定得清楚哈，千萬別和 Create 給搞混了。下面阿粉就直接簡(jiǎn)單的給大家稱之為創(chuàng)建吧。

ALTER TABLE testalter_tb1 DROP INDEX index_name 

這樣刪除我們剛才建立的索引就是

ALTER TABLE user DROP INDEX phoneIndex 

這時(shí)候我們就能看到刪除成功了。

> OK 
> 時(shí)間: 0.012s 

2. 創(chuàng)建唯一索引(unique)并刪除

ALTER TABLE user ADD unique phoneIndex (phone) 
ALTER TABLE user DROP INDEX phoneIndex; 

千萬不要想當(dāng)然的認(rèn)為創(chuàng)建的時(shí)候我指定了索引的類型，然后刪除的時(shí)候也執(zhí)行一個(gè) ALTER TABLE user DROP unique phoneIndex; 阿粉親身實(shí)踐，確實(shí)是不成功的。

3. 創(chuàng)建主鍵索引(primary key)并刪除

ALTER TABLE user ADD PRIMARY KEY (phone): 
ALTER TABLE user DROP PRIMARY KEY 

一般我們?cè)诮ū淼臅r(shí)候，都把這個(gè)主鍵索引都建好了，所以使用的場(chǎng)景并不是很多見。

4. 創(chuàng)建全文索引(fulltext)并刪除

創(chuàng)建方式都差不多就是這樣

ALTER TABLE user ADD FULLTEXT phoneIndex (phone) 
ALTER TABLE user DROP INDEX phoneIndex; 

既然我們了解了創(chuàng)建的方式了，那是不是該回歸正題，說說為什么使用索引就會(huì)快，這就得涉及到索引的底層知識(shí)了，

索引的實(shí)現(xiàn)

在沒有索引的情況下，我們查找數(shù)據(jù)只能按照從頭到尾的順序逐行查找，每查找一行數(shù)據(jù)，意味著我們要到到磁盤相應(yīng)的位置去讀取一條數(shù)據(jù)。

如果把查詢一條數(shù)據(jù)分為到磁盤中查詢和比對(duì)查詢條件兩步的話，到磁盤中查詢的時(shí)間會(huì)遠(yuǎn)遠(yuǎn)大于比對(duì)查詢條件的時(shí)間，這意味著在一次查詢中，磁盤io占用了大部分的時(shí)間。更進(jìn)一步地說，一次查詢的效率取絕于磁盤io的次數(shù)，如果我們能夠在一次查詢中盡可能地降低磁盤io的次數(shù)，那么我們就能加快查詢的速度。

所以我們就要開始引入索引，然后分析索引底層是如何實(shí)現(xiàn)查找迅速的。

實(shí)際上索引的底層實(shí)際上就是樹，也就 B 樹和 B+ 樹，也可以稱之為變種的 B+ 樹。大家也都知道 Mysql中最常用的引擎像InnoDB和MyISAM，最終都選擇了B+樹作為索引。

那我們來說說這個(gè)B樹和B+樹。

B-樹，也稱為B樹，是一種平衡的多叉樹(可以對(duì)比一下平衡二叉查找樹)，它比較適用于對(duì)外查找。

畫一個(gè)二階B樹：

二階B樹

那么我們?yōu)槭裁捶Q他為二階 B 樹呢?這個(gè)階數(shù)實(shí)際上就是說一個(gè) 節(jié)點(diǎn) 最多有幾個(gè) 子節(jié)點(diǎn)。

我們上面的圖，X元素，有2個(gè)子節(jié)點(diǎn)，A 元素，又有2個(gè) 子節(jié)點(diǎn) C 和 D ，而 B 元素，又有 2 個(gè)子節(jié)點(diǎn) E F ，也就是說一個(gè)節(jié)點(diǎn)最多有多少個(gè)子節(jié)點(diǎn)，我們就稱它為幾階的樹，通常這個(gè)值一般用 m 來表示。

注意我們所說的，也就是一個(gè)節(jié)點(diǎn)上 最多 的子節(jié)點(diǎn)數(shù)，如果有一個(gè)分支是有三個(gè)節(jié)點(diǎn)，而有一個(gè)是 兩個(gè)節(jié)點(diǎn) ，那我們就稱它為 三階 B 樹。

一顆m階的 B 樹 要滿足什么條件呢?

• 每個(gè)節(jié)點(diǎn)至多可以擁有m棵子樹。
• 根節(jié)點(diǎn)，只有至少有2個(gè)節(jié)點(diǎn)(要么極端情況，就是一棵樹就一個(gè)根節(jié)點(diǎn)，單細(xì)胞生物，即是根，也是葉，也是樹)。
• 非根非葉的節(jié)點(diǎn)至少有的Ceil(m/2)個(gè)子樹(Ceil表示向上取整，圖中3階B樹，每個(gè)節(jié)點(diǎn)至少有2個(gè)子樹，也就是至少有2個(gè)叉)。
• 非葉節(jié)點(diǎn)中的信息包括[n,A0,K1,A1,K2,A2,…,Kn,An]，，其中n表示該節(jié)點(diǎn)中保存的關(guān)鍵字個(gè)數(shù)，K為關(guān)鍵字且Ki
• 從根到葉子的每一條路徑都有相同的長(zhǎng)度，也就是說，葉子節(jié)點(diǎn)在相同的層，并且這些節(jié)點(diǎn)不帶信息，實(shí)際上這些節(jié)點(diǎn)就表示找不到指定的值，也就是指向這些節(jié)點(diǎn)的指針為空。

B樹的查詢過程和二叉排序樹比較類似，從根節(jié)點(diǎn)依次比較每個(gè)節(jié)點(diǎn)，因?yàn)槊總€(gè)節(jié)點(diǎn)中的關(guān)鍵字和左右子樹都是有序的，所以只要比較節(jié)點(diǎn)中的關(guān)鍵字，或者沿著指針就能很快地找到指定的關(guān)鍵字，如果查找失敗，則會(huì)返回葉子節(jié)點(diǎn)，即空指針。

B樹搜索的簡(jiǎn)單偽算法如下：

BTree_Search(node, key) { 
    if(node == null) return null; 
    foreach(node.key) 
    { 
        if(node.key[i] == key) return node.data[i]; 
            if(node.key[i] > key) return BTree_Search(point[i]->node); 
    } 
    return BTree_Search(point[i+1]->node); 
} 
 
data = BTree_Search(root, my_key); 

這就是個(gè)偽算法，寫的不好，大家見諒，那么什么是 B+ 樹呢?

B+ 樹是一種樹數(shù)據(jù)結(jié)構(gòu)，是一個(gè)n叉樹，每個(gè)節(jié)點(diǎn)通常有多個(gè)孩子，一顆B+樹包含根節(jié)點(diǎn)、內(nèi)部節(jié)點(diǎn)和葉子節(jié)點(diǎn)。根節(jié)點(diǎn)可能是一個(gè)葉子節(jié)點(diǎn)，也可能是一個(gè)包含兩個(gè)或兩個(gè)以上孩子節(jié)點(diǎn)的節(jié)點(diǎn)。

B+ 樹通常用于數(shù)據(jù)庫和操作系統(tǒng)的文件系統(tǒng)中。

NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系統(tǒng)都在使用B+樹作為元數(shù)據(jù)索引。

B+ 樹的特點(diǎn)是能夠保持?jǐn)?shù)據(jù)穩(wěn)定有序，其插入與修改擁有較穩(wěn)定的對(duì)數(shù)時(shí)間復(fù)雜度。

B+ 樹元素自底向上插入。

那 B+ 樹又有哪些比較顯著的特點(diǎn)呢?

• 每個(gè)父節(jié)點(diǎn)的元素都出現(xiàn)在了子節(jié)點(diǎn)中，分別是子節(jié)點(diǎn)最大或者最小的元素。
• 在上面的這一棵樹中，根節(jié)點(diǎn)元素8是子節(jié)點(diǎn)258的最大的元素，根元素15也是。這時(shí)候要注意了，根節(jié)點(diǎn)最大的元素等同于整個(gè)B+樹的最大的元素，以后無論是怎么插入或者是刪除，始終都要保持最大的元素在根節(jié)點(diǎn)中。
• 葉子節(jié)點(diǎn)，因?yàn)楦腹?jié)點(diǎn)的元素都出現(xiàn)在了子節(jié)點(diǎn)當(dāng)中，因此所有的葉子節(jié)點(diǎn)包含了全量的元素信息。

B+樹與B樹差異

• 有k個(gè)子節(jié)點(diǎn)的節(jié)點(diǎn)必然有k個(gè)元素
• 非葉子節(jié)點(diǎn)僅具有索引作用，跟記錄有關(guān)的信息均存放在葉子節(jié)點(diǎn)中
• 樹的所有葉子節(jié)點(diǎn)構(gòu)成一個(gè)有序鏈表，可以按照元素排序的次序遍歷全部記錄
• B樹和B+樹的區(qū)別在于，B+樹的非葉子節(jié)點(diǎn)只包含導(dǎo)航信息，不包含實(shí)際的值，所有的葉子節(jié)點(diǎn)和相連的節(jié)點(diǎn)使用鏈表相連，便于區(qū)間查找和遍歷。

說到這里，就會(huì)有讀者開始想，說了半天，沒有說到重點(diǎn)，為什么加了索引就快呢?

剛才阿粉也說了，數(shù)據(jù)庫讀取數(shù)據(jù)，是從磁盤上通過 IO 來進(jìn)行數(shù)據(jù)的操作，一次磁盤IO操作可以取出物理存儲(chǔ)中相鄰的一大片數(shù)據(jù)，如果查詢的索引數(shù)據(jù)(就是B+樹中從根節(jié)點(diǎn)一直到葉子節(jié)點(diǎn)整個(gè)過程中查詢的節(jié)點(diǎn)數(shù))都集中在該區(qū)域，那么只需要一次磁盤IO，否則就需要多次磁盤IO。

這么說是不是就相對(duì)的簡(jiǎn)單明了了。

再舉出一個(gè)簡(jiǎn)單的例子：

比如我們想要查詢 user 表中 name 為 xiaohong 的數(shù)據(jù)，在我們寫 SQL 的時(shí)候

select * from user where name = 'xiaohong' 

這時(shí)候沒有索引的情況下，數(shù)據(jù)庫直接就把整個(gè)表全部掃描一遍，然后去找 name = ‘xiaohong’ 的數(shù)據(jù)

而我們給他加上索引之后，會(huì)通過索引查找去查詢名為 ‘xiaohong‘ 的數(shù)據(jù)，因?yàn)樵撍饕呀?jīng)按照字母順序排列，因此要查找名為 ‘xiaohong' 的記錄時(shí)會(huì)快很多。

大家明白了么?就像是一個(gè)詞典，我把 x 開頭的數(shù)據(jù)都給你羅列出來，然后你從 x 開頭的數(shù)據(jù)中去尋找，和你直接沒有任何處理，直接一頁一頁的翻詞典的速度，哪一個(gè)更快，相信大家也都明白了吧。