如何优化UNION查询以实现指定日期范围的分页账本报表？

核心思路 #

不用先取出全部数据，而是借助数据库索引优化和合理的SQL结构，先筛选指定日期范围内的记录，合并后直接分页，同时保证排序的正确性。

可行方案 #

方案1：UNION ALL合并后直接分页（推荐）

先给Sales和Purchase表的transaction_date字段添加索引，确保日期范围查询能命中索引，避免全表扫描。再用UNION ALL合并两张表的符合条件的记录，排序后直接分页。

示例SQL：

-- 给两张表添加日期索引（仅需执行一次）
CREATE INDEX idx_sales_trans_date ON Sales(transaction_date);
CREATE INDEX idx_purchase_trans_date ON Purchase(transaction_date);

-- 分页查询语句
SELECT * FROM (
    SELECT 
        transaction_date, 
        'Sales' AS transaction_type, 
        amount, 
        customer_id, 
        invoice_no 
    FROM Sales
    WHERE transaction_date BETWEEN '2024-01-01' AND '2024-01-31'
    
    UNION ALL
    
    SELECT 
        transaction_date, 
        'Purchase' AS transaction_type, 
        amount, 
        vendor_id, 
        bill_no 
    FROM Purchase
    WHERE transaction_date BETWEEN '2024-01-01' AND '2024-01-31'
) AS combined
ORDER BY transaction_date DESC, transaction_type
LIMIT 10 OFFSET 20; -- 第3页，每页10条

说明：

• UNION ALL比UNION高效，因为不会执行去重操作（销售和采购记录本身无重复）
• 日期索引会让数据库快速筛选出指定范围内的记录，无需扫描全表
• 排序时加上transaction_type，避免相同日期的记录排序不稳定，导致分页数据重复或遗漏

方案2：用ROW_NUMBER()筛选分页范围

如果需要更精准的行号控制，可以给合并后的记录添加行号，再过滤行号范围，效果和方案1一致，但写法更直观。

示例SQL：

WITH combined AS (
    SELECT 
        transaction_date, 
        'Sales' AS transaction_type, 
        amount, 
        customer_id, 
        invoice_no 
    FROM Sales
    WHERE transaction_date BETWEEN '2024-01-01' AND '2024-01-31'
    
    UNION ALL
    
    SELECT 
        transaction_date, 
        'Purchase' AS transaction_type, 
        amount, 
        vendor_id, 
        bill_no 
    FROM Purchase
    WHERE transaction_date BETWEEN '2024-01-01' AND '2024-01-31'
), ranked AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY transaction_date DESC, transaction_type) AS row_num
    FROM combined
)
SELECT * FROM ranked
WHERE row_num BETWEEN 21 AND 30; -- 对应OFFSET 20 LIMIT 10

关键优化点 #

• 索引优化：必须确保日期字段有索引，这是避免全表扫描的核心
• **避免SELECT ***：只查询需要的字段，减少数据传输和内存占用
• 稳定排序：排序条件要包含区分度高的字段，防止分页时数据偏移

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