深入淺出 MySQL 索引之一

作者：悟空聊架構 2023-02-14 08:00:00

數據庫 MySQL

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

你好，我是悟空。

本文目錄如下：

一、前言

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

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

二、索引 VS 圖書館

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

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

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

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

三、索引是什么

索引它的英文名是 Index，它是一種數據結構。

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

數據結構按線性和非線性分為兩大類，八大種，比如線性數據結構的就有數組、鏈表、棧、隊列。

非線性的數據結構就有，樹、堆、散列表、圖等等。

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

B+樹

那 B+ 樹是如何存儲數據的呢？

我們可以打開這個網站看下。

設想下我們往一張數據表中隨機插入一些數字：

2、5、8、100、20

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

四、MySQL 索引的優缺點

優點

優點1：降低數據庫的 I/O 成本

這里其實就是減少數據庫讀寫數據的花費的時間。

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

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

優點2：保證數據的唯一性

通過創建唯一索引，可以保證數據庫表中每一行數據的唯一性。注意這里是唯一索引，通過關鍵字 UNIQUE 來創建唯一索引。

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

優點3：提高多表聯合查詢的效率

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

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

缺點

缺點1：創建索引和維護索引要耗費時間

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

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

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

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

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

五、體驗下索引加速查詢

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

首先你得創建一張表吧，然后往表里插入很多數據，對吧？

創建學生表

我這里創建了一張學生表：

字段說明：

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

插入 300 萬數據

現在表創建好了，就需要往表里面插入大量數據了，這里我就直接用寫好的腳本插入 300 萬數據。

測試不加索引的情況

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

查詢腳本如下：

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

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

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

測試加索引的情況

添加索引

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

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

workbench 工具添加索引

腳本添加索引

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

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

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

再來看下它的執行計劃：

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

有了索引之后，MySQL 在執行 SQL 語句的時候多了一種優化的手段。

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

六、創建索引的方式

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

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

創建索引的語法

創建索引有三種方式：

創建表的同時創建索引

語法：

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

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

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

直接給數據表創建索引

語法：

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

示例：創建一個索引 index_name，字段為 name。

CREATE INDEX index_name ON member (
  name
);

更新表的添加索引的語法

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

示例：創建一個聯合索引 index_id_name，字段為 id 和 name。

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

七、索引分類

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

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

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

按照作用字段個數進行劃分，分成單列索引和聯合索引。

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

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

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

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

詳細說明：

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

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

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

聯合索引（二級索引，組合索引）特點

同時為多個列建立索引。

八、創建不同的索引體會加速查詢

創建聚簇索引體會加速查詢

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

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

執行計劃中可以看到是直接用的 constant 方式，說明查詢直接找到了那條記錄，速度是非常快的。

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

先刪除主鍵索引：

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

查詢耗時 0.6 秒。

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

創建普通索引體會加速查詢

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

創建聯合索引體會加速查詢

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

在 student 表上的 age 和 classId 字段創建了一個聯合索引：

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 倍。

總結