PostgreSQL 事务与并发系列 · 第四期 事务隔离级别深度测试与序列化异常 前三期我们构建了从 MVCC 到锁机制的完整知识体系。本期将聚焦三种隔离级别的实际行为边界，通过可复现的案例演示“不可重复读”“幻读”以及最隐蔽的“写偏斜（Write Skew）”，同时揭开 PostgreSQL 可串行化快照隔离（SSI）的神秘面纱。 一、隔离级别速览与默认行为回顾 PostgreSQL 支持三种隔离级别（READ UNCOMMITTED 实际等同于 READ COMMITTED）： 隔离级别 脏读 不可重复读 幻读 序列化异常（写偏斜） READ COMMITTED ❌ ✅ 可能 ✅ 可能 ✅ 可能 REPEATABLE READ ❌ ❌ ❌（PostgreSQL 实现彻底阻止） ✅ 可能 SERIALIZABLE ❌ ❌ ❌ ❌（检测冲突并终止事务） 关键事实：PostgreSQL 的 REPEATABLE READ 级别不仅阻止了不可重复读，还彻底阻止了幻读（通过快照隔离实现了比 SQL 标准更严格的语义）。但即便如此，仍无法防止写偏斜这种序列化异常。 二、实战环境准备 创建一个简单的医生值班表，用于演示所有异常场景： DROP TABLE IF EXISTS doctors; CREATE TABLE doctors ( id INT PRIMARY KEY, name TEXT, on_call BOOLEAN -- 是否值班 ); INSERT INTO doctors VALUES (1, 'Alice', true), (2, 'Bob', true); 规则：至少有一名医生值班（业务约束）。两个事务同时试图交接班，可能会导致违反约束。 三、READ COMMITTED：不可重复读与幻读 3.1 不可重复读 时间 事务 A (READ COMMITTED) 事务 B (READ COMMITTED) T1 BEGIN; T2 SELECT on_call FROM doctors WHERE id=1; → true T3 UPDATE doctors SET on_call=false WHERE id=1; COMMIT; T4 SELECT on_call FROM doctors WHERE id=1; → false T5 COMMIT; 同一个事务内读取到不同的值 → 不可重复读。 3.2 幻读 在 PostgreSQL 中，幻读通常指“范围内新增/删除的行导致两次查询结果集不同”。虽然 REPEATABLE READ 能阻止，但 READ COMMITTED 仍会出现： 时间 事务 A 事务 B T1 BEGIN; T2 SELECT COUNT(*) FROM doctors WHERE on_call=true; → 2 T3 INSERT INTO doctors VALUES (3,'Carol',true); COMMIT; T4 SELECT COUNT(*) FROM doctors WHERE on_call=true; → 3 出现了之前未见的行 → 幻读。 四、REPEATABLE READ：快照隔离如何阻止幻读 在 REPEATABLE READ 隔离级别下，事务在第一条语句执行时获取一个固定的快照，整个事务期间复用。因此 T4 查询仍然看到的是 T2 时刻的快照结果，不会受 T3 提交的影响。 验证脚本： -- 会话 1 BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT COUNT(*) […]

PostgreSQL 事务与并发系列 · 第三期 锁机制与死锁处理实战 前两期我们深入了 MVCC 与快照隔离，知道了“读不阻塞写”。但有些场景必须靠锁来强制串行化。本期将带你掌握 PostgreSQL 中各类锁的用法、冲突关系、死锁成因与排查，并提供生产环境可用的监控脚本。 一、为什么有了 MVCC 还需要锁？ MVCC 主要解决了 读写冲突（读不阻塞写，写不阻塞读），但它无法解决 写写冲突 以及某些需要“绝对最新数据”的场景。 例如： 两个事务同时对同一行执行 UPDATE，必须有一个先等另一个完成，否则更新会相互覆盖。 业务上要求“先查询余额，余额足够才扣款”，如果不用锁就可能出现超卖。 因此，PostgreSQL 在 MVCC 之上仍然保留了完善的锁机制，包括表级锁、行级锁，以及用于应用层协调的咨询锁。 二、表级锁 表级锁由 PostgreSQL 内核自动管理，但你可以通过 LOCK 命令显式指定。表级锁按照冲突程度从低到高排列如下： 锁模式 关键字 冲突对象 典型场景 ACCESS SHARE ACCESS SHARE 仅与 ACCESS EXCLUSIVE 冲突 SELECT 查询自动加此锁 ROW SHARE ROW SHARE 与 EXCLUSIVE、ACCESS EXCLUSIVE 冲突 SELECT FOR UPDATE/SHARE ROW EXCLUSIVE ROW EXCLUSIVE 与 SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 UPDATE、DELETE、INSERT SHARE UPDATE EXCLUSIVE SHARE UPDATE EXCLUSIVE 与 SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 VACUUM、CREATE INDEX CONCURRENTLY SHARE SHARE 与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 保护表结构不被修改，但允许读 SHARE ROW EXCLUSIVE SHARE ROW EXCLUSIVE 与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 较少直接使用，类似保护整个表 EXCLUSIVE EXCLUSIVE 与 ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 允许读但阻止所有写和并发锁 ACCESS EXCLUSIVE ACCESS EXCLUSIVE 与所有锁模式冲突 DROP TABLE、TRUNCATE、REINDEX、VACUUM FULL、ALTER TABLE 等 查看当前表级锁 SELECT relation::regclass AS table_name, mode, granted, pid FROM pg_locks WHERE locktype = 'relation' AND relation IS NOT NULL; 显式加锁示例 -- 防止其他事务对表进行 DDL 操作，但允许普通查询 LOCK TABLE my_table IN SHARE MODE; -- 禁止任何并发写入，但允许读取 LOCK TABLE my_table IN EXCLUSIVE MODE; -- 完全排他（用于维护） LOCK TABLE my_table IN ACCESS EXCLUSIVE MODE; 三、行级锁 行级锁不会锁整个表，而是锁住特定的行版本。它们也是通过 pg_locks 查看，locktype 为 tuple（旧版本）或 transactionid（行锁本质关联事务）。更直观的是通过 SELECT ... FOR UPDATE/SHARE 来加锁。 行锁模式 关键字 冲突（与另一个行锁） 说明 FOR […]

