一文讀懂 MySQL 索引

1 索引簡介

1.1 什麼是 MySQL 的索引

官方定義:索引是幫助 MySQL 高效獲取數據的數據結構

從上面定義中我們可以分析出索引本質是一個數據結構,他的作用是幫助我們高效獲取數據,在正式介紹索引前,我們先來了解一下基本的數據結構

2 索引數據結構

2.1 Hash 索引

Hash 索引是比較常見的一種索引,他是通過計算出記錄對應的 hash 值,然後根據計算結果,存儲在對應位置。查詢的時候也是根據 hash 值快速找到位置。他的單條記錄查詢的效率很高,時間複雜度為1。但是,Hash索引並不是最常用的資料庫索引類型,尤其是我們常用的Mysql Innodb引擎就是不支援hash索引的。

hash 索引在等值查詢時速度很快,但是有以下兩個問題

  • 不支援範圍查詢
  • hash 衝突,當兩條記錄的 hash 值相同時,就產生了 hash 衝突,需要在後面用鏈表存儲起來

image-20220919121130111

2.2 二叉樹

2.2.1 經典二叉樹

1、一個節點只能有兩個子節點

2、左子節點的值小於父親節點值,右子節點的值大於父親節點的值,採用二分查找,速度較快

image-20220919121926581

經典二叉樹會出現一個極端例子,就是鏈表,節點數據越來越大。這種情況下,二叉樹搜索性能就會降低

image-20220919122203129

2.2.2 平衡二叉樹

平衡二叉樹又稱AVL樹。它可以是一顆空樹,或者具有以下性質的二叉排序樹

  • 它的左子樹和右子樹的高度之差(平衡因子)的絕對值不超過1
  • 它的左子樹和右子樹都是一顆平衡二叉樹。

數字 1-6 在平衡二叉樹中圖示如下:

image-20220919122445172

2.3 B 樹

B樹屬於多叉樹又名平衡多路查找樹,可以有多叉,有如下特點

(1)排序方式:所有節點關鍵字是按遞增次序排列,並遵循左小右大原則;

(2)子節點數:非葉節點(根節點和枝節點)的子節點數 >1、且子節點數量<=M 、且M>=2,空樹除外(註:M階代表一個樹節點最多有多少個查找路徑,M=M路,當M=2則是2叉樹,M=3則是3叉);

(3)關鍵字數:枝節點的關鍵字數量大於等於ceil(m/2)-1個且小於等於M-1個(註:ceil()是個朝正無窮方向取整的函數 如ceil(1.1)結果為2);

(4)所有葉子節點均在同一層、葉子節點除了包含了關鍵字 和 關鍵字記錄的指針外,也有指向其子節點的指針只不過其指針地址都為null對應下圖最後一層節點的空格子;

image-20220919130308858

MySQL 中 B 樹存儲結構如下:

image-20220919131013045

2.4 B+ 樹

B+樹是在B樹的基礎上又一次的改進,其主要對兩個方面進行了提升,一方面是查詢的穩定性,另外一方面是在數據排序方面更友好。MySQL 索引的底層數據結構採用的就是 B+ 樹

(1)B+樹的非葉子節點不保存具體的數據,而只保存關鍵字的索引,而所有的數據最終都會保存到葉子節點。因為所有數據必須要到葉子節點才能獲取到,所以每次數據查詢的次數都一樣,這樣一來B+樹的查詢速度也就會比較穩定,而B樹的查找過程中,不同的關鍵字查找的次數很有可能都是不同的(有的數據可能在根節點,有的數據可能在最下層的葉節點),所以在資料庫的應用層面,B+樹就顯得更合適。

(2)B+樹葉子節點的關鍵字從小到大有序排列,左邊結尾數據都會保存右邊節點開始數據的指針。因為葉子節點都是有序排列的,所以B+樹對於數據的排序有著更好的支援。

image-20220919131322779

2.5 B* 樹

B樹是B+樹一種變形,它是在B+樹的基礎上,將索引層以指針連接起來(B+ 樹只是將數據層用指針連接起來),使搜索取值更加快捷

image-20220919132355520

總結

