索引

S3-003必学进阶约 90 分钟JDK 21企业开发基础 · MySQL 与数据建模

1. 本节定位

索引是数据库性能的第一杠杆——同一个查询，有没有索引可能差上百倍。

本节用真实的数据库引擎实测：全表扫描与索引扫描的执行计划、耗时、缓冲区读取量的差别，然后讲清"索引为什么快""什么时候用不上""代价是什么"。

关于演示环境：《SQL 基础》与《表设计与约束》用 H2 演示 SQL 语法（可复现），但索引与执行计划必须用真实数据库——本节改用你本机的真实数据库引擎实测。讲义里标注了 PostgreSQL 与 MySQL 的行为差异，这本身就是重要知识。

2. 学习目标

完成本节后，你应该能够：

说出索引为什么能让查询变快（用"目录"的类比）；
读懂 EXPLAIN 的关键信息：扫描方式、估算代价、实际耗时、缓冲区读取量；
说出复合索引的"最左前缀"规则，并知道 PostgreSQL 与 MySQL 在这点的差异；
说出四种"索引失效"的写法，并知道怎么改；
说出索引的代价（写入变慢、占空间），并判断"这个索引该不该建"。

3. 前置知识检查

会写 CREATE INDEX 吗？（《表设计与约束》）
知道主键与唯一约束会自动建索引吗？（《表设计与约束》的 5.1 节）

4. 为什么需要这个知识

索引是"用空间换时间"的经典权衡，而它的效果用数字说话最清楚。本节实测的同一张表（10 万行）、同一条查询：

指标	没有索引	有索引	差距
扫描方式	`Seq Scan`（全表）	`Index Scan`（索引）	—
估算代价	`cost=0.00..1104.00`	`cost=0.42..8.44`	131 倍
读取的缓冲区块	`736`	`4`	184 倍
单次执行时间	`7.045 ms`	`0.062 ms`	113 倍
执行 30 次总耗时	`231 ms`	`16 ms`	14 倍

为什么总耗时的倍数（14）小于单次（113）：因为批量执行时数据进了缓存（shared hit），全表扫描的第二次、第三次不再从磁盘读——这也是"缓存能救一部分性能问题"的体现。但即便有缓存，全表扫描仍要遍历所有行。

5. 核心概念

5.1 索引是什么

类比：索引就是书的目录。

	没有目录	有目录
找"某个关键词"	从第 1 页翻到最后一页	查目录 → 直接翻到那一页
代价	慢	快
但	不用维护目录	改内容时要同步改目录

技术上的实现：绝大多数数据库索引是 B+ 树——一种"有序的多叉树"，能在 O(log n) 的复杂度内定位数据（10 万行只需约 17 次比较）。

这不是本文的重点——重点是怎么用：

关注点	问题
建什么索引	哪些查询需要加速？
什么时候用不上	为什么我建了索引还是全表扫描？
代价是什么	多了索引，写入会慢多少？

5.2 两种扫描方式

EXPLAIN 的输出里，第一行就是扫描方式——这是最该看的信息：

扫描方式	含义	什么时候出现
`Seq Scan`	全表扫描（逐行读）	没有可用索引，或"用索引更慢"
`Index Scan`	通过索引定位 → 回表取数据	条件命中索引、且返回行数少
`Bitmap Index Scan`	先扫索引建位图 → 再按物理顺序回表	返回行数中等（几十到几千行）
`Index Only Scan`	只读索引就够了（覆盖索引）	查询的列都在索引里

Seq Scan 不一定是坏事：当查询要返回表中大部分行时（如 WHERE age > 18 命中 90% 的行），全表扫描比"走索引再回表"更快——优化器会算这笔账。

这解释了一个常见困惑："我建了索引，为什么 EXPLAIN 还是 Seq Scan？" → 因为优化器认为全表扫描更划算。

5.3 读懂 EXPLAIN（本节最重要的技能）

text

Index Scan using idx_users_username on users_demo
    (cost=0.42..8.44 rows=1 width=29) (actual time=0.050..0.050 rows=1.00 loops=1)
  Index Cond: ((username)::text = 'user_99999'::text)
  Buffers: shared hit=1 read=3
Planning Time: 1.485 ms
Execution Time: 0.062 ms

