MySQL查询优化：如何筛选单日3次拒付的payment_ref

咱们先拆解下你当前查询的几个核心问题：

1. 子查询未限定日期范围，统计结果失真 #

你的子查询(SELECT COUNT(id) FROM client_payments WHERE status LIKE 'Declined' AND payment_ref = ref)没有添加payment_date的过滤条件，它统计的是该payment_ref所有历史记录里的拒付次数，而非指定日期内的次数，这就是你看到拒付次数远超3次的原因。

2. 嵌套子查询引发性能灾难 #

主查询先拉取指定日期内的所有交易记录（58000条），然后每条记录都要执行一次子查询——相当于58000次重复扫描表，哪怕有索引也扛不住这么高频的重复查询，这必然导致执行速度极慢。

3. 未过滤当日有成功支付的用户 #

你的需求明确要求“只要当日有一次支付成功则无需关注”，但原查询完全没处理这个逻辑，会把那些既有拒付又有成功支付的payment_ref也纳入结果，不符合需求。

优化后的查询语句 #

我们可以用分组聚合+条件过滤的方式，只扫描一次指定日期的记录，就能精准得到符合要求的结果：

SELECT 
    payment_ref AS ref,
    SUM(CASE WHEN status = 'Declined' THEN 1 ELSE 0 END) AS declined_count
FROM client_payments
WHERE payment_date BETWEEN '2018-05-14 00:00:00' AND '2018-05-14 23:59:59'
GROUP BY payment_ref
HAVING 
    SUM(CASE WHEN status = 'Declined' THEN 1 ELSE 0 END) >= 3
    AND MAX(CASE WHEN status = 'Authorised' THEN 1 ELSE 0 END) = 0;

逻辑说明： #

• 先通过WHERE筛选出指定日期内的所有交易，避免扫描全表。
• 按payment_ref分组，统计每个客户当天的拒付次数，同时判断是否存在成功支付的记录。
• 最后用HAVING过滤出：拒付次数≥3，且当天没有任何成功支付的客户。

索引优化建议 #

为了让这个查询更快，建议创建复合覆盖索引：

CREATE INDEX idx_payment_date_ref_status ON client_payments (payment_date, payment_ref, status);

这个索引可以让数据库直接从索引里获取需要的字段（日期、客户ID、状态），不需要回表查询原数据，进一步提升查询效率。

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