問(wèn)題背景

MySQL 慢查詢是影響業(yè)務(wù)響應(yīng)速度的最常見(jiàn)根因。業(yè)務(wù)高峰期一次看似簡(jiǎn)單的 SELECT 查詢，可能拖慢整個(gè)系統(tǒng)——前端頁(yè)面加載轉(zhuǎn)圈、API 超時(shí)、后臺(tái)任務(wù)堆積、數(shù)據(jù)庫(kù)連接池耗盡。更要命的是，慢查詢往往不是單一問(wèn)題，而是索引缺失、表結(jié)構(gòu)不合理、SQL 寫(xiě)法糟糕、統(tǒng)計(jì)信息過(guò)時(shí)、硬件資源不足等多重因素疊加的結(jié)果。初期只是偶爾卡頓，后期隨著數(shù)據(jù)量增長(zhǎng)演變成全面崩潰。

這篇文章面向初中級(jí) MySQL 運(yùn)維和后端開(kāi)發(fā)工程師，從一個(gè)典型的"頁(yè)面加載慢"投訴出發(fā)，系統(tǒng)講解 MySQL 慢查詢的完整排查流程：先開(kāi)啟慢查詢?nèi)罩咀コ霈F(xiàn)存的慢查詢，再用 EXPLAIN 分析執(zhí)行計(jì)劃，找到索引問(wèn)題和全表掃描，再結(jié)合 MySQL 8.0 的新特性做優(yōu)化，最后給出索引設(shè)計(jì)規(guī)范和 SQL 編寫(xiě)規(guī)范。所有內(nèi)容基于 MySQL 5.7 和 MySQL 8.0 兩個(gè)主流版本，部分特性差異會(huì)做說(shuō)明。

適用場(chǎng)景

業(yè)務(wù)接口響應(yīng)時(shí)間突然變長(zhǎng)，懷疑是數(shù)據(jù)庫(kù)查詢慢

監(jiān)控顯示 QPS 正常但 DB CPU 使用率持續(xù)偏高

慢查詢?nèi)罩疚募焖僭鲩L(zhǎng)，磁盤空間告警

需要優(yōu)化歷史遺留 SQL，消除全表掃描

新功能上線前做 SQL 審核，發(fā)現(xiàn)潛在性能問(wèn)題

主從復(fù)制延遲持續(xù)偏高，懷疑是慢查詢阻塞了復(fù)制線程

分庫(kù)分表前評(píng)估哪些大表需要優(yōu)先拆分

MySQL 查詢慢的常見(jiàn)根因

在動(dòng)手排查之前，先理解可能導(dǎo)致查詢慢的原因有哪些：

一、索引層面：

缺少 WHERE 條件列上的索引，導(dǎo)致全表掃描

索引失效：函數(shù)/運(yùn)算在索引列上、類型轉(zhuǎn)換、LIKE 前綴通配符

索引選擇不當(dāng)：MySQL 選錯(cuò)了索引（索引統(tǒng)計(jì)信息不準(zhǔn)）

聯(lián)合索引順序不對(duì)，最左前綴原則不滿足

二、SQL 寫(xiě)法層面：

SELECT * 讀取全部列，沒(méi)有利用覆蓋索引

多表 JOIN 時(shí)沒(méi)有給小表加驅(qū)動(dòng)限制

子查詢嵌套過(guò)深或用 IN (SELECT ...) 導(dǎo)致全表

OR 條件導(dǎo)致索引失效

分頁(yè)查詢 OFFSET 過(guò)大（深度分頁(yè)問(wèn)題）

缺少 LIMIT 限制返回行數(shù)

三、表結(jié)構(gòu)層面：

表過(guò)大（單表超過(guò)千萬(wàn)行且無(wú)分區(qū)）

字段類型選擇不當(dāng)（VARCHAR(255) 存大文本、TEXT 字段參與排序）

沒(méi)有合理使用分區(qū)表

字段冗余設(shè)計(jì)不合理導(dǎo)致更新阻塞

四、數(shù)據(jù)庫(kù)配置層面：

Buffer Pool 設(shè)置太小，熱數(shù)據(jù)無(wú)法全部緩存

臟頁(yè)刷新策略不合理導(dǎo)致磁盤 IO 突增

并發(fā)連接數(shù)設(shè)置過(guò)高導(dǎo)致上下文切換

臨時(shí)表和排序緩沖區(qū)不足

五、硬件和系統(tǒng)層面：

磁盤 IO 性能不足（SSD vs HDD）

內(nèi)存不足導(dǎo)致 swap

CPU 核心數(shù)不足

網(wǎng)絡(luò)延遲（跨機(jī)房部署）

第一步：開(kāi)啟慢查詢?nèi)罩?/p>

慢查詢?nèi)罩臼桥挪榈钠瘘c(diǎn)。如果還沒(méi)有開(kāi)啟，先開(kāi)啟它。

1.1 確認(rèn)慢查詢?nèi)罩臼欠耖_(kāi)啟

-- 查看慢查詢相關(guān)配置
SHOWVARIABLESLIKE'slow_query%';
SHOWVARIABLESLIKE'long_query_time';
SHOWVARIABLESLIKE'log_output';

-- 輸出示例：
-- slow_query_log        | ON
-- slow_query_log_file     | /var/lib/mysql/mysql-slow.log
-- long_query_time       | 2.000000  （超過(guò) 2 秒的查詢才記錄）
-- log_output          | FILE

long_query_time默認(rèn)是 10 秒，對(duì)生產(chǎn)環(huán)境來(lái)說(shuō)太寬松了。建議設(shè)置為 1 秒，有能力的話設(shè)置為 0.5 秒或更低，但要注意開(kāi)啟后日志量會(huì)增加。

