数据库内核月报 － 2025 / 12

概述

JOIN 是关系数据库中最基本的操作之一。根据类型的不同，可以将 JOIN 分为 INNER JOIN、OUTER JOIN，OUTER JOIN 可以进一步分为 LEFT OUTER JOIN、RIGHT OUTER JOIN 和 FULL OUTER JOIN。 对于 INNER JOIN，MySQL 中的优化器已有的优化方式是通过 greedy search 找到较优的 JOIN 顺序以提升查询性能。但是如果可以通过一定规则在不影响查询结果的情况下将不必要的 JOIN 消除，那么则可以完全避免一些 JOIN 操作。 对于 OUTER JOIN，除了 FULL OUTER JOIN，其余的两种 OUTER JOIN 都不具备交换性，因此无法像 INNER JOIN 一样通过交换顺序获取更好的性能。MySQL 已有的一些规则可以通过把 OUTER JOIN 转化为 INNER JOIN，这样就可以获取 JOIN 的交换性，以搜索更好的 JOIN 顺序。但是， MySQL 已有的转换规则不是很全面，一些可以将 OUTER JOIN 优化为 INNER JOIN 的 case 并没有覆盖到。 本文主题是 JOIN 消除（JOIN Elimination），包括 INNER JOIN 的消除和 OUTER JOIN 的消除。我们为polardb新增了诺干条消除规则，做到应消尽消，大幅领先于其他厂商的mysql系产品。

产品功能

连接（JOIN）消除已上线，在MySQL 8.0.2，且修订版本需为8.0.2.2.31中已灰度发布。 举个优化的例子，更加直观

SET SESSION join_elimination_mode = 'ON';
#####c表被完全消除掉了
EXPLAIN SELECT o.*, c.* FROM orders o LEFT JOIN customers c ON FALSE;
==》
select o.* ,NULL AS `customer_id`,NULL AS `customer_name` from `testdb`.`orders` `o`

更多样例，请详情参考https://help.aliyun.com/zh/polardb/polardb-for-mysql/user-guide/join-elimination

原理

支持六种场景下的JOIN消除。

LEFT JOIN + FALSE COND

SELECT ..., ti1.*, ti2.*, ..., tin.*, ... FROM ... LEFT JOIN (ti1, ti2, ..., tin) ON FALSE 
=>
SELECT ..., NULL, ..., NULL, ... FROM ... 

• 场景
  • 消除条件 LEFT JOIN的ON condition为FALSE
  • 消除方法
    • 查询中所有指向t1, t2, …, tn的Item_field转换为Item_null
    • 删除(t1, t2, …, tn)及其ON FALSE条件

LEFT JOIN + UNIQUE

SELECT to1.*, to2.*, ..., tom.* FROM to1, to2, ..., tom LEFT JOIN (ti1, ti2, ..., tin) ON cond_on WHERE cond_where ...
=>
SELECT to1.*, to2.*, ..., tom.* FROM to1, to2, ..., tom WHERE cond_where ...

• a. 场景
• b. 消除条件
  • i. 以下两个条件满足一个
    • 1. 外表在LEFT JOIN之前的每一行在LEFT JOIN后有且只有一行结果 => 即LEFT JOIN后外表的rowid能够函数决定内表的rowid。
    • 1. LEFT JOIN结果中的外表列的重复不会影响最终结果
         • a. 位于SEMI JOIN中
         • b. 位于subquery中，并且对group by，limit，window等有限制条件（见代码）
         • c. 递归应用a和b
  • ii. 在LEFT JOIN及其ON条件以外的地方没有对ti1, ti2, …, tin的引用
• c. 消除方法
  • i. 删除(t1, t2, …, tn)及其ON条件

SELF JOIN

CREATE TABLE t1 (uk int unique, ...);

基表JOIN基表
SELECT target.*, source.* FROM t1 as target JOIN t1 as source WHERE target.uk = source.uk;
=>
SELECT source.*, source.* FROM source WHERE source.uk = source.uk;

派生表JOIN派生表
SELECT target.*, source.* FROM (SELECT * FROM t1) target JOIN (SELECT * FROM t1 WHERE t1.a > 1) source WHERE target.uk = source.uk;
=>
SELECT source.*, source.* FROM (SELECT * FROM t1 WHERE t1.a > 1) source WHERE source.uk = source.uk;

