MySQL查询耗时久无法取数，执行指定SQL未获预期结果求助

我太懂这种明明执行计划看着没问题，但查询就是跑不动的憋屈了！先把你的原SQL贴出来方便对照：

SELECT 
    SUM(case when cp.payment_method_id_fk = 3 then dt.amount_attempted else 0 end) AS Subsequent_successDeduction_TIGO,
    SUM(case when cp.payment_method_id_fk = 2 then dt.amount_attempted else 0 end) AS Subsequent_successDeduction_MTN,
    date(dt.transaction_date) AS DateOfTransactions 
FROM deduction_transactions dt 
INNER JOIN customer_deductions cd ON dt.deduction_id_fk = cd.id 
INNER JOIN customer_policy cp ON cd.cust_policy_id = cp.customer_policy_id 
INNER JOIN policy_info pi ON cp.policy_id_fk = pi.id 
INNER JOIN product_info pr on pi.product_id_fk=pr.id 
WHERE 
    date(dt.transaction_date)=date_sub(curdate(),interval 1 day) 
    and dt.status=1 
    and date(cp.created_date) <> date(curdate()) 
    and cp.payment_confirmed_date <> dt.transaction_date 
GROUP BY DateOfTransactions;

接下来给你几个针对性的优化方向，亲测能解决大部分这类慢查询问题：

1. 干掉导致索引失效的日期函数 #

你的WHERE条件里用date(dt.transaction_date)和date(cp.created_date)包裹字段，这会让MySQL无法使用这些字段上的常规索引。把这些条件改成范围查询：

• 原条件date(dt.transaction_date)=date_sub(curdate(),interval 1 day)替换成：
  dt.transaction_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND dt.transaction_date < CURDATE()
• 原条件date(cp.created_date) <> date(curdate())，如果created_date不会有未来日期的话，直接写成：
  cp.created_date < CURDATE()
  （如果存在未来数据，就写成cp.created_date < CURDATE() OR cp.created_date >= DATE_ADD(CURDATE(), INTERVAL 1 DAY)）

这样改完，MySQL就能用上transaction_date和created_date上的索引了。

2. 添加覆盖索引减少回表查询 #

针对你的查询，给核心表创建覆盖索引，让MySQL不用回表就能拿到需要的所有数据：

• 给deduction_transactions创建：

  CREATE INDEX idx_dt_status_transaction_deduction_amount ON deduction_transactions (status, transaction_date, deduction_id_fk, amount_attempted);
  

  这个索引包含了过滤条件、关联字段和需要聚合的字段，完美覆盖查询需求。
• 给customer_policy创建：

  CREATE INDEX idx_cp_custpolicy_paymentmethod_created_confirmed ON customer_policy (customer_policy_id, payment_method_id_fk, created_date, payment_confirmed_date);
  

  包含关联字段、聚合用到的字段，以及过滤用的日期字段。
• 确保customer_deductions的主键和关联外键都有索引（主键默认有索引，外键建议手动补充）。

3. 去掉多余的GROUP BY #

你的WHERE条件已经把dt.transaction_date限定为前一天，所以GROUP BY DateOfTransactions其实只会得到一行结果——分组完全是多余的！删掉这个GROUP BY，能减少不必要的分组计算开销。

4. 移除无意义的关联表 #

你关联了policy_info和product_info，但整个查询里并没有用到这两个表的任何字段——这两个表是多余的！直接删掉这两个JOIN，能大幅减少数据关联的开销。

优化后的最终SQL #

把上面的优化点整合后，SQL会变成这样：

SELECT 
    SUM(CASE WHEN cp.payment_method_id_fk = 3 THEN dt.amount_attempted ELSE 0 END) AS Subsequent_successDeduction_TIGO,
    SUM(CASE WHEN cp.payment_method_id_fk = 2 THEN dt.amount_attempted ELSE 0 END) AS Subsequent_successDeduction_MTN,
    DATE(dt.transaction_date) AS DateOfTransactions 
FROM deduction_transactions dt 
INNER JOIN customer_deductions cd ON dt.deduction_id_fk = cd.id 
INNER JOIN customer_policy cp ON cd.cust_policy_id = cp.customer_policy_id 
WHERE 
    dt.transaction_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
    AND dt.transaction_date < CURDATE()
    AND dt.status = 1 
    AND cp.created_date < CURDATE()
    AND cp.payment_confirmed_date <> dt.transaction_date;

最后别忘了跑一下ANALYZE TABLE deduction_transactions, customer_deductions, customer_policy;更新表统计信息，让MySQL优化器能生成更准确的执行计划。

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