MySQL慢查詢優(yōu)化從30秒到300毫秒的完整過程
更新時間：2025年12月09日 09:09:16 作者：嘻哈baby
文章介紹了如何優(yōu)化一個響應(yīng)時間為30秒的SQL接口,將響應(yīng)時間優(yōu)化到300毫秒的過程,通過開啟慢查詢?nèi)罩?、分析SQL執(zhí)行計劃、添加聯(lián)合索引和優(yōu)化查詢技巧等步驟,解決了慢查詢問題,文章還分享了索引設(shè)計原則、實用工具和遠程數(shù)據(jù)庫排查技巧,需要的朋友可以參考下
最近接手一個老項目，某個列表接口響應(yīng)時間30秒，用戶瘋狂投訴。
排查下來是SQL問題，優(yōu)化后降到300毫秒。記錄一下完整過程。
一、發(fā)現(xiàn)問題
1.1 開啟慢查詢?nèi)罩?/h3>
-- 查看是否開啟
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 開啟慢查詢?nèi)罩?
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超過1秒記錄
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 查看是否開啟 SHOW VARIABLES LIKE 'slow_query%'; SHOW VARIABLES LIKE 'long_query_time'; -- 開啟慢查詢?nèi)罩? SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 超過1秒記錄 SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
1.2 分析慢查詢?nèi)罩?/h3>
# 用mysqldumpslow分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 輸出
Count: 1532 Time=28.35s (43424s) Lock=0.00s (0s) Rows=100.0 (153200)
SELECT * FROM orders WHERE user_id = N AND status = N ORDER BY create_time DESC LIMIT N, N
# 用mysqldumpslow分析 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 輸出 Count: 1532 Time=28.35s (43424s) Lock=0.00s (0s) Rows=100.0 (153200) SELECT * FROM orders WHERE user_id = N AND status = N ORDER BY create_time DESC LIMIT N, N
找到了！這條SQL執(zhí)行了1532次，平均28秒。
二、分析SQL
2.1 問題SQL
SELECT * FROM orders WHERE user_id = 12345 AND status = 1 ORDER BY create_time DESC LIMIT 0, 20;
看起來很簡單，為什么慢？
2.2 EXPLAIN分析
EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status = 1 ORDER BY create_time DESC LIMIT 0, 20;
+----+-------------+--------+------+---------------+------+---------+------+----------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | rows | filtered | Extra | +----+-------------+--------+------+---------------+------+---------+------+----------+-----------------------------+ | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | 5000000 | 0.10 | Using where; Using filesort | +----+-------------+--------+------+---------------+------+---------+------+----------+-----------------------------+
問題暴露了：
type = ALL：全表掃描key = NULL：沒用到索引rows = 5000000：掃描500萬行Using filesort：額外排序
2.3 查看表結(jié)構(gòu)
SHOW CREATE TABLE orders;
CREATE TABLE `orders` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_id` bigint NOT NULL, `status` tinyint NOT NULL DEFAULT '0', `amount` decimal(10,2) NOT NULL, `create_time` datetime NOT NULL, `update_time` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
果然，只有主鍵索引，沒有業(yè)務(wù)索引。
三、優(yōu)化方案
3.1 添加聯(lián)合索引
-- 創(chuàng)建聯(lián)合索引 ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);
再次EXPLAIN：
+----+-------------+--------+------+---------------------+---------------------+---------+-------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------------+---------------------+---------+-------------+------+-------+ | 1 | SIMPLE | orders | ref | idx_user_status_time| idx_user_status_time| 9 | const,const | 156 | NULL | +----+-------------+--------+------+---------------------+---------------------+---------+-------------+------+-------+
完美：
type = ref：使用索引rows = 156：只掃描156行Extra = NULL：不需要額外排序
3.2 為什么這樣設(shè)計索引？
聯(lián)合索引順序：(user_id, status, create_time) 查詢條件：WHERE user_id = ? AND status = ? 排序條件：ORDER BY create_time 索引匹配過程： 1. user_id = 12345 → 定位到用戶的訂單 2. status = 1 → 進一步過濾狀態(tài) 3. create_time → 索引本身有序，無需filesort
聯(lián)合索引設(shè)計原則：
- 等值查詢的列放前面
- 排序的列放最后
- 遵循最左前綴原則
3.3 優(yōu)化效果
優(yōu)化前：28.35秒，掃描500萬行 優(yōu)化后：0.003秒，掃描156行 提升：9000倍+
四、更多優(yōu)化技巧
4.1 避免SELECT *
-- 差：查詢所有字段 SELECT * FROM orders WHERE ... -- 好：只查需要的字段 SELECT id, user_id, amount, create_time FROM orders WHERE ...
好處：
- 減少網(wǎng)絡(luò)傳輸
- 可能用到覆蓋索引
4.2 覆蓋索引
如果查詢的字段都在索引里，不需要回表：
-- 索引：idx_user_status_time (user_id, status, create_time) -- 這個查詢可以用覆蓋索引 SELECT user_id, status, create_time FROM orders WHERE user_id = 12345; -- EXPLAIN顯示 Using index
4.3 避免索引失效
-- ? 對索引列使用函數(shù) SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01'; -- ? 改寫 SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'; -- ? 隱式類型轉(zhuǎn)換 SELECT * FROM orders WHERE user_id = '12345'; -- user_id是bigint -- ? 類型一致 SELECT * FROM orders WHERE user_id = 12345; -- ? LIKE前置通配符 SELECT * FROM orders WHERE order_no LIKE '%ABC'; -- ? LIKE后置通配符（可以用索引） SELECT * FROM orders WHERE order_no LIKE 'ABC%';
4.4 分頁優(yōu)化
-- ? 深分頁很慢 SELECT * FROM orders ORDER BY id LIMIT 1000000, 20; -- 需要掃描100萬行 -- ? 用游標(biāo)分頁 SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20; -- 直接定位，很快
五、索引設(shè)計原則
5.1 什么時候建索引？
| 場景 | 是否建索引 |
|---|---|
| WHERE條件頻繁查詢的列 | ? 是 |
| ORDER BY排序的列 | ? 是 |
| JOIN關(guān)聯(lián)的列 | ? 是 |
| 區(qū)分度低的列（如性別） | ? 否 |
| 頻繁更新的列 | ?? 權(quán)衡 |
5.2 聯(lián)合索引順序
原則： 1. 區(qū)分度高的列放前面 2. 等值查詢的列放前面 3. 排序列放最后
六、實用工具
6.1 慢查詢分析
# mysqldumpslow mysqldumpslow -s t -t 10 slow.log # 按時間排序，前10條 # pt-query-digest（推薦） pt-query-digest slow.log > report.txt
6.2 EXPLAIN詳解
type（重要，從好到差）： - system/const：常量查詢 - eq_ref：主鍵/唯一索引 - ref：普通索引 - range：范圍掃描 - index：索引全掃描 - ALL：全表掃描 ? Extra（重要）： - Using index：覆蓋索引 ? - Using where：需要回表過濾 - Using filesort：額外排序 ?? - Using temporary：臨時表 ??
七、遠程數(shù)據(jù)庫排查技巧
有時候問題數(shù)據(jù)庫在測試環(huán)境，本地連不上怎么辦？
我的做法是用組網(wǎng)工具把本地和測試服務(wù)器連起來。之前用VPN，經(jīng)常斷還慢?，F(xiàn)在用星空組網(wǎng)，本地直接連測試環(huán)境的MySQL：
# 組網(wǎng)后直接用虛擬IP連接 mysql -h 192.168.188.10 -u root -p
EXPLAIN、慢查詢分析都能直接在本地跑，比登服務(wù)器方便多了。
總結(jié)
SQL優(yōu)化核心步驟：
1. 開啟慢查詢?nèi)罩?→ 發(fā)現(xiàn)問題SQL 2. EXPLAIN分析 → 定位問題原因 3. 添加/優(yōu)化索引 → 解決問題 4. 再次EXPLAIN → 驗證效果 5. 線上執(zhí)行 → 監(jiān)控觀察
記住幾個原則：
- 避免全表掃描
- 利用覆蓋索引
- 注意索引失效場景
- 聯(lián)合索引最左前綴
以上就是MySQL慢查詢優(yōu)化從30秒到300毫秒的完整過程的詳細內(nèi)容，更多關(guān)于MySQL慢查詢優(yōu)化30秒到300毫秒的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
這篇文章主要介紹了 MySQL中count函數(shù)的正確使用方法，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下。2020-11-11
在數(shù)據(jù)庫系統(tǒng)中，同時有多個用戶或進程訪問數(shù)據(jù)是常見的情況，為了確保數(shù)據(jù)的完整性和一致性，數(shù)據(jù)庫管理系統(tǒng)引入了鎖機制，本文將深入探討?MySQL?鎖機制，幫助您理解鎖的分類、實現(xiàn)方式以及使用場景和優(yōu)化策略,需要的朋友可以參考下2023-05-05
這篇文章主要介紹了MySQL刪除了記錄不生效的原因排查，幫助大家解決MySQL刪除語句不生效的問題，感興趣的朋友可以了解下2020-11-11
本文主要介紹了MySQL阻塞與死鎖的解決,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-09-09
這篇文章主要介紹了詳解MySQL的sql_mode查詢與設(shè)置，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12
Centos 7 安裝mysql5.7.24二進制 版本的方法及解決辦法
這篇文章主要介紹了Centos 7 安裝mysql5.7.24二進制 版本的方法 及遇到問題解決辦法,本文給大家介紹的非常詳細，具有一定的參考借鑒價值，需要的朋友可以參考下2018-11-11
當(dāng)你的數(shù)據(jù)中有重復(fù)記錄時,如何快速找到每個分組的最新一條,一個優(yōu)雅的SQL查詢就能解決,下面小編就來和大家詳細講解一下SQL高效的去重技巧吧2025-07-07
MySQL無法啟動、無法停止解決方法(安全設(shè)置后容易出現(xiàn))
最近在Win2003上的MySQL出現(xiàn)過多次正常運行時無法連接數(shù)據(jù)庫故障，根本原因就是因為安全設(shè)置以后容易出現(xiàn)的問題，其實很簡單的解決2012-03-03
這篇文章主要為大家詳細介紹了mysql 5.7 zip archive版本安裝教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-09-09
數(shù)據(jù)庫的存儲引擎都是采用B樹或者B+樹來實現(xiàn)索引的存儲,總體來說，我認為技術(shù)方案的選型，更多的要根據(jù)具體的業(yè)務(wù)場景來決定，并不一定是說B+樹就是最好的選擇，就像MongoDB里面采用B樹結(jié)構(gòu),本文介紹我對MySQL索引結(jié)構(gòu)采用B+樹的理解2022-06-06