クエリ計算を最適化する

このドキュメントでは、クエリのパフォーマンスを最適化するためのベスト プラクティスについて説明します。

クエリを実行すると、 Google Cloud コンソールでクエリプランを表示できます。INFORMATION_SCHEMA.JOBS* ビューや jobs.get REST API メソッドを使用して、実行の詳細をリクエストすることもできます。

クエリプランには、クエリのステージとステップの詳細が含まれます。これらの詳細は、クエリのパフォーマンスを改善する方法を特定する際に役立ちます。たとえば、あるステージの書き込み出力が他のステージよりもはるかに多い場合は、クエリの早い段階でフィルタリングする必要がある可能性があります。

クエリプランの詳細と、クエリプランの情報を使用してクエリのパフォーマンスを改善する例については、クエリ パフォーマンスに関する分析情報を取得するをご覧ください。クエリ パフォーマンスの分析情報に対処したら、次のタスクを行ってクエリをさらに最適化できます。

処理されるデータの量を削減する

以降のセクションで説明するオプションを使用すると、処理する必要があるデータの量を減らすことができます。

SELECT * を避ける

ベスト プラクティス: 必要な列のみをクエリして射影を制御します。

射影とは、クエリによって読み取られる列の数のことです。列の射影が多すぎると、追加の（無駄な）I/O と実体化（書き込み結果）が発生します。

  • データ プレビュー オプションを使用する。データのテストまたは探索には、SELECT * ではなく、いずれかのデータ プレビュー オプションを使用してください。
  • 特定の列に対するクエリ。LIMIT 句を SELECT * クエリに適用しても、読み取られるデータの量には影響しません。テーブル全体のすべてのバイトの読み取りに対して課金され、クエリは無料枠割り当ての対象としてカウントされます。代わりに、必要な列のみを照会します。たとえば、SELECT * EXCEPT を使用して、結果から 1 つ以上の列を除外します。
  • パーティション分割テーブルを使用する。テーブル内のすべての列を対象とするものの、データのサブセットに対してのみクエリを実行する必要がある場合は、以下のことを検討してください。

  • SELECT * EXCEPT を使用する。データのサブセットをクエリすることや SELECT * EXCEPT を使用することにより、クエリで読み取られるデータの量を大幅に削減できます。コストの削減に加えて、データ I/O の量とクエリ結果に必要な実体化の量を減らすことで、パフォーマンスが向上します。

    SELECT * EXCEPT (col1, col2, col5)
FROM mydataset.newtable

過剰なワイルドカード テーブルを避ける

ベスト プラクティス: ワイルドカード テーブルをクエリする場合は、最も詳細度の高い接頭辞を使用する必要があります。

ワイルドカードは、簡潔な SQL ステートメントを使用して複数のテーブルを照会するために使用します。ワイルドカード テーブルとは、ワイルドカード式に一致するテーブルが結合されたものです。ワイルドカード テーブルは、データセットに次のリソースが含まれている場合に役立ちます。

  • スキーマに互換性がある複数の類似した名前のテーブル
  • シャーディングしたテーブル

ワイルドカード テーブルをクエリする場合は、共通のテーブル接頭部の後にワイルドカード（*）を指定します。たとえば、FROM bigquery-public-data.noaa_gsod.gsod194* の場合、1940 年代のすべてのテーブルがクエリされます。

接頭部を細かく指定すると、接頭部が短い場合よりもパフォーマンスが向上します。たとえば FROM bigquery-public-data.noaa_gsod.gsod194* は、ワイルドカードに一致するテーブルが少ないため、FROM bigquery-public-data.noaa_gsod.* よりもパフォーマンスが良好になります。

テーブルを日付別にシャーディングすることを避ける

ベスト プラクティス: 時間分割テーブルの代わりに日付別に分割されたテーブル（日付別テーブルとも呼ばれる）を使用しないでください。