1.2 臨時(shí)開(kāi)啟慢查詢?nèi)罩荆ú恢貑ⅲ?/p>

-- 將超過(guò) 1 秒的查詢記錄到慢查詢?nèi)罩?SETGLOBALslow_query_log ='ON';
SETGLOBALlong_query_time =1;
SETGLOBALslow_query_log_file ='/var/lib/mysql/mysql-slow.log';

-- 同時(shí)開(kāi)啟記錄未使用索引的查詢（注意：MySQL 8.0 中這個(gè)參數(shù)被移除）
-- MySQL 5.7 中可以開(kāi)啟
SETGLOBALlog_queries_not_using_indexes ='ON';

-- 確認(rèn)修改生效
SHOWVARIABLESLIKE'slow_query%';
SHOWVARIABLESLIKE'long_query_time';

注意：以上修改在 MySQL 重啟后會(huì)丟失。如果要永久生效，需要修改配置文件。

1.3 永久開(kāi)啟慢查詢?nèi)罩?/p>

# 編輯 MySQL 配置文件（根據(jù)安裝方式不同，配置文件位置不同）
# RPM 安裝：/etc/my.cnf
# Docker 安裝：掛載的配置文件
# 使用 mysqld --verbose --help | grep -A 10 'Default options' 查找

sudo vi /etc/my.cnf

# 在 [mysqld] 段添加或修改以下內(nèi)容：
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_output = FILE

# 保存后重啟 MySQL（需要確認(rèn)影響范圍）
sudo systemctl restart mysqld

風(fēng)險(xiǎn)提醒：重啟 MySQL 會(huì)斷開(kāi)所有現(xiàn)有連接，生產(chǎn)環(huán)境需要：

確認(rèn)業(yè)務(wù)是否有重連機(jī)制

確認(rèn)是否有長(zhǎng)事務(wù)未提交（重啟前最好確認(rèn)）

通知相關(guān)業(yè)務(wù)方

準(zhǔn)備好回滾方案（將原配置文件備份）

1.4 用 pt-query-digest 分析慢查詢?nèi)罩?/p>

pt-query-digest是 Percona Toolkit 中的工具，比直接查看日志文件效率高得多。它能聚合相似查詢、排序出最慢的查詢、展示查詢頻率和響應(yīng)時(shí)間分布。

# 如果沒(méi)有安裝 Percona Toolkit，先安裝
# CentOS/RHEL:
sudo yum install percona-toolkit -y
# Debian/Ubuntu:
sudo apt-get install percona-toolkit -y

# 分析慢查詢?nèi)罩?pt-query-digest /var/lib/mysql/mysql-slow.log

# 輸出結(jié)構(gòu)：
#  1) 查詢的指紋（將參數(shù)泛化后的標(biāo)準(zhǔn)化 SQL）
#  2) 查詢時(shí)間統(tǒng)計(jì)：Response time、Calls、R/M/V
#  3) SQL 文本本身
#  4) 執(zhí)行計(jì)劃（如果有 EXPLAIN 輸出）

# 只看前 10 個(gè)最慢的查詢
pt-query-digest --limit10 /var/lib/mysql/mysql-slow.log

# 只看某個(gè)時(shí)間范圍后的查詢
pt-query-digest --since'2026-04-29 1000'/var/lib/mysql/mysql-slow.log

# 排除某個(gè)用戶查詢
pt-query-digest --filter'$event->{user} =~ /^(?!repl_user)/'/var/lib/mysql/mysql-slow.log

pt-query-digest輸出的第一部分是最關(guān)鍵的——按響應(yīng)時(shí)間排序的查詢列表。第一條就是當(dāng)前最需要優(yōu)化的查詢。

第二步：用 EXPLAIN 分析執(zhí)行計(jì)劃

找到慢查詢后，用 EXPLAIN 分析它的執(zhí)行計(jì)劃。執(zhí)行計(jì)劃是 MySQL 優(yōu)化器的決策結(jié)果，告訴你 MySQL 準(zhǔn)備怎么執(zhí)行這條查詢。

2.1 基本 EXPLAIN 用法

-- 在 MySQL 客戶端中執(zhí)行
EXPLAIN;

-- MySQL 8.0 支持 EXPLAIN ANALYZE，能實(shí)際執(zhí)行并測(cè)量真實(shí)運(yùn)行時(shí)間和行數(shù)
EXPLAINANALYZE;

-- 完整輸出包含 12 個(gè)字段
-- id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra

2.2 逐字段解讀執(zhí)行計(jì)劃

type 字段（最重要的字段之一）：

type 字段描述了 MySQL 如何找到數(shù)據(jù)行，從好到壞排序：

如果 type 是 ALL，說(shuō)明這條查詢?cè)谧呷頀呙瑁锹樵兊淖畛Ｒ?jiàn)根因。

rows 字段：MySQL 優(yōu)化器預(yù)估需要掃描的行數(shù)。這個(gè)數(shù)字越大，性能通常越差。但注意這是預(yù)估，不一定準(zhǔn)確（統(tǒng)計(jì)信息過(guò)期時(shí)會(huì)不準(zhǔn)）。

Extra 字段：包含大量?jī)?yōu)化決策信息，需要重點(diǎn)關(guān)注的內(nèi)容：

