如何优化MySQL双表分组统计指定日期内门店数量的查询？

优化思路 #

原查询通过IN子查询加DISTINCT的方式统计数据，在120万条订单的大数据量场景下易产生性能瓶颈。我们可以通过调整关联逻辑、添加针对性索引来将查询耗时压缩到1秒以内。

优化后的查询语句 #

方案1：用INNER JOIN替代IN子查询 #

这种方式让数据库更高效地执行关联操作，避免子查询的额外开销：

SELECT o2.nation_id, COUNT(DISTINCT o2.id) AS outlet_count
FROM outlets o2
INNER JOIN orders o 
  ON o2.id = o.outlet_id
WHERE o.order_date >= '2022-05-01 00:00:00' 
  AND o.order_date < '2022-07-01 00:00:00' -- 兼容毫秒级时间，避免遗漏订单
GROUP BY o2.nation_id;

方案2：先去重再关联（适合订单重复度极高的场景） #

先筛选出日期范围内的唯一门店ID，再关联门店表统计，大幅减少关联的数据量：

SELECT o2.nation_id, COUNT(o2.id) AS outlet_count
FROM outlets o2
INNER JOIN (
    SELECT DISTINCT outlet_id 
    FROM orders 
    WHERE order_date >= '2022-05-01 00:00:00' 
      AND order_date < '2022-07-01 00:00:00'
) o 
  ON o2.id = o.outlet_id
GROUP BY o2.nation_id;

关键索引优化 #

创建合适的索引是提速核心，针对两张表分别添加：

1. 订单表(orders)：创建日期+门店ID的复合索引，让数据库快速过滤日期并获取门店ID，无需全表扫描：

CREATE INDEX idx_orders_orderdate_outletid ON orders(order_date, outlet_id);

2. 门店表(outlets)：创建ID+国家ID的复合索引，关联时直接从索引获取所需数据，避免访问表主数据：

CREATE INDEX idx_outlets_id_nationid ON outlets(id, nation_id);

额外优化点 #

• 用order_date < '2022-07-01'替代order_date <= '2022-06-30 23:59:59'，如果order_date包含毫秒值，不会漏掉最后一秒的订单，同时索引匹配更高效。

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