者 | Amazing10

責編 | 屠敏

頭圖 | CSDN 下載自視覺中國

本文為「業(yè)余碼農(nóng)」投稿

索引的概念基本所有人都會遇到過，就算沒有了解過數(shù)據(jù)庫中的索引，在生活中也不可避免的接觸到。比方說書籍的目錄，字典的查詢頁，圖書館的科目檢索等等。其實這些都是一種索引，并且所起到的作用大同小異。

而對于數(shù)據(jù)庫而言，只不過是將索引的概念抽象出來，讓建立索引的過程更為靈活而自由，從而可以在不同的場景下優(yōu)化數(shù)據(jù)庫的查詢效率。

索引在數(shù)據(jù)庫的實際應用場景中十分普遍，數(shù)據(jù)庫的優(yōu)化也離不開對索引的優(yōu)化。同時，索引相關(guān)的知識也是面試高頻的考點之一，是應試者理論結(jié)合現(xiàn)實最為直接的體現(xiàn)。

因此，本文將從基礎理論出發(fā)，介紹 MySQL 按照邏輯角度的索引分類和實現(xiàn)，通過數(shù)據(jù)結(jié)構(gòu)的實現(xiàn)原理闡述不同結(jié)構(gòu)對建立索引帶來的優(yōu)劣勢，同時針對物理存儲的方式對索引的組織特點和應用場景進行分析。最后根據(jù)不同的應用場景盡可能的探究如何建立起高性能的索引。文章結(jié)構(gòu)如下：

概念

什么是索引？

索引似乎并沒有十分明確的定義，更多的是一種定性的描述。簡單來講，索引就是一種將數(shù)據(jù)庫中的記錄按照特殊形式存儲的數(shù)據(jù)結(jié)構(gòu)。通過索引，能夠顯著地提高數(shù)據(jù)查詢的效率，從而提升服務器的性能。

專業(yè)一點來說呢，索引是一個排好序的列表，在這個列表中存儲著索引的值和包含這個值的數(shù)據(jù)所在行的物理地址。在數(shù)據(jù)庫十分龐大的時候，索引可以大大加快查詢的速度，這是因為使用索引后可以不用掃描全表來定位某行的數(shù)據(jù)，而是先通過索引表找到該行數(shù)據(jù)對應的物理地址然后訪問相應的數(shù)據(jù)。

說起索引，其實并不是 MySQL 數(shù)據(jù)庫特有的機制，在關(guān)系型數(shù)據(jù)庫中都會有類似不同的實現(xiàn)。這里我們也只是討論 MySQL 數(shù)據(jù)庫中的索引實現(xiàn)。

事實上，說是 MySQL 的索引其實并不準確。因為在 MySQL 中，索引是在存儲引擎層而不是服務器層實現(xiàn)的。這意味著我們所討論的索引準確來說是 InnoDB 引擎或 MyISAM 引擎或其它存儲引擎所實現(xiàn)的。

所以索引即便是在 MySQL 中也沒有統(tǒng)一的標準，不同存儲引擎的所實現(xiàn)的索引工作方式也并不一樣。不是所有的存儲引擎都支持相同類型的索引，即便是多個引擎支持同一種類型的索引，其底層的實現(xiàn)也可能不同。

為什么需要索引

說了這么多，索引似乎就是給數(shù)據(jù)庫添加了一個「目錄頁」，能夠方便查詢數(shù)據(jù)。但是索引的作用就僅此而已了嗎，為什么需要大費周章的建立并優(yōu)化索引？

說個題外話，我其實查字典從來都不喜歡查目錄頁，無論是查中文還是英文。因為覺得那樣很慢，一個個找索引，效率很低。我習慣用的方式就是直接翻開字典，根據(jù)翻開的位置進行前后調(diào)整。比方說我想找「醬 JIANG」字，會先隨機翻到一頁，可能是「F」開頭，在「J」前面，就往后翻一點；如果隨機翻到「L」，那就往前翻一點。重復直至找到。

這大概就是類似于二分查找的方式，看起來好像是擺脫了索引的束縛，并且也能夠獲得比較高的查詢效率。但是其實轉(zhuǎn)念一想，在計算機的運行處理中，「一個個找索引」這個過程其實非常快，不能跟我們手動比對偏旁部首的效率相提并論。同時，為什么我可以直接翻開字典根據(jù)字母進行調(diào)整呢，這其實不就是因為我的腦子里存在一個大概的「索引表」，知道每個字母大概對應于字典的哪一個位置。雖然是模糊的，但卻是真實存在的。（好不容易強行解釋了一波...）

如此一來，可以看出索引的一大好處是如其概念中所提及的，使用索引后可以不用掃描全表來定位某行的數(shù)據(jù)，而是先通過索引表找到該行數(shù)據(jù)對應的物理地址然后訪問相應的數(shù)據(jù)。這樣的方式自然減少了服務器在響應時所需要對數(shù)據(jù)庫掃描的數(shù)據(jù)量。

