クエリ計算を最適化する

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

クエリが完了すると、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 IS NOT NULL
  GROUP BY user_id
  ORDER BY comments_count DESC
  LIMIT 20
  )
SELECT
  user_id,
  display_name,
  reputation,
  comments_count
FROM comments
JOIN
  `bigquery-public-data`.stackoverflow.users AS u
ON
  user_id = u.id
ORDER BY comments_count DESC;

WHERE 句を使用する

ベスト プラクティス: WHERE 句を使用して、クエリが返すデータの量を制限します。可能であれば、WHERE 句で BOOL、INT、FLOAT、または DATE 列を使用します。

BOOL、INT、FLOAT、DATE 列のオペレーションは、通常、STRING または BYTE 列のオペレーションより高速です。可能な場合、WHERE 句でこれらのデータ型のいずれかを使用する列を使用して、クエリによって返されるデータの量を削減します。

クエリ オペレーションを最適化する

クエリ オペレーションを最適化するには、以降のセクションで説明するオプションを使用します。

データの繰り返し変換を避ける

ベスト プラクティス: SQL を使用して ETL オペレーションを実行する場合は、同じデータを繰り返し変換する状況を避けてください。

たとえば、SQL を使用して文字列をトリミングしたり、正規表現を使用してデータを抽出したりする場合は、宛先テーブルで変換結果を実体化する方がパフォーマンスが向上します。正規表現のような関数には追加のコンピューティングが必要です。追加される変換オーバーヘッドなしに宛先テーブルを照会する方がはるかに効率的です。

同じ CTE を複数回評価することを避ける

ベスト プラクティス: 手続き型言語、変数、一時テーブル、自動的に期限切れになるテーブルを使用して計算を維持し、後でクエリで計算を使用します。

クエリに共通テーブル式（CTE）が含まれており、クエリ内の複数の場所で使用される場合、これらの式は参照されるたびに評価される可能性があります。クエリ オプティマイザーは、1 回しか実行できないクエリ部分を検出しようとしますが、常に検出できるとは限りません。その結果、CTE を使用しても内部クエリの複雑さとリソース消費の軽減につながらない場合があります。

CTE の結果は、CTE が返すデータに応じてスカラー変数または一時テーブルに格納できます。

結合の繰り返しとサブクエリを回避する

ベスト プラクティス: 繰り返し同じテーブルを結合したり、同じサブクエリを使用したりしないでください。

データを繰り返し結合するのではなく、データをネストして繰り返す方法で関係を表現する方がパフォーマンスが向上する可能性があります。データをネストして繰り返すと、結合に必要な通信帯域幅が原因で生じるパフォーマンス低下が軽減されます。また、同じデータを繰り返し読み書きすることによって発生する I/O コストも削減できます。詳細については、ネストされ繰り返されているフィールドを使用するをご覧ください。

同様に、同じサブクエリを繰り返すと、クエリ処理が繰り返されるため、パフォーマンスが低下します。複数のクエリで同じサブクエリを使用している場合は、サブクエリの結果をテーブルで実体化し、実体化されたデータをクエリで使用することを検討してください。

サブクエリの結果を実体化することで、パフォーマンスが向上し、BigQuery によって読み書きされるデータの総量が削減されます。実体化されたデータを格納するためのコストは低レベルであるため、I/O やクエリ処理を繰り返す場合よりパフォーマンスが向上します。

結合パターンを最適化する

ベスト プラクティス: 複数のテーブルのデータを結合するクエリの場合は、最も大きいテーブルから順に結合パターンを最適化します。

JOIN 句を使用してクエリを作成する場合は、データを結合する順序を考慮します。GoogleSQL クエリ オプティマイザーが、結合のどちら側にどのテーブルを配置するかを決定します。ベスト プラクティスは、最初に最も行数の多いテーブルを配置し、その後に行数の少ないテーブルを順に配置することです。

大きいテーブルを JOIN の左側に配置し、小さいテーブルを JOIN の右側に配置した場合は、ブロードキャスト結合が形成されます。ブロードキャスト結合は、小さいテーブル内のすべてのデータを大きいテーブルを処理する各スロットに送信します。ブロードキャスト結合を最初に実行することをおすすめします。

JOIN 内のテーブルのサイズを表示するには、テーブルに関する情報を取得するをご覧ください。

ORDER BY 句を最適化する