片段	含义	怎么看
`Index Scan using idx_...`	用了哪个索引	没写索引名 = 没用索引
`cost=0.42..8.44`	估算代价（启动..总代价）	只用于比较，不同数据库不可比
`rows=1`	估算返回行数	与实际差距大 → 统计信息过期，需要 `ANALYZE`
`actual time=0.050..0.050`	实际耗时（毫秒）	只有 `EXPLAIN ANALYZE` 才有
`rows=1.00 loops=1`	实际行数 × 循环次数	`loops` 大说明这句被执行了很多次（嵌套循环）
`Index Cond`	索引上做的过滤	关键：条件是"索引条件"还是"过滤条件"
`Filter` / `Rows Removed by Filter`	索引没能过滤、逐行判定的部分	看到这个就要留意：说明索引没完全生效
`Buffers: shared hit=1 read=3`	命中缓存 / 读磁盘的块数	最能反映"实际读了多少数据"
`Execution Time`	实际执行时间	不含 Planning

两个最有用的判断：

Rows Removed by Filter 很大 → 索引没帮上忙（读了大量行又丢掉）；
Buffers 很大 → 实际读取量大，I/O 是瓶颈。

5.4 复合索引与最左前缀

复合索引：在多个列上建一个索引。

sql

CREATE INDEX idx_users_city_age ON users_demo (city, age);

"最左前缀"规则：这个索引相当于同时提供了 (city) 与 (city, age) 两种查找能力——但单独用 age 时，MySQL 用不上它。

本节的实测（PostgreSQL）出现了一个重要差异：

text

（a）city + age 两列都用：
  Bitmap Index Scan on idx_users_city_age
    Index Cond: ((city = '北京') AND (age = 30))

（b）只用 age（跳过最左列）：
  Bitmap Index Scan on idx_users_city_age        ← 仍然用上了索引！
    Index Cond: (age = 30)

为什么：PostgreSQL 对复合索引的任意列都能做位图索引扫描（如上面实测的 Bitmap Index Scan，把整条索引扫一遍按条件过滤），所以非最左列也"能用上"索引。MySQL 8.0.13+ 也支持跳扫（EXPLAIN 显示 Using index for skip scan），但限制较多：要求首列基数低（如性别、状态这类少枚举值），且优化器是否选用要看统计信息——不能指望它，最左前缀仍是索引设计的默认原则。

这条差异的实际意义：

数据库	只看非最左列	你要做什么
MySQL	⚠️ 8.0.13+ 有限支持（首列需低基数，通常指望不上）	为高频的单独查询再建一个索引（如 `(age)`）
PostgreSQL	✅ 可能用上（位图扫描）	索引可以少建一些

所以"照搬 PostgreSQL 的经验去设计 MySQL 索引"会踩坑——必须按目标数据库的行为验证（这也是本节坚持用真实引擎实测的原因）。

5.5 索引失效的四种写法

失效一：对列做函数或运算

sql

-- ✗ 索引失效：对 username 做了 UPPER()
SELECT * FROM users_demo WHERE UPPER(username) = 'USER_99999';

实测（10 万行）：

text

Seq Scan on users_demo  (cost=0.00..2236.00 rows=500 width=29)
  Filter: (upper((username)::text) = 'USER_99999'::text)
  Rows Removed by Filter: 99999
Execution Time: 39.971 ms          ← 比走索引慢 600 倍

为什么：索引里存的是原始值的有序结构；一旦对列做运算，索引的顺序就没用了。

修复：

方案	说明
改写 SQL	`WHERE username = 'user_99999'`（把函数挪到参数侧）
建函数索引	`CREATE INDEX ... ON users_demo (UPPER(username))`（MySQL 8 支持函数索引；PostgreSQL 也支持）
存冗余列	多存一列 `username_upper`，写入时维护

失效二：`LIKE` 以通配符开头

sql

SELECT * FROM users_demo WHERE username LIKE '%99999';       -- ✗ 索引失效
SELECT * FROM users_demo WHERE username LIKE 'user_9999%';   -- 前缀匹配（MySQL 里可走索引）

实测：

text

（LIKE '%99999'）
Seq Scan on users_demo  (cost=0.00..1986.00 rows=10)
Execution Time: 17.311 ms

（LIKE 'user_9999%'）
Seq Scan on users_demo  (cost=0.00..1986.00 rows=10)     ← PostgreSQL 默认排序规则下也没走索引
Execution Time: 10.756 ms

这里又是一处方言差异：

数据库	`LIKE 'prefix%'`	需要什么
MySQL	✅ 走索引（默认排序规则下）	无需特殊处理
PostgreSQL	❌ 默认不走	索引需用 `text_pattern_ops`，或列用 `C` 排序规则

共同点：LIKE '%...'（前置通配符）在两个数据库里都用不上普通索引——因为"以什么结尾"无法用有序结构快速定位。

失效三：隐式类型转换（MySQL 高发）

sql

-- phone 是 VARCHAR，参数是数字
SELECT * FROM users WHERE phone = 13800000001;        -- ✗ MySQL 会把列转成数字，索引失效
SELECT * FROM users WHERE phone = '13800000001';      -- ✓ 类型一致