Using filesort  -- 使用了文件排序（外部排序），非常慢，需要優(yōu)化
Using temporary  -- 創(chuàng)建了臨時(shí)表，很慢，需要優(yōu)化
Using index   -- 利用了覆蓋索引，性能好
Using index condition -- 利用了索引下推
Usingwhere   -- 在存儲(chǔ)引擎層過(guò)濾數(shù)據(jù)

常見(jiàn)有問(wèn)題的 Extra 組合：

Using filesort：ORDER BY 的列沒(méi)有索引，或索引順序和 ORDER BY 不一致

Using temporary：DISTINCT、GROUP BY、UNION 等操作需要臨時(shí)表

Using filesort + Using temporary：同時(shí)出現(xiàn)，說(shuō)明既需要排序又需要臨時(shí)表，最糟糕的情況

2.3 實(shí)際案例分析

-- 原始慢查詢
EXPLAINSELECT*FROMordersWHEREuser_id =12345ORDERBYcreated_atDESCLIMIT10;

-- 輸出：
-- id: 1
-- select_type: SIMPLE
-- table: orders
-- type: ALL       <-- 全表掃描！這是問(wèn)題
-- possible_keys: NULL ? ?<-- 沒(méi)有可用索引
-- key: NULL
-- rows: 1523872 ? ? ? ? <-- 掃描了 150 萬(wàn)行
-- filtered: 100.00
-- Extra: Using where; Using filesort ?<-- 還用了文件排序

-- 優(yōu)化后（添加索引后）
EXPLAIN?SELECT?*?FROM?orders?WHERE?user_id =?12345?ORDER?BY?created_at?DESC?LIMIT?10;

-- 輸出：
-- id: 1
-- select_type: SIMPLE
-- table: orders
-- type: ref ? ? ? ? ? ? ?<-- 使用了索引查找
-- possible_keys: idx_user_id_created ?<-- 可用索引
-- key: idx_user_id_created ?<-- 實(shí)際使用的索引
-- key_len: 4 ? ? ? ? ? ? <-- 索引長(zhǎng)度
-- ref: const
-- rows: 10 ? ? ? ? ? ? ? <-- 只掃描了 10 行
-- filtered: 100.00
-- Extra: Backward index scan; Using where ?<-- 倒序掃描索引，Using filesort 消失

2.4 聯(lián)合索引的索引列順序判斷

-- 如果查詢條件是 WHERE a = 1 AND b = 2 AND c > 3 ORDER BY d
-- 索引順序應(yīng)該是：(a, b, c, d)
-- 因?yàn)樽钭笄熬Y原則要求索引列從左到右依次使用

-- 驗(yàn)證索引順序是否正確
EXPLAINSELECT*FROMordersWHEREa =1ANDb =2ANDc >3ORDERBYd;

-- 如果 Extra 中仍然出現(xiàn) Using filesort，說(shuō)明索引順序不對(duì)
-- 需要?jiǎng)?chuàng)建索引：(a, b, c, d)

第三步：識(shí)別常見(jiàn)索引失效場(chǎng)景

慢查詢的另一個(gè)重災(zāi)區(qū)是索引明明存在，但因?qū)懛▎?wèn)題導(dǎo)致索引失效，優(yōu)化器選擇全表掃描。以下是常見(jiàn)場(chǎng)景。

3.1 在索引列上使用函數(shù)或運(yùn)算

-- 索引失效示例：WHERE YEAR(created_at) = 2026
EXPLAINSELECT*FROMordersWHEREYEAR(created_at) =2026;
-- type: ALL (全表掃描)，因?yàn)?MySQL 必須對(duì)每一行計(jì)算 YEAR() 才能判斷

-- 正確寫(xiě)法：使用范圍查詢
EXPLAINSELECT*FROMordersWHEREcreated_at >='2026-01-01'ANDcreated_at  100
EXPLAINSELECT*FROMproductsWHEREprice *1.1>100;
-- 同樣全表掃描

-- 正確寫(xiě)法：WHERE price > 100 / 1.1
EXPLAINSELECT*FROMproductsWHEREprice >100/1.1;

3.2 LIKE 前綴通配符

-- 索引失效：LIKE 以通配符開(kāi)頭
EXPLAINSELECT*FROMusersWHEREnameLIKE'%wang%';
-- type: ALL，全表掃描

-- 正確寫(xiě)法：如果必須模糊匹配，考慮全文索引（FULLTEXT）
EXPLAINSELECT*FROMusersWHEREnameLIKE'wang%';
-- type: range，使用索引

-- 如果業(yè)務(wù)確實(shí)需要 %wang% 這種匹配，考慮：
-- 1. 全文索引：ALTER TABLE users ADD FULLTEXT(name);
-- 2. 分詞 + 倒排索引
-- 3. Elasticsearch

3.3 隱式類型轉(zhuǎn)換

-- phone 是 VARCHAR(20) 類型的列，存儲(chǔ)了字符串 "13800138000"
-- 如果查詢時(shí)傳入數(shù)字，索引失效

EXPLAINSELECT*FROMusersWHEREphone =13800138000;
-- phone 是字符串類型，但傳入的是數(shù)字，MySQL 會(huì)將字符串轉(zhuǎn)換為數(shù)字再比較
-- 導(dǎo)致全表掃描

-- 正確寫(xiě)法：使用字符串字面量
EXPLAINSELECT*FROMusersWHEREphone ='13800138000';

3.4 OR 條件導(dǎo)致索引失效

-- OR 條件：只有當(dāng)所有條件都有索引時(shí)才會(huì)使用索引
EXPLAINSELECT*FROMusersWHEREname='zhangsan'ORemail ='zhangsan@example.com';
-- 如果 name 有索引、email 沒(méi)有索引，MySQL 仍然選擇全表掃描

