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

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

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

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

整個(gè)流程劃分成了 觀察（Show status） 和 行動(dòng)（Action） 兩個(gè)部分。字母 S 的部分代表觀察（會(huì)使 用相應(yīng)的分析工具），字母 A 代表的部分是行動(dòng)（對(duì)應(yīng)分析可以采取的行動(dòng)）。

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

詳細(xì)解釋一下這張圖：

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

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

SHOW STATUS語(yǔ)句語(yǔ)法如下：

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

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

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

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

SHOW STATUS LIKE 'Connections';

若查詢(xún)服務(wù)器工作時(shí)間，則可以執(zhí)行如下語(yǔ)句:

SHOW STATUS LIKE 'Uptime';

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

SHOW STATUS LIKE 'Slow_queries';

慢查詢(xún)次數(shù)參數(shù)可以結(jié)合慢查詢(xún)?nèi)罩菊页雎樵?xún)語(yǔ)句，然后針對(duì)慢查詢(xún)語(yǔ)句進(jìn)行表結(jié)構(gòu)優(yōu)化或者查詢(xún)語(yǔ)句優(yōu)化。

再比如，如下的指令可以查看相關(guān)的指令情況：

SHOW STATUS LIKE 'Innodb_rows_%';

3. 統(tǒng)計(jì)SQL的查詢(xún)成本: last_query_cost

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

如果我們想要查看某條SQL語(yǔ)句的查詢(xún)成本，可以在執(zhí)行完這條SQL語(yǔ)句之后，通過(guò)查看當(dāng)前會(huì)話(huà)中的last_query_cost變量值來(lái)得到當(dāng)前查詢(xún)的成本。它通常也是我們評(píng)價(jià)一個(gè)查詢(xún)的執(zhí)行效率的一個(gè)常用指標(biāo)。這個(gè)查詢(xún)成本對(duì)應(yīng)的是SQL 語(yǔ)句所需要讀取的讀頁(yè)的數(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;

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

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

運(yùn)行結(jié)果（1 條記錄，運(yùn)行時(shí)間為 0.042s ）

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

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

如果我們想要查詢(xún) id 在 900001 到 9000100 之間的學(xué)生記錄呢？

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

運(yùn)行結(jié)果（100 條記錄，運(yùn)行時(shí)間為 0.046s ）：

然后再看下查詢(xún)優(yōu)化器的成本，這時(shí)我們大概需要進(jìn)行 20 個(gè)頁(yè)的查詢(xún)。

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

你能看到頁(yè)的數(shù)量是剛才的 20 倍，但是查詢(xún)的效率并沒(méi)有明顯的變化，實(shí)際上這兩個(gè) SQL 查詢(xún)的時(shí)間 基本上一樣，就是因?yàn)椴捎昧隧樞蜃x取的方式將頁(yè)面一次性加載到緩沖池中，然后再進(jìn)行查找。雖然 頁(yè) 數(shù)量（last_query_cost）增加了不少 ，但是通過(guò)緩沖池的機(jī)制，并 沒(méi)有增加多少查詢(xún)時(shí)間 。

使用場(chǎng)景：它對(duì)于比較開(kāi)銷(xiāo)是非常有用的，特別是我們有好幾種查詢(xún)方式可選的時(shí)候。

SQL查詢(xún)時(shí)一個(gè)動(dòng)態(tài)的過(guò)程，從頁(yè)加載的角度來(lái)看，我們可以得到以下兩點(diǎn)結(jié)論：

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

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

4. 定位執(zhí)行慢的 SQL：慢查詢(xún)?nèi)罩?/h2>

4.1 開(kāi)啟慢查詢(xún)?nèi)罩緟?shù)

1. 開(kāi)啟 slow_query_log

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

mysql > show variables like '%slow_query_log';

我們可以看到 slow_query_log=OFF，我們可以把慢查詢(xún)?nèi)罩敬蜷_(kāi)，注意設(shè)置變量值的時(shí)候需要使用 global，否則會(huì)報(bào)錯(cuò)：

mysql > set global slow_query_log='ON';

然后我們?cè)賮?lái)查看下慢查詢(xún)?nèi)罩臼欠耖_(kāi)啟，以及慢查詢(xún)?nèi)罩疚募奈恢?#xff1a;

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

2. 修改 long_query_time 閾值

接下來(lái)我們來(lái)看下慢查詢(xún)的時(shí)間閾值設(shè)置，使用如下命令：

