MySQL（关系型数据库）索引：选型与验证

热身 · 一 场景 · 二 找整行 · 三 联合索引 · 四 范围与覆盖 · 五 验证 · 六 应用 · 检验 · 完整表达 · 追问 · 隔日复测

学完能做到什么，为什么现在学

给定“按部门、状态和提交时间查办件”的查询，你能提出一个联合索引，沿着索引顺序说明它减少了哪些查找；能判断查询是否需要回表，并列出执行计划与实测需要核对的证据。

本课从“查哪批办件”开始，再讲内部结构。目标是把业务条件、索引选择和验证串成一段完整的解释。课件中的办件表是教学场景，不是真实项目的优化成果。一次真实作答中容易混淆的地方，整理在本课末尾的课后问答。

前置： 能读懂按条件查询、知道一行是一条办件记录即可；不要求先学 JVM（Java 虚拟机）的内容。 必学： 第一至六节、检验与表达。拓展折叠区可稍后看；篇幅较长时，第三节之后是合适的停顿点，再从第四节范围查询接着读。事务、锁和日志留到后续课。

主读： 官方 Multiple-Column Indexes（多列索引）。只读“姓名两列索引”的例子和最后四条查询，核对为什么索引的第一列很重要。其他依据集中在来源区。

热身：先回忆前几课（3 分钟）

先凭记忆回答，再展开核对。答不上来就记一句，接着往下学，这不是进入新课的门槛。

一、先说业务：这条查询到底在找什么

“办件列表慢，你会直接给所有查询字段建索引吗？”

设想一个教学系统有100万条办件。经办人只想看“部门10、待审批、10月1日至8日之前提交”的记录。下面的表叫 case_item：id 是唯一办件编号，dept_id 是部门，status 是状态（0待审批、1已办结），submitted_at 是提交时间，applicant 是申请人。这些数字与字段是教学设定。

示例边界： 以下固定值用于阅读演示。应用接收部门和日期时应校验权限、类型与起止范围，并通过绑定参数传值；不要把输入拼进查询文本。新增索引会增加空间与写入成本，先在隔离测试表验证。[8]

SELECT id, dept_id, status, submitted_at
FROM case_item
WHERE dept_id = 10 AND status = 0
  AND submitted_at >= '2026-10-01'
  AND submitted_at <  '2026-10-08';

演示：起点10月1日包含在内，10月8日不包含；10月7日任意时刻都在区间中。第六节给出6行数据，请先手工选出符合条件的编号，再看答案。本课不承诺这条查询的实际耗时。

index（索引）可以理解为“按某些字段排好的查找目录”。没有合适的索引时，可能要逐行检查；有了合适的目录，就有机会直接找到符合条件的那一段。full table scan（全表扫描）就是检查整张表的记录。这里的关键不是“索引名字出现了”，而是 查找范围有没有缩小，以及后续还需要读多少内容 。[3]

二、先找编号，再找整行：回表是怎么来的

“主键索引和普通索引有什么区别？回表是什么？”

InnoDB（事务存储引擎）负责这张表的数据存储。我们显式把 id 定义为 primary key（主键）：每条记录的唯一标识，不能为空。在这个前提下，它的 clustered index（聚簇索引）按主键组织记录，末端的页存放行数据。这里的 page（页）是一次组织多条记录的存储单位，不是网页。[2][7]

再为部门等字段建的 secondary index（二级索引），记录中保存“这些索引列的值＋该行主键”。若查询还要申请人姓名，二级索引里没有这个字段，就先取出编号，再用编号到聚簇索引查整行。这个过程就是 table lookup（回表）。[2]

两次查找不等于固定两次磁盘读写：数据可能已经在内存中，一次树查找也可能涉及多个页。先把“查哪棵索引、要不要再取整行”说清楚，不背固定次数。

三、联合索引：先按部门，再按状态，最后按时间

“为什么用三列联合索引？最左前缀到底在说什么？”

composite index（联合索引）把多列按指定顺序放进同一个索引。候选是 (dept_id, status, submitted_at)：先按部门排序；同一部门内按状态排序；同一部门、同一状态内再按提交时间排序。可以想成通讯录先按姓、再按名，单独的名在全册中未必挨在一起。[1]