PostgreSQL 事务与并发系列 · 第二期 深入 MVCC：可见性判断与事务快照揭秘 第一期我们建立了 ACID 和 MVCC 的基本概念。本期将掀开 MVCC 的引擎盖，剖析元组头部的 xmin/xmax、事务快照的内存结构，并通过实战案例看清“读已提交”与“可重复读”的本质差异。 一、回顾：MVCC 的核心思想 PostgreSQL 中，每一行数据（称为元组 tuple）在被修改时不会原地更新，而是插入一个新的行版本，旧的版本仍然留在数据页中。这些版本通过元组头部的两个事务 ID 字段来标记生命周期： xmin – 插入该行版本的事务 ID xmax – 删除或更新该行版本的事务 ID（0 表示未删除） 当事务开始时，系统会为其生成一个快照（Snapshot），里面记录了当前“哪些事务正在运行、哪些事务已经提交”等信息。随后，对于每一个可见性判断，PostgreSQL 会根据快照中的规则决定是否显示该行版本。 二、元组头中的秘密：xmin 与 xmax 查看一个普通表的元组头部信息，可以使用 pageinspect 扩展（需要超级用户权限）： CREATE EXTENSION pageinspect; -- 创建一个测试表 CREATE TABLE test_mvcc (id int PRIMARY KEY, name text); INSERT INTO test_mvcc VALUES (1, 'Alice'), (2, 'Bob'); -- 查看数据页的元组信息 SELECT lp, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('test_mvcc', 0)); 输出示例： lp t_xmin t_xmax t_ctid 1 730 0 (0,1) 2 730 0 (0,2) 这里 t_xmin=730 表示插入这两行的事务 ID 是 730，t_xmax=0 表示当前版本未被删除。 更新时的版本变化 -- 开启一个新事务（假设事务 ID 为 735） BEGIN; UPDATE test_mvcc SET name = 'Alice2' WHERE id = 1; 此时数据页内部会变成： lp t_xmin t_xmax t_ctid 1 730 735 (0,3) 2 730 0 (0,2) 3 735 0 (0,3) 旧版本（lp=1）的 t_xmax 被设置为 735，表示它已被事务 735 删除。 新版本（lp=3）的 t_xmin = 735，表示它由事务 735 创建。 t_ctid 指向新版本的位置 (0,3)，形成一条版本链。 当事务 735 提交后，旧版本对其他事务可能不可见，具体取决于隔离级别和快照。 三、事务快照（Snapshot）的内存布局 PostgreSQL 中，快照是一个结构体（SnapshotData），其核心成员包括： xmin – 当前所有未提交事务中最小的 ID xmax – 下一个将要被分配的事务 ID（即大于等于此值的所有事务，肯定未开始） xip – 一个数组，存储所有活跃（未提交）的事务 ID snapshot_type – 区分 SNAPSHOT_MVCC（普通快照）、SNAPSHOT_SELF（包含当前事务）等 快照是调用 GetSnapshotData() 函数生成的。简单流程： 记录当前系统活跃事务列表（从 ProcArray 中读取） 计算出最小的活跃事务 ID 作为 xmin 用 nextXid（下一个未分配的事务 ID）作为 xmax 将活跃事务 ID 列表复制到 xip 数组 一个典型的快照内容（人为示例）： xmin = 100 xmax = 105 xip = [101, 103] (事务 102 已经提交，104 尚未开始) 四、可见性判断的核心规则 给定一个行版本，其 xmin 和 xmax，以及当前事务的快照，判断是否可见的简化逻辑如下： […]

