PG SQL含ORDER BY、LIMIT的UNION查询性能优化咨询

问题分析 #

原查询先对两张亿级表分别全量排序，再合并后再次排序取前5条，这种方式会触发全表扫描+全表排序，在数据量极大时性能极差，完全没必要处理所有数据。

优化思路 #

采用归并排序合并阶段的思路：利用两表id字段的索引（必须有索引），分别从两张表按id升序逐个读取数据，每次取当前两表指针指向的最小id数据，直到凑够LIMIT指定的5条数据。全程只读取必要的少量数据，避免全表扫描。

前提条件 #

确保两张表的id字段存在主键索引或普通B+树索引，这样按id排序的单行查询能快速定位，无需扫描全表。

通用SQL实现（以PostgreSQL为例，支持LATERAL JOIN） #

WITH RECURSIVE merged AS (
    -- 初始化：获取两表的第一条最小id数据
    SELECT 
        one.id AS one_id, one.name AS one_name,
        two.id AS two_id, two.name AS two_name,
        CASE WHEN one.id <= two.id THEN one.id ELSE two.id END AS current_id,
        CASE WHEN one.id <= two.id THEN one.name ELSE two.name END AS current_name,
        -- 标记本次是否取了one表的数据
        CASE WHEN one.id <= two.id THEN 1 ELSE 0 END AS one_taken,
        -- 标记本次是否取了two表的数据
        CASE WHEN one.id > two.id THEN 1 ELSE 0 END AS two_taken,
        1 AS row_num
    FROM (SELECT id, name FROM one ORDER BY id LIMIT 1) one
    CROSS JOIN (SELECT id, name FROM two ORDER BY id LIMIT 1) two

    UNION ALL

    -- 递归迭代：根据上一次的取数结果，从对应表取下一条数据，再比较取最小
    SELECT
        -- 更新one表的当前数据（如果上一次取的是one）
        CASE WHEN m.one_taken = 1 THEN next_one.id ELSE m.one_id END,
        CASE WHEN m.one_taken = 1 THEN next_one.name ELSE m.one_name END,
        -- 更新two表的当前数据（如果上一次取的是two）
        CASE WHEN m.two_taken = 1 THEN next_two.id ELSE m.two_id END,
        CASE WHEN m.two_taken = 1 THEN next_two.name ELSE m.two_name END,
        -- 确定当前要取的最小id
        CASE 
            WHEN m.one_taken = 1 AND next_one.id <= COALESCE(m.two_id, 999999999) THEN next_one.id
            WHEN m.two_taken = 1 AND next_two.id <= COALESCE(m.one_id, 999999999) THEN next_two.id
            ELSE CASE WHEN m.one_id <= m.two_id THEN m.one_id ELSE m.two_id END
        END AS current_id,
        -- 确定当前要取的name
        CASE 
            WHEN m.one_taken = 1 AND next_one.id <= COALESCE(m.two_id, 999999999) THEN next_one.name
            WHEN m.two_taken = 1 AND next_two.id <= COALESCE(m.one_id, 999999999) THEN next_two.name
            ELSE CASE WHEN m.one_id <= m.two_id THEN m.one_name ELSE m.two_name END
        END AS current_name,
        -- 标记本次是否取了one表的数据
        CASE 
            WHEN m.one_taken = 1 AND next_one.id <= COALESCE(m.two_id, 999999999) THEN 1
            ELSE 0
        END AS one_taken,
        -- 标记本次是否取了two表的数据
        CASE 
            WHEN m.two_taken = 1 AND next_two.id <= COALESCE(m.one_id, 999999999) THEN 1
            ELSE 0
        END AS two_taken,
        m.row_num + 1 AS row_num
    FROM merged m
    -- 从one表取下一条数据（仅当上一次取的是one时）
    LEFT JOIN LATERAL (SELECT id, name FROM one WHERE id > m.one_id ORDER BY id LIMIT 1) next_one ON m.one_taken = 1
    -- 从two表取下一条数据（仅当上一次取的是two时）
    LEFT JOIN LATERAL (SELECT id, name FROM two WHERE id > m.two_id ORDER BY id LIMIT 1) next_two ON m.two_taken = 1
    WHERE m.row_num < 5 -- 直到取够5条数据
)
-- 提取最终结果，按取数顺序输出
SELECT current_id AS id, current_name AS name
FROM merged
ORDER BY row_num;

MySQL兼容版（无LATERAL JOIN） #

WITH RECURSIVE merged AS (
    -- 初始化：获取两表第一条最小id数据
    SELECT 
        (SELECT id FROM one ORDER BY id LIMIT 1) AS one_id,
        (SELECT name FROM one ORDER BY id LIMIT 1) AS one_name,
        (SELECT id FROM two ORDER BY id LIMIT 1) AS two_id,
        (SELECT name FROM two ORDER BY id LIMIT 1) AS two_name,
        1 AS row_num
    UNION ALL
    -- 递归迭代：更新对应表的当前数据
    SELECT
        CASE WHEN m.one_id <= m.two_id THEN (SELECT id FROM one WHERE id > m.one_id ORDER BY id LIMIT 1) ELSE m.one_id END,
        CASE WHEN m.one_id <= m.two_id THEN (SELECT name FROM one WHERE id > m.one_id ORDER BY id LIMIT 1) ELSE m.one_name END,
        CASE WHEN m.one_id > m.two_id THEN (SELECT id FROM two WHERE id > m.two_id ORDER BY id LIMIT 1) ELSE m.two_id END,
        CASE WHEN m.one_id > m.two_id THEN (SELECT name FROM two WHERE id > m.two_id ORDER BY id LIMIT 1) ELSE m.two_name END,
        m.row_num + 1
    FROM merged m
    WHERE m.row_num < 5
)
-- 提取每次迭代的最小id数据
SELECT 
    CASE WHEN one_id <= COALESCE(two_id, 999999999) THEN one_id ELSE two_id END AS id,
    CASE WHEN one_id <= COALESCE(two_id, 999999999) THEN one_name ELSE two_name END AS name
FROM merged
ORDER BY row_num;

性能优势 #

• 原查询时间复杂度为O(N log N + M log M)（N、M为两表数据量），优化后为O(K)（K为LIMIT值，此处为5），性能提升几个数量级
• 全程仅读取K*2条以内的数据，完全避免全表扫描和全表排序

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