MySQL中OR条件查询引发索引失效的场景及解决方案_Mysql

今天这篇文章我们来简单介绍一下mysql的or条件查询，以及它可能会引发的索引失效（即不走索引）的场景以及解决方案。方便大家在实践中，留意是否正确使用了or条件查询。

mysql的or条件查询简介

在 mysql 中，or 条件查询是一种利用 or 逻辑操作符在 where 子句中连接多个条件的查询方式。当查询中使用 or 操作符时，只要其中任何一个条件满足就会返回对应的数据行。它是一种常见的逻辑运算，用于实现灵活的数据筛选。

or条件查询经常用于：多字段查询、联合范围查询、动态筛选等场景。

多字段查询示例：

select * from users 
where name = 'alice' or city = 'chicago';

联合范围查询示例：

select * from users
where age < 30 or age > 35;

动态筛选示例：

select * from orders
where status = 'pending' or status = 'processing';

其中，在多字段查询的场景下，需要特别留意是否会出现不走索引的情况，下面我们来详细介绍一下这种情况及解决方案。

不走索引案例分析

如果查询条件中包含 or 操作符，通常情况下，即使其中一个条件是基于索引的，mysql 也可能不会使用该索引，转而执行全表扫描（table scan）。

原因：mysql 查询优化器无法高效地利用索引来处理 or 条件，尤其是在多个条件中一个或多个字段没有索引的情况下。

推荐操作：尽量避免使用 or，可以通过 union all 或 union 来重构查询逻辑，从而强制使用索引。

案例分析

假设有一个表 example_table，结构如下：

create table example_table (
    id int not null,                  -- 有索引字段
    name varchar(100),                -- 没有索引字段
    age int,                          -- 有索引字段
    primary key (id),                 -- 主键索引
    index index_age (age)             -- 辅助索引
);

插入一些测试数据：

insert into example_table (id, name, age) values 
(1, 'alice', 30),
(2, 'bob', 25),
(3, 'charlie', 35),
(4, 'dave', 40);

使用or条件的查询：

mysql> explain select * from example_table  where id = 1 or name = 'alice' \g
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: example_table
   partitions: null
         type: all
possible_keys: primary
          key: null
      key_len: null
          ref: null
         rows: 4
     filtered: 43.75
        extra: using where

分析：

id = 1 是主键查询，本应该使用索引 primary。
但是，由于包含 or 条件 name = 'alice'，而 name 列没有索引，mysql 的优化器选择了不使用索引而执行全表扫描。
最终结果：查询执行了全表扫描 (type: all)。

解决方案

方法 1：使用union替代or

将原查询拆分，并通过 union 分别处理有索引和无索引的条件，从而强制使用索引：

mysql> explain select * from example_table where id = 1
    -> union all
    -> select * from example_table where name = 'alice' \g
*************************** 1. row ***************************
           id: 1
  select_type: primary
        table: example_table
   partitions: null
         type: const
possible_keys: primary
          key: primary
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        extra: null
*************************** 2. row ***************************
           id: 2
  select_type: union
        table: example_table
   partitions: null
         type: all
possible_keys: null
          key: null
      key_len: null
          ref: null
         rows: 4
     filtered: 25.00
        extra: using where

通过执行计划可以看出，对于第一部分查询使用了索引，对于第二部分查询使用全表扫描，因为name字段无索引。虽然仍有一部分未使用索引，但数据量较大的情况下，这种分拆方式对性能更优。

方法 2：添加索引优化

如果查询中经常根据 name 条件筛选数据，可以考虑为 name 列添加索引：

mysql> alter table example_table add index index_name (name);

mysql> explain select * from example_table where id = 1 or name = 'alice' \g
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: example_table
   partitions: null
         type: index_merge
possible_keys: primary,index_name
          key: primary,index_name
      key_len: 4,403
          ref: null
         rows: 2
     filtered: 100.00
        extra: using union(primary,index_name); using where

针对name列添加索引之后，mysql 使用了 index_merge 查询优化策略，结合主键索引和辅助索引 index_name。数据量较大的时候，这种方式可以显著加速查询，避免全表扫描。