MySQL中如何优化关联第二张表统计id对应订单数的查询速度？

慢查询根因 #

• 原SQL使用相关子查询，每返回一行product_today的记录就要单独执行一次订单表计数查询，2万行数据的场景下相当于执行2万次子查询，开销极高
• WHERE条件使用date(t.dtime)函数包裹字段，会导致dtime字段上的索引失效，无法快速筛选指定日期的数据
• 若product_today.id是主键，原SQL末尾的group by t.id属于多余操作，额外增加了不必要的分组开销

优化后的SQL语句 #

改写为关联+单次聚合逻辑，和原查询返回结果完全一致：

SELECT t.id, COUNT(o.hid) AS zid
FROM product_today t
LEFT JOIN product_today_order o ON o.hid = t.id
-- 替换date函数为范围查询，可触发索引
WHERE t.dtime >= '2021-11-26 00:00:00' 
  AND t.dtime < '2021-11-27 00:00:00'
GROUP BY t.id

索引优化建议 #

两个表新增对应索引后，查询可直接走覆盖索引，无需回表扫全量数据：

• 给product_today表创建联合索引：ALTER TABLE product_today ADD INDEX idx_dtime_id (dtime, id)
• 给product_today_order表创建关联字段索引：ALTER TABLE product_today_order ADD INDEX idx_hid (hid)

优化完成后，2万行数据的查询耗时可降至100ms以内。

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