你好，我是悟空。

本文目錄如下：

一、前言

最近在梳理 MySQL 核心知識，剛好梳理到了 MySQL 索引相關(guān)的知識，我的文章風格很多都是原理 + 實戰(zhàn)的方式帶你去了解知識點，所以本篇也是。

當然，索引的知識點還是很多的，本篇是對索引的基礎知識進行講解，不涉及索引的底層原理，以及未涉及到如何查看執(zhí)行計劃，將會分成多篇進行講解，請持續(xù)關(guān)注~

二、索引 VS 圖書館

設想一種場景，你現(xiàn)在是一名圖書管理員，每天的工作就是將歸還的書放回原位。如果有人想找某本書，則可以先通過書的分類、書號等，找到書所在的書架位置，進一步縮小了范圍。

假如圖書館沒有圖書管理員，書架上的書是無規(guī)律擺放的，那么有人想找一本書，就只能從頭開始找了，找不找得到完全靠運氣了。

如果你去過圖書館，應該會知道圖書館的檢索系統(tǒng)。圖書館為圖書準備了檢索目錄，包括書名、書號、對應的位置信息，包括在哪個區(qū)、哪個書架、哪一層。我們可以通過書名或書號，快速獲知書的位置，拿到需要的書。

MySQL 中的索引，就相當于圖書館的檢索目錄，它是幫助 MySQL 系統(tǒng)快速檢索數(shù)據(jù)的一種存儲結(jié)構(gòu)。我們可以在索引中按照查詢條件，檢索索引字段的值，然后快速定位數(shù)據(jù)記錄的位置，這樣就不需要遍歷整個數(shù)據(jù)表了。而且，數(shù)據(jù)表中的字段越多，表中數(shù)據(jù)記錄越多，速度提升越是明顯。

三、索引是什么

索引它的英文名是 Index，它是一種數(shù)據(jù)結(jié)構(gòu)。

數(shù)據(jù)結(jié)構(gòu)是計算機存儲、組織數(shù)據(jù)的方式。一種好的數(shù)據(jù)結(jié)構(gòu)可以帶來更高的運行或者存儲效率。數(shù)據(jù)在內(nèi)存中是呈線性排列的，但是我們可以使用指針等道具，構(gòu)造出類似“樹形”的復雜結(jié)構(gòu)。

數(shù)據(jù)結(jié)構(gòu)按線性和非線性分為兩大類，八大種，比如線性數(shù)據(jù)結(jié)構(gòu)的就有 數(shù)組、鏈表、棧、隊列。

非線性的數(shù)據(jù)結(jié)構(gòu)就有，樹、堆、散列表、圖等等。

那 MySQL 中的索引是其中哪一種呢？它是一種樹型數(shù)據(jù)結(jié)構(gòu)，而且是 B+ 樹，如下圖所示，不過圖中的樹是一種倒著的樹，它的根在最上面。

B+樹

那 B+ 樹是如何存儲數(shù)據(jù)的呢？

我們可以打開這個網(wǎng)站看下。

設想下我們往一張數(shù)據(jù)表中隨機插入一些數(shù)字：

2、5、8、100、20

類似我們將圖書館的書隨機擺放到書架中，然后我們來通過動圖演示的方式看下 B+ 樹是如何按照它的數(shù)據(jù)結(jié)構(gòu)來存放、查找和刪除這些數(shù)字的。

四、MySQL 索引的優(yōu)缺點

優(yōu)點

優(yōu)點1：降低數(shù)據(jù)庫的 I/O 成本

這里其實就是減少數(shù)據(jù)庫讀寫數(shù)據(jù)的花費的時間。

假如讓你從一堆雜亂中的書中找一本指定的書，是不是得一本一本的看下封面上寫的書名是不是對的，

有了索引，就不需要對每本書都翻看封面了，可以快速到那本書，減少了很多無效的查找。

優(yōu)點2：保證數(shù)據(jù)的唯一性

通過創(chuàng)建唯一索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。注意這里是唯一索引，通過關(guān)鍵字 UNIQUE 來創(chuàng)建唯一索引。

比如說員工表中的每個員工 id 都是唯一的。

優(yōu)點3：提高多表聯(lián)合查詢的效率

不論是單表查詢，還是多表查詢，索引都是提高查詢效率的。

任何事物都有其兩面性，索引有優(yōu)點，必定也會有缺點，那索引有什么缺點呢？

缺點

缺點1：創(chuàng)建索引和維護索引要耗費時間

就好比圖書館借書和還書都是需要圖書管理員來的維護，如果長期沒人管，圖書不就又亂了嗎？

缺點2：索引需要占磁盤空間

