プライマリキーと外部キーの制約を使用して結合クエリのパフォーマンスを向上させる方法 - AnalyticDB for MySQL

このトピックでは、主キーと外部キー間の制約関係を使用して、不要な結合を排除し、実行計画を最適化する方法について説明します。

前提条件

V3.1.10以降のAnalyticDB for MySQLクラスターが作成されます。

説明

クラスターのマイナーバージョンをクエリする方法については、AnalyticDB for MySQLクラスターのバージョンを照会するにはどうすればよいですか? クラスターのマイナーバージョンを更新するには、テクニカルサポートにお問い合わせください。

概要

ほとんどの場合、大規模なデータベーステーブルに大量のデータが格納され、複雑なクエリおよび分析操作が実行されます。 JOINステートメントを使用して、2つ以上のテーブルの行を結合できます。ただし、結合操作はクエリのパフォーマンスを低下させる可能性があります。クエリのパフォーマンスを最適化するには、結合の削除を使用できます。最適化方法の1つは、プライマリおよび外部キー制約を使用して不要な結合を排除することです。制約は、テーブル間のリレーションシップに関する情報を提供し、不要な結合を排除し、クエリ時間を短縮し、データベースのパフォーマンスを向上させます。

AnalyticDB for MySQLでは、/* + PK_FK_DEPENDENCY_ENABLED */ ヒントを使用して、プライマリキーと外部キーの制約情報を使用して不要な結合を排除する機能を有効または無効にすることができます。

/* + PK_FK_DEPENDENCY_ENABLED = true */: プライマリおよび外部キー制約情報を取得する機能を有効にします。この機能が有効になった後、システムは主キー制約情報と外部キー制約情報に基づいて結合削除ルールを適用します。
/* + PK_FK_DEPENDENCY_ENABLED = false */: プライマリおよび外部キー制約情報を取得する機能を無効にします。機能を無効にすると、システムは主キー制約情報と外部キー制約情報に基づいて結合削除ルールを適用できません。

説明

PK: 主キー。
FK: 外部キー。

外部キーを宣言

テーブルを作成するときは、create tableステートメントを使用して外部キーを追加します。
既存のテーブルの場合は、ALTER tableステートメントを使用して外部キーを追加または削除します。

重要

複数の列を外部キーとして使用することはできません。例: 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ステートメントを使用して外部キーを追加

構文

構文の詳細については、「CREATE TABLE」をご参照ください。

例

dbという名前のデータベースを作成し、データベースにitemという名前のテーブルを作成します。プライマリキーをi_item_skに設定します。

データベースの作成db;
使用db;
テーブルアイテムを作成
(
  i_item_sk bigint NOT NULL、
  i_current_price bigint、
  PRIMARYキー (i_item_sk)
)
ハッシュによる配布 (i_item_sk);

CREATE TABLEステートメントを使用して、外部キーを追加します。
- 同じデータベースの外部キーを追加します。
  dbデータベースにstore_returnsという名前のテーブルを作成します。 FOREIGN KEY句を使用して、store_returnsテーブルの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、
  参照アイテム (i_item_sk) FOREIGN KEY (sr_item_sk)
); 
```
  説明
  symbolパラメーターが指定されていません。この場合、パーサーは外部キー制約の名前としてsr_item_sk_fkを自動的に使用します。
- 別のデータベースの外部キーを追加します。
  db2という名前のデータベースを作成し、データベースにstore_salesという名前のテーブルを作成してから、ss_item_skという名前の外部キー列を追加します。
```
データベースdb2を作成します。使用db2;
CREATE TABLE store_sales
(
  ss_sale_id bigint、
  ss_store_sk bigint、
  ss_item_sk bigint nullではない、
  CONSTRAINT fk_constraint FOREIGN KEY (ss_item_sk) REFERENCES db.item (i_item_sk)
); 
```

CREATE TABLEステートメントを使用して、同じテーブルに対して複数の外部キーを宣言する

例：

customerとvendorという名前の2つのテーブルを作成します。

使用db;
テーブルの顧客を作成する
(
  i_customer_sk bigint NOT NULL、
  i_current_price bigint、
  主要なキー (i_customer_sk)
)
ハッシュによる配布 (i_customer_sk);

CREATE TABLEベンダー
(
  id bigint主キー,
  名前varchar(5) not null
);

store_productという名前のテーブルを作成します。 store_productテーブルのsr_sale_id列をvendorテーブルの主キー列idに関連付けます。次に、store_productテーブルの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) 参照ベンダー (id) 、
  FOREIGN KEY (sr_customer_sk) REFERENCES customer (i_customer_sk)
); 
```

クエリ外部キー

外部キーを照会するには、SHOW CREATE TABLEステートメントを使用します。

例：

使用db;
SHOW CREATE TABLE store_returns;

サンプル結果:

-- Result (その他のステートメントは省略):
-- CONSTRAINT 'sr_item_sk_fk 'FOREIGN KEY ('sr_item_sk') 参照 'db'.'item'('i_item_sk ')

ALTER TABLEステートメントを使用して外部キーを追加または削除します

構文

構文の詳細については、「ALTER TABLE」をご参照ください。

例

次の例では、dbという名前のデータベースが使用されています。外部キーを追加するテーブルの名前はstore_returnsです。プライマリテーブルの名前はitemです。

store_returnsテーブルから外部キーを削除します。

使用db;
ALTER TABLE store_returns DROP FOREIGN KEY sr_item_sk_fk;

store_returnsテーブルの外部キーを追加します。

使用db;
ALTER TABLE store_returns ADD CONSTRAINT sr_item_fk FOREIGN KEY (sr_item_sk) REFERENCESアイテム (i_item_sk);

