當一條SQL執行較慢,需要分析性能瓶頸,到底慢在哪? 我們一般會使用Explain查看其執行計劃,從執行計劃中得知這條SQL有沒有使用索引?使用了哪個索引? 但是執行計劃顯示內容不夠詳細,如果顯示用到了某個索引,查詢依然很慢,我們就無法得知具體是哪一步比較耗時? 好在MySQL提供一個SQL性能分析... ...
當一條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的最新性能瓶頸分析工具,使用更便捷。
文章持續更新,可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。