PostgreSQL 事务与并发系列 · 第一期 从 ACID 到 MVCC：PostgreSQL 并发控制的核心思想 本系列将带你系统掌握 PostgreSQL 事务与并发的每一步。第一期从 ACID 原则谈起，揭开 MVCC 如何实现”读不阻塞写、写不阻塞读”。 一、引言 在关系型数据库的世界里，事务（Transaction）是数据一致性与并发控制的基石。PostgreSQL 之所以被公认为”业界最先进的开源关系型数据库”，其强大的事务处理能力和高并发支持功不可没。 理解 PostgreSQ L的事务与并发机制，不仅仅是掌握几条 SQL 语句。它直接决定了： 你写的应用在高并发下能否稳定运行 你的数据库能否扛住千万级 QPS 的冲击 你遇到死锁、性能瓶颈时能否快速定位并解决 本系列将从 ACID 原则讲起，逐步深入到 MVCC 实现原理、隔离级别的行为差异、锁机制的分类与死锁排查，最后涵盖快照隔离、长事务与膨胀治理，帮助你对 PostgreSQL 的事务与并发模块建立系统性的认知。 二、事务的 ACID 原则 什么是事务？ 用数据库的术语来说，事务是由一个或多个 SQL 语句组成的工作单元。最经典的例子是银行转账——从账户 A 扣款和向账户 B 入账，这两个操作要么一起成功，要么一起失败，绝不允许出现”钱扣了，但对方没收到”这种中间状态。 事务所解决的四大并发现象 PGSQL 的事务机制正是为了解决以下四大潜在问题而产生的：脏读、不可重复读、幻读、序列化异常（后文将详细说明）。 ACID 四大特性 ACID 是事务的四大核心属性，也是 PostgreSQL 设计并发控制机制的根本原则： 特性 含义 PostgreSQL 的实现技术 原子性（Atomicity） 事务中的所有操作要么全部成功提交，要么全部失败回滚 事务管理 + 撤销日志 一致性（Consistency） 事务执行前后，数据库始终保持一致的状态（约束、规则） 主键、外键、检查约束等 隔离性（Isolation） 并发事务之间互不干扰 多版本并发控制（MVCC） 持久性（Durability） 事务一旦提交，其修改就永久保存，即使系统故障也不丢失 预写式日志（WAL）+ 恢复子系统 三、原始的并发控制：锁 在 MVCC 诞生之前，大多数数据库系统采用基于锁的并发控制机制，其中最具代表性的是 S2PL（严格两阶段锁）： 读操作需要获取共享锁 写操作需要获取排他锁 这也正是 PostgreSQL 经典的“读不阻塞写，写也不会阻塞读”设计初衷得以实现的基础。 四、MVCC：PostgreSQL 并发的灵魂 4.1 什么是 MVCC？ 多版本并发控制（Multi-Version Concurrency Control, MVCC）是 PostgreSQL 实现高并发隔离性的核心技术。 核心理念是：当数据被修改时，数据库不直接覆盖原有数据，而是创建一个新的版本，保留历史版本。 这种设计带来最直观的效果就是：读者永远不会被写者阻塞，写者也永远不会被读者阻塞。 4.2 MVCC 的核心组件 PostgreSQL 中的每一条元组（tuple）都带有两个系统字段： xmin：创建这行版本的事务 ID xmax：删除/过期这行版本的事务 ID 当事务读取数据时，PostgreSQL 会根据当前事务的隔离级别和快照信息，来决定应该看到哪个版本的数据，实现在无需加锁的情况下保证事务隔离性和一致性。 五、事务隔离级别 SQL 标准定义了四种隔离级别，但 PostgreSQL 在内部只实现了三种不同的隔离级别——因为其”读未提交”的行为实际等同于”读已提交”。 隔离级别 脏读 不可重复读 幻读 PostgreSQL 默认？ 读未提交 可能 可能 可能 ❌ 读已提交 不可能 可能 可能 ✅ 默认 可重复读 不可能 不可能 可能 🟡 可设置 可串行化 不可能 不可能 不可能 🟡 最高级别 三种的不可重复出现性及其影响：PostgreSQL 都提供了完整的保障。 六、快照隔离 6.1 什么是快照？ 快照是 PostgreSQL 实现 MVCC 的核心数据结构。当一个事务开始时，PostgreSQL 会为该事务生成一个快照，记录： xmin：当前未完成的最小事务 ID xmax：下一个被分配的事务 ID xip_list：当前活跃事务列表 通过这个快照，PostgreSQL 能准确地判断每个数据版本对当前事务是否可见。 6.2 不同隔离级别下的快照行为 读已提交：事务中的每条语句执行前都会重新获取一次快照，这意味着同一个事务中的不同语句可能看到不同的最新数据。 可重复读：事务只在其第一条语句执行时获取一次快照，后续所有语句都复用这个快照，保证整个事务内看到的数据是一致的。 可串行化：基于可重复读的快照机制，但额外增加了对事务间依赖的检测，在冲突时主动终止事务以保持”真正的串行化”语义。 七、PostgreSQL 中的锁 MVCC 减少了大多数情况下的锁竞争，但在某些场景下，锁依然是不可或缺的。 7.1 标准锁的分类 PostgreSQL 的锁分为多个类型，常见的有： 锁模式 使用场景 冲突模式 表级锁 控制对整个表的并发访问 ACCESS SHARE / ROW EXCLUSIVE / ACCESS EXCLUSIVE 等 行级锁 控制对特定行的并发修改 FOR UPDATE / FOR SHARE 7.2 建议锁 PostgreSQL 提供了一种应用层面的锁机制——建议锁。应用可以选择任意一个 64 […]

