文档首页/ 数据仓库服务 DWS/ 开发指南/ 开发指南(9.1.1.x)/ DWS性能调优/ SQL调优/ SQL调优进阶/ 通过主外键约束消除多余的JOIN
更新时间：2025-07-10 GMT+08:00
查看PDF
分享

通过主外键约束消除多余的JOIN

本文介绍如何使用主键（PRIMARY KEY）与外键（FOREIGN KEY）之间的约束关系来优化查询计划，消除多余的JOIN操作。

前提条件

  • 仅9.1.0.200及以上集群版本支持通过主外键约束消除多余的JOIN。
  • 创建FOREIGN KEY前，需开启声明外键约束功能： 
    1
    		 
    set info_constraint_options = 'foreign_key';

功能介绍

在关系型数据库中，使用JOIN连接可以将多张表数据组合在一起。在大型数据分析场景中，多表JOIN是非常常见的操作。与此同时，JOIN操作SQL执行中也是最为耗时的操作之一。因此JOIN消除是一种能极大提升SQL执行性能的优化技术之一。其中，根据用户定义的表直接的主外键约束信息进行JOIN消除是一种具体的优化方法。这些约束提供了关于表与表之间关系的信息，可以用来减少不必要的JOIN操作，减少查询时间，提高数据库性能。

DWS数据库支持通过设置GUC参数rewrite_rule来开启或关闭使用外键约束信息消除多余JOIN操作的功能。具体设置如下：

  • 开启JOIN消除优化功能。开启后，系统在SQL执行过程中，会获取表之间已经定义好的主外键约束关系，并根据此信息结合JOIN消除规则，进行JOIN消除。 
    1
    		 
    SET rewrite_rule = 'join_elimination';
  • 关闭JOIN消除优化功能。 
    1
    		 
    SET rewrite_rule = '';

操作流程

  1. 声明FOREIGN KEY。
  2. 应用JOIN消除优化查询。

声明FOREIGN KEY

  • 新建表时，通过CREATE TABLE创建FOREIGN KEY。
  • 对于已存在的表，通过ALTER TABLE创建或删除FOREIGN KEY。
  • 您需要自行确保主键和外键之间的数据约束关系。DWS不会进行数据的约束检查。如果用户未能保证正确的主键与外键约束关系，开启join消除后可能会造成查询返回的结果集错误问题。
  • DROP TABLE命令会自动删除外键，DWS不会进行检验和报错。
  • 如需使用JOIN消除优化，外键列须同时带有NOT NULL约束；若无NOT NULL约束，则优化器会自动添加外键列的IS NOT NULL过滤条件来确保外键不为NULL，然后应用JOIN消除优化。
  1. 通过CREATE TABLE创建FOREIGN KEY。
    • 创建一个带有主键的表item。 
      1
      		 
      CREATE TABLE item(a1 INT PRIMARY KEY, b1 INT) WITH (ORIENTATION=COLUMN) distribute by HASH(a1);
    • 通过CREATE TABLE语句创建带有外键约束的表store, 外键列为a2带有NOT NULL 约束，指向item表的主键列a1。 
      1
      		 
      CREATE TABLE store(a2 int NOT NULL REFERENCES item(a1), b2 int);
  2. 通过ALTER TABLE创建或删除FOREIGN KEY。
    • 创建一个带有主键的表item。 
      1
      		 
      CREATE TABLE item(a1 INT PRIMARY KEY, b1 INT) WITH (ORIENTATION=COLUMN) distribute by HASH(a1);
    • 通过CREATE TABLE语句创建一张非外键表store。 
      1
      		 
      CREATE TABLE store(a2 int NOT NULL , b2 int);
    • 通过ALTER TABLE为store表增加外键。 
      1
      		 
      ALTER TABLE store ADD FOREIGN KEY (a2) REFERENCES item(a1);
    • 通过ALTER TABLE 为store表删除外键。 
      1
      		 
      ALTER TABLE store DROP CONSTRAINT store_a2_fkey;
  3. 查看表上的外键约束。

  4. DWS还提供函数验证外键约束的合法性，用户需手动调用。 
    1
    		 
    SELECT check_foreign_key_constraint('store');

