MySQL 优化器深度解析：回表 vs 全表扫描 vs Skip Scan，范围查询为什么经常改变结局MySQL 优化器

MySQL 优化器深度解析：回表 vs 全表扫描 vs Skip Scan，范围查询为什么经常改变结局

很多人对索引有两个误解：

建了索引，查询就一定该走索引。
只要走了索引，就一定比全表扫描快。

这两句都不对。

MySQL 优化器真正关心的，从来不是“有没有用索引”，而是：

哪条访问路径的总代价最低。

也正因为如此，我们才会在生产里反复看到这些现象：

同一组过滤条件，只是把 SELECT emp_id, emp_name... 改成 SELECT *，执行计划就从索引扫描切成了全表扫描。
明明用了联合索引，遇到范围查询后，后面的列“看起来还在索引里”，但已经不能继续缩小扫描区间。
有时还能看到一个很神秘的 Extra：Using index for skip scan。

这篇文章就把这三件事放到同一个成本模型里讲透。

一、实验环境与结论先行

本文案例不是空想推导，而是基于工作区已有实验脚本跑出来的真实 EXPLAIN：

MySQL 版本：8.0.45
存储引擎：InnoDB
测试表：explain_lab.employees
行数：5000
emp_name 去重数：8
age 去重数：25
job_title 去重数：6

先给你一句结论，后面所有案例都在验证它：

覆盖索引会让索引方案变便宜；回表、范围扫描、Skip Scan 会让索引方案变贵；一旦贵过顺序读整表，优化器就会直接选全表扫描。

在这里插入图片描述

需要先提醒一个很容易误读的点：

EXPLAIN 里的 rows 是“预计要扫描多少行”，不是“最终返回多少行”。

所以你看计划时，要同时看：

type：访问级别
key / key_len：用了哪条索引，用到了什么程度
rows：优化器估计要付出多大扫描成本
Extra：有没有覆盖索引、ICP、Skip Scan、filesort 这些额外动作

在这里插入图片描述

一个最实用的记忆法

Using index：索引里拿全了
Using index condition：索引里先筛一遍
Using index for skip scan：没走最左列，硬想办法走索引
Using filesort：索引排不了，自己排

二、测试表与核心索引

本文只保留和主题相关的列，结构如下：

CREATE TABLE employees (
    emp_id BIGINT 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,
    city VARCHAR(20) NOT NULL,
    hire_time DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (emp_id),
    KEY idx_name_age_job (emp_name, age, job_title)
) ENGINE=InnoDB;

联合索引顺序是：

(emp_name, age, job_title)

后面所有争议，几乎都围绕这个顺序展开。

三、案例 1：完全命中联合索引，而且还是覆盖索引

先看最理想的情况：

EXPLAIN
SELECT emp_id, emp_name, age, job_title
FROM employees
WHERE emp_name = 'Alice'
  AND age = 34
  AND job_title = 'manager';

真实 EXPLAIN：

+----+-------------+-----------+------+------------------+------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table     | type | possible_keys    | key              | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-----------+------+------------------+------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | employees | ref  | idx_name_age_job | idx_name_age_job | 325     | const,const,const |    9 |   100.00 | Using index |
+----+-------------+-----------+------+------------------+------------------+---------+-------------------+------+----------+-------------+

实际返回行数：9

这个计划为什么最理想？

emp_name = ? AND age = ? AND job_title = ? 完整命中了联合索引的顺序。
查询列只有 emp_id, emp_name, age, job_title。
InnoDB 二级索引叶子节点里本来就带主键 emp_id，所以这是覆盖索引。

也就是说，MySQL 在二级索引里就把结果凑齐了：

能精确定位。
不需要回表。
rows 很小。

这就是“索引真的赚到钱”的典型场景。

四、案例 2：不满足最左前缀，但仍然用上了索引

再看这条：

EXPLAIN
SELECT emp_id, emp_name, age, job_title
FROM employees
WHERE age = 34
  AND job_title = 'manager';

真实 EXPLAIN：

+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+----------------------------------------+
| id | select_type | table     | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | employees | range | idx_name_age_job | idx_name_age_job | 203     | NULL |  499 |    10.00 | Using where; Using index for skip scan |
+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+----------------------------------------+

实际返回行数：34

这里最值得注意的不是 key=idx_name_age_job，而是 Extra：

Using index for skip scan

这说明 MySQL 8 没有直接放弃这条联合索引，而是启用了 Index Skip Scan。

它的本质不是“真的跳过了最左列”，而是：