mysql > show variables like '%long_query_time%';

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

#測(cè)試發(fā)現(xiàn)：設(shè)置global的方式對(duì)當(dāng)前session的long_query_time失效。對(duì)新連接的客戶(hù)端有效。所以可以一并
執(zhí)行下述語(yǔ)句
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%';

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

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

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

[mysqld]
slow_query_log=ON  # 開(kāi)啟慢查詢(xún)?nèi)罩鹃_(kāi)關(guān)
slow_query_log_file=/var/lib/mysql/atguigu-low.log  # 慢查詢(xún)?nèi)罩镜哪夸浐臀募畔?/span>
long_query_time=3  # 設(shè)置慢查詢(xún)的閾值為3秒，超出此設(shè)定值的SQL即被記錄到慢查詢(xún)?nèi)罩?/span>
log_output=FILE

如果不指定存儲(chǔ)路徑，慢查詢(xún)?nèi)罩灸J(rèn)存儲(chǔ)到MySQL數(shù)據(jù)庫(kù)的數(shù)據(jù)文件夾下。如果不指定文件名，默認(rèn)文件名為hostname_slow.log。

4.2 查看慢查詢(xún)數(shù)目

查詢(xún)當(dāng)前系統(tǒng)中有多少條慢查詢(xún)記錄

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è)置參數(shù) log_bin_trust_function_creators

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

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

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

set global log_bin_trust_function_creators=1; # 不加global只是當(dāng)前窗口有效。

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

隨機(jī)產(chǎn)生字符串：（同上一章）

DELIMITER //
CREATE FUNCTION rand_string(n INT)RETURNS VARCHAR(255) #該函數(shù)會(huì)返回一個(gè)字符串
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 ;# 測(cè)試
SELECT rand_string(10);

產(chǎn)生隨機(jī)數(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 ;#測(cè)試：
SELECT rand_num(10,100);

步驟4：創(chuàng)建存儲(chǔ)過(guò)程

DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;SET autocommit = 0; #設(shè)置手動(dòng)提交事務(wù)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; #提交事務(wù)
END //
DELIMITER ;

步驟5：調(diào)用存儲(chǔ)過(guò)程

#調(diào)用剛剛寫(xiě)好的函數(shù), 4000000條記錄,從100001號(hào)開(kāi)始CALL insert_stu1(100001,4000000);

4.4 測(cè)試及分析

1. 測(cè)試

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)

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

2. 分析

show status like 'slow_queries';

4.5 慢查詢(xún)?nèi)罩痉治龉ぞ?#xff1a;mysqldumpslow

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

查看mysqldumpslow的幫助信息

mysqldumpslow --help

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

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

舉例：我們想要按照查詢(xún)時(shí)間排序，查看前五條 SQL 語(yǔ)句，這樣寫(xiě)即可：

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

4.6 關(guān)閉慢查詢(xún)?nèi)罩?/h3>

MySQL服務(wù)器停止慢查詢(xún)?nèi)罩竟δ苡袃煞N方法：

方式1：永久性方式

[mysqld]
slow_query_log=OFF

或者，把slow_query_log一項(xiàng)注釋掉 或 刪除

[mysqld]
#slow_query_log =OFF

重啟MySQL服務(wù)，執(zhí)行如下語(yǔ)句查詢(xún)慢日志功能。

SHOW VARIABLES LIKE '%slow%'; #查詢(xún)慢查詢(xún)?nèi)罩舅谀夸?SHOW VARIABLES LIKE '%long_query_time%'; #查詢(xún)超時(shí)時(shí)長(zhǎng)

方式2：臨時(shí)性方式

使用SET語(yǔ)句來(lái)設(shè)置。

（1）停止MySQL慢查詢(xún)?nèi)罩竟δ?#xff0c;具體SQL語(yǔ)句如下。

SET GLOBAL slow_query_log=off;

（2）重啟MySQL服務(wù)，使用SHOW語(yǔ)句查詢(xún)慢查詢(xún)?nèi)罩竟δ苄畔?#xff0c;具體SQL語(yǔ)句如下。

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

4.7 刪除慢查詢(xún)?nèi)罩?/h3>

使用SHOW語(yǔ)句顯示慢查詢(xún)?nèi)罩拘畔?#xff0c;具體SQL語(yǔ)句如下。

SHOW VARIABLES LIKE `slow_query_log%`;