基表JOIN派生表
SELECT target.*, source.* FROM t1 target JOIN (SELECT * FROM t1 WHERE t1.a > 1) source WHERE target.uk = source.uk;
=>
SELECT source.*, source.* FROM (SELECT * FROM t1 WHERE t1.a > 1) source WHERE source.uk = source.uk;

• a. 场景：某个基表或派生表target与另一个基表或派生表source做inner join
• b. 消除条件
  • i. source表的结果集是target表的结果集的子集。
  • ii. 所有target表的被引用的field在source表中均存在。
  • iii. 设target表的表列对应的source表的表列为，JOIN条件可以推导出等值条件。消除需满足列集合在target表中具有唯一性。（若列集合为空，那么target表结果集只能有0或1行）
• c. 消除方法
  • i. 用对应的source表中的表列替代target表中的表列（Item_field）
  • ii. 删除target表

SELF SEMI JOIN

CREATE TABLE t1 (uk int unique, a int, b int);

有唯一列相等条件
SELECT source.* FROM t1 as source WHERE EXISTS (SELECT * FROM t1 as target WHERE source.uk = target.uk AND target.a > 1);
=>
SELECT source.* FROM t1 as source WHERE source.uk = source.uk AND source.a > 1;

无唯一列相等条件
SELECT source.* FROM t1 as source WHERE EXISTS (SELECT * FROM t1 as target WHERE source.a = target.a);
=>
SELECT source.* FROM t1 as source WHERE source.a = source.a;

• a. 场景：某个基表或派生表source与另一个基表或派生表target做semi join
• b. 消除条件
  • i. 满足SELF JOIN的消除条件1和2
  • ii. 满足SELF JOIN的消除条件3或者所有与source和target的相关条件均为，与其他条件为AND连接。TODO：可以扩展为所有条件引用的target列都存在于等值条件中。
• c. 消除方法 同SELF JOIN消除方法

FOREIGN KEY JOIN

CREATE TABLE t1 (a int primary key);
CREATE TABLE t2 (a int, foreign key (a) references t1(a));

SELECT t1.a, t2.a FROM t1, t2 WHERE t1.a = t2.a;
=>
SELECT t2.a, t2.a FROM t2 WHERE t2.a = t2.a;

• a. 场景：某个基表或派生表source与另一个基表或派生表target做join，source表中存在foreign key引用target表的列。
• b. 消除条件
  • i. 设target表的表列对应的source表的表列为，JOIN条件可以推导出等值条件。消除需满足下列两个条件之一
    • 1. 列集合不为空，并且在target表中具有唯一性。
    • 1. （特殊情况，需保证当source表不为空时，target表一定不为空）列集合为空，但foreign key对应关系不为空（即n > 0），并且存在一个foreign key的列为non-nullable。 * a. 还可以通过统计信息来判断target表是否为空
  • ii. target表的所有被引用的表列都在列集合中。
• c. 消除方法 i. 用对应的source表中的表列替代target表中的表列（Item_field） ii. 删除target表
• d. 消除前需要对<source, target>对做拓扑排序，优先消除不能用于消除其他表的表。

FOREIGN KEY SEMI JOIN

CREATE TABLE t1 (a int primary key);
CREATE TABLE t2 (a int, foreign key (a) references t1(a));

SELECT t2.a FROM t2 WHERE EXISTS(SELECT * FROM t1 WHERE t1.a = t2.a);
=>
SELECT t2.a FROM t2 WHERE t2.a = t2.a;

• a. 场景：某个基表或派生表source与另一个基表或派生表target做semi join，source表中存在foreign key引用target表的列。
• b. 消除条件
  • i. 设target表的表列对应的source表的表列为，JOIN条件可以推导出等值条件。消除需满足下列两个条件之一
    • 1. 列集合不为空。（只有这一条与FOREIGN KEY JOIN不同）
    • 1. （特殊情况，需保证当source表不为空时，target表一定不为空）列集合为空，但foreign key对应关系不为空（即n > 0），并且存在一个foreign key的列为non-nullable。
         • a. 还可以通过统计信息来判断target表是否为空
  • ii. target表的所有被引用的表列都在列集合中。
• c. 消除方法：与FOREIGN KEY JOIN消除相同 ​