SQL のベストプラクティス

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

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

GoogleSQL

CREATE TABLE Singers (
 SingerId   INT64 NOT NULL,
 FirstName  STRING(1024),
 LastName   STRING(1024),
 SingerInfo BYTES(MAX),
 BirthDate  DATE
) 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 の詳細なリファレンスについては、ステートメントの構文、関数と演算子、語彙の構造と構文をご覧ください。

PostgreSQL

CREATE TABLE Singers (
 SingerId   BIGINT PRIMARY KEY,
 FirstName  VARCHAR(1024),
 LastName   VARCHAR(1024),
 SingerInfo BYTEA,
 BirthDate  TIMESTAMPTZ
);

CREATE TABLE Albums (
 SingerId        BIGINT NOT NULL,
 AlbumId         BIGINT NOT NULL,
 AlbumTitle      VARCHAR(1024),
 ReleaseDate     DATE,
 PRIMARY KEY(SingerId, AlbumId),
 FOREIGN KEY (SingerId) REFERENCES Singers(SingerId)
) INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

詳細については、Cloud Spanner の PostgreSQL 言語をご覧ください。

クエリパラメータを使用する

Spanner はクエリパラメータをサポートしています。これにより、パフォーマンスが向上し、ユーザー入力を使用してクエリが作成されるときに SQL インジェクションを防ぐことができます。クエリパラメータは、任意の式の代わりに使用できますが、識別子、列名、テーブル名、またはクエリの他の部分の代わりとして使用することはできません。

パラメータは、リテラル値が期待されている場所であればどこにでも現れることができます。単一の SQL ステートメントで同じパラメータ名を複数回使用できる。

まとめると、クエリパラメータは次の方法でのクエリ実行をサポートします。

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

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

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

クエリ実行プランは、Google Cloud コンソールまたはクライアントライブラリで取得できます。

Google Cloud コンソールを使用して特定のクエリのクエリ実行プランを取得する手順は次のとおりです。

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

Spanner インスタンスに移動
クエリを実行する Spanner インスタンスとデータベースの名前を選択します。
左側のナビゲーションパネルで [クエリ] をクリックします。
テキストフィールドにクエリを入力し、[クエリを実行] をクリックします。
[説明] をクリックします。
Google Cloud コンソールにクエリの実行プランが表示されます。

ビジュアルプランを理解し、それらを使用してクエリをデバッグする方法の詳細については、クエリプランビジュアライザを使用したクエリのチューニングをご覧ください。

また、過去のクエリプランのサンプルを表示し、特定のクエリに対するクエリのパフォーマンスの推移を比較することもできます。詳細については、サンプリングされたクエリプランをご覧ください。

セカンダリインデックスを使用する

他のリレーショナルデータベースと同様に、Spanner でもセカンダリインデックスが提供されます。これによって、SQL ステートメントまたは Spanner の読み取りインターフェースを使用してデータを取得できます。インデックスからデータをフェッチする最も一般的な方法は、SQL クエリインターフェースを使用する方法です。SQL クエリでセカンダリインデックスを使用すると、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 が含まれるため、Spanner で Singers テーブル全体にスキャンを実行する代わりに、非常に小さいインデックステーブルからすべてのデータをフェッチできます。

このシナリオでは、クエリを実行するとき（データベースの作成から 3 日間がすぎている場合。新しいデータベースに関する注意事項を参照）Spanner は自動的にセカンダリインデックス SingersByLastName を使用します。しかし、FROM 句でインデックスディレクティブを指定して、そのインデックスを使用するように Spanner に明示的に指示する方法をおすすめします。

GoogleSQL

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

PostgreSQL

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

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

GoogleSQL

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

PostgreSQL

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

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

Spanner でベーステーブルから行を取得する必要がまったくない場合、FirstName 列のコピーをインデックス自体に格納できます。

GoogleSQL

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

PostgreSQL

CREATE INDEX SingersByLastName ON Singers (LastName) INCLUDE (FirstName);

このように STORING 句（GoogleSQL 言語）または INCLUDE 句（PostgreSQL 言語）を使用すると、追加のストレージ費用がかかりますが、次のような利点があります。

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

上述の例は、クエリの 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);

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

GoogleSQL

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

PostgreSQL

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums /*@ FORCE_INDEX=AlbumsByReleaseDateTitleDesc */ AS s
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

このクエリとインデックス定義は次の両方の基準を満たすものです。

並べ替えの手順を削除するには、ORDER BY 句の列リストがインデックスキーリストの接頭辞であることを確認します。
不足している列を取得するためにベーステーブルから結合しないようにするため、クエリで使用されるテーブル内のすべての列をインデックスでカバーするようにします。

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

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

範囲キーのルックアップを最適化する

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

次のベストプラクティスは、キーの範囲でデータをフェッチする場合に効率的なクエリの作成に役立ちます。

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

たとえば、キーのリストが {1, 5, 1000} の場合、次のようなクエリを作成します。
GoogleSQL
```
SELECT *
FROM Table AS t
WHERE t.Key IN UNNEST (@KeyList)
```
PostgreSQL
```
SELECT *
FROM Table AS t
WHERE t.Key IN UNNEST ($1)
```
メモ:
- 配列 UNNEST 演算子は、入力配列を要素の行にフラット化します。
- 前述のベストプラクティスで説明したとおり、クエリパラメータ（GoogleSQL の場合は @KeyList、PostgreSQL の場合は $1）を使用すると、クエリを高速化できます。
キーのリストが連続して範囲内である場合、WHERE 句でキーの範囲の下限と上限を指定します。

