SQL优化：WHERE子句中同一列用OR两次导致性能极差

我太懂这种无奈了——自动生成的SQL没法说改就改成UNION，但加了个OR之后查询直接从秒级变分钟级，简直抓狂。针对你那500万条记录的contact表，除了UNION替换，还有这些实用的优化方向可以试试：

• 精准优化索引，让OR分支都能命中索引
  先拿EXPLAIN跑一下你的慢查询，看看优化器是不是直接走了全表扫描。如果是，大概率是OR的某一个分支没用到合适的索引。比如你的WHERE子句是[原条件] OR row_id = 0，那你可以创建包含row_id和其他查询过滤字段的复合索引，比如：

  CREATE INDEX idx_contact_rowid_filter ON contact(row_id, delete, name, email);
  

  这里要注意把过滤优先级高的字段放在索引前面，同时确保OR的每个分支（比如原条件里的字段、row_id）都在索引覆盖范围内。另外，如果delete是状态字段（比如标记是否删除），一定要把它加到索引里，避免扫描已删除的无效数据。

• 给自动生成的SQL做“微创”调整
  虽然是自动生成的，但很多框架（比如MyBatis、JPA、Django ORM）都支持动态条件判断。你可以试试让生成工具做个小逻辑：

  • 当用户没有选择任何row_id时，直接生成WHERE row_id = 0的SQL；
  • 当用户选择了row_id时，生成WHERE row_id IN (...)的SQL；
    完全不用拼接OR，从根源上避免性能问题。如果实在没法改生成逻辑，那把你用的UNION换成UNION ALL（只要结果集不会有重复数据），因为UNION会额外做去重排序，性能比UNION ALL差不少。

• 调大数据库内存缓存，减少磁盘IO
  500万条记录的表，大部分数据如果能缓存到内存里，查询速度会飞起来。比如MySQL的innodb_buffer_pool_size，如果服务器内存足够，直接设为物理内存的50%-70%，让InnoDB把更多的表数据和索引缓存起来，减少磁盘读写的开销。另外，要是你的查询重复率高，低版本MySQL可以开启query_cache（MySQL 8.0已移除该功能），但要注意如果表更新频繁，缓存失效反而会拖慢性能。

• 强制索引或调整优化器策略
  如果优化器因为row_id=0的记录太多，觉得走索引不如全表扫描，那可以试试强制指定索引，比如：

  SELECT ids, name, email FROM contact FORCE INDEX(idx_contact_rowid) WHERE [原条件] OR row_id = 0;
  

  不过强制索引要谨慎，最好定期监控数据分布，要是后续row_id=0的记录占比变化了，可能反而适得其反。另外，也可以调整优化器的参数，比如MySQL的optimizer_switch里的index_merge_online，让优化器更好地合并多个索引的结果。

• 分区表拆分（长期优化方案）
  如果你的contact表数据还在增长，可以考虑按row_id或者delete字段做分区。比如把row_id=0的记录单独放在一个分区，其他正常记录放在另一个分区，这样查询的时候只会扫描对应的分区，直接减少一半左右的数据扫描量。不过分区表需要结合业务场景设计，比如有没有频繁的跨分区查询，维护成本会不会增加，这些都要提前考虑。

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