1 / 4概念
Indexing & Optimization
索引是额外的访问路径,优化器用统计信息在“全表扫描”和“索引扫描”之间做成本选择。判断标准不是“索引越多越快”,而是查询模式、选择性、写入成本和执行计划是否匹配。
1 概念
1.1 一句话定义
索引是一棵按某个键有序的辅助结构,让数据库用少量页定位目标行,而不是读取整张表。优化是让执行计划选择正确的访问路径、连接顺序和连接算法。
1.2 它解决什么问题
- 点查:
WHERE id = ?从 O(n) 全表扫描降到 O(log n)。 - 范围查/排序:索引本身有序,可避免额外排序。
- 连接:被连接列有索引时,嵌套循环可以只读少量行。
- 唯一性/约束:唯一索引在存储层强制不重复。
1.3 不属于本页的内容
| 主题 | 去哪 |
|---|---|
| 关系模型、SQL、数据库选型 | Relational Databases |
| ACID、隔离级别、锁 | Transactions & Concurrency |
| B+ 树的结构与复杂度 | Trees & Heaps |
| 全文/向量索引 | Full-Text Search / Vector Search |
| 数据仓库建模 | Batch Processing / Lakehouse |
1.4 关键术语
| 术语 | 含义 | 容易混淆的点 |
|---|---|---|
| 选择性 | 不同值比例,越高越适合索引 | 不是“索引命中率” |
| 基数 | 列的不同值数量 | 不是“行数” |
| 复合索引 | 多列按顺序组织的索引 | 列顺序决定可用查询 |
| 最左前缀 | 复合索引只能从最左列开始匹配 | 不是“任意列都能用” |
| 覆盖索引 | 查询所需列都在索引中 | 不是“索引包含整行” |
| 聚簇索引 | 数据按索引顺序存储 | MySQL 主键即聚簇 |
| 执行计划 | 优化器选择的算子与顺序 | 不是“SQL 文本” |
| Sargable | 条件能利用索引 | 函数/隐式转换会破坏它 |
2 机制
2.1 心智模型
B+ 树把键按页组织:内部页只存路由键,叶子页存键 + 行指针,叶子之间用链表连接。一次点查是从根到叶的 3–4 次页访问;一次范围查是定位起点后沿叶子链表扫描。
2.2 机制拆解
- 解析/重写:SQL → 逻辑计划,展开视图。
- 枚举计划:顺序扫描、索引扫描、索引仅扫描、位图扫描;连接顺序与算法(嵌套循环/哈希/归并)。
- 成本估算:用统计信息估算行数、页数、CPU;选择总成本最低的计划。
- 执行:按算子流水线读取;
EXPLAIN ANALYZE显示实际行数与缓冲区。
2.3 完整枚举
| 索引类型 | 结构 | 适用 | 限制 |
|---|---|---|---|
| B-tree | 平衡多路树 | 等值、范围、排序、前缀 | 最左前缀;大键占空间 |
| Hash | 哈希表 | 纯等值 | 不支持范围/排序 |
| GIN | 倒排/多值 | JSONB、数组、全文 | 写入/构建成本高 |
| GiST | 通用搜索树 | 几何、范围、近邻 | 需要操作符类 |
| BRIN | 块范围摘要 | 超大且物理有序的表 | 只适合相关性强的列 |
| 全文 | 倒排索引 | 文本检索 | 语言/分词配置 |
| 空间 | R-tree/GeoHash | 地理位置 | 维度/精度限制 |
常见索引形态:
| 形态 | 例子 | 作用 |
|---|---|---|
| 单列 | (user_id) | 点查/连接 |
| 复合 | (user_id, created_at) | 等值 + 排序/范围 |
| 唯一 | UNIQUE (email) | 约束 + 点查 |
| 部分 | WHERE status = 'open' | 只索引热子集 |
| 覆盖 | INCLUDE (amount) | 索引仅扫描,避免回表 |
| 表达式 | lower(email) | 大小写不敏感查询 |
2.4 关键数字与量级
| 指标 | 量级 | 说明 |
|---|---|---|
| 数据页 | 8 KB(PostgreSQL) | 索引页同样按页读写 |
| B+ 树高度 | 3–4 层 | 扇出大,点查 I/O 少 |
| 索引写入放大 | 每次写多棵索引 | 索引越多,写越慢 |
| 选择性 | 越高越好 | 低选择性列索引收益小 |
| 回表成本 | 随机 I/O | 覆盖索引可避免 |
3 实践
3.1 最小示例
-- 复合索引:先按 user_id 等值,再按 created_at 排序。
CREATE INDEX orders_user_created_idx ON orders (user_id, created_at DESC);
-- 部分索引:只索引未完成订单,减少体积。
CREATE INDEX orders_open_idx ON orders (created_at) WHERE status = 'open';
-- 覆盖索引:查询列都在索引里,避免回表。
CREATE INDEX orders_cover_idx ON orders (user_id, created_at) INCLUDE (amount);
-- 看实际计划:关注 scan 类型、估计/实际行数、buffers。
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount FROM orders
WHERE user_id = 42 AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC;
3.2 常用操作与参数
| 操作 | 命令 / SQL | 说明 |
|---|---|---|
| 建索引 | CREATE INDEX / CREATE INDEX CONCURRENTLY | 生产用并发建,避免长时间锁写 |
| 看计划 | EXPLAIN (ANALYZE, BUFFERS) | 关注实际行数与扫描方式 |
| 更新统计 | ANALYZE table | 统计过期会让计划失真 |
| 查索引使用 | pg_stat_user_indexes | 找从未使用的索引 |
| 重建 | REINDEX / OPTIMIZE TABLE | 处理膨胀/碎片 |
| 强制/提示 | 视数据库而定 | 先修统计/索引,再考虑 hint |
3.3 标准流程
- 收集真实查询:按频率和耗时排序,不要凭想象建索引。
- 用
EXPLAIN看扫描方式、估计/实际行数、回表次数。 - 按“等值列 → 排序列 → 范围列”设计复合索引顺序。
- 评估写入放大:每个索引都会增加写和存储成本。
- 上线后监控索引命中、膨胀和未使用索引。
3.4 与相邻技术的组合
- 事务:索引减少锁范围;缺索引的
FOR UPDATE可能锁住大量行。 - 关系库:索引是优化器的输入,统计信息是索引发挥作用的前提。
- 搜索:全文/向量索引解决文本/语义检索,B+ 树解决结构化查询。
- 数据仓库:列式存储 + 分区/排序键替代传统 B+ 树索引。
3.5 选型维度
| 维度 | 取值 | 影响 |
|---|---|---|
| 查询形态 | 点查 / 范围 / 排序 / 连接 | 决定索引类型与列序 |
| 选择性 | 高 / 低 | 低选择性索引收益小 |
| 写读比 | 写多 / 读多 | 写多要控制索引数量 |
| 数据规模 | 小 / 大 | 大表更要覆盖/分区 |
| 数据分布 | 均匀 / 倾斜 | 倾斜会让统计失真 |
| 一致性要求 | 强 / 弱 | 唯一索引承担约束 |
3.6 决策规则
- 等值 + 排序:复合索引按“等值列在前,排序列在后”。
- 范围条件放最后:范围列之后的列无法继续用于定位。
- 只查少数列:用覆盖索引/
INCLUDE避免回表。 - 只查热子集:用部分索引,减小体积与写入成本。
- 低选择性列(如布尔):不要单独建索引,放到复合索引里。
3.7 替代方案对比
| 方案 | 优势 | 代价 | 适用 |
|---|---|---|---|
| B-tree | 通用、支持范围/排序 | 写入放大、占空间 | 大多数 OLTP |
| Hash | 等值极快 | 不支持范围 | 纯点查 |
| 覆盖索引 | 避免回表 | 索引更大 | 高频只读查询 |
| 部分索引 | 体积小、写入少 | 只覆盖子集 | 状态/软删除 |
| 分区/列存 | 扫描快、易维护 | 不适合高频点写 | 分析/大表 |
3.8 何时不该用
- 表很小(几百行):全表扫描可能更快。
- 列选择性极低:索引扫描 + 回表可能比顺序扫描更慢。
- 写入极密集:每个额外索引都在放大写成本。
- 查询条件对列做函数/隐式转换:索引失效,先改查询。
4 边界
4.1 典型故障
| 症状 | 根因 | 处理 |
|---|---|---|
| 建了索引仍全表扫描 | 条件不 Sargable/隐式转换 | 改写法、表达式索引 |
| 查询突然变慢 | 统计过期/计划退化 | ANALYZE、检查计划 |
| 索引越大越慢 | 膨胀/碎片/回表多 | REINDEX、覆盖索引 |
| 写入变慢 | 索引过多/写放大 | 删未使用索引 |
| 范围查询只用上前缀 | 复合索引列序不对 | 调整列序 |
| 分页深翻很慢 | OFFSET 扫描大量行 | 游标/键集分页 |
4.2 反模式
| 做法 | 后果 | 替代 |
|---|---|---|
| 每列都建索引 | 写放大、优化器选择困难 | 按查询建复合索引 |
| 在索引列上套函数 | 索引失效 | 表达式索引或改查询 |
用 LIKE '%x%' 做前缀搜索 | 无法用 B-tree | 全文/倒排索引 |
| 只看 SQL 不看计划 | 误判瓶颈 | EXPLAIN ANALYZE |
| 忽略统计信息 | 计划估算严重偏离 | 定期 ANALYZE |
4.3 可观测信号
EXPLAIN (ANALYZE, BUFFERS) 的实际行数/缓冲区、pg_stat_statements 的耗时与调用次数、索引命中率、未使用索引、表/索引膨胀、磁盘 I/O。
4.4 安全 / 性能 / 一致性边界
- 正确性:唯一索引是约束,不是可选的性能优化;删之前确认业务不依赖。
- 写入成本:索引数量与写吞吐成反比;先算写入预算。
- 统计信息:优化器不是魔法,统计失真时任何索引都可能被放弃。
- 在线变更:大表建索引要评估锁与复制延迟,优先并发建。