先枚举 emp_name 的每个可能值。
对每个值分别做一次后缀扫描。
最后再合并结果。

在这套实验数据里，emp_name 只有 8 个 distinct 值，所以这个方案还有机会被接受。

但你要记住：Skip Scan 不是“白嫖联合索引”，而是“拿多次扫描换一次补救”。

在这里插入图片描述

为什么这次没有退化成全表扫描？

因为这条 SQL 仍然是覆盖索引：

只查 emp_id, emp_name, age, job_title
不需要回表
虽然要扫多轮索引，但代价还没高到输给全表扫描

五、案例 3：同样的条件，只改成 `SELECT *`，直接全表扫描

现在只做一个改动：

EXPLAIN
SELECT *
FROM employees
WHERE age = 34
  AND job_title = 'manager';

真实 EXPLAIN：

+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 4992 |     1.00 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+

这次优化器甚至连索引都懒得选了，直接 type=ALL。

为什么？

因为它比较的是两套总代价：

方案 1：Skip Scan + 回表

枚举多个 emp_name
多次扫描二级索引
每个候选结果再回表取整行

方案 2：全表扫描

顺序读整张表
在 Server 层过滤 age = 34 AND job_title = 'manager'

在这组数据分布下，优化器判断方案 2 更便宜。

这就是你在线上最容易误判的地方：

不是“索引失效了”，而是“索引方案不划算了”。

六、范围查询到底改变了什么

很多人把联合索引记成一句口诀：

范围之后，索引失效

这句话不算错，但太粗糙，拿来分析执行计划经常会误伤。

更准确的说法应该是：

遇到第一个范围列后，后续列通常不能再继续缩小“扫描区间”。
但后续列仍然可能用于覆盖索引。
也仍然可能用于 ICP，在回表前先过滤掉一批不满足条件的记录。
如果排序或分组依赖这些后续列，范围还可能破坏索引有序性，触发 Using filesort 或 Using temporary。

这张图先把关系讲清楚：

在这里插入图片描述

下面我们用真实计划把这四点拆开。

七、案例 4：等值 + 范围 + 后缀等值，仍然能走索引，但访问类型已经变了

EXPLAIN
SELECT emp_id, emp_name, age, job_title
FROM employees
WHERE emp_name = 'Alice'
  AND age BETWEEN 34 AND 38
  AND job_title = 'manager';

真实 EXPLAIN：

+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employees | range | idx_name_age_job | idx_name_age_job | 325     | NULL |  109 |    10.00 | Using where; Using index |
+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+--------------------------+

实际返回行数：45

这条 SQL 特别适合拿来讲“范围之后到底发生了什么”。

1. `emp_name = 'Alice'` 仍然在帮你找入口

它把扫描范围先锁定到：

emp_name = 'Alice'

2. `age BETWEEN 34 AND 38` 把点查变成了一段扫描

从这一刻开始，访问类型就不再是 ref，而是 range。

MySQL 要扫的是这样一段索引区间：

('Alice', 34, -inf)  ~  ('Alice', 38, +inf)

3. `job_title = 'manager'` 还在索引里，但它已经不是“继续定范围”

这是最关键的一点。

很多人看到：

key_len 仍然挺长
job_title 也在联合索引里

就会误以为：

既然第三列也在索引里，那它应该还在继续缩小扫描范围。

其实不是。

更准确的理解是：

真正决定扫描边界的是 emp_name 和 age BETWEEN ...
job_title = 'manager' 主要是在这段扫描里继续过滤
因为查询列仍被覆盖，所以 Extra 才会是 Using where; Using index

所以，“范围之后不是完全没用”，而是：

还能参与过滤和覆盖，但通常不能继续把扫描区间压得更窄。

八、案例 5：同样是范围查询，一旦不覆盖，就会出现 ICP 和回表

把上一条 SQL 改成 SELECT *：

EXPLAIN
SELECT *
FROM employees
WHERE emp_name = 'Alice'
  AND age BETWEEN 34 AND 38
  AND job_title = 'manager';

真实 EXPLAIN：

+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | range | idx_name_age_job | idx_name_age_job | 325     | NULL |  109 |    10.00 | Using index condition |
+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+-----------------------+

这次 type 仍然是 range，因为：

emp_name = 'Alice' 仍然把前缀锁得很窄
age BETWEEN 34 AND 38 也只是一个较小区间

所以优化器还认为“先走索引，再回表”是值得的。

但 Extra 变了：

Using index condition

这说明 MySQL 在做 ICP（Index Condition Pushdown）。

它的意义非常大：

