如何为视图返回的数百万条数据生成行号？

AI 小北

2026-7-3

优化方案

问题根源

直接对包含数百万条记录的UNION ALL视图使用无排序的row_number() over()时，数据库需要先将所有合并后的数据加载到内存或临时表中，再全局分配行号，这个过程会消耗大量IO与内存资源，导致查询长时间无法完成。

具体优化方案

1. 为每个子表单独生成行号再合并

避免全局排序，给每个表的行号加上前面所有表的总记录数作为偏移量，让每个表可以独立处理，无需全局聚合：

-- 示例（以3个表为例）
SELECT *, row_number() OVER() AS record_id FROM table_1
UNION ALL
SELECT *, row_number() OVER() + (SELECT COUNT(*) FROM table_1) AS record_id FROM table_2
UNION ALL
SELECT *, row_number() OVER() + (SELECT COUNT(*) FROM table_1) + (SELECT COUNT(*) FROM table_2) AS record_id FROM table_3
-- 后续表以此类推累加前面所有表的记录数

如果表数量较多，可使用CTE预计算累计偏移量（以PostgreSQL为例）：

WITH tbl_counts AS (
    SELECT 'table_1' AS tbl, COUNT(*) AS cnt FROM table_1
    UNION ALL SELECT 'table_2', COUNT(*) FROM table_2
    UNION ALL SELECT 'table_3', COUNT(*) FROM table_3
    -- 继续添加所有涉及的表
),
cumulative_offsets AS (
    SELECT tbl, cnt, SUM(cnt) OVER (ORDER BY tbl) - cnt AS start_offset
    FROM tbl_counts
)
-- 每个子查询关联对应偏移量生成行号
SELECT t.*, row_number() OVER() + co.start_offset AS record_id
FROM table_1 t
JOIN cumulative_offsets co ON co.tbl = 'table_1'
UNION ALL
SELECT t.*, row_number() OVER() + co.start_offset AS record_id
FROM table_2 t
JOIN cumulative_offsets co ON co.tbl = 'table_2'
UNION ALL
-- 后续表以此类推关联查询

2. 使用用户变量替代窗口函数（针对MySQL等支持用户变量的数据库）

用户变量可以在数据遍历过程中直接累加行号，避免全局排序的开销：

SET @current_id = 0;
SELECT *, @current_id := @current_id + 1 AS record_id
FROM (
    SELECT * FROM table_1
    UNION ALL
    SELECT * FROM table_2
    UNION ALL
    -- 继续添加所有涉及的表
) AS combined_data;

3. 给`row_number`指定明确的排序字段

无排序的row_number()会让数据库无法利用索引优化，指定一个存在索引的排序字段（比如各表的主键），可以大幅降低排序开销：

SELECT *, row_number() OVER(ORDER BY primary_key_column) AS record_id
FROM ABC;

如果各表主键不重复，也可以组合多个字段作为排序键，确保排序过程能借助索引完成。

4. 分批次分页查询

如果不需要一次性获取所有数据，采用分页查询减少单次数据处理量：

SELECT *, row_number() OVER(ORDER BY primary_key_column) AS record_id
FROM ABC
LIMIT 1000 OFFSET 0; -- 每次取1000条，逐步调整OFFSET值获取后续数据

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