不僅如此，在執(zhí)行數(shù)據(jù)庫的范圍查詢時，若不使用索引，那么MySQL會先掃描數(shù)據(jù)庫的所有行數(shù)據(jù)并從中篩選出目標范圍內(nèi)的行記錄，將這些行記錄進行排序并生成一張臨時表，然后通過臨時表返回用戶查詢的目標行記錄。這個過程會涉及到臨時表的建立和行記錄的排序，當目標行記錄較多的時候，會大大影響范圍查詢的效率。

所以當添加索引時，由于索引本身具有的順序性，使得在進行范圍查詢時，所篩選出的行記錄已經(jīng)排好序，從而避免了再次排序和需要建立臨時表的問題。

同時，由于索引底層實現(xiàn)的有序性，使得在進行數(shù)據(jù)查詢時，能夠避免在磁盤不同扇區(qū)的隨機尋址。使用索引后能夠通過磁盤預讀使得在磁盤上對數(shù)據(jù)的訪問大致呈順序的尋址。這本質(zhì)上是依據(jù)局部性原理所實現(xiàn)的。

局部性原理：當一個數(shù)據(jù)被用到時，其附近的數(shù)據(jù)也通常會馬上被使用。程序運行期間所需要的數(shù)據(jù)通常比較集中。由于磁盤順序讀取的效率很高(不需要尋道時間，只需很少的旋轉(zhuǎn)時間) ，因此對于具有局部性的程序來說，磁盤預讀可以提高I/O效率。

磁盤預讀要求每次都會預讀的長度一般為頁的整數(shù)倍。而且數(shù)據(jù)庫系統(tǒng)將一個節(jié)點的大小設為等于一個頁，這樣每個節(jié)點只需要一次 I/O 就可以完全載入。這里的頁是通過頁式的內(nèi)存管理所實現(xiàn)的，概念在這里簡單提一嘴。

分頁機制就是把內(nèi)存地址空間分為若干個很小的固定大小的頁，每一頁的大小由內(nèi)存決定。這樣做是為了從虛擬地址映射到物理地址，提高內(nèi)存和磁盤的利用率。

所以呢，總結(jié)一下。索引的存在具有很大的優(yōu)勢，主要表現(xiàn)為以下三點：

• 索引大大減少了服務器需要掃描的數(shù)據(jù)量

• 索引可以幫助服務器避免排序和臨時表

• 索引可以將隨機 I/O 變成順序 I/O

以上三點能夠大大提高數(shù)據(jù)庫查詢的效率，優(yōu)化服務器的性能。因此一般來說，為數(shù)據(jù)庫添加高效的索引對數(shù)據(jù)庫進行優(yōu)化的重要工作之一。

不過，凡事都有兩面性。索引的存在能夠帶來性能的提升，自然在其它方面也會付出額外的代價。

索引本身以表的形式存儲，因此會占用額外的存儲空間；

索引表的創(chuàng)建和維護需要時間成本，這個成本隨著數(shù)據(jù)量增大而增大；

構(gòu)建索引會降低數(shù)據(jù)的修改操作（刪除，添加，修改）的效率，因為在修改數(shù)據(jù)表的同時還需要修改索引表；

所以對于非常小的表而言，使用索引的代價會大于直接進行全表掃描，這時候就并不一定非得使用索引了。沒辦法，成年人的世界總是這么的趨利避害。

邏輯分類

從邏輯的角度來對索引進行劃分的話，可以分為單列索引、全文索引、組合索引和空間索引。其中單列索引又可分為主鍵索引、唯一索引和普通索引。這里的邏輯可以理解為從 SQL 語句的角度，或者是從數(shù)據(jù)庫關(guān)系表的角度。下面就簡單介紹這些索引的作用和用法，以及在修改表的時候如何添加索引。

主鍵索引

即主索引，根據(jù)主鍵建立索引，不允許重復，不允許空值；

主鍵：數(shù)據(jù)庫表中一列或列組合（字段）的值，可唯一標識表中的每一行。

加速查詢 + 列值唯一（不可以有）+ 表中只有一個

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col');

唯一索引

用來建立索引的列的值必須是唯一的，允許空值。唯一索引不允許表中任何兩行具有相同的索引值。比方說，在 employee 表中職員的姓 name 上創(chuàng)建了唯一索引，那么就表示任何兩個員工都不能同姓。

加速查詢 + 列值唯一（可以有）

ALTER TABLE 'table_name' ADD UNIQUE index_name('col');

普通索引

用表中的普通列構(gòu)建的索引，沒有任何限制。

僅加速查詢

ALTER TABLE 'table_name' ADD INDEX index_name('col');

全文索引

用大文本對象的列構(gòu)建的索引

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col');

組合索引

用多個列組合構(gòu)建的索引，這多個列中的值不允許有空值。

多列值組成一個索引，專門用于組合搜索，其效率大于索引合并。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');

在對多列組合建立索引時，會遵循「最左前綴」原則。