这是 MySQL 的经典坑：列的类型与参数类型不一致时，MySQL 可能对"列"做转换 → 等价于"对列做函数运算" → 索引失效。

PostgreSQL 更严格：类型不匹配时直接报错（而不是默默转换），从设计上避免了这个问题。

修复：参数类型与列类型保持一致（这也是《表设计与约束》里强调"类型选择"的原因之一）。

失效四：`OR` 连接不同列

sql

SELECT * FROM users_demo WHERE city = '北京' OR age = 30;    -- 可能不走索引

原因：两个条件各自命中不同的索引，优化器要合并结果，可能直接选择全表扫描。

修复：拆成两条 UNION ALL（各自走索引），或用复合索引覆盖两列。

5.6 索引的代价

实测（同样插入 2 万行）：

text

无索引插入 20000 行：293 ms
有索引插入 20000 行：872 ms

约 3 倍的写入开销——因为每插一行，都要在索引里找到正确位置并插入（还可能需要分裂索引页）。

代价	说明
写入变慢	每次 `INSERT` / `UPDATE` / `DELETE` 都要维护索引
占空间	索引本身要占存储（复合索引可能达到表大小的几十 %）
优化器负担	索引太多时，优化器选择执行计划的时间变长
更新频繁的列	索引列的更新代价更高

"该不该建这个索引"的判断表：

情况	建议
高频查询的过滤条件	✅ 建
`ORDER BY` 的列	✅ 建（能避免排序）
`JOIN` 的关联列	✅ 建（外键列尤其）
唯一约束列	✅ 自动建
很少查询的列	❌ 不建
区分度极低的列（如性别、状态只有 2-3 个值）	❌ 通常没用（全表扫描更划算）
表很小（几百行）	❌ 没必要
写多读少的表	⚠️ 谨慎（写入代价可能超过收益）

一条经验：

索引是给"读操作"服务的——如果一张表每分钟写 1000 次、每小时才查一次，索引带来的收益远小于写入代价。

6. 递进示例

6.1 最小示例：建索引前后

sql

-- 无索引：Seq Scan
SELECT * FROM users_demo WHERE username = 'user_99999';

CREATE INDEX idx_users_username ON users_demo (username);
ANALYZE users_demo;

-- 有索引：Index Scan
SELECT * FROM users_demo WHERE username = 'user_99999';

注意 ANALYZE：刚建索引时统计信息还是旧的，优化器可能仍然选择全表扫描。生产环境里 ANALYZE 通常由自动清理任务定期执行。

6.2 业务示例：为"列表页查询"设计索引

需求：后台的订单列表支持"按用户 + 按状态 + 按时间倒序"筛选。

sql

-- 最常见查询：某用户的某状态订单，按时间倒序
SELECT id, order_no, amount_in_cents, created_at
FROM shop_order
WHERE user_id = 1 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

-- 对应索引：把 WHERE 的等值列放前面，ORDER BY 的列放后面
CREATE INDEX idx_order_user_status_time
    ON shop_order (user_id, status, created_at DESC);

设计要点：

要点	说明
等值条件在前	`user_id`、`status` 用 `=` 匹配，放前面
排序/范围条件在后	`created_at` 用于 `ORDER BY`，放最后
`DESC` 也写进索引	让数据库能直接用索引顺序，省掉排序步骤
`LIMIT` 配合	有了这个索引，查询只需读 20 行就停

6.3 反例：给每个列都建一个索引

"索引越多越快"是错的：

后果	说明
写入变慢（上面实测 3 倍，索引更多会更慢）	每多一个索引就多一次维护
占空间	索引可能比数据还大
优化器可能选错索引	可选项太多时，估算误差影响更大

正确做法：为高频查询建少量精准的索引，而不是为每个列都建。

7. 完整可运行代码

环境准备（本机需要有一个可用的 PostgreSQL 或 MySQL）：

powershell

# PostgreSQL：连接信息通过环境变量传入，不写进代码
$env:PGPASSWORD = "你的密码"
$env:PGDATABASE = "kb_learn"      # 提前建好的库

文件：labs/sql-advanced/src/main/java/cn/kb/learn/sql/adv/IndexLesson.java（节选）

java

public class IndexLesson {
    private static final int ROW_COUNT = 100_000;
    private static final int QUERY_TIMES = 30;

