第09章_性能分析工具的使用

在數(shù)據(jù)庫調優(yōu)中，我們的目標是 響應時間更快, 吞吐量更大 。利用宏觀的監(jiān)控工具和微觀的日志分析可以幫我們快速找到調優(yōu)的思路和方式。

1. 數(shù)據(jù)庫服務器的優(yōu)化步驟

當我們遇到數(shù)據(jù)庫調優(yōu)問題的時候，該如何思考呢？這里把思考的流程整理成下面這張圖。

整個流程劃分成了 觀察（Show status） 和 行動（Action） 兩個部分。字母 S 的部分代表觀察（會使 用相應的分析工具），字母 A 代表的部分是行動（對應分析可以采取的行動）。

我們可以通過觀察了解數(shù)據(jù)庫整體的運行狀態(tài)，通過性能分析工具可以讓我們了解執(zhí)行慢的SQL都有哪些，查看具體的SQL執(zhí)行計劃，甚至是SQL執(zhí)行中的每一步的成本代價，這樣才能定位問題所在，找到了問題，再采取相應的行動。

詳細解釋一下這張圖：

2. 查看系統(tǒng)性能參數(shù)

在MySQL中，可以使用 SHOW STATUS 語句查詢一些MySQL數(shù)據(jù)庫服務器的性能參數(shù)、執(zhí)行頻率。

SHOW STATUS語句語法如下：

SHOW [GLOBAL|SESSION] STATUS LIKE '參數(shù)';

一些常用的性能參數(shù)如下：

• Connections：連接MySQL服務器的次數(shù)。
• Uptime：MySQL服務器的上線時間。
• Slow_queries：慢查詢的次數(shù)。
• Innodb_rows_read：Select查詢返回的行數(shù)
• Innodb_rows_inserted：執(zhí)行INSERT操作插入的行數(shù)
• Innodb_rows_updated：執(zhí)行UPDATE操作更新的 行數(shù)
• Innodb_rows_deleted：執(zhí)行DELETE操作刪除的行數(shù)
• Com_select：查詢操作的次數(shù)。
• Com_insert：插入操作的次數(shù)。對于批量插入的 INSERT 操作，只累加一次。
• Com_update：更新操作 的次數(shù)。
• Com_delete：刪除操作的次數(shù)。

若查詢MySQL服務器的連接次數(shù)，則可以執(zhí)行如下語句:

SHOW STATUS LIKE 'Connections';

若查詢服務器工作時間，則可以執(zhí)行如下語句:

SHOW STATUS LIKE 'Uptime';

若查詢MySQL服務器的慢查詢次數(shù)，則可以執(zhí)行如下語句:

SHOW STATUS LIKE 'Slow_queries';

慢查詢次數(shù)參數(shù)可以結合慢查詢日志找出慢查詢語句，然后針對慢查詢語句進行表結構優(yōu)化或者查詢語句優(yōu)化。

再比如，如下的指令可以查看相關的指令情況：

SHOW STATUS LIKE 'Innodb_rows_%';

3. 統(tǒng)計SQL的查詢成本: last_query_cost

一條SQL查詢語句在執(zhí)行前需要查詢執(zhí)行計劃，如果存在多種執(zhí)行計劃的話，MySQL會計算每個執(zhí)行計劃所需要的成本，從中選擇成本最小的一個作為最終執(zhí)行的執(zhí)行計劃。

如果我們想要查看某條SQL語句的查詢成本，可以在執(zhí)行完這條SQL語句之后，通過查看當前會話中的last_query_cost變量值來得到當前查詢的成本。它通常也是我們評價一個查詢的執(zhí)行效率的一個常用指標。這個查詢成本對應的是SQL 語句所需要讀取的讀頁的數(shù)量。

我們依然使用第8章的 student_info 表為例：

