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 服务的系统，刷新策略可以采用 双视图切换：

1. 创建新物化视图 mv_new。
2. 在 mv_new 上重建索引和统计。
3. 通过一个事务：DROP VIEW mv_current; ALTER VIEW mv_new RENAME TO mv_current;
4. 应用代码中始终指向 mv_current（通过视图或动态表名）。

这种方法可以做到刷新过程毫无停机时间（但需要两倍存储空间）。

二、分区表 – 拆解庞然大物

当表数据量达到几十亿行，即使索引优化也难以避免扫描大量无用数据。分区（Partitioning） 将一个大表物理上拆分为多个子表，查询时仅扫描相关分区，大幅减少 I/O。

2.1 声明式分区（推荐）

PostgreSQL 10+ 支持声明式分区，通过 PARTITION BY 定义分区策略。

(1) 范围分区（Range Partitioning）

最适合时间序列数据，例如按订单日期分区。

CREATE TABLE orders (
    id bigserial,
    order_date timestamptz NOT NULL,
    customer_id int,
    amount numeric
) PARTITION BY RANGE (order_date);

-- 创建分区
CREATE TABLE orders_2025_q1 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

CREATE TABLE orders_2025_q2 PARTITION OF orders
FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');

-- 还可以手动创建默认分区，但一般不推荐

分区裁剪（Partition Pruning）： 查询 WHERE order_date BETWEEN '2025-02-01' AND '2025-02-28'，优化器只扫描 orders_2025_q1 分区。

(2) 列表分区（List Partitioning）

适合按枚举值分区，例如按地区、状态。

CREATE TABLE logs (
    id bigserial,
    log_level text,
    message text
) PARTITION BY LIST (log_level);

CREATE TABLE logs_error PARTITION OF logs FOR VALUES IN ('ERROR', 'FATAL');
CREATE TABLE logs_warning PARTITION OF logs FOR VALUES IN ('WARNING');
CREATE TABLE logs_info PARTITION OF logs FOR VALUES IN ('INFO', 'DEBUG');

(3) 哈希分区（Hash Partitioning）

将数据均匀分布到指定数量的分区，用于负载均衡。例如按 customer_id 哈希分区，将不同客户分散到不同分区。

CREATE TABLE customers (
    id bigserial,
    name text,
    region text
) PARTITION BY HASH (id);

CREATE TABLE customers_p1 PARTITION OF customers FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- ...

2.2 分区表的索引策略

每个分区可以独立建立索引，也可以创建分区索引（自动在所有分区上创建）。

-- 在分区表上直接创建索引，会自动传播到已有和未来分区
CREATE INDEX ON orders (order_date);
CREATE INDEX ON orders (customer_id);

-- 在主表上唯一索引必须包含分区键
CREATE UNIQUE INDEX ON orders (id, order_date);  -- order_date 是分区键

本地索引（每个分区独立索引）是默认行为。 全局索引（跨越所有分区的单个 B 树）PostgreSQL 目前不支持（只有部分功能如主键约束需要包含分区键）。这既是限制也是好处：分区裁剪时无需查看其他分区索引，效率高。

2.3 分区裁剪的执行计划检查

EXPLAIN SELECT * FROM orders WHERE order_date = '2025-02-15';

计划应显示 Append 节点下仅包含 orders_2025_q1 的 Seq Scan 或 Index Scan。如果看到多个分区被扫描，说明裁剪未生效，常见原因：

• 分区键上使用了函数（date(order_date)）。
• 查询条件与分区边界类型不匹配（例如时间戳与字符串比较）。
• 未启用 enable_partition_pruning（默认开启）。

2.4 分区管理 – 自动创建与删除

对于按时间分区，通常需要自动创建未来分区、删除过期分区。

-- 删除旧分区（比保留数据快得多）
DROP TABLE orders_2024_q1;

-- 或者将分区从主表分离（保留为普通表）
ALTER TABLE orders DETACH PARTITION orders_2024_q1;