最左前綴原則：顧名思義，就是最左優(yōu)先，上例中我們創(chuàng)建了 (col1, col2, col3) 多列索引,相當于創(chuàng)建了 (col1) 單列索引，(col1, col2) 組合索引以及 (col1, col2, col3) 組合索引。

所以當我們在創(chuàng)建多列索引時，要根據(jù)業(yè)務場景，將 where 子句中使用最頻繁的一列放在最左邊。

空間索引

對空間數(shù)據(jù)類型的字段建立的索引，底層可通過 R 樹實現(xiàn)。只不過使用較少，了解即可。

實現(xiàn)原理

我們知道，索引的底層本身就是通過數(shù)據(jù)結(jié)構(gòu)來進行實現(xiàn)的。那么根據(jù)其底層的結(jié)構(gòu)，常見的索引類型可分為哈希索引，BTree 索引，B+Tree 索引等。這里我們就主要來介紹這三種索引背后的實現(xiàn)機制。

哈希索引

顧名思義，哈希索引是通過哈希表實現(xiàn)的。哈希表的特點在之前的文章「九大數(shù)據(jù)結(jié)構(gòu)」中已經(jīng)詳細介紹過。通過哈希表的鍵值之間的對應關(guān)系，能夠在查詢時精確匹配索引的所有列。哈希索引將所有的根據(jù)索引列計算出來的哈希碼存儲在索引中，同時將指向每個數(shù)據(jù)行的指針保存在哈希表中。

上圖是通過哈希索引查詢行數(shù)據(jù)的示意圖，可以發(fā)現(xiàn)哈希索引同樣會發(fā)生哈希沖突，并且是通過鏈地址法解決沖突的。當發(fā)送沖突時，還需要對鏈表進行遍歷對比，才能夠找到最終的結(jié)果。

在 MySQL 中，只有 Memory 存儲引擎顯式的支持哈希索引，而innodb是隱式支持哈希索引的。

這里的隱式支持是指，innodb引擎有一個特殊的功能 “自適應哈希索引”，當innodb注意到一些索引值被使用的非常頻繁時，且符合哈希特點（如每次查詢的列都一樣），它會在內(nèi)存中基于 B-Tree 索引之上再創(chuàng)建一個哈希索引。這樣就讓 BTree 索引也具有哈希索引的一些有點。這是一個完全自動的、內(nèi)部的行為。

由于哈希結(jié)構(gòu)的特殊性，其用于非常高的檢索效率，通過哈希函數(shù)的映射可以一步到位。但是同樣也是因為結(jié)構(gòu)的特殊，導致哈希索引只適用于某些特定的場合。哈希索引的限制[1]：

1. 不支持范圍查詢，比如 WHERE a > 5；只支持等值比較查詢，包括＝、IN 、<=>

2. 無法被用來避免數(shù)據(jù)的排序操作；因為經(jīng)過了哈希函數(shù)的映射過程，使得丟失了哈希前后的大小關(guān)系，從而無法按照索引值的順序存儲。

3. 不支持部分索引列的匹配查找，因為哈希索引始終使用索引列的全部內(nèi)容來計算哈希值。例如在數(shù)據(jù)列 (A, B) 上建立哈希索引，如果查詢只有數(shù)據(jù)列 A，則無法使用該索引。

4. 無法避免表掃描。因為當出現(xiàn)哈希沖突的時候，存儲引擎必須遍歷鏈表（拉鏈法）中所有的行指針，逐行進行比較，直到找到所有符合條件的行。

5. 哈希沖突很多的情況下，其索引維護的代價很高，并且性能并不一定會比 BTree 索引高。

BTree 索引

BTree 實際上是一顆多叉平衡搜索樹。從名字可以看出，BTree 首先是一顆多叉搜索樹，這意味著它是具有順序的；其次 BTree 還是平衡的，這意味著它的左右子樹高度差小于等于1。

事實上一顆 BTree 需要滿足以下幾個條件：

每個葉子結(jié)點的高度都是一樣的；

每個非葉子結(jié)點由 n-1 個 key 和 n 個指針 point 組成，其中 d<=n<=2d, key 和 point 相互間隔，結(jié)點兩端一定是 key；

葉子結(jié)點指針都為 ；

非葉子結(jié)點的key都是 [key, data] 二元組，其中 key 表示作為索引的鍵，data 為鍵值所在行的數(shù)據(jù)；

一顆常見的BTree樹見下圖。

這是一顆三階的BTree，可通過鍵值的大小排序進行數(shù)據(jù)的查詢和檢索，其中葉子節(jié)點的指針都為空，因此省略沒畫。從上圖可以發(fā)現(xiàn)，BTree 的樹形狀相較于我們之前常見的二叉樹等結(jié)構(gòu)，更為扁平和矮胖。

