05.3.2 索引优化与高可用运维
所属:全栈开发面试题库 / 05 数据库与存储 / 05.3 PostgreSQL
这个知识域解决什么问题
本域面向 PostgreSQL 实战优化与线上运维:从索引类型选型与设计,到成本模型、执行计划与统计信息,再到 JOIN/排序/聚合/SQL 改写,最后落到复制、PITR、高可用、连接池与监控运维。目的是让面试与实战中能按「出计划 → 找根因 → 改索引/SQL → 复测」处理慢查询,并讲清复制一致性、备份恢复、切换与容量治理的工程取舍,各题与 MySQL 机制对照标注。
知识地图
访问路径:索引类型选型(1)→ 成本模型/计划/统计(2)→ JOIN 排序聚合改写(3)
数据复制:流复制/同步异步 → 延迟监控 → 逻辑复制 → PITR 备份恢复(4)
高可用运维:选主切换 → 连接池 → 参数/监控/故障 → 升级/安全/容量(5)
子主题
| 编号 | 子主题 | 核心内容 | 题目数 |
|---|---|---|---|
| 05.3.2.1 | 索引类型与索引设计 | B-tree/Hash/GIN/GiST/SP-GiST/BRIN、部分/表达式/INCLUDE 索引、列序与前缀、JSONB/全文索引、无用索引发现、与 MySQL 索引对照 | 18 |
| 05.3.2.2 | 执行计划、成本模型与统计信息 | 成本模型、Seq/Index/Bitmap/Index-Only Scan、EXPLAIN 指标、pg_stats 与扩展统计、计划缓存、并行查询、计划漂移与 MySQL 优化器对照 | 18 |
| 05.3.2.3 | JOIN、排序聚合与 SQL 调优 | 三类 JOIN 触发条件、work_mem 与落盘、keyset 分页、CTE/LATERAL/窗口函数、分区裁剪、全文/JSONB 查询、慢查询排查流程与改写案例 | 18 |
| 05.3.2.4 | 流复制、逻辑复制与 PITR | 流复制原理、同步/异步级别与仲裁、延迟监控、复制槽、逻辑复制发布订阅与冲突、基础备份与时间点恢复、拓扑与 MySQL 复制对照 | 18 |
| 05.3.2.5 | 高可用、连接池与运维监控 | Patroni 选主切换、PgBouncer 模式与坑、参数调优、监控指标、故障处理、版本升级、权限安全、容量规划、在线变更、审计与检查清单 | 18 |
合计 90 题。 每题按「一句话结论 → 概念解释 → 原理流程 → 代码/命令 → 常见误区 → 追问(带答案)」组织;版本不确定行为标「待确认」并注明验证点,对照基线为 PostgreSQL 16/17,以官方文档为准。
学习路径
索引类型与索引设计(1)
→ 执行计划、成本模型与统计信息(2)
→ JOIN、排序聚合与 SQL 调优(3)
→ 流复制、逻辑复制与 PITR(4)
→ 高可用、连接池与运维监控(5)
自检清单
- [ ] 能按查询操作符与数据形态选择 B-tree/Hash/GIN/GiST/BRIN,设计列序与覆盖/部分/表达式索引
- [ ] 能读 EXPLAIN ANALYZE 的成本、rows 偏差、Buffers 与回表次数,定位统计偏差与路径问题
- [ ] 能区分三类 JOIN 触发条件,处理排序落盘、深分页、CTE 与窗口函数性能问题
- [ ] 能讲清流复制/同步级别/延迟来源,说明逻辑复制与物理复制差异及 PITR 恢复流程
- [ ] 能描述高可用选主切换与脑裂防护,配置 PgBouncer 并识别会话状态坑
- [ ] 能列出连接、复制、回卷、膨胀、慢查询、锁、磁盘等监控指标与故障处理路径