たとえば、キーのリストが {1,2,3,4,5} の場合、次のようにクエリを作成します。
GoogleSQL
```
SELECT *
FROM Table AS t
WHERE t.Key BETWEEN @min AND @max
```
注: ここで @min と @max は、値 1 と値 5 にそれぞれバインドされるクエリパラメータです。
PostgreSQL
```
SELECT *
FROM Table AS t
WHERE t.Key BETWEEN $1 AND $2
```
注: ここで $1 と $2 は、値 1 と値 5 にそれぞれバインドされるクエリパラメータです。
このクエリはキー範囲内のキーが連続している場合にのみより効率的です。言い換えると、キーのリストが {1, 5, 1000} の場合、作成されるクエリで 1 ～ 1000 のすべての値がスキャンされるため、上記のクエリのように上限と下限を指定しないでください。

結合を最適化する

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

可能な限り、インターリーブされたテーブルのデータを主キーによって結合する。例:
```
SELECT s.FirstName, a.ReleaseDate
FROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;
```
スキーマとデータモデルで説明しているとおり、インターリーブされたテーブル Albums 内の行は、Singers の親行と同じスプリットに物理的に保存されることが保証されます。したがって、ネットワークを介して大量のデータを送信することなく、結合をローカルで実行できます。
結合の順序を強制する場合、結合ディレクティブを使用します。例:
GoogleSQL
```
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%';
```
PostgreSQL
```
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 は Spanner に、クエリで指定された結合順序（つまり、Albums JOIN Singers ではなく Singers JOIN Albums）を使用するように指示します。ただし、クエリプランで Spanner によって結合順序が変更され、大量の中間結果や行検索の機会消失などの望ましくない結果が生じたことに気付いた場合、この結合ディレクティブを使用します。
結合ディレクティブを使用して、結合の実装を選択します。SQL を使用して複数のテーブルにクエリを実行する場合、Spanner はクエリを効率化する可能性のある結合メソッドを自動的に使用します。ただし、さまざまな結合アルゴリズムを使用してテストすることをおすすめします。適した結合アルゴリズムを選択することで、レイテンシを短縮できるか、メモリ使用量を削減できるか、あるいはその両方を実現できます。このクエリは、JOIN ディレクティブを JOIN_METHOD ヒントとともに使用して HASH JOIN を選択する構文を示しています。
GoogleSQL
```
SELECT *
FROM Singers s JOIN@{JOIN_METHOD=HASH_JOIN} Albums AS a
ON a.SingerId = a.SingerId
```
PostgreSQL
```
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 にあるので役立ち、Spanner は常に左側のテーブルがビルドとして、右側のテーブルがプローブとして選択します。同様に、APPLY JOIN の場合、Spanner は左側のテーブルを外側として、右側のテーブルを内側として選択します。これらの結合タイプの詳細については、ハッシュ結合と適用結合をご覧ください。
ワークロードに不可欠なクエリの場合は、より安定したパフォーマンスのために、SQL ステートメントで最もパフォーマンスの高い結合方法と結合順序を指定します。

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

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

Spanner のロックのしくみによって、大量の行を読み取る必要がある読み取りまたは SQL クエリの実行（たとえば、SELECT * FROM Singers）は、トランザクションが commit されるか中止されるまで、読み取られた行に他のトランザクションが書き込めないことを意味します。

さらに、トランザクションで大量の行を処理しているため、小規模な範囲の行を読み取るトランザクション（たとえば、SELECT LastName FROM Singers WHERE SingerId = 7）よりも時間がかかり、それによって問題が悪化し、システムのスループットが低下します。

つまり、書き込みスループットの低下を許容しない限り、トランザクションで大量の読み取り（たとえば、全テーブルスキャンまたは大量の結合オペレーション）を避けるようにしてください。

場合によっては、次のパターンでより良い結果が得られることがあります。

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

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

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

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

SELECT * FROM Singers
ORDER BY SingerId;

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

SELECT * FROM Singers;

Spanner では、このクエリの結果が主キー順になるとは限りません。さらに、結果の順序はいつでも変わる可能性があり、異なる呼び出しの間での整合性は保証されません。クエリに ORDER BY 句があり、Spanner が必要なオーダーを提供するインデックスを使用する場合、Spanner はデータを明示的に並べ替えません。そのため、この句を含めることによるパフォーマンスへの影響を心配する必要はありません。クエリプランを調べることで、実行に明示的な並べ替えオペレーションが含まれているかどうかを確認できます。

LIKE の代わりに STARTS_WITH を使用する

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

LIKE パターンの形式が foo%（たとえば、固定文字列で始まり、単一のワイルドカードパーセントで終わる）で、列にインデックスが付けられている場合、LIKE の代わりに STARTS_WITH を使用します。このオプションを使用すると、Spanner はクエリ実行プランをより効果的に最適化できます。

非推奨:

GoogleSQL

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

PostgreSQL

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE $1;

推奨:

GoogleSQL

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

PostgreSQL

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, $2);

commit タイムスタンプを使用する

特定の時間後に書き込まれたデータをアプリケーションでクエリする必要がある場合は、commit タイムスタンプ列を関連するテーブルに追加します。commit タイムスタンプを使用すると、特定の時間よりも後に書き込まれた行に対して、WHERE 句が結果を制限するクエリの I/O を減らすことができる Spanner 最適化が可能になります。

この詳細については、GoogleSQL 言語データベースまたは PostgreSQL 言語データベースを使用した最適化をご覧ください。

SQL のベスト プラクティス

GoogleSQL

PostgreSQL

クエリ パラメータを使用する

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

セカンダリ インデックスを使用する

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

範囲キーのルックアップを最適化する

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

結合を最適化する

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

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

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

LIKE の代わりに STARTS_WITH を使用する

GoogleSQL

PostgreSQL

GoogleSQL

PostgreSQL

commit タイムスタンプを使用する

SQL のベストプラクティス

クエリパラメータを使用する

セカンダリインデックスを使用する