ベスト プラクティス: ORDER BY 句を使用する場合は、次のベスト プラクティスに従ってください。

  • ORDER BY を、最も外側のクエリ内またはウィンドウ句内で使用します。 複雑なオペレーションをクエリの最後に配置します。ウィンドウ関数で使用する場合を除き、クエリの途中に ORDER BY 句を配置するとパフォーマンスに大きな影響を与えます。

    クエリを並べ替える別の方法は、正規表現や数学関数などの複雑なオペレーションをクエリの最後に配置することです。この方法では、複雑なオペレーションが実行される前の時点で、処理するデータが削減されます。

  • LIMIT 句を使用します。非常に大量の値を並べ替えるものの、それらをすべて返す必要がない場合は、LIMIT 句を使用します。たとえば、次のクエリは非常に大規模な結果セットを並び替えるため、Resources exceeded エラーが発生します。クエリは mytable の title 列で並べ替えられます。title 列には数百万個の値が含まれています。

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title;

    エラーを回避するには、次のようなクエリを使用します。

    SELECT
title
FROM
`my-project.mydataset.mytable`
ORDER BY
title DESC
LIMIT
1000;

  • ウィンドウ関数を使用します。大量の値を並べ替える場合は、ウィンドウ関数を使用し、ウィンドウ関数を呼び出す前にデータを制限します。たとえば、次のクエリは 10 人の最も古い Stack Overflow ユーザーとそのランキングを、最も古いアカウントを最も低いランクとして一覧表示します。

    SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM bigquery-public-data.stackoverflow.users
ORDER BY user_rank ASC
LIMIT 10;

    このクエリの実行には約 15 秒かかります。このクエリではクエリの最後で LIMIT を使用していますが、DENSE_RANK() OVER ウィンドウ関数では使用しません。このため、クエリではすべてのデータを単一のワーカーノードで並べ替える必要があります。

    パフォーマンスを向上させるためには、代わりにウィンドウ関数を計算する前にデータセットを制限すべきです。

    WITH users AS (
SELECT
id,
reputation,
creation_date,
FROM bigquery-public-data.stackoverflow.users
ORDER BY creation_date ASC
LIMIT 10)
SELECT
id,
reputation,
creation_date,
DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
FROM users
ORDER BY user_rank;

    このクエリの実行には約 2 秒かかりますが、前のクエリと同じ結果が返されます。

    注意点として、DENSE_RANK() 関数は年内のデータをランク付けするため、複数年にまたがるデータをランキングする場合、これらのクエリは同じ結果にならないということです。

複雑なクエリを小さなクエリに分割する

ベスト プラクティス: 複数ステートメント クエリ機能とストアド プロシージャを利用して、1 つの複雑なクエリとして設計されていた計算を、より小さく単純な複数のクエリとして実行します。

複雑なクエリ、REGEX 関数、階層化されたサブクエリまたは結合は、実行に時間がかかり、リソースを大量に消費することがあります。すべての計算を 1 つの巨大な SELECT ステートメントにまとめると（ビューを作成するなど）、アンチパターンとなり、リソース消費の多いクエリになる可能性があります。極端な場合、内部クエリプランが複雑化しすぎて、BigQuery で実行できなくなります。

複雑なクエリを分割すると、中間結果を変数または一時テーブルで実体化できます。これらの中間結果は、クエリの他の部分で使用できます。これらの結果がクエリの複数の部分で必要になる場合は非常に便利です。

多くの場合、データ実体化ポイントである一時テーブルを使用して、クエリの部分の真のインテントをより適切に表現できます。

ネストされ繰り返されているフィールドを使用する

ネストされ繰り返されているフィールドを使用してデータ ストレージを非正規化する方法については、ネストされ繰り返されているフィールドを使用するをご覧ください。

結合で INT64 データ型を使用する

ベスト プラクティス: コストを削減し、比較のパフォーマンスを改善するため、結合では STRING データ型ではなく INT64 データ型を使用します。

BigQuery では、従来のデータベースと同様に主キーのインデックスが作成されないため、結合列が広いほど比較に時間がかかります。したがって、結合で INT64 データ型を使用するほうが、STRING データ型を使用するよりも低コストで効率的です。

クエリ出力を削減する

クエリ出力を削減するには、以降のセクションで説明するオプションを使用します。

大規模な結果セットを実体化する

ベスト プラクティス: 抽出先テーブルに大規模な結果セットを実体化することを検討します。大規模な結果セットを書き込むと、パフォーマンスが低下し、コストが上昇します。

BigQuery にはキャッシュに保存された結果について、圧縮した状態で約 10 GB までという制限があります。大規模な結果を返すクエリはこの制限を上回るため、Response too large というエラーが高頻度で発生します。

このエラーは多くの場合、大量のデータを含むテーブルから多数のフィールドを選択すると発生します。キャッシュに保存された結果の書き込みに関する問題は、データを縮小または集約することなく正規化する ETL スタイルのクエリでも発生する可能性があります。

キャッシュに保存された結果のサイズ制限は、次のオプションを使用することで解決できます。

  • フィルタを使用して結果セットを制限する
  • ORDER BY 句を使用する場合は特に、LIMIT 句を使用して結果セットを削減する
  • 出力データを宛先テーブルに書き込む

BigQuery REST API を使用すると、結果をページ分割できます。詳細については、テーブルデータのページ分割をご覧ください。