之所以這樣設計，還是跟磁盤讀取的特點有關(guān)。我們知道在建立索引時，也是需要占據(jù)物理空間的。而實際上當數(shù)據(jù)量比較大的時候，索引文件的大小也十分嚇人。考慮到一個表上可能有多個索引、組合索引、數(shù)據(jù)行占用更小等情況，索引文件的大小可能達到物理盤中數(shù)據(jù)的1/10，甚至可達到1/3。

這就意味著索引無法全部裝入內(nèi)存之中。當通過索引對數(shù)據(jù)進行訪問時，不可避免的需要對磁盤進行讀寫訪問。同時我們還知道，內(nèi)存的讀寫速度是磁盤的幾個數(shù)量級。因此在對索引結(jié)構(gòu)進行設計時要盡可能的減少對磁盤的讀寫次數(shù)，也就是所謂的磁盤 I/O 次數(shù)。

這也就是索引會采用 BTree 這種扁平樹結(jié)構(gòu)的原因，樹的層數(shù)越少，磁盤I/O的次數(shù)自然就越少。不僅如此，我們上面提到過磁盤預讀的局部性原理。根據(jù)這個原理再加上頁表機制，能夠在進行磁盤讀取的時候更大化的提升性能。

BTree 相較于其它的二叉樹結(jié)構(gòu)，對磁盤的 I/O 次數(shù)已經(jīng)非常少了。但是在實際的數(shù)據(jù)庫應用中仍有些問題無法解決。

一是無法定位到數(shù)據(jù)行。通過 BTree 可以根據(jù)主鍵的排序定位出主鍵的位置，但是由于數(shù)據(jù)表的記錄有多個字段，僅僅定位到主鍵是不夠，還需要定位到數(shù)據(jù)行。雖然這個問題可以通過在 BTree 的節(jié)點中存儲數(shù)據(jù)行或者增加定位的字段，但是這種方式會使得 BTree 的深度大幅度提高，從而也導致 I/O 次數(shù)的提高。

二是無法處理范圍查詢。在實際的應用中，數(shù)據(jù)庫范圍查詢的頻率非常高，而 BTree 只能定位到一個索引位置。雖然可以通過先后查詢范圍的左右界獲得，但是這樣的操作實際上無法很好的利用磁盤預讀的局部性原理，先后查詢可能會造成通過預讀取的物理地址離散，使得 I/O 的效率并不高。

三是當數(shù)據(jù)量一大時，BTree的高度依舊會變得很高，搜索效率還是會大幅度的下降。

問題總是推動改進的前提。基于以上的問題考慮，就出現(xiàn)了對 BTree 的優(yōu)化版本，也就是 B+Tree。

B+Tree索引

B+Tree 一看就是在 BTree 的基礎上做了改進，那么到底改變了什么呢。廢話不多說，先上圖。

上圖實際上是一種帶有順序索引的 B+Tree，與最基本的 B+Tree 的區(qū)別就在于葉子節(jié)點是否通過指針相連。一般數(shù)據(jù)庫中常用的結(jié)構(gòu)都是這種帶有順序索引的 B+Tree。后文探討的也都是帶順序索引的 B+Tree 結(jié)構(gòu)。

對比 BTree 和 B+Tree，我們可以發(fā)現(xiàn)二者主要在以下三個方面上的不同：

1. 非葉子節(jié)點只存儲鍵值信息，不再存儲數(shù)據(jù)。

2. 所有葉子節(jié)點之間都有一個鏈指針，指向下一個葉子節(jié)點。

3. 數(shù)據(jù)都存放在葉子節(jié)點中。

看著 B+Tree，像不像是一顆樹與一個有序鏈表的結(jié)合體。因而其實 B+Tree 也就是帶有樹和鏈表的部分優(yōu)勢。樹結(jié)構(gòu)使得有序檢索更為簡單，I/O 次數(shù)更少；有序鏈表結(jié)構(gòu)使得可以按照鍵值排序的次序遍歷全部記錄。

B+Tree 在作為索引結(jié)構(gòu)時能夠帶來的好處有：

一，I/O 次數(shù)更少。這是因為上文也說過，BTree 的節(jié)點是存放在內(nèi)存頁中的。那么在相同的內(nèi)存頁大小（一般為4k）的情況下，B+Tree 能夠存儲更多的鍵值，那么整體樹結(jié)構(gòu)的高度就會更小，需要的 I/O 次數(shù)也就越小。

二，數(shù)據(jù)遍歷更為方便。這個優(yōu)勢很明顯是由有序鏈表帶來的。通過葉子節(jié)點的鏈接，使得對所有數(shù)據(jù)的遍歷只需要在線性的鏈表上完成，這就非常適合區(qū)間檢索和范圍查詢。

三，查詢性能更穩(wěn)定。這自然是由于只在葉子節(jié)點存儲數(shù)據(jù)，所以所有數(shù)據(jù)的查詢都會到達葉子節(jié)點，同時葉子節(jié)點的高度都相同，因此理論上來說所有數(shù)據(jù)的查詢速度都是一致的。

