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,'name@email.cn')); 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 其他定長類型
對于定長類型的字段，其索引長度與它的數據類型長度是一致的。
| 數據類型 | 長度 |
|---|---|
| int | 4 |
| bigint | 8 |
| date | 3 |
| datetime | 8 |
| timestamp | 4 |
| float | 4 |
| double | 8 |
需要注意的是，若該字段允許默認值為 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: 對結果進行外部索引排序（文件排序），排序不走索引
- 數據較少時在內存中排序，數據較多時在磁盤中排序
- 盡量避免該信息出現在執行計劃中
相關免費學習推薦：MySQL數據庫(視頻)
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
