在日常開發(fā)中，我們經(jīng)常會遇到 SQL 執(zhí)行緩慢的問題。此時，MySQL EXPLAIN 便是排查性能瓶頸的“利器”——它能清晰展示 SQL 的執(zhí)行計(jì)劃，幫助我們判斷索引是否生效、是否存在全表掃描、是否需要優(yōu)化排序等。本文將從測試環(huán)境搭建開始，逐步拆解 EXPLAIN 的核心字段含義，并通過實(shí)戰(zhàn)案例講解如何利用它優(yōu)化 SQL 性能，最后介紹 MySQL 8.0 帶來的執(zhí)行計(jì)劃新特性。

一、準(zhǔn)備：搭建測試環(huán)境

為了讓大家更直觀地理解 EXPLAIN 的用法，我們先創(chuàng)建一套測試數(shù)據(jù)。以下 SQL 可直接在 MySQL 中執(zhí)行，用于生成數(shù)據(jù)庫、表結(jié)構(gòu)及模擬數(shù)據(jù)。

1.1 創(chuàng)建數(shù)據(jù)庫與表

-- 1. 創(chuàng)建測試數(shù)據(jù)庫 martin
CREATE DATABASE martin;
USE martin;
-- 2. 創(chuàng)建表 t1（含主鍵與普通索引）
DROP TABLE IF EXISTS t1;
CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創(chuàng)建時間',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '記錄更新時間',
  PRIMARY KEY (`id`),  -- 主鍵索引
  KEY `idx_a` (`a`),   -- 普通索引 a
  KEY `idx_b` (`b`)    -- 普通索引 b
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 3. 創(chuàng)建存儲過程，批量插入 1000 條數(shù)據(jù)
DROP PROCEDURE IF EXISTS insert_t1;
DELIMITER ;;
CREATE PROCEDURE insert_t1()
BEGIN
  DECLARE i int;
  SET i = 1;
  WHILE (i <= 1000) DO
    INSERT INTO t1(a, b) VALUES(i, i);
    SET i = i + 1;
  END WHILE;
END;;
DELIMITER ;
CALL insert_t1();  -- 調(diào)用存儲過程插入數(shù)據(jù)
-- 4. 復(fù)制 t1 表結(jié)構(gòu)與數(shù)據(jù)到 t2
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 SELECT * FROM t1;

1.2 初識 EXPLAIN

執(zhí)行以下語句，即可查看 SQL 的執(zhí)行計(jì)劃：

EXPLAIN SELECT * FROM t1 WHERE b = 100;

執(zhí)行后會返回一張包含 12 個字段的表格，這些字段便是我們分析 SQL 性能的核心依據(jù)。接下來，我們逐一拆解這些字段的含義。

二、EXPLAIN 核心字段詳解

EXPLAIN 結(jié)果包含 id、select_type、table、type 等 12 個字段，其中 select_type、type、key、key_len、Extra 是需要重點(diǎn)關(guān)注的核心字段（下文已標(biāo)粗）。

2.1 select_type：區(qū)分查詢類型

select_type 用于標(biāo)識查詢的復(fù)雜程度，常見值如下：

示例：子查詢的 select_type

EXPLAIN SELECT * FROM t1 WHERE a = (SELECT a FROM t2 WHERE id = 10);

此時，子查詢 (SELECT a FROM t2 WHERE id = 10) 的 select_type 為 SUBQUERY，外層查詢的 select_type 為 PRIMARY。

2.2 type：判斷查詢性能等級

type 是 EXPLAIN 中最核心的字段之一，它表示表的查詢/連接方式，性能從優(yōu)到差依次為：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

優(yōu)化建議：實(shí)際開發(fā)中，應(yīng)盡量保證 type 至少達(dá)到 range 級別，避免 index 或 ALL（全表/全索引掃描）。

2.3 key_len：判斷索引生效長度

key_len 表示實(shí)際使用的索引字節(jié)數(shù)，可用于判斷 聯(lián)合索引的生效列數(shù)（需結(jié)合字段類型計(jì)算）。常見字段類型的 key_len 計(jì)算規(guī)則如下：

示例：聯(lián)合索引 idx_a_b(a, b)，若查詢 WHERE a = 100，key_len 為 5（int 允許 NULL）；若查詢 WHERE a = 100 AND b = 200，key_len 為 5 + 5 = 10 字節(jié)，說明聯(lián)合索引的兩列均生效。

2.4 Extra：性能優(yōu)化的關(guān)鍵提示

Extra 字段包含大量優(yōu)化相關(guān)的細(xì)節(jié)，常見值及優(yōu)化建議如下：

示例：Using filesort 優(yōu)化前：

-- 無索引的 ORDER BY，出現(xiàn) Using filesort
EXPLAIN SELECT * FROM t1 ORDER BY create_time;

優(yōu)化后（添加索引）：

ALTER TABLE t1 ADD INDEX idx_create_time(create_time);
EXPLAIN SELECT * FROM t1 ORDER BY create_time;  -- 無 Using filesort

三、實(shí)戰(zhàn)：EXPLAIN 優(yōu)化案例

理論結(jié)合實(shí)踐才能更好地掌握 EXPLAIN，以下通過 3 個實(shí)戰(zhàn)案例，展示如何用 EXPLAIN 定位并解決性能問題。

3.1 案例 1：對比有無索引的執(zhí)行計(jì)劃

索引是優(yōu)化 SQL 的核心手段，我們通過 EXPLAIN 對比“主鍵查詢”“有索引查詢”“無索引查詢”的差異：

