Day 2｜MySQL 索引与 EXPLAIN：BizeNova SQL 优化实战

Day 2 的核心不是背“索引会让查询更快”，而是建立一条可验证的工程链路：真实 SQL → EXPLAIN → 提出假设 → 改 SQL/索引 → EXPLAIN ANALYZE → 重复压测 → 决定是否上线。

昨天我们解决了“请求进来后任务怎样执行”；今天把视线移到数据库：数据为什么能查得快，索引为什么有时反而没用，以及怎样用证据证明优化有效。

一、今天要形成的完整认知模型

业务访问模式
    ↓
WHERE 等值 / 范围 + ORDER BY + LIMIT + SELECT 列
    ↓
设计索引列顺序
    ↓
EXPLAIN 看优化器计划
    ↓
EXPLAIN ANALYZE 看真实执行
    ↓
足量数据 + 多轮压测
    ↓
保留、调整或放弃索引

索引优化的对象从来不是一张孤立的表，也不是某个单独的 WHERE 字段，而是一类稳定的业务查询模式。索引还会增加磁盘占用，并让 INSERT、UPDATE、DELETE 多维护一棵树，所以“多建几个总不会错”恰恰是危险思路。

二、为什么 InnoDB 常用 B+Tree

1. 数据库真正昂贵的是什么

数据库数据按页管理。相较于内存比较，随机读取磁盘页或从 Buffer Pool 之外取页更昂贵。索引的第一目标不是把比较次数从 30 次减到 20 次，而是用较少的层级定位目标页。

B+Tree 的非叶子节点主要保存键与子节点指针，一个页能容纳很多分支，扇出高，因此大量数据也能维持很低的树高。所有数据项位于同一深度的叶子层，查询路径稳定；叶子节点又按键有序相连，范围扫描和顺序读取都很自然。

                    [30 | 60]
                  /     |      \
        [10 | 20]   [40 | 50]   [70 | 80]
          ↓             ↓            ↓
 [1...29]  ⇄  [30...59]  ⇄  [60...90]
              有序叶子链

为什么不是普通二叉搜索树？二叉树扇出只有 2，数据量大时层数高；若不平衡还可能退化。为什么不是 Hash？Hash 擅长等值定位，但天然不保序，不适合 BETWEEN、>、排序、最左前缀与范围分页。B+Tree 是点查、范围、排序和页读取成本之间的工程折中。

三、聚簇索引、二级索引与回表

1. 聚簇索引

InnoDB 每张表都有一个聚簇索引，其叶子记录保存完整行。通常主键就是聚簇索引；没有主键时会选择第一个所有列均为 NOT NULL 的唯一索引，再没有则创建隐藏的 6 字节行 ID。MySQL 官方文档也明确说明了这个选择顺序。

PRIMARY KEY(id)
       ↓
B+Tree 叶子：[id | 行的其他全部列]

主键查询沿树定位到叶子页时，行数据就在这里。

2. 二级索引

主键以外的 InnoDB 索引是二级索引。它的叶子记录保存“二级索引列 + 主键值”，而不是一份完整行。

idx_industry(industry)
        ↓
二级索引叶子：[industry='AI' | id=9527]
        ↓  若还需要 company_name 等非索引列
PRIMARY KEY B+Tree 再查 id=9527
        ↓
取得完整行

从二级索引拿到主键，再访问聚簇索引取整行，就是回表。主键过长还会被复制到每棵二级索引中，使索引整体膨胀，因此业务自然主键很长时，经常仍会选择短、稳定的代理主键。

3. 覆盖索引

如果查询所需的所有列都能从一棵索引得到，就不必回表：

CREATE INDEX idx_industry_status_name
ON company(industry, status, name);

SELECT name
FROM company
WHERE industry = 'AI' AND status = 1;

这种索引叫覆盖索引，传统 EXPLAIN 的 Extra 常显示 Using index。不要把它和 Using index condition 混淆：后者是索引条件下推，存储引擎先用索引元组过滤，减少读取完整行，但仍可能回表。

四、联合索引与最左前缀不是口诀

假设有索引：

KEY idx_industry_status_created (
  industry,
  status,
  created_at
)

