MySQL优化之1：or条件优化

本文主要是介绍MySQL优化之1：or条件优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MySQL5.0和更新版本中引入了一种叫：索引合并（Index merge）的策略,一定程度上可以使用表上多个单列索引来定位指定的行。

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

1. 对or语句求并集，如查询select * from film_actor where c1 = “xxx” or c2 = "xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果合并（union）操作，得到最终结果。

2. 对and语句求交集，如查询select * from film_actor where c1 = “xxx” and c2 = "xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果取交集（intersect）操作，得到最终结果。

3. 组合前两种情况的合并及相交。

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

以select * from film_actor where c1 = “xxx” and c2 = "xxx"为例：

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

2. 当c1列或c2列选择性较差且统计信息不准时，比如整表数据量1000万，按照c2列条件返回800万数据，按照c1列返回100条数据，此时按照c2列条件进行索引扫描+聚集索引查询的操作成本极高（可能是整表扫描的百倍消耗），对100

条数据和800万数据求交集的成本也极高，最终导致整条SQL需要消耗大量CPU和IO资源，且相应时间超长，而如果值使用c1列的索引，查询消耗资源少且性能较高。

即使用select * from film_actor where c1 = “xxx” union all select * from film_actor where c2 = "xxx"往往更好。

索引合并策略有时候是一种优化的结果，但实际上更多的时候说明了表上的索引建的的很糟糕:

1、当出现服务器对多个索引做相交操作时（通常有多个and条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。

2、当服务器需要对多个索引做合并操作时（通常有多个or条件），通常需要消耗大量cpu和内存资源在算法缓存、排序和合并操作上。特别是当其中某些索引的选择性不高，需要合并扫描返回的大量数据的时候。

3、更重要的是，优化器不会把这些计算到“查询成本”中，优化器只关心随机页面读取。这回使得查询的成本被“低估”，导致该执行计划还不如直接走全表扫描。

这篇关于MySQL优化之1：or条件优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！