分析了以上幾種數據結構,MySQL 採用的是 B+ 樹來存儲索引,綜合層面來說,這樣查詢效率最好。oracle 採用的是 B* 樹

3 索引分類

MySQL 索引主要有以下幾種

  • 主鍵索引
  • 唯一索引
  • 普通索引
  • 組合索引
  • 全文索引

3.1 主鍵索引

主鍵索引是比較特殊的索引,一般在建表時會給表設置一個主鍵,MySQL 會默認給這個主鍵加上索引。主鍵索引葉子節點存儲的是數據表的某一行數據。當表沒有創建主鍵索引是,InnDB 會自動創建一個 ROWID 欄位用於構建聚簇索引。規則如下:

  1. 在表上定義主鍵 PRIMARY KEY,InnoDB 將主鍵索引用作聚簇索引。
  2. 如果表沒有定義主鍵,InnoDB 會選擇第一個不為 NULL 的唯一索引列用作聚簇索引。
  3. 如果以上兩個都沒有,InnoDB 會使用一個 6 位元組長整型的隱式欄位 ROWID 欄位構建聚簇索引。該 ROWID 欄位會在插入新行時自動遞增。

創建方式:

CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `name` varchar(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

為什麼建表時沒有指定主鍵,MySQL 會默認使用一個隱式欄位 ROWID 欄位構建聚簇索引?這個在後面我們會提到

3.2 唯一索引

與前面的普通索引類似,不同的就是:索引列的值必須唯一,但允許有空值。如果是組合索引,則列值的組合必須唯一。

創建方式

CREATE UNIQUE INDEX indexName ON user(column)
或者
ALTER TABLE table_name ADD UNIQUE indexName ON (column)

3.3 普通索引

MySQL 基本的索引,沒有什麼限制

創建方式:

CREATE INDEX index_name ON user(column)
或者
ALTER TABLE user ADD INDEX index_name ON (column)

3.4 組合索引

組合索引,顧名思義,給 MySQL 多個欄位同時加上索引,在使用時要遵循最左匹配原則

創建方式:

CREATE INDEX index_name ON user(column1,column2) -- 給 column1 和 column2 加上索引

3.5 全文索引

全文索引,主要用來查找文本中的關鍵字,不是直接與索引值相比較。與我們常見的搜索引擎(如elasticsearch、solr 等)功能相似。MySQL 全文索引性能一般,所以一般不用,作為了解即可

創建方式:

CREATE FULLTEXT INDEX index_column ON user(column)
或者
ALTER TABLE user ADD FULLTEXT index_column(column)

4 索引設計

4.1 三星索引

三星索引是我們設計 MySQL 索引時的一個規範,符合三星索引的索引設計通常是比較好的設計

一星:索引中查詢相關的索引行是相鄰的,或者至少相距足夠靠近

二星:索引中數據列的順序和查找中排序順序相同

三星:索引中的列包含了查詢中需要的全部列。索引包含查詢所需要的數據列,不再進行全表查表,回表操作

下面舉一個例子為大家介紹一下三星索引是什麼樣子的

現在有一張表,表結構如下

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `age` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

一星

我們現在給 age 加上索引

create index idx_age on user (age);

查詢

select * from user where age in (10,20,35,43)

這條語句不一定符合一星,因為 age 是一個範圍,數據可能比較分散

select * from user where age = 20;

這條語句是符合一星的,因為索引是按照 age 從小到大排序的,所以 age = 20 的數據肯定是在一起的

二星

select * from user where age = 20 order by name;

這條語句符合一星,但不符合二星,因為數據列的順序是按照 age 排序的,如果現在改成 name 排序,可能導致索引順序與 order by 排序結果不同,結果如下:

image-20220919144629595

image-20220919144651218

select * from user where age = 20 order by age

這條查詢語句則符合一星和二星

三星

select * from user where age = 20

這條語句不符合三星,因為索引列中只有 id 和 age,沒有 name

select age from user where age = 20

這條語句則符合三星,因為只查詢了 age,age 在索引中存在,不需要回表

4.2 回表

上面三星索引提到了一個次回表,那麼回表是什麼?

簡單點說,就是查詢語句中需要的列,在索引中不包含,需要根據主鍵 id 再查詢一次才能獲取到。回表相當於多查詢一次,再查詢時我們要盡量避免回表查詢。

因為普通索引中只包含了對應列和主鍵的值,比如 age 索引,那麼 age 索引中包含的數據有 age,id。此時如果需要 name 的話,需要先通過 age 索引找到對應的 id,然後再去主鍵索引上找到 name,主鍵索引包含了一行所有記錄的值。這裡回答了上面的問題,為什麼 MySQL 一定要有主鍵索引,因為主鍵索引子節點中包含了全部數據

image-20220919151148480

4.3 索引覆蓋

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `age` int(1) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;


select name,age from user where name = "張三"
-- 這條語句就使用了索引覆蓋,因為 name 和 age 再 idx_name_age 索引中都有,不需要回表查詢
select name,age,sex from user where name = "張三"
-- 如果加上了 sex,那麼就需要回表查詢了,因為索引中不存在 sex 欄位

5 索引優化

5.1 慢查詢

5.1.1 簡介

慢查詢日誌是 MySQL 提供的日誌記錄,用來記錄所有的慢 SQL 語句,我們可以通過設置慢查詢的時間閾值 long_query_time,來定義什麼樣的 SQL 是慢 SQL。通過慢查詢日誌我們可以找出需要優化的 SQL,下一步就是進行 SQL 優化

5.1.2 慢查詢配置

第一步:我們可以通過 show variables like ‘slow_query_log’ 語句查詢慢查詢是否開啟,默認是關閉(OFF)

image-20221002093201220

slow_query_log_file 是慢查詢日誌存放的位置,如果是 window 的話,通常在你的安裝文件夾 Data 目錄下

第二步:打開慢查詢

set global slow_query_log  = 1;

第三步:設置慢查詢閾值

什麼樣的查詢叫做慢查詢呢?1s,5s 還是 10s,這點 MySQL 不知道,所以需要我們通過配置去設置 long_query_time 參數

image-20221002094056353

通過命令 show variables like ‘%long_query_time%’ 查看慢查詢時間,默認是 10 s

如果需要修改,可以通過命令 set global long_query_time = 5 來設置

image-20221002094328461

注意:這裡通過 set global long_query_time = 5 設置完慢查詢時間後,再次查詢發現慢查詢時間依然是 10s,難道是設置沒生效?

使用此命令修改後,需要重新連接或者新開啟一個會話就可以看到修改後的配置

image-20221002094619080

或者通過 show global variables like '%long_query_time%' 命令查看

5.1.3 慢查詢日誌分析

我們剛才已經將慢查詢閾值設置為 5s,現在我們執行一條這樣的 sql 語句

select sleep(6); 

這條語句執行時間為 6s,我們打開慢查詢日誌可以發現增加了一些數據

# Time: 2022-10-02T09:16:23.194396Z
# User@Host: root[root] @ localhost [::1]  Id:     6
# Query_time: 6.011569  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1664675770;
select sleep(6);

我們來逐個分析一下每行代表什麼含義:

User@Host:執行該 SQL 的用戶和慢查詢 IP 地址

Query_time:語句執行時長

Lock_time:獲取鎖的時長

Rows_sent:MySQL 返回給客戶端的行數

Rows_examined:MySQL 掃描行數

timestamp:表示慢 SQL 記錄時的時間戳

select sleep(6):則是慢查詢 SQL

下面我們來分析一條真實的慢查詢 SQL,之前測試時的一條 SQL 語句

# Time: 2022-07-27T09:26:44.440318Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:   249
# Query_time: 68.461112  Lock_time: 0.000938 Rows_sent: 877281  Rows_examined: 877303
SET timestamp=1658914004;
SELECT  id,prd_line_id,shift_name,shift_id,app_id,weight,upload_time,operator,status,prd_line_name  FROM prd_weight 
WHERE (upload_time > '2022-07-27 00:00' AND upload_time < '2022-07-27 17:24');

Query_time:總查詢時長 68.461112s

Lock_time:0.000938s

Rows_examined:掃描行 877281

Rows_sent:返回了 877303

當然了,這是測試用的,生產上一般不會出現這麼離譜的 SQL 語句

5.1.4 注意事項

  1. 在 MySQL 中,慢查詢日誌中默認不記錄管理語句,如:
    alter table,,analyze table,check table 等。不過可通過以下屬性進行設置:
    set global log_slow_admin_statements = “ON”
  2. 在 MySQL 中,還可以設置將未走索引的 SQL 語句記錄在慢日誌查詢文件中(默認為關閉狀態)。通過下述屬性即可進行設置:
    set global log_queries_not_using_indexes = “ON”
  3. 在 MySQL 中,日誌輸出格式有支援:FILE(默認),TABLE 兩種,可進行組合使用。如下所示:
    set global log_output = “FILE,TABLE”
    這樣設置會同時在 FILE, MySQL 庫中的 slow_log 表中同時寫入。但是日誌記錄到系統的專用日誌表中,要比記錄到文件耗費更多的系統資源,因此對於需要啟用慢查詢日誌,又需要能夠獲得更高的系統性能,那麼建議優先記錄到文件。

5.2 Explain 執行計劃

通過上面的慢查詢日誌分析,我們可以知道有哪些慢 SQL 語句。但是這些 SQL 具體慢在哪裡,需要如何優化,我們還需要更詳細的分析計劃,這裡 MySQL 給我們提供了 Explain 關鍵字,通過該關鍵字我們可以分析出 SQL 語句的詳細執行資訊。

5.2.1 Explain 使用

我們在資料庫中創建一張 user 表用於測試

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `dept_id` int(10) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name`(`name`) USING BTREE,
  INDEX `idx_dept_id`(`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '張三', '123', '男', '12323432', 1);