SQL 与查询优化（PostgreSQL 篇）· 第七期 查询缓存、连接池与中间件优化 从第一期到第六期，我们的焦点一直围绕单实例 PostgreSQL：执行计划、索引设计、连接算法、统计信息、物化视图与分区表，以及锁与并发控制。但是，当到达了几百万乃至上亿行数据之后，应用程序不仅查询复杂，连接数也可能成千上万。 本期我们跳出数据库内核，进入中间件层，探讨查询缓存、连接池和分布式中间件如何进一步提升系统的极限。 一、连接池 – 高并发的前提 1.1 为什么需要连接池？ 在 PostgreSQL 中，每一个新的客户端连接都会在服务器端 fork() 一个新的进程（Backend Process）。一个空闲的数据库连接，即使没有执行任何查询，也会占用大约 5-10 MB 的内存。 然而，对于现代的 Web 应用和无服务器架构，通常不需要上百个全时的数据库连接。一个典型的请求可能只持续几毫秒来执行一个查询，然后释放连接。如果每次请求都重新打开连接，断开后立刻关闭，下一轮请求再次创建……这会引入显著的连接建立/销毁开销。 高并发下会产生两种典型的“连接风暴”现象： 现象 机制 检测手段 连接风暴 连接数超 max_connections 阈值时，新连接请求直接被拒绝 ERROR: too many clients already 向连接风暴”升级” 空闲连接数过多（超过 tcp_keepalives_idle 阈值），系统不断尝试维持活着，且触发了 TCP keepalive 探针风暴 tcpdump 观察到大量极小的探针包，同时 pg_stat_activity 里 idle 连接数极高 查看当前连接状态，以评估是否需要连接池： SELECT state, count(*) FROM pg_stat_activity GROUP BY state; SELECT name, setting FROM pg_settings WHERE name = 'max_connections'; 如果输出中的 idle 状态连接占据了绝大多数，那么，这些空闲连接正在浪费大量的服务器内存资源。 1.2 PgBouncer – 轻量级专用连接池核心 为了解决“连接数爆炸”的问题，标准的解决方案是在客户端和数据库之间引入一个连接池中间件。PgBouncer 就是最著名的专用连接池。 其工作原理类似这样：应用程序连接到 PgBouncer；PgBouncer 维护一个到后端 PostgreSQL 的真实连接的池（池中包含一定数量已建立的连接）；当一个请求到达时，PgBouncer 从池中找出一个空闲的真实连接分配给该请求；请求完成后，真实连接被归还到池中。 PgBouncer 提供三种连接池模式： 模式 工作原理 适用场景 限制 会话池（Session） 连接生命周期 = 客户端会话全程（默认模式） 使用临时表、SET 会话变量、LISTEN/NOTIFY、PREPARE 语句、WITH HOLD 游标 性能收益最小 事务池（Transaction） 连接生命周期 = 单个事务。事务结束（COMMIT/ROLLBACK）后归还连接 短事务为主的 OLTP 系统（推荐模式） 不支持会话级特性（临时表、SET） 语句池（Statement） 连接生命周期 = 单条 SQL 语句执行结束后归还连接 大量短查询场景 不支持多语句事务 配置示例 (pgbouncer.ini)： [databases] mydb = host=localhost port=5432 dbname=mydb [pgbouncer] pool_mode = transaction # 事务池模式 default_pool_size = 20 # 每个数据库/用户的默认连接池大小 max_client_conn = 1000 # 允许的最大客户端连接数 listen_addr = * listen_port = 6432 auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt PgBouncer 非常轻量级：无论有多少客户端连接，一个 PgBouncer 实例仅占用约 2-5 MB 的 RAM；每个连接的内存开销只有 2 KB 左右。纯连接池场景下，建议使用 PgBouncer，轻量简单，能够减少 97.5% 的连接数，并提升 53% 的吞吐量。 1.3 连接池最佳实战 设计容量估算公式： pool_size = (max_connections - admin_connections) × buffer_factor × connection_multiplier / replication_factor 其中 connection_multiplier 取决于中间件架构：直连时代值为 1，引入 PgBouncer 后视压测情况可调整为 0.2~0.5。 Pgbouncer 的事务池模式能突破内存限制，把极大量的客户端连接收敛到少量的后端连接上。但使用事务池模式时，绝不能依赖会话级资源（即，不能在事务结束后还依赖临时数据存活）。这往往要求业务代码中所有 BEGIN … COMMIT ROI 级别的事务，都不应该依赖临时表和会话变量。若违反此限制，可能会看到一些后台报错（关于临时表或预备语句不存在）。 此外，也需要注意一些网络安全问题，尤其是在公网使用连接池时，务必配置 auth_file 中的用户密码，并建议使用 scram-sha-256 […]

