🔑

BigQuery クエリ性能改善: Primary key と Foreign key をテーブル作成後に設定したときの確認

2023/09/29に公開

本記事は、BigQuery の主キーと外部キーにより、クエリの性能改善が得られるかどうか検証してみたの検証の一部になります。
ここでは、テーブルを作成した後に Primary key と Foreign key を設定し、既存テーブルも PK / FK の効果を受けることができるのかを確認します。

1. データセット・テーブルの準備

BigQuery public datasets の wikipedia テーブルから、以下 2 つのテーブルを用意します。

このとき、pk1 と fk1 には Primary key / Foreign key をまだ設定しません。後ほど設定します。

-- データセット作成
CREATE SCHEMA IF NOT EXISTS pk_fk_dataset;

-- 通常テーブル作成
CREATE OR REPLACE TABLE pk_fk_dataset.pk1
(
    title STRING NOT NULL,
    id INTEGER,
    language STRING NOT NULL,
    wp_namespace INTEGER NOT NULL,
    is_redirect BOOLEAN,
    revision_id INTEGER,
    contributor_ip STRING,
    contributor_id INTEGER,
    contributor_username STRING,
    timestamp INTEGER NOT NULL,
    is_minor BOOLEAN,
    is_bot BOOLEAN,
    reversion_id INTEGER,
    comment STRING,
    num_characters INTEGER NOT NULL,
) AS (
     SELECT
         *
     FROM
         bigquery-public-data.samples.wikipedia
);

CREATE OR REPLACE TABLE
pk_fk_dataset.fk1
CLONE pk_fk_dataset.pk1;

INSERT pk_fk_dataset.fk1
SELECT
    *
FROM
    bigquery-public-data.samples.wikipedia;

2. 通常テーブルでの結合の検証

まず、PK / FK を設定していない通常のテーブルに対して SQL を実行します。

2.1. 内部結合 (左: PK, 右: FK)

SELECT
    pk1.revision_id
FROM
    pk_fk_dataset.pk1
INNER JOIN
    pk_fk_dataset.fk1
ON
    pk1.revision_id = fk1.revision_id

2.2. 内部結合 (左: FK, 右: PK)

SELECT
    fk1.revision_id
FROM
    pk_fk_dataset.fk1
INNER JOIN
    pk_fk_dataset.pk1
ON
    fk1.revision_id = pk1.revision_id

2.3. 左外部結合 (左: PK, 右: FK)

SELECT
    pk1.revision_id
FROM
    pk_fk_dataset.pk1
LEFT JOIN
    pk_fk_dataset.fk1
ON
    pk1.revision_id = fk1.revision_id

2.4. 左外部結合 (左: FK, 右: PK)

SELECT
    fk1.revision_id
FROM
    pk_fk_dataset.fk1
LEFT JOIN
    pk_fk_dataset.pk1
ON
    fk1.revision_id = pk1.revision_id

2.5. 結合解除されないときの PK / FK の効果確認 (内部結合)

pk1 と fk1 両方のカラムを取得するように 2.2. 内部結合 (左: FK, 右: PK) の SQL を変更することで、結合解除されないようにします。

SELECT
    fk1.revision_id,
    pk1.revision_id
FROM
    pk_fk_dataset.fk1
INNER JOIN
    pk_fk_dataset.pk1
ON
    fk1.revision_id = pk1.revision_id

2.6. 結合解除されないときの PK / FK の効果確認 (左外部結合)

pk1 と fk1 両方のカラムを取得するように 2.4. 左外部結合 (左: FK, 右: PK) の SQL を変更することで、結合解除されないようにします。

SELECT
    fk1.revision_id,
    pk1.revision_id
FROM
    pk_fk_dataset.fk1
LEFT JOIN
    pk_fk_dataset.pk1
ON
    fk1.revision_id = pk1.revision_id

3. Primary key と Foreign key を設定

Primary key と Foreign key を設定します。

ALTER table pk_fk_dataset.pk1 ADD PRIMARY KEY(revision_id) NOT ENFORCED;
ALTER table pk_fk_dataset.fk1 ADD FOREIGN KEY(revision_id) REFERENCES pk_fk_dataset.pk1(revision_id) NOT ENFORCED;

4. PK / FK テーブルでの結合の検証

次に Primary key / Foreign key を設定したテーブルに対して SQL を実行して、PK / FK の効果を受けることができるのか確認します。

4.1. 内部結合 (左: PK, 右: FK)

実行する SQL は 2.1. 内部結合 (左: PK, 右: FK) と同じです。

4.2. 内部結合 (左: FK, 右: PK)

実行する SQL は 2.2. 内部結合 (左: FK, 右: PK) と同じです。

PK / FK により結合解除が起きていることを確認できます。

4.3. 左外部結合 (左: PK, 右: FK)

実行する SQL は 2.3. 左外部結合 (左: PK, 右: FK) と同じです。

4.4. 左外部結合 (左: FK, 右: PK)

実行する SQL は 2.4. 左外部結合 (左: FK, 右: PK) と同じです。

PK / FK により結合解除が起きていることを確認できます。

4.5. 結合解除されないときの PK / FK の効果確認 (内部結合)

PK / FK により結合性能が改善されるか確認します。
実行する SQL は 2.5. 結合解除されないときの PK / FK の効果確認 (内部結合) と同じです。

通常テーブルと PK / FK テーブルそれぞれ 10 回実行した結果は以下のようになりました。

通常テーブル

#	経過時間 (s)	消費したスロット時間 (s)
1	20	3842
2	25	3894
3	25	3934
4	20	3547
5	21	4158
6	16	3679
7	19	3231
8	21	3142
9	17	4481
10	21	3200
平均	20.5	3875

PK / FK テーブル

#	経過時間 (s)	消費したスロット時間 (s)
1	19	3179
2	17	3534
3	19	3202
4	18	3012
5	16	3551
6	19	3557
7	16	3513
8	20	3603
9	16	3245
10	18	2953
平均	17.8	3335

今回の場合、PK / FK テーブルは、通常テーブルよりもクエリの処理速度が速くなりました。

4.6. 結合解除されないときの PK / FK の効果確認 (左外部結合)

PK / FK により結合性能が改善されるか確認します。
実行する SQL は 2.6. 結合解除されないときの PK / FK の効果確認 (左外部結合) と同じです。