如何让聚合查询的过滤操作高效利用索引提升执行性能

优化思路 #

• 原有查询的性能瓶颈在于：先全量关联两个表、对所有文档分组计算平均值后才做过滤，大量不符合条件的文档参与了运算，浪费了资源。
• 可以把过滤逻辑前置，先在心跳表层面完成分组计算和过滤，仅保留平均心跳>5的文档ID，再关联文档表取内容，这样能大幅减少参与运算的数据量。
• 你已经建立的(docid, heartbeat)联合索引刚好可以作为覆盖索引支撑心跳表的聚合计算，不需要回表查询心跳表的其他字段，性能最优。

优化后的SQL语句 #

SELECT
  d.id,
  d.content,
  h.beats
FROM
  docs d
INNER JOIN (
  SELECT 
    docid,
    AVG(heartbeat) AS beats
  FROM heartbeats
  GROUP BY docid
  HAVING AVG(heartbeat) > 5
) h ON d.id = h.docid
ORDER BY h.beats DESC

补充说明 #

• 平均心跳>5的文档必然存在至少一条心跳记录，所以用INNER JOIN替代原LEFT JOIN不会丢数据，执行效率更高。
• 优化后可以用EXPLAIN查看执行计划，确认心跳表的聚合操作走了(docid, heartbeat)索引，没有全表扫描即可。

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