從執(zhí)行結(jié)果可以看出，慢查詢(xún)?nèi)罩镜哪夸浤J(rèn)為MySQL的數(shù)據(jù)目錄，在該目錄下 手動(dòng)刪除慢查詢(xún)?nèi)罩疚募?/code> 即可。

使用命令 mysqladmin flush-logs 來(lái)重新生成查詢(xún)?nèi)罩疚募?#xff0c;具體命令如下，執(zhí)行完畢會(huì)在數(shù)據(jù)目錄下重新生成慢查詢(xún)?nèi)罩疚募?/p>

mysqladmin -uroot -p flush-logs slow

提示

慢查詢(xún)?nèi)罩径际鞘褂胢ysqladmin flush-logs命令來(lái)刪除重建的。使用時(shí)一定要注意，一旦執(zhí)行了這個(gè)命令，慢查詢(xún)?nèi)罩径贾淮嬖谛碌娜罩疚募?#xff0c;如果需要舊的查詢(xún)?nèi)罩?#xff0c;就必須事先備份。

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

show profile 在《邏輯架構(gòu)》章節(jié)中講過(guò)，這里作為復(fù)習(xí)。

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

我們可以在會(huì)話(huà)級(jí)別開(kāi)啟這個(gè)功能。

mysql > show variables like 'profiling';

通過(guò)設(shè)置 profiling=‘ON’ 來(lái)開(kāi)啟 show profile:

mysql > set profiling = 'ON';

然后執(zhí)行相關(guān)的查詢(xún)語(yǔ)句。接著看下當(dāng)前會(huì)話(huà)都有哪些 profiles，使用下面這條命令：

mysql > show profiles;

你能看到當(dāng)前會(huì)話(huà)一共有 2 個(gè)查詢(xún)。如果我們想要查看最近一次查詢(xún)的開(kāi)銷(xiāo)，可以使用：

mysql > show profile;

mysql> show profile cpu,block io for query 2

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

① ALL：顯示所有的開(kāi)銷(xiāo)信息。

② BLOCK IO：顯示塊IO開(kāi)銷(xiāo)。

③ CONTEXT SWITCHES：上下文切換開(kāi)銷(xiāo)。

④ CPU：顯示CPU開(kāi)銷(xiāo)信息。

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

⑥ MEMORY：顯示內(nèi)存開(kāi)銷(xiāo)信 息。

⑦ PAGE FAULTS：顯示頁(yè)面錯(cuò)誤開(kāi)銷(xiāo)信息。

⑧ SOURCE：顯示和Source_function，Source_file， Source_line相關(guān)的開(kāi)銷(xiāo)信息。

⑨ SWAPS：顯示交換次數(shù)開(kāi)銷(xiāo)信息。

日常開(kāi)發(fā)需注意的結(jié)論：

① converting HEAP to MyISAM: 查詢(xún)結(jié)果太大，內(nèi)存不夠，數(shù)據(jù)往磁盤(pán)上搬了。

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

③ Copying to tmp table on disk：把內(nèi)存中臨時(shí)表復(fù)制到磁盤(pán)上，警惕！

④ locked。

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

注意：

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

6. 分析查詢(xún)語(yǔ)句：EXPLAIN

6.1 概述

1. 能做什么？

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

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版本后，默認(rèn)explain直接顯示partitions和 filtered中的信息。

6.2 基本語(yǔ)法

EXPLAIN 或 DESCRIBE語(yǔ)句的語(yǔ)法形式如下：

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

如果我們想看看某個(gè)查詢(xún)的執(zhí)行計(jì)劃的話(huà)，可以在具體的查詢(xún)語(yǔ)句前邊加一個(gè) EXPLAIN ，就像這樣：

mysql> EXPLAIN SELECT 1;

EXPLAIN 語(yǔ)句輸出的各個(gè)列的作用如下：

在這里把它們都列出來(lái)知識(shí)為了描述一個(gè)輪廓，讓大家有一個(gè)大致的印象。

6.3 數(shù)據(jù)準(zhǔn)備

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è)置參數(shù) log_bin_trust_function_creators

創(chuàng)建函數(shù)，假如報(bào)錯(cuò)，需開(kāi)啟如下命令：允許創(chuàng)建函數(shù)設(shè)置：

set global log_bin_trust_function_creators=1; # 不加global只是當(dāng)前窗口有效。

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