正是由于 B+Tree 優(yōu)秀的結(jié)構(gòu)特性，使得常用作索引的實現(xiàn)結(jié)構(gòu)。在 MySQL 中，存儲引擎 MyISAM 和 InnoDB 都分別以 B+Tree 實現(xiàn)了響應的索引設計。

物理存儲

雖說 B+Tree 結(jié)構(gòu)都可以用在 MyISAM 和 InnoDB，但是這二者對索引的在物理存儲層次的實現(xiàn)方式卻不相同。InnoDB 實現(xiàn)的是聚簇索引，而 MyISAM 實現(xiàn)的卻是非聚簇索引。在介紹聚簇索引之前，我們需要先了解以下啥是佩奇，不對，是啥是「主鍵索引」和「輔助索引」。

其實概念很簡單。我們剛剛不是在講 B+Tree 的時候說過，樹的非葉子節(jié)點只存儲鍵值。沒錯就是這個鍵值，當這個鍵值是數(shù)據(jù)表的主鍵時，那么所建立的就是主鍵索引；當這個鍵值是其它字段的時候，就是輔助索引。因而可以得出，主鍵索引只能有一個，而輔助索引卻可以有很多個。

聚簇索引和非聚簇索引的區(qū)別也就是根據(jù)其對應的主鍵索引和輔助索引的不同特點而實現(xiàn)的。

聚簇索引

說回聚簇索引。先丟個定義。

聚簇索引的主鍵索引的葉子結(jié)點存儲的是鍵值對應的數(shù)據(jù)本身；輔助索引的葉子結(jié)點存儲的是鍵值對應的數(shù)據(jù)的主鍵鍵值。

這句話的信息量挺大的。首先，分析第一句話，主鍵索引的葉子節(jié)點存儲的是鍵值對應的數(shù)據(jù)本身。

我們知道，主鍵索引存儲的鍵值就是主鍵。那么也就是說，聚簇索引的主鍵索引，在葉子節(jié)點中存儲的是主鍵和主鍵對應的數(shù)據(jù)。數(shù)據(jù)和主鍵索引是存儲在一起的，一起作為葉子節(jié)點的一部分。

然后，分析第二句話，輔助索引的葉子結(jié)點存儲的是鍵值對應的數(shù)據(jù)的主鍵鍵值。

我們又知道，輔助索引存儲的鍵值是非主鍵的字段。那就也就是說，通過輔助索引，可以找到非主鍵字段對應的數(shù)據(jù)行中的主鍵。

重點來了。當然主鍵索引和輔助索引一結(jié)合，能干啥呢。當直接采用主鍵進行檢索時，可通過主鍵索引直接獲得數(shù)據(jù)；而當采用非主鍵進行檢索時，先需要通過輔助索引來獲得主鍵，然后再通過這個主鍵在主鍵索引中找到對應的數(shù)據(jù)行。

舉個例子吧。假設有這么一個數(shù)據(jù)表。

那么采用聚簇索引的存儲方式，對應的主鍵索引為：（主鍵為ID）

對應的輔助索引為：（鍵值為Name，大概的意思）：

所以當使用where ID=7這樣的條件查找主鍵，則按照B+樹的檢索算法即可查找到對應的葉節(jié)點，之后獲得行數(shù)據(jù)。對Name列進行條件搜索，則需要兩個步驟：第一步在輔助索引B+樹中檢索Name，到達其葉子節(jié)點獲取對應的主鍵。第二步使用主鍵在主鍵索引B+樹種再執(zhí)行一次B+樹檢索操作，最終到達葉子節(jié)點即可獲取整行數(shù)據(jù)。

最后把以上過程整理總結(jié)一下，聚簇索引實際上的過程就分為以下兩個過程。現(xiàn)在這個圖應該能夠看懂了吧。

非聚簇索引

學完了聚簇索引，非聚簇索引就簡單多啦。同樣，先上定義。

非聚簇索引的主鍵索引和輔助索引幾乎是一樣的，只是主索引不允許重復，不允許空值，他們的葉子結(jié)點都存儲指向鍵值對應的數(shù)據(jù)的物理地址。

與聚簇索引來對比著看，上面的定義能夠說明什么呢。首先，主鍵索引和輔助索引的葉子結(jié)點都存儲著鍵值對應的數(shù)據(jù)的物理地址，這說明無論是主鍵索引還是輔助索引都能夠通過直接獲得數(shù)據(jù)，而不需要像聚簇索引那樣在檢索輔助索引時還得多繞一圈。

同時還說明一個點，葉子結(jié)點存儲的是物理地址，那么表示數(shù)據(jù)實際上是存在另一個地方的，并不是存儲在B+樹的結(jié)點中。這說明非聚簇索引的數(shù)據(jù)表和索引表是分開存儲的。

同樣，對非聚簇索引的檢索過程來個總結(jié)。

