带日期范围条件的查询处理记录数远多于无该条件的查询，求分析

一、为什么添加日期范围后处理的记录数反而更多？ #

这种反直觉的现象通常是MySQL优化器选择了不同的执行计划导致的，具体可能有以下几个原因：

1. 索引选择的差异
   移除日期条件时，优化器可能优先使用report_custom_records中包含group_id或creative_id的复合索引（比如group_id_2），这些索引可以直接关联report_ad_groups和campaign_creatives表，同时配合cmp.business_id、adg.campaign_id等条件快速过滤出小数据集。
   添加日期范围后，优化器可能选择了单独的date索引——这个索引只能过滤日期，但需要**回表获取group_id和creative_id**来完成JOIN操作。回表过程中会扫描大量符合日期条件但不符合其他过滤规则的记录，最终导致统计的处理记录数飙升。

2. OR条件的性能拖累
   你的WHERE子句中包含((adg.target_type LIKE "%Pre-Roll%" AND adg.subproduct_type_id IN (2, 4)) OR adg.subproduct_type_id IN (12, 16 , 10 ))这种OR逻辑，MySQL优化器对OR条件的索引支持很差，通常会放弃使用report_ad_groups的索引，转而全表扫描该表。添加日期范围后，JOIN的数据集变大，全表扫描带来的性能损耗被放大，进而导致整体处理的记录数增多。

3. 表连接顺序的变化
   优化器可能根据条件的选择性调整表连接顺序：不加日期时，先过滤ad_campaigns和report_ad_groups得到小数据集，再关联report_custom_records；但加日期后，优化器可能先扫描report_custom_records的日期索引，再关联其他表，这时其他表的过滤条件无法提前生效，导致需要处理更多中间记录。

二、现有索引是否会拖慢查询？ #

是的，report_custom_records的现有索引存在冗余和设计不合理的问题，会直接影响查询性能：

• 冗余索引：group_id_3（单独的group_id索引）完全多余——group_id_2和adg_id都以group_id作为前缀，复合索引的前缀列已经能满足单独group_id的查询需求，保留这个索引只会增加写入时的维护成本。
• 低效的单独索引：单独的date索引在你的查询中几乎是负面作用——它只能过滤日期，但无法覆盖JOIN和聚合需要的字段，必须回表，导致大量随机IO。
• 复合索引顺序不够优：adg_id（group_id, date）和group_id_2（group_id, creative_id, date）的前缀都是group_id，如果group_id的选择性不高（比如大量记录共享同一个group_id），这两个索引的过滤效率会很低。而你的查询中date是范围条件，放在复合索引的中间或末尾会导致后面的字段无法被索引利用。

三、优化建议 #

针对你的场景，我给出以下具体优化步骤：

1. 清理冗余索引
   立即删除report_custom_records中的冗余索引：

   DROP INDEX group_id_3 ON report_custom_records;
   DROP INDEX date ON report_custom_records;
   

2. 创建覆盖型复合索引
   为report_custom_records创建一个覆盖所有查询需求的复合索引，让优化器无需回表：

   CREATE INDEX idx_date_group_creative ON report_custom_records (date, group_id, creative_id, impressions, clicks);
   

   这个索引的顺序是：先按date过滤范围条件，再匹配group_id和creative_id用于JOIN，最后包含impressions和clicks用于聚合——完全覆盖查询的所有字段，能极大减少处理的记录数和IO开销。

3. 优化report_ad_groups的索引
   针对report_ad_groups的WHERE条件，创建复合索引来加速过滤：

   CREATE INDEX idx_campaign_subproduct_target ON report_ad_groups (campaign_id, subproduct_type_id, target_type);
   

   这个索引可以快速匹配adg.campaign_id IN (...)和subproduct_type_id的条件，同时帮我们先过滤掉大部分不符合target_type LIKE "%Pre-Roll%"的记录。

4. 简化不必要的CAST操作
   你的rcr.date本身就是date类型，无需用cast(rcr.date as date)，直接写成：

   rcr.date BETWEEN '2018-03-01' AND '2018-10-31'
   

   虽然这个CAST不会导致索引失效，但保持代码简洁能避免潜在的优化器误解。

5. 拆分OR条件为UNION ALL
   OR条件是性能杀手，建议将其拆分为两个独立的查询用UNION ALL合并，让优化器为每个子查询选择最优索引：

   SELECT SUM(rcr.impressions), SUM(rcr.clicks) AS clicks, cv.id AS cup_version_id, cv.source_id as source_id, rcr.date AS date, adg.subproduct_type_id 
   FROM report_custom_records AS rcr 
   JOIN report_ad_groups AS adg ON (rcr.group_id = adg.ID) 
   JOIN ad_campaigns AS cmp ON (adg.campaign_id = cmp.id) 
   JOIN campaign_creatives AS cre ON (rcr.creative_id = cre.id) 
   JOIN campaign_versions AS cv ON (cre.version_id = cv.id) 
   WHERE cmp.business_id IN (-1,9126,102538) 
     AND adg.campaign_id IN (-1,870689,870696,884963,884964,902027,907809,914889,914893,925233,930390,930391,955423,955429,1004323,1004324,1021355,1021356,1078026,1078027) 
     AND rcr.date BETWEEN '2018-03-01' AND '2018-10-31' 
     AND adg.target_type LIKE "%Pre-Roll%" 
     AND adg.subproduct_type_id IN (2, 4) 
     AND cv.source_id IS NULL 
   GROUP BY cup_version_id, date
   
   UNION ALL
   
   SELECT SUM(rcr.impressions), SUM(rcr.clicks) AS clicks, cv.id AS cup_version_id, cv.source_id as source_id, rcr.date AS date, adg.subproduct_type_id 
   FROM report_custom_records AS rcr 
   JOIN report_ad_groups AS adg ON (rcr.group_id = adg.ID) 
   JOIN ad_campaigns AS cmp ON (adg.campaign_id = cmp.id) 
   JOIN campaign_creatives AS cre ON (rcr.creative_id = cre.id) 
   JOIN campaign_versions AS cv ON (cre.version_id = cv.id) 
   WHERE cmp.business_id IN (-1,9126,102538) 
     AND adg.campaign_id IN (-1,870689,870696,884963,884964,902027,907809,914889,914893,925233,930390,930391,955423,955429,1004323,1004324,1021355,1021356,1078026,1078027) 
     AND rcr.date BETWEEN '2018-03-01' AND '2018-10-31' 
     AND adg.subproduct_type_id IN (12, 16 , 10 ) 
     AND cv.source_id IS NULL 
   GROUP BY cup_version_id, date
   

6. 分析执行计划细节
   优化后再次执行EXPLAIN，重点关注这几个字段：

   • type：尽量达到range或ref级别，避免ALL（全表扫描）
   • key：确认使用了我们创建的新索引
   • rows：对比优化前后的预估扫描行数，应该有明显下降
   • Extra：避免出现Using filesort或Using temporary（如果GROUP BY导致这些，可以考虑调整索引或GROUP BY字段顺序）

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