手把手教你分析MySQL查詢性能瓶頸，包教包會

2022 年 8 月 15 日
筆記
java高頻面試題, MySQL

當一條SQL執行較慢，需要分析性能瓶頸，到底慢在哪？

我們一般會使用Explain查看其執行計劃，從執行計劃中得知這條SQL有沒有使用索引？使用了哪個索引？

但是執行計劃顯示內容不夠詳細，如果顯示用到了某個索引，查詢依然很慢，我們就無法得知具體是哪一步比較耗時？

好在MySQL提供一個SQL性能分析工具 — Profile。

Profile 可以幫助我們分析SQL性能瓶頸和資源消耗情況。

1. 查看Profile配置

show variables like '%profil%';

have_profiling 表示是否支援profile功能，YES表示支援

profiling 表示是否開啟profile功能，ON開啟，OFF關閉，默認是關閉狀態

profiling_history_size 表示保存最近15條歷史數據

2. 開啟Profile功能

set profiling=1;

注意：修改配置，只對當前會話生效，會話關閉，Profile歷史資訊被清空。

3. 使用Profile

先造點數據，創建一張用戶表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` tinyint NOT NULL  DEFAULT 0 NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

執行一條耗時SQL：

select * from user order by name;

下面輪到主角Profile出場了。

我們執行的所有SQL語句都會被記錄到Profile裡面，包括執行失敗的SQL語句。

可以使用show profiles命令查看：

輸出參數詳解：

Query_ID 表示自動分配的查詢ID，順序遞增。

Duration 表示SQL語句執行耗時

Query 表示SQL語句內容

然後，我們再使用Query_ID去Profile中查看具體每一步的耗時情況：

show profile for query 1;

可以清楚的看到耗時主要花在創建排序索引（Creating sort index）上面。

再試一條SQL：

select distinct name from user;

這次的耗時主要花在了，創建臨時文件、拷貝文件到磁碟、發送數據、刪除臨時表上面。

由此，可以得知distinct函數會創建臨時文件，提醒我們建索引。

我們還可以擴展一下這條分析語句，查看一下cpu和block io的使用情況：

show profile cpu,block io for query 2;

另外，其實所有Profile歷史數據都被記錄在information_schema.profiling表中，我們也可以查詢表得到結果：

select * from information_schema.profiling where Query_ID=2;

以上數據都是基於MySQL5.7版本，在MySQL8.0版本的輸出結果欄位有些變化。

另外，細心的你應該發現了，在我們每執行完一條SQL，都顯示了一條warning資訊，我們查看一下具體的warning資訊：

show warnings;

意思就是，Profile工具將來有可能被刪除，不建議繼續使用了。

好吧，下篇文章我們再一塊學習一下MySQL提供的，用來替換Profile的最新性能瓶頸分析工具，使用更便捷。

文章持續更新，可以微信搜一搜「一燈架構」第一時間閱讀更多技術乾貨。

Tags: java高頻面試題 MySQL