如何通过主外键约束提升JOIN查询性能 - 云原生数据仓库AnalyticDB

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

前提条件

AnalyticDB for MySQL集群内核版本需为3.1.10或以上。

说明

查看湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。

功能介绍

在数据库中，通常在大型数据表中保存大量数据，同时还需要进行复杂的查询和分析操作。使用JOIN连接可以将两个或多个表中的行组合在一起。但是，JOIN连接操作可能会导致性能下降，因此需要优化查询性能。JOIN消除是一种优化查询性能的技术。其中，根据主外键约束信息进行JOIN消除是一种具体的优化方法。这些约束提供了关于表与表之间关系的信息，可以用来减少不必要的JOIN操作，减少查询时间，提高数据库性能。

在AnalyticDB for MySQL中，支持通过Hint/*+ PK_FK_DEPENDENCY_ENABLED*/来开启或关闭使用外键约束信息消除多余JOIN操作的功能，其中：

/*+ PK_FK_DEPENDENCY_ENABLED = true*/：开启获取PK-FK信息功能。开启后，系统在应用JOIN消除规则的时候会获取到表中PK-FK信息，并根据此信息进行JOIN消除。
/*+ PK_FK_DEPENDENCY_ENABLED = false*/：关闭获取PK-FK信息功能。关闭后，系统无法根据PK-FK信息应用JOIN消除规则。

说明

PK（PRIMARY KEY）：主键
FK（FOREIGN KEY）：外键

声明FOREIGN KEY

新建表时，通过CREATE TABLE创建FOREIGN KEY。
对于已存在的表，通过ALTER TABLE创建或删除FOREIGN KEY。

重要

不支持多个列作为外键，例如：FOREIGN KEY (sr_item_sk, sr_ticket_number) REFERENCES store_sales(ss_item_sk,d_date_sk)。
您需要自行确保主键和外键之间的数据约束关系。AnalyticDB for MySQL不会进行数据的约束检查。
DROP TABLE命令会自动删除外键，AnalyticDB for MySQL不会进行检验和报错。
外表不支持创建外键约束。

通过CREATE TABLE创建FOREIGN KEY

语法

详细的语法请参见CREATE TABLE。

示例

创建名为db的数据库，并在该数据库下创建一个名为item的表，主键为i_item_sk，示例如下：

CREATE DATABASE db;
USE db;
CREATE TABLE item
(
  i_item_sk bigint NOT NULL,
  i_current_price bigint,
  PRIMARY KEY(i_item_sk)
)
DISTRIBUTED BY HASH(i_item_sk);

通过CREATE TABLE创建FOREIGN KEY。
- 在同一数据库中创建FOREIGN KEY。
  在db数据库中创建一个名为store_returns的表，通过使用外键语法FOREIGN KEY将sr_item_sk列和item表的主键列i_item_sk关联起来。示例如下：
```
CREATE TABLE store_returns
(
  sr_sale_id bigint,
  sr_store_sk bigint,
  sr_item_sk bigint NOT NULL,
  FOREIGN KEY (sr_item_sk) REFERENCES item (i_item_sk)
);
```
  说明
  由于缺少参数symbol，解析器将会使用外键列名自动补充约束名为sr_item_sk_fk。
- 跨数据库创建FOREIGN KEY。
  创建名为db2的数据库，并在该数据库中创建名为store_sales的表，创建外键列ss_item_sk。示例如下：
```
CREATE DATABASE db2;
USE db2;
CREATE TABLE store_sales
(
  ss_sale_id bigint,
  ss_store_sk bigint,
  ss_item_sk bigint not null,
  CONSTRAINT fk_constraint FOREIGN KEY (ss_item_sk) REFERENCES db.item (i_item_sk)
);
```

通过CREATE TABLE语句为同一个表声明多个FOREIGN KEY

示例：

分别创建名为customer和vendor的表。

USE db;
CREATE TABLE customer
(
  i_customer_sk bigint NOT NULL,
  i_current_price bigint,
  PRIMARY KEY(i_customer_sk)
)
DISTRIBUTED BY HASH(i_customer_sk);

CREATE TABLE vendor
(
  id bigint primary key,
  name varchar(5) not null
);

创建名为store_product的表，将sr_sale_id列和vendor表的主键列id关联起来；将sr_customer_sk列和customer表的主键列i_customer_sk关联起来。

CREATE TABLE store_product
(
  sr_sale_id bigint,
  sr_store_sk bigint,
  sr_customer_sk bigint NOT NULL,
  FOREIGN KEY (sr_sale_id) REFERENCES vendor (id),
  FOREIGN KEY (sr_customer_sk) REFERENCES customer (i_customer_sk)
);

查看已创建的FOREIGN KEY

通过SHOW CREATE TABLE查看已创建的FOREIGN KEY。

示例：

USE db;
SHOW CREATE TABLE store_returns;

返回结果：

-- 结果（省略其他语句）：
-- CONSTRAINT `sr_item_sk_fk` FOREIGN KEY (`sr_item_sk`) REFERENCES `db`.`item`(`i_item_sk`)

通过ALTER TABLE创建或删除FOREIGN KEY

语法

详细的语法请参见ALTER TABLE。

示例：

本示例中，数据库为db，需要添加外键的表为store_returns，主表为item。

删除表store_returns的外键。

USE db;
ALTER TABLE store_returns DROP FOREIGN KEY sr_item_sk_fk;

为表store_returns添加外键。

USE db;
ALTER TABLE store_returns ADD CONSTRAINT sr_item_fk FOREIGN KEY (sr_item_sk) REFERENCES item (i_item_sk);

