MySQL三表JOIN查询添加状态过滤后速度骤降如何优化？

核心问题原因 #

你添加的不等值判断、OR逻辑以及筛选条件位置不合理、索引顺序错误是性能下降的核心原因：

• 不等于（!=）、OR逻辑天生对索引不友好，放在JOIN的ON子句会打乱优化器的关联顺序判断
• 对publish_date字段额外加DATE()函数，导致该字段的索引无法生效
• 之前创建的联合索引顺序和查询筛选顺序不匹配，无法被优化器命中

具体优化步骤 #

1. 改写SQL逻辑，调整条件位置 #

将单表筛选条件挪到WHERE子句，仅在ON子句保留表关联条件，同时用安全等于替换OR逻辑提升效率，去掉publish_date的冗余函数：

SELECT 
    DATE_FORMAT(iu.publish_date, '%m-%d') AS CurrDate,
    ROUND(ifnull(sum(ifnull(iu.ad_price, 0)),0),2) as Printed
FROM insert_usage iu
STRAIGHT_JOIN newspaper_product_bid as npb on iu.bid_id = npb.id 
JOIN advertiser_campaign ac on ac.Id = npb.AdvertiserCampaignId
WHERE 
    iu.published=1 
    AND iu.publish_date BETWEEN  DATE_FORMAT(QtrStartDt, '%Y-%m-01') AND CurrentDt
    AND NOT (iu.ad_status <=> 7) -- 等价于ad_status !=7 或 ad_status is null，性能优于OR
    AND npb.`Status` != -10
GROUP BY DATE_FORMAT(iu.publish_date, '%m-%d')

2. 新建匹配查询逻辑的覆盖索引 #

无需回表查询即可拿到所有需要的字段，大幅提升IO效率：

• 给insert_usage新建联合索引：

CREATE INDEX idx_iu_pub_date_stat_bid_price ON insert_usage (published, publish_date, ad_status, bid_id, ad_price);

• 给newspaper_product_bid新建联合索引：

CREATE INDEX idx_npb_id_status_cid ON newspaper_product_bid (Id, `Status`, AdvertiserCampaignId);

3. 可选优化项 #

如果数据量持续增长，可以将该统计逻辑改成定时任务预计算，直接查询预计算的结果表，避免每次查询都做三表关联和聚合计算。

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