MySQL慢查詢?nèi)罩緩呐渲玫絻?yōu)化實踐全解析
更新時間：2026年01月21日 16:46:28 作者：小Mie不吃飯
慢查詢?nèi)罩臼荕ySQL性能優(yōu)化的關(guān)鍵工具,記錄執(zhí)行時間超過閾值的SQL語句,幫助定位性能瓶頸和優(yōu)化SQL,本文介紹MySQL慢查詢?nèi)罩緩呐渲玫絻?yōu)化實踐全解析,感興趣的朋友跟隨小編一起看看吧
慢查詢?nèi)罩臼荕ySQL性能優(yōu)化的核心工具之一，掌握其配置、分析和優(yōu)化方法，是架構(gòu)師和DBA必備的核心技能。
本文將從基礎(chǔ)概念到實戰(zhàn)優(yōu)化，全面講解慢查詢?nèi)罩镜氖褂梅椒ê妥罴褜嵺`。
一、什么是慢查詢?nèi)罩?/h2>
慢查詢?nèi)罩荆⊿low Query Log）是 MySQL 內(nèi)置的日志功能，專門用于記錄執(zhí)行時間超過預設(shè)閾值（long_query_time）的 SQL 語句。它就像MySQL的“性能黑匣子”，能精準定位執(zhí)行效率低下的SQL，是數(shù)據(jù)庫性能優(yōu)化的核心抓手。
二、核心作用
- 性能診斷：快速定位系統(tǒng)中執(zhí)行效率低的SQL語句，找到性能短板。
- 瓶頸定位：分析慢查詢的根因（如全表掃描、索引缺失、鎖等待等）。
- 優(yōu)化依據(jù)：為SQL改寫、索引調(diào)整、數(shù)據(jù)庫參數(shù)優(yōu)化提供數(shù)據(jù)支撐。
- 容量規(guī)劃：通過慢查詢趨勢，預判數(shù)據(jù)庫性能瓶頸和擴容需求。
三、配置參數(shù)詳解
通過以下命令可查看所有慢查詢相關(guān)配置參數(shù)：
-- 查看慢查詢相關(guān)參數(shù) SHOW VARIABLES LIKE '%slow%'; -- 查看慢查詢閾值參數(shù) SHOW VARIABLES LIKE '%long_query_time%';
核心配置參數(shù)說明：
| 參數(shù)名 | 取值 | 說明 |
|---|---|---|
slow_query_log | OFF/ON | 是否開啟慢查詢?nèi)罩荆J關(guān)閉） |
slow_query_log_file | 路徑字符串 | 慢查詢?nèi)罩疚募拇鎯β窂剑ㄈ?code>/var/log/mysql/slow.log） |
long_query_time | 數(shù)值（秒） | 慢查詢閾值，默認10秒（MySQL 5.7+支持微秒級，如0.5表示500毫秒） |
min_examined_row_limit | 數(shù)值 | 最少檢查行數(shù)閾值，低于該值的慢查詢不記錄（默認0） |
log_queries_not_using_indexes | OFF/ON | 是否記錄未使用索引的查詢（即使執(zhí)行時間未達閾值） |
log_slow_admin_statements | OFF/ON | 是否記錄慢管理語句（如ALTER TABLE、ANALYZE TABLE等） |
log_output | FILE/TABLE/NONE | 日志輸出方式：文件/數(shù)據(jù)庫表/不輸出 |
四、開啟和配置
1. 臨時開啟（重啟失效）
適用于臨時調(diào)試，MySQL重啟后配置會恢復默認值：
-- 開啟慢查詢?nèi)罩? SET GLOBAL slow_query_log = 'ON'; -- 設(shè)置慢查詢閾值為2秒 SET GLOBAL long_query_time = 2; -- 指定日志文件路徑 SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'; -- 記錄未使用索引的查詢 SET GLOBAL log_queries_not_using_indexes = 'ON';
2. 永久開啟（修改配置文件）
修改MySQL配置文件（my.cnf/my.ini），重啟后生效，適用于生產(chǎn)環(huán)境：
[mysqld] # 開啟慢查詢?nèi)罩荆?=開啟，0=關(guān)閉） slow_query_log = 1 # 日志文件路徑 slow_query_log_file = /var/log/mysql/slow.log # 慢查詢閾值（秒） long_query_time = 2 # 記錄未使用索引的查詢 log_queries_not_using_indexes = 1 # 日志輸出到文件 log_output = FILE # 可選：記錄慢管理語句 log_slow_admin_statements = 1 # 可選：最少檢查行數(shù)閾值 min_examined_row_limit = 100
修改完成后重啟MySQL服務(wù)：
# CentOS/RHEL systemctl restart mysqld # Ubuntu/Debian systemctl restart mysql
五、慢查詢?nèi)罩靖袷椒治?/h2>
典型日志條目
# Time: 2024-01-01T10:00:00.123456Z # User@Host: root[root] @ localhost [] Id: 5 # Query_time: 5.123456 Lock_time: 0.001000 Rows_sent: 10 Rows_examined: 1000000 SET timestamp=1672560000; SELECT * FROM users WHERE last_name LIKE '%smith%' ORDER BY create_time DESC;
關(guān)鍵字段解釋
| 字段名 | 說明 |
|---|---|
Time | 查詢執(zhí)行的時間戳（UTC時間） |
User@Host | 執(zhí)行查詢的用戶和主機信息 |
Id | 數(shù)據(jù)庫連接ID |
Query_time | 查詢總執(zhí)行時間（秒，含微秒） |
Lock_time | 查詢過程中鎖等待時間（秒） |
Rows_sent | 返回給客戶端的行數(shù) |
Rows_examined | 數(shù)據(jù)庫掃描的行數(shù)（核心指標，行數(shù)越多性能越差） |
Rows_affected | 受DML語句（UPDATE/DELETE/INSERT）影響的行數(shù) |
timestamp | 查詢開始的UNIX時間戳 |
六、慢查詢分析工具
1. mysqldumpslow（MySQL 自帶）
MySQL內(nèi)置的輕量級分析工具，無需額外安裝，適合快速匯總慢查詢：
# 按查詢時間排序，顯示最慢的前10條 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 按執(zhí)行次數(shù)排序 mysqldumpslow -s c /var/log/mysql/slow.log # 按鎖時間排序 mysqldumpslow -s l /var/log/mysql/slow.log # 分析特定用戶的慢查詢（保留原始SQL） mysqldumpslow -a -g "root" /var/log/mysql/slow.log
2. pt-query-digest（Percona Toolkit）
Percona出品的專業(yè)分析工具，功能強大，是生產(chǎn)環(huán)境首選：
安裝（以CentOS為例）：
yum install percona-toolkit -y
常用命令：
# 基礎(chǔ)分析，輸出詳細報告 pt-query-digest /var/log/mysql/slow.log # 分析最近12小時的慢查詢 pt-query-digest --since=12h /var/log/mysql/slow.log # 分析指定時間段的慢查詢 pt-query-digest --since='2024-01-01 00:00:00' --until='2024-01-01 23:59:59' /var/log/mysql/slow.log # 將分析結(jié)果輸出到文件 pt-query-digest /var/log/mysql/slow.log > /tmp/slow_report_$(date +%Y%m%d).txt
3. mysqlslow（第三方工具）
輕量級第三方工具，安裝簡單，輸出結(jié)果直觀：
# 安裝（需先安裝pip） pip install mysqlslow # 分析慢查詢?nèi)罩? mysqlslow /var/log/mysql/slow.log
七、慢查詢?nèi)罩颈砟Ｊ?/h2>
除了文件存儲，MySQL還支持將慢查詢?nèi)罩敬鎯Φ綌?shù)據(jù)庫表中，便于SQL查詢分析。
啟用表模式存儲：
-- 設(shè)置日志輸出到表（FILE,TABLE 表示同時輸出到文件和表） SET GLOBAL log_output = 'TABLE'; -- 開啟慢查詢?nèi)罩? SET GLOBAL slow_query_log = 'ON'; -- 查詢慢查詢?nèi)罩颈? SELECT * FROM mysql.slow_log;
表結(jié)構(gòu)：
SHOW CREATE TABLE mysql.slow_log;
核心字段說明：
| 字段名 | 類型 | 說明 |
|---|---|---|
start_time | DATETIME(6) | 查詢開始時間（含微秒） |
query_time | TIME(6) | 查詢執(zhí)行時間 |
lock_time | TIME(6) | 鎖等待時間 |
rows_sent | INT UNSIGNED | 返回行數(shù) |
rows_examined | INT UNSIGNED | 掃描行數(shù) |
sql_text | LONGTEXT | SQL語句內(nèi)容 |
user_host | MEDIUMTEXT | 用戶和主機信息 |
注意：表模式存儲會增加數(shù)據(jù)庫寫入壓力，生產(chǎn)環(huán)境建議優(yōu)先使用文件模式。
八、最佳實踐和優(yōu)化建議
1. 閾值設(shè)置建議
閾值需根據(jù)業(yè)務(wù)場景調(diào)整，避免記錄過多無效日志或遺漏關(guān)鍵慢查詢：
-- 生產(chǎn)環(huán)境（平衡性能和排查需求） SET GLOBAL long_query_time = 2; -- 2秒閾值 -- 開發(fā)/測試環(huán)境（嚴格排查） SET GLOBAL long_query_time = 0.5; -- 500毫秒 -- 高并發(fā)核心業(yè)務(wù)（微秒級監(jiān)控，MySQL 5.7+） SET GLOBAL long_query_time = 0.1; -- 100毫秒
2. 日志輪轉(zhuǎn)配置
避免慢查詢?nèi)罩疚募^大，使用logrotate實現(xiàn)日志自動輪轉(zhuǎn)：
# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow.log {
daily # 每日輪轉(zhuǎn)
rotate 30 # 保留30天日志
missingok # 日志文件不存在時不報錯
compress # 壓縮舊日志
delaycompress # 延遲壓縮（保留最新的輪轉(zhuǎn)文件未壓縮）
notifempty # 空文件不輪轉(zhuǎn)
create 640 mysql mysql # 新建日志文件的權(quán)限和屬主
postrotate # 輪轉(zhuǎn)后執(zhí)行的命令
mysqladmin flush-logs # 刷新日志，生成新文件
endscript
}3. 定期分析計劃
編寫自動化腳本，每日分析慢查詢并歸檔，示例：
#!/bin/bash
# /usr/local/bin/analyze_slow_log.sh
# 腳本功能：每日分析慢查詢?nèi)罩静w檔
# 定義變量
DATE=$(date +%Y%m%d)
LOG_PATH="/var/log/mysql"
REPORT_PATH="${LOG_PATH}/reports"
SLOW_LOG="${LOG_PATH}/slow.log"
# 創(chuàng)建報告目錄
mkdir -p ${REPORT_PATH}
# 使用pt-query-digest分析日志并生成報告
pt-query-digest ${SLOW_LOG} > ${REPORT_PATH}/slow_report_${DATE}.txt
# 備份并清空原日志文件
cp ${SLOW_LOG} ${LOG_PATH}/slow.log.${DATE}
> ${SLOW_LOG}
# 清理30天前的舊報告
find ${REPORT_PATH} -name "slow_report_*.txt" -mtime +30 -delete添加到crontab，每日凌晨執(zhí)行：
# 編輯crontab crontab -e # 添加以下內(nèi)容 0 0 * * * /usr/local/bin/analyze_slow_log.sh > /dev/null 2>&1
九、性能監(jiān)控和告警
1. 監(jiān)控慢查詢數(shù)量
通過MySQL狀態(tài)變量實時監(jiān)控慢查詢數(shù)量：
-- 查看累計慢查詢數(shù)量 SHOW GLOBAL STATUS LIKE 'Slow_queries'; -- 查看當前正在執(zhí)行的慢查詢 SHOW PROCESSLIST; -- 或更詳細的信息 SHOW FULL PROCESSLIST;
2. 慢查詢告警腳本
編寫腳本監(jiān)控慢查詢數(shù)量，超過閾值時發(fā)送告警：
#!/bin/bash
# /usr/local/bin/slow_query_alert.sh
# 配置參數(shù)
MYSQL_CMD="mysql -uroot -p'你的密碼' -e"
THRESHOLD=100 # 慢查詢閾值
ALERT_EMAIL="admin@example.com"
# 獲取當前慢查詢總數(shù)
SLOW_COUNT=$($MYSQL_CMD "SHOW GLOBAL STATUS LIKE 'Slow_queries'" | grep Slow_queries | awk '{print $2}')
# 對比閾值并發(fā)送告警
if [ $SLOW_COUNT -gt $THRESHOLD ]; then
SUBJECT="【告警】MySQL慢查詢數(shù)量異常"
CONTENT="當前慢查詢總數(shù)：${SLOW_COUNT}，超過閾值${THRESHOLD}！\n請及時登錄數(shù)據(jù)庫排查慢查詢。"
echo -e ${CONTENT} | mail -s "${SUBJECT}" ${ALERT_EMAIL}
fi添加到crontab，每分鐘執(zhí)行一次：
* * * * * /usr/local/bin/slow_query_alert.sh > /dev/null 2>&1
十、注意事項
- 性能影響：開啟慢查詢?nèi)罩緯黾蛹s1-3%的數(shù)據(jù)庫性能開銷（主要是磁盤I/O），生產(chǎn)環(huán)境需評估后開啟。
- 磁盤空間：慢查詢?nèi)罩驹鲩L較快，必須配置日志輪轉(zhuǎn)，避免占滿磁盤。
- 敏感信息：日志中可能包含用戶密碼、業(yè)務(wù)敏感數(shù)據(jù)，需限制日志文件的訪問權(quán)限（如僅root和mysql用戶可讀?。?。
- 版本差異：MySQL 5.7+支持
long_query_time的微秒級精度，5.6及以下版本僅支持秒級；8.0版本默認日志格式有小幅調(diào)整。 - 測試驗證：開啟慢查詢后，需執(zhí)行
SELECT SLEEP(3);（假設(shè)閾值為2秒）驗證日志是否正常記錄。 - 索引記錄：
log_queries_not_using_indexes開啟后，會記錄大量簡單的無索引查詢（如SELECT * FROM t LIMIT 1），需結(jié)合min_examined_row_limit過濾。
面試回答（精簡版）
慢查詢?nèi)罩臼荕ySQL記錄執(zhí)行時間超過閾值SQL的核心工具，就像數(shù)據(jù)庫的“性能病歷本”，是優(yōu)化的關(guān)鍵依據(jù)。
核心回答要點：
- 開啟配置：生產(chǎn)環(huán)境通過修改
my.cnf永久開啟，核心參數(shù)包括slow_query_log=1（開啟）、long_query_time=2（閾值）、log_queries_not_using_indexes=1（記錄無索引查詢）。 - 分析工具：常用
mysqldumpslow（快速匯總）和pt-query-digest（專業(yè)分析），重點關(guān)注Query_time（執(zhí)行時間）、Rows_examined（掃描行數(shù)）等字段。 - 優(yōu)化思路：找到慢查詢后，用
EXPLAIN分析執(zhí)行計劃，核心優(yōu)化手段包括：添加合適的索引、改寫SQL（避免SELECT *、優(yōu)化子查詢）、調(diào)整數(shù)據(jù)庫參數(shù)（如緩沖池）。 - 最佳實踐：生產(chǎn)環(huán)境設(shè)置合理閾值（2秒），配置日志輪轉(zhuǎn)，定期自動分析并設(shè)置告警，平衡性能開銷和問題排查需求。
總結(jié)
- 慢查詢?nèi)罩臼荕ySQL性能優(yōu)化的核心工具，核心配置參數(shù)為
slow_query_log（開關(guān)）、long_query_time（閾值）、log_queries_not_using_indexes（無索引查詢記錄）。 - 生產(chǎn)環(huán)境建議通過修改配置文件永久開啟，結(jié)合
logrotate實現(xiàn)日志輪轉(zhuǎn)，使用pt-query-digest進行專業(yè)分析。 - 慢查詢優(yōu)化的核心思路是：通過日志定位慢SQL → 用
EXPLAIN分析執(zhí)行計劃 → 針對性優(yōu)化（加索引/改SQL/調(diào)參數(shù)），并建立定期分析和告警機制。
到此這篇關(guān)于MySQL慢查詢?nèi)罩緩呐渲玫絻?yōu)化實踐全解析的文章就介紹到這了,更多相關(guān)mysql慢查詢?nèi)罩緝?nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了mysql去重的兩種方法詳解及實例代碼的相關(guān)資料,這里對去重的兩種方法進行了一一實例詳解，需要的朋友可以參考下2017-01-01
這篇文章主要為大家詳細介紹了計算機二級考試MySQL知識點，詳細介紹了mysql中alter命令的使用方法，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-08-08
如何修改MYSQL5.7.17數(shù)據(jù)庫存儲文件的路徑
在搭建華為云服務(wù)器的時候遇到點問題，查看了網(wǎng)上好多的帖子都沒能解決，不知道有沒有跟我遇到一樣問題的老鐵，我就把我的解決辦法分享給大家，希望能夠幫助各位老鐵2023-05-05
mysql insert if not exists防止插入重復記錄的方法
在 MySQL 中，插入（insert）一條記錄很簡單，但是一些特殊應用，在插入記錄前，需要檢查這條記錄是否已經(jīng)存在，只有當記錄不存在時才執(zhí)行插入操作，本文介紹的就是這個問題的解決方案。2011-04-04
mysql忘記root密碼的解決辦法(針對不同mysql版本)
這篇文章主要介紹了mysql忘記root密碼的解決辦法(針對不同mysql版本),文章通過代碼示例和圖文結(jié)合的方式給大家講解的非常詳細,對大家的學習或工作有一定的幫助,需要的朋友可以參考下2024-06-06
CentOS7環(huán)境下安裝MySQL5.5數(shù)據(jù)庫
大家好，本篇文章主要講的是CentOS7環(huán)境下安裝MySQL5.5數(shù)據(jù)庫，感興趣的同學趕快來看一看吧，對你有幫助的話記得收藏一下，方便下次瀏覽2021-12-12
索引與視圖是我們在日常使用mysql必不可少的一部分，最近在學習中看到一本書中關(guān)于這方法寫的不錯，所以這篇文章主要給大家介紹了關(guān)于MySQL中索引與視圖的使用與區(qū)別的相關(guān)資料，需要的朋友可以參考借鑒，下面隨著小編來一起學習學習吧。2017-11-11
因為mysql的版本較多，而且又被oracle公司收購，所有很多朋友不是很清楚各個版本的區(qū)別，這里簡單介紹下，方便需要的朋友2013-06-06
這篇文章主要為大家分享了Win7下安裝MySQL5.7.16過程的筆記，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-01-01
MySQL性能優(yōu)化之table_cache配置參數(shù)淺析
這篇文章主要介紹了MySQL性能優(yōu)化之table_cache配置參數(shù)淺析,本文介紹了它的緩存機制、參數(shù)優(yōu)化及清空緩存的命令等,需要的朋友可以參考下2014-07-07