我所理解的MySQL(三)執行計劃

語言: CN / TW / HK
時間 2020-10-18 20:15:20

你好，有幸相見。

從九月開始，我決定發起「每週一博」的目標：每週至少釋出一篇部落格，可以是各種原始碼分析研讀，也可以是記錄工作中遇到的難題。

在經過了一段時間漫無目的的學習之後，我發現那樣用處好像不大，看過的東西過段時間就忘了，而且也沒有做什麼筆記。

“凡所學，必有所輸出。”我認為這才是最適合我的學習方式，這也是「每週一博」活動的來由，朋友們，如果你也覺得經常會忘記以前看過的東西，一起加入這個活動吧。

這是十月的第二篇部落格，同時這也是 MySQL 系列的第三篇。

MySQL 系列的第三篇部落格，主要內容是 MySQL 中關於 Explain 執行計劃的分析，假如你已經知道如何分析執行計劃，那麼對於 SQL 調優也就信手拈來了。

縱觀眾多一二線大廠招聘時的崗位要求，但凡設計資料庫的必定會要求有 SQL 調優的經驗，這幾乎已經成為與 Spring 不相上下的“八股文”類面試題。

要想進行 SQL 調優，首先需要知道 SQL 的執行情況，最直觀的感覺當然是 SQL 語句執行的時間，然而除此之外，我們還可以通過執行計劃來分析 SQL 語句的執行情況，從而進行調優。

1. Explain 簡述

Explain 語句可以檢視 MySQL 是如何執行這條 SQL 語句的，包括使用索引情況、掃描行數等，這些資訊對於 SQL 調優來說十分重要，所以首先得看懂執行計劃。

mysql> explain select * from user where name='one';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | a             | a    | 13      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
複製程式碼

以上是一條簡單查詢語句的執行計劃，這張表一共有12個欄位，分別代表不同的含義，下面一一敘述。

  • id: 表示 SQL 執行的順序，值越大，優先順序越高。若值相同，執行順序由優化器決定。
  • select_type: 表示 select 查詢語句的型別
  • table: SQL 語句查詢的表名（或該表的別名），也可能是臨時表等不存在的表
  • partitions: 查詢語句涉及的分割槽資訊
  • type: 關聯型別（訪問型別），決定了 MySQL 是如何查詢表中行的。效能從最差到最優依次是 ALL, index, range, index_merge, ref, eq_ref, const, system, NULL
  • possible_keys: 展示了查詢語句可以使用的所有索引
  • key: 展示了優化器決定採用的索引名稱
  • key_len: 展示了 MySQL 使用索引長度的位元組數
  • ref: 在 key 列記錄的索引中查詢值所使用的列或常量
  • rows: 掃描行數的估值
  • filtered: 最終滿足查詢語句行數佔儲存引擎返回總行數的百分比
  • Extra: 其他執行資訊

以上只是對執行計劃表各個欄位的名詞解釋，接下來我會通過實際的例子來幫助大家（我自己）更好地理解其中 select_type, type, key_len, rows, Extra 這些重要的欄位。

2. Explain 詳述

2.1 示例表結構

首先介紹本文中將用到的示例表表結構以及資料行：

CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(36) DEFAULT NULL COMMENT '姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年齡',
  `email` varchar(36) DEFAULT NULL COMMENT '郵箱',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_age_name`(`age`, `name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1;
複製程式碼

通過函式向表中插入1000000條測試資料。

CREATE DEFINER=`root`@`localhost` PROCEDURE `idata`()
begin 
  declare i int; 
  set i=1; 
  while(i<=1000000)do 
    insert into user(id,name,age,email) values(i, CONCAT('name',i), i % 50, concat(i,'[email protected]'));
    set i=i+1; 
  end while;
end
複製程式碼

2.2 select_type in Explain

執行計劃中 select_type 欄位表示 select 查詢語句的型別，常見型別有：

  • SIMPLE: 簡單的查詢語句，不包括子查詢和關聯，如：
mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
複製程式碼

2.2.1 PRIMARY

若查詢語句中包含任何複雜的子部分，那麼最外層部分會被標記為 PRIMARY，如：

mysql> explain select * from user where id=(select id from user where id=1);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
複製程式碼

在這條 SQL 語句的執行計劃中，第一條執行的 SQL，即 select * from yser where id = (...) 就被標記為 PRIMARY

2.2.2 SUBQUERY