    private static void prepareData(PgRunner db) {
        db.execute("DROP TABLE IF EXISTS users_demo");
        db.execute("""
                CREATE TABLE users_demo (
                    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                    username   VARCHAR(32) NOT NULL,
                    city       VARCHAR(32) NOT NULL,
                    age        INT NOT NULL
                )
                """);
        db.execute("""
                INSERT INTO users_demo (username, city, age)
                SELECT 'user_' || i,
                       (ARRAY['北京', '上海', '广州', '深圳'])[1 + (i %% 4)],
                       18 + (i %% 50)
                FROM generate_series(1, %d) AS i
                """.formatted(ROW_COUNT));
    }

    private static void lightUp(PgRunner db) {
        String sql = "SELECT * FROM users_demo WHERE username = 'user_99999'";

        System.out.println("=== 没有索引 ===");
        System.out.print(db.explainAnalyze(sql));
        System.out.println("执行 " + QUERY_TIMES + " 次耗时：" + db.timeMillis(sql, QUERY_TIMES) + " ms");

        System.out.println("=== 建立索引后 ===");
        db.execute("CREATE INDEX idx_users_username ON users_demo (username)");
        db.execute("ANALYZE users_demo");
        System.out.print(db.explainAnalyze(sql));
        System.out.println("执行 " + QUERY_TIMES + " 次耗时：" + db.timeMillis(sql, QUERY_TIMES) + " ms");
    }
}

运行命令：

powershell

cd labs
./mvnw -pl sql-advanced compile exec:exec -Dexec.mainClass=cn.kb.learn.sql.adv.IndexLesson

预期输出（节选关键对比）：

text

=== 1. 准备 100000 行测试数据 ===
已插入 100000 行

=== 2. 没有索引：全表扫描（Seq Scan）===
Seq Scan on users_demo  (cost=0.00..1104.00 rows=147 width=176) (actual time=7.027..7.028 rows=1.00 loops=1)
  Filter: ((username)::text = 'user_99999'::text)
  Rows Removed by Filter: 99999
  Buffers: shared hit=736
Execution Time: 7.045 ms
执行 30 次耗时：231 ms

=== 3. 建立索引后再查 ===
Index Scan using idx_users_username on users_demo  (cost=0.42..8.44 rows=1 width=29) (actual time=0.050..0.050 rows=1.00 loops=1)
  Index Cond: ((username)::text = 'user_99999'::text)
  Buffers: shared hit=1 read=3
Execution Time: 0.062 ms
执行 30 次耗时：16 ms

=== 4. 复合索引与最左前缀 ===
（a）用最左列 city + 第二列 age：索引可用
  Bitmap Index Scan on idx_users_city_age
    Index Cond: (((city)::text = '北京'::text) AND (age = 30))
（b）跳过最左列、只用 age：PostgreSQL 18 仍可能用上这个复合索引
  Bitmap Index Scan on idx_users_city_age
    Index Cond: (age = 30)

=== 5. 索引失效：对列做函数运算 ===
Seq Scan on users_demo  (cost=0.00..2236.00 rows=500 width=29)
  Filter: (upper((username)::text) = 'USER_99999'::text)
Execution Time: 39.971 ms

=== 6. 索引失效：LIKE 以通配符开头 ===
Seq Scan on users_demo  (cost=0.00..1986.00 rows=10 width=29)
Execution Time: 17.311 ms

=== 8. 索引的成本：写入变慢 ===
无索引插入 20000 行：293 ms
有索引插入 20000 行：872 ms

三处最值得记住的数字：

对比	数字	含义
`Buffers` 736 → 4	读的块数差 184 倍	索引减少的是实际 I/O，这是它快的根本原因
`Rows Removed by Filter: 99999`	白读了 10 万行	这就是全表扫描的本质
写入 293 ms → 872 ms	索引让写入慢约 3 倍	索引不是免费的

8. Debug 观察点

只看 EXPLAIN 的第一行：从 Seq Scan 变 Index Scan——这是判断"索引有没有生效"最快的方法；
对比两次输出的 Buffers 字段：shared hit=736 → shared hit=1 read=3——"读了多少个数据块"比"耗时"更稳定（耗时受缓存、机器负载影响）；
在第 5 步（函数运算）的输出里找 Filter：Filter: (upper(...) = ...) + Rows Removed by Filter: 99999——看到这种组合就说明索引没起作用；
删掉 ANALYZE 后重跑第 3 步：观察优化器可能仍然选择全表扫描——这演示了"统计信息"的作用。

9. 常见错误

错误 1：建了索引但查询没走

排查顺序：

步骤	检查什么	命令
1	索引存在吗？	`SHOW INDEX FROM t`（MySQL）/ `\d t`（PG）
2	统计信息新鲜吗？	`ANALYZE t` 后重试
3	条件写法能用索引吗？	有没有函数、`LIKE '%'`、类型不一致
4	优化器为什么不用？	`EXPLAIN` 看 cost 对比
5	返回行数是不是太多了？	命中 30% 以上时全表扫描更划算

