MySQL Explain 执行计划实战题

weixin_39085641

542人浏览 · 2026-03-17 11:15:30

weixin_39085641 · 2026-03-17 11:15:30 发布

MySQL Explain 执行计划实战题

这份文档是给 Explain 和索引优化练手用的。内容包括：

完整表结构
可直接执行的造数脚本
分层实战题
每题的观察点
参考答案方向

配套 SQL 文件在这里：

-- Explain practice lab
-- Compatible with MySQL 5.7 / 8.0+

DROP DATABASE IF EXISTS explain_lab;
CREATE DATABASE explain_lab DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE explain_lab;

SET NAMES utf8mb4;

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS helper_seq_20000;
DROP TABLE IF EXISTS helper_seq_10000;
DROP TABLE IF EXISTS helper_digits;

CREATE TABLE helper_digits (
    n TINYINT NOT NULL,
    PRIMARY KEY (n)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO helper_digits (n) VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE helper_seq_10000 (
    n INT NOT NULL,
    PRIMARY KEY (n)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO helper_seq_10000 (n)
SELECT d4.n * 1000 + d3.n * 100 + d2.n * 10 + d1.n + 1
FROM helper_digits d1
CROSS JOIN helper_digits d2
CROSS JOIN helper_digits d3
CROSS JOIN helper_digits d4;

CREATE TABLE helper_seq_20000 (
    n INT NOT NULL,
    PRIMARY KEY (n)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO helper_seq_20000 (n)
SELECT n
FROM helper_seq_10000
UNION ALL
SELECT n + 10000
FROM helper_seq_10000;

CREATE TABLE departments (
    dept_id INT NOT NULL,
    dept_name VARCHAR(50) NOT NULL,
    region VARCHAR(20) NOT NULL,
    status TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (dept_id),
    UNIQUE KEY uk_dept_name (dept_name),
    KEY idx_region_status (region, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE employees (
    emp_id BIGINT NOT NULL,
    emp_no VARCHAR(20) NOT NULL,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT NOT NULL,
    age TINYINT NOT NULL,
    job_title VARCHAR(30) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    status TINYINT NOT NULL DEFAULT 1,
    city VARCHAR(20) NOT NULL,
    hire_time DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (emp_id),
    UNIQUE KEY uk_emp_no (emp_no),
    KEY idx_name_age_job (emp_name, age, job_title),
    KEY idx_dept_status_hire (dept_id, status, hire_time),
    KEY idx_phone (phone),
    KEY idx_city_age (city, age),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE products (
    product_id BIGINT NOT NULL,
    product_name VARCHAR(80) NOT NULL,
    category_id INT NOT NULL,
    brand VARCHAR(30) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    status TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (product_id),
    KEY idx_category_status_price (category_id, status, price),
    KEY idx_brand_status (brand, status),
    KEY idx_product_name (product_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
    order_id BIGINT NOT NULL,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    sales_emp_id BIGINT NOT NULL,
    order_status TINYINT NOT NULL,
    pay_status TINYINT NOT NULL,
    city VARCHAR(20) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL,
    pay_time DATETIME DEFAULT NULL,
    remark VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY (order_id),
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user_status_created (user_id, order_status, created_at),
    KEY idx_status_created (order_status, created_at),
    KEY idx_sales_emp_created (sales_emp_id, created_at),
    KEY idx_city_pay (city, pay_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_items (
    item_id BIGINT NOT NULL AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    sale_price DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (item_id),
    KEY idx_order_product (order_id, product_id),
    KEY idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO departments (dept_id, dept_name, region, status, created_at)
SELECT
    n,
    CONCAT('部门', n),
    ELT((n MOD 4) + 1, '华东', '华南', '华北', '西南'),
    IF(n MOD 10 = 0, 0, 1),
    DATE_ADD('2023-01-01 09:00:00', INTERVAL n DAY)
FROM helper_seq_10000
WHERE n <= 10;

INSERT INTO employees (
    emp_id,
    emp_no,
    emp_name,
    dept_id,
    age,
    job_title,
    salary,
    phone,
    status,
    city,
    hire_time,
    created_at
)
SELECT
    n,
    CONCAT('E', LPAD(n, 6, '0')),
    ELT((n MOD 8) + 1, 'LiLei', 'HanMeimei', 'Lucy', 'Tom', 'Jerry', 'Alice', 'Bob', 'Cindy'),
    (n MOD 10) + 1,
    20 + (n MOD 25),
    ELT((n MOD 6) + 1, 'dev', 'qa', 'ops', 'manager', 'hr', 'pm'),
    5000 + (n MOD 20) * 700,
    CONCAT('138', LPAD(n, 8, '0')),
    IF(n MOD 7 = 0, 0, 1),
    ELT((n MOD 6) + 1, '北京', '上海', '深圳', '杭州', '成都', '武汉'),
    DATE_ADD('2018-01-01 09:00:00', INTERVAL n HOUR),
    DATE_ADD('2018-01-01 09:00:00', INTERVAL n HOUR)
FROM helper_seq_10000
WHERE n <= 5000;

INSERT INTO products (
    product_id,
    product_name,
    category_id,
    brand,
    price,
    status,
    created_at
)
SELECT
    n,
    CONCAT('商品', LPAD(n, 5, '0')),
    (n MOD 20) + 1,
    ELT((n MOD 6) + 1, '华为', '小米', '苹果', '联想', '美的', '耐克'),
    ROUND(99 + (n MOD 50) * 18.60 + (n MOD 7) * 3.50, 2),
    IF(n MOD 9 = 0, 0, 1),
    DATE_ADD('2023-01-01 08:00:00', INTERVAL n HOUR)
FROM helper_seq_10000
WHERE n <= 3000;

INSERT INTO orders (
    order_id,
    order_no,
    user_id,
    sales_emp_id,
    order_status,
    pay_status,
    city,
    total_amount,
    created_at,
    pay_time,
    remark
)
SELECT
    n,
    CONCAT('ORD', LPAD(n, 10, '0')),
    (n MOD 3000) + 1,
    (n MOD 5000) + 1,
    CASE
        WHEN n MOD 20 = 0 THEN 4
        WHEN n MOD 5 = 0 THEN 3
        WHEN n MOD 3 = 0 THEN 2
        ELSE 1
    END,
    IF(n MOD 5 = 0, 0, 1),
    ELT((n MOD 6) + 1, '北京', '上海', '深圳', '杭州', '成都', '武汉'),
    ROUND(49 + (n MOD 200) * 17.30 + (n MOD 7) * 3.50, 2),
    DATE_ADD('2024-01-01 00:00:00', INTERVAL n * 15 MINUTE),
    IF(
        n MOD 5 = 0,
        NULL,
        DATE_ADD('2024-01-01 00:10:00', INTERVAL n * 15 MINUTE)
    ),
    IF(n MOD 15 = 0, '加急订单', NULL)
FROM helper_seq_20000;

INSERT INTO order_items (
    order_id,
    product_id,
    quantity,
    sale_price,
    created_at
)
SELECT
    o.order_id,
    ((o.order_id * 7 + x.seq * 13) MOD 3000) + 1,
    (x.seq MOD 3) + 1,
    ROUND(99 + (((o.order_id * 7 + x.seq * 13) MOD 50) * 18.60), 2),
    o.created_at
FROM orders o
JOIN (
    SELECT 1 AS seq
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
) x;

ANALYZE TABLE departments, employees, products, orders, order_items;

-- Optional checks
SELECT 'departments' AS table_name, COUNT(*) AS row_count FROM departments
UNION ALL
SELECT 'employees', COUNT(*) FROM employees
UNION ALL
SELECT 'products', COUNT(*) FROM products
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items;

-- Optional cleanup if you do not want helper tables.
-- DROP TABLE helper_seq_20000;
-- DROP TABLE helper_seq_10000;
-- DROP TABLE helper_digits;

1. 使用说明

建议环境：

MySQL 5.7 或 8.0+
字符集 utf8mb4
先执行配套 SQL，生成 explain_lab 数据库

建议练习顺序：

先执行 EXPLAIN
再记录 type、key、key_len、rows、Extra
自己先判断为什么会这样
最后再看参考答案

如果你使用的是 MySQL 8.0.18+，可以额外执行：

EXPLAIN ANALYZE your_sql_here;

这样可以把“预估计划”和“真实执行”对照起来看。

2. 本次练习会用到的核心表

2.1 `employees`

核心索引：

PRIMARY KEY (emp_id)
UNIQUE KEY uk_emp_no (emp_no)
KEY idx_name_age_job (emp_name, age, job_title)
KEY idx_dept_status_hire (dept_id, status, hire_time)
KEY idx_created_at (created_at)

适合练习：

主键查询
唯一索引查询
联合索引最左前缀
范围条件截断
函数导致索引失效

2.2 `orders`

核心索引：

PRIMARY KEY (order_id)
UNIQUE KEY uk_order_no (order_no)
KEY idx_user_status_created (user_id, order_status, created_at)
KEY idx_status_created (order_status, created_at)
KEY idx_sales_emp_created (sales_emp_id, created_at)
KEY idx_city_pay (city, pay_status)

适合练习：

覆盖索引
排序与 Using filesort
分组与 Using temporary
OR 和 index_merge
连接查询

2.3 `products` / `order_items`

核心索引：

products.idx_product_name (product_name)
products.idx_category_status_price (category_id, status, price)
order_items.idx_order_product (order_id, product_id)

适合练习：

LIKE 前缀匹配
明细表回表
驱动表与被驱动表

3. 执行计划分析记录模板

每做一题，建议都按这个模板记录：

SQL：

1. id / select_type：
2. type：
3. possible_keys：
4. key：
5. key_len：
6. ref：
7. rows：
8. Extra：

问题判断：

1. 当前最大的代价在哪里？
2. 是扫描太多、过滤太晚、排序太重，还是回表太多？
3. 可以通过改 SQL 解决，还是要补索引？

4. 基础题

题 1：主键等值查询

EXPLAIN
SELECT *
FROM employees
WHERE emp_id = 1001;

你需要回答：

type 大概率是什么？
为什么 rows 通常很小？
这类 SQL 为什么通常是比较理想的查询？

题 2：唯一索引等值查询

EXPLAIN
SELECT emp_id, emp_no, status
FROM employees
WHERE emp_no = 'E000888';

你需要回答：

这条 SQL 和主键查询在执行计划上有哪些相似点？
uk_emp_no 为什么也能把扫描范围压得很小？
如果把条件写成 emp_no LIKE 'E000888%'，现象会不会变化？

题 3：联合索引全值匹配

EXPLAIN
SELECT emp_id, emp_name, age, job_title
FROM employees
WHERE emp_name = 'LiLei'
  AND age = 28
  AND job_title = 'dev';

你需要回答：

预计会走哪个索引？
key_len 为什么值得重点看？
这条 SQL 有没有机会出现 Using index？

题 4：跳过最左列

EXPLAIN
SELECT emp_id, emp_name, age, job_title
FROM employees
WHERE age = 28
  AND job_title = 'dev';

你需要回答：

为什么这条 SQL 很难高效利用 idx_name_age_job？
这时候 type 可能退化到什么级别？
如果业务里这类查询很多，索引设计应该怎么调整？

题 5：范围条件截断联合索引

EXPLAIN
SELECT emp_id, emp_name, age, job_title
FROM employees
WHERE emp_name = 'LiLei'
  AND age > 30
  AND job_title = 'dev';

你需要回答：

这条 SQL 有没有可能继续走 idx_name_age_job？
为什么 job_title 往往很难继续被高效利用？
这类问题该从 SQL 写法还是索引顺序上优化？

题 6：函数让索引失效

先执行下面这条：

EXPLAIN
SELECT emp_id, created_at
FROM employees
WHERE DATE(created_at) = '2018-05-01';

再执行改写后的版本：

EXPLAIN
SELECT emp_id, created_at
FROM employees
WHERE created_at >= '2018-05-01 00:00:00'
  AND created_at < '2018-05-02 00:00:00';

你需要回答：

两条 SQL 的访问类型有什么差异？
为什么第二条更容易走索引范围扫描？
这类改写思路还能迁移到哪些日期函数场景？

5. 进阶题

题 7：`LIKE` 前缀匹配和前置通配符

EXPLAIN
SELECT product_id, product_name
FROM products
WHERE product_name LIKE '商品12%';

EXPLAIN
SELECT product_id, product_name
FROM products
WHERE product_name LIKE '%12';

你需要回答：

哪一条更容易走 idx_product_name？
为什么前置通配符通常会破坏 B+ 树的查找能力？
如果业务一定要支持 %关键字%，通常有哪些替代思路？

题 8：覆盖索引和 `select *`

先执行：

EXPLAIN
SELECT user_id, order_status, created_at
FROM orders
WHERE user_id = 100
  AND order_status = 2
ORDER BY created_at DESC
LIMIT 20;

再执行：

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 100
  AND order_status = 2
ORDER BY created_at DESC
LIMIT 20;

你需要回答：

这两条 SQL 大概率会使用哪个索引？
为什么第一条更有机会出现 Using index？
第二条的额外代价主要来自哪里？

题 9：排序为什么会出现 `Using filesort`

EXPLAIN
SELECT order_id, city, total_amount
FROM orders
WHERE order_status = 2
ORDER BY total_amount DESC
LIMIT 20;

你需要回答：

即使条件里有索引，为什么排序还是可能走 Using filesort？
当前现有索引为什么帮不了这次排序？
如果这是高频查询，你会怎样设计索引？

题 10：分组为什么会出现 `Using temporary`

EXPLAIN
SELECT city, COUNT(*) AS order_cnt
FROM orders
WHERE pay_status = 1
GROUP BY city
ORDER BY order_cnt DESC;

你需要回答：

为什么这类 SQL 容易出现 Using temporary？
为什么即使补索引，也未必能彻底消掉最终排序？
如果这是报表类查询，除了补索引还能怎么优化？

题 11：连接查询里谁是驱动表

EXPLAIN
SELECT o.order_id, o.user_id, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.user_id = 100
  AND o.order_status = 2;

你需要回答：

Explain 里哪一行通常是驱动表？
orders 更可能通过哪个索引先过滤？
order_items 为什么通常能通过 idx_order_product 快速关联？

题 12：`OR` 条件和 `index_merge`

EXPLAIN
SELECT order_id, user_id, sales_emp_id
FROM orders
WHERE user_id = 100
   OR sales_emp_id = 100;

你需要回答：

这条 SQL 可能出现什么执行路径？
为什么有些版本会走 index_merge，有些版本可能干脆全表扫描？
如果你想稳定优化，为什么 UNION ALL 往往更容易控制？

6. 综合题

题 13：日期函数 + `OR` + 分组排序的综合优化

先执行：

EXPLAIN
SELECT o.city, COUNT(*) AS order_cnt
FROM orders o
WHERE DATE(o.created_at) = '2024-03-08'
  AND (o.order_status = 1 OR o.order_status = 2)
GROUP BY o.city
ORDER BY order_cnt DESC;

请你分 4 步完成：

指出这条 SQL 为什么不友好。
把 DATE() 改成范围查询。
把 OR 改成更可控的写法。
设计一个更合适的联合索引，并重新观察执行计划。

建议你自己先改写成类似这样：

SELECT o.city, COUNT(*) AS order_cnt
FROM orders o
WHERE o.created_at >= '2024-03-08 00:00:00'
  AND o.created_at < '2024-03-09 00:00:00'
  AND o.order_status IN (1, 2)
GROUP BY o.city
ORDER BY order_cnt DESC;

然后尝试补索引：

ALTER TABLE orders
ADD INDEX idx_status_created_city (order_status, created_at, city);

再次执行 EXPLAIN，对比变化。

题 14：派生表和分组子查询

EXPLAIN
SELECT *
FROM (
    SELECT user_id, COUNT(*) AS cnt
    FROM orders
    WHERE created_at >= '2024-03-01 00:00:00'
      AND created_at < '2024-04-01 00:00:00'
    GROUP BY user_id
) t
WHERE t.cnt >= 5;

你需要回答：

select_type 里会不会出现 DERIVED？
为什么这类 SQL 往往会有临时结果集？
如果这是高频报表，能不能考虑汇总表或离线预计算？

7. 参考答案方向

这一部分故意只给“方向”，不把每一列的具体结果写死。因为实际 rows、filtered、Extra 会受到 MySQL 版本、统计信息和数据分布影响。

题 1 参考方向

主键等值查询通常是最理想的单表查询之一。
type 往往是 const。
rows 一般接近 1。

题 2 参考方向

唯一索引等值查询通常也会非常高效。
单表唯一等值查询很多时候也会显示为 const。
如果改成 LIKE 'E000888%'，仍可能用索引，但匹配语义已经从“唯一命中”变成了“范围匹配”。

题 3 参考方向

大概率会命中 idx_name_age_job。
key_len 用来判断联合索引到底用了几列。
如果查询列都能从二级索引直接拿到，可能出现 Using index。

题 4 参考方向

idx_name_age_job 的入口是 emp_name。
跳过最左列以后，优化器通常很难高效命中这棵索引树。
如果这类查询是高频场景，需要单独设计适合 age, job_title 的索引。

题 5 参考方向

仍可能走 idx_name_age_job，但更多是利用到 emp_name 和 age。
一旦中间列变成范围条件，右侧列往往难以继续高效用于定位。
这就是“范围条件右边列利用率下降”的典型场景。

题 6 参考方向

DATE(created_at) 破坏了原始索引值的有序性。
改成 >= 和 < 的时间范围后，更容易变成 range。
这类写法适用于 DATE()、YEAR()、MONTH() 等函数场景。

题 7 参考方向

LIKE '商品12%' 更容易用到 idx_product_name。
LIKE '%12' 无法从左到右定位索引前缀，通常更容易退化成全表扫描。
如果业务是全文模糊匹配，应该考虑全文索引或搜索引擎。

题 8 参考方向

两条 SQL 都可能使用 idx_user_status_created。
第一条查询列更少，更容易形成覆盖索引。
第二条 select * 需要回表，IO 成本更高。

题 9 参考方向

过滤和排序是两件事。
当前索引可以帮你按 order_status 过滤，但不能直接按 total_amount 排序。
所以常见结果是“先过滤，再 filesort”。

题 10 参考方向

GROUP BY 本身就可能需要额外聚合过程。
当前条件里 pay_status 没有合适的左前缀索引来支撑过滤和分组。
即使补 (pay_status, city)，最终 ORDER BY count(*) DESC 仍可能需要排序。

题 11 参考方向

orders 大概率先被过滤，作为驱动表。
order_items 再通过 order_id 关联，通常成本较低。
Explain 里多表通常一行代表一个表的访问路径。

题 12 参考方向

可能会出现 index_merge，也可能因为成本评估而退化。
OR 往往让优化器更难稳定选择最优路径。
用两条可控的查询改写成 UNION ALL，常常更容易拿到稳定计划。

题 13 参考方向

DATE(created_at) 会削弱索引能力。
OR 容易让访问路径变复杂。
GROUP BY + ORDER BY 聚合结果 仍可能触发临时表和排序。
优化重点应该是先把过滤范围压下去，再看能不能减少聚合代价。

题 14 参考方向

子查询形成的派生表常见 DERIVED。
先聚合、再外层过滤，本身就容易生成临时结果集。
高频统计类查询更适合汇总表、物化结果或离线计算。

8. 建议你再多做一步

同一题不要只跑一次。建议每题至少做下面这 3 件事：

先跑原 SQL 的 EXPLAIN
再改写 SQL 或补索引
最后重新 EXPLAIN 做对比

这样你练到的就不是“背结论”，而是真正的执行计划分析能力。

AtomGit开源社区

AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。

更多推荐

免费AI创作+零跳转！影刀6.0在百家号当前页全自动发文

AtomGit开源社区

2026年物理学论文降AI工具推荐：理论推导和实验数据部分怎么降

AtomGit开源社区

Python机器学习：从零基础到项目实战

AtomGit开源社区

所有评论(0)

查看更多评论

weixin_39085641

@weixin_39085641

已为社区贡献3条内容

MySQL Explain 执行计划实战题

weixin_39085641

MySQL Explain 执行计划实战题

1. 使用说明

2. 本次练习会用到的核心表

2.1 employees

2.2 orders

2.3 products / order_items

3. 执行计划分析记录模板

4. 基础题

题 1：主键等值查询

题 2：唯一索引等值查询

题 3：联合索引全值匹配

题 4：跳过最左列

题 5：范围条件截断联合索引

题 6：函数让索引失效

5. 进阶题

题 7：LIKE 前缀匹配和前置通配符

题 8：覆盖索引和 select *

题 9：排序为什么会出现 Using filesort

题 10：分组为什么会出现 Using temporary

题 11：连接查询里谁是驱动表

题 12：OR 条件和 index_merge

6. 综合题

题 13：日期函数 + OR + 分组排序的综合优化

题 14：派生表和分组子查询

7. 参考答案方向

题 1 参考方向

题 2 参考方向

题 3 参考方向

题 4 参考方向

题 5 参考方向

题 6 参考方向

题 7 参考方向

题 8 参考方向

题 9 参考方向

题 10 参考方向

题 11 参考方向

题 12 参考方向

题 13 参考方向

题 14 参考方向

8. 建议你再多做一步

所有评论(0)

weixin_39085641

2.1 `employees`

2.2 `orders`

2.3 `products` / `order_items`

题 7：`LIKE` 前缀匹配和前置通配符

题 8：覆盖索引和 `select *`

题 9：排序为什么会出现 `Using filesort`

题 10：分组为什么会出现 `Using temporary`

题 12：`OR` 条件和 `index_merge`

题 13：日期函数 + `OR` + 分组排序的综合优化