MySQL慢查詢開啟與優(yōu)化指南
更新時間：2026年03月26日 09:08:36 作者：qq_28372005
文章主要介紹了慢查詢日志在MySQL中的應用,包括其定義、開啟方式、參數(shù)解析及分析工具,通過案例詳細講解了如何通過慢查詢日志定位和優(yōu)化性能瓶頸,如索引失效、隱式類型轉換等問題,并給出了生產環(huán)境的最佳實踐及學習建議,需要的朋友可以參考下
一、前言
1.1 什么是慢查詢日志
慢查詢日志是MySQL提供的一種性能診斷工具，用于記錄執(zhí)行時間超過指定閾值的SQL語句。通過分析這些“慢SQL”，可以精準定位數(shù)據庫性能瓶頸，優(yōu)化索引、SQL寫法或表結構。
1.2 基礎知識要求
- MySQL基礎：熟悉配置文件、基本SQL命令
- 權限要求：需要
SUPER或PROCESS權限查看運行狀態(tài) - 運維經驗：了解磁盤空間、日志輪轉等基本概念
二、慢查詢日志的開啟方式
2.1 臨時開啟（當前會話/全局，重啟失效）
-- 查看當前慢查詢狀態(tài) SHOW VARIABLES LIKE '%slow_query%'; SHOW VARIABLES LIKE '%long_query_time%'; -- 開啟慢查詢日志（全局，立即生效，重啟失效） SET GLOBAL slow_query_log = ON; -- 設置慢查詢閾值（秒），建議設為0.1~2秒之間 SET GLOBAL long_query_time = 1; -- 設置日志文件路徑（可選，默認在數(shù)據目錄下） SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow-query.log'; -- 設置未使用索引的SQL也記錄 SET GLOBAL log_queries_not_using_indexes = ON;
2.2 永久開啟（修改配置文件）
Linux/Mac：/etc/my.cnf 或 /etc/mysql/my.cnf
Windows：my.ini
[mysqld] # 開啟慢查詢日志 slow_query_log = 1 # 日志文件路徑 slow_query_log_file = /var/lib/mysql/slow-query.log # 慢查詢閾值（秒） long_query_time = 1 # 記錄未使用索引的查詢 log_queries_not_using_indexes = 1 # 日志輸出格式（FILE或TABLE，默認FILE） # log_output = FILE
配置完成后重啟MySQL服務：
# systemctl sudo systemctl restart mysqld # service sudo service mysql restart
三、參數(shù)解析
| 參數(shù) | 類型 | 默認值 | 說明 | 建議值 |
|---|---|---|---|---|
slow_query_log | Boolean | OFF | 是否開啟慢查詢日志 | ON（生產環(huán)境建議開啟） |
long_query_time | Float | 10.0 | 慢查詢閾值（秒） | 1~2秒（業(yè)務敏感可設為0.5） |
slow_query_log_file | String | hostname-slow.log | 日志文件路徑 | 獨立目錄，便于監(jiān)控 |
log_queries_not_using_indexes | Boolean | OFF | 是否記錄未使用索引的查詢 | ON（找出索引缺失的SQL） |
log_output | Enum | FILE | 日志輸出方式 | FILE 或 TABLE |
min_examined_row_limit | Integer | 0 | 掃描行數(shù)超過此值才記錄 | 1000（過濾小表掃描） |
log_slow_admin_statements | Boolean | OFF | 是否記錄慢管理語句（如OPTIMIZE） | ON（全面監(jiān)控） |
四、慢查詢日志分析工具
4.1 使用mysqldumpslow工具
MySQL自帶日志分析工具，可對慢查詢日志進行聚合統(tǒng)計。
# 基本用法 mysqldumpslow /var/lib/mysql/slow-query.log # 常用參數(shù) mysqldumpslow -s t -t 10 /var/lib/mysql/slow-query.log # 按查詢時間排序，取前10條 mysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log # 按執(zhí)行次數(shù)排序 mysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log # 按返回行數(shù)排序 mysqldumpslow -a /var/lib/mysql/slow-query.log # 不抽象數(shù)字，顯示具體SQL
4.2 使用pt-query-digest（Percona Toolkit）
更強大的第三方分析工具，提供詳細的統(tǒng)計報告。
# 安裝percona-toolkit # Ubuntu/Debian sudo apt-get install percona-toolkit # CentOS/RHEL sudo yum install percona-toolkit # 分析慢查詢日志 pt-query-digest /var/lib/mysql/slow-query.log > slow_report.txt # 分析當前運行的查詢（實時） pt-query-digest --processlist h=localhost,u=root,p=password
五、實際案例：電商訂單慢查詢優(yōu)化
5.1 案例背景
某電商平臺訂單表orders，數(shù)據量約500萬行，業(yè)務反饋訂單列表頁面加載緩慢（超過5秒），需要定位并優(yōu)化。
5.2 步驟一：開啟慢查詢并復現(xiàn)問題
-- 臨時開啟慢查詢記錄閾值0.5秒 SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 0.5; SET GLOBAL log_queries_not_using_indexes = ON; -- 確認日志文件位置 SHOW VARIABLES LIKE 'slow_query_log_file'; -- 結果：/var/lib/mysql/slow-query.log
執(zhí)行慢的訂單查詢SQL：
SELECT
o.order_id,
o.user_id,
o.order_amount,
o.order_status,
o.created_at,
u.user_name,
u.phone
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE o.order_status = 'pending'
AND o.created_at >= '2024-01-01'
AND o.created_at < '2024-02-01'
ORDER BY o.created_at DESC
LIMIT 20;5.3 步驟二：分析慢查詢日志
# 查看慢查詢日志 mysqldumpslow -s t -t 5 /var/lib/mysql/slow-query.log
日志輸出：
Count: 156 Time=3.52s (549s) Lock=0.01s (1.56s) Rows_sent=20.0 (3120), Rows_examined=5234567.0 (816M), root[root]@localhost SELECT o.order_id, o.user_id, o.order_amount, o.order_status, o.created_at, u.user_name, u.phone FROM orders o LEFT JOIN users u ON o.user_id = u.user_id WHERE o.order_status = 'S' AND o.created_at >= 'YYYY-MM-DD' AND o.created_at < 'YYYY-MM-DD' ORDER BY o.created_at DESC LIMIT N
關鍵信息：
- 平均耗時：3.52秒
- 平均掃描行數(shù)：523萬行（幾乎全表掃描）
- 執(zhí)行次數(shù)：156次，總耗時549秒
5.4 步驟三：使用EXPLAIN分析執(zhí)行計劃
EXPLAIN SELECT
o.order_id,
o.user_id,
o.order_amount,
o.order_status,
o.created_at,
u.user_name,
u.phone
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE o.order_status = 'pending'
AND o.created_at >= '2024-01-01'
AND o.created_at < '2024-02-01'
ORDER BY o.created_at DESC
LIMIT 20\GEXPLAIN結果：
| id | select_type | table | type | possible_keys | key | key_len | rows | Extra |
|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | o | ALL | idx_created_at | NULL | NULL | 5,234,567 | Using where; Using filesort |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | 1 | NULL |
問題診斷：
- type=ALL：orders表全表掃描，未使用任何索引
- rows≈523萬：掃描全部數(shù)據行
- Extra包含Using filesort：ORDER BY需要額外排序，無法利用索引
- possible_keys顯示idx_created_at：雖然有created_at索引，但優(yōu)化器未選擇
5.5 步驟四：深入分析索引失效原因
-- 查看orders表現(xiàn)有索引 SHOW INDEX FROM orders;
現(xiàn)有索引：
- PRIMARY KEY (
order_id) - INDEX idx_user_id (
user_id) - INDEX idx_created_at (
created_at) - INDEX idx_status (
order_status)
索引失效分析：
- WHERE條件包含
order_status和created_at兩個字段 - MySQL優(yōu)化器判斷使用任一單列索引都需要回表過濾另一個條件，掃描行數(shù)依然很大
- 最終選擇了全表掃描
5.6 步驟五：制定優(yōu)化方案
方案一：創(chuàng)建聯(lián)合索引（推薦）
-- 創(chuàng)建聯(lián)合索引，將等值查詢字段放前面，范圍查詢放后面 CREATE INDEX idx_status_created ON orders (order_status, created_at); -- 驗證索引效果 EXPLAIN SELECT ...（同原SQL）\G
優(yōu)化后EXPLAIN結果：
| table | type | key | key_len | rows | Extra |
|---|---|---|---|---|---|
| o | range | idx_status_created | 102 | 185,000 | Using where; Using index condition |
| u | eq_ref | PRIMARY | 4 | 1 | NULL |
優(yōu)化效果：
- 掃描行數(shù)從523萬降到18.5萬（減少96.5%）
- 執(zhí)行時間從3.5秒降至0.08秒
方案二：使用覆蓋索引（進一步優(yōu)化）
-- 創(chuàng)建覆蓋索引，避免回表查詢 -- 注意： -- 創(chuàng)建索引需要在線上業(yè)務停止時進行，避免死鎖 -- 覆蓋索引需要包含所有查詢字段 -- 重建索引可能需要很長時間，可能破壞數(shù)據，建議先備份數(shù)據 CREATE INDEX idx_status_created_cover ON orders (order_status, created_at, order_id, user_id, order_amount); -- 但orders表字段較多，覆蓋索引可能過大，需權衡
方案三：SQL語句改寫
-- 使用子查詢先篩選出訂單ID，再關聯(lián)用戶表
SELECT
o.order_id,
o.user_id,
o.order_amount,
o.order_status,
o.created_at,
u.user_name,
u.phone
FROM (
SELECT order_id, user_id, order_amount, order_status, created_at
FROM orders
WHERE order_status = 'pending'
AND created_at >= '2024-01-01'
AND created_at < '2024-02-01'
ORDER BY created_at DESC
LIMIT 20
) o
LEFT JOIN users u ON o.user_id = u.user_id;5.7 步驟六：驗證優(yōu)化效果
再次查看慢查詢日志
mysqldumpslow -s t -t 5 /var/lib/mysql/slow-query.log
優(yōu)化后日志
優(yōu)化成果總結：
| 指標 | 優(yōu)化前 | 優(yōu)化后 | 提升 |
|---|---|---|---|
| 平均耗時 | 3.52秒 | 0.08秒 | 97.7% ↓ |
| 掃描行數(shù) | 523萬 | 18.5萬 | 96.5% ↓ |
| 總耗時/天 | 549秒 | 12.5秒 | 97.7% ↓ |
六、更多實際案例
6.1 案例二：隱式類型轉換導致索引失效
問題SQL：
sql
-- phone字段定義為varchar(20)，但傳入數(shù)字類型 SELECT * FROM users WHERE phone = 13800138000;
EXPLAIN分析：
- type=ALL，key=NULL，rows=全表
原因：MySQL將phone字段自動轉換為數(shù)字類型，導致索引失效
優(yōu)化：
-- 正確寫法，傳入字符串 SELECT * FROM users WHERE phone = '13800138000';
6.2 案例三：函數(shù)操作導致索引失效(和mysql版本有關系)
問題SQL：
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';
優(yōu)化：
SELECT * FROM orders WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16';
6.3 案例四：分頁查詢深度過大
問題SQL：
-- 第10000頁，每頁20條 SELECT * FROM orders ORDER BY order_id LIMIT 200000, 20;
優(yōu)化方案（延遲關聯(lián)）：
SELECT * FROM orders o
INNER JOIN (
SELECT order_id FROM orders
ORDER BY order_id
LIMIT 200000, 20
) t ON o.order_id = t.order_id;七、生產環(huán)境最佳實踐
7.1 慢查詢閾值設置建議
- OLTP系統(tǒng)（高并發(fā)）：
0.5~1秒 - OLAP系統(tǒng)（分析查詢）：
2~5秒 - 核心交易鏈路：
0.1~0.3秒（配合監(jiān)控告警）
7.2 日志管理
- 定期輪轉，避免占滿磁盤
- 使用
logrotate工具管理日志 - 生產環(huán)境建議將
log_output設為TABLE，便于SQL查詢分析
-- 將日志輸出到mysql.slow_log表 SET GLOBAL log_output = 'TABLE'; -- 查詢慢日志表 SELECT * FROM mysql.slow_log WHERE query_time > 2 ORDER BY start_time DESC LIMIT 10;
7.3 監(jiān)控告警
- 接入Prometheus/Grafana，監(jiān)控慢查詢數(shù)量趨勢
- 設置告警：每分鐘慢查詢數(shù) > 10 或 某SQL耗時 > 5秒
7.4 慢查詢分析流程總結
開啟慢查詢 → 收集日志 → 分析TOP慢SQL → EXPLAIN執(zhí)行計劃 → 定位問題
↑ ↓
監(jiān)控告警 ← 驗證效果 ← 上線變更 ← 制定優(yōu)化方案 ← 索引失效/掃描行數(shù)多八、學習建議
- 循序漸進：先從
mysqldumpslow入手，掌握基礎分析后再引入pt-query-digest - 結合EXPLAIN：每個慢SQL都要用
EXPLAIN分析，理解MySQL優(yōu)化器的選擇 - 建立知識庫：記錄常見慢查詢模式及優(yōu)化方案（隱式轉換、函數(shù)操作、排序問題等）
- 預防為主：上線前通過
EXPLAIN審核新SQL，避免慢查詢流入生產 - 定期巡檢：每周分析慢查詢日志，發(fā)現(xiàn)潛在性能隱患
以上就是MySQL慢查詢開啟與優(yōu)化指南的詳細內容，更多關于MySQL慢查詢開啟與優(yōu)化的資料請關注腳本之家其它相關文章！
相關文章
MySQL show命令的用法，在dos下很方便的顯示一些信息。2010-04-04
在數(shù)據庫優(yōu)化中,索引是一項至關重要的技術手段,可以顯著提升查詢性能,下面這篇文章主要介紹了MySQL的聯(lián)合索引范圍條件失效問題解決的相關資料,文中介紹的非常非常詳細,需要的朋友可以參考下2026-04-04
文章介紹了如何修改my.ini文件以解決數(shù)據庫忘記密碼或其他基礎問題,首先,需要停止數(shù)據庫服務,然后創(chuàng)建并編輯my.ini文件,設置數(shù)據庫字符集、緩沖池大小等參數(shù),接著,刪除舊的data文件夾并重新生成,配置my.ini文件時要注意命名規(guī)范2025-01-01
mysql查詢字符串中某個字符串出現(xiàn)的次數(shù)(實例詳解)
這篇文章主要介紹了mysql查詢字符串中某個字符串出現(xiàn)的次數(shù),本文通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-04-04
DBeaver是一款數(shù)據庫管理工具,最重要的是他是一款比較好的開源工具,這篇文章主要介紹了DBeaver導入.sql后綴文件的相關資料,文中通過圖文介紹的非常詳細,需要的朋友可以參考下2025-12-12
MySQL已經成為世界上最受歡迎的數(shù)據庫管理系統(tǒng)之一，無論是用在小型開發(fā)項目上，還是用在構建那較大型的網站，MySQL都用實力證明了自己是一個穩(wěn)定、可靠、快速、可信的系統(tǒng)，足以勝任任何數(shù)據存儲業(yè)務的需要。本文總結了MySQL的使用規(guī)范2020-09-09
這篇文章主要給大家介紹了關于MySQL如何修改賬號的IP限制條件的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧。2017-08-08
這段文章詳細介紹了數(shù)據庫事務的ACID特性,重點闡述了原子性和持久性的實現(xiàn)機制,包括CommitLogging和WAL機制,通過具體案例和代碼演示,深入解析了數(shù)據庫如何確保事務的正確執(zhí)行,感興趣的朋友跟隨小編一起看看吧2026-05-05
下面小編就為大家?guī)硪黄狹ySQL和Lucene索引的對比分析。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2016-09-09
Mysql數(shù)據庫編碼問題 （修改數(shù)據庫，表，字段編碼為utf8)
個人建議,數(shù)據庫字符集盡量使用 utf8(HTML頁面對應的是utf-8),以使你的數(shù)據能很順利的實現(xiàn)遷移2011-10-10