MySQL 使用 OR 条件导致索引失效

文章讨论了在MySQL中,当查询语句包含OR条件且涉及大量数据时,响应时间会变长。通过创建索引和分析查询执行计划,发现OR条件可能导致索引失效。解决方法是使用UNION或UNIONALL分开查询,分别利用索引。UNION和UNIONALL的区别在于是否去除重复和是否排序,以及效率差异。最后,解释了EXPLAIN输出的关键列,帮助理解查询性能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

背景

需要根据工号或英文名带出中文名,但数据量过大,导致响应时间过长

查询工号为 x20230506 或者英文名是 codeporter 的用户信息

select * from user_test where login="x20230506" or login_id="codeporter"

查看MySQL版本:

select version();
version()
8.0.18

创建普通索引

alter table  user_test add index user_idx_login(login)
alter table  user_test add index user_idx_login_id(login_id)

使用EXPLAIN分析后面SELECT语句的执行情况,并且能够分析出所查询表的一些特征。 

explain select * from user_test where login="x20230506" or login_id="codeporter"  

查询条件中有or,即使两个条件都带索引也会失效  

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEuser_testNULLALLuser_idx_login,user_idx_login_idNULLNULLNULL1100Using where

解决办法:优化的方法是改成 UNION或者UNION ALL,分成多个 sql,走各自的索引。

UNION和UNION ALL的区别

1.对重复结果的处理:UNION会去掉重复记录,UNION ALL不会

2.对排序的处理:UNION会排序,UNION ALL只是简单地将两个结果集合并

3.效率方面的区别:因为UNION 会做去重和排序处理,因此效率比UNION ALL慢很多

explain select * from ddc.user_test where login="x20230506" union all select * from ddc.user_test where login_id="codeporter";
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1PRIMARYuser_testNULLrefuser_idx_loginuser_idx_login202const1100NULL
2UNIONuser_testNULLrefuser_idx_login_iduser_idx_login_id203const1100NULL

explain关注点

重点要关注如下几列:

列名备注
type本次查询表联接类型,从这里可以看到本次查询大概的效率。
key最终选择的索引,如果没有索引的话,本次查询效率通常很差。
key_len本次查询用于结果过滤的索引实际长度。
rows预计需要扫描的记录数,预计需要扫描的记录数越小越好。
Extra额外附加信息,主要确认是否出现 Using filesort、Using temporary 这两种情况。

 其中,type包含以下几种结果,从上之下依次是最差到最好:

类型备注
ALL执行full table scan,这是最差的一种方式。
index执行full index scan,并且可以通过索引完成结果扫描并且直接从索引中取的想要的结果数据,也就是可以避免回表,比ALL略好,因为索引文件通常比全部数据要来的小。
range利用索引进行范围查询,比index略好。
index_subquery子查询中可以用到索引。
unique_subquery子查询中可以用到唯一索引,效率比 index_subquery 更高些。
index_merge可以利用index merge特性用到多个索引,提高查询效率。
ref_or_null表连接类型是ref,但进行扫描的索引列中可能包含NULL值。
fulltext全文检索。
ref基于索引的等值查询,或者表间等值连接。
eq_ref表连接时基于主键或非NULL的唯一索引完成扫描,比ref略好。
const基于主键或唯一索引唯一值查询,最多返回一条结果,比eq_ref略好。
system查询对象表只有一行数据,这是最好的情况。

MySQL中的explain解析_杨 戬的博客-CSDN博客

索引失效的情况及解决(超详细)_zyy_demon的博客-CSDN博客

MySQL中,`OR` 运算符在 WHERE 子句中的使用可能会对数据库查询的性能产生影响,特别是当涉及到多个条件和索引优化时。当`OR`连接了两个或更多的列,并且这些列没有共同的索引时,MySQL可能不会使用已有的索引来加速查询。 原因在于,MySQL默认情况下倾向于选择使用覆盖索引(也就是只需要读取索引本身就可以获取所需数据),而不是等价于全表扫描。对于`OR`操作,如果每个条件对应的列都没有单独索引,MySQL可能需要分别扫描每个列的数据,然后在内存中合并结果,这可能导致索引无法发挥其作用,性能下降。 例如: ```sql SELECT * FROM table_name WHERE column1 = 'value1' OR column2 = 'value2'; ``` 在这个查询中,如果没有为 `column1` 和 `column2` 分别建立组合索引(包含这两个列),MySQL可能会分别查找满足每个条件的行,最后返回它们的并集,此时单列索引将被分开使用,效率不高。 为了优化这样的查询,你可以考虑以下几个策略: 1. **创建复合索引**:如果有多个条件都经常一起使用,可以尝试创建包含所有条件的复合索引。 2. **子查询或临时表**:有时可以将查询分解成更小的部分,使用子查询或者创建临时表来提高效率。 3. **避免不必要的使用`*`**:尽量指定需要的具体列,这样MySQL可以根据实际需要只检索部分数据。 相关问题: 1. MySQL如何决定是否使用索引? 2. 如何确定何时需要创建复合索引? 3. 使用NOT IN代替OR有什么效果?
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值