パーティション分割テーブルは、日付指定のテーブルより優れたパフォーマンスを発揮します。日付別に分割されたテーブルを作成する場合、BigQuery は各日付指定テーブルのスキーマとメタデータのコピーを保持する必要があります。また、日付指定のテーブルを使用する場合は、クエリされた各テーブルの権限を確認するために BigQuery が必要となることがあります。このプラクティスはさらに、クエリのオーバーヘッドを増やし、クエリのパフォーマンスを低下させます。

テーブルの過度な分割を回避する

ベスト プラクティス: テーブルを分割しすぎないでください。テーブルを日付別に分割している場合は、代わりに時間分割テーブルを使用してください。

テーブルのシャーディングとは、大規模なデータセットを個別のテーブルに分割し、各テーブル名にサフィックスを追加することを指します。テーブルを日付別にシャーディングしている場合は、代わりに時間パーティション分割テーブルを使用してください。

BigQuery ストレージは低コストであるため、リレーショナル データベース システムのようにテーブルを最適化してコストを調整する必要はありません。テーブルをシャーディングしすぎると、パフォーマンスへの悪影響が、コスト上のメリットを上回ります。

シャーディングされたテーブルでは、BigQuery で各分割のスキーマ、メタデータ、および権限を保持する必要があります。テーブルをシャーディングしすぎると、各分割の情報を保持する必要性からオーバーヘッドが増えるため、クエリのパフォーマンスが低下する可能性があります。

クエリによって読み取られるデータの量とソースは、クエリのパフォーマンスとコストに影響を与える可能性があります。

分割されたクエリを取り除く

ベスト プラクティス: パーティション分割テーブルのパーティションを使用してフィルタリングする目的でパーティション分割テーブルをクエリする場合は、次の列を使用します。

  • 取り込み時間パーティション分割テーブルの場合は、疑似列 _PARTITIONTIME を使用します。
  • 時間単位列ベースや整数範囲などのパーティション分割テーブルには、パーティショニング列を使用します。

時間単位のパーティション分割テーブルの場合、_PARTITIONTIME またはパーティショニング列を使用してデータをフィルタリングすると、日付または日付の範囲を指定できます。たとえば、次の WHERE 句では、_PARTITIONTIME 疑似列を使用して 2016 年 1 月 1 日から 2016 年 1 月 31 日までのパーティションを指定しています。

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
AND TIMESTAMP("20160131")

このクエリは、日付範囲で指定されたパーティション内のデータのみを処理します。パーティションをフィルタすると、クエリのパフォーマンスが向上し、コストが削減されます。

JOIN を使用する前にデータを削減する

ベスト プラクティス: 集計を実行して、JOIN 句よりも前の時点で処理されるデータの量を減らします。

集計関数で GROUP BY 句を使用すると、計算量が多くなります。これは、これらのタイプのクエリではシャッフルが使用されるためです。これらのクエリは計算集約型であるため、GROUP BY 句は必要な場合にのみ使用する必要があります。

GROUP BY と JOIN を使用するクエリでは、クエリの早い段階で集計を実行して、処理されるデータの量を減らします。たとえば、次のクエリは、事前にフィルタリングせずに 2 つの大きなテーブルに対して JOIN を実行します。

WITH
  users_posts AS (
  SELECT *
  FROM
    `bigquery-public-data`.stackoverflow.comments AS c
  JOIN
    `bigquery-public-data`.stackoverflow.users AS u
  ON
    c.user_id = u.id
  )
SELECT
  user_id,
  ANY_VALUE(display_name) AS display_name,
  ANY_VALUE(reputation) AS reputation,
  COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

このクエリは、コメント数を事前集計します。これにより、JOIN の読み取りデータ量が削減されます。

WITH
  comments AS (
  SELECT
    user_id,
    COUNT(text) AS comments_count
  FROM
    `bigquery-public-data`.stackoverflow.comments
  WHERE
    user_id