CodeIgniter中关联查询客户销售欠款总和替代逐行循环的可扩展SQL方案

你之前采用的循环逐行查询属于典型的N+1查询问题，客户量级增长后会产生大量数据库请求，性能自然会下降。使用单条关联聚合SQL即可解决该问题，可支撑百万级以上的数据量，扩展性更强。

可用SQL语句 #

SELECT 
    c.name,
    SUM(s.price) AS total_debt
FROM 
    customers c
INNER JOIN 
    sales s ON c.id = s.customerid
WHERE 
    s.state = 1
GROUP BY 
    c.id, c.name

逻辑说明 #

• 首先通过WHERE s.state = 1筛选出销售表中所有存在欠款的交易记录，减少后续关联计算的数据量
• 通过内连接关联客户表匹配客户姓名，无欠款交易的客户自然不会出现在结果中
• 按客户维度分组聚合，对欠款交易的金额求和得到单个客户的总欠款额

性能优化建议 #

• 给sales表的(state, customerid, price)字段建立联合索引，命中索引后不需要回表查询，可提升数倍查询效率
• 若客户量级超过百万、销售表数据量过亿，可新增定时任务预计算客户欠款总额，存入customers表的冗余字段，查询时直接读取该字段即可，响应速度可提升至毫秒级

用你提供的示例数据执行上述SQL，得到的结果完全符合预期：

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