深入理解 MySQL：快速學(xué)會(huì)分析SQL執(zhí)行效率
作者 |馬聽(tīng)老師
來(lái)源 |慕課專欄《一線數(shù)據(jù)庫(kù)工程師帶你深入理解 MySQL》
本文首先會(huì)一起討論一下 SQL 優(yōu)化，然后分享如何定位慢查詢和如何分析 SQl 執(zhí)行效率，并用一些簡(jiǎn)單的例子讓大家學(xué)會(huì)這些分析技巧。
在工作中可能會(huì)遇到某個(gè)新功能在測(cè)試時(shí)需要很久才返回結(jié)果，這時(shí)就應(yīng)該分析是不是慢查詢導(dǎo)致的。如果確實(shí)有慢查詢，又應(yīng)該怎么去分析 SQL 執(zhí)行效率呢？這一篇文章我們就來(lái)學(xué)習(xí)怎么找到慢查詢和怎么分析 SQL 執(zhí)行效率。
1 定位慢 SQL
當(dāng)我們實(shí)際工作中，碰到某個(gè)功能或者某個(gè)接口需要很久才能返回結(jié)果，我們就應(yīng)該去確定是不是慢查詢導(dǎo)致的。定位慢 SQL 有如下兩種解決方案：
查看慢查詢?nèi)罩敬_定已經(jīng)執(zhí)行完的慢查詢
show processlist 查看正在執(zhí)行的慢查詢
我們一起來(lái)了解下這兩種方法的使用場(chǎng)景和使用技巧吧！
1.1 通過(guò)慢查詢?nèi)罩?/span>
如果需要定位到慢查詢，一般的方法是通過(guò)慢查詢?nèi)罩緛?lái)查詢的，MySQL 的慢查詢?nèi)罩居脕?lái)記錄在 MySQL 中響應(yīng)時(shí)間超過(guò)參數(shù) long_query_time（單位秒，默認(rèn)值 10）設(shè)置的值并且掃描記錄數(shù)不小于 min_examined_row_limit（默認(rèn)值0）的語(yǔ)句，能夠幫我們找到執(zhí)行完的慢查詢，方便我們對(duì)這些 SQL 進(jìn)行優(yōu)化。
知識(shí)擴(kuò)展：
默認(rèn)情況下，慢查詢?nèi)罩局胁粫?huì)記錄管理語(yǔ)句，可通過(guò)設(shè)置 log_slow_admin_statements = on 讓管理語(yǔ)句中的慢查詢也會(huì)記錄到慢查詢?nèi)罩局小?/span>
默認(rèn)情況下，也不會(huì)記錄查詢時(shí)間不超過(guò) long_query_time 但是不使用索引的語(yǔ)句，可通過(guò)配置log_queries_not_using_indexes = on 讓不使用索引的 SQL 都被記錄到慢查詢?nèi)罩局校词共樵儠r(shí)間沒(méi)超過(guò) long_query_time 配置的值）。
如果需要使用慢查詢?nèi)罩?，一般分為四步?/span>開(kāi)啟慢查詢?nèi)罩?、設(shè)置慢查詢閥值、確定慢查詢?nèi)罩韭窂?、確定慢查詢?nèi)罩镜奈募?/span>下面我們來(lái)學(xué)習(xí)下:
首先開(kāi)啟慢查詢?nèi)罩?，由參?shù) slow_query_log 決定是否開(kāi)啟，在 MySQL 命令行下輸入下面的命令：
mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)
默認(rèn)環(huán)境下，慢查詢?nèi)罩臼顷P(guān)閉的。
設(shè)置慢查詢時(shí)間閥值
mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)
知識(shí)擴(kuò)展：
MySQL 中 long_query_time 的值如何確定呢？線上業(yè)務(wù)一般建議把 long_query_time 設(shè)置為 1 秒，如果某個(gè)業(yè)務(wù)的 MySQL 要求比較高的 QPS，可設(shè)置慢查詢?yōu)?0.1 秒。發(fā)現(xiàn)慢查詢及時(shí)優(yōu)化或者提醒開(kāi)發(fā)改寫。
一般測(cè)試環(huán)境建議 long_query_time 設(shè)置的閥值比生產(chǎn)環(huán)境的小，比如生產(chǎn)環(huán)境是 1 秒，則測(cè)試環(huán)境建議配置成 0.5 秒。便于在測(cè)試環(huán)境及時(shí)發(fā)現(xiàn)一些效率低的 SQL。
甚至某些重要業(yè)務(wù)測(cè)試環(huán)境 long_query_time 可以設(shè)置為 0，以便記錄所有語(yǔ)句。并留意慢查詢?nèi)罩镜妮敵觯暇€前的功能測(cè)試完成后，分析慢查詢?nèi)罩久款愓Z(yǔ)句的輸出，重點(diǎn)關(guān)注 Rows_examined（語(yǔ)句執(zhí)行期間從存儲(chǔ)引擎讀取的行數(shù)），提前優(yōu)化。
確定慢查詢?nèi)罩韭窂?/span>
慢查詢?nèi)罩镜穆窂侥J(rèn)是 MySQL 的數(shù)據(jù)目錄
mysql> show global variables like "datadir";
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| datadir | /data/mysql/data/3306/ |
+---------------+------------------------+
1 row in set (0.00 sec)
確定慢查詢?nèi)罩镜奈募?/span>
mysql> show global variables like "slow_query_log_file";
+---------------------+----------------+
| Variable_name | Value |
+---------------------+----------------+
| slow_query_log_file | mysql-slow.log |
+---------------------+----------------+
1 row in set (0.00 sec)
根據(jù)上面的查詢結(jié)果，可以直接查看 /data/mysql/data/3306/mysql-slow.log 文件獲取已經(jīng)執(zhí)行完的慢查詢
[root@mysqltest ~]# tail -n5 /data/mysql/data/3306/mysql-slow.log
Time: 2019-05-21T09:15:06.255554+08:00
User@Host: root[root] @ localhost [] Id: 8591152
Query_time: 10.000260 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1558401306;
select sleep(10);
這里對(duì)上方的執(zhí)行結(jié)果詳細(xì)描述一下：
tail -n5：只查看慢查詢文件的最后5行
Time：慢查詢發(fā)生的時(shí)間
User@Host：客戶端用戶和IP
Query_time：查詢時(shí)間
Lock_time：等待表鎖的時(shí)間
Rows_sent：語(yǔ)句返回的行數(shù)
Rows_examined：語(yǔ)句執(zhí)行期間從存儲(chǔ)引擎讀取的行數(shù)
上面這種方式是用系統(tǒng)自帶的慢查詢?nèi)罩静榭吹?，如果覺(jué)得系統(tǒng)自帶的慢查詢?nèi)罩静环奖悴榭?，小伙伴們可以使?pt-query-digest 或者 mysqldumpslow 等工具對(duì)慢查詢?nèi)罩具M(jìn)行分析，由于本節(jié)重點(diǎn)是找到慢查詢，這里就不一一示例了。
1.2 通過(guò) show processlist;
有時(shí)慢查詢正在執(zhí)行，已經(jīng)導(dǎo)致數(shù)據(jù)庫(kù)負(fù)載偏高了，而由于慢查詢還沒(méi)執(zhí)行完，因此慢查詢?nèi)罩具€看不到任何語(yǔ)句。此時(shí)可以使用 show processlist 命令判斷正在執(zhí)行的慢查詢。show processlist 顯示哪些線程正在運(yùn)行。如果有 PROCESS 權(quán)限，則可以看到所有線程。否則，只能看到當(dāng)前會(huì)話的線程。
知識(shí)擴(kuò)展：如果不使用 FULL 關(guān)鍵字，在 info 字段中只顯示每個(gè)語(yǔ)句的前 100 個(gè)字符，如果想看語(yǔ)句的全部?jī)?nèi)容可以使用 full 修飾（show full processlist）。
執(zhí)行結(jié)果如下：
mysql> show processlist\G`
`......`
`*************************** 10. row ***************************`
`Id: 7651833`
`User: one`
`Host: 192.168.1.251:52154`
`db: ops`
`Command: Query`
`Time: 3`
`State: User sleep`
`Info: select sleep(10)`
`......`
`10 rows in set (0.00 sec)`
這里對(duì)上面結(jié)果解釋一下：
Time：表示執(zhí)行時(shí)間
Info：表示 SQL 語(yǔ)句
我們這里可以通過(guò)它的執(zhí)行時(shí)間（Time）來(lái)判斷是否是慢 SQL。
2 使用 explain 分析慢查詢
分析 SQL 執(zhí)行效率是優(yōu)化 SQL 的重要手段，通過(guò)上面講的兩種方法，定位到慢查詢語(yǔ)句后，我們就要開(kāi)始分析 SQL 執(zhí)行效率了，子曾經(jīng)曰過(guò)：“工欲善其事，必先利其器”，我們可以通過(guò) explain、show profile 和 trace 等診斷工具來(lái)分析慢查詢。本節(jié)先講解 explain 的使用，在下節(jié)將分享 show profile 和 trace 的使用。
Explain 可以獲取 MySQL 中 SQL 語(yǔ)句的執(zhí)行計(jì)劃，比如語(yǔ)句是否使用了關(guān)聯(lián)查詢、是否使用了索引、掃描行數(shù)等。可以幫我們選擇更好地索引和寫出更優(yōu)的 SQL 。使用方法：在查詢語(yǔ)句前面加上 explain 運(yùn)行就可以了。
這也是分析 SQL 時(shí)最常用的，也是作者最推薦的一種分析慢查詢的方式。下面我們來(lái)看下示例~~
為了便于理解，先創(chuàng)建兩張測(cè)試表（方便第 1、2 節(jié)實(shí)驗(yàn)使用），建表及數(shù)據(jù)寫入語(yǔ)句如下：
CREATE DATABASE muke; /* 創(chuàng)建測(cè)試使用的database，名為muke */
use muke; /* 使用muke這個(gè)database */
drop table if exists t1; /* 如果表t1存在則刪除表t1 */
CREATE TABLE `t1` ( /* 創(chuàng)建表t1 */
`id` int(11) NOT NULL auto_increment,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創(chuàng)建時(shí)間',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '記錄更新時(shí)間',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`),
KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
drop procedure if exists insert_t1; /* 如果存在存儲(chǔ)過(guò)程insert_t1，則刪除 */
delimiter ;;
create procedure insert_t1() /* 創(chuàng)建存儲(chǔ)過(guò)程insert_t1 */
begin
declare i int; /* 聲明變量i */
set i=1; /* 設(shè)置i的初始值為1 */
while(i<=1000)do /* 對(duì)滿足i<=1000的值進(jìn)行while循環(huán) */
insert into t1(a,b) values(i, i); /* 寫入表t1中a、b兩個(gè)字段，值都為i當(dāng)前的值 */
set i=i+1; /* 將i加1 */
end while;
end;;
delimiter ; /* 創(chuàng)建批量寫入1000條數(shù)據(jù)到表t1的存儲(chǔ)過(guò)程insert_t1 */
call insert_t1(); /* 運(yùn)行存儲(chǔ)過(guò)程insert_t1 */
drop table if exists t2; /* 如果表t2存在則刪除表t2 */
create table t2 like t1; /* 創(chuàng)建表t2，表結(jié)構(gòu)與t1一致 */
insert into t2 select * from t1; /* 將表t1的數(shù)據(jù)導(dǎo)入到t2 */
下面嘗試使用 explain 分析一條 SQL，例子如下：
mysql> explain select * from t1 where b=100;
Explain 的結(jié)果各字段解釋如下：
加粗的列為需要重點(diǎn)關(guān)注的項(xiàng)。
| 列名 | 解釋 |
|---|---|
| id | 查詢編號(hào) |
| select_type | 查詢類型：顯示本行是簡(jiǎn)單還是復(fù)雜查詢 |
| table | 涉及到的表 |
| partitions | 匹配的分區(qū)：查詢將匹配記錄所在的分區(qū)。僅當(dāng)使用 partition 關(guān)鍵字時(shí)才顯示該列。對(duì)于非分區(qū)表，該值為 NULL。 |
| type | 本次查詢的表連接類型 |
| possible_keys | 可能選擇的索引 |
| key | 實(shí)際選擇的索引 |
| key_len | 被選擇的索引長(zhǎng)度：一般用于判斷聯(lián)合索引有多少列被選擇了 |
| ref | 與索引比較的列 |
| rows | 預(yù)計(jì)需要掃描的行數(shù)，對(duì) InnoDB 來(lái)說(shuō)，這個(gè)值是估值，并不一定準(zhǔn)確 |
| filtered | 按條件篩選的行的百分比 |
| Extra | 附加信息 |
其中 explain 各列都有各種不同的值，這里介紹幾個(gè)比較重要列常包含的值:包含 select_typ、type 和 Extra。
下面將列出它們常見(jiàn)的一些值，可稍微過(guò)一遍，不需要完全記下來(lái)，在后續(xù)章節(jié)分析 SQL 時(shí)，可以返回查詢本節(jié)內(nèi)容并對(duì)比各種值的區(qū)別。
2.1 select_type
| select_type 的值 | 解釋 |
|---|---|
| SIMPLE | 簡(jiǎn)單查詢(不使用關(guān)聯(lián)查詢或子查詢) |
| PRIMARY | 如果包含關(guān)聯(lián)查詢或者子查詢，則最外層的查詢部分標(biāo)記為primary |
| UNION | 聯(lián)合查詢中第二個(gè)及后面的查詢 |
| DEPENDENT UNION | 滿足依賴外部的關(guān)聯(lián)查詢中第二個(gè)及以后的查詢 |
| UNION RESULT | 聯(lián)合查詢的結(jié)果 |
| SUBQUERY | 子查詢中的第一個(gè)查詢 |
| DEPENDENT SUBQUERY | 子查詢中的第一個(gè)查詢，并且依賴外部查詢 |
| DERIVED | 用到派生表的查詢 |
| MATERIALIZED | 被物化的子查詢 |
| UNCACHEABLE SUBQUERY | 一個(gè)子查詢的結(jié)果不能被緩存，必須重新評(píng)估外層查詢的每一行 |
| UNCACHEABLE UNION | 關(guān)聯(lián)查詢第二個(gè)或后面的語(yǔ)句屬于不可緩存的子查詢 |
2.2 type
| type的值 | 解釋 |
|---|---|
| system | 查詢對(duì)象表只有一行數(shù)據(jù),且只能用于 MyISAM 和 Memory 引擎的表，這是最好的情況 |
| const | 基于主鍵或唯一索引查詢，最多返回一條結(jié)果 |
| eq_ref | 表連接時(shí)基于主鍵或非 NULL 的唯一索引完成掃描 |
| ref | 基于普通索引的等值查詢，或者表間等值連接 |
| fulltext | 全文檢索 |
| ref_or_null | 表連接類型是 ref，但進(jìn)行掃描的索引列中可能包含 NULL 值 |
| index_merge | 利用多個(gè)索引 |
| unique_subquery | 子查詢中使用唯一索引 |
| index_subquery | 子查詢中使用普通索引 |
| range | 利用索引進(jìn)行范圍查詢 |
| index | 全索引掃描 |
| ALL | 全表掃描 |
上表的這些情況，查詢性能從上到下依次是最好到最差。
2.3 Extra
| Extra 常見(jiàn)的值 | 解釋 | 例子 |
|---|---|---|
| Using filesort | 將用外部排序而不是索引排序，數(shù)據(jù)較小時(shí)從內(nèi)存排序，否則需要在磁盤完成排序 | explain select * from t1 order by create_time; |
| Using temporary | 需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來(lái)存儲(chǔ)結(jié)構(gòu)，通常發(fā)生對(duì)沒(méi)有索引的列進(jìn)行 GROUP BY 時(shí) | explain select * from t1 group by create_time; |
| Using index | 使用覆蓋索引 | explain select a from t1 where a=111; |
| Using where | 使用 where 語(yǔ)句來(lái)處理結(jié)果 | explain select * from t1 where create_time=‘2019-06-18 14:38:24’; |
| Impossible WHERE | 對(duì) where 子句判斷的結(jié)果總是 false 而不能選擇任何數(shù)據(jù) | explain select * from t1 where 1<0; |
| Using join buffer (Block Nested Loop) | 關(guān)聯(lián)查詢中，被驅(qū)動(dòng)表的關(guān)聯(lián)字段沒(méi)索引 | explain select * from t1 straight_join t2 on (t1.create_time=t2.create_time); |
| Using index condition | 先條件過(guò)濾索引，再查數(shù)據(jù) | explain select * from t1 where a >900 and a like “%9”; |
| Select tables optimized away | 使用某些聚合函數(shù)（比如 max、min）來(lái)訪問(wèn)存在索引的某個(gè)字段是 | explain select max(a) from t1; |
3 總結(jié)
今天我分享的關(guān)于定位慢 SQL 及使用 explain 分析慢 SQL 到這里就結(jié)束了。本節(jié)知識(shí)點(diǎn)總結(jié)如下：
本節(jié)首先講到如何定位慢 SQL:
一種方法是查看慢查詢?nèi)罩?/span>
另一種方法是 show process 查看正在執(zhí)行的 SQL
再講到通過(guò) explain 分析慢 SQL，explain 會(huì)返回很多字段，其中 select_type、type、key、rows、Extra 是重點(diǎn)關(guān)注項(xiàng)。
在工作中及面試時(shí)，SQL 性能優(yōu)化都是我們經(jīng)常遇到的問(wèn)題，要想做好性能優(yōu)化，我們必須學(xué)會(huì)使用 SQL 優(yōu)化時(shí)需要的工具，進(jìn)行定位和分析。由于篇幅的問(wèn)題，本小節(jié)只介紹了 explain 工具的使用，在下節(jié)將補(bǔ)充另外兩種分析慢查詢的工具：show profile 和 trace。在后面我會(huì)再講解 SQL 優(yōu)化的一些知識(shí)點(diǎn)，相信小伙伴們 SQL 性能優(yōu)化時(shí)一定可以越來(lái)越熟練。
最后小伙伴們可以將處理問(wèn)題時(shí)的心得體會(huì)進(jìn)行總結(jié)，也歡迎給我留言分享，我們一起來(lái)交流、學(xué)習(xí)、進(jìn)步。
參考資料：《深入淺出 MySQL》（第2版）：第 18 章第 1 節(jié)
— 完 —
歡迎關(guān)注微信公眾號(hào)：互聯(lián)網(wǎng)全棧架構(gòu)，收取更多有價(jià)值的信息。