DELIMITER //
CREATE FUNCTION rand_string1(n INT)RETURNS VARCHAR(255) #該函數(shù)會(huì)返回一個(gè)字符串
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)建存儲(chǔ)過(guò)程

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

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ù)的存儲(chǔ)過(guò)程：

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. 調(diào)用存儲(chǔ)過(guò)程

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

CALL insert_s1(10001,10000);

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

CALL insert_s2(10001,10000);

6.4 EXPLAIN各列作用

為了讓大家有比較好的體驗(yàn)，我們調(diào)整了下 EXPLAIN 輸出列的順序。

1. table

不論我們的查詢(xún)語(yǔ)句有多復(fù)雜，里邊兒 包含了多少個(gè)表 ，到最后也是需要對(duì)每個(gè)表進(jìn)行 單表訪問(wèn) 的，所 以MySQL規(guī)定EXPLAIN語(yǔ)句輸出的每條記錄都對(duì)應(yīng)著某個(gè)單表的訪問(wèn)方法，該條記錄的table列代表著該 表的表名（有時(shí)不是真實(shí)的表名字，可能是簡(jiǎn)稱(chēng)）。

mysql > EXPLAIN SELECT * FROM s1;

這個(gè)查詢(xún)語(yǔ)句只涉及對(duì)s1表的單表查詢(xún)，所以 EXPLAIN 輸出中只有一條記錄，其中的table列的值為s1，表明這條記錄是用來(lái)說(shuō)明對(duì)s1表的單表訪問(wèn)方法的。

下邊我們看一個(gè)連接查詢(xún)的執(zhí)行計(jì)劃

mysql > EXPLAIN SELECT * FROM s1 INNER JOIN s2;

可以看出這個(gè)連接查詢(xún)的執(zhí)行計(jì)劃中有兩條記錄，這兩條記錄的table列分別是s1和s2，這兩條記錄用來(lái)分別說(shuō)明對(duì)s1表和s2表的訪問(wèn)方法是什么。

2. id

我們寫(xiě)的查詢(xún)語(yǔ)句一般都以 SELECT 關(guān)鍵字開(kāi)頭，比較簡(jiǎn)單的查詢(xún)語(yǔ)句里只有一個(gè) SELECT 關(guān)鍵字，比 如下邊這個(gè)查詢(xún)語(yǔ)句：

SELECT * FROM s1 WHERE key1 = 'a';

稍微復(fù)雜一點(diǎn)的連接查詢(xún)中也只有一個(gè) SELECT 關(guān)鍵字，比如：

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

但是下邊兩種情況下在一條查詢(xún)語(yǔ)句中會(huì)出現(xiàn)多個(gè)SELECT關(guān)鍵字：

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

對(duì)于連接查詢(xún)來(lái)說(shuō)，一個(gè)SELECT關(guān)鍵字后邊的FROM字句中可以跟隨多個(gè)表，所以在連接查詢(xún)的執(zhí)行計(jì)劃中，每個(gè)表都會(huì)對(duì)應(yīng)一條記錄，但是這些記錄的id值都是相同的，比如：

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;

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

對(duì)于包含子查詢(xún)的查詢(xún)語(yǔ)句來(lái)說(shuō)，就可能涉及多個(gè)SELECT關(guān)鍵字，所以在**包含子查詢(xún)的查詢(xún)語(yǔ)句的執(zhí)行計(jì)劃中，每個(gè)SELECT關(guān)鍵字都會(huì)對(duì)應(yīng)一個(gè)唯一的id值，比如這樣：

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

# 查詢(xún)優(yōu)化器可能對(duì)涉及子查詢(xún)的查詢(xún)語(yǔ)句進(jìn)行重寫(xiě)，轉(zhuǎn)變?yōu)槎啾聿樵?xún)的操作。  
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');

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

對(duì)于包含UNION子句的查詢(xún)語(yǔ)句來(lái)說(shuō)，每個(gè)SELECT關(guān)鍵字對(duì)應(yīng)一個(gè)id值也是沒(méi)錯(cuò)的，不過(guò)還是有點(diǎn)兒特別的東西，比方說(shuō)下邊的查詢(xún)：

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

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

小結(jié):

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

3. select_type

具體分析如下：

• SIMPLE

  查詢(xún)語(yǔ)句中不包含UNION或者子查詢(xún)的查詢(xún)都算作是SIMPLE類(lèi)型，比方說(shuō)下邊這個(gè)單表查詢(xún)select_type的值就是SIMPLE:

  mysql> EXPLAIN SELECT * FROM s1;
  

  

