SQL优化技巧 - OR连接的条件重写为UNION

2024年1月15日 · 阅读需 6 分钟

Optimize your SQL Queries by Clicks!

问题定义

如果使用OR连接两个查询条件，数据库优化器有可能无法使用索引来完成查询。譬如对于以下的SQL语句，

select * from customer where c_phone like '139%' or c_name = 'Ray'

如果这两个字段上都有索引，可以把他们重写为UNION查询，以便使用索引提升查询性能。

select * from customer where c_phone like '139%'
union 
select * from customer where c_name = 'Ray'

但是这种转换并不总是能够提升查询性能，它需要一定的适用条件，并需要经过基于代价的估算。

如果数据库支持INDEX MERGING（请参考如和创建高效的索引），也可以调整数据库相关参数启用INDEX MERGING优化策略来提升数据库性能。

适用条件

OR连接的条件必须是可以利用索引的
重写后的UNION语句估算代价比原SQL小
如果OR分支的条件是互斥的，那么重写为UNION ALL

案例分析

案例1. 条件分支无法利用索引，不进行重写

select * from customer where c_phone = '1' or c_phone like '%139%'

解析: 其中一个条件c_phone like '%139%'无法利用索引，重写后仍然需要全表扫描，PawSQL不进行重写

案例2. 过滤条件选择率足够低，不进行重写

select * from customer where custkey = 1 and (c_phone = '1' or c_phone like '%139%')

解析：custkey是主键，custkey = 1唯一定位一条记录，所以无需进行重写

案例3. 满足重写条件，进行重写

select distinct * from customer where c_phone like '139%' or c_name = 'Ray'

解析：两个条件都可以利用索引，且选择率低于10%，可以进行重写，由于union可以去重，所以原SQL中的distinct在重写后可以去除。

select * from customer where c_phone = '1' 
union 
select * from customer where c_phone like '139%'

案例4. OR条件分支互斥，重写为UNION ALL

select * from customer where custkey = 1 or (custkey = 2 and c_phone like '139%')

解析：由于两个条件分支c_custkey = 2 and c_phone like '139%' 和 c_custkey = 1 互斥，因此重写为UNION ALL

select * from customer where c_custkey = 2 and c_phone like '139%'
union all 
select * from customer where c_custkey = 1

案例5. 包含order by子句和limit子句，满足重写条件

select * from orders o
where O_ORDERDATE>='2021-01-01' and (O_ORDERPRIORITY = 1 or O_SHIPPRIORITY = 1)
order by O_ORDERDATE desc limit 10

**解析：**虽然O_ORDERPRIORITY = 1 和 O_SHIPPRIORITY = 1的选择率较高，但是由于通过索引可以避免排序，总体代价较低，PawSQL进行了重写优化.

select *
from (
   (select /*QB_2*/ *
    from orders as o
    where o.O_ORDERDATE >= '2021-01-01'
    and o.O_SHIPPRIORITY = 1
    order by o.O_ORDERDATE desc limit 10) 
       union 
  (select /*QB_1*/ *
  from orders as o
  where o.O_ORDERDATE >= '2021-01-01'
    and o.O_ORDERPRIORITY = '1'
  order by o.O_ORDERDATE desc limit 10
  )
) as PawDT_1702555889039
order by PawDT_1702555889039.O_ORDERDATE desc limit 10

案例6. 包含分组和聚集，满足重写条件

select O_ORDERDATE, count(1) from orders o
where O_ORDERDATE>='2021-01-01' and (O_ORDERPRIORITY = 1 or O_SHIPPRIORITY = 1) 
group by O_ORDERDATE

**解析：**虽然O_ORDERPRIORITY = 1 和O_SHIPPRIORITY = 1的选择率较高，但是由于通过索引可以避免排序，总体代价较低，PawSQL进行了重写优化.

select PawDT_1702884016144.O_ORDERDATE, count(1)
from (
    select /*QB_2*/ o.O_ORDERDATE, o.O_ORDERKEY
    from tpch_pkfk.orders as o
    where o.O_ORDERDATE >= '2021-01-01'
    and o.O_SHIPPRIORITY = 1
       union 
select /*QB_1*/ o.O_ORDERDATE, o.O_ORDERKEY
 from tpch_pkfk.orders as o
 where o.O_ORDERDATE >= '2021-01-01'
    and o.O_ORDERPRIORITY = '1'
 ) as PawDT_1702884016144
group by PawDT_1702884016144.O_ORDERDATE

性能验证

案例5性能提升900倍

优化前执行计划（执行时间432.322ms）

优化后执行计划（执行时间0.189ms）

案例6性能提升20倍

优化前执行计划（2.816ms）

优化后执行计划（0.131ms）

关于PawSQL

PawSQL专注数据库性能优化的自动化和智能化，支持MySQL，PostgreSQL，openGauss，Oracle等，提供的SQL优化产品包括

PawSQL Cloud，在线自动化SQL优化工具，支持SQL审查，智能查询重写、基于代价的索引推荐，适用于数据库管理员及数据应用开发人员，
PawSQL Advisor，IntelliJ 插件, 适用于数据应用开发人员，可以IDEA/DataGrip应用市场通过名称搜索“PawSQL Advisor”安装。
PawSQL Engine, 是PawSQL系列产品的后端优化引擎，可以独立安装部署，并通过http/json的接口提供SQL优化服务。PawSQL Engine以docker镜像的方式提供部署安装。

联系我们

邮件：service@pawsql.com
Twitter: https://twitter.com/pawsql
扫描关注PawSQL公众号

问题定义​

适用条件​

案例分析​

性能验证​

案例5性能提升900倍​

案例6性能提升20倍​

关于PawSQL​

联系我们​