概述

MySQL 的 EXPLAIN 命令用于分析 SQL 查詢的執(zhí)行計(jì)劃（Execution Plan），它可以輸出查詢?nèi)绾伪粌?yōu)化器處理，包括表掃描方式、索引使用、連接順序、行數(shù)估計(jì)等。通過(guò)分析 EXPLAIN 的輸出（如 id、select_type、type、possible_keys、key、rows、Extra 等字段），可以排查查詢性能問(wèn)題，幫助優(yōu)化慢查詢、減少資源消耗。EXPLAIN 一般能排查以下類型的問(wèn)題：

• 索引使用不當(dāng)：如未使用索引導(dǎo)致全表掃描（type: ALL），或使用了低效索引。
• 連接順序問(wèn)題：如大表驅(qū)動(dòng)小表，導(dǎo)致笛卡爾積或低效連接（type: ref 或 eq_ref 不理想）。
• 子查詢或派生表優(yōu)化問(wèn)題：如子查詢未優(yōu)化成連接，導(dǎo)致多次執(zhí)行（select_type: SUBQUERY 或 DEPENDENT SUBQUERY）。
• 排序和分組問(wèn)題：如使用文件排序（Extra: Using filesort）或臨時(shí)表（Extra: Using temporary），表示內(nèi)存不足或缺少索引支持。
• 行數(shù)估計(jì)不準(zhǔn)：rows 值過(guò)大，表示優(yōu)化器估計(jì)錯(cuò)誤，可能因統(tǒng)計(jì)信息過(guò)時(shí)。
• 其他額外操作：如使用臨時(shí)表、回表查詢（key_len 不匹配）等，導(dǎo)致 I/O 開銷高。

EXPLAIN 不能直接排查硬件問(wèn)題（如 CPU/內(nèi)存不足）或鎖爭(zhēng)用，但能間接指出查詢效率瓶頸。以下是多個(gè)例子，每個(gè)例子包括問(wèn)題描述、EXPLAIN 輸出示例、業(yè)務(wù)場(chǎng)景，以及優(yōu)化建議。例子基于常見 MySQL 場(chǎng)景，假設(shè)使用 InnoDB 引擎。

示例詳解

示例 1: 全表掃描（未使用索引）

• 問(wèn)題描述：查詢未使用索引，導(dǎo)致全表掃描（type: ALL），掃描行數(shù)巨大，查詢變慢。
• EXPLAIN 輸出示例：

- id: 1
select_type: SIMPLE
table: users
type: ALL
possible_keys: NULL
key: NULL
rows: 1000000
Extra: Using where

這里 type 為 ALL，表示全表掃描；rows 為 1000000，表示掃描了百萬(wàn)行。

• 業(yè)務(wù)場(chǎng)景：在一個(gè)電商平臺(tái)的用戶管理系統(tǒng)中，需要查詢所有活躍用戶（status = ‘active’）。用戶表有 100 萬(wàn)行，但 status 字段未建索引。高峰期查詢耗時(shí) 10 秒以上，導(dǎo)致頁(yè)面加載慢，用戶投訴訂單確認(rèn)延遲。
• 優(yōu)化建議：在 status 字段添加索引（ALTER TABLE users ADD INDEX idx_status (status)），重新執(zhí)行 EXPLAIN，type 變?yōu)?ref 或 range，rows 減少到幾千行，查詢時(shí)間降到毫秒級(jí)。

示例 2: 低效連接順序（大表驅(qū)動(dòng)小表）

• 問(wèn)題描述：多表連接時(shí)，優(yōu)化器選擇了錯(cuò)誤的連接順序，導(dǎo)致大表作為驅(qū)動(dòng)表，產(chǎn)生大量中間結(jié)果。
• EXPLAIN 輸出示例：

- id: 1
select_type: SIMPLE
table: large_orders (大表，100萬(wàn)行)
type: ALL
possible_keys: NULL
key: NULL
rows: 1000000
Extra: NULL
id: 1
select_type: SIMPLE
table: small_users (小表，1萬(wàn)行)
type: ref
possible_keys: idx_user_id
key: idx_user_id
rows: 1
Extra: Using where