通过ALTER TABLE为同一个表声明多个FOREIGN KEY

ALTER TABLE语句一次只能为一个表添加一个外键，当需要为同一个表添加多个外键时，可以使用多条ALTER TABLE语句。

示例：

在名称为db2的数据库中创建一个store表：

USE db2;
CREATE TABLE store
(
  id bigint primary key,
  name varchar(5) not null
);

为db数据库的store_returns表添加外键：

ALTER TABLE db.store_returns ADD FOREIGN KEY (sr_store_sk) REFERENCES store(id);

应用JOIN消除规则

JOIN消除是在JOIN连接中，只需要查询一个表中的数据时，可以利用主外键约束消除不必要的JOIN关系，以简化查询计划并提升查询性能。常见应用场景如下：

同一数据库中的双表连接查询

例如，查询在store_returns和item表之间执行JOIN操作，但仅选择输出store_returns中的列。

/*+ PK_FK_DEPENDENCY_ENABLED = true*/
EXPLAIN
SELECT
  s.sr_sale_id,
  s.sr_store_sk,
  s.sr_item_sk
FROM
  store_returns s,
  item
WHERE
  sr_item_sk = i_item_sk;

返回结果：

+---------------+
| Plan Summary  |
+---------------+
 1- Output[ Query plan ] {Est rowCount: 1.0}
 2    -> Exchange[GATHER] {Est rowCount: 1.0}
 3        - TableScan {table: store_returns, Est rowCount: 1.0}

执行计划中没有INNER JOIN算子，说明优化器能够利用sr_item_sk和i_item_sk的约束安全地消除表store_returns与表item的连接。

跨数据库连接查询

例如，涉及数据库db的item表和数据库db2的store_sales表连接的查询：

USE db2;
-- 示例查询语句
/*+ PK_FK_DEPENDENCY_ENABLED = true*/
EXPLAIN
SELECT
  s.ss_sale_id,
  s.ss_item_sk
FROM
  store_sales s,
  db.item
WHERE
  ss_item_sk = i_item_sk;

返回结果：

+---------------+
| Plan Summary  |
+---------------+
 1- Output[ Query plan ] {Est rowCount: 1.0}
 2    -> Exchange[GATHER] {Est rowCount: 1.0}
 3        - TableScan {table: store_sales, Est rowCount: 1.0}

多表连接查询

例如，涉及表store_returns、表item以及表store连接的查询：

USE db;
-- 示例查询语句
/*+ PK_FK_DEPENDENCY_ENABLED = true*/
EXPLAIN
SELECT
  s.sr_sale_id,
  s.sr_store_sk,
  s.sr_item_sk
FROM
  store_returns s,
  item,
  db2.store
WHERE
  sr_item_sk = i_item_sk
  AND sr_store_sk = id;

返回结果：

+---------------+
| Plan Summary  |
+---------------+
 1- Output[ Query plan ] {Est rowCount: 1.0}
 2    -> Exchange[GATHER] {Est rowCount: 1.0}
 3        - TableScan {table: store_returns, Est rowCount: 1.0}

视图

由于视图的设计，可能会使用到两个或以上表的JOIN，但是将视图内容应用到其他查询语句中时，该视图就有可能包含了无用的信息，从而允许优化器消除无用的JOIN。例如创建一个包含表store_returns和表item信息的视图结构：

CREATE VIEW sr_item_v AS
SELECT
  s.sr_store_sk AS store_name,
  s.sr_sale_id AS sale_id,
  s.sr_item_sk AS sr_item_id,
  item.i_current_price AS item_price,
  item.i_item_sk as item_id
FROM
  store_returns s,
  item
WHERE
  sr_item_sk = i_item_sk;

在查询中不引用item表中的item_price列时，优化器可以优化不扫描item表。例如：

/*+ PK_FK_DEPENDENCY_ENABLED = true*/
EXPLAIN
SELECT store_name, sr_item_id, sale_id
FROM sr_item_v;

返回结果：

+---------------+
| Plan Summary  |
+---------------+
 1- Output[ Query plan ] {Est rowCount: 1.0}
 2    -> Exchange[GATHER] {Est rowCount: 1.0}
 3        - TableScan {table: store_returns, Est rowCount: 1.0}

由于item.i_item_sk和store_returns.sr_item_sk之间存在外键约束，优化器知道可以将对item.i_item_sk的引用替换为对store_returns.sr_item_sk的引用。在这种情况下，优化器将查询转换为store_returns表中的列，从而允许使用JOIN消除规则。

无法应用JOIN消除规则的情况

以上文的视图结构为例，查询使用到了item表上的一些列，JOIN操作就是必需的。

-- 示例查询语句
/*+ PK_FK_DEPENDENCY_ENABLED = true*/
EXPLAIN
SELECT store_name, sr_item_id, sale_id, item_price
FROM sr_item_v;

返回结果：

+---------------+
| Plan Summary  |
+---------------+
 1- Output[ Query plan ] {Est rowCount: 1.0}
 2    -> Exchange[GATHER] {Est rowCount: 1.0}
 3        -> InnerJoin[Hash Join] {Est rowCount: 1.0}
 4            -> Project {Est rowCount: 1.0} 
 5                -> Exchange[REPARTITION] {Est rowCount: 1.0}
 6                    - TableScan {table: store_returns, Est rowCount: 1.0} 
 7            -> LocalExchange[HASH] {Est rowCount: 1.0}
 8                -> ScanProject {table: item, Est rowCount: 1.0} 
 9                    - TableScan {table: item, Est rowCount: 1.0}