如何通過主外鍵約束提升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}