带成功率计算的SQL查询性能优化咨询

嘿，我来帮你搞定这个慢查询的问题！先拆解原查询的核心痛点，再一步步给出优化方案：

原查询的核心问题 #

1. 日期过滤导致索引失效：DATE(t.sales_time) = CURDATE()这种写法会让MySQL无法利用sales_time字段的索引，只能全表扫描115万行数据，这是查询慢的主要原因。
2. 冗余的条件判断：内查询里的SUM(CASE WHEN offer_id = t.offer_id AND ...)完全多余——既然已经按offer_id分组，每一组的offer_id必然相同，这个判断纯粹浪费计算资源。
3. 单索引效率不足：现有的offer_id和sales_status单字段索引，无法满足「日期过滤+分组统计」的复合场景需求，联合索引的效率会高得多。

第一步：优化SQL逻辑 #

先简化查询逻辑，同时修正日期过滤方式，用范围查询替代函数包装，让索引能正常生效：

SELECT j.offer_id, o.offer_name, j.success_rate
FROM (
    SELECT 
        t.offer_id,
        -- 用COUNT(IF)替代SUM(CASE)，语法更简洁，性能一致
        COUNT(IF(t.sales_status = 'SUCCESS', 1, NULL)) / COUNT(*) AS success_rate
    FROM tblSales AS t
    -- 日期范围查询，避免函数导致索引失效
    WHERE t.sales_time >= CURDATE() 
      AND t.sales_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
    GROUP BY t.offer_id
    ORDER BY success_rate DESC
    LIMIT 5
) AS j
LEFT JOIN tblOffers AS o ON j.offer_id = o.offer_id;

第二步：添加覆盖联合索引 #

给tblSales创建一个覆盖联合索引，包含查询所需的所有字段，让MySQL无需回表就能完成统计计算：

CREATE INDEX idx_sales_time_offer_status ON tblSales(sales_time, offer_id, sales_status);

索引顺序的逻辑：

• 优先放sales_time：我们用它做范围过滤，放在最前面能快速定位当日的销售数据。
• 接着是offer_id：用于分组统计，MySQL可以直接按索引内的offer_id分组，无需额外排序。
• 最后是sales_status：用于统计成功订单数，索引内直接能获取该值，不需要回表查询原数据。

第三步：验证优化效果 #

执行EXPLAIN查看执行计划，若出现以下特征，说明优化已生效：

• type列显示为range（范围扫描，而非全表扫描）
• key列显示我们创建的idx_sales_time_offer_status
• Extra列包含Using index（表示使用覆盖索引，无需回表）

额外小提示 #

如果业务允许，还可以：

• 对success_rate保留固定小数位（比如ROUND(COUNT(...) / COUNT(*), 2)），让结果更整洁。
• 若tblOffers的offer_id是主键，LEFT JOIN的效率本身就很高，无需额外优化。

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