在上篇文章我們學(xué)習(xí)了MySQL進(jìn)階——存儲引擎，這篇文章學(xué)習(xí)MySQL進(jìn)階——SQL性能分析。

SQL性能分析主要是從SQL語句執(zhí)行頻率、耗時時間、CPU使用情況和執(zhí)行時表連接情況進(jìn)行分析，常用的方法工具有：SQL執(zhí)行頻率、慢查詢?nèi)罩?、profile詳情和explain執(zhí)行計(jì)劃。

SQL執(zhí)行頻率

通過show [session|global] status命令可以提供服務(wù)器狀態(tài)信息，通過如下命令，可以查看當(dāng)前數(shù)據(jù)庫的增刪改查的訪問頻率：

SHOW?GLOBAL?STATUS?LIKE?'Com_______';

其中：一個‘_’表示一個字符。

運(yùn)行結(jié)果如下：

慢查詢?nèi)罩?/h3>

通過SQL執(zhí)行頻率，我們可以發(fā)現(xiàn)增數(shù)據(jù)的SQL語句執(zhí)行頻率更多，但無法知道哪條SQL語句執(zhí)行情況。

慢查詢?nèi)罩居涗浟怂袌?zhí)行時間超過指定參數(shù)（long_query_time，單位：秒，默認(rèn)10秒）的所有SQL語句的日志。

執(zhí)行如下命令查看是否開啟慢查詢?nèi)罩?#xff0c;

show?variables?like?'slow_query_log';

如下圖所示：

配置文件開啟

默認(rèn)情況下，MySQL的慢查詢?nèi)罩臼顷P(guān)閉的，我們可以在MySQL的配置文件（/etc/my.cnf）添加如下命令開啟慢查詢?nèi)罩?#xff0c;

slow_query_log=1??#?開啟慢查詢?nèi)罩?long_query_time=2??#?設(shè)置慢查詢?nèi)罩镜臅r間為2秒，SQL語句執(zhí)行超過2秒，就視為慢查詢，記錄慢查詢?nèi)罩?

如下圖所示：

注意：如果是在docker安裝的mysql，需要先執(zhí)行如下命令，進(jìn)入MySQL容器內(nèi)部再在MySQL配置文件中添加開啟慢查詢?nèi)罩敬a。

docker?exec?-it?容器ID??/bin/bash

在/etc/my.cnf文件添加如下圖代碼：

配置完成后需要重啟MySQL服務(wù)器。

查看慢查詢?nèi)罩鹃_啟，如下圖所示：

SQL命令開啟

當(dāng)然我們也可以通過在mysql中執(zhí)行如下命令，開啟慢查詢?nèi)罩?#xff0c;

set?global?slow_query_log?=?ON;
set?global?slow_launch_time?=?3;
show?variables?like?'slow%';

如下圖所示：

注意：這種開啟方式不是永久開啟慢查詢?nèi)罩?#xff0c;只要MySQL重啟了，慢查詢?nèi)罩揪蜁P(guān)閉。

示例

當(dāng)我們執(zhí)行了SQL語句而操作時長超過2秒，就會記錄慢查詢，這里我們執(zhí)行了刪除UserTable表操作，大概用了10秒，慢日志如下圖所示：

在日志中，我們可以看到執(zhí)行日期、時長、用戶、IP、數(shù)據(jù)庫和SQL語句等相關(guān)信息。

profile詳情

在慢查詢?nèi)罩局?#xff0c;我們只能獲取超過設(shè)置的時間SQL語句信息，例如設(shè)置的時長為2秒，那么只能獲取超過2秒的SQL語句信息，執(zhí)行了1.99秒的SQL語句無法獲取，這時我們就可以使用profie詳情。

profiles詳情能夠在做SQL優(yōu)化時幫助我們了解時間都耗費(fèi)到哪里去了。

首先通過have_profiling參數(shù)，查看當(dāng)前MySQL是否支持profile操作：

SELECT?@@have_profiling;

如下圖所示：

接著執(zhí)行如下代碼查看profile是否開啟，

SELECT?@@profiling;???#?查看是否開啟
SET?profiling=1;???#?開啟profile

如下圖所示：

接下來我們就可以使用show profiles命令查看SQL語句詳情的執(zhí)行時間了，如下圖所示：

這樣我們就可以查看每條SQL語句的耗時情況，我們可以通過上面的Query_ID詳細(xì)地查看SQL語句各階段的耗時情況、CPU使用情況，可以執(zhí)行如下代碼：

#?show?profile?for?query?Query_ID;???#?查看SQL語句各階段的耗時情況
#?show?profile?cpu?for?query?Query_ID;??#?查看SQL語句CPU使用情況

如下圖所示：

explain執(zhí)行計(jì)劃

在上面的SQL性能分析中，我們只能獲取到SQL語句的頻率和耗時時間，無法知道SQL語句的執(zhí)行過程中的表連接情況，這時我們可以通過explain或desc命令來查看SQL語句的執(zhí)行過程中的表連接情況，其使用方法如下：

explain/desc?SQL語句;

如下圖所示：

其中：

• ID：select查詢的序列號，表示查詢中執(zhí)行select子句或者操作表的順序，ID相同，執(zhí)行順序從上到下，ID不同，值越大，越先執(zhí)行；

• select_type：SELECT的類型，常見的取值有SIMPLE(簡單表，即不使用表連接或者子查詢)、PRIMARY(主查詢，即外層的查詢)、UNION(UNION 中的第二個或者后面的查詢語句)、SUBOUERY(SELECT/WHERE之后包含了子查詢)；

• type：連接類型，性能由好到差的類型為：NULL、system、const、eq_ref、range、index、all；

• possible_keys：可能用到的索引，一個或多個；

• key：實(shí)際使用的索引，如果為NULL，則沒有使用索引；

• Key_len：表示索引中使用的字節(jié)數(shù)，該值為索引字段最大可能長度，并非實(shí)際使用長度，在不損失精確性的前提下，長度越短越好；

• rows：MySQL認(rèn)為必要執(zhí)行查詢的行數(shù)，在innodb引擎的表中，是一個估計(jì)值，可能并不總是準(zhǔn)確的；

• filtered：返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比，filtered的值越大越好；

好了，SQL性能分析就講到這里了。

公眾號：白巧克力LIN

該公眾號發(fā)布Python、數(shù)據(jù)庫、Linux、Flask、Django、自動化測試、Git、算法、前端、服務(wù)器等相關(guān)文章！

- END -