这是索引记录的排序示意，年份均为2026，未展示真实页结构。查部门10，能定位到这个部门的一段；再限制状态0，段更小；最后限制时间，可以在这一段中找10月1日至8日之前的部分。

leftmost prefix（最左前缀）就是从索引第一列开始连续取：这里有“部门”“部门＋状态”“部门＋状态＋时间”。它描述的是 索引定义中的列顺序 ，不是要求查询条件按这个顺序书写；把同样的等值条件前后交换，不会把索引本身重排。[1]

只给状态0、不限制部门时，状态0会散落在不同部门之下，无法直接按“从部门开始的一个连续前缀”定位。因此不能把这个候选当成专门按状态查询的等价替代。也不要说成“任何情况下都不会用这个索引”：可能扫整个索引，或使用后面拓展里说明的特殊优化。

对于本课这条查询，部门、状态都是等值，二者互换也可能形成有效候选。先放部门，是因为我们同时考虑“按部门看办件”的查询； 并不是永远把区分度最高的列放第一位 。还要看其他查询能否复用这个前缀，再用实际计划比较。[1]

可以在这里暂停：先不背名词，只用上表解释“为什么状态0不一定全挨在一起”。下一节仍用同一条查询。

四、范围、覆盖与下推：分别减少哪一步

“范围查询后面的列是不是全失效？覆盖索引和索引下推一样吗？”

1. 先区分“缩小要扫的区间”和“在扫到的记录里继续筛选”

两个候选都可能沿索引查找。差别不是“先筛选还是先走索引”，而是索引圈出的区间是否已排除其他状态；没有排除的记录还要继续检查。

当前候选先固定部门10、状态0，再对时间做范围查找。假如换成 (dept_id, submitted_at, status)，先固定部门10，再取10月1日至8日之前的时间区间，区间里可能同时有状态0和1。状态排在时间之后，这时通常不能继续用它把这个范围压成只含状态0的一个更小区间，但仍能对扫到的记录检查状态。[4]

所以准确说法是： 这里的时间范围限制了后续列参与区间构造，不代表后续列完全没用 。后面的状态还可能参与过滤，或者为查询提供返回字段。上述判断针对本课简单的等值＋时间范围，不推成所有表达式都相同的口诀。

2. covering index（覆盖索引）：需要的信息已经在索引里

第一节只返回编号、部门、状态和提交时间。三个业务列在二级索引里，编号作为主键也在里面，因此这个索引在字段上覆盖了查询：无需为了取得其他业务列再取整行。若加上 applicant，当前索引就不再覆盖。covering index（覆盖索引）是 查询与索引的关系 ，不是另一种固定树结构。[3][5]

不要为了覆盖所有查询把所有列塞进索引。索引变宽会占更多空间，插入、删除和相关更新也要维护索引。先确认页面究竟要哪些字段，尽量避免不必要的 SELECT *，再权衡收益。[8]

3. ICP（索引条件下推）：先筛掉，再少取整行

假设查询还要申请人，且采用“部门、时间、状态”的候选。扫描时间区间时，索引中已有状态，存储引擎可以先检查状态0，排除状态1，再为通过的记录读取整行中的申请人。这就是 ICP（索引条件下推）要减少的工作： 仍可能回表，只是先过滤以减少回表 。是否采用由具体查询与执行计划确认。[9]

五、怎样验证：有候选，不等于已经优化成功

“索引建好了，你拿什么证明有效？”

optimizer（查询优化器）负责选择访问方案，会比较成本。小表或者一次要读大部分记录时，扫描表可能更合适。执行计划是选择的方案；实际执行是它在某次数据和环境上的表现。二者要分开看。[3][5]

先用 EXPLAIN FORMAT=TRADITIONAL（表格执行计划）看候选查询。显式指定格式，便于对照下表；字段全表不用一次背完。

EXPLAIN FORMAT=TRADITIONAL
SELECT id, dept_id, status, submitted_at
FROM case_item
WHERE dept_id = 10 AND status = 0
  AND submitted_at >= '2026-10-01'
  AND submitted_at <  '2026-10-08';

