高级SQL优化 | 你真的了解用 UNION替换OR吗？

2024-08-20 15:48:15 浏览数 (2)

本篇属于高级SQL优化系列专题中的一篇，该专题介绍PawSQL引擎优化算法原理及优化案例，欢迎大家订阅。

问题定义

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

代码语言：javascript复制
select * from customer where c_phone like '139%' or c_name = 'Ray'

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

代码语言：javascript复制
select * from customer where c_phone like '139%'
union 
select * from customer where c_name = 'Ray'

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

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

适用条件

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

案例分析

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

代码语言：javascript复制
select * from customer 
where c_phone = '1' or c_phone like '9%'

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

案例2. 过滤条件选择率足够低，无需重写

代码语言：javascript复制
select * from customer 
where custkey = 1 and (c_phone = '1' or c_phone like '9%')

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

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

代码语言：javascript复制
select distinct * from customer 
where c_phone like '139%' or c_name = 'Ray'

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

代码语言：javascript复制
select * from customer where c_phone = '1' 
union 
select * from customer where c_phone like '139%'

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

代码语言：javascript复制
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

代码语言：javascript复制
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子句，满足重写条件

代码语言：javascript复制
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进行了重写优化.

代码语言：javascript复制
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. 包含分组和聚集，满足重写条件

代码语言：javascript复制
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进行了重写优化.

代码语言：javascript复制
select PawDT_1702555890385.O_ORDERDATE, count(1)
from (
    select /*QB_2*/ o.O_ORDERDATE
    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
     from tpch_pkfk.orders as o
     where o.O_ORDERDATE >= '2021-01-01' and o.O_ORDERPRIORITY = '1'
 ) as PawDT_1702555890385
group by PawDT_1702555890385.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镜像的方式提供部署安装。
sql union 索引 性能 优化

0 人点赞