就好比圖書館對每本書的位置信息都是需要存放到一份數(shù)據(jù)里面的，如果是存放到電腦里面，就會占用電腦的硬盤空間，如果是用紙質(zhì)文檔來存儲，則會占用房間的空間。

缺點3：降低更新表的速度

就好比圖書館將新出的書放到書架之前，管理員是需要先查詢下這本書的所屬位置，再去放到書架上，這個查詢的過程就會耗費一定的時間。

五、體驗下索引加速查詢

前面說了索引的優(yōu)點很多，最主要的原因是提高查詢速度。那我們就來看下不加索引和加索引兩種場景下的查詢速度。

首先你得創(chuàng)建一張表吧，然后往表里插入很多數(shù)據(jù)，對吧？

創(chuàng)建學生表

我這里創(chuàng)建了一張學生表：

字段說明：

• id：這條記錄的 id，也是主鍵 id，具有唯一性，也就是說每條記錄都是唯一的。
• stu_no：學生編號，插入樣本數(shù)據(jù)時為自增的數(shù)字
• stu_name：學生姓名，插入樣本數(shù)據(jù)時為隨機的英文字母組合
• age：學生年齡，插入樣本數(shù)據(jù)時會隨機分布年齡
• classId：班級 id，插入樣本數(shù)據(jù)時會隨機分布班級 id。

插入 300 萬數(shù)據(jù)

現(xiàn)在表創(chuàng)建好了，就需要往表里面插入大量數(shù)據(jù)了，這里我就直接用寫好的腳本插入 300 萬數(shù)據(jù)。

測試不加索引的情況

那如果我想根據(jù)某個學生編號stu_no來找到學生的記錄該怎么查詢呢？

查詢腳本如下：

現(xiàn)在 student 表是沒有添加索引的，來看下它的查詢速度吧。

如何去統(tǒng)計腳本執(zhí)行所花的時間呢？因為我現(xiàn)在用的是 workbench 圖形化管理工具，所以可以借助這款工具來看執(zhí)行時間：

可以看到查詢這條數(shù)據(jù)用了 0.47s 時間，從查詢計劃中也可以看到這個查詢是全表掃描了，也就是說查詢 stu_no = '555555'?這條記錄是從記錄的第一行開始，一行一行掃描，看下哪條記錄的stu_no = '555555'，這種查詢方式是很慢很慢的，尤其是要要從這么大的數(shù)據(jù)量來中找。

測試加索引的情況

添加索引

如果我們這個要查詢的字段 stu_no 加上索引會發(fā)生什么事情呢？

加索引的方式可以直接通過 workbench 工具或者通過腳本。

workbench 工具添加索引

腳本添加索引

ALTER TABLE `test`.`student` 
ADD INDEX `index_stu_no` (`stu_no` ASC) VISIBLE;

測試添加索引后的查詢速度

加了索引后，查詢只需要 0.0013s，如下圖所示：

再來看下它的執(zhí)行計劃：

可以看到利用了索引查找，通過索引直接定位到那一行數(shù)據(jù)。

有了索引之后，MySQL 在執(zhí)行 SQL 語句的時候多了一種優(yōu)化的手段。

也就是說，在查詢的時候，可以先通過查詢索引快速定位，然后再找到對應的數(shù)據(jù)進行讀取，這樣就大大提高了查詢的速度。

六、創(chuàng)建索引的方式

在工作中，我們一般都是寫好創(chuàng)建索引的 SQL 腳本，然后將腳本提交到代碼倉庫。這樣更方便維護 SQL 腳本和索引。

那創(chuàng)建索引的腳本是怎么樣的呢？有沒有語法要求？

創(chuàng)建索引的語法

創(chuàng)建索引有三種方式：

創(chuàng)建表的同時創(chuàng)建索引

語法：

CREATE TABLE 表名(
  字段 數(shù)據(jù)類型,
  字段 數(shù)據(jù)類型,
  ...
  { INDEX | KEY } 索引名 (字段1，字段2，...)
)

示例：創(chuàng)建 member 表的同時創(chuàng)建一個索引 uk_idx_id，字段是 id。

CREATE TABLE member (
    id INT NOT NULL,
    name VARCHAR(30) NOT NULL,
    INDEX uk_idx_id (id)
);

直接給數(shù)據(jù)表創(chuàng)建索引

語法：

CREATE INDEX 索引名 ON 表名 (
  字段1，字段2，...
);

示例：創(chuàng)建一個索引 index_name，字段為 name。

CREATE INDEX index_name ON member (
  name
);

更新表的添加索引的語法

ALTER TABLE 表名 
ADD { INDEX | KEY } 索引名 (字段1，字段2，...);

示例：創(chuàng)建一個聯(lián)合索引 index_id_name，字段為 id 和 name。