? 當(dāng)然，連接查詢(xún)也算是 SIMPLE 類(lèi)型，比如：

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;

• PRIMARY

  對(duì)于包含UNION、UNION ALL或者子查詢(xún)的大查詢(xún)來(lái)說(shuō)，它是由幾個(gè)小查詢(xún)組成的，其中最左邊的那個(gè)查詢(xún)的select_type的值就是PRIMARY,比方說(shuō)：

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

  

  從結(jié)果中可以看到，最左邊的小查詢(xún)SELECT * FROM s1對(duì)應(yīng)的是執(zhí)行計(jì)劃中的第一條記錄，它的select_type的值就是PRIMARY。

• UNION

  對(duì)于包含UNION或者UNION ALL的大查詢(xún)來(lái)說(shuō)，它是由幾個(gè)小查詢(xún)組成的，其中除了最左邊的那個(gè)小查詢(xún)意外，其余的小查詢(xún)的select_type值就是UNION，可以對(duì)比上一個(gè)例子的效果。

• UNION RESULT

  MySQL 選擇使用臨時(shí)表來(lái)完成UNION查詢(xún)的去重工作，針對(duì)該臨時(shí)表的查詢(xún)的select_type就是UNION RESULT, 例子上邊有。

• SUBQUERY

  如果包含子查詢(xún)的查詢(xún)語(yǔ)句不能夠轉(zhuǎn)為對(duì)應(yīng)的semi-join的形式，并且該子查詢(xún)是不相關(guān)子查詢(xún)，并且查詢(xún)優(yōu)化器決定采用將該子查詢(xún)物化的方案來(lái)執(zhí)行該子查詢(xún)時(shí)，該子查詢(xún)的第一個(gè)SELECT關(guān)鍵字代表的那個(gè)查詢(xún)的select_type就是SUBQUERY，比如下邊這個(gè)查詢(xún)：

  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í)行計(jì)劃中可以看出，id為2的記錄就代表子查詢(xún)的執(zhí)行方式，它的select_type是DERIVED, 說(shuō)明該子查詢(xún)是以物化的方式執(zhí)行的。id為1的記錄代表外層查詢(xún)，大家注意看它的table列顯示的是derived2，表示該查詢(xún)時(shí)針對(duì)將派生表物化之后的表進(jìn)行查詢(xún)的。

• MATERIALIZED

  當(dāng)查詢(xún)優(yōu)化器在執(zhí)行包含子查詢(xún)的語(yǔ)句時(shí)，選擇將子查詢(xún)物化之后的外層查詢(xún)進(jìn)行連接查詢(xún)時(shí)，該子查詢(xún)對(duì)應(yīng)的select_type屬性就是DERIVED，比如下邊這個(gè)查詢(xún)：

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

  

• UNCACHEABLE SUBQUERY

  不常用

• UNCACHEABLE UNION

  不常用

4. partitions (可略)

• 代表分區(qū)表中的命中情況，非分區(qū)表，該項(xiàng)為NULL。一般情況下我們的額查詢(xún)語(yǔ)句的執(zhí)行計(jì)劃的partitions列的值為NULL。
• https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html
• 如果想詳細(xì)了解，可以如下方式測(cè)試。創(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;

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

5. type ☆

執(zhí)行計(jì)劃的一條記錄就代表著MySQL對(duì)某個(gè)表的 執(zhí)行查詢(xún)時(shí)的訪問(wèn)方法 , 又稱(chēng)“訪問(wèn)類(lèi)型”，其中的 type 列就表明了這個(gè)訪問(wèn)方法是啥，是較為重要的一個(gè)指標(biāo)。比如，看到type列的值是ref，表明MySQL即將使用ref訪問(wèn)方法來(lái)執(zhí)行對(duì)s1表的查詢(xún)。

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

我們?cè)敿?xì)解釋一下：

• system

  當(dāng)表中只有一條記錄并且該表使用的存儲(chǔ)引擎的統(tǒng)計(jì)數(shù)據(jù)是精確的，比如MyISAM、Memory，那么對(duì)該表的訪問(wèn)方法就是system。比方說(shuō)我們新建一個(gè)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)
  

  然后我們看一下查詢(xún)這個(gè)表的執(zhí)行計(jì)劃：

  mysql> EXPLAIN SELECT * FROM t;
  

  

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

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

• const

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

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

  

