Cloud Spanner でインターリーブテーブルを高速に取得する

Published in

google-cloud-jp

16 min readDec 16, 2020

本記事ではインターリーブテーブルを使いこなす、ちょっとしたテクニックを紹介します。

TL;DR

Cloud Spanner では SELECT AS STRUCT 構文をサブクエリに使うことで、親のテーブルとインターリーブされたテーブルのレコードを一発で高速に取得することができます。更に Cloud Spanner の CPU 使用率も抑えることができるので非常に効率的にクエリできます。

はじめに

Cloud Spanner ではインターリーブと呼ばれる、あるテーブルのレコードの物理的な配置を別のテーブルのレコードの配下に置ける仕組みがあります。この仕組みを使ってテーブル間に親子関係を作ることで、複数のテーブル間に参照整合性制約を持たせたり、パフォーマンスを向上させることができます。

このインターリーブですが、親のテーブルと子のテーブルを両方一辺に取得したいと思ったことはないでしょうか？ユーザの様々な情報をまとめて返す API を作っていたり、正規化して複数のテーブルに分けている場合など、親子のテーブルの情報を同時に必要とする場面は多いと思います。

親子のテーブルからクエリする方法

例として、Singers という親のテーブルがあり、そのテーブルのインターリーブとして Albums テーブルと Concerts テーブルがある構成を考えます。