验证思路：在测试表加索引前后分别记录计划，查询保持相同。不要为“看到理想输出”强制指定索引。本课没有在数据库上运行这条查询，以下只解释字段，不提供虚构输出。[5]

EXPLAIN ANALYZE（实际执行分析）会运行查询，再给出执行节点的实际耗时、返回行数和循环次数。它会产生真实执行负载，先在测试环境使用；每个节点的实际返回行数也不能一概叫“全表扫描行数”。本课先看估计与实际是否相差明显，不要求背完树状输出。[6]

版本注意：MySQL 8.0 官方文档写明 EXPLAIN ANALYZE（实际执行并分析） 是 MySQL 8.0.18 才引入的[10]。如果使用的数据库还是 5.7，或早于 8.0.18 的 8.0，就只能先看普通 EXPLAIN（查看执行计划的语句） 的估计值，再用慢查询记录、实际耗时对比来验证。具体以所用数据库的实际版本为准。

比较时保留同一查询、相近数据量和分布，并考虑缓存、并发和重复运行的波动；同时核对返回结果是否一致、写入维护成本是否可接受。这些是本课的验证方法，不是官方保证的提速比例。

六、一次完整应用：从6行数据推到候选索引

“给你一条慢查询和几行样例数据，你怎么推出候选索引，又怎么确认它对？”

使用第三节的6行表，假设每行另有不同的申请人姓名。完成下面三步后再看答案。这是索引顺序与结果的纸上演示，不是数据库性能实验。

1. 第一节查询应该返回哪些编号？
2. 在 (dept_id, status, submitted_at) 和 (dept_id, submitted_at, status) 中，先选哪个作为候选？用105号说明区别。
3. 现在页面要显示申请人姓名，原索引还覆盖吗？你会立即把姓名加进索引吗？

CREATE TABLE teach_case_item_0010 (
  id BIGINT PRIMARY KEY,
  dept_id INT NOT NULL,
  status TINYINT NOT NULL,
  submitted_at DATETIME NOT NULL,
  applicant VARCHAR(80) NOT NULL
) ENGINE=InnoDB;

INSERT INTO teach_case_item_0010 VALUES
(101,10,0,'2026-09-30 12:00:00','甲'),
(102,10,0,'2026-10-01 00:00:00','乙'),
(103,10,0,'2026-10-03 12:00:00','丙'),
(104,10,0,'2026-10-08 00:00:00','丁'),
(105,10,1,'2026-10-02 12:00:00','戊'),
(106,20,0,'2026-10-03 12:00:00','己');

CREATE INDEX idx_dept_status_time
ON teach_case_item_0010 (dept_id, status, submitted_at);

七、检验：先关掉上面，凭记忆做

6 道题，每题三个选项字数相同。先回答，再展开核对；做完一次即可，不用刷到全对才继续表达。

1. 本课二级索引中已有部门、状态、时间和主键，查询另外要申请人，通常还要做什么？

• A. 直接从索引取姓名
• B. 根据主键查找整行
• C. 根据状态计算姓名

2. 对本课联合索引，判断最左前缀首先看什么？

• A. 索引定义的列顺序
• B. 查询条件书写顺序
• C. 查询结果返回顺序

3. “部门、时间、状态”索引先按部门等值和时间范围扫描。后面的状态列怎样理解？

• A. 后续列值完全无用
• B. 保证缩小扫描区间
• C. 仍可参与条件过滤

4. 第一节查询没有申请人，三个业务列和主键全在当前二级索引里。字段关系上属于什么？

• A. 必须返回整行取值
• B. 当前索引覆盖查询
• C. 当前索引完全失效

5. 普通表格执行计划里的 rows（估计检查行数）=500，表示什么？

• A. 估计需要检查行数
• B. 实际已经扫描行数
• C. 最终必定返回行数

6. 看到 Using index condition（索引条件下推），正确理解是？

• A. 所需字段全部覆盖
• B. 必定没有整行读取
• C. 先筛索引再读取行

八、用完整回答检验理解