INSERT INTO `user` VALUES (2, '李四', '456', '男', '178873937', 1);
INSERT INTO `user` VALUES (3, '小花', '123', '女', '1988334554', 2);
INSERT INTO `user` VALUES (4, '小芳', '334', '女', '18765287937', 2);
INSERT INTO `user` VALUES (5, NULL, '122', NULL, NULL, NULL);


DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, '開發部');
INSERT INTO `dept` VALUES (2, '銷售部');

explain 使用也很簡單,直接在查詢語句前面加上 explain 關鍵字即可:

EXPLAIN SELECT * FROM user where id = 1;

image-20221002104547978

從圖中我們看到 MySQL 返回了一行記錄,下面我們一起來分析每個欄位代表什麼含義

欄位 含義
id 一次查詢過程中該條 select 語句的唯一標識
select_type 查詢類型,共包含四種 simple、primary、subquery、derived
table 查詢的是哪張表
partitions 表的分區資訊
type 訪問類型,分析性能主要通過該欄位
possible_keys 可能會用到的索引
key 實際用到的索引
key_len 索引里使用的位元組數
ref 這一列顯示了在key列記錄的索引中,表查找值所用到的列或常量
rows MySQL 預估的掃描行
filtered MySQL 過濾後,滿足條件記錄數的比例
Extra 展示了一些額外資訊