• eq_ref

  在連接查詢(xún)時(shí)，如果被驅(qū)動(dòng)表是通過(guò)主鍵或者唯一二級(jí)索引列等值匹配的方式進(jìn)行訪問(wèn)的（如果該主鍵或者唯一二級(jí)索引是聯(lián)合索引的話(huà)，所有的索引列都必須進(jìn)行等值比較）。則對(duì)該被驅(qū)動(dòng)表的訪問(wèn)方法就是eq_ref，比方說(shuō)：

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

  

  從執(zhí)行計(jì)劃的結(jié)果中可以看出，MySQL打算將s2作為驅(qū)動(dòng)表，s1作為被驅(qū)動(dòng)表，重點(diǎn)關(guān)注s1的訪問(wèn) 方法是 eq_ref ，表明在訪問(wèn)s1表的時(shí)候可以 通過(guò)主鍵的等值匹配 來(lái)進(jìn)行訪問(wèn)。

• ref

  當(dāng)通過(guò)普通的二級(jí)索引列與常量進(jìn)行等值匹配時(shí)來(lái)查詢(xún)某個(gè)表，那么對(duì)該表的訪問(wèn)方法就可能是ref，比方說(shuō)下邊這個(gè)查詢(xún)：

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

  

• fulltext

  全文索引

• ref_or_null

  當(dāng)對(duì)普通二級(jí)索引進(jìn)行等值匹配查詢(xún)，該索引列的值也可以是NULL值時(shí)，那么對(duì)該表的訪問(wèn)方法就可能是ref_or_null，比如說(shuō)：

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

  

• index_merge

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

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

  

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

• unique_subquery

  類(lèi)似于兩表連接中被驅(qū)動(dòng)表的eq_ref訪問(wèn)方法，unique_subquery是針對(duì)在一些包含IN子查詢(xún)的查詢(xún)語(yǔ)句中，如果查詢(xún)優(yōu)化器決定將IN子查詢(xún)轉(zhuǎn)換為EXISTS子查詢(xún)，而且子查詢(xún)可以使用到主鍵進(jìn)行等值匹配的話(huà)，那么該子查詢(xún)執(zhí)行計(jì)劃的type列的值就是unique_subquery，比如下邊的這個(gè)查詢(xún)語(yǔ)句：

  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 類(lèi)似，只不過(guò)訪問(wèn)子查詢(xún)中的表時(shí)使用的是普通的索引，比如這樣：

  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

  當(dāng)我們可以使用索引覆蓋，但需要掃描全部的索引記錄時(shí)，該表的訪問(wèn)方法就是index，比如這樣：

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

  

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

  再一次強(qiáng)調(diào)，對(duì)于使用InnoDB存儲(chǔ)引擎的表來(lái)說(shuō)，二級(jí)索引的記錄只包含索引列和主鍵列的值，而聚簇索引中包含用戶(hù)定義的全部列以及一些隱藏列，所以?huà)呙瓒?jí)索引的代價(jià)比直接全表掃描，也就是掃描聚簇索引的代價(jià)更低一些。

• ALL

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

  mysql> EXPLAIN SELECT * FROM s1;
  

  

小結(jié):

結(jié)果值從最好到最壞依次是：

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

其中比較重要的幾個(gè)提取出來(lái)（見(jiàn)上圖中的粗體）。SQL 性能優(yōu)化的目標(biāo)：至少要達(dá)到 range 級(jí)別，要求是 ref 級(jí)別，最好是 consts級(jí)別。（阿里巴巴 開(kāi)發(fā)手冊(cè)要求）

6. possible_keys和key

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

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

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

7. key_len ☆

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

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

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

int 占用 4 個(gè)字節(jié)

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

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

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

key1 VARCHAR(100) 一個(gè)字符占3個(gè)字節(jié)，100*3，是否為NULL占用一個(gè)字節(jié)，varchar的長(zhǎng)度信息占兩個(gè)字節(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

練習(xí)：

key_len的長(zhǎng)度計(jì)算公式：

varchar(10)變長(zhǎng)字段且允許NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(變長(zhǎng)字段)varchar(10)變長(zhǎng)字段且不允許NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(變長(zhǎng)字段)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í)行查詢(xún)時(shí)，與key1列作等值匹配的對(duì)象是一個(gè)常數(shù)，當(dāng)然有時(shí)候更復(fù)雜一點(diǎn):

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 ☆

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

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

10. filtered

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

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

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