無論是主鍵索引還是輔助索引的檢索過程，都只需要通過相應的 B+Tree 進行搜索即可獲得數(shù)據(jù)對應的物理地址，然后經(jīng)過依次磁盤 I/O 就可訪問數(shù)據(jù)。

對比聚簇索引和非聚簇索引，可以發(fā)現(xiàn)二者最主要的區(qū)別就是在于是否在 B+Tree 的節(jié)點中存儲數(shù)據(jù)，也就是數(shù)據(jù)和索引是否存儲在一起。這個區(qū)別導致最大的問題就是聚簇索引的索引的順序和數(shù)據(jù)本身的順序是相同的，而非聚簇索引的順序跟數(shù)據(jù)的順序沒有啥關(guān)系。

索引優(yōu)化

介紹了這么多的索引，其實最終都是為了建立高性能的索引策略，對數(shù)據(jù)庫中的索引進行優(yōu)化。索引的優(yōu)化有很多角度，針對特定的業(yè)務場景可采用不同的優(yōu)化策略。這里考慮到文章篇幅，就不具體介紹，下次再出一篇專門講索引優(yōu)化的文章。簡單列舉一下在進行優(yōu)化時可以考慮的幾個方向：

1 獨立的列。索引列不能是表達式的一部分，也不能是函數(shù)的參數(shù)。

2 前綴索引和索引選擇性。這二者實際上是相互制約的關(guān)系，制約條件在于前綴的長度。一般應選擇足夠長的前綴以保證較高的選擇性（保證查詢效率），同時又不能太長以便節(jié)省空間。

3 盡量使用覆蓋索引。覆蓋索引是指一個索引包含所有需要查詢的字段的值，這樣在查詢時只需要掃描索引而無須再去讀取數(shù)據(jù)行，從而極大的提高性能。

4 使用索引掃描來做排序。要知道，掃描索引本身是很快的，在設計索引時，可盡可能的使用同一個索引既滿足排序，又滿足查找行數(shù)據(jù)。

最后，在建立索引時給幾個小貼士：

1 優(yōu)先使用自增key作為主鍵

2 記住最左前綴匹配原則

3 索引列不能參與計算

4 選擇區(qū)分度高的列作索引

5 能擴展就不要新建索引

總結(jié)

索引的概念和原理是我們在了解和精通數(shù)據(jù)庫過程中無法逃避的重點，而事實上建立高性能的索引對實際的應用場景也具有重要意義。本文的目的依舊是由淺入深的介紹索引這么個東西，從概念到實現(xiàn)再到最終的優(yōu)化策略。

點到為止，學無止境。掌握了基本的理論和概念后，還需要在實際的服務器開發(fā)場景中針對具體的問題和服務進行索引優(yōu)化方案的設計和使用。功力不夠，仍需努力。

Reference

• 高性能 MySQL，Baron Schwartz 等人著，電子工業(yè)出版社

公眾號碼海系列文章：

• https://www.jianshu.com/p/9e9aca844c13

• https://www.runoob.com/mysql/mysql-index.html

• https://www.cnblogs.com/Aiapple/p/5693239.html

• https://blog.csdn.net/tongdanping/article/details/79878302

• https://www.cnblogs.com/igoodful/p/9361500.html

者：Jia-Xin

cnblogs.com/YangJiaXin/p/11153579.html

前言

• 只有Innodb和myisam存儲引擎能用全文索引（innodb支持全文索引是從mysql5.6開始的）
• char、varchar、text類型字段能創(chuàng)建全文索引（fulltext index type）
• 全文索引的基于關(guān)鍵詞的，如何區(qū)分不同的關(guān)鍵詞了，就要用到分詞（stopword）
• 英文單詞用空格，逗號進行分詞；中文分詞不方便（一個句子不知道怎樣區(qū)分不同的關(guān)鍵詞）
• 內(nèi)置分詞解析器ngram支持中文，日文，韓文（將句子分成固定數(shù)字的短語）
• 當對表寫入大量數(shù)據(jù)時，寫入數(shù)據(jù)后再創(chuàng)建全文索引的速度更快（減少了維護索引的開銷）
• 全文索引的原理的倒排索引（一種數(shù)據(jù)結(jié)構(gòu)），一般利用關(guān)聯(lián)數(shù)組，在輔助表中存儲單詞與文檔中所在位置的映射

使用

用MATCH() … AGAINST 方式來進行搜索

match()表示搜索的是那個列，against表示要搜索的是那個字符串

查看默認的分詞（以這些詞來區(qū)分不同的關(guān)鍵詞）；也可以自定義分詞，以這些詞來區(qū)分不同的關(guān)鍵詞SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

如

三種類型的全文搜索方式

natural language search（自然語言搜索）

通過MATCH AGAINST 傳遞某個特定的字符串來進行檢，默認方式

boolean search（布爾搜索）

為檢索的字符串增加操作符，如“+”表示必須包含，"-"不包含，"*" 表示通配符，即使傳遞的字符串較小或出現(xiàn)在停詞中，也不會被過濾掉

query expansion search（查詢擴展搜索）