存储引擎先扫描索引区间
在索引层先判断 job_title = 'manager'
只有过滤通过的记录才回表

换句话说，范围之后的后缀列虽然不能继续缩小扫描段，但仍然可以帮你减少回表次数。

这就是为什么我不喜欢把它简单说成“范围之后索引失效”。

更准确的表述应该是：

范围之后，后续列通常不能继续参与“定界”，但仍然可能参与“过滤”和“减少回表”。

九、案例 6：范围查询还会破坏排序能力，触发 `Using filesort`

很多人只盯着“能不能走索引”，却忽略了范围对排序的破坏。

看这条：

EXPLAIN
SELECT emp_id, emp_name, age, job_title
FROM employees
WHERE emp_name = 'Alice'
  AND age BETWEEN 34 AND 38
ORDER BY job_title;

真实 EXPLAIN：

+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table     | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | employees | range | idx_name_age_job | idx_name_age_job | 203     | NULL |  125 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+------------------------------------------+

为什么 job_title 明明在联合索引里，还会 Using filesort？

因为索引顺序是：

(emp_name, age, job_title)

当 age 变成范围后，索引天然输出的顺序更接近：

先按 age，再按每个 age 内部的 job_title

但你的 ORDER BY job_title 要求的是：

整个结果集按 job_title 全局有序

这两件事不是一回事。

所以即使查找过程走了索引，排序阶段仍然要单独做一次 filesort。

这就是范围查询的第三个影响：

它不仅放大扫描成本，还可能切断后缀列继续复用索引顺序的能力。

十、案例 7：范围 + Skip Scan 是最容易翻车的组合

先看覆盖索引版本：

EXPLAIN
SELECT emp_id, emp_name, age, job_title
FROM employees
WHERE age BETWEEN 34 AND 38
  AND job_title = 'manager';

真实 EXPLAIN：

+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+----------------------------------------+
| id | select_type | table     | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | employees | range | idx_name_age_job | idx_name_age_job | 203     | NULL |  554 |    10.00 | Using where; Using index for skip scan |
+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+----------------------------------------+

实际返回行数：167

再看 SELECT *：

EXPLAIN
SELECT *
FROM employees
WHERE age BETWEEN 34 AND 38
  AND job_title = 'manager';

真实 EXPLAIN：

+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 4992 |     1.11 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+

这组对比非常有代表性。

为什么范围 + Skip Scan 更脆弱？

因为它同时叠加了两种成本放大器：

Skip Scan：要枚举最左列，多轮扫描
Range：每轮扫描还不是点查，而是一整段区间

如果此时还要 SELECT *：

候选记录变多
回表次数也会跟着变多
优化器很容易直接投向全表扫描

所以我通常会把这类 SQL 归为：

“勉强能靠优化器救一把，但绝对不该长期依赖”的查询。

十一、把范围查询真正讲透：你至少要记住这 5 句话

如果你只想带走最关键的结论，就记这 5 句：

范围列之前的等值条件，决定索引扫描从哪里开始。
第一个范围条件，决定这次扫描要扫多宽。
范围列之后的列，通常不能继续缩小扫描区间。
但范围列之后的列，仍然可能用于覆盖索引或 ICP，减少回表。
范围列之后如果还想继续复用索引顺序做排序或分组，往往就没那么容易了。

用一句更完整的话来概括就是：

范围查询真正带来的，不是“索引彻底失效”，而是“索引从精确定位退化成区间扫描，并削弱后缀列继续参与定界、排序和分组的能力”。

十二、为什么回表经常输给全表扫描

很多人觉得“全表扫描”这个词天生就差，其实不然。

对 InnoDB 来说，常见的成本差异大致可以理解成：

全表扫描：顺序读数据页，IO 更连续
索引 + 回表：先扫二级索引，再随机回主键索引，IO 更离散

所以当下面几件事叠加出现时，索引方案就会越来越贵：

过滤条件不够精准
范围过宽
Skip Scan 需要多轮扫描
还要 SELECT *
结果集和候选集差距不大

此时优化器选择 ALL，并不奇怪，反而很合理。

十三、实战优化建议

如果你在线上遇到类似 SQL，优化优先级我建议这样排：

1. 先问自己：真的需要 `SELECT *` 吗

这是最便宜、最经常立竿见影的优化。

能覆盖索引时：

省掉回表
降低随机 IO
让 Skip Scan 还有存在价值

2. 高频查询如果经常跳过最左列，就别指望 Skip Scan 长期兜底

比如下面这种高频查询：

WHERE age = ? AND job_title = ?