-- 優(yōu)化寫(xiě)法：拆分成 UNION
EXPLAINSELECT*FROMusersWHEREname='zhangsan'
UNION
SELECT*FROMusersWHEREemail ='zhangsan@example.com';

-- 注意：如果 email 列的選擇性很差（很多重復(fù)值），即使有索引也不該用

3.5 確認(rèn)索引是否被使用

-- 查看某條查詢實(shí)際使用的索引
EXPLAINSELECT*FROMordersWHEREstatus='completed'ANDcreated_at >'2026-04-01';

-- possible_keys: 顯示 MySQL 認(rèn)為可用的索引
-- key: 顯示實(shí)際使用的索引

-- 如果 possible_keys 有值但 key 是 NULL：
-- 可能是 MySQL 認(rèn)為全表掃描更快（數(shù)據(jù)量小、統(tǒng)計(jì)信息不準(zhǔn)）
-- 或者索引列的選擇性太差（大量重復(fù)值）

-- 查看索引的選擇性（基數(shù)）
SHOWINDEXFROMorders;
-- Cardinality 列顯示索引列的唯一值數(shù)量
-- 如果 Cardinality 很小（接近行數(shù)），說(shuō)明選擇性差，不適合建索引

第四步：深度分頁(yè)問(wèn)題

深度分頁(yè)（OFFSET 很大）是慢查詢的重災(zāi)區(qū)。比如LIMIT 100000, 10這種查詢，MySQL 需要先掃描前 100010 行，然后丟棄前 100000 行，返回最后 10 行。OFFSET 越大，浪費(fèi)越多。

4.1 識(shí)別深度分頁(yè)查詢

-- 這種查詢就是典型的深度分頁(yè)
SELECT*FROMordersWHEREstatus='completed'ORDERBYidDESCLIMIT100000,10;

-- pt-query-digest 輸出的 Query 特征：
-- FROM orders WHERE status = 'completed' ORDER BY id DESC LIMIT ... OFFSET ...
-- Rows in set after limit: 10 (但實(shí)際掃描了 100010 行)

4.2 優(yōu)化方案一：使用游標(biāo)分頁(yè)

利用主鍵 ID 的單調(diào)性，避免 OFFSET：

-- 第一頁(yè)
SELECT*FROMordersWHEREstatus='completed'ORDERBYidDESCLIMIT10;
-- 記住最后一行的 id，比如是 987654

-- 第二頁(yè)：利用上一頁(yè)最后的 ID 作為起點(diǎn)
SELECT*FROMorders
WHEREstatus='completed'ANDid

	

	注意：游標(biāo)分頁(yè)只適合有明確排序場(chǎng)景，且排序字段最好是單調(diào)遞增/遞減的（時(shí)間倒序、ID 倒序）。如果需要隨機(jī)跳頁(yè)，這種方式不適用。

	4.3 優(yōu)化方案二：延遲關(guān)聯(lián)

	減少回表次數(shù)：

	
-- 原始深度分頁(yè)（慢）
SELECT*FROMordersWHEREstatus='completed'ORDERBYidDESCLIMIT100000,10;

-- 優(yōu)化：先通過(guò)索引只查主鍵，再關(guān)聯(lián)回原表取其他列
SELECTo.*FROMorders o
INNERJOIN(
 SELECTidFROMordersWHEREstatus='completed'ORDERBYidDESCLIMIT100000,10
)AStONo.id = t.id;


	

	4.4 優(yōu)化方案三：記錄總頁(yè)數(shù)上限

	
-- 對(duì)搜索結(jié)果設(shè)置最大頁(yè)數(shù)限制，超過(guò)后返回空或提示用戶使用更精確的搜索條件
-- 例如：限制最大 OFFSET 為 10000
SELECT*FROMorders
WHEREstatus='completed'ANDcreated_at >'2026-04-01'
ORDERBYidDESC
LIMIT10OFFSET100000;

-- 如果超過(guò) 10000 頁(yè)，返回友好提示或建議用戶使用更精確的搜索條件


	

	第五步：多表 JOIN 優(yōu)化

	多表 JOIN 是慢查詢的另一大來(lái)源。JOIN 的執(zhí)行順序、驅(qū)動(dòng)表選擇、索引條件都會(huì)極大影響性能。

	5.1 查看 JOIN 執(zhí)行計(jì)劃

	
EXPLAINSELECTu.name, o.order_no, o.amount
FROMusersu
INNERJOINorders oONu.id = o.user_id
WHEREu.status ='active'ANDo.created_at >'2026-04-01';

-- id: 執(zhí)行順序，id 相同從上到下執(zhí)行，id 越大優(yōu)先級(jí)越高
-- select_type: SIMPLE（簡(jiǎn)單查詢）、DERIVED（派生表/子查詢）、UNION 等
-- table: 查詢涉及的表
-- type: 關(guān)聯(lián)類型
-- key: 實(shí)際使用的索引

-- 重點(diǎn)關(guān)注：
-- 1. 驅(qū)動(dòng)表是誰(shuí)（通常是第一張表）
-- 2. 是否有全表掃描（type=ALL）
-- 3. JOIN 的 key 是否正確關(guān)聯(lián)


	

	5.2 JOIN 優(yōu)化原則

	原則一：小表驅(qū)動(dòng)大表。

	MySQL 優(yōu)化器通常會(huì)自動(dòng)選擇小表作為驅(qū)動(dòng)表（數(shù)據(jù)量小的表），但不是絕對(duì)的。可以用 STRAIGHT_JOIN 強(qiáng)制指定驅(qū)動(dòng)表：

	
