实现多表可筛选、可排序分页表格视图的最佳SQL方案

针对你提到的多表合并后支持筛选、排序、分页的需求，以下是几种实用的PostgreSQL实现方案，按场景适配性排序：

一、UNION ALL + 子查询/CTE（通用实时场景） #

这是最直接的实时查询方案，核心思路是先在单表内完成筛选，再合并结果，最后统一排序分页，避免先合并大数据集再过滤的性能浪费。

示例代码 #

WITH filtered_data AS (
  -- 单表内先筛选符合条件的数据
  SELECT id, name, common_prop FROM table_a
  WHERE common_prop = 'some value'
  UNION ALL
  SELECT id, name, common_prop FROM table_b
  WHERE common_prop = 'some value'
  UNION ALL
  SELECT id, name, common_prop FROM table_c
  WHERE common_prop = 'some value'
)
-- 统一排序分页
SELECT id, name, common_prop
FROM filtered_data
ORDER BY id
LIMIT 30 OFFSET 30; -- 第2页，每页30条

关键优化点 #

• 用UNION ALL替代UNION：如果各表的id不会重复，UNION ALL无需去重，性能比UNION高很多；若存在重复ID，可给每个表的ID加标识前缀（如a_123）或改用UUID避免冲突。
• 给每个表建立复合索引：针对筛选字段+排序字段创建索引，让单表筛选和排序直接走索引，避免全表扫描：

  CREATE INDEX idx_table_a_common_prop_id ON table_a(common_prop, id);
  CREATE INDEX idx_table_b_common_prop_id ON table_b(common_prop, id);
  CREATE INDEX idx_table_c_common_prop_id ON table_c(common_prop, id);
  

二、物化视图（非实时/低更新频率场景） #

如果业务可以接受数据有一定延迟（比如分钟级或小时级更新），物化视图是性能最优的选择——预先将多表合并结果持久化到物理表，并建立索引，查询时直接读取预计算数据。

示例代码 #

-- 创建物化视图，预合并所有表数据
CREATE MATERIALIZED VIEW combined_data AS
SELECT id, name, common_prop FROM table_a
UNION ALL
SELECT id, name, common_prop FROM table_b
UNION ALL
SELECT id, name, common_prop FROM table_c;

-- 为物化视图建立筛选+排序的复合索引
CREATE INDEX idx_combined_common_prop_id ON combined_data(common_prop, id);

-- 直接查询物化视图完成分页
SELECT id, name, common_prop
FROM combined_data
WHERE common_prop = 'some value'
ORDER BY id
LIMIT 30 OFFSET 30;

维护方式 #

当源表数据更新时，需要手动或定时刷新物化视图：

-- 全量刷新（会锁表，适合低峰期）
REFRESH MATERIALIZED VIEW combined_data;

-- 增量刷新（PostgreSQL 12+支持，需提前配置）
REFRESH MATERIALIZED VIEW CONCURRENTLY combined_data;

可配合pg_cron扩展设置定时刷新任务，实现自动化维护。

三、分区表（逻辑同源数据场景） #

如果这几个表是同一类数据的拆分存储（比如按业务模块、时间范围拆分），可以将它们改造为分区表，通过父表实现统一查询，PostgreSQL会自动扫描符合条件的分区，性能接近单表查询。

示例代码 #

-- 创建父表，定义分区规则（这里假设按来源标识分区，需给源表添加source字段）
CREATE TABLE combined_table (
  id INT,
  name VARCHAR(100),
  common_prop VARCHAR(100),
  source VARCHAR(20) -- 标识数据来源：table_a/table_b/table_c
) PARTITION BY LIST (source);

-- 将现有表挂载为分区
ALTER TABLE table_a ADD COLUMN source VARCHAR(20) DEFAULT 'table_a';
ALTER TABLE table_a ATTACH PARTITION combined_table FOR VALUES IN ('table_a');

ALTER TABLE table_b ADD COLUMN source VARCHAR(20) DEFAULT 'table_b';
ALTER TABLE table_b ATTACH PARTITION combined_table FOR VALUES IN ('table_b');

ALTER TABLE table_c ADD COLUMN source VARCHAR(20) DEFAULT 'table_c';
ALTER TABLE table_c ATTACH PARTITION combined_table FOR VALUES IN ('table_c');

-- 统一查询父表，自动扫描对应分区
SELECT id, name, common_prop
FROM combined_table
WHERE common_prop = 'some value'
ORDER BY id
LIMIT 30 OFFSET 30;

优势 #

• 支持实时数据更新，源表数据变更会自动同步到父视图；
• 查询时仅扫描符合筛选条件的分区，避免无效数据扫描，性能优异。

进阶优化：解决大OFFSET分页性能问题 #

当分页页数很大时，OFFSET会导致数据库扫描大量无用数据，此时建议改用键集分页（Keyset Pagination）：记录上一页的最后一条数据的排序键（比如id），下一页查询时直接从该键之后取数：

WITH filtered_data AS (
  SELECT id, name, common_prop FROM table_a
  WHERE common_prop = 'some value' AND id > 100 -- 上一页最后一个id是100
  UNION ALL
  SELECT id, name, common_prop FROM table_b
  WHERE common_prop = 'some value' AND id > 100
  UNION ALL
  SELECT id, name, common_prop FROM table_c
  WHERE common_prop = 'some value' AND id > 100
)
SELECT id, name, common_prop
FROM filtered_data
ORDER BY id
LIMIT 30;

这种方式可以完全利用索引，避免大OFFSET带来的性能损耗。

内容的提问来源于stack exchange，提问作者Johnczek