ALTER TABLEステートメントを使用して同じテーブルに対して複数の外部キーを宣言する

1つのALTER TABLEステートメントを使用して、一度にテーブルの外部キーを追加できます。テーブルに複数の外部キーを追加するには、複数のALTER tableステートメントを実行します。

例：

db2データベースにstoreという名前のテーブルを作成します。

使用db2;
テーブルストアの作成
(
  id bigint主キー,
  名前varchar(5) not null
);

dbデータベースのstore_returnsテーブルの外部キーを追加します。
```
ALTER TABLE db.store_returns ADD FOREIGN KEY (sr_store_sk) REFERENCES store(id);
```

結合削除ルールの適用

結合されたテーブルの1つのみからデータをクエリする場合は、主キーと外部キーの制約を使用して、不要な結合を排除し、実行計画を簡素化し、クエリのパフォーマンスを向上させることができます。結合の削除は、次のシナリオでよく使用されます。

同じデータベース内の2つのテーブルが結合されます

たとえば、store_returnsテーブルとitemテーブルで結合操作を実行した後に、store_returnsテーブルのデータをクエリします。

/* + PK_FK_DEPENDENCY_ENABLED = true * /
説明
選択
  s.sr_sale_id、
  s.sr_store_sk、
  s.sr_item_sk
から
  store_returns s,
  アイテム
どこ
  sr_item_sk = i_item_sk;

サンプル結果:

+ --------------
| プランの概要 |
+ --------------
 1-出力 [クエリプラン] {Est rowCount: 1.0}
 2 -> 交換 [ギャザー] {Est rowCount: 1.0}
 3 - TableScan {table: store_returns、Est rowCount: 1.0}

実行プランにINNER JOIN演算子が含まれていません。この場合、オプティマイザはsr_item_sk列とi_item_sk列の制約関係を使用して、store_returnsテーブルとitemテーブルの結合を排除できます。

2つのテーブルがデータベース間で結合されます

たとえば、dbデータベースのitemテーブルとdb2データベースのstore_salesテーブルに対して結合操作を実行した後、store_salesテーブルからデータをクエリします。

使用db2;
-- サンプルクエリ文
/* + PK_FK_DEPENDENCY_ENABLED = true * /
説明
選択
  s.ss_sale_id、
  s.ss_item_sk
から
  store_sales s、
  db.item
どこ
  ss_item_sk = i_item_sk;

サンプル結果:

+ --------------
| プランの概要 |
+ --------------
 1-出力 [クエリプラン] {Est rowCount: 1.0}
 2 -> 交換 [ギャザー] {Est rowCount: 1.0}
 3 - TableScan {table: store_sales、Est rowCount: 1.0}

複数のテーブルが結合されている

たとえば、store_returns、item、およびstoreテーブルで結合操作を実行した後に、store_returnsテーブルのデータをクエリします。

使用db;
-- サンプルクエリ文
/* + PK_FK_DEPENDENCY_ENABLED = true * /
説明
選択
  s.sr_sale_id、
  s.sr_store_sk、
  s.sr_item_sk
から
  store_returns s,
  アイテム、
  db2.store
どこ
  sr_item_sk = i_item_sk
  そしてsr_store_sk = id;

サンプル結果:

+ --------------
| プランの概要 |
+ --------------
 1-出力 [クエリプラン] {Est rowCount: 1.0}
 2 -> 交換 [ギャザー] {Est rowCount: 1.0}
 3 - TableScan {table: store_returns、Est rowCount: 1.0}

結合されたテーブルに基づいてビューが作成されます

ビューは、結合される2つ以上のテーブルに基づいて作成することができる。ビューに関する特定の情報を参照しない別のクエリ文でビューを使用する場合、オプティマイザは不要な結合を排除できます。たとえば、store_returnsおよびitemテーブルに関する情報を含むビューを作成します。

ビューの作成sr_item_v AS
選択
  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をitem_idとして
から
  store_returns s,
  アイテム
どこ
  sr_item_sk = i_item_sk;

クエリがitemテーブルのiteme_price列を参照しない場合、オプティマイザはitemテーブルではなく、store_returnsテーブルのみをスキャンできます。

/* + PK_FK_DEPENDENCY_ENABLED = true * /
説明
SELECT store_name, sr_item_id, sale_id
からsr_item_v;

サンプル結果:

+ --------------
| プランの概要 |
+ --------------
 1-出力 [クエリプラン] {Est rowCount: 1.0}
 2 -> 交換 [ギャザー] {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テーブルの列からデータを照会し、不要な結合を排除できます。

例外的な結合削除

上記のビューの例では、クエリがitemテーブルの列を参照する場合、結合を削除することはできません。

-- サンプルクエリ文
/* + PK_FK_DEPENDENCY_ENABLED = true * /
説明
SELECT store_name, sr_item_id, sale_id, item_price
からsr_item_v;

サンプル結果:

+ --------------
| プランの概要 |
+ --------------
 1-出力 [クエリプラン] {Est rowCount: 1.0}
 2 -> 交換 [ギャザー] {Est rowCount: 1.0}
 3 -> InnerJoin [ハッシュ参加] {Est rowCount: 1.0}
 4 -> プロジェクト {Est rowCount: 1.0} 
 5 -> 交換 [REPARTITION] {Est rowCount: 1.0}
 6 - TableScan {table: store_returns、Est rowCount: 1.0} 
 7 -> LocalExchange [ハッシュ] {Est rowCount: 1.0}
 8 -> ScanProject {table: item, Est rowCount: 1.0} 
 9 - TableScan {table: item, Est rowCount: 1.0}