-- 強(qiáng)制使用 users 作為驅(qū)動(dòng)表（需要根據(jù)實(shí)際數(shù)據(jù)量判斷）
SELECTSTRAIGHT_JOINu.name, o.order_no, o.amount
FROMusersu
INNERJOINorders oONu.id = o.user_id
WHEREu.status ='active'ANDo.created_at >'2026-04-01';


	

	原則二：被驅(qū)動(dòng)表的關(guān)聯(lián)列必須有索引。

	
-- orders.user_id 如果沒(méi)有索引，每次關(guān)聯(lián)都是全表掃描
-- 先確認(rèn)索引是否存在
SHOWINDEXFROMorders;

-- 如果不存在，創(chuàng)建索引
ALTERTABLEordersADDINDEXidx_user_id (user_id);


	

	5.3 常見(jiàn) JOIN 問(wèn)題分析

	
-- 問(wèn)題一：多表 JOIN 后出現(xiàn) Using filesort
EXPLAINSELECTu.name, o.order_no
FROMusersu
INNERJOINorders oONu.id = o.user_id
WHEREu.status ='active'
ORDERBYo.created_atDESC;
-- 如果 o.created_at 上沒(méi)有索引，排序會(huì)用到文件排序

-- 解決方案：添加聯(lián)合索引
ALTERTABLEordersADDINDEXidx_user_created (user_id, created_at);

-- 問(wèn)題二：子查詢導(dǎo)致的性能問(wèn)題
EXPLAINSELECT*FROMorders
WHEREuser_idIN(SELECTidFROMusersWHEREstatus='inactive');
-- IN (SELECT ...) 在 MySQL 5.7 及之前性能很差，會(huì)先執(zhí)行外層再執(zhí)行內(nèi)層

-- 優(yōu)化：改寫(xiě)為 JOIN
EXPLAINSELECTo.*FROMorders o
INNERJOINusersuONo.user_id = u.id
WHEREu.status ='inactive';

-- 問(wèn)題三：LEFT JOIN 右表?xiàng)l件放錯(cuò)位置
SELECTu.name, o.order_no
FROMusersu
LEFTJOINorders oONu.id = o.user_idANDo.status ='completed'-- 條件放在 ON
WHEREo.idISNULL; -- 找沒(méi)有訂單的用戶

-- 這樣做是對(duì)的，但很多人會(huì)把這個(gè)條件放到 WHERE 里：
SELECTu.name, o.order_no
FROMusersu
LEFTJOINorders oONu.id = o.user_id
WHEREo.status ='completed'; -- 錯(cuò)誤：LEFT JOIN 條件放 WHERE 會(huì)把 LEFT 變成 INNER


	

	第六步：查看 MySQL 實(shí)時(shí)狀態(tài)和進(jìn)程

	有時(shí)候慢查詢不是單條 SQL 的問(wèn)題，而是大量并發(fā)查詢把數(shù)據(jù)庫(kù)打滿了。需要從全局視角看數(shù)據(jù)庫(kù)負(fù)載。

	6.1 查看當(dāng)前連接數(shù)和活躍查詢

	
-- 查看當(dāng)前連接狀態(tài)
SHOWSTATUSLIKE'Threads%';
-- Threads_connected: 當(dāng)前連接數(shù)
-- Threads_running: 當(dāng)前正在執(zhí)行的查詢數(shù)（不包括等待中的）

-- 查看最大連接數(shù)配置
SHOWVARIABLESLIKE'max_connections';
-- 默認(rèn) 151，建議根據(jù)實(shí)際并發(fā)需求調(diào)整

-- 查看當(dāng)前所有連接
SHOWPROCESSLIST;
-- 或
SHOWFULLPROCESSLIST;

-- 輸出各列含義：
-- Id: 連接 ID
-- User: 用戶名
-- Host: 客戶端 IP
-- db: 連接的數(shù)據(jù)庫(kù)
-- Command: 當(dāng)前命令（Sleep/Query/Connect 等）
-- Time: 執(zhí)行時(shí)間（秒）
-- State: 狀態(tài)
-- Info: SQL 內(nèi)容（只顯示前 100 字符）


	

	重點(diǎn)關(guān)注：Command=Query 且 Time 很長(zhǎng)（比如超過(guò) 60 秒）的查詢，說(shuō)明有慢查詢正在執(zhí)行。

	6.2 查看數(shù)據(jù)庫(kù)鎖等待

	鎖等待是導(dǎo)致查詢卡住的常見(jiàn)原因，特別是有大事務(wù)或長(zhǎng)事務(wù)時(shí)。

	
-- 查看當(dāng)前鎖等待信息（MySQL 8.0）
SELECT*FROMperformance_schema.data_lock_waits;

-- 查看當(dāng)前的事務(wù)
SELECT*FROMinformation_schema.INNODB_TRX;

-- 輸出各列含義：
-- trx_id: 事務(wù) ID
-- trx_state: 事務(wù)狀態(tài)（RUNNING/LOCK WAIT/ROLLING BACK）
-- trx_started: 事務(wù)開(kāi)始時(shí)間
-- trx_rows_locked: 鎖住的行數(shù)
-- trx_mysql_thread_id: 對(duì)應(yīng)的 MySQL 連接 ID
-- trx_query: 正在執(zhí)行的 SQL