错误 2：以为"索引一定能加速"

见 5.2 节：返回大部分行时，全表扫描更快。

判断方法：EXPLAIN 里 rows（估算返回行数）与表总行数的比例——超过 20-30% 时，优化器通常会选全表扫描，而且这往往是对的。

错误 3：`LIKE` 用前置通配符

sql

WHERE name LIKE '%关键词%'      -- 索引失效

修复思路：

需求	方案
前缀搜索	`LIKE '关键词%'`（MySQL 可走索引）
全文搜索	用全文索引（MySQL `FULLTEXT`）或专门的搜索引擎（Elasticsearch）
后缀搜索	存一列倒序字符串（`reverse(name)`）再前缀匹配

关键认知：普通 B+ 树索引不适合"中间匹配"——这类需求要用专门的方案。

错误 4：索引列参与运算

sql

WHERE DATE(created_at) = '2026-09-21'          -- ✗ 失效
WHERE created_at >= '2026-09-21' AND created_at < '2026-09-22'   -- ✓ 范围查询

这是最容易被忽略的一类（尤其是日期）——把函数从"列"挪到"参数侧"。

错误 5：只建索引，忘了 `ANALYZE`

症状：刚建完索引，EXPLAIN 却还是 Seq Scan。

原因：优化器依赖统计信息来决定用不用索引，而统计信息是定期更新的。

修复：手动 ANALYZE 表名（生产环境通常由自动清理负责，但大变更后手动跑一次是好习惯）。

10. 动手实验

目标：亲手做一遍索引的效果对比，并观察优化器的选择。

步骤：

准备好本机数据库与环境变量，运行第 7 节的命令，核对关键数字；
只改索引的列：把索引改成 (username, city)，然后用 WHERE city = '北京' 查询——观察是否走索引（结合 5.4 节的最左前缀讨论）；
观察选择性对优化器的影响：分别查 city = '北京'（25% 的行）与 username = 'user_99999'（1 行），对比两次的扫描方式；
验证 ANALYZE 的作用：删掉索引重建、不执行 ANALYZE，观察 EXPLAIN 的结果；再执行 ANALYZE 重看；
验证写入代价：把 5.6 节的插入实验扩展到 3 个索引，记录写入时间的变化；
设计一个复合索引：为"某用户在某状态下的订单，按创建时间倒序"设计索引，并用 EXPLAIN 验证它被用上。

验收方式：能说出第 3 步两次的扫描方式差异，以及第 4 步 ANALYZE 前后的变化。

扩展任务：用 EXPLAIN ANALYZE 找出你项目里最慢的一条查询，判断它是"缺索引"还是"写法用不上索引"，并给出修改方案。

11. 练习题

基础题

判断：表上建的索引越多，数据库越快。（对 / 错）
填空：EXPLAIN 输出里，Seq Scan 表示 ______；Rows Removed by Filter 很大说明 ______。
简答：为什么 WHERE UPPER(name) = 'ABC' 用不上 name 上的索引？

代码阅读题

sql

CREATE INDEX idx_order ON shop_order (user_id, status, created_at);

-- 查询 A
SELECT * FROM shop_order WHERE user_id = 1;

-- 查询 B
SELECT * FROM shop_order WHERE status = 'PAID';

-- 查询 C
SELECT * FROM shop_order WHERE user_id = 1 AND created_at > '2026-01-01';

三个查询里，哪些能用上 idx_order？（以 MySQL 的最左前缀规则判断）
如果 status 的查询特别频繁，你会怎么调整索引？

编程题

给一个"文章列表"设计索引并验证：
- 表结构：article(id, author_id, status, published_at, title)，插入 10 万行；
- 需求：a) 某作者的文章列表（按发布时间倒序）；b) 某状态的文章列表（按发布时间倒序）；c) 某作者在某状态下的文章数；
- 为每个需求设计索引，用 EXPLAIN 验证是否被用上；
- 记录"建索引前 / 建索引后"的执行时间；
- 说明：为什么不能只为需求 a 建索引就完事？

综合任务

做一次"索引体检"：
- 选一张你项目里的表（或自己造一张 10 万行的表）；
- 列出它的全部索引（SHOW INDEX FROM t / \d t）；
- 写出 3-5 条高频查询，用 EXPLAIN 逐条判断"用了哪个索引、有没有走索引、Rows Removed by Filter 大不大"；
- 找出：有没有"该建没建"的索引？有没有"建了没用"的冗余索引？
- 给出改进方案（新增哪些、删除哪些），并说明每条的依据；
- 额外要求：估算一下新增索引会让写入慢多少（参考 5.6 节的实测比例）。