它可以理解为一个按拼接键排序的序列：先比较 industry；相同时比较 status；两者仍相同时才比较 created_at。

AI, 0, 2026-09-01
AI, 1, 2026-08-20
AI, 1, 2026-09-03
制造, 0, 2026-08-30
制造, 1, 2026-09-02

因此：

条件	能否有效使用	原因
WHERE industry=?	能	命中第一个索引列
WHERE industry=? AND status=?	能	连续使用左侧两列
WHERE status=?	通常不能做高效前缀定位	全局并非按 status 单独有序；新版本有时可能选择 skip scan，但不能依赖猜测
WHERE industry=? AND created_at>?	industry 可定位；created_at 通常不能继续形成连续范围	中间跳过了 status，created_at 可能只参与索引条件过滤
WHERE industry=? ORDER BY status,created_at	通常可以利用索引顺序	industry 已固定，后两列的排序与索引顺序一致

MySQL 官方将联合索引描述为按各列值拼接后排序，并说明第一列、前两列、前三列等左侧前缀可用于查找。

再注意一个边界：若前导列出现范围条件，例如 industry='AI' AND status>0 AND created_at=?，索引通常能在 industry/status 上形成范围，但范围后的 created_at 往往不能继续缩小 B+Tree 定位区间。它仍可能用于 ICP、覆盖或排序的一部分，因此最终以 EXPLAIN 为准，而不是机械判定“完全失效”。

五、EXPLAIN 到底在看什么

EXPLAIN
SELECT *
FROM company_evaluations
WHERE company_id = 42 AND deleted = 0
ORDER BY evaluation_date DESC, create_time DESC
LIMIT 20;

1. 七个核心字段

字段	应该怎样读
type	表的访问方式，不是 SQL 类型
possible_keys	优化器认为可能使用的索引；出现不代表最终会用
key	实际选择的索引，NULL 表示没选普通索引
key_len	实际参与访问的键长度，受类型、可空性、字符集和已使用索引列影响
rows	优化器估算需要检查的行数，不是真实行数
filtered	估算经过表条件后保留的百分比
Extra	排序、临时表、覆盖索引、额外过滤等补充信息

官方定义中，rows 明确是估算检查行数，key 才是最终选中的索引。读计划时不要只看“key 不为空”就宣布优化完成。

2. 常见 type

从今天需要掌握的范围看：

• const：主键或唯一索引所有部分与常量等值比较，最多一行。
• ref：使用非唯一索引或联合索引左前缀等值查找，可能匹配多行。
• range：在一个索引区间中扫描，例如 >、<、BETWEEN、IN 的适用情况。
• index：扫描整棵索引。它可能比整表窄，但依然是全索引扫描。
• ALL：全表扫描。大表高频查询应警觉，但几十行的小表一次扫完可能比绕索引回表更便宜。

3. 常见 Extra

• Using index：覆盖索引，仅凭索引树返回所需列。
• Using where：读出记录后还需执行 WHERE 条件过滤，本身不等于坏计划。
• Using index condition：索引条件下推，先在存储引擎层过滤索引元组。
• Using filesort：需要额外排序过程；名字是历史术语，不等于一定写磁盘。
• Using temporary：需要内部临时表，常见于某些 GROUP BY、DISTINCT、复杂排序。
• Using intersect(...)：多个索引通过 index merge 取交集；不一定差，但联合索引经常更贴合固定组合条件和排序。

4. 为什么还要 EXPLAIN ANALYZE

普通 EXPLAIN 展示优化器的估计和计划，不真正告诉你每个迭代器跑了多久、返回多少行。MySQL 8 的 EXPLAIN ANALYZE 会实际执行查询，提供估计行数、真实行数、首行时间、总时间和循环次数。

EXPLAIN ANALYZE
SELECT ...;

注意它会真正执行语句；学习阶段优先用于安全的 SELECT，生产环境也要评估查询成本。

六、BizeNova 三条真实 SQL

本次没有凭空造 SQL，而是从项目现有代码选择企业列表、用户分页和企业评估历史三条链路。实验使用隔离的 MySQL 8.1，每张相关表 300,000 行合成数据；每条查询预热 5 次、计时 60 次，并记录 EXPLAIN ANALYZE。

