SQL のベスト プラクティス

クエリ実行プランで説明しているとおり、Cloud Spanner の SQL コンパイラは SQL ステートメントをクエリ実行プランに変換します。クエリ実行プランはクエリの結果を取得するために使用されます。このページでは、Cloud Spanner が効率的な実行プランを見つけるのに役立つ SQL ステートメントを作成するためのおすすめの方法について説明します。

このページに示す SQL ステートメントの例では以下のサンプル スキーマを使用します。

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo BYTES(MAX),
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
 SingerId     INT64 NOT NULL,
 AlbumId      INT64 NOT NULL,
 AlbumTitle   STRING(MAX),
 ReleaseDate  DATE,
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

SQL の詳細なリファレンスについては、ステートメントの構文、関数と演算子、語彙の構造と構文をご覧ください。

クエリ パラメータを使用して、頻繁に実行するクエリを高速化する

パラメータ化されたクエリは、クエリ文字列とクエリ パラメータ値を分離するクエリ実行のテクニックです。たとえば、アプリケーションで、指定した年に特定のタイトルのアルバムをリリースした歌手を取得する必要があるとします。「Love」というタイトルで 2017 年にリリースされたアルバムをすべて取得するには、次の例のような SQL ステートメントを作成します。

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Love' AND a.ReleaseDate >= '2017-01-01'

もう 1 つのクエリでは、アルバムのタイトルを「Peace」に変更します。

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Peace' AND a.ReleaseDate >= '2017-01-01'

アプリケーションで、後続のクエリのリテラル値のみを変更するこのようなクエリを大量に実行する必要がある場合、その値のパラメータ プレースホルダを使用する必要があります。パラメータ クエリをキャッシュに保存して再利用できます。これにより、コンパイルのコストを削減できます。

たとえば、以下の書き換えたクエリでは Love を title という名前のパラメータに置換します。

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'

クエリ パラメータを使用する際、次のことに注意してください。

  • クエリのパラメータ参照では @ 文字とその後にパラメータ名を使用する。これには英数字、アンダースコアを含めることができます。
  • パラメータは、リテラル値が期待されているどの場所にも使用できる。
  • 1 つの SQL ステートメントで同じパラメータ名を複数回使用できる。
  • クエリ パラメータと値を指定して、ExecuteSQL または ExecuteStreamingSQL リクエスト API の params フィールドにバインドする。
  • 詳細については、SQL 語彙の構造と構文のクエリ パラメータ構文を参照する。

まとめると、クエリ パラメータは次の方法でのクエリ実行に役立ちます。

  • 事前に最適化されたプラン: パラメータを使用するクエリは、パラメータ化によって Cloud Spanner での実行プランのキャッシュ保存が容易になるため、呼び出しごとに高速に実行できます。
  • 簡単なクエリ比較: 文字列の値をクエリ パラメータに指定する場合、その文字列をエスケープする必要はありません。クエリ パラメータを使用すると、構文エラーのリスクが少なくなります。
  • セキュリティ: クエリ パラメータでさまざまな SQL インジェクション攻撃から保護することによって、クエリの安全性が高まります。クエリをユーザーの入力から作成する場合に、この保護は特に重要になります。

Cloud Spanner によるクエリの実行方法を理解する

Cloud Spanner では、取得するデータを指定する宣言型 SQL 文を使用してデータベースでクエリを実行できます。Cloud Spanner での結果の取得方法も理解したい場合、クエリ実行プランを使用する必要があります。クエリ実行プランではクエリの各ステップに関連付けられた計算コストが表示されます。これにより、クエリのパフォーマンスに関する問題をデバッグし、クエリを最適化できます。

クエリ実行プランは、Cloud Console またはクライアント ライブラリで取得できます。

Cloud Console を使用してクエリプランを取得するには:

  1. Cloud Spanner のインスタンス ページを開きます。

    Cloud Spanner のインスタンスに移動する

  2. クエリを実行する Cloud Spanner インスタンスとデータベースの名前をクリックします。

  3. [クエリ] をクリックします。

  4. テキスト フィールドにクエリを入力し、[クエリを実行] をクリックします。

  5. [説明] をクリックします。
    Cloud Console にクエリの実行プランが表示されます。

    Console UI [説明] のスクリーンショット

クエリプランの詳細なリファレンスについては、クエリ実行プランをご覧ください。

