🔑

BigQuery クエリ性能改善: BigQuery の主キーと外部キーにより、クエリの性能改善が得られるかどうか検証してみた

2023/09/29に公開

Google Cloud

BigQuery

tech

こんにちは、クラウドエース Data/ML ディビジョン所属の田中です。

2023 年 7 月 17 日に、BigQuery の主キーと外部キーが Generally Available になりました。(リリースノート)
しかし、BigQuery の主キーと外部キーの仕様についてはあまり詳しく記載されていなかったので、いくつか検証してみました。
各検証は別記事に記載し、本記事ではまとめのみ記載しています。

以降、主キーを PK (Primary key)、外部キーを FK (Foreign key) と略すことがあります。

1. BigQuery の主キーと外部キーとは

主キーと外部キーは、一般的な RDB ですと、データの整合性を確保するために使用されます。しかし、BigQuery では主キーと外部キーの制約は強制されません。

ですが、BigQuery のテーブルに主キーと外部キーを設定すると、BigQuery の主キーと外部キーで結合を最適化に記載されている通り、結合を行うクエリの性能改善が期待できます。

2. 検証一覧

以下は、実施した検証の詳細を記載した記事です。

上記検証では、主に BigQuery public datasets の wikipedia テーブルのデータを利用しています。

3. まとめ

PK / FK は、結合を行うテーブル同士に設定し、結合を含むクエリの処理速度を向上させるために利用するのが良いと思いました。
ただし、使用する際は、以降に記載している内容や公式ドキュメント TABLE_CONSTRAINTS ビューに記載されている制限事項を考慮する必要がありそうです。

3.1. PK / FK による効果

BigQuery の主キーと外部キーで結合を最適化に記載されている通り、以下によって結合を行うクエリの性能改善が期待できます。
- 内部結合解除
- 外部結合解除
- 結合順序変更

内部結合解除、外部結合解除は 3.2 節で解説します。
結合順序変更については、後日、別記事で記載予定です。

3.2. PK / FK による内部結合解除、外部結合解除について

3.2.1. 結合解除されたかどうかの確認方法

結合解除とは、テーブルの結合を行う SQL を実行したとき、クエリオプティマイザーが結合を不要と判断し、内部的に無駄な結合を排除することを指します。
結合解除されると、GUI 上の実行グラフ内からJoinステージが無くなります。
- 結合解除なし
- 結合解除あり

3.2.2. 結合解除が起きる条件

内部結合の場合、以下のとき結合解除されます。
- 「FK テーブルのカラムのみ取得」
左外部結合の場合、以下のとき結合解除されます。
- 「左側に FK、右側に PK」 and 「FK テーブルのカラムのみ取得」
右外部結合の場合、以下のとき結合解除されます。
- 「左側に PK、右側に FK」 and 「FK テーブルのカラムのみ取得」
結合解除と、結合方法・PK / FK テーブルの位置・PK / FK テーブルのカラムの取得の関係は、次の表の通りです。

結合方法	左テーブル	右テーブル	PK テーブルのカラムを取得 (o: あり、 -: なし)	FK テーブルのカラムを取得 (o: あり、 -: なし)	結合解除 (o: あり、 -: なし)
内部結合	PK テーブル	FK テーブル	o	-	-
	PK テーブル	FK テーブル	-	o	o
	FK テーブル	PK テーブル	o	-	-
	FK テーブル	PK テーブル	-	o	o
左外部結合	PK テーブル	FK テーブル	o	-	-
	PK テーブル	FK テーブル	-	o	-
	FK テーブル	PK テーブル	o	-	-
	FK テーブル	PK テーブル	-	o	o
右外部結合	PK テーブル	FK テーブル	o	-	-
	PK テーブル	FK テーブル	-	o	o
	FK テーブル	PK テーブル	o	-	-
	FK テーブル	PK テーブル	-	o	-

3.2.3. 結合が誤った結果になる、PK / FK の状態

PK / FK が以下の場合でも、結合解除は起きます。ただし、(a). PK で、値が重複している、(c). FK が、PK に無い値を含む場合、誤った結果になりました (PK / FK を設定していない通常テーブルの結果と一致しません)。
- (a). PK で、値が重複している
- (b). PK が NULL を含む
- (c). FK が、PK に無い値を含む

3.2.4. Foreign key カラムで GROUP BY した後、この Foreign key と Primary key で結合

外部結合解除は、発生可能でした。
内部結合解除は、発生しませんでした。

3.3. 結合解除されないときの PK / FK の効果について

次の表は、各検証の結合解除されないときの PK / FK の効果確認で取得した測定結果をまとめたものです。

表内の PK 値ごとにおけるFKのレコード数 (平均) 列は、下記 SQL のテーブル1とカラム1、テーブル2、カラム2をそれぞれ各検証で用意した PK テーブルと PK カラム、FK テーブル、FK カラムに置き換えて求めた数値です。