这是本地、单连接、热缓存下的合成数据对比，不是生产 P95。它能证明执行计划是否改善，但简历或面试中必须带上实验边界。

SQL 1：企业列表筛选——候选索引被否决

SELECT *
FROM companies
WHERE deleted = 0
  AND verification_status = 'approved'
  AND industry = 'AI'
ORDER BY create_time DESC
LIMIT 20;

现有结构为几个单列索引，优化前选择 index_merge，对 12,000 个候选结果过滤并 Using filesort：

阶段	type	真实候选行	median	P95
原索引	index_merge	12,000	205.439 ms	266.514 ms
添加 (industry,verification_status,deleted,create_time) 后	仍是 index_merge	仍为 12,000	199.576 ms	224.411 ms

候选联合索引并没有被优化器采用，执行路径、候选行和 filesort 都没改变。两组延迟的差异不能排除缓存和机器波动，因此本次不添加这个索引。一个成熟的优化结果可以是“有证据地不改”。

代码层还有更明确的问题：公司分页完成后，服务按每条公司的 userId 再查用户手机号，形成 N+1 查询。20 条列表可能变成 1+20 次访问；应批量查用户后构建 Map，或用一次 JOIN/DTO 查询。

SQL 2：用户分页——有效，但暂缓上线

SELECT id, phone, email, nickname, role, status, create_time
FROM users
WHERE deleted=0 AND role='enterprise' AND status='active'
ORDER BY create_time DESC
LIMIT 20;

候选索引：

(deleted, role, status, create_time DESC)

阶段	type/key	真实扫描	median	P95
优化前	index / idx_create_time	1,082	2.802 ms	3.487 ms
优化后	ref / 联合索引	20	1.017 ms	1.516 ms

P95 在本机下降 56.5%，计划也确实改善。但这是一条动态 SQL，role/status 都是可选条件，后台流量也可能不高。额外索引是否值得，需要生产访问频率和写入成本支持，所以本次只保留为候选。

SQL 3：企业评估历史——本次正式实施

项目当前查询：

SELECT *
FROM company_evaluations
WHERE company_id = 42 AND deleted = 0
ORDER BY evaluation_date DESC, create_time DESC
LIMIT 20;

原来只有 company_id、deleted 等单列索引。优化前 MySQL 选择两个索引求交集，再过滤和排序：

type:  index_merge
key:   idx_company_id, idx_deleted
Extra: Using intersect(...); Using where; Using filesort

新增与查询形状一致的索引：

INDEX idx_company_deleted_eval_created
  (company_id, deleted, evaluation_date DESC, create_time DESC)

实验中如果两个旧单列索引都保留，MySQL 8.1 仍错误偏好较慢的 index merge。因此在确认联合索引左前缀能接替 company_id 查询、且没有关键链路单独依赖低选择性的 deleted 后，一并移除 idx_company_id 和 idx_deleted。

阶段	type	Extra	真实扫描	median	P95
优化前	index_merge	intersect + where + filesort	约 1,000 行结果；deleted 索引读约 296,949 项	90.430 ms	109.944 ms
优化后	ref	无 filesort	20	1.051 ms	1.723 ms

在这组本地合成数据中，结果行扫描约从 1,000 降到 20，P95 从 109.944 ms 降到 1.723 ms，下降约 98.43%。它同时满足“计划改变、扫描量减少、多轮计时改善”，所以成为今天唯一真正落地的索引优化。

生产上线仍要做低峰 DDL、磁盘/锁/复制延迟评估，并在预发布使用真实数据分布复测；这组数字不能伪装成线上收益。

七、为什么不能给所有 WHERE 字段建索引

1. 低选择性：例如绝大多数记录 deleted=0，单列索引可能先读几乎全表，再大量回表。
2. 返回比例太高：需要表中大部分行时，顺序扫描可能更便宜。
3. 小表：整表只有几十行，优化器选择 ALL 完全合理。
4. 写放大：每次写入都要维护相关 B+Tree，索引越多写得越慢。
5. 占空间和缓存：冗余索引挤占磁盘和 Buffer Pool。
6. SQL 写法不匹配：LIKE '%词'、对列做函数、隐式类型转换都可能妨碍定位。
7. 索引顺序错误：三个单列索引不等价于一个匹配过滤与排序的联合索引。
8. 优化器基于成本选择：possible_keys 有候选，不代表 key 会选择它。