-- 查看具體的鎖信息
SHOWENGINEINNODBSTATUS;
-- 輸出信息量很大，包含：
-- LATEST DETECTED DEADLOCK: 最近檢測(cè)到的死鎖
-- TRANSACTIONS: 當(dāng)前所有事務(wù)和鎖的狀態(tài)
-- LOCK WAIT: 等待中的鎖
-- ROW OPERATIONS: 當(dāng)前的行操作統(tǒng)計(jì)


	

	發(fā)現(xiàn)長(zhǎng)時(shí)間 LOCK WAIT 的事務(wù)：

	
-- 查看超過(guò) 60 秒仍未完成的事務(wù)
SELECT
  trx_id,
  trx_state,
  trx_started,
 TIMESTAMPDIFF(SECOND, trx_started,NOW())ASduration_sec,
  trx_rows_locked,
  trx_query,
  ps.userASmysql_user,
  host_info
FROMinformation_schema.INNODB_TRX
JOINperformance_schema.threadsASpsONps.processlist_id = trx_mysql_thread_id
WHERETIMESTAMPDIFF(SECOND, trx_started,NOW()) >60
ORDERBYduration_secDESC;


	

	風(fēng)險(xiǎn)提醒：KILL 正在執(zhí)行的事務(wù)是破壞性操作，可能導(dǎo)致事務(wù)回滾。如果要 KILL，先確認(rèn)：

	該事務(wù)是否是正常的長(zhǎng)事務(wù)（比如數(shù)據(jù)導(dǎo)入）

	是否已經(jīng)提交（已提交的話 KILL 沒(méi)有意義）

	回滾本身也需要時(shí)間（大事務(wù)回滾可能更慢）

	6.3 查看數(shù)據(jù)庫(kù)整體性能指標(biāo)

	
-- 查看 Buffer Pool 命中率
SHOWSTATUSLIKE'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests: 讀請(qǐng)求數(shù)
-- Innodb_buffer_pool_reads: 從磁盤讀取的次數(shù)（未命中）
-- 命中率 = 1 - (reads / read_requests)，應(yīng)該 > 95%

-- 計(jì)算公式（在 MySQL 客戶端執(zhí)行）
SELECT
  variable_valueASread_requests,
  variable_valueASdisk_reads
FROMperformance_schema.global_status
WHEREvariable_nameIN('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads');

-- 查看 QPS 和 TPS
SHOWSTATUSLIKE'Questions';
SHOWSTATUSLIKE'Uptime';

-- 查看慢查詢數(shù)量
SHOWGLOBALSTATUSLIKE'Slow_queries';

-- 查看已經(jīng)創(chuàng)建臨時(shí)表和磁盤臨時(shí)表的數(shù)量
SHOWGLOBALSTATUSLIKE'Created_tmp%';
-- Created_tmp_disk_tables 不應(yīng)該太高，否則說(shuō)明排序緩沖區(qū)不足


	

	第七步：索引設(shè)計(jì)與優(yōu)化實(shí)戰(zhàn)

	7.1 如何判斷一個(gè)查詢是否需要索引

	原則：在 WHERE 條件、JOIN ON 條件、ORDER BY、GROUP BY 中出現(xiàn)的列，考慮建索引。

	
-- 高頻查詢：WHERE status = 'completed' AND created_at > '2026-04-01'
-- 應(yīng)該建聯(lián)合索引：(status, created_at)

-- 低頻查詢：如果某查詢每天只執(zhí)行幾次，即使慢也不值得花太多精力優(yōu)化
-- 優(yōu)先優(yōu)化高頻查詢


	

	7.2 聯(lián)合索引的最左前綴原則

	聯(lián)合索引 (a, b, c) 能支持的查詢場(chǎng)景：

	
 WHERE a = 1         -- 使用索引 (a)
 WHERE a = 1 AND b = 2    -- 使用索引 (a, b)
 WHERE a = 1 AND b = 2 AND c = 3 -- 使用完整索引 (a, b, c)
 WHERE a IN (1, 2)      -- 使用索引 (a)
 WHERE a = 1 AND c = 3    -- 使用索引 (a)，但 c 部分無(wú)法利用索引
 WHERE b = 2         -- 不使用索引
 WHERE c = 3         -- 不使用索引
 WHERE b = 2 AND c = 3    -- 不使用索引


	

	ORDER BY 同樣遵循最左前綴原則：

	
 WHERE a = 1 ORDER BY b    -- 使用索引 (a, b)，有序
 WHERE a = 1 ORDER BY b, c  -- 使用索引 (a, b, c)，有序
 WHERE a = 1 ORDER BY c    -- 不使用索引排序，會(huì)產(chǎn)生 filesort


	

	7.3 覆蓋索引

	覆蓋索引是指一個(gè)索引包含了查詢需要的所有列，這樣 MySQL 不需要回表（訪問(wèn)主鍵索引），直接在索引樹(shù)就能拿到結(jié)果：

	
-- 查詢：只需要 id, user_id, order_no 三列
-- 如果這三個(gè)列都在索引中，就是覆蓋索引

-- 創(chuàng)建覆蓋索引
ALTERTABLEordersADDINDEXidx_cover (user_id, order_no,id);

-- 驗(yàn)證是否使用了覆蓋索引（Extra 列顯示 Using index）
EXPLAINSELECTuser_id, order_no,idFROMordersWHEREuser_id =123;
-- Extra: Using index <-- 說(shuō)明用了覆蓋索引，不需要回表

EXPLAIN?SELECT?*?FROM?orders?WHERE?user_id =?123;
-- Extra: 沒(méi)有 Using index ?<-- SELECT * 會(huì)回表取所有列


	

	7.4 索引設(shè)計(jì)規(guī)范

	規(guī)范一：避免在低選擇性列上建索引

	
