在Redshift中无需使用OFFSET或ORDER BY选取1000万行数据的可行方法

当处理1000万级别的数据集时，直接用ORDER BY + OFFSET确实会因为全表排序和偏移扫描导致性能瓶颈，以下是Redshift中可行的优化方案：

1. 基于有序主键/唯一列的范围分页 #

如果你的表有自增主键、时间戳这类天然有序的唯一列，完全可以抛弃OFFSET，改用范围条件定位分页：

-- 第一页：取前100000行
SELECT * FROM your_table 
WHERE id BETWEEN 1 AND 100000 
ORDER BY id;

-- 第二页：基于上一页最后一条的id定位
SELECT * FROM your_table 
WHERE id > 100000 AND id <= 200000 
ORDER BY id;

原理：Redshift对有序列（尤其是作为排序键的列）的范围扫描效率极高，不需要全表遍历或排序，直接定位数据块读取。

2. 先过滤再使用窗口函数分页 #

如果必须按非主键列排序，先通过WHERE子句缩小数据集范围，再用ROW_NUMBER()窗口函数实现分页，避免全表排序：

SELECT * FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(ORDER BY sort_col DESC) AS rn
    FROM your_table
    -- 先过滤掉不需要的数据，减少窗口函数处理量
    WHERE category = 'target'
) t
WHERE rn BETWEEN 100001 AND 200000;

提示：如果sort_col是表的排序键，窗口函数的排序操作会自动利用预排序的存储结构，进一步降低成本。

3. 利用排序键优化ORDER BY性能 #

如果一定要用ORDER BY，确保排序列是表的排序键（SORT KEY）：

-- 创建表时指定排序键
CREATE TABLE your_table (
    id INT,
    sort_col TIMESTAMP,
    ...
)
SORTKEY(sort_col);

-- 查询时直接使用排序键排序，无需额外排序操作
SELECT * FROM your_table 
ORDER BY sort_col DESC 
LIMIT 100000 OFFSET 100000;

Redshift会按排序键的顺序存储数据，ORDER BY排序键时无需重新排序，直接按存储顺序读取，能大幅降低ORDER BY的成本。

4. 物化视图预计算排序结果 #

如果是频繁执行的固定排序查询，提前创建物化视图预存排序后的数据：

-- 创建物化视图，预排序数据
CREATE MATERIALIZED VIEW mv_sorted_data AS
SELECT * FROM your_table 
ORDER BY sort_col DESC;

-- 查询时直接从物化视图取数，无需实时排序
SELECT * FROM mv_sorted_data 
LIMIT 100000 OFFSET 100000;

注意：物化视图需要定期刷新（REFRESH MATERIALIZED VIEW），适合数据更新频率较低的场景。

5. 使用游标进行批量读取 #

对于需要逐批处理数据的场景，Redshift的游标可以避免OFFSET的性能问题：

-- 声明游标，指定排序规则
DECLARE cursor_data CURSOR FOR
SELECT * FROM your_table 
ORDER BY sort_col DESC;

-- 批量读取100000行
FETCH NEXT 100000 FROM cursor_data;

游标会维护读取位置，每次FETCH直接从当前位置开始读取，不需要重复扫描前面的数据。

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