索引列顺序的常用思考方式是：高频等值条件在前，随后考虑范围条件，再让排序列与 LIMIT 配合；同时结合选择性、动态条件、覆盖需要和其他查询复用。它不是“选择性最高永远放第一”的单一公式。

八、分页的另一个坑：大 OFFSET

SELECT *
FROM company_evaluations
WHERE company_id=? AND deleted=0
ORDER BY evaluation_date DESC, create_time DESC, id DESC
LIMIT 100000, 20;

即便有索引，数据库通常仍需找到并丢弃前 100,000 行。适合连续翻页时可用游标/Seek Pagination：

SELECT *
FROM company_evaluations
WHERE company_id=? AND deleted=0
  AND (evaluation_date, create_time, id) < (?, ?, ?)
ORDER BY evaluation_date DESC, create_time DESC, id DESC
LIMIT 20;

相应索引可扩展为 (company_id, deleted, evaluation_date DESC, create_time DESC, id DESC)。要保证排序唯一、处理首尾页和筛选条件变化；需要任意跳页时仍要权衡 OFFSET、缓存、预计算或产品交互。

九、算法联动：二分查找

B+Tree 和二分查找共享一个重要思想：利用有序性不断缩小搜索空间。B+Tree 不是二叉树，但每个节点内的有序键和多路分支，同样避免线性扫描全部记录。

LeetCode 704：二分查找

class Solution {
    public int search(int[] nums, int target) {
        int left = 0, right = nums.length - 1;
        while (left <= right) {
            int mid = left + ((right - left) >>> 1);
            if (nums[mid] == target) return mid;
            if (nums[mid] < target) left = mid + 1;
            else right = mid - 1;
        }
        return -1;
    }
}

循环不变量：答案若存在，一直在闭区间 [left,right] 中。left <= right 对应闭区间仍非空；mid + 1 与 mid - 1 必须排除已经比较过的中点。中点写法避免 left + right 溢出。

LeetCode 34：第一个和最后一个位置

把问题统一成 lowerBound：寻找第一个 >= target 的位置。左边界是 lowerBound(target)，右边界是 lowerBound(target + 1)-1，但 target+1 可能整数溢出，因此更稳妥的是分别寻找第一个 >= target 与第一个 > target。

class Solution {
    public int[] searchRange(int[] nums, int target) {
        int first = lowerBound(nums, target, false);
        if (first == nums.length || nums[first] != target) {
            return new int[]{-1, -1};
        }
        int afterLast = lowerBound(nums, target, true);
        return new int[]{first, afterLast - 1};
    }

    // strict=false: 第一个 >= target；strict=true: 第一个 > target
    private int lowerBound(int[] nums, int target, boolean strict) {
        int left = 0, right = nums.length;
        while (left < right) {
            int mid = left + ((right - left) >>> 1);
            if (nums[mid] > target || (!strict && nums[mid] == target)) {
                right = mid;
            } else {
                left = mid + 1;
            }
        }
        return left;
    }
}

这里使用左闭右开区间 [left,right)：循环结束时两者相等，正好是第一个满足条件的位置。面试时，比背模板更重要的是说清区间定义和循环不变量。

十、面试题与参考答案

1. 为什么 MySQL 索引常用 B+Tree，而不是普通二叉树？

B+Tree 一个节点能容纳多个键，扇出高、树高低，能用更少的页访问定位记录；叶子有序相连，既支持等值查找，也支持范围与排序。普通二叉树层数更高且可能失衡，Hash 又不适合范围和有序访问。

2. 聚簇索引和二级索引有什么区别？

InnoDB 聚簇索引的叶子保存完整行，通常由主键承担；二级索引叶子保存索引列和主键。用二级索引查到主键后，再访问聚簇索引取得其他列，就是回表。一张表只有一个聚簇索引，但可以有多棵二级索引。

3. 什么是回表？怎样减少？

