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 の詳しいリファレンスについては、文の構文、関数と演算子、語彙の構造と構文をご覧ください。

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

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

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 語彙の構造と構文のクエリ パラメータ構文を参照する。

上記の書き換えたクエリでは、日付の値 2017-01-01 にプレースホルダを使用しないことに注意してください。この値は後続のクエリ呼び出しで定数であるためです。この場合、定数をリテラルのままにすると、選択した特定のクエリプランを向上させることができるため有益です。

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

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

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

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

クエリプランを取得するには、GCP Console またはクライアント ライブラリを使用します。

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

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

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

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

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

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

   

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

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

他のリレーショナル データベースと同様に、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 JOIN Albums）を使用するように指示します。Cloud Spanner で選択された順序に関係なく、返される結果は同じです。ただし、クエリプランで Cloud Spanner によって結合順序が変更され、大量の中間結果や行検索の機会消失などの望ましくない結果が生じたことに気付いた場合、この結合ディレクティブを使用します。

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

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

• HASH JOIN または LOOP JOIN を使用する場合、または JOIN の片側で高度に選択的な WHERE 句がある場合、結合の FROM 句の最初のテーブルとして、最小数の行を生成するテーブルを配置します。これは現在 HASH JOIN を使用しているためで、Cloud Spanner で常に左側のテーブルがビルドとして、右側のテーブルがプローブとして選択されます。同様に、LOOP 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;

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