查询优化器与慢 SQL 线上排障
校招考察频度：★ 高频（排障必考·核心生死线） 一句话速记：优化决策靠代价，SSD 必调随机代价比；计划阅读找偏差，Bitmap 转随机为有序；小表驱动走嵌套，大表哈希防落盘。
在后端校招技术面试中，面试官最看重候选人**“遇到生产接口超时或慢 SQL 时，能否冷静利用工具准确定位根因并快速优化”的实战能力。PostgreSQL 拥有关系库中最先进的代价优化器（Cost-based Optimizer, CBO）**，理解其代价模型与执行计划解读是拿下高评级的杀手锏。
一、代价优化器（CBO）核心计算模型
PostgreSQL 优化器不会盲目执行 SQL，而是会枚举所有可能的执行路径（全表扫描、索引扫描、不同 JOIN 算法与顺序），依据数学代价模型计算出总代价（Total Cost）最小的那条路径。
核心认知：执行计划中的
cost（如cost=0.42..8.45）是没有单位的抽象开销数值，绝对不是毫秒！
关键代价参数基准（面试亮点）
seq_page_cost = 1.0：顺序读取一个 8KB 磁盘数据页的代价基准（作为标尺单位 1.0）。random_page_cost = 4.0（生产必调项！）：- 默认值为 4.0，源于传统机械硬盘时代，随机 I/O 寻道成本大约是顺序 I/O 的 4 倍；
- SSD 时代致命陷阱：在如今主流的 NVMe SSD 上，随机读与顺序读的性能差距已微乎其微。若保持 4.0 不变，优化器会严重“高估”索引扫描的随机 I/O 代价，导致很多本该走索引的查询被错误判定为全表扫描（Seq Scan）！
- 生产建议：全固态硬盘环境下强烈建议将
random_page_cost调至 1.1 ~ 1.5。
cpu_tuple_cost = 0.01：CPU 处理解析一行元组的代价。
二、EXPLAIN (ANALYZE, BUFFERS) 深度阅读技巧
面试官：“你在排查慢 SQL 时，EXPLAIN 后面一般跟什么参数？怎么看懂输出？”
EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING, SUMMARY)
SELECT * FROM orders WHERE user_id = 100 AND status = 'PAID';为什么必须加上 ANALYZE 和 BUFFERS？
- 单写
EXPLAIN只是“纸上谈兵”：仅展示优化器根据统计信息预测的估算计划，根本没有真正执行 SQL； ANALYZE真正执行：输出真实执行耗时（actual time）与实际过滤产出的行数（actual rows）；BUFFERS揭示 I/O 真实开销：shared hit=125：在内存shared_buffers中命中 125 个数据页，零磁盘 I/O；read=48：真实从磁盘（或 OS Page Cache）读取了 48 个物理块；written=3：执行过程中发生脏页刷写。
执行计划排查“四步黄金法则”
典型节点输出示例：
-> Index Scan using idx_orders_user on orders
(cost=0.42..8.45 rows=10 width=72)
(actual time=0.032..0.085 rows=12 loops=1)
Buffers: shared hit=3- 自底向上逐层阅读：从最内层的扫描节点（叶子算子）开始向外阅读数据流向；
- 寻找“第一处估算偏差”：重点对比估算行数
rows=10与实际行数actual rows=12。如果发现某一步预估只有 5 行，实际跑出来 50 万行，这就是一切慢 SQL 的万恶之源！ 偏差会沿着 JOIN 树逐层几何级放大； - 关注
loops循环次数：内层算子可能被循环调用多次，算子真实的开销是actual time × loops，真实产出行数是actual rows × loops； - 检查是否存在落盘操作：查找是否有
Sort Method: external merge Disk或Batches: 2（说明work_mem不足导致中间数据溢出到磁盘临时文件！）。
三、常见执行计划扫描节点全景
| 节点类型 | 运行机制与特点 | 调优关注点 |
|---|---|---|
| Seq Scan（全表扫描） | 从磁盘顺序扫描整张表的全部数据页并过滤。 | 小表或查询数据超过全表 30% 时完全正常；千万级大表若出现，需检查是否索引失效或缺失索引。 |
| Index Scan（索引扫描） | 遍历 B-tree 索引获取 CTID，逐个回表读取 8KB 数据页验证 MVCC。 | 适合返回少量行的高选择性查询；返回行数过多时，大量随机 I/O 回表导致性能下降。 |
| Index Only Scan（仅索引扫描） | 数据全部来自覆盖索引，依赖 VM（Visibility Map）。 | 若伴随大量 Heap Fetches，说明该表缺乏 VACUUM，VM 标记落后。 |
| Bitmap Scan（位图扫描） | PostgreSQL 特色核心利器（见下文）。 | 先走 Bitmap Index Scan 搜集页位图，再走 Bitmap Heap Scan 按物理顺序读盘。 |
核心解密：Bitmap Scan 是怎么工作的？
很多同学看到 Bitmap Index Scan + Bitmap Heap Scan 感到困惑。这其实是 PostgreSQL 的一项绝技：
- 场景：若一个查询命中 5000 条记录，直接走
Index Scan会产生 5000 次可能无序的磁盘随机 I/O，代价很大。 - Bitmap 的处理方式：
Bitmap Index Scan：在索引中快速捞出所有满足条件的元组物理坐标（CTID）；- 在内存中构建一个关于数据块号（Block Number）的位图（Bitmap）；
- 若条件包含多个索引（如
WHERE user_id = 1 AND status = 'PAID'），PG 分别对两个索引建立位图，在内存中进行极其高效的位运算（Bitwise AND / OR）！ Bitmap Heap Scan：最终拿算好的位图，严格按照物理磁盘块号由小到大的顺序，一次性批量读取数据页！ 成功将离散的随机 I/O 转化为顺序或局部寻道 I/O。
四、三种 JOIN 连接算法深度对比（大厂面试高频）
三种关系连接算法
| 连接算法 | 核心机制 | 最佳适用场景 | 性能瓶颈与调优 |
|---|---|---|---|
| Nested Loop | 外层驱动表每有一行，就去内层表中检索一次（O(M × log N)）。 | 外层结果集极小（几十行），内层表连接键建有高效索引。 | 内层若无索引会退化为双重全表扫描（O(M × N)），直接卡死。 |
| Hash Join | 先扫描小表在内存中建立 Hash 表，然后流式遍历大表探查（Probe）匹配。 | 两表数据量均较大，且连接条件为等值比对（=），内表缺乏索引。 | work_mem 瓶颈：若哈希表大小超过 work_mem，哈希表将被切片溢出落盘（Batches > 1），I/O 剧增！ |
| Merge Join | 要求两个输入集已经按连接键排好序，使用双指针从头到尾线性归并。 | 两表均非常庞大，且输入本身在索引中已有序，或属于大批量范围等值连接。 | 若输入无序，需要先执行昂贵的 Sort 排序算子。 |
五、生产慢 SQL 排查与调优实战手册
1. 治本之策：修复失效的统计信息
若 EXPLAIN 发现估算行数与真实行数偏差巨大，优化器就会选错计划（例如把该用 Hash Join 的选成了 Nested Loop）：
-- 立即重新采样收集指定表的统计信息
ANALYZE orders;
-- 针对高基数或倾斜严重的列，调大单列统计采样精度（默认 100，最高 10000）
ALTER TABLE orders ALTER COLUMN tenant_id SET STATISTICS 500;
ANALYZE orders;2. 生产抓取慢 SQL 神器：pg_stat_statements
在 postgresql.conf 中开启核心监控插件：
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 查询系统中最耗 CPU、调用最频繁、平均耗时最长的 Top 5 慢 SQL
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;