Zenn
🔑

BigQuery クエリ性能改善: 1 つのカラムに複数の Foreign key を設定したときの確認

2023/09/29に公開
Google Cloud
BigQuery
tech

本記事は、BigQuery の主キーと外部キーにより、クエリの性能改善が得られるかどうか検証してみたの検証の一部になります。
ここでは、1 つのカラムに複数の Foreign key を設定したとき、PK / FK の効果を受けることができるのかを確認します。

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

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

  • normal1
  • normal2
  • normal3
  • pk1
  • pk2
  • fk1

normal1 と normal2、normal3 は通常テーブルです。
pk1 と pk2、fk1 は、それぞれ revision_id カラムを Primary key / Primary key / Foreign key として設定します。

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

-- 通常テーブル作成
CREATE OR REPLACE TABLE pk_fk_dataset.normal1
(
    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.normal2
CLONE pk_fk_dataset.normal1;

CREATE OR REPLACE TABLE
pk_fk_dataset.normal3
CLONE pk_fk_dataset.normal1;

-- Primary key を設定したテーブルを作成
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,
    PRIMARY KEY(revision_id) NOT ENFORCED
) AS (
     SELECT
         *
     FROM
         bigquery-public-data.samples.wikipedia
);

-- Primary key を設定したテーブルを作成
CREATE OR REPLACE TABLE
pk_fk_dataset.pk2
CLONE pk_fk_dataset.pk1;

-- Foreign key を設定したテーブルを作成
CREATE OR REPLACE TABLE pk_fk_dataset.fk1
(
    title STRING NOT NULL,
    id INTEGER,
    language STRING NOT NULL,
    wp_namespace INTEGER NOT NULL,
    is_redirect BOOLEAN,
    revision_id INTEGER REFERENCES pk_fk_dataset.pk1(revision_id) NOT ENFORCED,
    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
);

ALTER table pk_fk_dataset.fk1 ADD FOREIGN KEY(revision_id) references pk_fk_dataset.pk2(revision_id) NOT ENFORCED;






2. 結合の検証

内部結合・左外部結合・右外部結合で結合解除される条件の確認で確認した内部結合・外部結合で結合解除される以下の場合と、結合解除されないときの PK / FK の効果を確認します。

  • 内部結合 (左: FK, 右: PK) and FK テーブルのカラムのみ取得
  • 左外部結合 (左: FK, 右: PK) and FK テーブルのカラムのみ取得

2.1. 内部結合 (左: FK, 右: PK) and FK テーブルのカラムのみ取得

PK / FK テーブルでの結果 (行数: 313,797,035) は、通常テーブルの結果 (行数: 313,797,035) と同じでした。
また、結合解除も発生しました。

通常テーブル

SELECT
    n1.revision_id
FROM
    pk_fk_dataset.normal1 n1
INNER JOIN
    pk_fk_dataset.normal2 n2
ON
    n1.revision_id = n2.revision_id
INNER JOIN
    pk_fk_dataset.normal3 n3
ON
    n1.revision_id = n3.revision_id

PK / FK テーブル

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

2.2. 左外部結合 (左: FK, 右: PK) and FK テーブルのカラムのみ取得

PK / FK テーブルでの結果 (行数: 313,797,035) は、通常テーブルの結果 (行数: 313,797,035) と同じでした。
また、結合解除も発生しました。

通常テーブル

SELECT
    n1.revision_id
FROM
    pk_fk_dataset.normal1 n1
LEFT JOIN
    pk_fk_dataset.normal2 n2
ON
    n1.revision_id = n2.revision_id
LEFT JOIN
    pk_fk_dataset.normal3 n3
ON
    n1.revision_id = n3.revision_id

PK / FK テーブル

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

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

normal1 (fk1) と normal2 (pk1)、normal3 (pk2) のカラムを取得するように 2.1. 内部部結合 (左: FK, 右: PK) and FK テーブルのカラムのみ取得の SQL を変更することで、結合解除されないようにし、PK / FK により結合性能が改善されるか確認します。

通常テーブル

SELECT
    n1.revision_id,
    n2.revision_id,
    n3.revision_id
FROM
    pk_fk_dataset.normal1 n1
INNER JOIN
    pk_fk_dataset.normal2 n2
ON
    n1.revision_id = n2.revision_id
INNER JOIN
    pk_fk_dataset.normal3 n3
ON
    n1.revision_id = n3.revision_id

PK / FK テーブル

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

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

通常テーブル

# 経過時間 (s) 消費したスロット時間 (s)
1 20 3420
2 18 3121
3 19 3048
4 19 2888
5 18 2953
6 17 3083
7 18 3028
8 17 2910
9 18 3043
10 21 3232
平均 18.5 3073

PK / FK テーブル

# 経過時間 (s) 消費したスロット時間 (s)
1 18 3381
2 18 3281
3 20 3027
4 22 3298
5 22 3305
6 24 3237
7 21 3582
8 19 3546
9 22 3507
10 21 3415
平均 20.7 3358

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

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

normal1 (fk1) と normal2 (pk1)、 normal3 (pk2) のカラムを取得するように 2.2. 左外部結合 (左: FK, 右: PK) and FK テーブルのカラムのみ取得の SQL を変更することで、結合解除されないようにし、PK / FK　により結合性能が改善されるか確認します。

通常テーブル

SELECT
    n1.revision_id,
    n2.revision_id,
    n3.revision_id
FROM
    pk_fk_dataset.normal1 n1
LEFT JOIN
    pk_fk_dataset.normal2 n2
ON
    n1.revision_id = n2.revision_id
LEFT JOIN
    pk_fk_dataset.normal3 n3
ON
    n1.revision_id = n3.revision_id

PK / FK テーブル

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

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

通常テーブル

# 経過時間 (s) 消費したスロット時間 (s)
1 22 3759
2 23 3592
3 20 3375
4 22 3302
5 20 3489
6 19 3165
7 22 3247
8 23 3154
9 22 3180
10 23 3267
平均 22 3353

PK / FK テーブル

# 経過時間 (s) 消費したスロット時間 (s)
1 21 3567
2 19 3175
3 22 3620
4 21 3662
5 27 3198
6 21 3332
7 22 3420
8 22 3209
9 26 3787
10 19 3765
平均 22 3473

今回の場合、PK / FK テーブルは、通常テーブルのクエリの処理速度と同じでした。

3. まとめ

1 つのカラムに、複数の Foreign key を設定できました。PK / FK の効果も発揮されました。

Discussion