CREATE TABLE `student_info` (`id` INT(11) NOT NULL AUTO_INCREMENT,`student_id` INT NOT NULL ,`name` VARCHAR(20) DEFAULT NULL,`course_id` INT NOT NULL ,`class_id` INT(11) DEFAULT NULL,`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

如果我們想要查詢 id=900001 的記錄，然后看下查詢成本，我們可以直接在聚簇索引上進行查找：

SELECT student_id, class_id, NAME, create_time FROM student_info WHERE id = 900001;

運行結果（1 條記錄，運行時間為 0.042s ）

然后再看下查詢優(yōu)化器的成本，實際上我們只需要檢索一個頁即可：

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+----------+
| Variable_name   |   Value  |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+

如果我們想要查詢 id 在 900001 到 9000100 之間的學生記錄呢？

SELECT student_id, class_id, NAME, create_time 
FROM student_info
WHERE id BETWEEN 900001 AND 900100;

運行結果（100 條記錄，運行時間為 0.046s ）：

然后再看下查詢優(yōu)化器的成本，這時我們大概需要進行 20 個頁的查詢。

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name   |   Value   |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+

你能看到頁的數(shù)量是剛才的 20 倍，但是查詢的效率并沒有明顯的變化，實際上這兩個 SQL 查詢的時間 基本上一樣，就是因為采用了順序讀取的方式將頁面一次性加載到緩沖池中，然后再進行查找。雖然 頁 數(shù)量（last_query_cost）增加了不少 ，但是通過緩沖池的機制，并 沒有增加多少查詢時間 。

使用場景：它對于比較開銷是非常有用的，特別是我們有好幾種查詢方式可選的時候。

SQL查詢時一個動態(tài)的過程，從頁加載的角度來看，我們可以得到以下兩點結論：

1. 位置決定效率。如果頁就在數(shù)據(jù)庫 緩沖池 中，那么效率是最高的，否則還需要從 內存 或者 磁盤 中進行讀取，當然針對單個頁的讀取來說，如果頁存在于內存中，會比在磁盤中讀取效率高很多。
2. 批量決定效率。如果我們從磁盤中對單一頁進行隨機讀，那么效率是很低的(差不多10ms)，而采用順序讀取的方式，批量對頁進行讀取，平均一頁的讀取效率就會提升很多，甚至要快于單個頁面在內存中的隨機讀取。

所以說，遇到I/O并不用擔心，方法找對了，效率還是很高的。我們首先要考慮數(shù)據(jù)存放的位置，如果是進程使用的數(shù)據(jù)就要盡量放到緩沖池中，其次我們可以充分利用磁盤的吞吐能力，一次性批量讀取數(shù)據(jù)，這樣單個頁的讀取效率也就得到了提升。

4. 定位執(zhí)行慢的 SQL：慢查詢日志

4.1 開啟慢查詢日志參數(shù)

1. 開啟 slow_query_log

在使用前，我們需要先查下慢查詢是否已經(jīng)開啟，使用下面這條命令即可：

mysql > show variables like '%slow_query_log';

我們可以看到 slow_query_log=OFF，我們可以把慢查詢日志打開，注意設置變量值的時候需要使用 global，否則會報錯：

mysql > set global slow_query_log='ON';

然后我們再來查看下慢查詢日志是否開啟，以及慢查詢日志文件的位置：

你能看到這時慢查詢分析已經(jīng)開啟，同時文件保存在 /var/lib/mysql/atguigu02-slow.log 文件 中。

2. 修改 long_query_time 閾值

接下來我們來看下慢查詢的時間閾值設置，使用如下命令：

mysql > show variables like '%long_query_time%';

這里如果我們想把時間縮短，比如設置為 1 秒，可以這樣設置：

#測試發(fā)現(xiàn)：設置global的方式對當前session的long_query_time失效。對新連接的客戶端有效。所以可以一并
執(zhí)行下述語句
mysql > set global long_query_time = 1;
mysql> show global variables like '%long_query_time%';mysql> set long_query_time=1;
mysql> show variables like '%long_query_time%';

補充：配置文件中一并設置參數(shù)

如下的方式相較于前面的命令行方式，可以看做是永久設置的方式。

修改 my.cnf 文件，[mysqld] 下增加或修改參數(shù) long_query_time、slow_query_log 和 slow_query_log_file 后，然后重啟 MySQL 服務器。

[mysqld]
slow_query_log=ON  # 開啟慢查詢日志開關
slow_query_log_file=/var/lib/mysql/atguigu-low.log  # 慢查詢日志的目錄和文件名信息
long_query_time=3  # 設置慢查詢的閾值為3秒，超出此設定值的SQL即被記錄到慢查詢日志
log_output=FILE

如果不指定存儲路徑，慢查詢日志默認存儲到MySQL數(shù)據(jù)庫的數(shù)據(jù)文件夾下。如果不指定文件名，默認文件名為hostname_slow.log。

4.2 查看慢查詢數(shù)目

查詢當前系統(tǒng)中有多少條慢查詢記錄

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

4.3 案例演示

步驟1. 建表

CREATE TABLE `student` (`id` INT(11) NOT NULL AUTO_INCREMENT,`stuno` INT NOT NULL ,`name` VARCHAR(20) DEFAULT NULL,`age` INT(3) DEFAULT NULL,`classId` INT(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步驟2：設置參數(shù) log_bin_trust_function_creators

創(chuàng)建函數(shù)，假如報錯：

This function has none of DETERMINISTIC......

• 命令開啟：允許創(chuàng)建函數(shù)設置：

set global log_bin_trust_function_creators=1; # 不加global只是當前窗口有效。

步驟3：創(chuàng)建函數(shù)

隨機產生字符串：（同上一章）

DELIMITER //
CREATE FUNCTION rand_string(n INT)RETURNS VARCHAR(255) #該函數(shù)會返回一個字符串
BEGINDECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i < n DOSET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));SET i = i + 1;END WHILE;RETURN return_str;
END //
DELIMITER ;# 測試
SELECT rand_string(10);

產生隨機數(shù)值：（同上一章）

DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGINDECLARE i INT DEFAULT 0;SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;RETURN i;
END //
DELIMITER ;#測試：
SELECT rand_num(10,100);

步驟4：創(chuàng)建存儲過程

DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;SET autocommit = 0; #設置手動提交事務REPEAT #循環(huán)SET i = i + 1; #賦值INSERT INTO student (stuno, NAME ,age ,classId ) VALUES((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));UNTIL i = max_numEND REPEAT;COMMIT; #提交事務
END //
DELIMITER ;

步驟5：調用存儲過程

#調用剛剛寫好的函數(shù), 4000000條記錄,從100001號開始CALL insert_stu1(100001,4000000);

4.4 測試及分析

1. 測試

mysql> SELECT * FROM student WHERE stuno = 3455655;
+---------+---------+--------+------+---------+
|   id    |  stuno  |  name  | age  | classId |
+---------+---------+--------+------+---------+
| 3523633 | 3455655 | oQmLUr |  19  |    39   |
+---------+---------+--------+------+---------+
1 row in set (2.09 sec)mysql> SELECT * FROM student WHERE name = 'oQmLUr';
+---------+---------+--------+------+---------+
|   id    |  stuno  |  name  |  age | classId |
+---------+---------+--------+------+---------+
| 1154002 | 1243200 | OQMlUR | 266  |   28    |
| 1405708 | 1437740 | OQMlUR | 245  |   439   |
| 1748070 | 1680092 | OQMlUR | 240  |   414   |
| 2119892 | 2051914 | oQmLUr | 17   |   32    |
| 2893154 | 2825176 | OQMlUR | 245  |   435   |
| 3523633 | 3455655 | oQmLUr | 19   |   39    |
+---------+---------+--------+------+---------+
6 rows in set (2.39 sec)

從上面的結果可以看出來，查詢學生編號為“3455655”的學生信息花費時間為2.09秒。查詢學生姓名為 “oQmLUr”的學生信息花費時間為2.39秒。已經(jīng)達到了秒的數(shù)量級，說明目前查詢效率是比較低的，下面 的小節(jié)我們分析一下原因。

2. 分析

show status like 'slow_queries';

4.5 慢查詢日志分析工具：mysqldumpslow

在生產環(huán)境中，如果要手工分析日志，查找、分析SQL，顯然是個體力活，MySQL提供了日志分析工具 mysqldumpslow 。

查看mysqldumpslow的幫助信息

mysqldumpslow --help

mysqldumpslow 命令的具體參數(shù)如下：

• -a: 不將數(shù)字抽象成N，字符串抽象成S
• -s: 是表示按照何種方式排序：
  • c: 訪問次數(shù)
  • l: 鎖定時間
  • r: 返回記錄
  • t: 查詢時間
  • al:平均鎖定時間
  • ar:平均返回記錄數(shù)
  • at:平均查詢時間 （默認方式）
  • ac:平均查詢次數(shù)
• -t: 即為返回前面多少條的數(shù)據(jù)；
• -g: 后邊搭配一個正則匹配模式，大小寫不敏感的；

舉例：我們想要按照查詢時間排序，查看前五條 SQL 語句，這樣寫即可：

mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log

[root@bogon ~]# mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.logReading mysql slow query log from /var/lib/mysql/atguigu01-slow.log
Count: 1 Time=2.39s (2s) Lock=0.00s (0s) Rows=13.0 (13), root[root]@localhost
SELECT * FROM student WHERE name = 'S'Count: 1 Time=2.09s (2s) Lock=0.00s (0s) Rows=2.0 (2), root[root]@localhost
SELECT * FROM student WHERE stuno = NDied at /usr/bin/mysqldumpslow line 162, <> chunk 2.

工作常用參考：

#得到返回記錄集最多的10個SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log#得到訪問次數(shù)最多的10個SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log#得到按照時間排序的前10條里面含有左連接的查詢語句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log#另外建議在使用這些命令時結合 | 和more 使用 ，否則有可能出現(xiàn)爆屏情況
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

4.6 關閉慢查詢日志

MySQL服務器停止慢查詢日志功能有兩種方法：

方式1：永久性方式

[mysqld]
slow_query_log=OFF

或者，把slow_query_log一項注釋掉 或 刪除

[mysqld]
#slow_query_log =OFF

重啟MySQL服務，執(zhí)行如下語句查詢慢日志功能。

SHOW VARIABLES LIKE '%slow%'; #查詢慢查詢日志所在目錄
SHOW VARIABLES LIKE '%long_query_time%'; #查詢超時時長

方式2：臨時性方式

使用SET語句來設置。

（1）停止MySQL慢查詢日志功能，具體SQL語句如下。

SET GLOBAL slow_query_log=off;

（2）重啟MySQL服務，使用SHOW語句查詢慢查詢日志功能信息，具體SQL語句如下。

SHOW VARIABLES LIKE '%slow%';
#以及
SHOW VARIABLES LIKE '%long_query_time%';

4.7 刪除慢查詢日志

使用SHOW語句顯示慢查詢日志信息，具體SQL語句如下。

SHOW VARIABLES LIKE `slow_query_log%`;

從執(zhí)行結果可以看出，慢查詢日志的目錄默認為MySQL的數(shù)據(jù)目錄，在該目錄下 手動刪除慢查詢日志文件 即可。

使用命令 mysqladmin flush-logs 來重新生成查詢日志文件，具體命令如下，執(zhí)行完畢會在數(shù)據(jù)目錄下重新生成慢查詢日志文件。

mysqladmin -uroot -p flush-logs slow

提示

慢查詢日志都是使用mysqladmin flush-logs命令來刪除重建的。使用時一定要注意，一旦執(zhí)行了這個命令，慢查詢日志都只存在新的日志文件中，如果需要舊的查詢日志，就必須事先備份。

5. 查看 SQL 執(zhí)行成本：SHOW PROFILE

show profile 在《邏輯架構》章節(jié)中講過，這里作為復習。

show profile 是 MySQL 提供的可以用來分析當前會話中 SQL 都做了什么、執(zhí)行的資源消耗工具的情況，可用于 sql 調優(yōu)的測量。默認情況下處于關閉狀態(tài)，并保存最近15次的運行結果。

我們可以在會話級別開啟這個功能。

mysql > show variables like 'profiling';

通過設置 profiling=‘ON’ 來開啟 show profile:

mysql > set profiling = 'ON';

然后執(zhí)行相關的查詢語句。接著看下當前會話都有哪些 profiles，使用下面這條命令：

mysql > show profiles;

你能看到當前會話一共有 2 個查詢。如果我們想要查看最近一次查詢的開銷，可以使用：

mysql > show profile;

mysql> show profile cpu,block io for query 2

show profile的常用查詢參數(shù)：

① ALL：顯示所有的開銷信息。

② BLOCK IO：顯示塊IO開銷。

③ CONTEXT SWITCHES：上下文切換開銷。

④ CPU：顯示CPU開銷信息。

⑤ IPC：顯示發(fā)送和接收開銷信息。

⑥ MEMORY：顯示內存開銷信 息。

⑦ PAGE FAULTS：顯示頁面錯誤開銷信息。

⑧ SOURCE：顯示和Source_function，Source_file， Source_line相關的開銷信息。

⑨ SWAPS：顯示交換次數(shù)開銷信息。

日常開發(fā)需注意的結論：

① converting HEAP to MyISAM: 查詢結果太大，內存不夠，數(shù)據(jù)往磁盤上搬了。

② Creating tmp table：創(chuàng)建臨時表。先拷貝數(shù)據(jù)到臨時表，用完后再刪除臨時表。

③ Copying to tmp table on disk：把內存中臨時表復制到磁盤上，警惕！

④ locked。

如果在show profile診斷結果中出現(xiàn)了以上4條結果中的任何一條，則sql語句需要優(yōu)化。

注意：

不過SHOW PROFILE命令將被啟用，我們可以從 information_schema 中的 profiling 數(shù)據(jù)表進行查看。

6. 分析查詢語句：EXPLAIN

6.1 概述

1. 能做什么？

• 表的讀取順序
• 數(shù)據(jù)讀取操作的操作類型
• 哪些索引可以使用
• 哪些索引被實際使用
• 表之間的引用
• 每張表有多少行被優(yōu)化器查詢

2. 官網(wǎng)介紹

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

3. 版本情況

• MySQL 5.6.3以前只能 EXPLAIN SELECT ；MYSQL 5.6.3以后就可以 EXPLAIN SELECT，UPDATE， DELETE
• 在5.7以前的版本中，想要顯示 partitions 需要使用 explain partitions 命令；想要顯示 filtered 需要使用 explain extended 命令。在5.7版本后，默認explain直接顯示partitions和 filtered中的信息。

6.2 基本語法

EXPLAIN 或 DESCRIBE語句的語法形式如下：

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

如果我們想看看某個查詢的執(zhí)行計劃的話，可以在具體的查詢語句前邊加一個 EXPLAIN ，就像這樣：

mysql> EXPLAIN SELECT 1;

EXPLAIN 語句輸出的各個列的作用如下：

在這里把它們都列出來知識為了描述一個輪廓，讓大家有一個大致的印象。

6.3 數(shù)據(jù)準備

1. 建表

CREATE TABLE s1 (id INT AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),INDEX idx_key1 (key1),UNIQUE INDEX idx_key2 (key2),INDEX idx_key3 (key3),INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE s2 (id INT AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),INDEX idx_key1 (key1),UNIQUE INDEX idx_key2 (key2),INDEX idx_key3 (key3),INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

2. 設置參數(shù) log_bin_trust_function_creators

創(chuàng)建函數(shù)，假如報錯，需開啟如下命令：允許創(chuàng)建函數(shù)設置：

set global log_bin_trust_function_creators=1; # 不加global只是當前窗口有效。

3. 創(chuàng)建函數(shù)

DELIMITER //
CREATE FUNCTION rand_string1(n INT)RETURNS VARCHAR(255) #該函數(shù)會返回一個字符串
BEGINDECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i < n DOSET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));SET i = i + 1;END WHILE;RETURN return_str;
END //
DELIMITER ;

4. 創(chuàng)建存儲過程

創(chuàng)建往s1表中插入數(shù)據(jù)的存儲過程：

DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO s1 VALUES((min_num + i),rand_string1(6),(min_num + 30 * i + 5),rand_string1(6),rand_string1(10),rand_string1(5),rand_string1(10),rand_string1(10));UNTIL i = max_numEND REPEAT;COMMIT;
END //
DELIMITER ;

創(chuàng)建往s2表中插入數(shù)據(jù)的存儲過程：

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO s2 VALUES((min_num + i),rand_string1(6),(min_num + 30 * i + 5),rand_string1(6),rand_string1(10),rand_string1(5),rand_string1(10),rand_string1(10));UNTIL i = max_numEND REPEAT;COMMIT;
END //
DELIMITER ;

5. 調用存儲過程

s1表數(shù)據(jù)的添加：加入1萬條記錄：

CALL insert_s1(10001,10000);

s2表數(shù)據(jù)的添加：加入1萬條記錄：

CALL insert_s2(10001,10000);

6.4 EXPLAIN各列作用

為了讓大家有比較好的體驗，我們調整了下 EXPLAIN 輸出列的順序。

1. table

不論我們的查詢語句有多復雜，里邊兒 包含了多少個表 ，到最后也是需要對每個表進行 單表訪問 的，所 以MySQL規(guī)定EXPLAIN語句輸出的每條記錄都對應著某個單表的訪問方法，該條記錄的table列代表著該 表的表名（有時不是真實的表名字，可能是簡稱）。

mysql > EXPLAIN SELECT * FROM s1;

這個查詢語句只涉及對s1表的單表查詢，所以 EXPLAIN 輸出中只有一條記錄，其中的table列的值為s1，表明這條記錄是用來說明對s1表的單表訪問方法的。

下邊我們看一個連接查詢的執(zhí)行計劃

mysql > EXPLAIN SELECT * FROM s1 INNER JOIN s2;

可以看出這個連接查詢的執(zhí)行計劃中有兩條記錄，這兩條記錄的table列分別是s1和s2，這兩條記錄用來分別說明對s1表和s2表的訪問方法是什么。

2. id

我們寫的查詢語句一般都以 SELECT 關鍵字開頭，比較簡單的查詢語句里只有一個 SELECT 關鍵字，比 如下邊這個查詢語句：

SELECT * FROM s1 WHERE key1 = 'a';

稍微復雜一點的連接查詢中也只有一個 SELECT 關鍵字，比如：

SELECT * FROM s1 INNER JOIN s2
ON s1.key1 = s2.key1
WHERE s1.common_field = 'a';

但是下邊兩種情況下在一條查詢語句中會出現(xiàn)多個SELECT關鍵字：

mysql > EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

對于連接查詢來說，一個SELECT關鍵字后邊的FROM字句中可以跟隨多個表，所以在連接查詢的執(zhí)行計劃中，每個表都會對應一條記錄，但是這些記錄的id值都是相同的，比如：

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;

可以看到，上述連接查詢中參與連接的s1和s2表分別對應一條記錄，但是這兩條記錄對應的id都是1。這里需要大家記住的是，在連接查詢的執(zhí)行計劃中，每個表都會對應一條記錄，這些記錄的id列的值是相同的，出現(xiàn)在前邊的表表示驅動表，出現(xiàn)在后面的表表示被驅動表。所以從上邊的EXPLAIN輸出中我們可以看到，查詢優(yōu)化器準備讓s1表作為驅動表，讓s2表作為被驅動表來執(zhí)行查詢。

對于包含子查詢的查詢語句來說，就可能涉及多個SELECT關鍵字，所以在**包含子查詢的查詢語句的執(zhí)行計劃中，每個SELECT關鍵字都會對應一個唯一的id值，比如這樣：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

# 查詢優(yōu)化器可能對涉及子查詢的查詢語句進行重寫，轉變?yōu)槎啾聿樵兊牟僮鳌? 
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');

可以看到，雖然我們的查詢語句是一個子查詢，但是執(zhí)行計劃中s1和s2表對應的記錄的id值全部是1，這就表明查詢優(yōu)化器將子查詢轉換為了連接查詢。

對于包含UNION子句的查詢語句來說，每個SELECT關鍵字對應一個id值也是沒錯的，不過還是有點兒特別的東西，比方說下邊的查詢：

# Union去重
mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

mysql> EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

小結:

• id如果相同，可以認為是一組，從上往下順序執(zhí)行
• 在所有組中，id值越大，優(yōu)先級越高，越先執(zhí)行
• 關注點：id號每個號碼，表示一趟獨立的查詢, 一個sql的查詢趟數(shù)越少越好

3. select_type

具體分析如下：

• SIMPLE

  查詢語句中不包含UNION或者子查詢的查詢都算作是SIMPLE類型，比方說下邊這個單表查詢select_type的值就是SIMPLE:

  mysql> EXPLAIN SELECT * FROM s1;
  

  

? 當然，連接查詢也算是 SIMPLE 類型，比如：

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;

• PRIMARY

  對于包含UNION、UNION ALL或者子查詢的大查詢來說，它是由幾個小查詢組成的，其中最左邊的那個查詢的select_type的值就是PRIMARY,比方說：

  mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
  

  

  從結果中可以看到，最左邊的小查詢SELECT * FROM s1對應的是執(zhí)行計劃中的第一條記錄，它的select_type的值就是PRIMARY。

• UNION

  對于包含UNION或者UNION ALL的大查詢來說，它是由幾個小查詢組成的，其中除了最左邊的那個小查詢意外，其余的小查詢的select_type值就是UNION，可以對比上一個例子的效果。

• UNION RESULT

  MySQL 選擇使用臨時表來完成UNION查詢的去重工作，針對該臨時表的查詢的select_type就是UNION RESULT, 例子上邊有。

• SUBQUERY

  如果包含子查詢的查詢語句不能夠轉為對應的semi-join的形式，并且該子查詢是不相關子查詢，并且查詢優(yōu)化器決定采用將該子查詢物化的方案來執(zhí)行該子查詢時，該子查詢的第一個SELECT關鍵字代表的那個查詢的select_type就是SUBQUERY，比如下邊這個查詢：

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
  

  

• DEPENDENT SUBQUERY

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
  

  

• DEPENDENT UNION

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');
  

  

• DERIVED

  mysql> EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;
  

  

  從執(zhí)行計劃中可以看出，id為2的記錄就代表子查詢的執(zhí)行方式，它的select_type是DERIVED, 說明該子查詢是以物化的方式執(zhí)行的。id為1的記錄代表外層查詢，大家注意看它的table列顯示的是derived2，表示該查詢時針對將派生表物化之后的表進行查詢的。

• MATERIALIZED

  當查詢優(yōu)化器在執(zhí)行包含子查詢的語句時，選擇將子查詢物化之后的外層查詢進行連接查詢時，該子查詢對應的select_type屬性就是DERIVED，比如下邊這個查詢：

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
  

  

• UNCACHEABLE SUBQUERY

  不常用

• UNCACHEABLE UNION

  不常用

4. partitions (可略)

• 代表分區(qū)表中的命中情況，非分區(qū)表，該項為NULL。一般情況下我們的額查詢語句的執(zhí)行計劃的partitions列的值為NULL。
• https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html
• 如果想詳細了解，可以如下方式測試。創(chuàng)建分區(qū)表：

-- 創(chuàng)建分區(qū)表，
-- 按照id分區(qū)，id<100 p0分區(qū)，其他p1分區(qū)
CREATE TABLE user_partitions (id INT auto_increment,
NAME VARCHAR(12),PRIMARY KEY(id))
PARTITION BY RANGE(id)(
PARTITION p0 VALUES less than(100),
PARTITION p1 VALUES less than MAXVALUE
);

DESC SELECT * FROM user_partitions WHERE id>200;

查詢id大于200（200>100，p1分區(qū)）的記錄，查看執(zhí)行計劃，partitions是p1，符合我們的分區(qū)規(guī)則

5. type ☆

執(zhí)行計劃的一條記錄就代表著MySQL對某個表的 執(zhí)行查詢時的訪問方法 , 又稱“訪問類型”，其中的 type 列就表明了這個訪問方法是啥，是較為重要的一個指標。比如，看到type列的值是ref，表明MySQL即將使用ref訪問方法來執(zhí)行對s1表的查詢。

完整的訪問方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。

我們詳細解釋一下：

• system

  當表中只有一條記錄并且該表使用的存儲引擎的統(tǒng)計數(shù)據(jù)是精確的，比如MyISAM、Memory，那么對該表的訪問方法就是system。比方說我們新建一個MyISAM表，并為其插入一條記錄：

  mysql> CREATE TABLE t(i int) Engine=MyISAM;
  Query OK, 0 rows affected (0.05 sec)mysql> INSERT INTO t VALUES(1);
  Query OK, 1 row affected (0.01 sec)
  

  然后我們看一下查詢這個表的執(zhí)行計劃：

  mysql> EXPLAIN SELECT * FROM t;
  

  

  可以看到type列的值就是system了，

  測試，可以把表改成使用InnoDB存儲引擎，試試看執(zhí)行計劃的type列是什么。ALL

• const

  當我們根據(jù)主鍵或者唯一二級索引列與常數(shù)進行等值匹配時，對單表的訪問方法就是const, 比如：

  mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005;
  

  

• eq_ref

  在連接查詢時，如果被驅動表是通過主鍵或者唯一二級索引列等值匹配的方式進行訪問的（如果該主鍵或者唯一二級索引是聯(lián)合索引的話，所有的索引列都必須進行等值比較）。則對該被驅動表的訪問方法就是eq_ref，比方說：

  mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
  

  

  從執(zhí)行計劃的結果中可以看出，MySQL打算將s2作為驅動表，s1作為被驅動表，重點關注s1的訪問 方法是 eq_ref ，表明在訪問s1表的時候可以 通過主鍵的等值匹配 來進行訪問。

• ref

  當通過普通的二級索引列與常量進行等值匹配時來查詢某個表，那么對該表的訪問方法就可能是ref，比方說下邊這個查詢：

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
  

  

• fulltext

  全文索引

• ref_or_null

  當對普通二級索引進行等值匹配查詢，該索引列的值也可以是NULL值時，那么對該表的訪問方法就可能是ref_or_null，比如說：

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
  

  

• index_merge

  一般情況下對于某個表的查詢只能使用到一個索引，但單表訪問方法時在某些場景下可以使用Interseation、union、Sort-Union這三種索引合并的方式來執(zhí)行查詢。我們看一下執(zhí)行計劃中是怎么體現(xiàn)MySQL使用索引合并的方式來對某個表執(zhí)行查詢的：

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
  

  

  從執(zhí)行計劃的 type 列的值是 index_merge 就可以看出，MySQL 打算使用索引合并的方式來執(zhí)行 對 s1 表的查詢。

• unique_subquery

  類似于兩表連接中被驅動表的eq_ref訪問方法，unique_subquery是針對在一些包含IN子查詢的查詢語句中，如果查詢優(yōu)化器決定將IN子查詢轉換為EXISTS子查詢，而且子查詢可以使用到主鍵進行等值匹配的話，那么該子查詢執(zhí)行計劃的type列的值就是unique_subquery，比如下邊的這個查詢語句：

  mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';
  

  

• index_subquery

  index_subquery 與 unique_subquery 類似，只不過訪問子查詢中的表時使用的是普通的索引，比如這樣：

  mysql> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';
  

  

• range

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
  

  

  或者：

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';
  

  

• index

  當我們可以使用索引覆蓋，但需要掃描全部的索引記錄時，該表的訪問方法就是index，比如這樣：

  mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
  

  

  上述查詢中的所有列表中只有key_part2 一個列，而且搜索條件中也只有 key_part3 一個列，這兩個列又恰好包含在idx_key_part這個索引中，可是搜索條件key_part3不能直接使用該索引進行ref和range方式的訪問，只能掃描整個idx_key_part索引的記錄，所以查詢計劃的type列的值就是index。

  再一次強調，對于使用InnoDB存儲引擎的表來說，二級索引的記錄只包含索引列和主鍵列的值，而聚簇索引中包含用戶定義的全部列以及一些隱藏列，所以掃描二級索引的代價比直接全表掃描，也就是掃描聚簇索引的代價更低一些。

• ALL

  最熟悉的全表掃描，就不多說了，直接看例子：

  mysql> EXPLAIN SELECT * FROM s1;
  

  

小結:

結果值從最好到最壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

其中比較重要的幾個提取出來（見上圖中的粗體）。SQL 性能優(yōu)化的目標：至少要達到 range 級別，要求是 ref 級別，最好是 consts級別。（阿里巴巴 開發(fā)手冊要求）

6. possible_keys和key

在EXPLAIN語句輸出的執(zhí)行計劃中，possible_keys列表示在某個查詢語句中，對某個列執(zhí)行單表查詢時可能用到的索引有哪些。一般查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用。key列表示實際用到的索引有哪些，如果為NULL，則沒有使用索引。比方說下面這個查詢：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

上述執(zhí)行計劃的possible_keys列的值是idx_key1, idx_key3，表示該查詢可能使用到idx_key1, idx_key3兩個索引，然后key列的值是idx_key3，表示經(jīng)過查詢優(yōu)化器計算使用不同索引的成本后，最后決定采用idx_key3。

7. key_len ☆

實際使用到的索引長度 (即：字節(jié)數(shù))

幫你檢查是否充分的利用了索引，值越大越好，主要針對于聯(lián)合索引，有一定的參考意義。

mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005;

int 占用 4 個字節(jié)

mysql> EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

key2上有一個唯一性約束，是否為NULL占用一個字節(jié)，那么就是5個字節(jié)

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

key1 VARCHAR(100) 一個字符占3個字節(jié)，100*3，是否為NULL占用一個字節(jié)，varchar的長度信息占兩個字節(jié)。

mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';

mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

聯(lián)合索引中可以比較，key_len=606的好于key_len=303

練習：

key_len的長度計算公式：

varchar(10)變長字段且允許NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(變長字段)varchar(10)變長字段且不允許NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(變長字段)char(10)固定字段且允許NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)char(10)固定字段且不允許NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

8. ref

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

可以看到ref列的值是const，表明在使用idx_key1索引執(zhí)行查詢時，與key1列作等值匹配的對象是一個常數(shù)，當然有時候更復雜一點:

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

9. rows ☆

預估的需要讀取的記錄條數(shù)，值越小越好。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

10. filtered

某個表經(jīng)過搜索條件過濾后剩余記錄條數(shù)的百分比

如果使用的是索引執(zhí)行的單表掃描，那么計算時需要估計出滿足除使用到對應索引的搜索條件外的其他搜索條件的記錄有多少條。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';

對于單表查詢來說，這個filtered的值沒有什么意義，我們更關注在連接查詢中驅動表對應的執(zhí)行計劃記錄的filtered值，它決定了被驅動表要執(zhí)行的次數(shù) (即: rows * filtered)

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

從執(zhí)行計劃中可以看出來，查詢優(yōu)化器打算把s1作為驅動表，s2當做被驅動表。我們可以看到驅動表s1表的執(zhí)行計劃的rows列為9688，filtered列為10.00，這意味著驅動表s1的扇出值就是9688 x 10.00% = 968.8，這說明還要對被驅動表執(zhí)行大約968次查詢。

11. Extra ☆

顧名思義，Extra列是用來說明一些額外信息的，包含不適合在其他列中顯示但十分重要的額外信息。我們可以通過這些額外信息來更準確的理解MySQL到底將如何執(zhí)行給定的查詢語句。MySQL提供的額外信息有好幾十個，我們就不一個一個介紹了，所以我們只挑選比較重要的額外信息介紹給大家。

• No tables used

  當查詢語句沒有FROM子句時將會提示該額外信息，比如：

  mysql> EXPLAIN SELECT 1;
  

  

• Impossible WHERE

  當查詢語句的WHERE子句永遠為FALSE時將會提示該額外信息

  mysql> EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
  

  

• Using where

mysql> EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';

• No matching min/max row

  當查詢列表處有MIN或者MAX聚合函數(shù)，但是并沒有符合WHERE子句中的搜索條件的記錄時。

  mysql> EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
  

  

• Using index

  當我們的查詢列表以及搜索條件中只包含屬于某個索引的列，也就是在可以使用覆蓋索引的情況下，在Extra列將會提示該額外信息。比方說下邊這個查詢中只需要用到idx_key1而不需要回表操作:

  mysql> EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';
  

  

• Using index condition

  有些搜索條件中雖然出現(xiàn)了索引列，但卻不能使用到索引，比如下邊這個查詢：

  SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
  

  
  

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';
  

  

• Using join buffer (Block Nested Loop)

  在連接查詢執(zhí)行過程中，當被驅動表不能有效的利用索引加快訪問速度，MySQL一般會為其分配一塊名叫join buffer的內存塊來加快查詢速度，也就是我們所講的基于塊的嵌套循環(huán)算法。

  mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
  

  

• Not exists

  當我們使用左(外)連接時，如果WHERE子句中包含要求被驅動表的某個列等于NULL值的搜索條件，而且那個列是不允許存儲NULL值的，那么在該表的執(zhí)行計劃的Extra列就會提示這個信息：

  mysql> EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
  

  

• Using intersect(...) 、 Using union(...) 和 Using sort_union(...)

  如果執(zhí)行計劃的Extra列出現(xiàn)了Using intersect(...)提示，說明準備使用Intersect索引合并的方式執(zhí)行查詢，括號中的...表示需要進行索引合并的索引名稱；

  如果出現(xiàn)Using union(...)提示，說明準備使用Union索引合并的方式執(zhí)行查詢;

  如果出現(xiàn)Using sort_union(...)提示，說明準備使用Sort-Union索引合并的方式執(zhí)行查詢。

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
  

  

• Zero limit

  當我們的LIMIT子句的參數(shù)為0時，表示壓根兒不打算從表中讀取任何記錄，將會提示該額外信息

  mysql> EXPLAIN SELECT * FROM s1 LIMIT 0;
  

  

• Using filesort

  有一些情況下對結果集中的記錄進行排序是可以使用到索引的。

  mysql> EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
  

  

  

  mysql> EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
  

  

  需要注意的是，如果查詢中需要使用filesort的方式進行排序的記錄非常多，那么這個過程是很耗費性能的，我們最好想辦法將使用文件排序的執(zhí)行方式改為索引進行排序。

• Using temporary
  

  mysql> EXPLAIN SELECT DISTINCT common_field FROM s1;
  

  

  再比如：

  mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
  

  

  執(zhí)行計劃中出現(xiàn)Using temporary并不是一個好的征兆，因為建立與維護臨時表要付出很大的成本的，所以我們最好能使用索引來替代掉使用臨時表，比方說下邊這個包含GROUP BY子句的查詢就不需要使用臨時表：

  mysql> EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
  

  

  從 Extra 的 Using index 的提示里我們可以看出，上述查詢只需要掃描 idx_key1 索引就可以搞 定了，不再需要臨時表了。

• 其他

  其它特殊情況這里省略。

12. 小結

• EXPLAIN不考慮各種Cache
• EXPLAIN不能顯示MySQL在執(zhí)行查詢時所作的優(yōu)化工作
• EXPLAIN不會告訴你關于觸發(fā)器、存儲過程的信息或用戶自定義函數(shù)對查詢的影響情況
• 部分統(tǒng)計信息是估算的，并非精確值

7. EXPLAIN的進一步使用

7.1 EXPLAIN四種輸出格式

這里談談EXPLAIN的輸出格式。EXPLAIN可以輸出四種格式： 傳統(tǒng)格式 ，JSON格式 ， TREE格式 以及 可視化輸出 。用戶可以根據(jù)需要選擇適用于自己的格式。

1. 傳統(tǒng)格式

傳統(tǒng)格式簡單明了，輸出是一個表格形式，概要說明查詢計劃。

mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

2. JSON格式

第1種格式中介紹的EXPLAIN語句輸出中缺少了一個衡量執(zhí)行好壞的重要屬性 —— 成本。而JSON格式是四種格式里面輸出信息最詳盡的格式，里面包含了執(zhí)行的成本信息。

• JSON格式：在EXPLAIN單詞和真正的查詢語句中間加上 FORMAT=JSON 。

EXPLAIN FORMAT=JSON SELECT ....

• EXPLAIN的Column與JSON的對應關系：(來源于MySQL 5.7文檔)

  

這樣我們就可以得到一個json格式的執(zhí)行計劃，里面包含該計劃花費的成本。比如這樣：

mysql> EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G

我們使用 # 后邊跟隨注釋的形式為大家解釋了 EXPLAIN FORMAT=JSON 語句的輸出內容，但是大家可能 有疑問 “cost_info” 里邊的成本看著怪怪的，它們是怎么計算出來的？先看 s1 表的 “cost_info” 部 分：

"cost_info": {"read_cost": "1840.84","eval_cost": "193.76","prefix_cost": "2034.60","data_read_per_join": "1M"
}

• read_cost 是由下邊這兩部分組成的：

  • IO 成本
  • 檢測 rows × (1 - filter) 條記錄的 CPU 成本

  小貼士： rows和filter都是我們前邊介紹執(zhí)行計劃的輸出列，在JSON格式的執(zhí)行計劃中，rows 相當于rows_examined_per_scan，filtered名稱不變。

• eval_cost 是這樣計算的：

  檢測 rows × filter 條記錄的成本。

• prefix_cost 就是單獨查詢 s1 表的成本，也就是：

  read_cost + eval_cost

• data_read_per_join 表示在此次查詢中需要讀取的數(shù)據(jù)量。

對于 s2 表的 “cost_info” 部分是這樣的：

"cost_info": {"read_cost": "968.80","eval_cost": "193.76","prefix_cost": "3197.16","data_read_per_join": "1M"
}

由于 s2 表是被驅動表，所以可能被讀取多次，這里的read_cost 和 eval_cost 是訪問多次 s2 表后累加起來的值，大家主要關注里邊兒的 prefix_cost 的值代表的是整個連接查詢預計的成本，也就是單次查詢 s1 表和多次查詢 s2 表后的成本的和，也就是：

968.80 + 193.76 + 2034.60 = 3197.16

3. TREE格式

TREE格式是8.0.16版本之后引入的新格式，主要根據(jù)查詢的 各個部分之間的關系 和 各部分的執(zhí)行順序 來描述如何查詢。

mysql> EXPLAIN FORMAT=tree SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE
s1.common_field = 'a'\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=1360.08 rows=990)
-> Filter: ((s1.common_field = 'a') and (s1.key1 is not null)) (cost=1013.75
rows=990)
-> Table scan on s1 (cost=1013.75 rows=9895)
-> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index
condition: (cast(s1.key1 as double) = cast(s2.key2 as double)) (cost=0.25 rows=1)
1 row in set, 1 warning (0.00 sec)

4. 可視化輸出

可視化輸出，可以通過MySQL Workbench可視化查看MySQL的執(zhí)行計劃。通過點擊Workbench的放大鏡圖標，即可生成可視化的查詢計劃。

上圖按從左到右的連接順序顯示表。紅色框表示 全表掃描 ，而綠色框表示使用 索引查找 。對于每個表， 顯示使用的索引。還要注意的是，每個表格的框上方是每個表訪問所發(fā)現(xiàn)的行數(shù)的估計值以及訪問該表的成本。

7.2 SHOW WARNINGS的使用

在我們使用EXPLAIN語句查看了某個查詢的執(zhí)行計劃后，緊接著還可以使用SHOW WARNINGS語句查看與這個查詢的執(zhí)行計劃有關的一些擴展信息，比如這樣：

mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************Level: NoteCode: 1003
Message: /* select#1 */ select `atguigu`.`s1`.`key1` AS `key1`,`atguigu`.`s2`.`key1`
AS `key1` from `atguigu`.`s1` join `atguigu`.`s2` where ((`atguigu`.`s1`.`key1` =
`atguigu`.`s2`.`key1`) and (`atguigu`.`s2`.`common_field` is not null))
1 row in set (0.00 sec)

大家可以看到SHOW WARNINGS展示出來的信息有三個字段，分別是Level、Code、Message。我們最常見的就是Code為1003的信息，當Code值為1003時，Message字段展示的信息類似于查詢優(yōu)化器將我們的查詢語句重寫后的語句。比如我們上邊的查詢本來是一個左(外)連接查詢，但是有一個s2.common_field IS NOT NULL的條件，這就會導致查詢優(yōu)化器把左(外)連接查詢優(yōu)化為內連接查詢，從SHOW WARNINGS的Message字段也可以看出來，原本的LEFE JOIN已經(jīng)變成了JOIN。

但是大家一定要注意，我們說Message字段展示的信息類似于查詢優(yōu)化器將我們的查詢語句重寫后的語句，并不是等價于，也就是說Message字段展示的信息并不是標準的查詢語句，在很多情況下并不能直接拿到黑框框中運行，它只能作為幫助我們理解MySQL將如何執(zhí)行查詢語句的一個參考依據(jù)而已。

8. 分析優(yōu)化器執(zhí)行計劃：trace

SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

開啟后，可分析如下語句：

• SELECT
• INSERT
• REPLACE
• UPDATE
• DELETE
• EXPLAIN
• SET
• DECLARE
• CASE
• IF
• RETURN
• CALL

測試：執(zhí)行如下SQL語句

select * from student where id < 10;

最后， 查詢 information_schema.optimizer_trace 就可以知道MySQL是如何執(zhí)行SQL的 ：

select * from information_schema.optimizer_trace\G

*************************** 1. row ***************************
//第1部分：查詢語句
QUERY: select * from student where id < 10
//第2部分：QUERY字段對應語句的跟蹤信息
TRACE: {
"steps": [
{"join_preparation": { //預備工作"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `student`.`id` AS`id`,`student`.`stuno` AS `stuno`,`student`.`name` AS `name`,`student`.`age` AS`age`,`student`.`classId` AS `classId` from `student` where (`student`.`id` < 10)"}] /* steps */} /* join_preparation */
},
{"join_optimization": { //進行優(yōu)化"select#": 1,"steps": [{"condition_processing": { //條件處理"condition": "WHERE","original_condition": "(`student`.`id` < 10)","steps": [{"transformation": "equality_propagation","resulting_condition": "(`student`.`id` < 10)"},{"transformation": "constant_propagation","resulting_condition": "(`student`.`id` < 10)"},{"transformation": "trivial_condition_removal","resulting_condition": "(`student`.`id` < 10)"}] /* steps */} /* condition_processing */},{"substitute_generated_columns": { //替換生成的列} /* substitute_generated_columns */},{"table_dependencies": [ //表的依賴關系{"table": "`student`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": [] /* depends_on_map_bits */}] /* table_dependencies */},{"ref_optimizer_key_uses": [ //使用鍵] /* ref_optimizer_key_uses */},{"rows_estimation": [ //行判斷{"table": "`student`","range_analysis": {"table_scan": {"rows": 3973767,"cost": 408558} /* table_scan */, //掃描表"potential_range_indexes": [ //潛在的范圍索引{"index": "PRIMARY","usable": true,"key_parts": ["id"] /* key_parts */}] /* potential_range_indexes */,"setup_range_conditions": [ //設置范圍條件] /* setup_range_conditions */,"group_index_range": {"chosen": false,"cause": "not_group_by_or_distinct"} /* group_index_range */,"skip_scan_range": {"potential_skip_scan_indexes": [{"index": "PRIMARY","usable": false,"cause": "query_references_nonkey_column"}] /* potential_skip_scan_indexes */} /* skip_scan_range */,"analyzing_range_alternatives": { //分析范圍選項"range_scan_alternatives": [{"index": "PRIMARY","ranges": ["id < 10"] /* ranges */,"index_dives_for_eq_ranges": true,"rowid_ordered": true,"using_mrr": false,"index_only": false,"rows": 9,"cost": 1.91986,"chosen": true}] /* range_scan_alternatives */,"analyzing_roworder_intersect": {"usable": false,"cause": "too_few_roworder_scans"} /* analyzing_roworder_intersect */} /* analyzing_range_alternatives */,"chosen_range_access_summary": { //選擇范圍訪問摘要"range_access_plan": {"type": "range_scan","index": "PRIMARY","rows": 9,"ranges": ["id < 10"] /* ranges */} /* range_access_plan */,"rows_for_plan": 9,"cost_for_plan": 1.91986,"chosen": true} /* chosen_range_access_summary */} /* range_analysis */}] /* rows_estimation */},{"considered_execution_plans": [ //考慮執(zhí)行計劃{"plan_prefix": [] /* plan_prefix */,"table": "`student`","best_access_path": { //最佳訪問路徑"considered_access_paths": [{"rows_to_scan": 9,"access_type": "range","range_details": {"used_index": "PRIMARY"} /* range_details */,"resulting_rows": 9,"cost": 2.81986,"chosen": true}] /* considered_access_paths */} /* best_access_path */,"condition_filtering_pct": 100, //行過濾百分比"rows_for_plan": 9,"cost_for_plan": 2.81986,"chosen": true}] /* considered_execution_plans */},{"attaching_conditions_to_tables": { //將條件附加到表上"original_condition": "(`student`.`id` < 10)","attached_conditions_computation": [] /* attached_conditions_computation */,"attached_conditions_summary": [ //附加條件概要{"table": "`student`","attached": "(`student`.`id` < 10)"}] /* attached_conditions_summary */} /* attaching_conditions_to_tables */},{"finalizing_table_conditions": [{"table": "`student`","original_table_condition": "(`student`.`id` < 10)","final_table_condition ": "(`student`.`id` < 10)"}] /* finalizing_table_conditions */},{"refine_plan": [ //精簡計劃{"table": "`student`"}] /* refine_plan */}] /* steps */} /* join_optimization */
},{"join_execution": { //執(zhí)行"select#": 1,"steps": [] /* steps */} /* join_execution */}] /* steps */
}
//第3部分：跟蹤信息過長時，被截斷的跟蹤信息的字節(jié)數(shù)。
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 //丟失的超出最大容量的字節(jié)
//第4部分：執(zhí)行跟蹤語句的用戶是否有查看對象的權限。當不具有權限時，該列信息為1且TRACE字段為空，一般在
調用帶有SQL SECURITY DEFINER的視圖或者是存儲過程的情況下，會出現(xiàn)此問題。
INSUFFICIENT_PRIVILEGES: 0 //缺失權限
1 row in set (0.00 sec)

9. MySQL監(jiān)控分析視圖-sys schema

9.1 Sys schema視圖摘要

1. 主機相關：以host_summary開頭，主要匯總了IO延遲的信息。
2. Innodb相關：以innodb開頭，匯總了innodb buffer信息和事務等待innodb鎖的信息。
3. I/o相關：以io開頭，匯總了等待I/O、I/O使用量情況。
4. 內存使用情況：以memory開頭，從主機、線程、事件等角度展示內存的使用情況
5. 連接與會話信息：processlist和session相關視圖，總結了會話相關信息。
6. 表相關：以schema_table開頭的視圖，展示了表的統(tǒng)計信息。
7. 索引信息：統(tǒng)計了索引的使用情況，包含冗余索引和未使用的索引情況。
8. 語句相關：以statement開頭，包含執(zhí)行全表掃描、使用臨時表、排序等的語句信息。
9. 用戶相關：以user開頭的視圖，統(tǒng)計了用戶使用的文件I/O、執(zhí)行語句統(tǒng)計信息。
10. 等待事件相關信息：以wait開頭，展示等待事件的延遲情況。

9.2 Sys schema視圖使用場景

索引情況

#1. 查詢冗余索引
select * from sys.schema_redundant_indexes;
#2. 查詢未使用過的索引
select * from sys.schema_unused_indexes;
#3. 查詢索引的使用情況
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname';

表相關

# 1. 查詢表的訪問量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 2. 查詢占用bufferpool較多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 查看表的全表掃描情況
select * from sys.statements_with_full_table_scans where db='dbname';

語句相關

#1. 監(jiān)控SQL執(zhí)行的頻率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;
#2. 監(jiān)控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;
#3. 監(jiān)控使用了臨時表或者磁盤臨時表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;

IO相關

#1. 查看消耗磁盤IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb 相關

#1. 行鎖阻塞情況
select * from sys.innodb_lock_waits;

10. 小結

查詢是數(shù)據(jù)庫中最頻繁的操作，提高查詢速度可以有效地提高MySQL數(shù)據(jù)庫的性能。通過對查詢語句的分析可以了解查詢語句的執(zhí)行情況，找出查詢語句執(zhí)行的瓶頸，從而優(yōu)化查詢語句。