分级提示

提示 1（方向）：第 4 题按 MySQL 的最左前缀规则判断——索引 (user_id, status, created_at) 能被"以 user_id 开头"的条件用上。

提示 2（关键点）：第 6 题的需求 b（只按 status）在 MySQL 里用不上 idx_article_author_status_time，需要单独为 status 建索引。

提示 3（接近答案）：第 7 题的"冗余索引"常见形态是 (a) 与 (a, b) 同时存在——前者可以被后者覆盖（在最左前缀规则下），可以删掉。

12. 参考答案

基础题

错。索引让读变快，但让写变慢（本节实测约 3 倍），还占空间、增加优化器负担。索引要按查询需求精准建，不是越多越好。
全表扫描（逐行读取整张表）；索引没帮上忙（读了大量行又被过滤掉——Filter 阶段的产物）。
因为索引里存的是"原始值"的有序结构（如 'abc'、'abd' 的顺序）。

WHERE UPPER(name) = 'ABC' 要求对每一行先算 UPPER(name) 再比较——这个计算后的值不在索引里，所以索引的顺序完全用不上，只能全表扫描。

修复方案（三选一）：

方案	写法
改写 SQL	`WHERE name = 'abc'`（把函数挪走）
函数索引	`CREATE INDEX ... ON t (UPPER(name))`
冗余列	加一列存 `UPPER(name)`，写入时维护

代码阅读题（第 4、5 题）

第 4 题（按 MySQL 的最左前缀规则）：

查询	能用上 `(user_id, status, created_at)` 吗	说明
A：`WHERE user_id = 1`	✅	用了最左列
B：`WHERE status = 'PAID'`	❌	跳过了最左列 `user_id`
C：`WHERE user_id = 1 AND created_at > ...`	⚠️ 部分	能用 `user_id` 定位，但 `created_at` 用不上（中间跳过了 `status`）

查询 C 是这条规则里最微妙的情况：索引的排序是"先 user_id → 再 status → 再 created_at"。跳过了中间列，后面的列就无法用于定位（只能用于"过滤"或"排序"）。

第 5 题：为 status 单独建一个索引。

sql

CREATE INDEX idx_order_status_created ON shop_order (status, created_at);

注意为什么带上 created_at：因为业务上的"某状态列表"通常要按时间排序——把排序列放进索引可以省掉排序步骤。

为什么不直接改成 (status, user_id, created_at) 并删掉原索引：因为查询 A（只按 user_id）会失去索引支持 ✗ —— 两个高频查询需要两个索引。

这体现了一条经验：索引的设计要基于"高频查询的组合"，而不是"表的所有列"。

编程题（第 6 题）

sql

-- 表与数据
CREATE TABLE article (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author_id    BIGINT NOT NULL,
    status       VARCHAR(16) NOT NULL,
    published_at TIMESTAMP NOT NULL,
    title        VARCHAR(200) NOT NULL
);

INSERT INTO article (author_id, status, published_at, title)
SELECT (i % 1000) + 1,
       (ARRAY['DRAFT', 'PUBLISHED', 'ARCHIVED'])[1 + (i % 3)],
       TIMESTAMP '2026-01-01 00:00:00' + (i || ' minutes')::INTERVAL,
       'title_' || i
FROM generate_series(1, 100000) AS i;

-- 需求 a：某作者的文章列表（按发布时间倒序）
CREATE INDEX idx_article_author_time ON article (author_id, published_at DESC);

-- 需求 b：某状态的文章列表（按发布时间倒序）
CREATE INDEX idx_article_status_time ON article (status, published_at DESC);

-- 需求 c：某作者在某状态下的文章数（覆盖索引）
CREATE INDEX idx_article_author_status ON article (author_id, status);

验证：

sql

EXPLAIN ANALYZE SELECT * FROM article WHERE author_id = 1 ORDER BY published_at DESC LIMIT 20;
-- 期望：Index Scan using idx_article_author_time（无需 Sort）

EXPLAIN ANALYZE SELECT * FROM article WHERE status = 'PUBLISHED' ORDER BY published_at DESC LIMIT 20;
-- 期望：Index Scan using idx_article_status_time

EXPLAIN ANALYZE SELECT COUNT(*) FROM article WHERE author_id = 1 AND status = 'PUBLISHED';
-- 期望：Index Only Scan using idx_article_author_status（不回表）

为什么不能只为需求 a 建索引：