這里大表 large_orders 先掃描，導(dǎo)致效率低。

• 業(yè)務(wù)場(chǎng)景：在金融 App 的交易記錄系統(tǒng)中，需要聯(lián)查用戶表（小表）和訂單表（大表）來(lái)統(tǒng)計(jì)用戶交易總額。訂單表有百萬(wàn)行，用戶表只有萬(wàn)行。但由于連接條件不當(dāng)，高峰期報(bào)表生成需幾分鐘，影響財(cái)務(wù)人員實(shí)時(shí)分析交易風(fēng)險(xiǎn)。
• 優(yōu)化建議：調(diào)整 SQL 連接順序，使用 STRAIGHT_JOIN 強(qiáng)制小表驅(qū)動(dòng)大表，或優(yōu)化索引確保小表先連接。優(yōu)化后，EXPLAIN 顯示小表先 type: ALL 或 index，整體 rows 減少，查詢加速 5 倍以上。

示例 3: 子查詢未優(yōu)化（多次執(zhí)行子查詢）

• 問(wèn)題描述：子查詢未被優(yōu)化成連接，導(dǎo)致每次主查詢都獨(dú)立執(zhí)行子查詢，效率低下（select_type: SUBQUERY）。
• EXPLAIN 輸出示例：

- id: 1
select_type: PRIMARY
table: products
type: ALL
rows: 50000
Extra: Using where
id: 2
select_type: SUBQUERY
table: inventory
type: ALL
rows: 10000
Extra: NULL

子查詢獨(dú)立執(zhí)行，可能被調(diào)用多次。

• 業(yè)務(wù)場(chǎng)景：在庫(kù)存管理系統(tǒng)中，查詢所有銷量大于平均銷量的產(chǎn)品，使用子查詢計(jì)算平均值。產(chǎn)品表 5 萬(wàn)行，庫(kù)存表 1 萬(wàn)行。雙 11 促銷期，庫(kù)存檢查查詢頻繁執(zhí)行，導(dǎo)致數(shù)據(jù)庫(kù)負(fù)載高，系統(tǒng)響應(yīng)變慢，影響商家實(shí)時(shí)補(bǔ)貨決策。
• 優(yōu)化建議：將子查詢改寫為 JOIN 或使用 WITH 子句（CTE）。優(yōu)化后，EXPLAIN 顯示 select_type: DERIVED（派生表），子查詢只執(zhí)行一次，查詢時(shí)間從秒級(jí)降到毫秒，系統(tǒng)負(fù)載降低 30%。

示例 4: 文件排序問(wèn)題（缺少排序索引）

• 問(wèn)題描述：查詢涉及 ORDER BY 但無(wú)對(duì)應(yīng)索引，導(dǎo)致使用文件排序（Extra: Using filesort），磁盤 I/O 高。
• EXPLAIN 輸出示例：

id: 1
select_type: SIMPLE
table: logs
type: ref
possible_keys: idx_date
key: idx_date
rows: 200000
Extra: Using where; Using fileso

Extra 中有 Using filesort，表示排序在磁盤上進(jìn)行。

• 業(yè)務(wù)場(chǎng)景：在日志分析平臺(tái)中，按時(shí)間降序查詢最近 1 個(gè)月的訪問(wèn)日志。日志表有 20 萬(wàn)行，無(wú)創(chuàng)建時(shí)間索引。運(yùn)維團(tuán)隊(duì)每天生成報(bào)告時(shí)，查詢耗時(shí)長(zhǎng)，導(dǎo)致服務(wù)器 CPU 占用率飆升，影響其他服務(wù)如用戶登錄。
• 優(yōu)化建議：在 ORDER BY 字段（如 created_at）添加復(fù)合索引（ALTER TABLE logs ADD INDEX idx_date_created (date, created_at DESC)）。優(yōu)化后，Extra 變?yōu)?Using index，排序在內(nèi)存中完成，報(bào)告生成時(shí)間縮短 80%。

示例 5: 使用臨時(shí)表（GROUP BY 低效）

