如何编写子查询优化关联大表的SQL查询性能

现有查询问题说明 #

你当前的查询逻辑存在隐性错误：LEFT JOIN后在全局WHERE条件中添加了date(B.date) = date('2021-01-01')，会直接将左关联转为内关联，b表无匹配的行都会被过滤，不符合左关联的预期效果。

具体优化建议 #

• 提前裁剪大表b的扫描范围
  不要在全局WHERE中过滤b表，改为先对b表做子查询过滤，仅扫描2021-01-01的有效数据再参与关联，避免全表扫描：

  SELECT 
      a.date,
      a.hour,
      a.category_id,
      a.product_id,
      b_filtered.product_name,  
      sum(a.sales) AS sales
  FROM  a
  LEFT JOIN (
      SELECT product_id, product_name 
      FROM b 
      WHERE date(b.date) = '2021-01-01'
  ) AS b_filtered
  ON a.product_id = b_filtered.product_id
  WHERE date(a.date) >= '2021-01-01'
  GROUP BY 1, 2, 3, 4, 5
  

• 新增索引减少扫描成本
  • 给a表的date、product_id字段加联合索引，加快a表的条件过滤和关联匹配速度
  • 给b表的date、product_id、product_name加联合覆盖索引，b表子查询可以直接通过索引拿到所有需要的字段，不需要回表读取原始数据
• 去掉过滤字段上的函数运算
  如果a.date本身是日期类型或者符合yyyy-MM-dd前缀的时间格式，把date(a.date) >= date('2021-01-01')改为a.date >= '2021-01-01'，可以让过滤条件命中a表的date索引，避免全表扫描。b表的过滤条件也可以按相同逻辑调整。
• 先聚合a表再做关联
  先对a表按需要的维度完成聚合，再和b表关联，大幅降低关联阶段的运算量，适合a表相同product_id行数较多的场景：

  WITH a_agg AS (
      SELECT 
          date,
          hour,
          category_id,
          product_id,
          sum(sales) AS sales
      FROM a
      WHERE date >= '2021-01-01'
      GROUP BY 1,2,3,4
  )
  SELECT 
      a_agg.*,
      b_filtered.product_name
  FROM a_agg
  LEFT JOIN (
      SELECT product_id, product_name 
      FROM b 
      WHERE date(b.date) = '2021-01-01'
  ) AS b_filtered
  ON a_agg.product_id = b_filtered.product_id
  

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