原因	说明
索引 `(author_id, published_at)` 无法服务"按 status 查询"——跳过了最左列
两个查询的"过滤列"完全不同	a 用 `author_id`，b 用 `status`——没有共同的索引可用
优化器的选择受限	没有可用索引时只能全表扫描——10 万行的全表扫描在本节实测里是 10-40 ms，QPS 高时会被放大

idx_article_author_status 的作用（需求 c）：COUNT(*) 只需要 author_id 与 status 两列——它们都在索引里，所以能走 Index Only Scan（只读索引、不回表）✓ 这是覆盖索引的典型用法。

顺带一个观察：需求 a 与需求 c 的索引都是"以 author_id 开头"的——(author_id, published_at) 与 (author_id, status) 不能互相替代（第二个列不同），但如果需求 c 只按 author_id 计数，那 (author_id) 就够了。

索引设计的核心问题："哪些查询最频繁？它们各自的过滤列与排序列是什么？"

综合任务（第 7 题）

索引体检报告模板：

text

【表】shop_order（10 万行）
【已有索引】
  1. PRIMARY KEY (id)
  2. idx_order_user (user_id)
  3. idx_order_no (order_no)          ← 来自唯一约束

【高频查询与执行计划】

Q1: 某用户的订单，按时间倒序
   SELECT * FROM shop_order WHERE user_id = 1 ORDER BY created_at DESC LIMIT 20;
   计划：Index Scan using idx_order_user + Sort      ← 用了索引，但还要额外排序
   耗时：0.8 ms
   问题：排序是额外的开销

Q2: 某状态的订单数
   SELECT COUNT(*) FROM shop_order WHERE status = 'PAID';
   计划：Seq Scan（cost=0.00..1986.00）              ← 没走索引
   耗时：12 ms
   问题：status 没有索引

Q3: 某用户在某状态下的订单
   SELECT * FROM shop_order WHERE user_id = 1 AND status = 'PAID';
   计划：Index Scan using idx_order_user + Filter
   耗时：0.5 ms
   问题：Rows Removed by Filter 偏大（status 没在索引里）

【问题清单】
- 该建没建：status 缺索引（Q2 全表扫描）
- 索引未覆盖排序：Q1 需要额外 Sort
- 可以合并：idx_order_user 可以被 (user_id, status, created_at) 覆盖

【改进方案】

-- 1. 用一个复合索引同时服务 Q1 与 Q3（并省掉排序）
CREATE INDEX idx_order_user_status_time
    ON shop_order (user_id, status, created_at DESC);
-- Q1：WHERE user_id = ? ORDER BY created_at DESC  ← 能用（user_id 是最左列，created_at 用于排序？）
--     注意：中间跳过了 status，所以 created_at 的排序**用不上** —— 这就是最左前缀的代价
-- 所以 Q1 更准确的索引是：(user_id, created_at DESC)

-- 2. status 单独建索引（服务 Q2 与后台的"全部订单按状态筛选"）
CREATE INDEX idx_order_status_created
    ON shop_order (status, created_at DESC);

-- 3. 删掉可以被覆盖的索引
DROP INDEX idx_order_user;      -- 被 (user_id, created_at DESC) 覆盖

【代价评估】
- 新增 2 个索引 → 依据 5.6 节实测（1 个索引让写入慢约 3 倍），
  预计写入从 ~290 ms / 2 万行 上升到 ~900 ms 左右
- 需要确认：这张表的写入频率有多高？如果写多读少，要重新权衡

这份报告里最有价值的一步：第 1 条我的方案是错的——

sql

CREATE INDEX idx_order_user_status_time ON shop_order (user_id, status, created_at DESC);

Q1（WHERE user_id = 1 ORDER BY created_at DESC）用不上这个索引的排序能力（因为中间跳过了 status）✓ 这正是最左前缀规则的另一面：不仅"过滤"要连续，"排序"也要连续使用索引列。

正确的两个索引：

sql

CREATE INDEX idx_order_user_time    ON shop_order (user_id, created_at DESC);       -- 服务 Q1
CREATE INDEX idx_order_user_status  ON shop_order (user_id, status);                -- 服务 Q3
CREATE INDEX idx_order_status_time  ON shop_order (status, created_at DESC);        -- 服务 Q2

但要问一句：三个索引值不值？

问题	判断
Q1、Q3 是不是都很频繁？	如果 Q3 很少，可以合并进 Q1 的索引（用 `status` 做过滤）
表的写入频率多高？	每个索引让写入慢一份，写多的表要少建
能否用覆盖索引合并？	如 `(user_id, created_at, status)` 既服务 Q1 的排序，又"覆盖"Q3 的 status（做过滤而非定位）

最后一句经验：