セカンダリ インデックスを使用して、よく使用するクエリを高速化する

他のリレーショナル データベースと同様に、Cloud Spanner でもセカンダリ インデックスが提供されます。これによって、SQL ステートメントまたは Cloud Spanner の読み取りインターフェースを使用してデータを取得できます。インデックスからデータをフェッチする最も一般的な方法は、SQL クエリ インターフェースを使用する方法です。SQL クエリでセカンダリ インデックスを使用すると、Cloud Spanner が結果を取得する方法を指定できます。セカンダリ インデックスを指定することで、クエリを高速化できる場合があります。

たとえば、特定の姓のすべての歌手の ID をフェッチするとします。このような SQL クエリを作成する 1 つの方法は次のとおりです。

SELECT s.SingerId
FROM Singers AS s
WHERE s.LastName = 'Smith';

このクエリは期待どおりの結果を返しますが、結果を返すまでに時間がかかる場合があります。タイミングは Singers テーブル内の行数と述語 WHERE s.LastName = 'Smith' の数に応じて異なります。読み取り先の LastName 列を含むセカンダリ インデックスがない場合、クエリプランは Singers テーブル全体を読み取り、述語に一致する行を検索します。テーブル全体の読み取りはフルテーブル スキャンと呼ばれます。テーブルに含まれる Singers のうち、その姓を持つものの割合が小さければ、フルテーブル スキャンで結果を取得するのは高コストになります。

姓の列についてセカンダリ インデックスを定義すると、クエリのパフォーマンスを向上させることができます。

CREATE INDEX SingersByLastName on Singers (LastName);

セカンダリ インデックス SingersByLastName にはインデックス登録されたテーブル列 LastName と主キー列 SingerId が含まれるため、Cloud Spanner で Singers テーブル全体にスキャンを実行する代わりに、非常に小さいインデックス テーブルからすべてのデータをフェッチできます。

このシナリオでは、クエリの実行時に Cloud Spanner がセカンダリ インデックス SingersByLastName を自動的に使用する可能性が高くなりますが、FROM 句でインデックス ディレクティブを指定して、そのインデックスを使用するように Cloud Spanner に明示的に指示する方法をおすすめします。

SELECT s.SingerId
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

歌手の名前と ID を取得する場合について考えてみましょう。インデックスに FirstName 列が含まれていないため、前の例と同様に、インデックス ディレクティブを指定する必要があります。

SELECT s.SingerId, s.FirstName
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

Cloud Spanner でクエリプランの実行時に全表スキャンを実行する必要がないため、この場合でもインデックスの使用によってパフォーマンス上の利点を得られます。代わりに、SingersByLastName インデックスから述語を満たす行のサブセットを選択し、ベーステーブル Singers からルックアップを実行して、行のサブセットのみを対象として名前をフェッチします。

Cloud Spanner でベーステーブルの行からフェッチをまったく行わないようにする場合、FirstName 列のコピーをインデックス自体に保存することもできます。

CREATE INDEX SingersByLastName on Singers (LastName) STORING (FirstName);

このような STORING 句を使用すると、追加のストレージ費用がかかりますが、インデックスを使用したクエリと読み取り呼び出しにより次の利点が得られます。

  • インデックスを使用し、STORING 句に保存された列を選択する SQL クエリで、ベーステーブルへの余分な結合が不要になる。
  • インデックスを使用する読み取り呼び出しで、STORING 句に保存された列を読み取ることができる。

上述の例は、クエリの WHERE 句によって選択された行を、セカンダリ インデックスを使用してすばやく特定できる場合、セカンダリ インデックスでクエリをどれだけ高速化できるかを示しています。セカンダリ インデックスによってパフォーマンスを向上させることができるもう 1 つのシナリオは、順序付けされた結果を返す特定のクエリの場合です。たとえば、すべてのアルバム タイトルとそれらのリリース日をフェッチし、リリース日の昇順、アルバム タイトルの降順でそれらを返すとします。次のような SQL クエリを作成できます。

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

セカンダリ インデックスがない場合、このクエリには高コストになりがちな実行プランの並べ替えステップが必要となります。このセカンダリ インデックスを定義して、クエリ実行を高速化できます。

CREATE INDEX AlbumsByReleaseDateTitleDesc on Albums (ReleaseDate, AlbumTitle DESC);

