mysql中or条件的使用优化避免全表扫描

发表于 | 分类于

原因(cause)

生产环境有个sql很缓慢,查询了全表,类似

select * from table where  = 1 or b = 2

本文介绍

本文主要解决在sql中使用or的情况下,尽量避免sql进行全表查询，也就是type 显示all的情况

介绍

MySQL在 5.0版本中引入新特性：索引合并优化(Index merge optimization)，当查询中单张表可以使用多个索引时，同时扫描多个索引并将扫描结果进行合并

该特新主要应用于以下三种场景：

1、对OR语句求并集，如查询SELECT * FROM TB1 WHERE c1=”xxx” OR c2=””xxx”时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果合并（union）操作，得到最终结果

2、对AND语句求交集，如查询SELECT * FROM TB1 WHERE c1=”xxx” AND c2=””xxx”时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果取交集（intersect）操作，得到最终结果

3、对AND和OR组合语句求结果

该新特性可以在一些场景中大幅度提升查询性能，但受限于MySQL糟糕的统计信息，也导致很多场景查询性能极差甚至导致数据库崩溃。

以SELECT * FROM table WHERE a=”xxx” AND a=””xxx” 为例：

1、当a列和b列选择性较高时，按照a和b条件进行查询性能较高且返回数据集较小，再对两个数据量较小的数据集求交集的操作成本也较低，最终整个语句查询高效；

2、当a列或b列选择性较差且统计信息不准时，比如整表数据量2000万，按照b列条件返回1500万数据，按照a列返回1000条数据，此时按照b列条件进行索引扫描+聚集索引查找的操作成本极高(可能是整表扫描的百倍消耗)，对1000条数据和1500万数据求交集的成本也极高，最终导致整条SQL需要消耗大量CPU和IO资源且相应时间超长，而如果值使用a列的索引，查询消耗资源较少且性能较高。

由于上述的问题，绝大多数的运维团队都会选择关闭该特性来避免执行异常，京东商城也出现过类似案例，严重影响业务正常运行。

解决方法

在某些情况下，or条件可以避免全表扫描的。
1、where 语句里面如果带有or条件, myisam表能用到索引， innodb不行。

mysql版本5.5.24测试不成功 mysql版本5.5.35innodb测试不成功

2 .必须所有的or条件都必须是独立索引，独立索引通俗的讲就是自己这一列单独有一个索引

  1. 用UNION替换OR (适用于索引列)
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 
注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.
  1. 用in来替换or 
低效: 
select…. from location where loc_id = 10 or loc_id = 20 or loc_id = 30 
高效 
select… from location where loc_in  in (10,20,30);

参考文章

https://blog.csdn.net/hguisu/article/details/7106159
https://blog.csdn.net/fsp88927/article/details/80662422