對(duì)于單表查詢(xún)來(lái)說(shuō)，這個(gè)filtered的值沒(méi)有什么意義，我們更關(guān)注在連接查詢(xún)中驅(qū)動(dòng)表對(duì)應(yīng)的執(zhí)行計(jì)劃記錄的filtered值，它決定了被驅(qū)動(dòng)表要執(zhí)行的次數(shù) (即: rows * filtered)

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

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

11. Extra ☆

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

• No tables used

  當(dāng)查詢(xún)語(yǔ)句沒(méi)有FROM子句時(shí)將會(huì)提示該額外信息，比如：

  mysql> EXPLAIN SELECT 1;
  

  

• Impossible WHERE

  當(dāng)查詢(xún)語(yǔ)句的WHERE子句永遠(yuǎn)為FALSE時(shí)將會(huì)提示該額外信息

  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

  當(dāng)查詢(xún)列表處有MIN或者MAX聚合函數(shù)，但是并沒(méi)有符合WHERE子句中的搜索條件的記錄時(shí)。

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

  

• Using index

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

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

  

• Using index condition

  有些搜索條件中雖然出現(xiàn)了索引列，但卻不能使用到索引，比如下邊這個(gè)查詢(xú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)

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

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

  

• Not exists

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

  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í)行計(jì)劃的Extra列出現(xiàn)了Using intersect(...)提示，說(shuō)明準(zhǔn)備使用Intersect索引合并的方式執(zhí)行查詢(xún)，括號(hào)中的...表示需要進(jìn)行索引合并的索引名稱(chēng)；

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

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

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

  

• Zero limit

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

  mysql> EXPLAIN SELECT * FROM s1 LIMIT 0;
  

  

• Using filesort

  有一些情況下對(duì)結(jié)果集中的記錄進(jìn)行排序是可以使用到索引的。

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

  

  

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

  

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

• 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í)行計(jì)劃中出現(xiàn)Using temporary并不是一個(gè)好的征兆，因?yàn)榻⑴c維護(hù)臨時(shí)表要付出很大的成本的，所以我們最好能使用索引來(lái)替代掉使用臨時(shí)表，比方說(shuō)下邊這個(gè)包含GROUP BY子句的查詢(xún)就不需要使用臨時(shí)表：

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

  

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

• 其他

  其它特殊情況這里省略。

12. 小結(jié)

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

7. EXPLAIN的進(jìn)一步使用

7.1 EXPLAIN四種輸出格式

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

1. 傳統(tǒng)格式

傳統(tǒng)格式簡(jiǎn)單明了，輸出是一個(gè)表格形式，概要說(shuō)明查詢(xún)計(jì)劃。

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語(yǔ)句輸出中缺少了一個(gè)衡量執(zhí)行好壞的重要屬性 —— 成本。而JSON格式是四種格式里面輸出信息最詳盡的格式，里面包含了執(zhí)行的成本信息。

• JSON格式：在EXPLAIN單詞和真正的查詢(xún)語(yǔ)句中間加上 FORMAT=JSON 。

EXPLAIN FORMAT=JSON SELECT ....

• EXPLAIN的Column與JSON的對(duì)應(yīng)關(guān)系：(來(lái)源于MySQL 5.7文檔)

  

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

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

我們使用 # 后邊跟隨注釋的形式為大家解釋了 EXPLAIN FORMAT=JSON 語(yǔ)句的輸出內(nèi)容，但是大家可能 有疑問(wèn) “cost_info” 里邊的成本看著怪怪的，它們是怎么計(jì)算出來(lái)的？先看 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 成本
  • 檢測(cè) rows × (1 - filter) 條記錄的 CPU 成本

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

• eval_cost 是這樣計(jì)算的：

  檢測(cè) rows × filter 條記錄的成本。

• prefix_cost 就是單獨(dú)查詢(xún) s1 表的成本，也就是：

  read_cost + eval_cost

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

對(duì)于 s2 表的 “cost_info” 部分是這樣的：

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

由于 s2 表是被驅(qū)動(dòng)表，所以可能被讀取多次，這里的read_cost 和 eval_cost 是訪問(wèn)多次 s2 表后累加起來(lái)的值，大家主要關(guān)注里邊兒的 prefix_cost 的值代表的是整個(gè)連接查詢(xún)預(yù)計(jì)的成本，也就是單次查詢(xún) s1 表和多次查詢(xún) s2 表后的成本的和，也就是：

