BigQuery クエリ性能改善: Primary key で値が重複しているときの確認
本記事は、BigQuery の主キーと外部キーにより、クエリの性能改善が得られるかどうか検証してみたの検証の一部になります。
ここでは、Primary key で値が重複しているとき、正しい結果になるのか・PK / FK の効果を受けることができるのかを確認します。
1. データセット・テーブルの準備
BigQuery public datasets の wikipedia テーブルから、以下 4 つのテーブルを用意します。
- normal1
- normal2
- pk1
- fk1
normal1 と normal2 は通常テーブルです。
pk1 では、値が重複している id カラムを Primary key として設定し、
fk1 では、値が一意である revision_id カラムを Foreign key を設定します。(※ id と revision_id の間には何の関係もありません)
-- データセット作成
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;
-- 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(id) NOT ENFORCED
) AS (
SELECT
*
FROM
bigquery-public-data.samples.wikipedia
);
-- 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(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
);
id カラムが重複していることの確認
SELECT
id,
COUNT(id) AS count
FROM
bigquery-public-data.samples.wikipedia
GROUP BY
id
HAVING
count > 1
revision_id カラムが一意であることの確認
SELECT
revision_id,
COUNT(revision_id) AS count
FROM
bigquery-public-data.samples.wikipedia
GROUP BY
revision_id
HAVING
count > 1
2. 結合の検証
2.1. 内部結合 (左: PK(重複), 右: FK(一意)) で結合解除されない場合
PK / FK テーブルでの結果 (行数: 287,135,569) は、通常テーブルの結果 (行数: 287,135,569) と同じでした。
通常テーブル
SELECT
n1.id
FROM
pk_fk_dataset.normal1 n1
INNER JOIN
pk_fk_dataset.normal2 n2
ON
n1.id = n2.revision_id
PK / FK テーブル
SELECT
pk1.id
FROM
pk_fk_dataset.pk1
INNER JOIN
pk_fk_dataset.fk1
ON
pk1.id = fk1.revision_id
2.2. 内部結合 (左: FK(一意), 右: PK(重複)) で結合解除される場合
結合解除により、PK / FK テーブルでの結果 (行数: 313,797,035) が、通常テーブルの結果 (行数: 287,135,569) と異なってしまいました。
通常テーブル
SELECT
n1.revision_id
FROM
pk_fk_dataset.normal1 n1
INNER JOIN
pk_fk_dataset.normal2 n2
ON
n1.revision_id = n2.id
PK / FK テーブル
SELECT
fk1.revision_id
FROM
pk_fk_dataset.fk1
INNER JOIN
pk_fk_dataset.pk1
ON
fk1.revision_id = pk1.id
2.3. 左外部結合 (左: PK(重複), 右: FK(一意)) で結合解除されない場合
PK / FK テーブルでの結果 (行数: 287,135,569) は、通常テーブルの結果 (行数: 287,135,569) と同じでした。
通常テーブル
SELECT
n1.id
FROM
pk_fk_dataset.normal1 n1
LEFT JOIN
pk_fk_dataset.normal2 n2
ON
n1.id = n2.revision_id
PK / FK テーブル
SELECT
pk1.id
FROM
pk_fk_dataset.pk1
LEFT JOIN
pk_fk_dataset.fk1
ON
pk1.id = fk1.revision_id
2.4. 左外部結合 (左: FK(一意), 右: PK(重複)) で結合解除される場合
結合解除により、PK / FK テーブルでの結果 (行数: 313,797,035) が、通常テーブルの結果 (行数: 583,571,457) と異なってしまいました。
通常テーブル
SELECT
n1.revision_id
FROM
pk_fk_dataset.normal1 n1
LEFT JOIN
pk_fk_dataset.normal2 n2
ON
n1.revision_id = n2.id
PK / FK テーブル
SELECT
fk1.revision_id
FROM
pk_fk_dataset.fk1
LEFT JOIN
pk_fk_dataset.pk1
ON
fk1.revision_id = pk1.id
2.5. 結合解除されないときの PK / FK の効果確認 (内部結合)
normal1 (pk1) と normal2 (fk1) 両方のカラムを取得するように、2.2. 内部結合 (左: FK(一意), 右: PK(重複)) で結合解除される場合の SQL を変更することで、結合解除されないようにし、PK / FK により結合性能が改善されるか確認します。
通常テーブル
SELECT
n1.revision_id,
n2.id
FROM
pk_fk_dataset.normal1 n1
INNER JOIN
pk_fk_dataset.normal2 n2
ON
n1.revision_id = n2.id
PK / FK テーブル
SELECT
fk1.revision_id,
pk1.id
FROM
pk_fk_dataset.fk1
INNER JOIN
pk_fk_dataset.pk1
ON
fk1.revision_id = pk1.id
通常テーブルと PK / FK テーブルそれぞれ 10 回実行した結果は以下のようになりました。
通常テーブル
| # | 経過時間 (s) | 消費したスロット時間 (s) |
|---|---|---|
| 1 | 13 | 1708 |
| 2 | 12 | 1510 |
| 3 | 12 | 1214 |
| 4 | 13 | 1383 |
| 5 | 14 | 1393 |
| 6 | 12 | 1483 |
| 7 | 11 | 1371 |
| 8 | 14 | 1394 |
| 9 | 14 | 1403 |
| 10 | 14 | 1434 |
| 平均 | 12.9 | 1429 |
PK / FK テーブル
| # | 経過時間 (s) | 消費したスロット時間 (s) |
|---|---|---|
| 1 | 13 | 1644 |
| 2 | 12 | 1563 |
| 3 | 13 | 1377 |
| 4 | 12 | 1601 |
| 5 | 10 | 1339 |
| 6 | 12 | 1639 |
| 7 | 11 | 1410 |
| 8 | 10 | 1221 |
| 9 | 13 | 1316 |
| 10 | 13 | 1575 |
| 平均 | 11.9 | 1469 |
今回の場合、PK / FK テーブルは、通常テーブルよりもクエリの処理速度が速くなりました。
2.6. 結合解除されないときの PK / FK の効果確認 (左外部結合)
normal1 (pk1) と normal2 (fk1) 両方のカラムを取得するように、2.4. 左外部結合 (左: FK(一意), 右: PK(重複)) で結合解除される場合の SQL を変更することで、結合解除されないようにし、PK / FK により結合性能が改善されるか確認します。
通常テーブル
SELECT
n1.revision_id,
n2.id
FROM
pk_fk_dataset.normal1 n1
LEFT JOIN
pk_fk_dataset.normal2 n2
ON
n1.revision_id = n2.id
PK / FK テーブル
SELECT
fk1.revision_id,
pk1.id
FROM
pk_fk_dataset.fk1
LEFT JOIN
pk_fk_dataset.pk1
ON
fk1.revision_id = pk1.id
通常テーブルと PK / FK テーブルそれぞれ 10 回実行した結果は以下のようになりました。
通常テーブル
| # | 経過時間 (s) | 消費したスロット時間 (s) |
|---|---|---|
| 1 | 18 | 2840 |
| 2 | 24 | 2990 |
| 3 | 20 | 2911 |
| 4 | 22 | 3033 |
| 5 | 22 | 3556 |
| 6 | 19 | 2873 |
| 7 | 22 | 3163 |
| 8 | 30 | 3253 |
| 9 | 20 | 3110 |
| 10 | 21 | 3132 |
| 平均 | 21.8 | 3086 |
PK / FK テーブル
| # | 経過時間 (s) | 消費したスロット時間 (s) |
|---|---|---|
| 1 | 19 | 3080 |
| 2 | 21 | 2914 |
| 3 | 23 | 3909 |
| 4 | 21 | 2917 |
| 5 | 20 | 2752 |
| 6 | 22 | 2986 |
| 7 | 24 | 3120 |
| 8 | 20 | 2628 |
| 9 | 27 | 2918 |
| 10 | 22 | 3044 |
| 平均 | 21.9 | 3027 |
今回の場合、PK / FK テーブルは、通常テーブルよりもクエリの処理速度が遅くなりました。(0.1秒差なので、ほぼ同じ)
3. まとめ
PK で値が重複している場合でも、結合解除は起きました。しかし、結合解除が起きる結合を行なった場合、誤った結果になりました (PK / FK を設定していない通常テーブルの結果と一致しませんでした)。
Discussion