索引设计不是"把每个查询都优化到极致"，而是"在读写代价之间找到一个可接受的平衡点"。
一个能接受的方案是：先只建最关键的 1-2 个索引，上线后看慢查询日志再补充——用真实数据驱动决策，而不是提前优化。

13. 掌握标准

[ ] 能说出索引为什么快（用"目录"类比），以及它的代价；
[ ] 能读懂 EXPLAIN 的扫描方式、cost、actual time、Buffers、Rows Removed by Filter；
[ ] 知道复合索引的最左前缀规则，并知道 PostgreSQL 与 MySQL 的差异；
[ ] 能说出四种索引失效的写法并会修复；
[ ] 知道"返回大部分行时全表扫描更快"，并能判断自己的查询属于哪种；
[ ] 设计索引时先问"高频查询的过滤列与排序列是什么"。

14. 扩展资源

15. 下一步学习

《事务与隔离级别》：数据一致性的核心机制。

前置知识

S3-002 表设计与约束

后续知识

S3-015 执行计划与慢查询定位

进度只保存在本机浏览器，可随时清除

外部学习资源

面试题库索引

项目与练手索引

工具与软件索引

索引

1. 本节定位

2. 学习目标

3. 前置知识检查

4. 为什么需要这个知识

5. 核心概念

5.1 索引是什么

5.2 两种扫描方式

5.3 读懂 EXPLAIN（本节最重要的技能）

5.4 复合索引与最左前缀

5.5 索引失效的四种写法

失效一：对列做函数或运算

失效二：`LIKE` 以通配符开头

失效三：隐式类型转换（MySQL 高发）

失效四：`OR` 连接不同列

5.6 索引的代价

6. 递进示例

6.1 最小示例：建索引前后

6.2 业务示例：为"列表页查询"设计索引

6.3 反例：给每个列都建一个索引

7. 完整可运行代码

8. Debug 观察点

9. 常见错误

错误 1：建了索引但查询没走

错误 2：以为"索引一定能加速"

错误 3：`LIKE` 用前置通配符

错误 4：索引列参与运算

错误 5：只建索引，忘了 `ANALYZE`

10. 动手实验

11. 练习题

12. 参考答案

13. 掌握标准

14. 扩展资源

15. 下一步学习

前置知识

后续知识

索引 ​

1. 本节定位 ​

2. 学习目标 ​

3. 前置知识检查 ​

4. 为什么需要这个知识 ​

5. 核心概念 ​

5.1 索引是什么 ​

5.2 两种扫描方式 ​

5.3 读懂 EXPLAIN（本节最重要的技能） ​

5.4 复合索引与最左前缀 ​

5.5 索引失效的四种写法 ​

失效一：对列做函数或运算 ​

失效二：LIKE 以通配符开头 ​

失效三：隐式类型转换（MySQL 高发） ​

失效四：OR 连接不同列 ​

5.6 索引的代价 ​

6. 递进示例 ​

6.1 最小示例：建索引前后 ​

6.2 业务示例：为"列表页查询"设计索引 ​

6.3 反例：给每个列都建一个索引 ​

7. 完整可运行代码 ​

8. Debug 观察点 ​

9. 常见错误 ​

错误 1：建了索引但查询没走 ​

错误 2：以为"索引一定能加速" ​

错误 3：LIKE 用前置通配符 ​

错误 4：索引列参与运算 ​

错误 5：只建索引，忘了 ANALYZE ​

10. 动手实验 ​

11. 练习题 ​

12. 参考答案 ​

13. 掌握标准 ​

14. 扩展资源 ​

15. 下一步学习 ​

前置知识

后续知识

索引

1. 本节定位

2. 学习目标

3. 前置知识检查

4. 为什么需要这个知识

5. 核心概念

5.1 索引是什么

5.2 两种扫描方式

5.3 读懂 EXPLAIN（本节最重要的技能）

5.4 复合索引与最左前缀

5.5 索引失效的四种写法

失效一：对列做函数或运算

失效二：`LIKE` 以通配符开头

失效三：隐式类型转换（MySQL 高发）

失效四：`OR` 连接不同列

5.6 索引的代价

6. 递进示例

6.1 最小示例：建索引前后

6.2 业务示例：为"列表页查询"设计索引

6.3 反例：给每个列都建一个索引

7. 完整可运行代码

8. Debug 观察点

9. 常见错误

错误 1：建了索引但查询没走

错误 2：以为"索引一定能加速"

错误 3：`LIKE` 用前置通配符

错误 4：索引列参与运算

错误 5：只建索引，忘了 `ANALYZE`

10. 动手实验

11. 练习题

12. 参考答案

13. 掌握标准

14. 扩展资源

15. 下一步学习