結(jié)論：索引能顯著降低掃描行數(shù)，將 type 從 ALL（全表）提升至 ref 或 const。

3.2 案例 2：分析分區(qū)表的執(zhí)行計(jì)劃

對于海量數(shù)據(jù)，分區(qū)表是常用方案。EXPLAIN 的 partitions 字段可展示查詢命中的分區(qū)，幫助驗(yàn)證分區(qū)有效性。

步驟 1：創(chuàng)建分區(qū)表

CREATE TABLE sales (
  sale_id INT,
  sale_date DATE,
  amount DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(sale_date)) (  -- 按年份分區(qū)
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN MAXVALUE
);
-- 插入 2022-2024 年數(shù)據(jù)
INSERT INTO sales (sale_id, sale_date, amount)
VALUES
(1, '2022-01-01', 100.50),
(8, '2024-08-23', 270.60),
(10, '2024-10-05', 280.75);

步驟 2：查看分區(qū)執(zhí)行計(jì)劃

-- 查詢 2024 年數(shù)據(jù)，僅命中 p2024 分區(qū)
EXPLAIN SELECT * FROM sales WHERE sale_date = '2024-08-23';
-- 查詢 2023 年后數(shù)據(jù)，命中 p2023、p2024 分區(qū)
EXPLAIN SELECT * FROM sales WHERE sale_date > '2023-01-01';

此時 partitions 字段會顯示 p2024 或 p2023,p2024，說明分區(qū)生效，避免了全分區(qū)掃描。

3.3 案例 3：排查正在執(zhí)行的慢查詢

當(dāng)生產(chǎn)環(huán)境出現(xiàn)慢查詢時，可通過 EXPLAIN FOR CONNECTION 查看其執(zhí)行計(jì)劃，無需等待查詢結(jié)束。

步驟 1：構(gòu)造慢查詢

在窗口 1 執(zhí)行一條含 sleep(100) 的慢查詢：

SELECT *, SLEEP(100) FROM t1 LIMIT 1;  -- 執(zhí)行時間約 100 秒

步驟 2：獲取連接 ID

在窗口 2 執(zhí)行 show processlist，找到慢查詢的 Id（如 12）：

show processlist;

步驟 3：查看慢查詢執(zhí)行計(jì)劃

EXPLAIN FOR CONNECTION 12;  -- 12 為慢查詢的 Id

通過此方式，可快速定位慢查詢是否存在全表掃描、未使用索引等問題，及時優(yōu)化。

四、MySQL 8.0 執(zhí)行計(jì)劃新特性

MySQL 8.0 對 EXPLAIN 進(jìn)行了增強(qiáng)，新增了 樹狀執(zhí)行計(jì)劃 和 EXPLAIN ANALYZE，進(jìn)一步提升了優(yōu)化效率。

4.1 樹狀執(zhí)行計(jì)劃（format=tree）

從 MySQL 8.0.16 開始，支持輸出樹狀結(jié)構(gòu)的執(zhí)行計(jì)劃，更直觀地展示查詢邏輯（如關(guān)聯(lián)順序、過濾條件）。

EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE a = 100;

執(zhí)行結(jié)果如下（結(jié)構(gòu)清晰，包含預(yù)估成本和行數(shù)）：

-> Rows fetched before execution (cost=0.25 rows=1)
    -> Index lookup on t1 using idx_a (a=100) (cost=0.25 rows=1)

4.2 EXPLAIN ANALYZE（實(shí)際執(zhí)行分析）

從 MySQL 8.0.18 開始，EXPLAIN ANALYZE 會實(shí)際執(zhí)行 SQL，并返回更精確的執(zhí)行信息（如實(shí)際掃描行數(shù)、執(zhí)行時間、循環(huán)次數(shù)），解決了傳統(tǒng) EXPLAIN 估值不準(zhǔn)的問題。

EXPLAIN ANALYZE SELECT * FROM t1 WHERE a BETWEEN 100 AND 200;

執(zhí)行結(jié)果包含以下關(guān)鍵信息：

• actual time: 實(shí)際執(zhí)行時間（如 0.02 秒）
• actual rows: 實(shí)際掃描行數(shù)（如 101 行）
• loops: 循環(huán)次數(shù)（如 1 次）

注意：EXPLAIN ANALYZE 會執(zhí)行 SQL，若為寫操作（如 INSERT/UPDATE），需先備份數(shù)據(jù)或在測試環(huán)境使用。

五、總結(jié)

EXPLAIN 是 MySQL 性能優(yōu)化的“基石”，掌握它的核心要點(diǎn)可幫助我們快速定位問題：

1. 重點(diǎn)關(guān)注字段：type（性能等級）、key（實(shí)際索引）、Extra（優(yōu)化提示）；
2. 索引優(yōu)化原則：避免 type=ALL（全表掃描），消除 Using filesort 和 Using temporary；
3. 實(shí)戰(zhàn)技巧：用 EXPLAIN FOR CONNECTION 排查慢查詢，用 MySQL 8.0 的 EXPLAIN ANALYZE 獲取精確執(zhí)行信息。

希望本文能幫助你更好地利用 EXPLAIN 優(yōu)化 SQL 性能，讓數(shù)據(jù)庫查詢更高效！

到此這篇關(guān)于MySQL EXPLAIN 從入門到實(shí)戰(zhàn)完全指南的文章就介紹到這了,更多相關(guān)mysql explain實(shí)戰(zhàn)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論