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 を使用してクエリプランを取得するには:

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

Cloud Spanner のインスタンスに移動する
クエリを実行する Cloud Spanner インスタンスとデータベースの名前をクリックします。
[クエリ] をクリックします。
テキストフィールドにクエリを入力し、[クエリを実行] をクリックします。
[説明] をクリックします。
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）での読み取り専用トランザクションの実行時以降に値が変更されていないことを確認します。
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);