SQL 与查询优化（PostgreSQL 篇）· 第六期 锁与并发控制 – 查询优化的另一维度 前五期我们专注 SQL 本身：执行计划、统计信息、连接算法、高级 SQL、分区表，以及优化器调参。 但在高并发系统中，锁与事务往往成为性能瓶颈。本期深入 PostgreSQL 的锁机制、MVCC、Vacuum 原理，学会诊断锁冲突、优化高并发写入，让数据库在多用户环境下依然流畅运行。 一、PostgreSQL 的并发控制基石：MVCC PostgreSQL 没有使用传统的两阶段锁（2PL）来避免读写冲突，而是采用了 多版本并发控制（MVCC）。 读操作不会阻塞写操作，写操作也不会阻塞读操作。 每个 SQL 语句（或事务）看到的是某个时间点的数据库快照。 更新操作会产生新的元组版本，旧版本仍然保留，直到不再需要（被所有活跃事务可见）。 关键系统列： 列 含义 xmin 插入该元组的事务 ID xmax 删除或更新该元组的事务 ID（0 表示未删除） cmin / cmax 事务内命令序列号 查询时，PostgreSQL 结合当前事务的快照（xmin、xmax 与当前事务 ID、pg_snapshot）判断可见性。 MVCC 带来的好处 读不阻塞写，写不阻塞读。 SELECT 可以拿到一致性快照，无需加锁。 MVCC 的代价 旧版本元组会一直留在表中，导致表膨胀。 需要后台进程 autovacuum 清理死元组。 长事务会阻止旧版本被清理，加速膨胀。 二、PostgreSQL 锁体系概览 PostgreSQL 的锁分为表级锁和行级锁，还有一个轻量级的 自旋锁（SpinLock） 用于保护共享内存。 2.1 表级锁模式（从低到高冲突程度） 锁模式 关键词 冲突对象 典型场景 ACCESS SHARE SELECT ACCESS EXCLUSIVE 读取表 ROW SHARE SELECT FOR UPDATE/SHARE EXCLUSIVE, ACCESS EXCLUSIVE 准备修改行 ROW EXCLUSIVE INSERT, UPDATE, DELETE SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 修改数据 SHARE UPDATE EXCLUSIVE VACUUM, CREATE INDEX CONCURRENTLY SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 保护模式变更 SHARE CREATE INDEX (非并发) ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 读但阻止写入 SHARE ROW EXCLUSIVE 较少使用 ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 并发读但禁止并发 DML EXCLUSIVE REFRESH MATERIALIZED VIEW (非并发) ROW SHARE 及以上 防止并发读写 ACCESS EXCLUSIVE DROP, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL 所有其他锁 完全独占 查看当前表级锁： SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks WHERE locktype = 'relation' AND relation = 'orders'::regclass; 2.2 行级锁 行级锁记录在内存的锁表中（不是存储元组中），通过 xmax 标记哪个事务持有锁。 FOR UPDATE：排他行锁。 FOR SHARE：共享行锁。 FOR NO KEY UPDATE、FOR KEY SHARE：用于外键约束的细化锁。 注意：行级锁与表级锁 ROW […]

