索引与查询计划：从 EXPLAIN 到执行优化

0. 元信息

• 主题路径：docs/topics/db-and-sql/subtopics/index-and-query-plan/README.md
• 主分类：计算机基础
• 辅助分类：Web 与后端
• 适合对象：完成父主题前置阶段的开发者
• 建议周期：1～3 周，每周 8～12 小时
• 前置知识：relational-model-and-sql
• 最终目标：能用 EXPLAIN ANALYZE 与 buffers 找到估算偏差并验证索引改动

1. 学习路线

数据分布 → 统计信息 → 候选访问路径 → 成本估算 → 执行计划

2. 阶段周数分配

本子主题按实验推进，不固定周数。每完成一个概念就留下 SQL、输入数据、输出和解释。

3. 九阶段表（精简）

4. 第一周任务

精简版不固定 Day 1～7。执行约定是 PostgreSQL 16+、psql -v ON_ERROR_STOP=1、脚本可在空库重复运行。

5. 阶段通用验收

以 §3 的可观察标准和 §9.4 题目为验收。

6. 最终验收

独立完成一份最小实验、三组数据、一份解释和一次 PostgreSQL/MySQL 行为对照。

7. 综合项目

并入父主题“小型电商数据库”，本子主题负责 查询计划调优 的设计、实验与报告。

本主题贡献

• 在父主题 db-and-sql 的综合项目「小型电商数据库」中，本主题（索引与查询计划）负责 查询计划调优：用 B+Tree / Hash / GiST、planner 与 executor 把上层 SQL 变成可解释的执行计划，并通过 EXPLAIN (ANALYZE, BUFFERS) 锁定代价热点与统计偏差。
• 把 §1 路线「数据分布 → 统计信息 → 候选访问路径 → 成本估算 → 执行计划」落到 3 条真实查询（订单按时段聚合、商品按分类筛选、库存按 SKU 列表 IN）上：每条查询给改前 / 改后两份 EXPLAIN；用 BUFFERS 量化 shared hit / read、local hit、temp written；用 ANALYZE 后 estimated vs actual rows 找出统计偏差并修正（重新 ANALYZE、CREATE STATISTICS、扩大 sample）。
• 与 db-and-sql 其它子主题对接：B+Tree 复合索引列顺序遵从 relational-model-and-sql 给出的查询前缀；为 transaction-and-isolation 的 WHERE 条件提供索引建议，并测出 RR 下走 Index Only Scan 还是 Heap Fetch（heap fetches 不等于 IO miss）。

交付物清单：

1. plans/before.sql / plans/after.sql：3 条核心查询改前 / 改后 EXPLAIN ANALYZE 全量文本（含 cost / rows / time / Buffers），落到 git 仓库可复现到 commit hash；
2. plans/composite_index.md：≥ 5 条复合索引设计，附「等值列放最左、范围列在后」的 EXPLAIN 证据与前缀规律演示；
3. plans/stats_skew.md：制造数据倾斜后 estimated vs actual rows ≥ 10× 偏差的 3 条 SQL，证明重新 ANALYZE 与扩展统计 (CREATE STATISTICS) 的效果；
4. plans/pg_vs_mysql_explain.md：PostgreSQL EXPLAIN (ANALYZE, BUFFERS) 与 MySQL EXPLAIN FORMAT=TREE / JSON 在 hash join / nested loop / index condition pushdown / BKA 上的差异。

验收标准：

• 3 条核心查询中至少 2 条加索引后 P95 延迟降幅 ≥ 50%，且结果集行数未变；
• 至少 1 条 estimated / actual rows 比 ≥ 5×，并给出修正（重新 ANALYZE / 增加统计 / 改写 SQL）的证据；
• 全部 EXPLAIN 输出落到 git 仓库并打 git tag，可复现到该 commit。

8. 推荐资料

见 §9.3。先用 PostgreSQL 官方文档和课程实验，书用于补理论。

9. 学习资料汇聚（v0.3 自包含）

9.1 背景与动机

索引减少扫描范围，成本优化器选择访问路径。真实系统还要处理数据规模、失败和并发。我们用 PostgreSQL 实验观察语义，再用 MySQL 核对实现差异。

9.2 概念地图

flowchart LR
  A[需求与数据] --> B[关系与约束]
  B --> C[SQL]
  C --> D[Planner / Transaction]
  I[Index / Statistics] --> D
  D --> E[执行与并发]
  E --> F[结果 / 延迟 / 锁等待]
  F --> G[验证与修改]
  G --> C

核心概念：heap、page、B+Tree、Hash、GiST、statistics、planner、executor。图中从业务不变量出发，经 SQL、计划或事务到可观察结果，再用相同数据验证修改。

9.3 基础知识讲解

9.3.1 经典论文

9.3.2 经典书籍

9.3.3 优秀博客与文档

9.3.4 核心人物

9.3.5 开发与学习方法

课程补充：CMU 15-445 适合数据库实现，Berkeley CS186 适合系统课程与实验。Andy Pavlo 的课堂讲解用于连接论文、实现与计划输出。

9.4 经典问题与经典案例

9.5 学习难点

概念难点

思维难点

工程难点

9.6 技术标准与接口

9.6.1 Entity

9.6.2 Scope

本子主题处理 查询计划调优。SQL 标准描述通用语义，产品文档描述 planner、storage、locking 等实现。不要把 PostgreSQL 扩展当成所有数据库的共同保证。

9.6.3 Structure

必须掌握的接口：DDL/DML、约束、SELECT/JOIN、事务控制、EXPLAIN (ANALYZE, BUFFERS)、system catalog 与统计视图。具体重点是：heap、page、B+Tree、Hash、GiST、statistics、planner、executor。

9.6.4 Ecosystem

PostgreSQL 是主实验环境。MySQL 用于 InnoDB 和 optimizer 对照。SQLite 适合单文件实验。Oracle 与 SQL Server 用来了解企业产品的方言、执行计划和并发差异。JDBC、ODBC、DB-API 是常见客户端接口。

9.6.5 Depth Tiers

本计划要求达到 L3。

9.6.6 Source

• PostgreSQL 官方文档：引用快照 2026-07-30。
• MySQL Reference Manual：引用快照 2026-07-30。
• ISO SQL 标准条目：引用快照 2026-07-30。

10. 常见误区

• 用单个正常样例证明理解；
• 混淆逻辑结果与物理执行顺序；
• 忽略 NULL 与三值逻辑；
• 把数据库默认行为当业务规范；
• 不保存版本、DDL 和种子数据；
• 看到索引就断言查询会更快；
• 只看平均耗时，不看计划和数据分布；
• 在生产直接试破坏性 SQL；
• 长事务里做网络调用；
• 跨数据库迁移时只检查语法；
• 用 ORM 隐藏生成 SQL 后不再检查；
• 修改后不重跑相同正确性断言。

11. 所有知识点分类（统一规则）

1. 编程语言
2. 数据结构与算法
3. 计算机基础
4. 工程技术
5. Web 与后端
6. 前端与客户端
7. 数据与人工智能
8. 项目与职业能力
9. 安全与可靠性

本计划归属：计算机基础 主 + Web 与后端 辅。