跳到主要内容

SQL优化技巧 - 连接消除

· 阅读需 6 分钟
PawSQL Team
PawSQL Team
Optimize your SQL Queries by Clicks!

定义​

连接消除（Join Elimination）通过在不影响最终结果的情况下从查询中删除表，来简化SQL以提高查询性能。通常，当查询包含主键-外键连接并且查询中仅引用主表的主键列时，可以使用此优化。

考虑下面的例子，

select o.* from orders o inner join customer c on c.c_custkey=o.o_custkey

订单表（orders）和客户表（customer）关联，且c_custkey是客户表的主键，那么客户表可以被消除掉，重写后的SQL如下：

select * from orders where o_custkey

连接消除的类型​

内连接消除​

内连接消除需要满足以下条件

  • 事实上的主外键等值连接（父表的连接列非空且唯一）

  • 父表的主键是查询中唯一被引用的父表的列

内连接消除的方式：

  • 父表及主外键连接条件被消除

  • 其他对于父表主键的引用被替换成外表的外键

  • 如果外键可以为空且无其他的NFC条件1，则需要新增一个外键不为空的条件

案例：

  • 原始SQL

    select c_custkey from customer,orders where c_custkey=o_custkey

  • 重写后的SQL

    select orders.o_custkey from orders where orders.o_custkey is not null

外连接消除​

外连接消除需要满足以下条件：

  • 被消除的外连接必须是一个左外连接或右外连接

  • 连接的条件必须存在一个由AND连接的主外键等值连接

  • 内表的主键（非空且唯一）是查询中唯一被引用的内表的列

外连接消除的方式：

  • 内表及其全部连接条件被消除

  • 其他对于内表主键的引用被替换成内表的外键

案例1：PK只出现在连接条件中

  • 原始SQL
select o_custkey from orders left join customer on c_custkey=o_custkey
  • 重写后的SQL
select orders.o_custkey from orders

案例2：PK出现在其他地方

  • 原始SQL
select orders.* from customer right join orders on c_custkey=o_custkey and c_custkey>20 where o_orderstatus='T'
  • 重写后的SQL
select orders.* from orders where orders.o_orderstatus = 'T'

数据库中的连接消除​

对于第一章节中的SQL:

select o.* from orders o inner join customer c on c.c_custkey=o.o_custkey

MySQL执行计划为：

-> Inner hash join (o.O_CUSTKEY = c.C_CUSTKEY)  (cost=20541.07 rows=20013)
    -> Table scan on o  (cost=2529.20 rows=200128)
    -> Hash
        -> Index scan on c using key_idx  (cost=0.35 rows=1)

PostgreSQL的执行计划为：

Hash Join  (cost=401.29..711.56 rows=10001 width=115)
  Hash Cond: (o.o_custkey = c.c_custkey)
  ->  Seq Scan on orders o  (cost=0.00..284.01 rows=10001 width=115)
  ->  Hash  (cost=276.29..276.29 rows=10000 width=4)
        ->  Index Only Scan using customer_pkey on customer c  (cost=0.29..276.29 rows=10000 width=4)

重要提示: 可以看出，MySQL和PostgreSQL都不支持连接消除重写优化。

PawSQL中的连接消除​

PawSQL通过JoinEliminationRewrite优化重写，提供比较完善的连接消除优化。

  • 输入SQL语句
  select orders.* from customer right join orders on c_custkey=o_custkey and c_custkey>20
  • 应用JoinEliminationRewrite后重写的SQL为：
  select orders.* from orders
  • 优化前的执行计划
   -> Nested loop left join  (cost=90585.51 rows=200128)
    -> Table scan on orders  (cost=20540.71 rows=200128)
    -> Filter: (orders.O_CUSTKEY > 20)  (cost=0.25 rows=1)
        -> Single-row covering index lookup on customer using key_idx (C_CUSTKEY=orders.O_CUSTKEY)  (cost=0.25 rows=1)
  • 优化后的执行
   -> Table scan on orders  (cost=20540.71 rows=200128)

可以看到，PawSQL很好的支持了连接消除重写优化，仅仅通过连接消除重写，性能的提升就达到了441.01%.

总结​

由于原生的MySQL和PostgreSQL都不支持表关联消除，PawSQL的JoinEliminationRewrite重写优化对它们是一个有意义的补充。在SQL被部署至生产环境真正执行之前，使用PawSQL可以把无意义的表关联给消除掉，避免数据库花费资源进行无意义的表关联操作。

关于PawSQL​

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

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

联系我们​

Footnotes​

  1. 关于NFC条件，请参考高级SQL优化系列中外连接优化中对于NFC的定义 ↩