Union关联相似表结果集的SQL查询性能优化方案咨询

优化方案 #

1. 优先替换UNION为UNION ALL
   原写法用UNION会对两个结果集做全量排序去重，开销极高。如果你的业务场景下两个子查询返回的行天然不会重复（比如section关联和graph关联出来的元素没有重叠），直接替换为UNION ALL就能获得数倍的性能提升，不需要改其他逻辑。

2. 重构查询逻辑避免重复扫描公共表
   原写法两次子查询都重复扫描了charting_views、charting_view_translations、charting_elements、charting_element_translations这四张公共表，数据量大的话会浪费大量IO。可以先把两组差异关联的逻辑合并成一个中间结果集，再和公共表做一次关联即可，参考写法如下：

SELECT 
    cv.orig_charting_view_id, 
    cvt.view_name, 
    cvt.view_label, 
    cv.version, 
    ce.orig_charting_element_id, 
    ce.version, 
    cet.element_name, 
    cet.element_label, 
    ce.element_type, 
    ce.element_type_meaning, 
    ce.beg_effective_dt_tm
FROM charting_views cv
INNER JOIN charting_view_translations cvt 
    ON cvt.charting_view_id = cv.charting_view_id
-- 先合并两组关联逻辑的结果
INNER JOIN (
    -- 第一组section关联
    SELECT 
        scr.orig_charting_view_id,
        scr.charting_view_version,
        esr.orig_charting_element_id,
        esr.charting_element_version
    FROM section_view_reltns scr
    INNER JOIN element_section_reltns esr 
        ON esr.orig_charting_section_id = scr.orig_charting_section_id 
        AND esr.charting_section_version = scr.charting_section_version
    UNION ALL
    -- 第二组graph关联
    SELECT 
        gvr.orig_charting_view_id,
        gvr.graph_version as charting_view_version,
        ger.element_id as orig_charting_element_id,
        ger.charting_element_version
    FROM graph_view_reltns gvr
    INNER JOIN graph_element_reltns ger 
        ON ger.graph_id = gvr.orig_graph_id 
        AND ger.graph_version = gvr.graph_version
) rel ON rel.orig_charting_view_id = cv.orig_charting_view_id 
      AND rel.charting_view_version = cv.version
INNER JOIN charting_elements ce 
    ON ce.orig_charting_element_id = rel.orig_charting_element_id 
    AND ce.version = rel.charting_element_version
INNER JOIN charting_element_translations cet 
    ON cet.charting_element_id = ce.charting_element_id
WHERE 
    cv.end_effective_dt_tm > NOW() 
    AND cv.status = 2
    AND cet.locale='en-US'
    AND ce.end_effective_dt_tm > NOW() 
    AND ce.status = 2;

3. 索引优化建议
   如果性能还达不到预期，可以检查以下关联字段是否有联合索引：

• section_view_reltns：(orig_charting_view_id, charting_view_version)
• element_section_reltns：(orig_charting_section_id, charting_section_version)
• graph_view_reltns：(orig_charting_view_id)
• graph_element_reltns：(graph_id, graph_version)
• 公共表的关联字段和过滤字段也建议加联合索引，比如charting_views的(status, end_effective_dt_tm, orig_charting_view_id)

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