5.2.2 Explain 詳解

1、id

id 是查詢語句中的唯一標識,id 的值越大,該 id 對應的 sql 語句越先執行

explain select * from dept where id = (select dept_id from user where id = 1);

image-20221002111658358

從執行計劃來看,select dept_id from user where id = 1 這條語句先執行,因為外層查詢需要藉助這條查詢語句的結果

2、select_type

查詢類型,共包含四種

simple:簡單查詢。查詢不包含子查詢和union

image-20221002112227619

primary:複雜查詢中最外層的 select

image-20221002111658358

subquery:包含在 select 中的子查詢(不在 from 子句中)

image-20221002111658358

derived:包含在 from 子句中的子查詢。MySQL會將結果存放在一個臨時表中,也稱為派生表(derived的英文含義)

3、table

查詢的是哪張表,比較好理解

4、partitions

查詢時匹配到的分區資訊,對於非分區表值為 NULL,當查詢的是分區表時,partitions 顯示分區表命中的分區情況。

5、type

type:查詢使用了何種類型,它在 SQL優化中是一個非常重要的指標,以下性能從好到壞依次是:system > const > eq_ref > ref > ref_or_null > range > index > ALL

  • system 是 const 的特例,也就是當表中只存在一條記錄時,type 為 system

  • const,常量查詢,id 是主鍵,通過 id 可以查詢到所有資訊

    image-20221002113133121

  • eq_ref,連接查詢中,primary key 或 unique key 索引的所有部分被連接使用

