🔑

BigQuery クエリ性能改善: Primary key に NULL が含まれるときの確認

2023/09/29に公開

本記事は、BigQuery の主キーと外部キーにより、クエリの性能改善が得られるかどうか検証してみたの検証の一部になります。
ここでは、Primary key に NULL が含まれているとき、正しい結果になるのか・PK / FK の効果を受けることができるのかを確認します。

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

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

  • normal1
  • normal2
  • pk1
  • fk1

normal1normal2 は通常テーブルです。
pk1fk1 は、それぞれ revision_id カラムを Primary key / Foreign key として設定します。
normal1pk1 には、revision_id カラムが NULL であるレコードを 1 つ追加します。

-- データセット作成
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;

-- revision_id カラムが NULL であるレコードを 1 つ normal1 テーブルに追加
INSERT pk_fk_dataset.normal1
SELECT
    title,
    id,
    language,
    wp_namespace,
    is_redirect,
    NULL as revision_id,
    contributor_ip,
    contributor_id,
    contributor_username,
    timestamp,
    is_minor,
    is_bot,
    reversion_id,
    comment,
    num_characters
FROM
    bigquery-public-data.samples.wikipedia
LIMIT
    1;

-- 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
         pk_fk_dataset.normal1
);

-- 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
         pk_fk_dataset.normal2
);




2. 結合の検証

2.1. 内部結合 (左: PK(NULL含む), 右: 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

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(NULL含む)) で結合解除される場合

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

通常テーブル

SELECT
    n2.revision_id
FROM
    pk_fk_dataset.normal2 n2
INNER JOIN
    pk_fk_dataset.normal1 n1
ON
    n2.revision_id = n1.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

2.3. 左外部結合 (左: PK(NULL含む), 右: FK) で結合解除されない場合

PK / FK テーブルでの結果 (行数: 313,797,036) は、通常テーブルの結果 (行数: 313,797,036) と同じでした。

通常テーブル

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

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(NULL含む)) で結合解除される場合

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

通常テーブル

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

Foreign key と Primary key テーブル

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 の効果確認 (内部結合)

normal1 (pk1) と normal2 (fk1) 両方のカラムを取得するように、2.2. 内部結合 (左: FK, 右: PK(NULL含む)) で結合解除される場合の SQL を変更することで、結合解除されないようにし、PK / FK により結合性能が改善されるか確認します。

通常テーブル

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

PK / FK テーブル

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

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

通常テーブル

# 経過時間 (s) 消費したスロット時間 (s)
1 13 2031
2 14 2275
3 12 2098
4 12 1806
5 15 1875
6 13 2029
7 22 1963
8 13 2102
9 35 2894
10 14 1855
平均 16.3 2093

PK / FK テーブル

# 経過時間 (s) 消費したスロット時間 (s)
1 15 2256
2 16 2015
3 17 2019
4 21 2640
5 15 1793
6 13 2026
7 13 1919
8 15 2148
9 13 1816
10 13 1976
平均 15.1 2061

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

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

normal1 (pk1) と normal2 (fk1) 両方のカラムを取得するように、2.4. 左外部結合 (左: FK, 右: PK(NULL含む)) で結合解除される場合の SQL を変更することで、結合解除されないようにし、PK / FK により結合性能が改善されるか確認します。

通常テーブル

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

PK / FK テーブル

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

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

通常テーブル

# 経過時間 (s) 消費したスロット時間 (s)
1 13 1846
2 15 1887
3 12 2032
4 13 1956
5 14 1884
6 14 1958
7 17 2504
8 15 1948
9 15 2042
10 13 2403
平均 14.1 2046

PK / FK テーブル

# 経過時間 (s) 消費したスロット時間 (s)
1 13 2038
2 14 2050
3 13 2028
4 15 2165
5 16 2025
6 14 2009
7 14 1907
8 14 1945
9 18 1939
10 12 1810
平均 14.3 1992

今回の場合、PK / FK テーブルは、通常テーブルよりもクエリの処理速度が遅くなりました。(0.2秒の差で、ほぼ同じ)

3. まとめ

PK に NULL が含まれる場合でも、結合解除は起きました。また、結合解除が起きる結合を行なった場合でも、PK / FK テーブルは通常テーブルの結果と一致していました。

Discussion