MySQL?SQL性能分析之慢查詢?nèi)罩?、explain使用詳解

更新時間：2023年04月14日 11:24:49 作者：南有喬木i

這篇文章主要介紹了MySQL?SQL性能分析?慢查詢?nèi)罩?、explain使用,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下

SQL執(zhí)行頻率

MySQL 客戶端連接成功后，通過 show [session|global] status 命令可以提供服務器狀態(tài)信息。通過如下指令，可以查看當前數(shù)據(jù)庫的insert、update、delete、delect的訪問頻次：

-- session 是查看當前會話;
-- global 是查詢?nèi)謹?shù)據(jù); 
show global status like 'Com_______';

Com_delete：刪除次數(shù)
Com_insert：插入次數(shù)
Com_select：查詢次數(shù)
Com_update：更新次數(shù)

通過上述指令，可以查看到當前數(shù)據(jù)庫是以查詢?yōu)橹鳎€是以增刪改為主，從而為數(shù)據(jù)庫優(yōu)化提供參考依據(jù)。如果以增刪改為主，可以考慮不進行索引優(yōu)化。如果以查詢?yōu)橹?，要考慮對數(shù)據(jù)庫的索引進行優(yōu)化。

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

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

MySQL的慢查詢?nèi)罩灸J不開啟，可以查看系統(tǒng)變量 slow_query_log。

-- 查看慢查詢?nèi)罩臼欠耖_啟
show variables like 'slow_query_log';

如果要開啟慢查詢?nèi)罩?，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息：

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

配置完畢之后，通過以下指令重新啟動MySQL服務器進行測試，查看慢日志文件中記錄的信息 /var/lib/mysql/localhost-slow.log。

例如：導入近1000w條數(shù)據(jù)。執(zhí)行以下SQL：

select count(*) from tb_sku;

可以看到，耗時近90秒

profile

show profiles 能夠在做SQL優(yōu)化時了解時間都耗費到了哪里。

通過 have_profiling 參數(shù)，能夠看到當前MySQL是否支持profile操作：

select @@have_profiling ;

查看profile是否開啟：

select @@profiling;

通過set語句在session/global級別開啟profiling：

set profiling = 1;

執(zhí)行一些SQL語句，然后就會記錄SQL的耗時明細了。

-- 查看每一條SQL的耗時基本情況
show profiles;
-- 查看指定query_id的SQL語句各個階段的耗時情況
show profile for query [query_id];
-- 查看指定query_id的SQL語句CPU的使用情況
show profile cpu for query [query_id];

explain

explain或者desc命令獲取 MySQL 如何執(zhí)行select語句的信息，包括在select語句執(zhí)行過程中表如何連接和連接的順序。

-- 直接在select語句之前加上關鍵字 explain / desc
explain select 字段列表 from 表名 where 條件;

字段	描述
id	select查詢的序列號，表示查詢中執(zhí)行select子句或者是操作表的順序 id相同，執(zhí)行順序從上到下；id不同，值越大，越先執(zhí)行
select_type	表示select的類型，常見的取值(不止這么多)： `SIMPLE`：簡單表，即不使用表連接或者子查詢 `PRIMARY`：主查詢，即外層的查詢 `UNION`：UNION 中的第二個或者后面的查詢語句 `SUBQUERY`：SELECT/WHERE之后包含了子查詢
type	表示連接類型，性能由好到差的連接類型為NULL(不查詢表)、system(訪問系統(tǒng)表)、const(主鍵或唯一索引)、eq_ref、ref(非唯一索引)、range、 index(用了索引，遍歷全部索引樹)、all(全表掃描)。
possible_key	顯示可能應用在這張表上的索引，一個或多個。
key	實際使用的索引，如果為NULL，則沒有使用索引。
key_len	表示索引中使用的字節(jié)數(shù)，該值為索引字段最大可能長度，并非實際使用長度，在不損失精確性的前提下，長度越短越好。
rows	MySQL認為必須要執(zhí)行查詢的行數(shù)，在innodb引擎的表中，是一個估計值，可能并不總是準確的。
filtered	表示返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比， filtered 的值越大越好。