BigQueryのクエリ最適化 - テックブログ

✅

はじめに BigQueryでのアンチパターンとベストプラクティス SELECT * の使用を避ける SELECT COUNT(*) の使用を避ける LIMIT の使用はコストに影響しない自己結合を避ける日付で区切られたテーブルの全期間の読み込みを避けるサブクエリでのORDER BY を避ける参考

はじめに

BigQueryでは、大量のデータを簡単に操作できるのが特徴ですが、クエリの内容次第でパフォーマンスに悪影響を与えるアンチパターンが存在します。そこでこの記事では、アンチパターンを回避するベストプラクティスを解説します。

BigQueryでのアンチパターンとベストプラクティス

**SELECT * の使用を避ける**

SELECT *とすることで全てのカラムを指定することができます。しかし、これは全てのデータをスキャンするため、大きなデータセットではコストが高くなります。また、テーブルのスキーマが変更された場合に、想定外の結果をもたらす可能性もあります。そのため、必要なフィールドのみを明示的に指定することをおすすめします。

🙅悪い例（全列を選択）

SELECT
	*,
FROM my_dataset.my_table

🙆良い例（必要な列だけを選択）

SELECT
	id,
	name,
	ammount,
FROM my_dataset.my_table

**SELECT COUNT(*) の使用を避ける**

❗

SELECT COUNT(*)とSELECT COUNT(1) のパフォーマンスは同じのようです。

SELECT * と同じで、SELECT COUNT(*) は全てのデータをスキャンするため、大きなデータセットではコストが高くなります。そのため、SELECT COUNT(1) と指定することをおすすめします。

~~🙅悪い例~~

SELECT
	id,
	COUNT(*),
FROM my_dataset.my_table
GROUP BY id

~~🙆良い例~~

SELECT
	id,
	COUNT(1),
FROM my_dataset.my_table
GROUP BY id

LIMIT の使用はコストに影響しない

SQL経験者は、LIMIT 1000でクエリのコスト削減ができると思われがちですが、実際は違います。BigQueryでは、全レコードをスキャン後にLIMITが適用されるので、LIMITによるクエリのコスト減少にはつながりません。

自己結合を避ける

テーブルに対して自己結合を実行は避けるべきです。自己結合によりテーブルのスキャンが増えてクエリのパフォーマンスが低下し、処理されるバイト数も増加するため、クエリの実行コストも増加します。力技で自己結合するよりも、よりスマートなウィンドウ関数の使用を検討してください。

🙅悪い例（自己結合）

SELECT
  item,
  purchases,
  category,
  total.total_purchases,
FROM Produce
LEFT JOIN (
  SELECT
    category,
    SUM(purchases) AS total_purchases
  FROM Produce
  GROUP BY category) AS total
USING (category)

🙆良い例（ウィンドウズ関数）

SELECT
  item,
  purchases,
  category,
  SUM(purchases) OVER (PARTITION BY category) AS total_purchases
FROM Produce

日付で区切られたテーブルの全期間の読み込みを避ける

日付でパーティション化（シャーディング化）されたテーブルを読み込む場合、特定の日付の範囲のみを読み込むクエリを実行できます。これにより、テーブルの全期間をスキャンする必要がなくなるため、パフォーマンスが向上します。

パーティションされたテーブルの読み込み
🙅悪い例（my_tableテーブルの全期間を読み込む）
```
SELECT id, timestamp
FROM my_dataset.my_table
```
🙆良い例（my_tableテーブルを期間指定して読み込む）
```
SELECT id, timestamp
FROM my_dataset.my_table
WHERE _PARTITIONTIME BETWEEN TIMESTAMP("2023-01-01") AND TIMESTAMP("2023-01-31")
```
シャーディングされたテーブルの読み込み
🙅悪い例（頭文字がmy_table_の全テーブルを読み込む）
```
SELECT id, timestamp
FROM my_dataset.my_table_*
```
🙆良い例（my_table_20230101〜my_table_20230131のテーブルを読み込む）
```
SELECT id, timestamp
FROM my_dataset.my_table_*
WHERE _TABLE_SUFFIX BETWEEN "20230101" AND "20230131"
```

サブクエリでのORDER BY を避ける

ORDER BYは、全てのレコードをスキャンし比較するので非常にコストがかかります。最終的にORDER BYする際もサブクエリ内での使用は控えることをお勧めします。

🙅悪い例（サブクエリでORDER BYを使用）

WITH
  t1 AS (
    SELECT
      id,cate_1_1,cate_1_2,cate_1_3,cate_1_4
    FROM
      my_dataset.my_table_1
    ORDER BY
      1,2,3,4,5
  )、
  t2 AS (
    SELECT
      id,cate_2_1,cate_2_2,cate_2_3,cate_2_4
    FROM
      my_dataset.my_table_2
    ORDER BY
      1,2,3,4,5
  )
SELECT *
FROM t1
LEFT JOIN t2
USING (id)
ORDER BY
  1,2,3,4,5

🙅良い例（サブクエリでORDER BYを使用しない）

WITH
  t1 AS (
    SELECT
      id,cate_1_1,cate_1_2,cate_1_3,cate_1_4
    FROM
      my_dataset.my_table_1
  )、
  t2 AS (
    SELECT
      id,cate_2_1,cate_2_2,cate_2_3,cate_2_4
    FROM
      my_dataset.my_table_2
  )
SELECT *
FROM t1
LEFT JOIN t2
USING (id)
ORDER BY
  1,2,3,4,5