SQL 与查询优化（PostgreSQL 篇）· 第五期 查询优化器内部机制与高级调参 前四期我们构建了完整的优化知识体系：执行计划、统计信息、连接算法、高级 SQL、物化视图与分区表。 本期我们钻进优化器的“黑盒”，剖析代价模型、参数开关、并行查询，甚至学会强制执行计划——当你比优化器更懂数据时，这些能力会让你如虎添翼。 一、优化器的工作流程回顾 PostgreSQL 优化器基于代价选择执行计划。流程如下： 解析与重写：SQL → 解析树 → 基于规则的重写（视图展开、常量折叠）。 生成路径：为每个表考虑不同的扫描路径（Seq Scan、Index Scan、Bitmap Scan），为每个 JOIN 考虑不同的连接方法（Nested Loop、Hash Join、Merge Join）和连接顺序。 代价估算：利用统计信息和代价参数计算每个路径的启动成本（获取第一行的成本）和总成本。 选择最优路径：选出成本最低的执行计划（默认是最低总成本，可以通过 cursor_tuple_fraction 调整偏好）。 优化器并不完美，因为它依赖于： 统计信息的准确性（第一期、第三期）。 代价参数的合理性（本期重点）。 查询复杂度限制（from_collapse_limit 等，避免指数爆炸）。 二、代价模型参数详解 代价公式（简化）： 总成本 = seq_page_cost * 顺序页数 + random_page_cost * 随机页数 + cpu_tuple_cost * 处理的元组数 + cpu_index_tuple_cost * 索引元组数 + cpu_operator_cost * 操作次数 这些参数定义在 postgresql.conf 中，可以在会话级动态修改。 2.1 核心 I/O 参数 参数 默认值 含义 调优建议 seq_page_cost 1.0 顺序读取一个数据页的代价 SSD 保持不变或略低（0.9），HDD 可以提高到 1.5~2.0 random_page_cost 4.0 随机读取一个数据页的代价 关键参数。SSD 应设为 1.1～1.5，NVMe 甚至可以 1.0；HDD 保持 4.0 effective_cache_size 4GB（根据系统） 操作系统和 PG 共享缓存的总大小（用于评估索引扫描是否受益于缓存） 设置为系统内存的 50%~75%。设太小会低估索引扫描，设太大会高估 原理：当 random_page_cost 远大于 seq_page_cost 时，优化器会偏向 Seq Scan；当两者接近时，Index Scan 更容易被选中。 实例：某系统用 SSD，但未调整 random_page_cost，许多本应走索引的查询走了全表扫描，导致响应时间从 50ms 飙升到 3s。调整到 1.1 后，计划恢复正常。 -- 会话级调整（测试后可以写入配置文件） SET random_page_cost = 1.1; 2.2 CPU 相关参数 参数 默认值 影响 cpu_tuple_cost 0.01 处理一个元组的 CPU 代价。调高会使 Seq Scan 变贵，倾向于减少扫描行数 cpu_index_tuple_cost 0.005 索引扫描中处理一个索引元组的代价 cpu_operator_cost 0.0025 执行一个操作符或函数的代价 通常保持默认值。在极端 OLAP 场景（大量计算），可以适当调高 cpu_tuple_cost 和 cpu_operator_cost 来反映真实负载。 2.3 连接顺序与搜索限制 参数 默认值 说明 from_collapse_limit 8 将子查询提升到主查询的 FROM 列表时，最多处理多少个表 join_collapse_limit 8 显式 JOIN 时，优化器尝试重排连接顺序的最大表数量 当查询涉及超过 8 张表时，优化器会放弃穷举所有连接顺序，采用贪心或启发式算法。对于极端复杂的查询，提高这两个值可以找到更好的计划，但会增加规划时间。 三、enable_* 开关 – 手动干预优化器 当优化器做出错误选择（例如应该用 Hash Join 却用了 Nested Loop，并且无法通过统计信息修正），你可以临时禁用某种扫描或连接方法，强迫优化器选择正确路径。 所有 enable_* 参数默认为 on，可以在会话级或事务级修改。 开关 作用 enable_seqscan 是否允许顺序扫描。关闭后强制走索引（慎用！通常说明统计信息或 cost 参数有问题） enable_indexscan 是否允许索引扫描 enable_indexonlyscan 是否允许仅索引扫描 enable_bitmapscan 是否允许位图扫描 enable_nestloop 是否允许 Nested Loop 连接 enable_hashjoin 是否允许 Hash Join enable_mergejoin 是否允许 Merge Join enable_partition_pruning […]

