转载

     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’)



发布评论
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

数据库迁移框架Flyway介绍知识解答
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。