セカンダリ インデックスを使用するようにクエリを書き換えます。

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums@{FORCE_INDEX=AlbumsByReleaseDateTitleDesc} AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

このクエリとインデックス定義は次の両方の条件を満たすことに注意してください。

  • ORDER BY 句の列リストがインデックス キーリストの接頭辞である。
  • クエリで使用されるテーブル内のすべての列がインデックスの対象である。

これらの条件の両方が満たされるため、作成されるクエリプランで並べ替えステップが削除され、高速で実行できます。

セカンダリ インデックスによって、よく使用するクエリを高速化できますが、セカンダリ インデックスを追加すると、一般的に commit ごとに追加のノードが必要となるため、commit オペレーションの待ち時間が追加される可能性があります。ほとんどのワークロードで、セカンダリ インデックスを少なくすることをおすすめします。ただし、読み取りまたは書き込みの待ち時間を重視するかどうか、ワークロードで最も重要なオペレーションはどれかを検討する必要があります。また、ワークロードのベンチマークを実行して、期待どおりのパフォーマンスが得られることを確認する必要もあります。

セカンダリ インデックスの詳細なリファレンスについては、セカンダリ インデックスをご覧ください。

範囲キーのルックアップのための効率的なクエリを作成する

SQL クエリの一般的な用途は、既知のキーのリストに基づいて、Cloud Spanner から複数行を読み取ることです。

以下はキーの範囲によってデータをフェッチする場合に効率的なクエリを作成するためのベスト プラクティスです。

  • キーのリストが短く、連続していない場合、クエリ パラメータと UNNEST を使用してクエリを作成する。

    たとえば、キーのリストが {1, 5, 1000} の場合、次のようなクエリを作成します。

    SELECT *
FROM Table AS t
WHERE t.Key IN UNNEST (@KeyList)

    注:

    • 配列 UNNEST 演算子は、入力配列を要素の行にフラット化します。

    • @KeyList はクエリ パラメータで、前述のベスト プラクティスで説明したとおり、クエリを高速化できます。

  • キーのリストが連続する範囲である場合、WHERE 句でキーの範囲の下限と上限を指定する。

    たとえば、キーのリストが {1,2,3,4,5} の場合、次のようなクエリを作成します。

    SELECT *
FROM Table AS t
WHERE t.Key BETWEEN @min AND @max

    ここで @min と @max は、1 と 5 にそれぞれバインドされるクエリ パラメータです。

    このクエリはキー範囲内のキーが連続している場合にのみ効率的です。言い換えると、キーのリストが {1, 5, 1000} の場合、作成されるクエリで 1～1000 のすべての値がスキャンされるため、上記の例のように上限と下限を指定すべきではありません。

結合のための効率的なクエリを作成する

結合オペレーションはコストが高くなる可能性があります。これは、JOIN によってクエリでスキャンする必要がある行数が大幅に増加し、その結果、クエリが低速になる可能性があるためです。他のリレーショナル データベースで使い慣れている結合クエリを最適化するためのテクニックに加えて、Cloud Spanner SQL を使用する場合のさらに効率的な JOIN のおすすめの方法がいくつかあります。

  • 可能な限り、インターリーブされたテーブルのデータを主キーによって結合する。例:

    SELECT s.FirstName, a.ReleaseDate
FROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;

    スキーマとデータモデルで説明しているとおり、インターリーブされたテーブル Albums 内の行は、Singers の親行と同じスプリットに物理的に保存されることが保証されます。したがって、ネットワークを介して大量のデータを送信することなく、JOIN をローカルで実行できます。

  • JOIN の順序を強制する場合、結合ディレクティブを使用する。例:

    SELECT *
FROM Singers AS s JOIN@{FORCE_JOIN_ORDER=TRUE} Albums AS a
ON s.SingerId = a.Singerid
WHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';

    結合ディレクティブ @{FORCE_JOIN_ORDER=TRUE} は Cloud Spanner に、クエリで指定された結合順序（つまり、Albums JOIN Singers Singers ではなく Singers JOIN Albums）を使用するように指示します。ただし、クエリプランで Cloud Spanner によって結合順序が変更され、大量の中間結果や行検索の機会消失などの望ましくない結果が生じたことに気付いた場合、この結合ディレクティブを使用します。

  • 結合ディレクティブを使用して、結合の実装を選択します。クエリに適した結合アルゴリズムを選択することで、待ち時間を短縮できるか、メモリ使用量を削減できるか、あるいはその両方を実現できます。このクエリは、JOIN ディレクティブを JOIN_METHOD ヒントと使用して HASH JOIN を選択する構文を示しています。

    SELECT *
