BigQuery クエリ性能改善: Foreign key が Primary key に無い値を含むときの確認
本記事は、BigQuery の主キーと外部キーにより、クエリの性能改善が得られるかどうか検証してみたの検証の一部になります。
ここでは、Foreign key が Primary key に無い値を含むとき、正しい結果になるのか・PK / FK の効果を受けることができるのかを確認します。
1. データセット・テーブルの準備
BigQuery public datasets の wikipedia テーブルから、以下 4 つのテーブルを用意します。
- normal1
- normal2
- pk1
- fk1
normal1 と normal2 は通常テーブルです。
pk1 と fk1 は、それぞれ revision_id カラムを Primary key / Foreign key として設定します。
normal2 と fk1 には、revision_id カラムが normal1 (pk1) に無い値になっているレコードを 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 カラムが normal1 テーブルに無い値になっているレコードを 1 つ normal2 テーブルに追加
INSERT pk_fk_dataset.normal2
SELECT
title,
id,
language,
wp_namespace,
is_redirect,
923372036854775807 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, 右: 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) で結合解除される場合
結合解除により、PK / FK テーブルでの結果 (行数: 313,797,036) が、通常テーブルの結果 (行数: 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, 右: 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
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) で結合解除される場合
PK / FK テーブルでの結果 (行数: 313,797,036) は、通常テーブルの結果 (行数: 313,797,036) と同じでした。
通常テーブル
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) で結合解除される場合の 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 | 23 | 1766 |
| 2 | 13 | 1701 |
| 3 | 14 | 1696 |
| 4 | 24 | 1457 |
| 5 | 14 | 1596 |
| 6 | 15 | 2203 |
| 7 | 15 | 2350 |
| 8 | 13 | 2014 |
| 9 | 13 | 2142 |
| 10 | 16 | 2016 |
| 平均 | 16 | 1643 |
PK / FK テーブル
| # | 経過時間 (s) | 消費したスロット時間 (s) |
|---|---|---|
| 1 | 33 | 2254 |
| 2 | 15 | 2127 |
| 3 | 20 | 2629 |
| 4 | 17 | 2434 |
| 5 | 15 | 2444 |
| 6 | 14 | 1934 |
| 7 | 14 | 2103 |
| 8 | 21 | 2134 |
| 9 | 13 | 2249 |
| 10 | 15 | 2116 |
| 平均 | 17.7 | 2242 |
今回の場合、PK / FK テーブルは、通常テーブルよりもクエリの処理速度が遅くなりました。
2.6. 結合解除されないときの PK / FK の効果確認 (左外部結合)
normal1 (pk1) と normal2 (fk1) 両方のカラムを取得するように 2.4. 左外部結合 (左: FK, 右: PK) で結合解除される場合の 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 | 14 | 1873 |
| 2 | 16 | 1942 |
| 3 | 16 | 2155 |
| 4 | 15 | 2445 |
| 5 | 17 | 2219 |
| 6 | 16 | 2038 |
| 7 | 13 | 2248 |
| 8 | 16 | 2257 |
| 9 | 14 | 2289 |
| 10 | 16 | 2243 |
| 平均 | 15.3 | 2127 |
PK / FK テーブル
| # | 経過時間 (s) | 消費したスロット時間 (s) |
|---|---|---|
| 1 | 13 | 2177 |
| 2 | 15 | 2168 |
| 3 | 13 | 2209 |
| 4 | 15 | 2081 |
| 5 | 16 | 1881 |
| 6 | 15 | 2170 |
| 7 | 26 | 1911 |
| 8 | 14 | 2046 |
| 9 | 15 | 2119 |
| 10 | 15 | 2032 |
| 平均 | 15.7 | 2079 |
今回の場合、PK / FK テーブルは、通常テーブルよりもクエリの処理速度が遅くなりました。(0.4秒差なので、ほぼ同じ)
3. まとめ
FK が PK に無い値を含むの場合でも、結合解除は起きました。ただし、結合解除が起きる結合を行なった場合、誤った結果になりました (PK / FK を設定していない通常テーブルの結果と一致しませんでした)。
Discussion