1. 问题背景与现象解析
在MySQL数据库优化实践中，我们经常遇到一个经典问题：当WHERE子句包含OR条件时，查询性能会出现断崖式下降。特别是当OR两侧的字段并非都有索引时，执行计划往往会显示全表扫描（ALL）。这种现象让不少开发者感到困惑——为什么一个简单的OR操作就会导致索引失效？
我曾在电商平台的订单查询系统中遇到过真实案例：一个包含WHERE status='paid' OR create_time>'2023-01-01'的查询，虽然status字段有索引，但由于create_time未建索引，导致百万级数据表全表扫描，平均查询耗时从20ms飙升到800ms。通过EXPLAIN分析发现，type列显示为ALL，key列显示NULL，这就是典型的索引失效场景。
2. MySQL索引工作原理深度剖析
2.1 B+树索引的检索机制
MySQL的InnoDB引擎采用B+树作为索引数据结构。当执行WHERE col1=value这类等值查询时，引擎会沿着B+树的根节点向下查找，经过内部节点的比较定位到叶子节点，整个过程时间复杂度是O(log n)。这种查找方式就像在图书馆按照索书号找书——通过目录快速定位书架位置。
但对于OR条件，情况变得复杂。假设有查询WHERE col1=1 OR col2=2：
- 如果col1有索引而col2没有，引擎无法单独使用col1索引
- 因为满足col2=2的记录可能分布在任何数据页中
- 索引扫描后仍需回表检查col2条件，相当于要做两次全表扫描
2.2 索引合并(Index Merge)的局限性
MySQL5.0之后引入了Index Merge优化，理论上可以同时使用多个索引。但有以下严格限制：
- 只适用于特定场景的OR条件（要求所有OR分支都有独立索引）
- 合并操作本身需要额外计算开销
- 优化器认为全表扫描更快时会放弃合并
通过EXPLAIN可以看到Using union(index1,index2)的提示。但实践中发现，当表数据量超过10万行时，优化器往往直接选择全表扫描而非索引合并。
3. OR条件索引失效的根本原因
3.1 布尔代数与执行计划生成
WHERE条件本质上是一个布尔表达式。对于AND条件A AND B，可以先通过索引快速过滤A，再在结果集中检查B。但OR条件A OR B要求返回满足任一条件的记录，这种逻辑决定了：
如果只有A有索引，引擎需要：
- 用索引找出所有A的记录
- 再全表扫描找出所有B的记录（因为无法用索引）
- 最后合并去重
这个过程的代价可能比直接全表扫描更高
3.2 成本估算模型的决策逻辑
MySQL优化器基于成本估算选择执行计划，主要考虑：
- IO成本：读取数据页的代价
- CPU成本：处理数据的代价
当出现WHERE indexed_col OR non_indexed_col时：
- 使用索引方案：IO成本=索引读取+全表读取
- 全表扫描方案：IO成本=全表读取 优化器通常会选择后者
4. 解决方案与最佳实践
4.1 索引设计策略
对称索引原则：对频繁出现在OR条件中的字段成对建立索引
ALTER TABLE orders ADD INDEX idx_status (status); ALTER TABLE orders ADD INDEX idx_create_time (create_time);联合索引慎用：OR条件中的字段不适合建联合索引，因为联合索引对
A OR B无效覆盖索引优化：确保SELECT的字段都在索引中，避免回表
-- 好的设计 CREATE INDEX idx_covering ON orders(status, create_time, order_id);
4.2 SQL重写技巧
UNION ALL拆分法（最有效方案）：
SELECT * FROM orders WHERE status = 'paid' UNION ALL SELECT * FROM orders WHERE create_time > '2023-01-01' AND (status <> 'paid' OR status IS NULL)注意：需要添加第二个条件的反逻辑避免重复
CASE表达式转换：
SELECT * FROM orders WHERE CASE WHEN status = 'paid' THEN 1 WHEN create_time > '2023-01-01' THEN 1 ELSE 0 END = 1
4.3 参数调优建议
调整optimizer_switch开启index_merge：
SET optimizer_switch='index_merge=on,index_merge_union=on';强制使用索引合并（需测试验证）：
SELECT * FROM orders FORCE INDEX(idx_status,idx_create_time) WHERE status = 'paid' OR create_time > '2023-01-01'
5. 实战案例与性能对比
5.1 测试环境
- 表结构：orders表含120万数据
- 索引：仅status字段有索引
- 查询：
WHERE status='completed' OR amount>1000
5.2 执行计划对比
| 方案 | 执行时间 | 扫描行数 | 使用索引 |
|---|---|---|---|
| 原始OR查询 | 720ms | 120万 | 无 |
| UNION拆分 | 45ms | 18万 | idx_status |
| 强制索引合并 | 680ms | 120万 | idx_status |
5.3 关键发现
- UNION方案性能提升16倍
- 索引合并在本案例反而更慢
- 全表扫描时buffer_pool命中率显著下降
6. 特殊场景与边界情况
6.1 多列OR混合条件
对于WHERE (A AND B) OR (C AND D)这类复杂条件：
- 确保A、B、C、D都有独立索引
- 或者重写为：
SELECT * FROM table WHERE A AND B UNION ALL SELECT * FROM table WHERE C AND D AND NOT (A AND B) -- 排重
6.2 NULL值处理
OR条件遇到NULL时需特别注意：
-- 这种写法可能导致索引失效 WHERE col1 = 1 OR col2 IS NULL -- 建议改写为 WHERE col1 = 1 UNION ALL SELECT * FROM table WHERE col2 IS NULL AND col1 <> 17. 监控与诊断方法
7.1 执行计划分析
关键观察点：
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE status='paid' OR create_time>'2023-01-01';检查：
access_type字段是否为ALLusing_union是否出现rows_estimation的数值
7.2 性能模式监控
-- 开启性能监控 UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE '%events_statements%'; -- 查看慢查询 SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 5;8. 延伸思考与替代方案
8.1 应用层分治策略
对于极端复杂的OR条件，可以考虑：
- 在应用层拆解查询条件
- 分多次查询后合并结果
- 使用内存缓存中间结果
8.2 物化视图方案
对于频繁执行的OR查询：
CREATE TABLE order_active AS SELECT * FROM orders WHERE status='paid' OR create_time>'2023-01-01'; -- 定期刷新 REPLACE INTO order_active SELECT * FROM orders WHERE [...]8.3 全文索引替代方案
当OR条件涉及文本搜索时：
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx (title,content); SELECT * FROM articles WHERE MATCH(title,content) AGAINST('mysql OR index')关键提示：所有优化方案都需要通过EXPLAIN验证实际执行计划，理论最优解在实践中可能因数据分布而失效