SQL 与查询优化（PostgreSQL 篇）· 第四期 物化视图、分区表与批量数据优化 前三期我们深入了执行计划、统计信息、连接算法与高级 SQL 能力。本期聚焦数据架构层面的优化：物化视图、分区表以及批量数据处理技巧。 当单表数据量达到亿级，日常查询和分析变得缓慢时，这些技术能让你在不改业务代码的前提下，获得数量级的性能提升。 一、物化视图 – 预计算的艺术 1.1 什么是物化视图？ 普通视图只是保存一条查询规则，每次访问都会重新执行查询。而物化视图（Materialized View） 会实际存储查询结果，就像一张真实的表。你可以对其创建索引、分析统计信息，甚至进行 ANALYZE。 适用场景： 报表统计（复杂聚合、多表连接），数据不需要绝对实时（容忍秒级或分钟级延迟）。 对外提供预计算结果，避免重复执行昂贵查询。 作为数据仓库的中间层，加速 ETL 后续处理。 基本语法： CREATE MATERIALIZED VIEW daily_sales_summary AS SELECT product_id, date(order_date) AS sale_day, sum(amount) AS total, count(*) AS cnt FROM orders GROUP BY product_id, date(order_date); -- 刷新（全量重建，会锁表） REFRESH MATERIALIZED VIEW daily_sales_summary; -- 并发刷新（不阻塞读取，但需要唯一索引） REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary; 1.2 刷新策略与性能 REFRESH MATERIALIZED VIEW：会锁定视图，禁止读取，直到刷新完成。适合低频（如夜间）全量刷新。 REFRESH MATERIALIZED VIEW CONCURRENTLY：使用临时表增量更新，不阻塞并发的 SELECT。但前提是物化视图上必须存在唯一索引（通常是主键或唯一约束）。刷新时间会比非并发模式长，因为需要比较差异。 注意：如果物化视图非常大（几亿行），并发刷新可能会产生大量临时 I/O 和表膨胀。建议根据业务需求选择刷新频率（每小时/每天），并在低峰期执行。 1.3 增量更新 – 物化视图 + 触发器 PostgreSQL 本身不提供物化视图的增量刷新（类似 Oracle 的快速刷新）。但你可以自己实现： 记录源表的变更（通过触发器写入增量日志表）。 定期将增量数据合并到物化视图中（INSERT ... ON CONFLICT DO UPDATE）。 这需要应用业务逻辑，但能换来近乎实时的预计算结果。 简单示例（基于 daily_sales_summary）： -- 增量表 CREATE TABLE sales_delta (like orders); -- 触发器函数将变更插入增量表... -- 然后定时合并： INSERT INTO daily_sales_summary (product_id, sale_day, total, cnt) SELECT product_id, date(order_date), sum(amount), count(*) FROM sales_delta GROUP BY product_id, date(order_date) ON CONFLICT (product_id, sale_day) DO UPDATE SET total = daily_sales_summary.total + EXCLUDED.total, cnt = daily_sales_summary.cnt + EXCLUDED.cnt; TRUNCATE sales_delta; 1.4 物化视图的索引与统计 物化视图就是一张表，因此可以建立索引、执行 ANALYZE，甚至参与分区。 CREATE INDEX ON daily_sales_summary (sale_day); ANALYZE daily_sales_summary; 查询时直接使用物化视图： SELECT * FROM daily_sales_summary WHERE sale_day = '2025-03-01'; 对比普通视图的性能（数据量 1 亿行订单，按日聚合后仅 10 万行）： 普通视图：每次需扫描 1 亿行，聚合耗时 30 秒。 物化视图：预聚合后只扫描 10 万行，加上索引后响应 < 10 毫秒。 1.5 刷新时避免应用中断 对于需要 7×24 服务的系统，刷新策略可以采用 双视图切换： 创建新物化视图 mv_new。 在 mv_new 上重建索引和统计。 通过一个事务：DROP VIEW mv_current; ALTER VIEW mv_new RENAME TO mv_current; 应用代码中始终指向 mv_current（通过视图或动态表名）。 这种方法可以做到刷新过程毫无停机时间（但需要两倍存储空间）。 […]