• 問(wèn)題描述：GROUP BY 或 DISTINCT 操作缺少支持索引，導(dǎo)致創(chuàng)建臨時(shí)表（Extra: Using temporary），內(nèi)存或磁盤消耗大。
• EXPLAIN 輸出示例：

id: 1
select_type: SIMPLE
table: sales
type: ALL
rows: 300000
Extra: Using temporary; Using file

Extra 有 Using temporary，表示創(chuàng)建了臨時(shí)表。

• 業(yè)務(wù)場(chǎng)景：在 CRM 系統(tǒng)（客戶關(guān)系管理）中，按地區(qū)分組統(tǒng)計(jì)銷售總額。銷售表 30 萬(wàn)行，無(wú)地區(qū)索引。月度業(yè)績(jī)報(bào)告生成時(shí)，查詢卡住幾分鐘，影響銷售經(jīng)理評(píng)估團(tuán)隊(duì)績(jī)效和調(diào)整營(yíng)銷策略。
• 優(yōu)化建議：在 GROUP BY 字段（如 region）添加索引，并確保 WHERE 條件也覆蓋索引。優(yōu)化后，Extra 移除 Using temporary，查詢使用索引覆蓋，報(bào)告生成即時(shí)完成，提高了決策效率。

示例 6: 回表查詢（非覆蓋索引）

• 問(wèn)題描述：索引未覆蓋所有 SELECT 字段，導(dǎo)致回表查詢（key_len 小于預(yù)期，Extra: Using index condition），增加 I/O。
• EXPLAIN 輸出示例：
• Extra 有 Using temporary，表示創(chuàng)建了臨時(shí)表。
• 業(yè)務(wù)場(chǎng)景：在 CRM 系統(tǒng)（客戶關(guān)系管理）中，按地區(qū)分組統(tǒng)計(jì)銷售總額。銷售表 30 萬(wàn)行，無(wú)地區(qū)索引。月度業(yè)績(jī)報(bào)告生成時(shí)，查詢卡住幾分鐘，影響銷售經(jīng)理評(píng)估團(tuán)隊(duì)績(jī)效和調(diào)整營(yíng)銷策略。
• 優(yōu)化建議：在 GROUP BY 字段（如 region）添加索引，并確保 WHERE 條件也覆蓋索引。優(yōu)化后，Extra 移除 Using temporary，查詢使用索引覆蓋，報(bào)告生成即時(shí)完成，提高了決策效率。

id: 1
select_type: SIMPLE
table: employees
type: ref
possible_keys: idx_dept
key: idx_dept
key_len: 4
rows: 5000
Extra: Using index condition

key_len 短，表示只用了部分索引，需要回表取其他列。

• 業(yè)務(wù)場(chǎng)景：在 HR 系統(tǒng)（人力資源）中，查詢某個(gè)部門的所有員工姓名和薪資。員工表 5 萬(wàn)行，部門索引存在但不覆蓋姓名薪資。批量導(dǎo)出員工數(shù)據(jù)時(shí)，查詢慢，導(dǎo)致 HR 無(wú)法及時(shí)處理 payroll（工資單），影響員工滿意度。
• 優(yōu)化建議：創(chuàng)建覆蓋索引（ALTER TABLE employees ADD INDEX idx_dept_name_salary (dept_id, name, salary)）。優(yōu)化后，Extra 變?yōu)?Using index（索引覆蓋），無(wú)需回表，導(dǎo)出速度提升 10 倍。

總結(jié)

通過(guò)這些例子，可以看到 EXPLAIN 是優(yōu)化 MySQL 查詢的核心工具。在實(shí)際業(yè)務(wù)中，結(jié)合慢查詢?nèi)罩荆╯low log）和 SHOW STATUS 檢查全局性能，能更全面排查問(wèn)題。如果查詢復(fù)雜，建議使用 EXPLAIN ANALYZE（MySQL 8.0+）獲取實(shí)際執(zhí)行統(tǒng)計(jì)。

到此這篇關(guān)于MySQL EXPLAIN排查問(wèn)題指南的文章就介紹到這了,更多相關(guān)MySQL EXPLAIN排查內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論