含SUM、CASE语句的千万级大表慢SQL查询优化方案问询

一、查询语句调整 #

• 去掉对entrydate的无效函数转换：原查询中(TO_CHAR(entrydate, 'YYYY-MM-DD')::DATE)的写法会对字段加函数运算，导致无法命中索引，直接改为时间范围匹配即可：

  entrydate >= '2021-01-01 00:00:00' AND entrydate < '2021-02-01 00:00:00'
  

  该写法覆盖了1月全量时间，同时支持索引命中。
• 删除不必要的子查询和SELECT *写法：不需要嵌套子查询，也不需要查询全表字段，仅提取本次需要的date、country_name、atdelay字段即可，大幅减少数据IO开销。
• 修正拼写错误：原查询中Brezil是Brazil的拼写错误，会导致该列求和结果始终为0，可同步修正。
• 检查冗余条件：原查询WHERE条件中存在code IS NOT NULL，但你提供的表结构中没有code字段，若该字段不存在可直接删除该条件，避免额外过滤开销。

优化后的参考SQL如下：

SELECT 
  EXTRACT(HOUR FROM date) AS HOUR,
  SUM(CASE WHEN country_name = 'France' THEN atdelay ELSE 0 END) AS France,
  SUM(CASE WHEN country_name = 'USA' THEN atdelay ELSE 0 END) AS USA,
  SUM(CASE WHEN country_name = 'China' THEN atdelay ELSE 0 END) AS China,
  SUM(CASE WHEN country_name = 'Brazil' THEN atdelay ELSE 0 END) AS Brazil,
  SUM(CASE WHEN country_name = 'Argentine' THEN atdelay ELSE 0 END) AS Argentine,
  SUM(CASE WHEN country_name = 'Equator' THEN atdelay ELSE 0 END) AS Equator,
  SUM(CASE WHEN country_name = 'Maroc' THEN atdelay ELSE 0 END) AS Maroc,
  SUM(CASE WHEN country_name = 'Egypt' THEN atdelay ELSE 0 END) AS Egypt
FROM Contry
WHERE 
  entrydate >= '2021-01-01 00:00:00' 
  AND entrydate < '2021-02-01 00:00:00'
  -- 若code字段不存在直接删除下面这行
  AND code IS NOT NULL
GROUP BY HOUR
ORDER BY HOUR ASC;

注意：原SQL中用到的atdelay字段未出现在你提供的表结构中，请确认该字段存在后执行上述语句。

二、表结构优化 #

• 给entrydate加普通索引：原表entrydate字段没有索引，查询时会全表扫描5000万行数据，加索引后可快速过滤出1月的目标数据。
• 优先建联合覆盖索引：最优方案是创建包含所有查询用到字段的联合索引，索引顺序参考过滤优先级排列：(entrydate, country_name, date, atdelay)，如果需要保留code IS NOT NULL条件，可把code也加到索引首位。该索引可以让查询直接走索引获取所有需要的数据，不需要回表查询主键数据，性能提升最明显。

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