包含在 select 或 where 內容中的子查詢會被標記為 SUBQUERY，如上一條示例 SQL 的執行計劃中第二條語句，即 select id from user where id=1 的 select_type 就被標記為了SUBQUERY。

2.2.3 DERIVED

包含在 FROM 關鍵字後的子查詢（即將子查詢的結果視為「表」），被視為「表」的子查詢會被標記為 DERIVED，其結果將被存放在臨時表中，如：

mysql> explain select * from (select id,name,count(*) from user where id=1) as user_1 where id=1;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | user       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
複製程式碼

從執行計劃中可以看到，第二條執行的 SQL，即 select id,name,count(*) from user where id=1 的查詢型別是 DERIVED。

select_type 一共有12中查詢型別，具體釋義可以看官方文件-explain_select_type

2.3 type in Explain

type 欄位是執行計劃中衡量 SQL 非常重要的依據，它展示了 SQL 語句的關聯型別（訪問型別），決定了 MySQL 是如何查詢表中行的。

type 欄位的值效能從最差到最優依次是 ALL, index, range, index_merge, ref, eq_ref, const, system。

為了能更好地理解各個型別的含義，我對上述每一種型別都舉出了相應的示例。

並未全部列出，完整的解釋可以看官方文件-EXPLAIN Join Types

2.3.1 ALL

ALL 表示全表掃描，意味著儲存引擎查詢記錄時未走索引，所以它是效能最差的一種訪問型別，如

mysql> explain select * from user where age+2=20;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1002301 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
複製程式碼

可以看到 rows 行的值為1002301，即掃描了全表的所有資料（掃描行數的值實際為估算），如果在生產環境有這樣的 SQL，絕對是要優化的。

我們知道在 where 查詢條件中，不應該對查詢欄位使用函式或表示式（應該寫在等號不等號右側），不瞭解此內容的可以看看我的上一篇部落格 —— 我所理解的MySQL(二)索引。

這條查詢語句在優化後應該是: select * from user where age=18，去掉等號左側的表示式，優化後的執行計劃如下: 

mysql> explain select * from user where age=18;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const | 39360 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
複製程式碼

2.3.2 index

index 表示全索引樹掃描，由於掃描的是索引樹，所以比 ALL 形式的全表掃描效能要好。

同時，由於索引樹本身就是有序的，可以避免排序。

mysql> explain select id,age from user where name='name1';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | idx_age_name | 116     | NULL | 1002301 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+---------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
複製程式碼

示例查詢語句如上所述，當查詢條件存在於聯合索引 idx_age_name 中，但又無法直接使用該索引（由於最左字首原則），同時查詢列 id,age 也存在於聯合索引中，無須通過回表來獲取時，執行計劃中的訪問型別 type 列就會是 index。

2.3.3 range

range 表示範圍掃描，準確的說是基於索引樹的範圍掃描，掃描的是部分索引樹，所以效能比 index 稍好。

需要注意的是，若使用 in 或者 or 時，也可以使用範圍掃描。

mysql> explain select * from user where age>18 and age<20;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | idx_age_name  | idx_age_name | 5       | NULL | 36690 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from user where age=18 or age=20;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | idx_age_name  | idx_age_name | 5       | NULL | 78720 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
複製程式碼

2.3.4 index_merge

index_merge 即索引合併，它表示在查詢時 MySQL 會使用多個索引。

MySQL 在 where 語句中存在多個查詢條件，並且其中存在多個欄位可以分別使用到多個不同的索引，在這種情況下 MySQL 可以對多個索引樹同時進行掃描，最後將它們的結果進行合併，如：