WITH
  -- カラム1の種類を取得
  pk_types AS (
  SELECT
    DISTINCT `カラム1` AS pk
  FROM
    `テーブル1` ),
  -- カラム2の値ごとにレコード数をカウント
  fk_count AS (
  SELECT
    `カラム2` AS fk,
    COUNT(*) AS fk_count
  FROM
    `テーブル2`
  GROUP BY
    `カラム2` ),
  -- カラム1に存在する値のみに絞り込む
  count_by_pk_types AS (
  SELECT
    *
  FROM
    fk_count
  INNER JOIN
    pk_types
  ON
    fk_count.fk = pk_types.pk)
-- レコード数の平均
SELECT
  AVG(fk_count)
FROM
  count_by_pk_types

PK 値ごとにおけるFKのレコード数 (平均)が大きい#7~10のとき、平均経過時間 (s)が通常テーブルと比較してPK / FK テーブルの方が小さくなっています。
#7~10の中だと、内部結合の時の方が、左外部結合よりPK / FK テーブルの平均経過時間 (s)が小さいです。
上記から次のことが考えられます。
- 「PK値ごとにおけるFKのレコード数」が大きいと、PK / FK の恩恵を受けて、クエリの処理速度が速くなりやすい。
- 内部結合の方が、外部結合よりも PK / FK の恩恵を受けやすい。

3.4. PK / FK を既存テーブルに設定できる

テーブル作成後に PK / FK を設定しても PK / FK の効果は発揮されました。

3.5. 主キーを設定可能なデータ型について

公式ドキュメント TABLE_CONSTRAINTS ビューには、以下のような記載があります。

主キーと外部キーの値の制約は適用されません。ユーザーは、各制約に値が一致することを確認する必要があります。そうしないと、誤った結果が生じる可能性があります。具体的な内容は以下のとおりです。

(中略)

主キーと外部キーは、BIGNUMERIC、BOOLEAN、DATE、DATETIME、INT64、NUMERIC、STRING、TIMESTAMP のいずれかのタイプにする必要があります。

上記以外の下記データ型に対して、主キーを設定しようとすると、BigQuery がエラーを発生させました。
- BYTES
- FLOAT64
- GEOGRAPHY
- INTERVAL
- JSON
ただし、次のカラムに対して PK / FK を設定可能でした。PK / FK の効果も発揮されました。
- TIME

3.6. 主キーを設定できる列数について

16 列まで設定可能です。
公式ドキュメントTABLE_CONSTRAINTS ビューには、以下のような記載がありますが、17 列以上設定しようとすると、BigQuery がエラーを発生させました。

主キーと外部キーの値の制約は適用されません。ユーザーは、各制約に値が一致することを確認する必要があります。そうしないと、誤った結果が生じる可能性があります。具体的な内容は以下のとおりです。

(中略)

主キーは 16 列を超えることはできません。

3.7. 1 つのカラムに設定できる外部キーの数について

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

3.8. Primary key の設定、追加、削除、変更方法

設定方法
- CREATE TABLE ステートメントを使用する。
追加方法
- ALTER TABLE ADD PRIMARY KEY ステートメントを使用する。
削除方法
- ALTER TABLE DROP PRIMARY KEY ステートメントを使用する。
- ALTER TABLE DROP CONSTRAINT ステートメントを使用する。
変更方法
- ALTER TABLE DROP PRIMARY KEY ステートメントと ALTER TABLE ADD PRIMARY KEY ステートメントを使用する。
- テーブルを再作成する。

3.9. Foreign key の設定、追加、削除、変更方法

設定方法
- CREATE TABLE ステートメントを使用する。
追加方法
- ALTER TABLE ADD FOREIGN KEY ステートメントを使用する。
削除方法
- ALTER TABLE DROP CONSTRAINT ステートメントを使用する。
変更方法
- ALTER TABLE DROP CONSTRAINT ステートメントと ALTER TABLE ADD FOREIGN KEY ステートメントを使用する。
- テーブルを再作成する。

付録: クエリの経過時間・消費したスロット時間を複数回測定するために使用したSQL

各検証で結合解除されないときの PK / FK の効果を複数回測定する際、下記 SQL の /* 測定対象クエリ */ 部分を書き換えて使用していました。

/* 定数 */
-- 合計測定回数
DECLARE NUMBER INT64 DEFAULT 10;

/* 変数 */
-- 現在の測定回数
DECLARE cnt INT64 DEFAULT 1;

/* 結果一時保存用テーブル */
CREATE OR REPLACE TEMP TABLE result (
    number INT64,
    elapsed_seconds FLOAT64,
    total_slot_seconds FLOAT64,
);

/* 測定開始 */
WHILE cnt <= NUMBER DO
    /* 測定対象クエリ */
    -- ここに測定したいクエリを書き込む
    -- 例えば下記のように
    SELECT
        *
    FROM
       bigquery-public-data.new_york_taxi_trips.taxi_zone_geom

    ;
    /* 結果取得 */
    INSERT result
    SELECT
        cnt,
        TIMESTAMP_DIFF(end_time, start_time, SECOND) AS elapsed_seconds,
        total_slot_ms / 1000 AS total_slot_seconds, 
    FROM
        `region-us`.INFORMATION_SCHEMA.JOBS
    ORDER BY
        creation_time DESC
    LIMIT
        1;

    SET cnt = cnt + 1;
END WHILE;

/* 結果表示 */
SELECT
    *
FROM
    result
ORDER BY
    number