ALTER TABLE member
ADD INDEX index_id_name (id, name);

七、索引分類

MySQL的索引包括普通索引、唯一性索引、全文索引、單列索引、多列索引和空間索引等。

從 功能邏輯上說，索引主要有 4 種，分別是普通索引、唯一索引、主鍵索引、全文索引。

按照 物理實現(xiàn)方式 ，索引可以分為 2 種：聚簇索引和非聚簇索引。

按照 作用字段個數(shù) 進行劃分，分成單列索引和聯(lián)合索引。

聚簇索引（主鍵索引）特點

• 主鍵作為索引，B+樹的 葉子節(jié)點 存儲的是完整的用戶記錄

非聚簇索引（二級索引、輔助索引）特點

回表查詢：先到普通索引上定位主鍵值，再到聚集索引上定位行記錄，它的性能較掃一遍索引樹低（一般情況下）。

詳細說明：

一般我們自己建的索引不管是單列索引還是聯(lián)合索引，都稱為普通索引，相對應的另外一種就是聚簇索引。每個普通索引就對應著一顆獨立的索引B+樹，索引 B+ 樹的節(jié)點僅僅包含了索引里的幾個字段的值以及主鍵值。

根據(jù)索引樹按照條件找到了需要的數(shù)據(jù)，僅僅是索引里的幾個字段的值和主鍵值，如果用 select * 則還需要很多其他的字段，就得走一個回表操作，根據(jù)主鍵再到主鍵的聚簇索引里去找，聚簇索引的葉子節(jié)點是數(shù)據(jù)頁，找到數(shù)據(jù)頁里才能把一行數(shù)據(jù)的所有字段值提取出來。

假設有 select * from table order by a,b,c 的語句，（table 有 abcdef 6 個字段），首先得從聯(lián)合索引的索引樹里按照順序 a、b、c 取出來所有數(shù)據(jù)，接著對每一條數(shù)據(jù)都根據(jù)主鍵到聚簇索引的查找，其實性能不高。

聯(lián)合索引（二級索引，組合索引）特點

• 同時為多個列建立索引。

八、創(chuàng)建不同的索引體會加速查詢

創(chuàng)建聚簇索引體會加速查詢

我們之前創(chuàng)建 student 表的同時添加了以 id 為索引字段的主鍵索引（聚簇索引），所以看下使用主鍵 id 來查詢的速度怎么樣。如果你之前對這個表沒有添加過主鍵索引，可以通過這個腳本添加：

# id 不為空，自增長主鍵，自動添加聚簇索引
ALTER TABLE `test`.`student` 
CHANGE COLUMN `id` `id` INT NOT NULL AUTO_INCREMENT ,
ADD PRIMARY KEY (`id`);

執(zhí)行計劃中可以看到是直接用的 constant 方式，說明查詢直接找到了那條記錄，速度是非?？斓摹?/p>

然后我們把主鍵索引刪除之后，再看下查詢用時。

先刪除主鍵索引：

ALTER TABLE `test`.`student` 
CHANGE COLUMN `id` `id` INT NOT NULL,
DROP PRIMARY KEY;

查詢耗時 0.6 秒。

而且查看執(zhí)行計劃是全表掃描，這種查詢方式非常耗時。

創(chuàng)建普通索引體會加速查詢

在本文中的第 5 小節(jié)已經(jīng)通過在 stu_no 學生編號上創(chuàng)建普通索引來演示查詢效果了，索引也是加速了查詢。

創(chuàng)建聯(lián)合索引體會加速查詢

不加索引的情況下，查詢 年齡=15，班級 id = 20 的學生，用時 0.46 秒。

在 student 表上的  age 和 classId 字段創(chuàng)建了一個聯(lián)合索引：

CREATE INDEX index_age_class_id ON test.student (
  age, 
  classId
);

查詢語句：

SELECT 
    *
FROM
    test.student
WHERE
    age = 15 AND classId = 20;

耗時 0.014 秒。

0.46 秒降低到 0.014 秒，速度提升了 30 倍。

總結(jié)

本篇講解了 MySQL 的索引是什么，優(yōu)缺點，MySQL 索引分類，以及如何通過腳本創(chuàng)建 MySQL 索引，最后通過演示不同類型的索引如何加速查詢。

下一篇 MySQL 文章我們接著聊 MySQL 索引。

關(guān)于我

8 年互聯(lián)網(wǎng)開發(fā)經(jīng)驗，擅長微服務、分布式、架構(gòu)設計。目前在一家大型上市公司從事基礎架構(gòu)和性能優(yōu)化工作。

InfoQ 簽約作者、藍橋簽約作者、阿里云專家博主、51CTO 紅人。