搜索字符串用于執(zhí)行自然語言搜索，然后，搜索返回的最相關(guān)行的單詞被添加到搜索字符串，并且再次進行搜索，查詢將返回來自第二個搜索的行

相關(guān)參數(shù)

配置相關(guān)參數(shù)

innodb_ft_min_token_size默認3，表示最小3個字符作為一個關(guān)鍵詞，增大該值可減少全文索引的大小

innodb_ft_max_token_size默認84，表示最大84個字符作為一個關(guān)鍵詞，限制該值可減少全文索引的大小

ngram_token_size默認2，表示2個字符作為內(nèi)置分詞解析器的一個關(guān)鍵詞，如對“abcd”建立全文索引，關(guān)鍵詞為'ab'，'bc'，'cd'

當使用ngram分詞解析器時，innodb_ft_min_token_size和innodb_ft_max_token_size 無效

注意 這三個參數(shù)均不可動態(tài)修改，修改了這些參數(shù)，需重啟MySQL服務，并重新建立全文索引

測試innodb引擎使用全文索引

準備

1、目標

• 查詢文章中是否含有某個關(guān)鍵詞；一系列文章出現(xiàn)某個關(guān)鍵詞的次數(shù)
• 查詢文章的標題是否含有某個關(guān)鍵詞

2、設置以下參數(shù)減少磁盤IO壓力

SET GLOBAL sync_binlog=100;
SET GLOBAL innodb_flush_log_at_trx_commit=2;

3、導入1kw 數(shù)據(jù)進行測試全文索引

該數(shù)據(jù)來源網(wǎng)上搜索

https://pan.baidu.com/s/1aaB1R3bkBGZRMEx0o6T61w 提取碼：60l7

4、某個文章表 的結(jié)構(gòu)

使用myloader 多線程導入測試數(shù)據(jù)

-- 先把測試數(shù)據(jù)進行解壓
tar -zxf mydumper_dump_article.tar.gz
time myloader -u $user -p $passwd -S $socket -t 32 -d /datas/dump_article -v 3

5、導入數(shù)據(jù)后總數(shù)據(jù)量和數(shù)據(jù)文件、索引文件大小

SELECT COUNT(*) FROM `article`;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (7.85 sec)

SELECT     table_name,   CONCAT(FORMAT(SUM(data_length) / 1024 / 1024,2),'M') AS dbdata_size,   CONCAT(FORMAT(SUM(index_length) / 1024 / 1024,2),'M') AS dbindex_size,   CONCAT(FORMAT(SUM(data_length + index_length) / 1024 / 1024 / 1024,2),'G') AS `db_size(G)`,   AVG_ROW_LENGTH,table_rows,update_time FROM   information_schema.tables WHERE table_schema = DATABASE() and table_name='article';
+------------+-------------+--------------+------------+----------------+------------+---------------------+
| table_name | dbdata_size | dbindex_size | db_size(G) | AVG_ROW_LENGTH | table_rows | update_time         |
+------------+-------------+--------------+------------+----------------+------------+---------------------+
| article    | 3,710.00M   | 1,003.00M    | 4.60G      |            414 |    9388739 | 2019-07-05 15:31:37 |
+------------+-------------+--------------+------------+----------------+------------+---------------------+

使用默認方式創(chuàng)建全文索引

1、該表已有關(guān)鍵詞字段（對文章內(nèi)容的簡述），并以“,”作為分詞符

2、不建全文索引時搜索某個關(guān)鍵詞

需要進行全表掃描

3、對關(guān)鍵詞字段創(chuàng)建全文索引（以 , 作為分詞）

my.cnf配置文件中設置innodb_ft_min_token_size，并重啟MySQL服務（最小兩個字符作為一個關(guān)鍵詞，默認三個字符作為一個關(guān)鍵詞）

[mysqld]
innodb_ft_min_token_size=2

3.1 設置自定義stopwords（即分詞）

USE mysql;
CREATE TABLE my_stopwords(VALUE VARCHAR(30)) ENGINE = INNODB;
INSERT INTO my_stopwords(VALUE) VALUE (',');
SET GLOBAL innodb_ft_server_stopword_table = 'mysql/my_stopwords';

~

SHOW GLOBAL  VARIABLES WHERE Variable_name IN('innodb_ft_min_token_size','innodb_ft_server_stopword_table');
+---------------------------------+--------------------+
| Variable_name                   | Value              |
+---------------------------------+--------------------+
| innodb_ft_min_token_size        | 2                  |
| innodb_ft_server_stopword_table | mysql/my_stopwords |
+---------------------------------+--------------------+

3.2 創(chuàng)建全文索引

alter table article add fulltext index idx_full_keyword(keywords);
* [ ] Query OK, 0 rows affected, 1 warning (1 min 27.92 sec)
* [ ] Records: 0  Duplicates: 0  Warnings: 1

3.3 剩余磁盤空間需足夠，原表4.6G，剩余5.7G磁盤，添加全文索引也會失敗

