代码之家 › 专栏 › 技术社区 › èèw

关于“UNION ALL”语句的一个问题,MYSQL

sql mysql

-1

èèw · 技术社区 · 6 年前

最近,我编写了如下SQL查询:

SELECT DESCR, ip, CPUCpuUtil_day, CPUWio_day
FROM (SELECT res.res_descr DESCR,
node.ip ip,
(ifnull(ROUND(max(CASE WHEN kpi_no = 100000041 THEN k.valuemax END), 2), 0)) CPUCpuUtil_day,
(ifnull(ROUND(max(CASE WHEN kpi_no = 100000041 THEN k.valuemax END), 2), 0)) CPUWio_day
      FROM res_object res
      LEFT JOIN (SELECT res.res_id, p.kbp, p.valuemax, p.time_id, p.kpi_no
      FROM res_object res
      LEFT JOIN pm_day_p_reshost p ON p.kbp = res.res_id
      AND (p.kpi_no = 100000041 OR p.kpi_no = 100000041) WHERE res.classname = 'ResHost'
      AND p.time_id >= '20180913'
      AND p.time_id < '20180914') k ON k.res_id = res.res_id, res_node node, res_multiselect rs, res_dim_os os
      WHERE rs.multiselect_id = res.multiselect_id
      AND node.res_id = res.res_id
      AND os.os_id = node.os_id
      AND OS.DESCR = 'Linux'
      GROUP BY res.res_descr, node.ip) x

由于数据量巨大,此查询将花费一分钟以上的时间向我显示数据。但如果我加上UNION ALL语句: UNION ALL SELECT 1 res_id, 1 valuemax, 1 kbp, 1 time_id, 1 kpi_no 或UNION ALL语句,该语句在 AND p.time_id >= '20180913' AND p.time_id < '20180914' ,此查询将在三秒内完成。
为什么会这样?为什么在添加这样一个UNION-ALL语句之后会这么快呢?
我想知道它的原理。
任何回答都将不胜感激。

1 回复 | 直到 6 年前

ScaisEdge 6 年前

这只是一个建议(但需要评论)

查看原始查询似乎有一些奇怪的编码

用逗号分隔的列是什么意思 k在k.res\u id=资源资源id,res\u node node,res\u multiselect rs,res\u dim\u os

假设您在where条件下混合了explict join和implict join sintax basec。您应该只使用explict join sintax,而不使用旧的implict sintax

(选择两个相同的值,列别名为CPUCpuUtil\u day和CPUWio\u day)

    SELECT DESCR
        , ip
        , CPUCpuUtil_day
        , CPUWio_day
    FROM (
        SELECT res.res_descr DESCR
            , node.ip ip
            , (ifnull(ROUND(max(CASE WHEN kpi_no = 100000041 
                            THEN k.valuemax END), 2), 0)) CPUCpuUtil_day
            , (ifnull(ROUND(max(CASE WHEN kpi_no = 100000041 
                            THEN k.valuemax END), 2), 0)) CPUWio_day
         FROM res_object res
         LEFT JOIN (
            SELECT res.res_id
                , p.kbp
                , p.valuemax
                , p.time_id
                , p.kpi_no
            FROM res_object res
            INNER JOIN pm_day_p_reshost p ON p.kbp = res.res_id
                AND (p.kpi_no = 100000041 OR p.kpi_no = 100000041) 
                    AND res.classname = 'ResHost'
                        AND p.time_id >= '20180913'
                            AND p.time_id < '20180914'
            ) k ON k.res_id = res.res_id 
         INNER JOIN res_multiselect rs ON rs.multiselect_id = res.multiselect_id
         INNER JOIN res_node node ON  node.res_id = res.res_id
         INNER JOIN res_dim_os os ON os.os_id = node.os_id
            AND OS.DESCR = 'Linux'
        GROUP BY res.res_descr, node.ip 
    ) x