MySQL带索引WHERE条件查询超慢，移至HAVING后性能骤升问题咨询

1. WHERE子句添加过滤条件后查询极慢的原因 #

核心是MySQL优化器选错驱动表，加上关联逻辑和索引特性共同导致性能暴跌：

• 你的关联条件存在pp.id = ss.id_fisico OR pp.id = ss.id_virtuale的OR逻辑，这种写法本身无法高效利用pp表的主键索引，关联时很容易出现行数放大。
• 当你把pp.confermato = '1'、pp.annullato = '0'、pp.id_esito != 4放在WHERE子句时，优化器会误判这三个条件的过滤效率，优先选择pp作为驱动表。但这三个字段都是低区分度字段：confermato和annullato都是只有0/1两个值的TINYINT类型，id_esito !=4的过滤范围也非常小，你创建的三个单列独立索引完全发挥不了作用，反而会出现「索引过滤后仍要扫描大量行+回表查询+和其他表关联时行数爆炸级放大」的情况，最终导致查询耗时飙升到5分钟。
• 你尝试强制走联合索引没用，本质是关联的OR逻辑没有优化，就算过滤了pp的行，后续关联其他表时的开销依然非常大。而去掉这三个条件或者放到HAVING时，优化器会选择行数更小的scadenze表作为驱动表，先通过日期和状态过滤出少量行再逐级关联，自然速度快。

2. 两种写法返回记录数不一致的原因 #

本质是GROUP BY时非分组字段的随机取值特性导致的：

• WHERE写法的逻辑是：先过滤所有符合条件的行，再进行分组。只要一个分组里存在至少一行符合pp三个过滤条件的记录，这个分组就会保留。
• HAVING写法的逻辑是：先完成所有关联和分组，再对分组后的结果过滤。你没有对pp表的三个过滤字段加任何聚合函数，在MySQL关闭ONLY_FULL_GROUP_BY的默认配置下，分组后非分组字段会取分组内随机一行的值。刚好存在一个分组，里面有符合条件的pp行（所以WHERE写法会保留），但分组后随机取的那行pp不符合三个过滤条件，就被HAVING筛掉了，最终就少了1条记录。

优化建议 #

• 优化关联的OR逻辑：把pp.id = ss.id_fisico OR pp.id = ss.id_virtuale拆为两个查询的UNION ALL，分别关联ss.id_fisico = pp.id和ss.id_virtuale = pp.id，避免OR导致的索引失效。
• 给pp表创建覆盖联合索引：INDEX idx_filter_pp (confermato, annullato, id_esito, id)，可以直接在索引层完成过滤不需要回表，大幅提升过滤效率。
• 避免在字段上用函数：把DATE(s.data_scadenza) BETWEEN '2021-06-01' AND '2021-06-16'改写为s.data_scadenza BETWEEN '2021-06-01 00:00:00' AND '2021-06-16 23:59:59'，让s.data_scadenza的索引可以生效。
• 开启ONLY_FULL_GROUP_BYSQL模式，避免非分组字段随机取值的逻辑漏洞，需要取pp字段的话要明确使用MAX()、MIN()等聚合函数，或者将pp相关字段加入GROUP BY子句。

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