MySQL中关于OR条件的优化

转载

     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 TB1 WHERE c1="xxx" AND c2=""xxx" 为例：

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

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

 

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

 

最近系统中发现SQL执行异常，SQL类似为:

SELECT *

FROM tb_xxxx_xxxx

WHERE yn=0

AND C1=‘123456789’

OR C2=‘123456789’;

 

表上C1和C2列分别建有索引，但OR条件导致仅扫描任何一个索引都无法得到满足条件的全部数据，需要同时扫描两个索引并对两个临时结果求并集，但由于我们关闭了Index merge特性，导致执行优化器只能对表进行全表扫描并导致执行性能不佳。

 

该问题的临时解决办法为开启Index merge特性，但存在未知风险，因此我们建议修改SQL，将OR操作修改为UNION操作，使得不开启Index merge特性的情况下语句依然能使用多个索引，优化SQL为：

SELECT *

FROM tb_xxxx_xxxx

WHERE yn=0

AND C1=‘123456789’

UNION ALL

SELECT *

FROM tb_xxxx_xxxx

WHERE yn=0

AND C2=‘123456789’

AND C1<>‘123456789’

 

PS：

1、在第二个SELECT语句中增加第一个SELECT语句条件的反操作，从而保证两个SELECT 语句中没有重复数据，可以使用UNION ALL来求交集，避免UNION所带来的排序消耗。

     2、在编写SQL语句时，需要注意OR条件的书写，

原SQL为：

WHERE yn=0

AND C1=‘123456789’

OR C2=‘123456789’

等价于：

WHERE (yn=0 AND C1=‘123456789’)

OR C2=‘123456789’

          而实际需求要求所有返回数据满足yn=0的条件，应正确写为:

WHERE yn=0

AND (C1=‘123456789’

OR C2=‘123456789’)