二级索引没有查询所需的全部列时，根据叶子中的主键再查聚簇索引，这一步叫回表。可通过提高过滤精度、覆盖索引、减少无用 SELECT 列来减少，但不应把大量列全塞进索引，因为会增加体积和写成本。

4. 什么是覆盖索引？

一次查询需要的筛选、排序和返回列都能从同一索引获得，无需读完整行。Extra 常见 Using index。覆盖索引是“索引相对于某条查询”的属性，不是独立索引类型。

5. 联合索引为什么有最左前缀？

因为联合键先按第一列排序，第一列相同时才按第二列，以此类推。跳过左侧列后，后列在全局上不连续，通常无法直接形成高效定位区间。实际是否出现 skip scan、ICP 或其他优化仍要看执行计划。

6. EXPLAIN 重点看哪些字段？

先看 type 和实际 key，再结合 key_len 判断用了联合索引的哪些部分；看 rows × filtered 理解估算候选量；最后看 Extra 是否有覆盖、额外过滤、排序、临时表或 index merge。复杂查询还要按表的读取顺序整体看，不能孤立判断一行。

7. 给所有 WHERE 字段建索引一定更快吗？

不一定。索引可能选择性低、返回数据多、触发大量回表，优化器也可能认为全表扫描更便宜；索引还增加写入、存储和缓存压力。应围绕高频查询组合设计，并用 EXPLAIN、真实数据分布和压测验证。

8. Using filesort 一定使用磁盘吗？

不一定。它表示 MySQL 不能直接按选定索引顺序产生结果，需要额外排序；排序可能在内存，也可能因数据量和配置使用临时文件。优化时看参与排序的行数、LIMIT、内存和真实耗时。

9. possible_keys 有索引，为什么 key 仍是 NULL？

优化器按统计信息估算成本。如果条件命中比例很高、表很小、回表代价大，扫描可能更便宜；统计信息不准也会造成误判。可先 ANALYZE TABLE 更新统计，再检查数据分布和 SQL，而不是立即强制索引。

10. 联合索引列顺序怎样确定？

从业务查询集合出发：哪些是稳定等值条件、哪些是范围、怎样排序、返回多少行、能否覆盖、还有哪些查询能复用左前缀。通常等值列在范围列之前，排序列与 LIMIT 配合，但还要衡量选择性、动态条件和写成本，没有万能排序公式。

十一、今天可用于项目介绍的表述

严谨版本：

针对 BizeNova 企业评估历史分页，从真实 Mapper SQL 出发设计 (company_id, deleted, evaluation_date, create_time) 联合索引；在 MySQL 8.1、30 万行本地合成数据上通过 EXPLAIN ANALYZE 和 60 轮计时验证，实际结果扫描约从 1,000 行降至 20 行，消除 index merge 与 filesort，P95 从 109.944 ms 降至 1.723 ms。该数据为本地实验结果，生产收益需预发布复测。

这比“熟悉 MySQL 索引优化”更有说服力，因为它包含场景、方法、执行计划、量化结果和边界。

十二、闭卷验收清单

• 能画出二级索引叶子保存主键，再回到聚簇索引取完整行的过程。
• 能解释联合索引的排序方式，而不是只背最左匹配口诀。
• 能独立读 type / possible_keys / key / key_len / rows / filtered / Extra。
• 知道 rows 是估算，EXPLAIN ANALYZE 才展示实际执行信息。
• 能解释为什么企业列表候选索引没有落地。
• 能讲清企业评估历史联合索引为何同时服务过滤、排序与 LIMIT。
• 能手写 704，并用 lowerBound 思路完成 34。
• 能回答“索引是不是越多越好”，并从读写成本、选择性、回表和优化器四方面展开。

参考资料

• MySQL 8.4：聚簇索引与二级索引
• MySQL 8.4：联合索引
• MySQL 8.4：EXPLAIN 输出格式
• MySQL 8.4：EXPLAIN 与 EXPLAIN ANALYZE

下一天再进入事务、隔离级别、undo log、Read View 与 MVCC。今天先确保“索引 → SQL → EXPLAIN → 项目实验 → 可量化表达”这条链已经闭环。