如果业务里非常常见，就应该认真考虑增加更直接的索引：

KEY idx_age_job (age, job_title)

而不是把 Skip Scan 当常规方案。

3. 联合索引设计时，把高频等值列放前面，把范围列放后面

一个非常通用的原则是：

等值列优先，范围列靠后，排序列再综合评估

因为一旦范围列放得太靠前，后面的列可利用空间就会迅速缩小。

4. 不要把“范围之后索引失效”当成死口诀

正确姿势是分三层看：

它还能不能继续定界
它还能不能继续过滤
它还能不能继续保序

把这三层分开，你看执行计划会清晰很多。

5. 用 `EXPLAIN ANALYZE` 验证预估和真实执行是否一致

特别是下面几种 SQL：

rows 看起来不大，但实际很慢
type 看起来还行，但仍然走了大量回表
明明建了索引，优化器就是选 ALL

这时 EXPLAIN ANALYZE 和 optimizer_trace 往往比背口诀更有用。

十四、终极总结

把本文浓缩成一句面试可用的话，就是：

MySQL 优化器会基于成本模型，在“索引扫描、Skip Scan、全表扫描”之间选择代价最低的路径。覆盖索引会显著降低索引方案成本；而范围查询、Skip Scan 和回表都会抬高索引方案成本。一旦这些成本叠加后高于顺序读整表，优化器就会果断放弃索引。

如果再压缩成一句最短记忆版，就是：

能覆盖，就尽量覆盖；
要回表，就看候选集有多大；
既范围又 Skip Scan 还 select *，最容易直接退化成全表扫描。

十五、配套文件

本文用到的造数脚本已经在工作区里，可以直接复现：

-- 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;

我还把本文涉及的查询单独整理成了一个脚本：

USE explain_lab;

SELECT VERSION() AS mysql_version;

SELECT COUNT(*) AS total_rows,
       COUNT(DISTINCT emp_name) AS distinct_emp_name,
       COUNT(DISTINCT age) AS distinct_age,
       COUNT(DISTINCT job_title) AS distinct_job_title
FROM employees;

-- Case 1: exact match + covering index
EXPLAIN
SELECT emp_id, emp_name, age, job_title
FROM employees
WHERE emp_name = 'Alice'
  AND age = 34
  AND job_title = 'manager';

SELECT COUNT(*) AS actual_rows
FROM employees
WHERE emp_name = 'Alice'
  AND age = 34
  AND job_title = 'manager';

-- Case 2: skip scan + covering index
EXPLAIN
SELECT emp_id, emp_name, age, job_title
FROM employees
WHERE age = 34
  AND job_title = 'manager';

SELECT COUNT(*) AS actual_rows
FROM employees
WHERE age = 34
  AND job_title = 'manager';

-- Case 3: skip scan candidate but select * forces full scan
EXPLAIN
SELECT *
FROM employees
WHERE age = 34
  AND job_title = 'manager';

-- Case 4: equality + range + trailing condition, still covered
EXPLAIN
SELECT emp_id, emp_name, age, job_title
FROM employees
WHERE emp_name = 'Alice'
  AND age BETWEEN 34 AND 38
  AND job_title = 'manager';

SELECT COUNT(*) AS actual_rows
FROM employees
WHERE emp_name = 'Alice'
  AND age BETWEEN 34 AND 38
  AND job_title = 'manager';

-- Case 5: equality + range + trailing condition + select *
EXPLAIN
SELECT *
FROM employees
WHERE emp_name = 'Alice'
  AND age BETWEEN 34 AND 38
  AND job_title = 'manager';

-- Case 6: range breaks ordering ability on trailing column
EXPLAIN
SELECT emp_id, emp_name, age, job_title
FROM employees
WHERE emp_name = 'Alice'
  AND age BETWEEN 34 AND 38
ORDER BY job_title;

SELECT COUNT(*) AS actual_rows
FROM employees
WHERE emp_name = 'Alice'
  AND age BETWEEN 34 AND 38;

-- Case 7: range + skip scan is much more fragile
EXPLAIN
SELECT emp_id, emp_name, age, job_title
FROM employees
WHERE age BETWEEN 34 AND 38
  AND job_title = 'manager';

SELECT COUNT(*) AS actual_rows
FROM employees
WHERE age BETWEEN 34 AND 38
  AND job_title = 'manager';

EXPLAIN
SELECT *
FROM employees
WHERE age BETWEEN 34 AND 38
  AND job_title = 'manager';

MySQL 优化器深度解析：回表 vs 全表扫描 vs Skip Scan，范围查询为什么经常改变结局