3.4 利用創(chuàng)建的全文索引進行查詢某個關(guān)鍵詞出現(xiàn)的次數(shù)

查詢響應時間有了很大的提升，只需0.05s；使用where keywords like '%時尚%' 需要7.56s。推薦閱讀：MySQL性能優(yōu)化實踐（很全面，值得收藏）

3.5 如需同時完全匹配多個關(guān)鍵詞，用布爾全文搜索

表示完全匹配 "三里屯,北京" 的記錄數(shù)

select count(*) from article where match(keywords)  against('+三里屯,北京' in boolean mode);
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.06 sec)

表示匹配“三里屯” 或者 “北京”的記錄數(shù)

select count(*) from article where match(keywords)  against('三里屯,北京');
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.06 sec)

3.6 創(chuàng)建全文索引后，會創(chuàng)建一些其它文件

96K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_1.ibd96K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_2.ibd96K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_3.ibd96K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_4.ibd128K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_5.ibd256K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_6.ibd96K Jul 5 16:29 FTS_00000000000000a7_BEING_DELETED_CACHE.ibd96K Jul 5 16:29 FTS_00000000000000a7_BEING_DELETED.ibd96K Jul 5 16:30 FTS_00000000000000a7_CONFIG.ibd96K Jul 5 16:29 FTS_00000000000000a7_DELETED_CACHE.ibd96K Jul 5 16:29 FTS_00000000000000a7_DELETED.ibd

• 前6個表示倒排索引（輔助索引表）
• 第7，8個表示包含已刪除文檔的文檔ID（DOC_ID），其數(shù)據(jù)當前正在從全文索引中刪除
• 第9個表示FULLTEXT索引內(nèi)部狀態(tài)的信息
• 第10，11個表示包含已刪除但尚未從全文索引中刪除其數(shù)據(jù)的文檔

使用ngram分詞解析器創(chuàng)建全文索引

1、對title字段建立全文索引（該字段沒有固定的stopwords 分詞，使用ngram分詞解析器）

需先在my.cnf 配置文件中設置ngram_token_size（默認為2，2個字符作為ngram 的關(guān)鍵詞），并重啟mysql服務

這里使用默認的 2

select title from article limit 10;
+------------------------------------------------------------------------------+
| title                                                                        |
+------------------------------------------------------------------------------+
| worth IT                                                                    |
|Launchpad 江南皮革廠小show                                                  |
|Raw 幕后罕見一刻 “瘋子”被抬回后臺                                           |
|Raw:公子大罵老爸你就是個綠茶  公子以一打四                                  |
|四組30平米精裝小戶型，海量圖片，附戶型圖                                    |
|夜店女王性感煙熏貓眼妝                                                      |
|大秀哥重摔“巨石”強森                                                        |
|少女時代 崔秀英 服飾科普 林允兒 黃美英 金泰妍 鄭秀晶                        |                                              
|德陽戶外踏青，花田自助燒烤                                                  |
+------------------------------------------------------------------------------+

2、對title字段創(chuàng)建全文索引

alter table article add fulltext index ft_index_title(title) with parser ngram;
Query OK, 0 rows affected (3 min 29.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、會創(chuàng)建倒排索引（title字段越長長，創(chuàng)建的倒排索引越大）

112M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_1.ibd28M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_2.ibd20M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_3.ibd140M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_4.ibd128M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_5.ibd668M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_6.ibd

4、不建立全文索引搜索title的某個關(guān)鍵詞

5、使用全文索引搜索某個關(guān)鍵詞

響應時間有很大的提升

6、注意當搜索的關(guān)鍵詞字符數(shù)大于2 （ngram_token_size定義大小）會出現(xiàn)不一致問題

普通搜索，實際中出現(xiàn)該關(guān)鍵詞的記錄數(shù)為6

全文搜索，出現(xiàn)關(guān)鍵字的記錄數(shù)為9443

實際出現(xiàn)該關(guān)鍵字的記錄數(shù)為1

全文搜索出現(xiàn)該關(guān)鍵詞的記錄數(shù)為3202

結(jié)論

當mysql 某字段中有固定的stopword 分詞（英文的空格符，中文的“,”"-"等），對該字段建立全文索引，能快速搜索出現(xiàn)某個關(guān)鍵詞的相關(guān)記錄信息，實現(xiàn)簡單搜索引擎的效果

當mysql 某字段沒有固定的stopword 分詞，使用內(nèi)置解析器ngram 可將字段值分成固定數(shù)量（ngram_token_size定義大小）的關(guān)鍵詞快速進行搜索；當搜索的關(guān)鍵詞的字符數(shù)量不等于ngram_token_size定義大小時，會出現(xiàn)與實際情況不一致的問題

全文索引能快速搜索，也存在維護索引的開銷；字段長度越大，創(chuàng)建的全文索引也越大，會影響DML語句的吞吐量，可用專門的全文搜索引擎ES來做這件事