技術文章 » 一文讀懂 MySQL 索引

一文讀懂 MySQL 索引

2022-10-11 15:01:43

1 索引簡介

1.1 什麼是 MySQL 的索引

官方定義：索引是幫助 MySQL 高效獲取資料的資料結構

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

2 索引資料結構

2.1 Hash 索引

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

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

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

2.2 二元樹

2.2.1 經典二元樹

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

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

經典二元樹會出現一個極端例子，就是連結串列，節點資料越來越大。這種情況下，二元樹搜尋效能就會降低

2.2.2 平衡二元樹

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

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

數位 1-6 在平衡二元樹中圖示如下：

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對應下圖最後一層節點的空格子;

MySQL 中 B 樹儲存結構如下：

2.4 B+ 樹

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

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

（2）B+樹葉子節點的關鍵字從小到大有序排列，左邊結尾資料都會儲存右邊節點開始資料的指標。因為葉子節點都是有序排列的，所以B+樹對於資料的排序有著更好的支援。

2.5 B* 樹

B樹是B+樹一種變形，它是在B+樹的基礎上，將索引層以指標連線起來（B+ 樹只是將資料層用指標連線起來），使搜尋取值更加快捷

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 一定要有主鍵索引，因為主鍵索引子節點中包含了全部資料

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）

slow_query_log_file 是慢查詢紀錄檔存放的位置，如果是 window 的話，通常在你的安裝資料夾 Data 目錄下

第二步：開啟慢查詢

set global slow_query_log  = 1;

第三步：設定慢查詢閾值

什麼樣的查詢叫做慢查詢呢？1s，5s 還是 10s，這點 MySQL 不知道，所以需要我們通過設定去設定 long_query_time 引數

通過命令 show variables like '%long_query_time%' 檢視慢查詢時間，預設是 10 s

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

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

使用此命令修改後，需要重新連線或者新開啟一個對談就可以看到修改後的設定

或者通過 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;

從圖中我們看到 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);

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

2、select_type

查詢型別，共包含四種

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

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

subquery：包含在 select 中的子查詢（不在 from 子句中）

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 可以查詢到所有資訊

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

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

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

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

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

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

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

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

6、possible_keys

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

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

7、key

查詢時真正用到的 key

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

8、ken_len

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

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

建立 user 表的時候，不知道大家有沒有注意到，name 的字元集為 utf8

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

9、ref

當使用索引列等值匹配的條件去執行查詢時，也就是在存取方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一時，ref列展示的就是與索引列作等值匹配的具體資訊，比如只是一個常數或者是某個列。

10、rows

預計需要掃描的函數

11、filtered

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

12、Extra

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

5.3高效能 的索引使用策略

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

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

這段 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 = '華為手機';

從上圖可以看到 type 為 ref。

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

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

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

5.3.3 儘量使用覆蓋索引

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