在上篇文章我們學習了MySQL進階——存儲引擎，這篇文章學習MySQL進階——SQL性能分析。

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

SQL執(zhí)行頻率

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

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

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

運行結果如下：

慢查詢日志

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

慢查詢日志記錄了所有執(zhí)行時間超過指定參數（long_query_time，單位：秒，默認10秒）的所有SQL語句的日志。

執(zhí)行如下命令查看是否開啟慢查詢日志，

show?variables?like?'slow_query_log';

如下圖所示：

配置文件開啟

默認情況下，MySQL的慢查詢日志是關閉的，我們可以在MySQL的配置文件（/etc/my.cnf）添加如下命令開啟慢查詢日志，

slow_query_log=1??#?開啟慢查詢日志
long_query_time=2??#?設置慢查詢日志的時間為2秒，SQL語句執(zhí)行超過2秒，就視為慢查詢，記錄慢查詢日志

如下圖所示：

注意：如果是在docker安裝的mysql，需要先執(zhí)行如下命令，進入MySQL容器內部再在MySQL配置文件中添加開啟慢查詢日志代碼。

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

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

配置完成后需要重啟MySQL服務器。

查看慢查詢日志開啟，如下圖所示：

SQL命令開啟

當然我們也可以通過在mysql中執(zhí)行如下命令，開啟慢查詢日志，

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

如下圖所示：

注意：這種開啟方式不是永久開啟慢查詢日志，只要MySQL重啟了，慢查詢日志就會關閉。

示例

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

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

profile詳情

在慢查詢日志中，我們只能獲取超過設置的時間SQL語句信息，例如設置的時長為2秒，那么只能獲取超過2秒的SQL語句信息，執(zhí)行了1.99秒的SQL語句無法獲取，這時我們就可以使用profie詳情。

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

首先通過have_profiling參數，查看當前MySQL是否支持profile操作：

SELECT?@@have_profiling;

如下圖所示：

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

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

如下圖所示：

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

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

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

如下圖所示：

explain執(zhí)行計劃

在上面的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：實際使用的索引，如果為NULL，則沒有使用索引；

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

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

• filtered：返回結果的行數占需讀取行數的百分比，filtered的值越大越好；

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

公眾號：白巧克力LIN

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

- END -