BI Engine を使用する

最も頻繁に使用するデータをキャッシュに保存することで SQL クエリをさらに高速化するには、クエリが計算されるプロジェクトに BI Engine 予約を追加することを検討してください。BigQuery BI Engine は、ベクトル化されたクエリエンジンを使用して SELECT クエリのパフォーマンスを向上させます。

アンチ SQL パターンを避ける

以下のベスト プラクティスでは、BigQuery のパフォーマンスに影響するクエリ アンチパターンの回避に関するガイダンスを提供します。

自己結合を避ける

ベスト プラクティス: 自己結合ではなく、ウィンドウ（分析）関数または PIVOT 演算子を使用します。

通常、自己結合は、行依存の関係をコンピューティングするために使用します。自己結合を使用すると、出力行の数が 2 乗倍になる可能性があります。このように出力データが増加した場合は、パフォーマンスが低下することがあります。

クロス結合を避ける

ベスト プラクティス: 入力より出力の方が多い結合を回避します。CROSS JOIN が必要な場合は、データを事前集計します。

クロス結合は、1 つ目のテーブルの各行が 2 つ目のテーブルのすべての行に結合されるクエリです（両側に一意ではないキーが存在します）。最悪の場合の出力は、左側のテーブルの行数に右側のテーブルの行数を掛けたものになります。極端な場合、クエリが終了しないことがあります。

クエリジョブが完了すると、クエリプランの説明に出力行数と入力行数が表示されます。デカルト積を確認するには、JOIN 句の両側の行数を結合キーでグループ分けして出力するようにクエリを変更します。

入力より出力の方が多い結合に関連したパフォーマンス問題を回避するには:

  • GROUP BY 句を使用して、データを事前集計します。
  • ウィンドウ関数を使用します。多くの場合、ウィンドウ関数は、クロス結合を使用するより効率的です。詳細については、ウィンドウ関数をご覧ください。

単一行を更新または挿入する DML ステートメントを避ける

ベスト プラクティス: 単一行を更新または挿入する DML ステートメントは使用しないでください。更新と挿入をバッチ処理します。

ポイント固有の DML ステートメントを使用すると、BigQuery をオンライン トランザクション処理（OLTP）システムのように扱おうとしていることになります。BigQuery は、ポイント検索ではなく、テーブル スキャンを使用したオンライン分析処理（OLAP）に重点が置かれています。OLTP に似た動作（単一行の更新または挿入）が必要な場合は、Cloud SQL などの OLTP ユースケースをサポートするように設計されたデータベースを検討してください。

BigQuery DML ステートメントの目的は一括更新です。BigQuery 内の UPDATE および DELETE DML ステートメントは、単一行を変更するのではなく、データを定期的に書き換えるためのものです。INSERT DML ステートメントは、控え目に使用するように意図されています。挿入では、読み込みジョブと同じ変更割り当てが消費されます。ユースケースで単一行の挿入が頻繁に行われる場合は、代わりにデータのストリーミングを検討してください。

UPDATE ステートメントをバッチ処理すると非常に長いクエリ内に多数のタプルが発生する場合は、256 KB というクエリの長さ制限に近づく可能性があります。クエリの長さ制限を回避するには、一連の直接タプル置換ではなく、論理的な基準に基づいて更新を処理できるかどうかを検討してください。

たとえば、置換レコードのセットを別のテーブルに読み込んでから、更新されていない列が一致した場合に元のテーブルのすべての値を更新する DML ステートメントを記述できます。たとえば、元のデータがテーブル t 内に存在し、更新がテーブル u 内でステージングされている場合、クエリは次のようになります。

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key

類似した名前の列にエイリアス名を使用する

ベスト プラクティス: サブクエリなど、クエリ全体で同じ名前の列を操作する場合は、列とテーブルのエイリアスを使用します。

エイリアスは、列の最初の参照に加えて、参照される列とテーブルを特定するのに役立ちます。エイリアスを使用すると、サブクエリで使用されている列の検索など、SQL クエリの問題を把握して対処できます。

テーブル スキーマで制約を指定する

テーブルデータに制約が含まれている場合は、テーブル スキーマで制約を指定します。クエリエンジンでは、テーブル制約を使用してクエリプランを最適化できます。

主キー制約と外部キー制約を指定する

テーブルデータが主キー制約または外部キー制約のデータの整合性要件を満たしている場合は、テーブル スキーマでキー制約を指定する必要があります。クエリエンジンは、キー制約を使用してクエリプランを最適化できます。詳細については、ブログ投稿 BigQuery の主キーと外部キーで結合を最適化をご覧ください。

BigQuery はデータの整合性を自動的にチェックしないため、データがテーブル スキーマで指定された制約に従っていることを確認する必要があります。指定された制約があるテーブルでデータの整合性を維持しないと、クエリ結果が不正確になる可能性があります。

次のステップ