SQL 与查询优化（PostgreSQL 篇）· 第三期 连接优化与统计信息深度调优 经过前两期，你已经能读懂执行计划、用好窗口函数与 CTE。本期进入多表连接优化的核心地带，并深挖优化器的“大脑”——统计信息。 我们将剖析三种连接算法背后的代价模型，学会用扩展统计信息解决多列关联的基数误判，最终通过真实案例把执行时间从分钟级降至毫秒级。 一、连接算法深度解析 – 优化器的三种武器 当查询涉及多个表时，PostgreSQL 优化器需要在 Nested Loop、Hash Join、Merge Join 中做出选择，并决定连接顺序。这个决策的基础是 基数估计（每步返回的行数）与 代价计算。 1.1 Nested Loop – 索引依赖之王 原理：对外层结果集的每一行，遍历内层表查找匹配行。 代价：cost = outer_rows * inner_scan_cost 如果内层走索引（Index Scan），inner_scan_cost ≈ O(log N)；如果内层全表扫描，代价爆炸。 适用场景： 外层表很小（例如只返回几十行）。 内层表有高效索引（通常是连接键）。 支持非等值连接（如 a.id > b.id），这是 Hash Join 做不到的。 执行计划识别： Nested Loop (cost=0.29..2150.30 rows=10) -> Index Scan using idx_users_id on users (rows=1) -> Index Scan using idx_orders_user_id on orders (rows=10) 1.2 Hash Join – 中等数据集的王者 原理：先构建一个表的哈希表（通常在内存中），然后扫描另一个表探测。 代价：cost = build_cost + probe_cost ≈ O(outer + inner)（无索引也可）。 适用场景： 两表较大，但连接键无索引或索引选择性不佳。 等值连接（= 或 IN）。 内存足够容纳哈希表（work_mem 参数控制，不足则会 spill 到磁盘，严重降低性能）。 执行计划识别： Hash Join (cost=5000..12000 rows=50000) Hash Cond: (o.user_id = u.id) -> Seq Scan on orders o (rows=1000000) -> Hash (cost=3000..3000 rows=100000) -> Seq Scan on users u 调优点： 增大 work_mem 避免哈希表落盘（监控 temp_files）。 调整连接顺序：让行数较少的表作为 build 表（哈希表源）。 1.3 Merge Join – 排序的交换 原理：两表按连接键预先排序，然后像合并有序数组一样归并。 代价：cost = sort_cost(outer) + sort_cost(inner) + merge_cost。 适用场景： 连接键本身有序（例如已有索引，或子查询带 ORDER BY）。 连接条件为不等式（<, > 等）。 数据量非常大且 Hash Join 因内存不足会频繁落盘时。 执行计划识别： Merge Join (cost=15000..18000 rows=50000) Merge Cond: (o.user_id = u.id) -> Index Scan using idx_orders_user_id on orders (rows=1000000) -> Sort (cost=10000..10200 rows=80000) Sort Key: u.id -> Seq Scan on users u 注意：如果两表都需要显式排序，额外开销可能超过 Hash Join。 1.4 连接顺序 – 基数的艺术 多表连接时（如 A JOIN B JOIN C），优化器会尝试不同的连接顺序。基数估计的准确性直接影响顺序选择。例如： SELECT * FROM A JOIN B […]

SQL 与查询优化（PostgreSQL 篇）· 第二期 窗口函数与 CTE 的深度优化 继第一期掌握执行计划与索引基础后，本期聚焦 SQL 的高级能力：窗口函数（Window Functions） 与 公共表表达式（CTE）。 你将学会如何用它们写出更简洁高效的查询，同时避开常见的性能陷阱——包括 CTE 物化屏障、递归 CTE 的优化技巧，以及窗口函数与索引的协作之道。 一、窗口函数 – 不改变行数的聚合利器 1.1 核心概念 窗口函数在保留每一行原始数据的同时，基于一组行（窗口）进行计算。 相比于聚合 GROUP BY（行数减少），窗口函数不压缩结果集。 语法模板： 函数() OVER ( [PARTITION BY 分组列] [ORDER BY 排序列] [ROWS/RANGE 窗口帧子句] ) 常见窗口函数： 类别 函数 用途 排名 ROW_NUMBER(), RANK(), DENSE_RANK() 为行分配序号 偏移 LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE() 访问同一分区内前后行 分布 NTILE(n), PERCENT_RANK() 分桶与百分位 常规聚合 SUM(), AVG(), COUNT(), MAX(), MIN() 移动或累积聚合 1.2 典型高效场景 (1) 每组取 Top N（如每个客户最近 3 笔订单） WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders ) SELECT * FROM ranked WHERE rn <= 3; 优化要点： PARTITION BY customer_id 配合索引 (customer_id, order_date DESC) 可实现 仅索引扫描，避免显式排序。 若表巨大，可加 INCLUDE 覆盖其他输出列。 (2) 计算环比/同比（LAG 替代自连接） ❌ 低效的自连接写法： SELECT o1.date, o1.amount, o2.amount AS prev_amount FROM sales o1 LEFT JOIN sales o2 ON o1.date = o2.date + interval '1 day'; ✅ 窗口函数高效写法： SELECT date, amount, LAG(amount, 1) OVER (ORDER BY date) AS prev_amount FROM sales; 自连接会产生嵌套循环或合并连接，而窗口函数只需一次扫描，按序计算。 若 date 字段有唯一索引，可进一步走 Index Only Scan。 (3) 移动窗口聚合（7 日移动平均） SELECT date, amount, AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d FROM sales; ROWS 基于物理行号，适合日期连续场景；若日期有缺失，建议用 RANGE 基于实际时间间隔（需配合 ORDER BY date 且 date 类型可加减）。 1.3 窗口函数的执行计划与优化 执行计划中窗口函数体现为 WindowAgg 节点。 […]