CREATE TABLE Singers (
  SingerId INT64 NOT NULL,
  FirstName STRING(1024) NOT NULL,
  LastName STRING(1024) NOT NULL,
) PRIMARY KEY (SingerId);CREATE TABLE Albums (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  Title STRING(1024) NOT NULL,
) PRIMARY KEY (SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;CREATE TABLE Concerts (
  SingerId INT64 NOT NULL,
  ConcertId INT64 NOT NULL,
  Price INT64 NOT NULL,
) PRIMARY KEY (SingerId, ConcertId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;# Sample Data
INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (1, "Nick", "Porter");
INSERT INTO Albums (SingerId, AlbumId, Title) VALUES (1, 1, "Total Junk"), (1, 2, "Nice Field");
INSERT INTO Concerts (SingerId, ConcertId, Price) values (1, 1, 3800), (1, 2, 5000), (1, 3, 4000);

これらの親子テーブルから、以下のように SingerId = 1 のレコードを全て取得したい場合、どうするのがいいでしょうか。

一番簡単に取得する方法は、親のテーブルと子テーブルをそれぞれ引いてくることでしょう。

SELECT * FROM Singers WHERE SingerId = 1;
SELECT * FROM Albums WHERE SingerId = 1;
SELECT * FROM Songs WHERE SingerId = 1;

しかしこの方法ではインターリーブしているテーブルの数に応じてクエリ数も増えてしまいます。また、せっかく物理的配置が近くなるようインターリーブで設計しているのに、クエリを分けていたら効率が悪そうです。

では以下のような JOIN で親子テーブルを結合して取得する方法はどうでしょうか。

SELECT * FROM Singers s
INNER JOIN Albums a ON s.SingerId = a.SingerId
INNER JOIN Concerts c ON s.SingerId = c.SingerId
WHERE s.SingerId = 1;

この方法であれば単発のクエリで取得できますが、全ての結合で同じ SingerId を結合条件として使用しているので、JOIN は直積となってしまい、JOIN を重ねれば重ねるほど結果のレコード数が増えてしまいます。

例えば、SingerId = 1 の Albums が 2レコード、Concerts が 3レコードあるような場合、上記のJOIN のクエリを投げると以下のように 2 * 3 = 6 レコード返ってきてしまいます。

これではテーブル数の増加、レコード数の増加に伴い結果セットが指数関数的に増えてしまうので、現実的には使用できない恐れがあります。

また結果が直積になることから、同じカラムの値が複数の行に含まれてしまっています。これでは見るからにクライアントから扱いづらそうですね。

サブクエリで解決

そこで使用できるのが SELECT AS STRUCT という構文のサブクエリを使う方法です。まずはどんなクエリになるか見てみましょう。

SELECT *,
  ARRAY(SELECT AS STRUCT * FROM Albums WHERE SingerId = 1) as Albums,
  ARRAY(SELECT AS STRUCT * FROM Concerts WHERE SingerId = 1) as Concerts,
FROM Singers WHERE SingerId = 1;

少し複雑なので分解して見てみます。まず SELECT AS STRUCT の部分です。

SELECT AS STRUCT * FROM Albums WHERE SingerId = 1

この SELECT 文ではインターリーブテーブルである Albums から対象のレコードを引っ張ってきています。SELECT AS STRUCT というのは結果セットを STRUCT 型で取得するものであり、以下のように単一のカラムに複数のフィールドをまとめて返すような構文です。

+------------------------------------------------+
| Value                                          |
+------------------------------------------------+
| {SingerId: 1, AlbumId: 1, Title: "Total Junk"} |
| {SingerId: 1, AlbumId: 2, Title: "Nice Field"} |
+------------------------------------------------+

先程「単一のカラムに」と言いましたが、実はこの SELECT AS STRUCT 構文は、カラムすらも存在しない Value Table と呼ばれる少し特殊な結果を返すものであり、そのままではトップレベルの SELECT 文の結果として扱うことができません。そのため ARRAY() で包んで ARRAY Subquery として結果を返してあげる必要があります。

ARRAY(SELECT AS STRUCT * FROM Albums WHERE SingerId = 1)

これでトップレベルの SELECT 文の1カラムとして扱うことが出来ます。あとはインターリーブされているテーブルの数だけ ARRAY(SELECT AS STRUCT …) を書いていけば ok です。

ARRAY(SELECT AS STRUCT * FROM Albums WHERE SingerId = 1) as Albums,
ARRAY(SELECT AS STRUCT * FROM Concerts WHERE SingerId = 1) as Concerts,
...

実際に最初のクエリを実行してみると以下のような結果になります。

見た目上は Albums と Concerts カラムが配列の配列になって返ってきているように見えますが、実際はクライアントから扱う時には STRUCT 内の各フィールドの型や名前を扱えるようになっています。

これはクエリの結果をメモリ上のモデルに変換する際に威力を発揮します。例えば Go の場合、Row.ToStruct を使えば spanner.Row を一発で Go の struct にマッピングできます。

type Singer struct {
  SingerID  int64      `spanner:"SingerId"`
  FirstName string     `spanner:"FirstName"`
  LastName  string     `spanner:"LastName"`
  Albums    []*Album   `spanner:"Albums"`
  Concerts  []*Concert `spanner:"Concerts"`
}type Album struct {
  SingerID int64  `spanner:"SingerId"`
  AlbumID  int64  `spanner:"AlbumId"`
  Title    string `spanner:"Title"`
}type Concert struct {
  SingerID  int64 `spanner:"SingerId"`
  ConcertID int64 `spanner:"ConcertId"`
  Price     int64 `spanner:"Price"`
}func decodeRow(row *spanner.Row) (*Singer, error) {
  var singer Singer
  err := row.ToStruct(&singer)
  return &singer, err
}

Singer, Album, Concertの struct に一発でマッピングができるのがわかると思います。

では最後に、クエリの実行計画も見てみましょう。まず JOIN を使った場合の実行計画は以下のようになります。

3つのテーブルを JOIN しているので、Cross Apply オペレータが2回実行されています。

ではサブクエリを使用した場合の実行計画も見てみましょう。

実行計画を見ると、ARRAY(SELECT AS STRUCT …) の部分は Array Subquery オペレータと呼ばれるサブクエリの一種として実行されています。Cloud Spanner では JOIN を使うと、1クエリの中で結合できるテーブルは15個までという制限が存在するのですが、今回はサブクエリなので15個以上のインターリーブされたテーブルがあってもその制限に引っかかることはありません。

ベンチマーク

では、ここまで説明してきた ARRAY(SELECT AS STRUCT) のサブクエリを使って親のテーブルと子のテーブルを一発で引っ張ってくるとどのくらい速くなるのでしょうか？

試してみましょう。

今回は以下のような親のテーブルが1つ、子のテーブルが4つの計5つのテーブルからデータを引っ張ってくることを考えます。

スキーマは PK 以外にカラムがないシンプルなものにします。

CREATE TABLE `Parent` (
  ParentId INT64 NOT NULL,
) PRIMARY KEY (ParentId);CREATE TABLE Child01 (
  ParentId INT64 NOT NULL,
  Child01Id INT64 NOT NULL,
) PRIMARY KEY (ParentId, Child01Id),
  INTERLEAVE IN PARENT `Parent` ON DELETE CASCADE;CREATE TABLE Child02 (
  ParentId INT64 NOT NULL,
  Child02Id INT64 NOT NULL,
) PRIMARY KEY (ParentId, Child02Id),
  INTERLEAVE IN PARENT `Parent` ON DELETE CASCADE;CREATE TABLE Child03 (
  ParentId INT64 NOT NULL,
  Child03Id INT64 NOT NULL,
) PRIMARY KEY (ParentId, Child03Id),
  INTERLEAVE IN PARENT `Parent` ON DELETE CASCADE;CREATE TABLE Child04 (
  ParentId INT64 NOT NULL,
  Child04Id INT64 NOT NULL,
) PRIMARY KEY (ParentId, Child04Id),
  INTERLEAVE IN PARENT `Parent` ON DELETE CASCADE;

比較対象は以下の2つのトランザクションです。

#1 全てのテーブルから1つずつ引いてくるパターン:

BEGIN RO;SELECT * FROM Parent WHERE ParentId = @id;
SELECT * FROM Child01 WHERE ParentId = @id;
SELECT * FROM Child02 WHERE ParentId = @id;
SELECT * FROM Child03 WHERE ParentId = @id;
SELECT * FROM Child04 WHERE ParentId = @id;

#2 全てのテーブルからサブクエリで一発で引いてくるパターン:

BEGIN RO;SELECT 
  *,
  ARRAY(SELECT AS STRUCT * FROM Child01 WHERE ParentId = @id) as c01,
  ARRAY(SELECT AS STRUCT * FROM Child02 WHERE ParentId = @id) as c02,
  ARRAY(SELECT AS STRUCT * FROM Child03 WHERE ParentId = @id) as c03,
  ARRAY(SELECT AS STRUCT * FROM Child04 WHERE ParentId = @id) as c04,
FROM Parent p WHERE ParentId = @id;

今回は Read-Only Transaction で実行するので、BEGIN RO; で Read 用のタイムスタンプを決定するようにします (最後に COMMIT は必要ありません)。これらのトランザクションは spanner-cli という CLI ツールから試すことが出来ます。

上記のスキーマのテーブルにあらかじめ100万行を入れておき、1 Node の Cloud Spanner インスタンスに対して 32 vCPU のクライアントマシンから並列にランダムにアクセスさせます。

ベンチマーク結果

ベンチマークの結果がこちらです。

上から順に、CPU 使用率、TPS (Transaction Per Second)、クエリ単位のレイテンシを表しています。またグラフの最初の山が各テーブルを1つずつ引いてくるパターン、次の山が全てのテーブルをサブクエリで一発で引いてくるパターンの結果です。どちらも High Priority な CPU 使用率が 65% くらいになるようにクライアントの並列度を調整してあります。

見ていただくとわかるように、どちらも大体同じ CPU 使用率を保ちながら、各テーブルを1つずつ引いてくる場合は約 4,000 TPS、サブクエリで一発で引いてくる場合は約 10,000 TPS と、一発で引いてくる方が約2.5倍のトランザクションを捌けていることがわかります。これは逆に言うと、同じ TPS であればサブクエリの方が CPU 使用率を低く抑えられるということです。

クエリ単位のレイテンシに関してはサブクエリで引いてくるパターンの方が少し遅いように見えますが、各テーブルを1つずつ引いてくる場合は計5回のクエリが走るので、トランザクション単位で見るとサブクエリで引いてくる方が速くなります。

この結果だけ見るとインターリーブされたテーブルが4つもあるからここまで差が出ているように見えるかもしれません。インターリーブされたテーブルが1つのみ(つまり親のテーブル合わせて2テーブル)の場合はどうなるでしょうか？

他の条件は全く同じにして、2テーブルから引いた場合のベンチマーク結果を以下に記載します。

先ほどと同じく、グラフの最初の山が親と子のテーブルを別々に引いてくるパターン、次の山が親と子のテーブルをサブクエリで一発で引いてくるパターンの結果です。見ていただくとわかるように、別々に引いてくる場合は約 8,000 TPS、サブクエリでまとめて引いてくる場合は約 13,000 TPS と、サブクエリの方が約1.6倍のトランザクションを捌けていることがわかります。インターリーブされたテーブルが1つだけの場合でも、親と子をサブクエリで一発で引いた方が十分効率的になりそうですね。

尚ベンチマーク結果はスキーマ、クエリ、テーブル数によっても異なってきますので、実際に試される場合はアプリケーションで使用しているスキーマとクエリを使って試してみて下さい。今回使用したベンチマークのコードはこちらにあります。

まとめ

親と子のテーブルを同時に参照したい時に、ARRAY(SELECT AS STRUCT) のサブクエリを使ってまとめて引いてくると、Cloud Spanner のCPU 使用率を抑えつつ、高速に、クライアントにとって扱いやすい形でクエリできることを見てきました。

是非この方法を使って Cloud Spanner の特徴であるインターリーブをより活用してみてください。

Acknowledgement

Takahiko Sato さん、記事のレビュー・コメントありがとうございました！