-- 添加新分区
CREATE TABLE orders_2025_q3 PARTITION OF orders
FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');

可以结合 pg_cron 或操作系统定时任务，定期运行这些 DDL。

2.5 分区表 + 物化视图 = 终极组合

对于极大规模数据，可以先按日分区，再在分区表上建立物化视图进行小时级预聚合。

CREATE MATERIALIZED VIEW hourly_stats
AS SELECT date_trunc('hour', order_date) AS hour,
          product_id,
          sum(amount) AS total
   FROM orders
   GROUP BY 1,2;

-- 刷新时利用分区裁剪
REFRESH MATERIALIZED VIEW CONCURRENTLY hourly_stats;

查询最近几小时数据时，先通过物化视图，再结合分区表验证细节。

三、批量数据优化 – 让大数据写入飞起来

3.1 COPY – 比 INSERT 快一个数量级

当需要导入大量数据（CSV、文本）时，COPY 是首选。

COPY orders FROM '/data/orders.csv' DELIMITER ',' CSV HEADER;

• 避免触发过多的触发器、约束检查。
• 写入数据后记得手动运行 ANALYZE。

对比：1000 万行数据，INSERT 单条插入需要 2 小时，COPY 只需 40 秒。

3.2 INSERT ... ON CONFLICT 实现 upsert

避免先 SELECT 再决定 INSERT/UPDATE 的二次开销。

INSERT INTO product_stats (product_id, sale_day, total)
VALUES (1, '2025-03-15', 100)
ON CONFLICT (product_id, sale_day) DO UPDATE
SET total = product_stats.total + EXCLUDED.total;

• 需要唯一约束或唯一索引。
• 可以搭配排除表（EXCLUDED）引用提议的值。

3.3 批量 UPDATE / DELETE 的分批处理

直接执行 UPDATE huge_table SET ... WHERE ... 可能导致长事务、锁竞争、表膨胀。推荐分批处理：

-- 每次处理 10000 行
DO $$
DECLARE
    rows_affected int;
BEGIN
    LOOP
        WITH cte AS (
            SELECT id FROM huge_table
            WHERE status = 'old'
            LIMIT 10000
            FOR UPDATE SKIP LOCKED   -- 避免锁冲突
        )
        UPDATE huge_table SET status = 'archived'
        FROM cte WHERE huge_table.id = cte.id;

        GET DIAGNOSTICS rows_affected = ROW_COUNT;
        EXIT WHEN rows_affected = 0;
        COMMIT;  -- 确保自动提交，或在外层循环控制
    END LOOP;
END $$;

• 使用 FOR UPDATE SKIP LOCKED 避免等待其他会话。
• 每批提交，减少膨胀和锁持有时间。
• 对删除也类似：DELETE FROM huge_table WHERE id IN (SELECT ... LIMIT 10000)。

3.4 TRUNCATE vs DELETE

• 删除全表：TRUNCATE 是 DDL，速度快，无法回滚（在事务中可回滚，但会阻塞并发），重置高水位线，释放空间。
• 带条件的删除：必须用 DELETE，且通常需要分批。
• 对于分区表，DROP PARTITION 比批量 DELETE 快数百倍。

3.5 关闭约束与索引，最后重建

极大数据量的导入，可以先禁用外键约束、删除索引，导入完成后重建。

ALTER TABLE orders DROP CONSTRAINT orders_product_id_fkey;
DROP INDEX idx_orders_date;
COPY orders FROM 'data.csv';
CREATE INDEX idx_orders_date ON orders(order_date);
ALTER TABLE orders ADD CONSTRAINT ...;

这样可以避免每插入一行都更新索引和外键检查，提速 5~10 倍。

四、实战案例：千万级日志表从 30 秒到 0.1 秒

背景

Web 访问日志表 weblogs，已有 2 亿行，每天增长 500 万行。 业务需求：查询某天某 URL 的访问量趋势（按小时聚合）。

原表结构（未分区）：

