你好，我是悟空。

本文目錄如下：

一、前言

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

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

二、索引 VS 圖書館

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

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

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

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

三、索引是什么

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

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

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

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

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

B+樹

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

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

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

2、5、8、100、20

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

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

優(yōu)點(diǎn)

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

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

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

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

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

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

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

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

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

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

缺點(diǎn)

缺點(diǎn)1：創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間

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

缺點(diǎn)2：索引需要占磁盤空間

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

缺點(diǎn)3：降低更新表的速度

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

五、體驗(yàn)下索引加速查詢

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

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

創(chuàng)建學(xué)生表

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

字段說明：

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

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

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

測試不加索引的情況

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

查詢腳本如下：

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

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

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

測試加索引的情況

添加索引

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

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

workbench 工具添加索引

腳本添加索引

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

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

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

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

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

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

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

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

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

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

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

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

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

語法：

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

示例：創(chuàng)建 member 表的同時(shí)創(chuàng)建一個(gè)索引 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)建一個(gè)索引 index_name，字段為 name。

CREATE INDEX index_name ON member (
  name
);

更新表的添加索引的語法

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

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

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

七、索引分類

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

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

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

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

聚簇索引（主鍵索引）特點(diǎn)

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

非聚簇索引（二級(jí)索引、輔助索引）特點(diǎn)

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

詳細(xì)說明：

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

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

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

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

• 同時(shí)為多個(gè)列建立索引。

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

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

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

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

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

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

先刪除主鍵索引：

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

查詢耗時(shí) 0.6 秒。

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

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

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

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

不加索引的情況下，查詢 年齡=15，班級(jí) id = 20 的學(xué)生，用時(shí) 0.46 秒。

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

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

查詢語句：

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

耗時(shí) 0.014 秒。

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

總結(jié)

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

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

關(guān)于我

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

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