-- 例如 status 字段只有 3 個(gè)值（pending/active/completed），選擇性極低
-- 對(duì)這種字段建 B-Tree 索引幾乎沒(méi)有意義
SELECTCOUNT(DISTINCTstatus) /COUNT(*)ASselectivityFROMorders;
-- 如果結(jié)果 < 0.01，說(shuō)明選擇性太低，不適合單獨(dú)建索引

-- 但如果是聯(lián)合索引的第一列，還是有意義的（配合其他高選擇性列）


	

	規(guī)范二：字符串前綴索引

	如果字符串列很長(zhǎng)（VARCHAR(255) 或 TEXT），直接建索引太占空間。用前綴索引：

	
-- 對(duì) email 字段建立前 10 個(gè)字符的前綴索引
ALTERTABLEusersADDINDEXidx_email_prefix (email(10));

-- 前綴長(zhǎng)度選擇：讓前綴的選擇性接近完整列
SELECT
 COUNT(DISTINCTLEFT(email,5)) /COUNT(*)ASprefix_5,
 COUNT(DISTINCTLEFT(email,10)) /COUNT(*)ASprefix_10,
 COUNT(DISTINCTLEFT(email,15)) /COUNT(*)ASprefix_15,
 COUNT(DISTINCTemail) /COUNT(*)ASfull
FROMusers;
-- 選擇使前綴選擇性接近 full 值的最小長(zhǎng)度


	

	規(guī)范三：不要在頻繁更新的字段上建索引

	
-- 每次更新 orders 表的 updated_at 字段時(shí)，索引也需要更新
-- 如果 updated_at 更新非常頻繁，索引維護(hù)開(kāi)銷會(huì)很大
-- 評(píng)估方式：查看該字段的 UPDATE 頻率和查詢頻率的對(duì)比


	

	第八步：配置層面的優(yōu)化

	除了 SQL 和索引，MySQL 本身的一些參數(shù)也會(huì)影響查詢性能。

	8.1 Buffer Pool 配置

	Buffer Pool 是 MySQL 最核心的緩存區(qū)域，存放表數(shù)據(jù)、索引、鎖信息等。如果 Buffer Pool 不夠大，熱數(shù)據(jù)無(wú)法全部緩存，頻繁 disk IO 導(dǎo)致查詢變慢。

	
-- 查看 Buffer Pool 大小配置
SHOWVARIABLESLIKE'innodb_buffer_pool_size';
-- 默認(rèn)通常很小，需要調(diào)整為物理內(nèi)存的 50%-80%（留內(nèi)存給 OS 和其他用途）

-- 查看 Buffer Pool 使用情況
SHOWSTATUSLIKE'Innodb_buffer_pool%';
-- Innodb_buffer_pool_pages_total: 總頁(yè)數(shù)
-- Innodb_buffer_pool_pages_free: 空閑頁(yè)數(shù)
-- Innodb_buffer_pool_pages_dirty: 臟頁(yè)數(shù)（已修改未刷盤）

# 修改 Buffer Pool 大?。ㄐ枰貑?MySQL）
# 編輯 /etc/my.cnf
sudo vi /etc/my.cnf
# 在 [mysqld] 段添加：
innodb_buffer_pool_size = 8G # 調(diào)整為 8GB，根據(jù)實(shí)際內(nèi)存和業(yè)務(wù)需求來(lái)

# 如果 MySQL 8.0，可以使用在線調(diào)整（不需要重啟）
SET GLOBAL innodb_buffer_pool_size = 8589934592;


	

	8.2 慢查詢?nèi)罩緟?shù)調(diào)優(yōu)

	
# /etc/my.cnf 中的慢查詢相關(guān)配置
[mysqld]
# 超過(guò) 1 秒的查詢記錄到慢查詢?nèi)罩?long_query_time = 1

# 慢查詢?nèi)罩疚募窂?slow_query_log_file = /var/lib/mysql/mysql-slow.log

# 開(kāi)啟慢查詢?nèi)罩?slow_query_log = 1

# 記錄未使用索引的查詢（MySQL 5.7 有用，8.0 已移除該參數(shù)）
log_queries_not_using_indexes = 1

# 日志輸出格式（建議 FILE，不要 TABLE，TABLE 會(huì)有鎖問(wèn)題）
log_output = FILE

# 限制慢查詢?nèi)罩疚募笮。苊鈸伪疟P
max_slow_log_size = 100M


	

	8.3 連接數(shù)和臨時(shí)表配置

	
-- 查看最大連接數(shù)
SHOWVARIABLESLIKE'max_connections';
-- 默認(rèn) 151，建議根據(jù)峰值并發(fā)調(diào)整
-- 注意：每個(gè)連接都占用內(nèi)存，盲目加太大會(huì) OOM

-- 查看臨時(shí)表和排序緩沖區(qū)使用情況
SHOWGLOBALSTATUSLIKE'Created_tmp%';
SHOWVARIABLESLIKE'tmp_table_size';
SHOWVARIABLESLIKE'max_heap_table_size';

-- 如果 Created_tmp_disk_tables 很高，考慮增加 tmp_table_size
SETGLOBALtmp_table_size =256M;
SETGLOBALmax_heap_table_size =256M;


	

	第九步：完整排查流程總結(jié)

	9.1 慢查詢排查標(biāo)準(zhǔn)流程

	第一步：抓取慢查詢

	
-- 1. 確認(rèn)慢查詢?nèi)罩鹃_(kāi)啟
SHOWVARIABLESLIKE'slow_query_log';

-- 2. 如果沒(méi)開(kāi)啟，臨時(shí)開(kāi)啟
SETGLOBALslow_query_log ='ON';
SETGLOBALlong_query_time =1;

