多对多关系场景下如何高效统计客户商品订购频次

现有明细结构下的实时查询实现 #

你当前设计的纯订单明细结构，本身就可以实现目标查询，核心逻辑就是COUNT聚合搭配左连接补0，只要配套正确的索引，小数据量下性能完全够用。
核心查询逻辑参考：

-- 查询Customer 1的全商品购买频次，包含未购买的0值
SELECT
  i.ItemID,
  i.ItemName,
  COALESCE(COUNT(ib.ItemID), 0) AS purchase_count
FROM Item i
LEFT JOIN ItemsBought ib
  ON ib.ItemID = i.ItemID
  AND ib.CustomerID = 1
GROUP BY i.ItemID, i.ItemName;

性能注意点：

• 必须给ItemsBought表建*(CustomerID, ItemID)的联合覆盖索引*，查询时可以直接从索引拿到所有需要的字段，不需要回表扫描全表明细。
• 当订单明细量突破千万级，或者这个统计查询的调用频率极高时，实时聚合的延迟会随着数据量上涨越来越明显，就需要考虑结构优化了。

提效方案：预聚合汇总表结构 #

针对固定维度的高频统计需求，完全可以通过调整表结构把实时聚合的成本前置到写入环节，专门建一张客户-商品维度的预聚合频次表：

• 表设计核心字段：CustomerID、ItemID、total_purchase_count（累计购买次数）
• 表主键直接设为联合主键(CustomerID, ItemID)，保证每个客户和商品的组合仅存一条记录，从结构上避免重复数据。

写入环节同步更新的逻辑：
每次往ItemsBought插入一条新的购买记录时，同步对汇总表做原子更新操作，主流数据库都支持原生的upsert语法，示例（MySQL版本）：

-- 新购买事件触发时，同步更新汇总表计数
INSERT INTO CustomerItemPurchaseSummary (CustomerID, ItemID, total_purchase_count)
VALUES (当前购买的客户ID, 当前购买的商品ID, 1)
ON DUPLICATE KEY UPDATE total_purchase_count = total_purchase_count + 1;

这种结构下的查询完全不需要扫大体积的订单明细表，直接从汇总表拿对应客户的所有计数，再和Item表左连接补0即可，查询速度不会随着订单总量的增长出现性能衰减。

方案选择的取舍判断 #

• 数据规模小、查询频率低：纯实时COUNT + GROUP BY的方案就足够，配合正确的索引基本能做到毫秒级响应，完全没必要额外维护汇总表——毕竟多一张表就要多考虑数据一致性、异常补偿的问题，小场景下属于过度设计。
• 数据规模大、查询频率高（比如用户个人中心每次进来都要展示该客户的购买偏好、累计购买量）：预聚合的方案收益远大于维护成本，哪怕需要额外做异步任务校验汇总表的计数偏差，整体查询性能也比实时扫百万级的明细表做聚合好上几个数量级。
• 不管选哪种方案，“未购买商品显示0”的逻辑都是统一的：通过全量商品表左连接计数结果，用COALESCE把空值替换成0即可，不需要额外的特殊逻辑。

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