CREATE TABLE weblogs (
    id bigserial primary key,
    log_time timestamptz,
    url text,
    user_id bigint,
    ip inet,
    status_code int
);
CREATE INDEX idx_weblogs_time ON weblogs(log_time);

原查询（生成某天按小时统计）：

SELECT date_trunc('hour', log_time) AS hour, count(*)
FROM weblogs
WHERE log_time BETWEEN '2025-03-15 00:00:00' AND '2025-03-15 23:59:59'
  AND url = '/product/123'
GROUP BY 1
ORDER BY 1;

执行时间：32 秒（扫描当天所有数据，约 500 万行，再过滤 URL）。

优化步骤

1. 按天分区

CREATE TABLE weblogs (LIKE weblogs INCLUDING ALL) PARTITION BY RANGE (log_time);

-- 生成近 30 个日分区（略）
CREATE TABLE weblogs_20250315 PARTITION OF weblogs
FOR VALUES FROM ('2025-03-15') TO ('2025-03-16');

迁移数据（使用 INSERT INTO weblogs SELECT * FROM old_weblogs，好在晚上执行）。 分区后查询相同条件：

Append
  Subquery Scan on weblogs_20250315
    ->  Index Scan using idx_weblogs_20250315_url_time on weblogs_20250315
          Index Cond: (url = '/product/123' AND log_time >= ...)

时间降到 2.5 秒（只扫描当天分区，但仍需全分区扫描）。

2. 完善索引

对每个分区创建复合索引 (url, log_time)，实现索引快速定位。因为分区裁剪后数据量已从 2 亿降到 500 万，复合索引过滤 url 后只扫描几百行。 时间降到 0.3 秒。

3. 创建物化视图按天预统计

业务上不需要实时，每小时刷新即可。

CREATE MATERIALIZED VIEW weblogs_hourly AS
SELECT date(log_time) AS day,
       date_trunc('hour', log_time) AS hour,
       url,
       count(*) AS cnt
FROM weblogs
GROUP BY 1,2,3;

CREATE INDEX ON weblogs_hourly (day, url, hour);

现在查询改为：

SELECT hour, cnt
FROM weblogs_hourly
WHERE day = '2025-03-15' AND url = '/product/123'
ORDER BY hour;

响应时间：0.1 秒（仅扫描物化视图的 24 行数据）。

4. 自动刷新（每小时执行）

REFRESH MATERIALIZED VIEW CONCURRENTLY weblogs_hourly;

效果总结

最终完成了超过两个数量级的性能提升，且对业务透明（可保留原表作为归档）。

五、常见陷阱与最佳实践

六、总结与下期预告

本期要点

• 掌握物化视图的创建、刷新（并发/非并发）以及增量更新的自研方案。
• 理解声明式分区的三种策略（范围、列表、哈希），学会利用分区裁剪大幅减少扫描数据量。
• 学会批量数据优化的核心技巧：COPY、ON CONFLICT、分批 DML、TRUNCATE、以及导入前删索引。
• 通过日志表实战，展示了分区表 + 复合索引 + 物化视图的组合效果，性能提升 300 倍。

下期预告

第五期：查询优化器内部机制与高级调参

• 优化器的代价模型参数详解（cpu_tuple_cost、cpu_index_tuple_cost、effective_cache_size 等）如何影响执行计划。
• 理解 enable_* 系列开关（如 enable_nestloop、enable_hashjoin）在调试中的使用。
• 使用 pg_hint_plan 强制执行计划（适合无法修改 SQL 的遗留系统）。
• 并行查询的原理与调优（max_parallel_workers_per_gather、分区并行）。
• 真实案例：因优化器参数不当导致索引失效，通过调整参数恢复性能。

欢迎在评论区分享你在大数据量下遇到的物化视图、分区或批量操作的棘手问题，我们将在后续内容中选取典型分析。

不是所有大表都需要分区，但遇到对的场景，它能拯救你的午夜告警。 我们第五期见。