应用JOIN消除优化查询

JOIN消除是在JOIN连接中，利用主外键约束消除不必要的JOIN关系，以简化查询计划并提升查询性能。

前提条件：将join_elimination加入rewrite_rule中。

1
 
 SET rewrite_rule='join_elimination';
  • 两表内连接主外键消除。

    test2.a2 为外键，test1.a1为主键。

    1
2
3
4
5
6
7
8
    		 
    EXPLAIN (COSTS OFF, NODES OFF) SELECT a1, a2 FROM test1, test2 WHERE a1 = a2 ORDER BY 1,2;
             QUERY PLAN              
-------------------------------------
  id |          operation           
 ----+------------------------------
   1 | ->  Streaming (type: GATHER)
   2 |    ->  Sort                  
   3 |       ->  Seq Scan on test2  

    1
2
3
4
5
6
7
8
    		 
    EXPLAIN (COSTS OFF, NODES OFF) SELECT a1, a2 FROM test1 inner join test2 on a1 = a2 ORDER BY 1,2;
             QUERY PLAN              
-------------------------------------
  id |          operation           
 ----+------------------------------
   1 | ->  Streaming (type: GATHER)
   2 |    ->  Sort                  
   3 |       ->  Seq Scan on test2
  • 多表内连接消除 vtest2 -> (vtest1, vtest3)。

    vtest2 外键表，vtest1，vtest3主键表。

    1
2
3
4
5
6
7
8
    		 
    EXPLAIN (COSTS OFF, NODES OFF)select vtest2.* from vtest2, vtest1, vtest3 where a2 = a1 and b2 = a3;
                  QUERY PLAN                   
-----------------------------------------------
  id |               operation                
 ----+----------------------------------------
   1 | ->  Row Adapter                        
   2 |    ->  Vector Streaming (type: GATHER)
   3 |       ->  CStore Scan on vtest2
  • 多表串联内连接消除 test2 -> test1 -> test3。

    test2 外键表, test1既是外键表，也是主键表，test3是主键表。

    1
2
3
4
5
6
7
8
    		 
    EXPLAIN (COSTS OFF, NODES OFF) SELECT a1, a2 FROM test1, test2, test3 WHERE a1 = a2 and b1 = a3 ORDER BY 1,2;
             QUERY PLAN              
-------------------------------------
  id |          operation           
 ----+------------------------------
   1 | ->  Streaming (type: GATHER)
   2 |    ->  Sort                  
   3 |       ->  Seq Scan on test2  

    1
2
3
4
5
6
7
    		 
    EXPLAIN (COSTS OFF, NODES OFF) SELECT a1, a2 FROM test2 inner join (select test1.* from test1 inner join test3 on b1 = a3) on a1 = a2;;
             QUERY PLAN              
-------------------------------------
  id |          operation           
 ----+------------------------------
   1 | ->  Streaming (type: GATHER)
   2 |    ->  Seq Scan on test2
  • 无法消除的场景。

    如果查询中引用了主键表的非主键列，内连接也无法消除 （如下需要返回test1.b1, 则无法消除）：

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    		 
    EXPLAIN (COSTS OFF, NODES OFF)
SELECT b1, a2 FROM test1, test2 WHERE a1 = a2 ORDER BY 1,2;
                  QUERY PLAN                   
-----------------------------------------------
  id |             operation             
 ----+-----------------------------------
   1 | ->  Streaming (type: GATHER)      
   2 |    ->  Sort                       
   3 |       ->  Hash Join (4,5)         
   4 |          ->  Seq Scan on test2    
   5 |          ->  Hash                 
   6 |             ->  Seq Scan on test1

 Predicate Information (identified by plan id)
 ---------------------------------------------
   3 --Hash Join (4,5)
         Hash Cond: (test2.a2 = test1.a1)
父主题： SQL调优进阶

相关文档