高级SQL优化 - 表连接消除（Join Elimination）

高级SQL优化系列（Channel of advanced SQL tuning）

连接消除定义

---

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

考虑下面的例子，

select o.*from orders o innerjoin 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

外连接消除

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

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

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

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

外连接消除的方式：

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

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

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

• 原始SQL

select o_custkey from orders leftjoin 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 loopleftjoin  (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: https://app.pawsql.com

邮件：service@pawsql.com

Twitter: https://twitter.com/pawsql

扫描关注PawSQL公众号

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