mysql> explain select * from user where id=1 or age=18;
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys        | key                  | key_len | ref  | rows  | filtered | Extra                                               |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | user  | NULL       | index_merge | PRIMARY,idx_age_name | idx_age_name,PRIMARY | 5,4     | NULL | 39361 |   100.00 | Using sort_union(idx_age_name,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+----------------------+----------------------+---------+------+-------+----------+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
複製程式碼

上面這條查詢語句中的 id=1 和 age=18 分別使用到了 PRIMARY 主鍵索引和 idx_age_name 聯合索引，最後再將滿足這兩個條件的記錄進行合併。

2.3.5 ref

ref 表示索引訪問（索引查詢），這種訪問型別會出現在查詢條件中以非聚簇索引列的常量值進行查詢的情況。

比如在介紹全表掃描中優化後 SQL 的訪問型別就是 ref。

2.3.6 eq_ref

eq_ref 這種訪問型別會出現在連線查詢時，通過聚簇索引進行連線的情況，此型別最多隻返回一條符合條件的記錄。若表的聚簇索引為聯合索引，所有的索引列必須是等值查詢，如：

mysql> explain select * from user user1 inner join user user2 where user1.id=user2.id limit 10;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows    | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+
|  1 | SIMPLE      | user1 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                | 1002301 |   100.00 | NULL  |
|  1 | SIMPLE      | user2 | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | all_in_one.user1.id |       1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+---------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
複製程式碼

2.3.7 const

const 這種訪問型別會出現在通過聚簇索引進行常量等值查詢的情況，如：

mysql> explain select * from user where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
複製程式碼

2.4 key_len in Explain

在上一篇部落格 —— 我所理解的MySQL(二)索引 中 5.2 部分欄位匹配 中已經提到過關於索引長度的計算方式，這裡再來總結一下。

2.4.1 字元型別

字元型別的欄位若作為索引列，它的索引長度 = 欄位定義長度 字元長度 + 是否預設NULL + 是否是變長欄位*，其中：

  • 欄位定義長度 就是定義表結構時跟在欄位型別後括號中的數字
  • 字元長度 是常數，utf8=3, gbk=2, latin1=1
  • 是否預設NULL 也是常數，若欄位預設值為 NULL，該值為1，因為 NULL 需要額外的一個位元組來表示；否則該值為0
  • 是否是變長欄位 也是常數，若該欄位為變長欄位，該值為2；否則該值為0

所謂的變長欄位就是 varchar，它所佔用的就是欄位實際內容的長度而非定義欄位時的長度。而定長欄位，也就是 char 型別，它所佔用的空間就是自定欄位時的長度，若超過會被擷取。

舉個例子，為上述例項表中新增一個字元型別欄位的索引。

alter table user add index idx_name(`name`);
複製程式碼

然後通過 name 欄位去做查詢，檢視執行計劃。

mysql> explain select * from user where name='name1';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_name      | idx_name | 111     | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
複製程式碼

可以看到，執行計劃中 key_len 一列的值為 111。

根據上述索引長度的計算公式，name 列欄位定義長度為36，字符集型別為預設的 utf8，該欄位預設允許 NULL，同時該欄位是可變長欄位 varchar。

所以 idx_name 索引的索引長度=36*3+1+2=111，恰如執行計劃中顯示的值。

2.4.2 其他定長型別

對於定長型別的欄位，其索引長度與它的資料型別長度是一致的。

資料型別長度
int4
bigint8
date3
datetime8
timestamp4
float4
double8

需要注意的是，若該欄位允許預設值為 NULL，與字元型別一樣，其索引長度也需要加上1。

mysql> explain select * from user where age=1;
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_age_name  | idx_age_name | 5       | const | 39366 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
複製程式碼

如上面這個示例（本示例中索引只用到了 age 欄位），age 欄位為 int 型別，其索引長度本應為 4，但由於 age 欄位預設允許為 NULL，所以它的索引長度就變成了5。

2.5 rows in Explain

掃描行數在執行計劃中其實是一個估值，MySQL 會選擇 N 個不同的索引資料頁，計算平均值得到單頁索引基數，然後再乘以索引頁面數，就得到了掃描行數的估值。

掃描行數就是優化器考量索引執行效率的因素之一，一般而言掃描行數越少，執行效率越高。

2.6 Extra in Explain

執行計劃中 Extra 欄位的常見型別有：

  • Using index: 使用了覆蓋索引，以避免回表
  • Using index condition: 使用了索引下推，具體可以看我的上一篇部落格 —— 我所理解的MySQL(二)索引
  • Using where: 表示MySQL 會通過 where 條件過濾記錄
    • 全表掃描：where 中有該表字段作為搜尋條件
    • 掃描索引樹：where 中包含索引欄位之外的其他欄位作為搜尋條件
  • Using temporary: MySQL 在對查詢結果排序時會使用臨時表
  • Using filesort: 對結果進行外部索引排序（檔案排序），排序不走索引
    • 資料較少時在記憶體中排序，資料較多時在磁碟中排序
    • 儘量避免該資訊出現在執行計劃中

3. 參考資料

「」
「」