连续回答四问，总共 3 分钟左右。可以写下来，也可以先口述再整理成文字；选择题答对只证明能辨认，不自动代表能独立讲清楚。写完再对照各节的参考回答。

1. 按部门、状态和时间区间查办件，你提出什么候选索引？为什么这样排？
2. 现有二级索引保存部门、状态、提交时间及主键编号。①页面只显示这四项，是否还要按编号查整行？②页面再增加申请人姓名（索引中没有），需要多做哪一步？先答这两个小场景；范围后的状态过滤留到第四节单独复习。
3. 有人说“用了索引，肯定快了”，你会补哪些验证？如果手上没有真实的优化数据，怎样如实说明？
4. 举一类可能需要这样索引的业务查询，说出查询条件、候选索引和要注意的代价。可参考第九节末尾的候选场景。

练习：请在自己的笔记中写出答案，再展开参考内容核对。

九、常见追问

先答，再展开

应用场景：可能用在哪里（候选）

十、隔日复测：明天换条件再答（约 5 分钟）

建议隔一天、开始新内容前用3分钟完成。约一周后再换一条查询复述；这些是复习建议，不阻挡新专题。

主来源与引用

本课末尾的课后问答：范围定位、覆盖与验证。

技术定义以8.4官方文档为准；本课的编号、办件数据、候选比较和验证步骤属于教学应用。索引速查表保留关键规则与易混点。

1. 联合索引及最左前缀：第三节主读。

2. 聚簇与二级索引：主键、行数据和二级索引中的主键。

3. 索引用途：覆盖、小表和大比例扫描。

4. 范围优化：多列区间构造与特殊跳跃扫描。

5. 执行计划输出：字段含义、覆盖与下推的区别。

6. 执行计划语句：实际运行分析。

7. 索引物理结构：叶子页及默认页大小。

8. 索引成本：空间及插入、更新、删除维护。

9. 索引条件下推：先判断索引条件，再读取整行。

10. MySQL 8.0 执行计划语句：EXPLAIN ANALYZE（实际执行并分析） 自 8.0.18 引入。2026-10-06 核对。

学完后，可以沿同一个办件例子，用自己的话复述扫描区间、回表与覆盖的区别；卡住时回到第三节的排序表，找出是哪一行数据或哪一步访问路径没想通。下一课进入事务边界与隔离级别，范围定位与覆盖会在那里的热身中再回顾。

课后问答：理解是怎样变化的

问题与结论默认可见。展开后可看理解过程；以下根据真实交流整理，并非逐字稿。

查询条件是部门等值、状态等值、提交时间范围。联合索引按“部门、状态、时间”和按“部门、时间、状态”排列，差别在哪里？

最终结论： 把部门、状态两个等值条件放在前面，再按时间做范围查找，可以先定位到指定部门、指定状态的一段，再在其中找时间范围。时间排在状态前面时仍可能使用索引，只是时间区间里可能混入其他状态，需要继续过滤。

二级索引里已有部门、状态、提交时间和主键编号。页面只显示这四项，与页面再加一个申请人姓名，访问上有什么不同？

最终结论： 只显示编号、部门、状态、时间时，所需字段都在索引里，构成 covering index（覆盖索引），不必为了额外字段再读整行。再加申请人姓名时，索引不再覆盖，需要按主键编号到聚簇索引取整行，这一步就是 table lookup（回表）。

加了索引、用 EXPLAIN ANALYZE（实际执行分析）看过计划，怎样才能说明这个索引确实有效？

最终结论： 用同一查询在修改前后比较实际执行节点的行数与耗时，核对结果一致，并考虑多次运行的波动；估计行数和实际行数分开说。索引名出现在计划里、估计行数下降，都不能单独证明已经变快。

只有“按部门管理”“按状态查看”这类场景线索时，怎样提出具体的索引候选？加索引的代价应该怎么说？

最终结论： 可以这样表达：“如果常用查询是部门等值、状态等值、时间范围，我会先提出部门、状态、时间的候选，再看其他查询能否复用和实际效果。加索引也会增加空间及写入维护成本。”这是假设方案，需要用具体查询和验证结果支撑。