968.80 + 193.76 + 2034.60 = 3197.16

3. TREE格式

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

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. 可視化輸出

可視化輸出，可以通過(guò)MySQL Workbench可視化查看MySQL的執(zhí)行計(jì)劃。通過(guò)點(diǎn)擊Workbench的放大鏡圖標(biāo)，即可生成可視化的查詢(xún)計(jì)劃。

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

7.2 SHOW WARNINGS的使用

在我們使用EXPLAIN語(yǔ)句查看了某個(gè)查詢(xún)的執(zhí)行計(jì)劃后，緊接著還可以使用SHOW WARNINGS語(yǔ)句查看與這個(gè)查詢(xún)的執(zhí)行計(jì)劃有關(guān)的一些擴(kuò)展信息，比如這樣：

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展示出來(lái)的信息有三個(gè)字段，分別是Level、Code、Message。我們最常見(jiàn)的就是Code為1003的信息，當(dāng)Code值為1003時(shí)，Message字段展示的信息類(lèi)似于查詢(xún)優(yōu)化器將我們的查詢(xún)語(yǔ)句重寫(xiě)后的語(yǔ)句。比如我們上邊的查詢(xún)本來(lái)是一個(gè)左(外)連接查詢(xún)，但是有一個(gè)s2.common_field IS NOT NULL的條件，這就會(huì)導(dǎo)致查詢(xún)優(yōu)化器把左(外)連接查詢(xún)優(yōu)化為內(nèi)連接查詢(xún)，從SHOW WARNINGS的Message字段也可以看出來(lái)，原本的LEFE JOIN已經(jīng)變成了JOIN。

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

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

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

開(kāi)啟后，可分析如下語(yǔ)句：

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

測(cè)試：執(zhí)行如下SQL語(yǔ)句

select * from student where id < 10;

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

select * from information_schema.optimizer_trace\G

*************************** 1. row ***************************
//第1部分：查詢(xún)語(yǔ)句
QUERY: select * from student where id < 10
//第2部分：QUERY字段對(duì)應(yīng)語(yǔ)句的跟蹤信息
TRACE: {
"steps": [
{"join_preparation": { //預(yù)備工作"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": { //進(jìn)行優(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": [ //表的依賴(lài)關(guān)系{"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": [ //設(shè)置范圍條件] /* 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": { //分析范圍選項(xiàng)"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": { //選擇范圍訪問(wèn)摘要"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í)行計(jì)劃{"plan_prefix": [] /* plan_prefix */,"table": "`student`","best_access_path": { //最佳訪問(wèn)路徑"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, //行過(guò)濾百分比"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": [ //精簡(jiǎn)計(jì)劃{"table": "`student`"}] /* refine_plan */}] /* steps */} /* join_optimization */
},{"join_execution": { //執(zhí)行"select#": 1,"steps": [] /* steps */} /* join_execution */}] /* steps */
}
//第3部分：跟蹤信息過(guò)長(zhǎng)時(shí)，被截?cái)嗟母櫺畔⒌淖止?jié)數(shù)。
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 //丟失的超出最大容量的字節(jié)
//第4部分：執(zhí)行跟蹤語(yǔ)句的用戶(hù)是否有查看對(duì)象的權(quán)限。當(dāng)不具有權(quán)限時(shí)，該列信息為1且TRACE字段為空，一般在
調(diào)用帶有SQL SECURITY DEFINER的視圖或者是存儲(chǔ)過(guò)程的情況下，會(huì)出現(xiàn)此問(wèn)題。
INSUFFICIENT_PRIVILEGES: 0 //缺失權(quán)限
1 row in set (0.00 sec)

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

9.1 Sys schema視圖摘要

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

9.2 Sys schema視圖使用場(chǎng)景

索引情況

#1. 查詢(xún)?nèi)哂嗨饕?/span>
select * from sys.schema_redundant_indexes;
#2. 查詢(xún)未使用過(guò)的索引
select * from sys.schema_unused_indexes;
#3. 查詢(xún)索引的使用情況
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname';

表相關(guān)

# 1. 查詢(xún)表的訪問(wèn)量
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. 查詢(xún)占用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';

語(yǔ)句相關(guān)

#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)控使用了臨時(shí)表或者磁盤(pán)臨時(shí)表的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相關(guān)

#1. 查看消耗磁盤(pán)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 相關(guān)

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

10. 小結(jié)

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