SQL优化案例

涉及GROUP BY的SQL优化

• 原SQL

  SELECT
    dict.`name` xAxis,
    SUM( str.num ) num 
  FROM
    ( SELECT city, MAX( `value` ) num FROM `dc_yidong_region_flow_area` GROUP BY `day`, position, city HAVING city IS NOT NULL AND `day` BETWEEN '20200301' AND '20200331'
    ) str
    LEFT JOIN dc_flow_dictionary dict ON str.city = dict.`code` 
    AND dict.`pcode` = '2000250' 
  GROUP BY
    city 
  HAVING
    xAxis IS NOT NULL 
  ORDER BY
    num DESC 
    LIMIT 0,
    10
  

• 优化后

  SELECT
    yidong.`name` xAxis,
    SUM( yidong.num ) num 
  FROM
    (
    SELECT
      flow.`name`,
      MAX( flow.`value` ) num 
    FROM
      (
      SELECT
        str.`day`,
        str.position,
        dict.`name`,
        str.`value` 
      FROM
        dc_yidong_region_flow_area str
        LEFT JOIN dc_flow_dictionary dict ON str.city = dict.`code` 
      WHERE
        dict.`pcode` = '2000250' 
        AND str.`day` BETWEEN '20200301' 
        AND '20200331' 
      ) flow 
    GROUP BY
      flow.`day`,
      flow.`name`,
      flow.position 
    ) yidong 
  GROUP BY
    yidong.`name` 
  ORDER BY
    num DESC 
    LIMIT 0,
    10
  

结论：GROUP BY字段会把符合条件的数据创建一张临时表并根据分组字段进行排序、分组，这个过程是比较耗时间的。因此要尽可能的在GROUP BY之前缩减数据，也就是先筛选出需要进行GROUP BY的数据。