注意:這裡 dept 的 id 和 user 的 id 並無關聯關係,只是為了演示該查詢類型

image-20221002113825037

user 的 id 和 dept 的 id 都是主鍵,在連接查詢中,兩個主鍵都被使用到

  • ref,不使用唯一索引,使用普通索引或者唯一索引,可能會找到多個條件的值,idx_name 是普通索引

image-20221002114115876

  • ref_of_null,和 ref 功能類似,區別在於會額外搜索索引包含 NULL 的值,name 欄位是普通索引,且資料庫中存在 name 為 null 的數據

image-20221002114412412

  • range,在索引欄位上使用範圍查詢,常見的有 >、<、in、like 等查詢

image-20221002145754924

  • index,通過索引樹進行全表掃描

image-20221002150036038

  • ALL,全表掃描,不通過索引樹,因為這次是 select * 查詢

image-20221002150112138

6、possible_keys

MySQL 分析此次查詢可能會用到的索引,但是實際查詢中不一定會用到

image-20221002150344478

分析可能會用到 idx_name 這個索引,實際查詢中沒有用到索引,走的全表掃描

7、key

查詢時真正用到的 key

image-20221002150558281

查詢中實際上用到了 idx_name 這個索引

8、ken_len

表示查詢用到的索引列長度

image-20221002150558281

我們用這個索引來分析,key_len 為 63 是怎麼來的?

image-20221002151036404

創建 user 表的時候,不知道大家有沒有注意到,name 的字符集為 utf8

MySQL 5.0 版本以上,utf8 字符集下每個字元佔用 3 個位元組,varchar(20) 則佔用 60 個位元組,同時因為 varchar 是變長字元串,需要額外地位元組存放字元長度,共兩個位元組,此外,name 欄位可以為 null 值,null 值單獨佔用一個位元組,加在一起一共 63 個位元組

9、ref

當使用索引列等值匹配的條件去執行查詢時,也就是在訪問方法是consteq_refrefref_or_nullunique_subqueryindex_subquery其中之一時,ref列展示的就是與索引列作等值匹配的具體資訊,比如只是一個常數或者是某個列。

image-20221002152423370

10、rows

預計需要掃描的函數

11、filtered

filtered 這個是一個百分比的值,表裡符合條件的記錄數的百分比。簡單點說,這個欄位表示存儲引擎返回的數據在經過過濾後,剩下滿足條件的記錄數量的比例。

12、Extra

Extra是用來說明一些額資訊的,從而幫助我們更加準確的理解查詢

5.3高性能 的索引使用策略

5.3.1 不要在索引列上做任何操作

explain select * from user where left(name,2) = '小芳';

image-20221002153601423

這段 sql 對 name 欄位做了函數操作,導致索引失效

5.3.2 最左前綴法則

在使用聯合索引查詢時,應該遵循最左前綴原則,指的是查詢從索引的最左前列開始並且不跳過索引中的列。

創建一張 goods 表,有一個聯合索引包含了 name,price、mark 三個欄位

DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `price` int(10) NULL DEFAULT NULL,
  `mark` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_all`(`name`, `price`, `mark`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES (1, '手機', 5678, '華為手機');
INSERT INTO `goods` VALUES (2, '電腦', 9888, '蘋果電腦');
INSERT INTO `goods` VALUES (3, '衣服', 199, '好看的衣服');

執行下面的查詢語句:

explain select * from goods where name = '手機' and price = 5678 and mark = '華為手機';

image-20221002155147045

從上圖可以看到 type 為 ref。

現在我們不從最左側開始查詢,直接跳過 name 欄位

explain select * from goods where price = 5678 and mark = '華為手機';

image-20221002155322785

type 從 ref 變成了 index,這是因為 MySQL 建立索引時是按照組合索引中的欄位順序來排序的,如果跳過中間某個欄位,則不一定是有序的了。

5.3.3 盡量使用覆蓋索引

覆蓋索引,需要查詢的欄位全部包含在索引列中,不需要回表查詢