FROM Singers s JOIN@{JOIN_METHOD=HASH_JOIN} Albums AS a
ON a.SingerId = a.SingerId

  • HASH JOIN または APPLY JOIN を使用する場合、または JOIN の片側で高度に選択的な WHERE 句がある場合、結合の FROM 句の最初のテーブルとして、最小数の行を生成するテーブルを配置します。これは現在 HASH JOIN を使用しているためで、Cloud Spanner で常に左側のテーブルがビルドとして、右側のテーブルがプローブとして選択されます。同様に、APPLY JOIN の場合、Cloud Spanner で左側のテーブルが外側として、右側のテーブルが内側として選択されます。これらの結合タイプの詳細については、ハッシュ結合と適用結合をご覧ください。

読み取り / 書き込みトランザクションで大量の読み取りを回避する

読み取り / 書き込みトランザクションでは、0 回または 1 回以上の読み取りまたは SQL クエリが可能で、commit の呼び出しの前に一連のミューテーションを含めることができます。データの整合性を維持するために、Cloud Spanner ではテーブル内の行およびインデックスの読み取り / 書き込み時にロックを取得します（ロックの詳細については、読み取り / 書き込みのライフサイクルを参照）。

Cloud Spanner のロックのしくみによって、大量の行を読み取る必要がある読み取りまたは SQL クエリの実行（たとえば、SELECT * FROM Singers）は、トランザクションが commit されるか中止されるまで、読み取られた行に他のトランザクションが書き込めないことを意味します。さらに、トランザクションで大量の行を処理しているため、小規模な範囲の行を読み取るトランザクション（たとえば、SELECT LastName FROM Singers WHERE SingerId = 7）よりも時間がかかり、それによって問題が悪化し、システムのスループットが低下します。

つまり、書き込みスループットの低下を許容しない限り、トランザクション内で大量の読み取り（たとえば、全テーブル スキャンまたは大量の結合操作）を避ける必要があります。場合によっては、次のパターンでより良い結果が得られることがあります。

  1. 読み取り専用トランザクションで大量の読み取りを実行します（読み取り専用トランザクションではロックを使用しないため、集計スループットが向上します）。
  2. （省略可能）読み取ったばかりのデータに対してなんらかの処理を行う必要がある場合、それを実行します。
  3. 読み取り / 書き込みトランザクションを開始します。
  4. ステップ 1 での読み取り専用トランザクションの実行時以降に、重要な行の値が変更されていないことを確認します。
    1. 行が変更されている場合、トランザクションをロールバックし、ステップ 1 を再実行します。
    2. 何も問題がないように見える場合は、ミューテーションを commit します。

読み取り / 書き込みトランザクションでの大量の読み取りを回避する 1 つの方法は、クエリによって生成された実行プランを確認することです。

ORDER BY を使用して SQL 結果の順序を指定する

SELECT クエリの結果を特定の順序で表示する場合、ORDER BY 句を明示的に含める必要があります。たとえば、主キーの順序ですべての歌手を表示する場合、次のクエリを使用します。

SELECT * FROM Singers
ORDER BY SingerId;

Cloud Spanner で結果の順序が保証されるのは、クエリに ORDER BY 句がある場合のみであることに注意してください。言い換えると、ORDER BY を使用しないでこのクエリを考えます。

SELECT * FROM Singers;

Cloud Spanner では、このクエリの結果が主キー順になるとは限りません。さらに、結果の順序はいつでも変わる可能性があり、異なる呼び出しの間での整合性は保証されません。

LIKE の代わりに STARTS_WITH を使用して、パラメータ化された SQL クエリを高速化する

Cloud Spanner はパラメータ化された LIKE パターンを実行時まで評価しないので、すべての行を読み取って LIKE 式で評価し、一致しない行を除外しなければなりません。

LIKE パターンで値の先頭が一致するものを検索する場合、列にインデックスが作成されていれば、LIKE の代わりに STARTS_WITH を使用してください。そうすれば、Cloud Spanner はクエリ実行プランをより効率的なものに改善できます。

非推奨:

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE @like_clause;

推奨:

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, @prefix);