-- 3. 用 pt-query-digest 分析（推薦）
pt-query-digest /var/lib/mysql/mysql-slow.log--limit 20


	

	第二步：分析執(zhí)行計(jì)劃

	
-- 對(duì)最慢的查詢逐條執(zhí)行 EXPLAIN
EXPLAIN;

-- 關(guān)注：
-- type: 是否有 ALL（全表掃描）
-- rows: 預(yù)估掃描行數(shù)是否過(guò)大
-- Extra: 是否有 Using filesort、Using temporary


	

	第三步：定位根因并優(yōu)化

	
根因類型         解決方案
────────────────────────────────────────────────────
type=ALL         添加 WHERE 條件列的索引
Extra: Using filesort  添加 ORDER BY 列的索引，或調(diào)整索引順序
Extra: Using temporary  優(yōu)化 GROUP BY / DISTINCT / UNION 寫(xiě)法
索引失效（函數(shù)/運(yùn)算）   改寫(xiě) SQL，不在索引列上使用函數(shù)
深度分頁(yè)（OFFSET 很大）  改用游標(biāo)分頁(yè)或延遲關(guān)聯(lián)
JOIN 驅(qū)動(dòng)表不對(duì)      加 STRAIGHT_JOIN 或加索引
Buffer Pool 命中率低   調(diào)大 innodb_buffer_pool_size


	

	第四步：驗(yàn)證優(yōu)化效果

	
-- 優(yōu)化后再次 EXPLAIN，確認(rèn) type/rows/Extra 是否改善

-- 對(duì)比優(yōu)化前后的查詢時(shí)間
SETprofiling =1;
;
SHOWPROFILES;
SHOWPROFILEFORQUERY;

-- 檢查慢查詢?nèi)罩局性摬樵兪欠裣?

	

	9.2 SQL 編寫(xiě)規(guī)范（避免慢查詢）

	規(guī)范一：避免 SELECT *

	
-- 慢：讀取所有列
SELECT*FROMordersWHEREuser_id =123;

-- 快：只讀取需要的列，利用覆蓋索引
SELECTorder_no, amount, created_atFROMordersWHEREuser_id =123;


	

	規(guī)范二：WHERE 條件要具體

	
-- 慢：類型隱式轉(zhuǎn)換
SELECT*FROMusersWHEREphone =13800138000;

-- 快：使用正確的類型
SELECT*FROMusersWHEREphone ='13800138000';


	

	規(guī)范三：分頁(yè)查詢使用游標(biāo)

	
-- 慢：深度分頁(yè)
SELECT*FROMordersORDERBYidDESCLIMIT100000,10;

-- 快：游標(biāo)分頁(yè)
SELECT*FROMordersWHEREid< ORDERBYidDESCLIMIT10;


	

	規(guī)范四：批量操作分批提交

	
-- 慢：一次刪除 100 萬(wàn)行（產(chǎn)生大事務(wù)、長(zhǎng)時(shí)間鎖）
DELETEFROMlogsWHEREcreated_at 

	

	規(guī)范五：避免在 JOIN 前先做 WHERE 過(guò)濾

	
-- 慢：先 JOIN 再過(guò)濾，MySQL 可能先笛卡爾積再過(guò)濾
SELECTo.*, u.name
FROMorders o
INNERJOINusersuONo.user_id = u.id
WHEREu.status ='inactive';

-- 快：先在子查詢中過(guò)濾，再 JOIN（讓 MySQL 先處理小數(shù)據(jù)集）
SELECTo.*, u.name
FROMorders o
INNERJOIN(SELECTid,nameFROMusersWHEREstatus='inactive') uONo.user_id = u.id;


	

	總結(jié)

	MySQL 慢查詢的排查核心在于：先用慢查詢?nèi)罩咀コ霈F(xiàn)存問(wèn)題，再用 EXPLAIN 分析執(zhí)行計(jì)劃找到索引和查詢寫(xiě)法的問(wèn)題。

	最重要的三個(gè)判斷標(biāo)準(zhǔn)：

	type 列是否為 ALL（全表掃描，是最需要優(yōu)化的）

	rows 預(yù)估掃描行數(shù)是否過(guò)大（超過(guò)十萬(wàn)行就要警惕）

	Extra 列是否有 Using filesort 或 Using temporary（說(shuō)明有排序或臨時(shí)表開(kāi)銷）

	最常見(jiàn)的慢查詢根因：

	WHERE 條件列缺少索引，導(dǎo)致全表掃描

	在索引列上使用函數(shù)或運(yùn)算，導(dǎo)致索引失效

	深度分頁(yè) OFFSET 過(guò)大

	SELECT * 不利用覆蓋索引

	JOIN 被驅(qū)動(dòng)表沒(méi)有索引

	優(yōu)化優(yōu)先級(jí)：

	全表掃描（type=ALL）必須優(yōu)先解決，幾乎任何情況下都該加索引

	Using filesort 其次優(yōu)化，通常加個(gè)索引就能消除

	深度分頁(yè)改寫(xiě)游標(biāo)分頁(yè)

	最后考慮配置層面的優(yōu)化（Buffer Pool、臨時(shí)表大?。?/p>

	日常預(yù)防：

	上線前用 EXPLAIN 審核每條新 SQL

	用 pt-query-digest 定期分析慢查詢?nèi)罩?/p>

	確保每個(gè)高并發(fā)查詢都有合適的索引

	不要在低選擇性列上單獨(dú)建索引

	批量操作分批提交，避免長(zhǎng)事務(wù)