基于本文回答

播面

0
评论

PostgreSQL 中常见的扫描方式有哪些？

知识点图片

在 PostgreSQL 中，扫描（Scan）是指数据库执行引擎从表或索引中读取数据的方式。查询优化器（Query Planner）会根据表的统计信息、索引情况以及查询条件，自动选择代价（Cost）最低的扫描方式。

你可以通过在 SQL 语句前加 EXPLAIN 或 EXPLAIN ANALYZE 来查看 PostgreSQL 实际使用了哪种扫描方式。

以下是 PostgreSQL 中最常见的几种核心扫描方式：

1. 全表扫描 (Sequential Scan / Seq Scan)

  • 工作原理：从表的第一个数据块（Page）开始，按顺序依次读取直到最后一个数据块。读取后，根据查询的 WHERE 条件对每一行进行过滤。
  • 适用场景：
    • 表上没有相关的索引。
    • 查询需要返回表中很大一部分数据（例如超过 10%-20%），此时顺序 I/O 的效率反而高于通过索引带来的大量随机 I/O。
    • 表的数据量非常小（即使有索引，优化器也认为直接扫表更快）。
  • 特点：纯顺序 I/O，但如果只查少数数据，效率极低。

2. 索引扫描 (Index Scan)

  • 工作原理：分两步走。首先扫描索引树（通常是 B-Tree），找到满足条件的索引项，并获取对应的行物理位置（TID，Tuple ID）。然后，根据 TID 回表（访问堆表 Heap Table），读取完整的数据行，并检查 MVCC（多版本并发控制）可见性。
  • 适用场景：
    • 查询条件区分度高，只需要返回表中极小一部分数据（通常少于 5%）。
    • 查询需要请求索引中没有包含的列。
  • 特点：索引是顺序读取的，但回表取数据是随机 I/O。如果返回数据量大，随机 I/O 会导致性能急剧下降。

3. 仅索引扫描 (Index Only Scan)

  • 工作原理：查询所需要的所有列都包含在索引中（覆盖索引）。理论上不需要回表。但由于 PostgreSQL 的 MVCC 机制，索引本身不保存事务可见性信息，因此它需要检查 可见性映射表（Visibility Map, VM）。
    • 如果 VM 显示该数据块对所有当前事务可见，则直接返回索引中的数据，无需回表。
    • 如果 VM 显示不可见或不确定，仍需要回表检查实际数据的系统列（如 xmin, xmax）。
  • 适用场景：
    • SELECT 的列和 WHERE 过滤的列全都在同一个索引中。
  • 特点：极大地减少了 I/O，是最高效的扫描方式之一。定期执行 VACUUM 更新可见性映射表（VM）是保证仅索引扫描高效的关键。

4. 位图扫描 (Bitmap Scan)

位图扫描是介于全表扫描和索引扫描之间的一种折中方案，它通常由两个步骤组合而成：Bitmap Index Scan 和 Bitmap Heap Scan。

  • 工作原理：
    1. Bitmap Index Scan：先扫描索引，找到所有符合条件的行的物理位置（TID），但在内存中将这些 TID 按照数据块（Page）的物理顺序构建成一个位图（Bitmap）。
    2. Bitmap Heap Scan：根据构建好的位图，按照物理顺序去访问堆表（Heap Table）提取数据。
  • 适用场景：
    • 查询需要返回中等数量的数据（索引扫描随机 I/O 太重，全表扫描顺序 I/O 又太浪费）。
    • 多索引合并：当查询有多个条件且都有单列索引时（如 WHERE a = 1 AND b = 2），PostgreSQL 会分别对 a 和 b 做 Bitmap Index Scan，然后在内存中做位图按位与（BitmapAnd）或按位或（BitmapOr），最后再进行一次 Bitmap Heap Scan。
  • 特点：它巧妙地将 随机 I/O 转换成了顺序 I/O（或接近顺序的 I/O）。注意：如果查询结果太多导致 work_mem 内存不足，精确到行的位图会退化为精确到数据页（Page）的“有损位图”（Lossy Bitmap），此时回表后需要重新 Recheck 条件。

5. 元组 ID 扫描 (TID Scan)

  • 工作原理：直接利用行的物理存储位置标识符（CTID，包含数据块号和块内偏移量）来访问数据。
  • 适用场景：
    • 在 SQL 中明确指定了 ctid，例如：SELECT * FROM table WHERE ctid = '(0,1)';
  • 特点：这是访问单行数据最快的方式，但由于行更新（UPDATE 会改变 ctid）或 VACUUM FULL 等操作会导致 ctid 变化，通常不在业务代码中直接使用，多用于 DBA 内部维护或某些特殊的数据恢复场景。

其他逻辑扫描方式

除了上述针对物理表/索引的扫描，EXPLAIN 中还会看到一些针对特定逻辑结构的扫描：

  • Subquery Scan：扫描子查询的输出结果集。
  • CTE Scan：扫描 WITH 语句（公共表表达式）产生的临时结果集。
  • Function Scan：扫描返回结果集的函数（如 generate_series）的输出。
  • Values Scan：扫描 VALUES (...) 语句提供的常量数据集。

总结与优化建议

优化器选择扫描方式的根本依据是 代价估计（Cost Estimation）。如果你发现 PostgreSQL 选错了扫描方式（比如该走索引却走了全表扫描），通常是因为：

  1. 统计信息过时：需要对表执行 ANALYZE table_name; 来更新统计信息。
  2. 过滤条件不匹配：查询条件使用了函数（需要函数索引）、存在隐式类型转换，或者使用了不支持索引的模糊查询（如 LIKE '%xxx'）。
  3. Cost 参数设置不合理：例如机械硬盘和 SSD 的随机 I/O 代价不同，可以通过调整 random_page_cost 参数（默认是 4.0，SSD 环境下通常建议调低至 1.1 - 1.5）来鼓励优化器多使用索引。
右滑查看面试常问