このトピックでは、主キーと外部キー間の制約関係を使用して、不要な結合を排除し、実行計画を最適化する方法について説明します。
前提条件
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}