Vol. 16 インターン生が新規チームに入ってSpannerのクエリパフォーマンスチューニングに取り組んだ話

この記事は、Sansan Data Intelligence 開発Unit ブログリレーの第16弾です！！

こんにちは。2026年新卒内定者として、技術本部Data Intelligence Engineering Unit Data Intelligence Groupでインターンをしている山口です。

この記事では、インターン期間中にCloud Spannerのクエリパフォーマンスチューニングに取り組んだ経験をまとめます。

Cloud Spannerを使って気づいたこと

Sansan Data Intelligence (SDI)はデータベースとしてGoogle Cloudの分散データベースであるCloud Spannerを採用しています。

SDIはマルチテナントSaaSであり、大規模なデータの取り込み・書き出しを高速に実施する必要があります。 Spannerは水平スケーリングが可能でありながら、強整合性が保証されており、SDIの要件にマッチしたため、Spannerを採用しました。

（その他、SDIが採用している技術と選定理由は Vol.01 Sansanの4年ぶりの新規プロダクトにおける技術選定の意思と意図 - Sansan Tech Blog をご覧ください！）

一方で、Spannerはパフォーマンスに関して分散型ならではのいくつかの独特なポイントがあります。

実際に手を動かす中で、「SpannerはRDBと似ているようで、パフォーマンス特性がかなり違う」と感じることが多くありました。今回はその中でも特に印象に残ったクエリチューニングのパターンをいくつかご紹介します。

チューニングのパターン1：FORCE_INDEXによるインデックス強制

Spannerにはクエリオプティマイザが存在し、自動的に適切なインデックスを選択しようとします。しかし、実際にはオプティマイザの判断が最適でないケースが起きることがあります。

私が経験したケースでは、特定のカラムによる検索クエリがあったにもかかわらず、Spannerがフルスキャンを選択してしまい、簡単なクエリでレスポンスが数十秒に達してしまうことがありました。

こういった場面で有効なのがFORCE_INDEXヒントです。

-- インデックスを明示的に指定する
FROM some_table@{FORCE_INDEX=some_index_name}
WHERE ...

FORCE_INDEXを追加することで、意図したインデックスを使わせることができます。

ただ、FORCE_INDEXは一般的なRDBではアンチパターンとして知られています。しかし、統計情報による影響やレコード数の増え方によっては、クエリオプティマイザが間違った判断をする場合もあり、FORCE_INDEXを使うことも有効だと学びました。

特にSpannerは固有の事情として、統計情報の更新頻度が低頻度です。 他のRDBでは、約1日ごとやテーブル行数の10%が変更されたタイミングで統計情報が更新されることが多いかと思いますが、Spannerの統計情報はバックグラウンドで約3日ごとの更新となっています。 (参考: Spanner のクエリ オプティマイザーの詳細 | Google Cloud 公式ブログ)

このため、SDIのように短期間で大量のデータを扱うプロダクトでは、統計情報が実態と乖離しやすく、オプティマイザが間違った判断をしてしまうことに繋がります。 こういった背景もあり、現在SDIでは特に検索条件が複数あるようなクエリではFORCE_INDEXを追加するようにしています。

チューニングのパターン2：インデックスへのSTORINGカラム追加

Spannerのインデックスは、デフォルトではPRIMARY KEYとインデックスの定義カラムのみを保持しています。クエリがインデックスに含まれていないカラムを参照する場合、Spannerはインデックスを引いた後にベーステーブルへの追加のJOIN処理を行います。

このJOINを避けるためのテクニックが、STORING句を使ったインデックスへのカラム追加です。

-- カバリングインデックスを作る
CREATE INDEX some_index ON some_table(col1, col2) STORING (col3);

-- 既存インデックスへの追加も可能
ALTER INDEX some_index ADD STORED COLUMN col3;

STORINGに必要なカラムを追加しておくことで、インデックスオンリーのスキャンでクエリが済むようになり、ベーステーブルへのアクセスが不要になります。

今回のパフォーマンスチューニングでは、1秒かかっていたクエリが30ms程度に下がる結果につながりました。

なぜSpannerではSTORINGが特に有効なのか

カバリングインデックスの使用だけでここまでクエリが速くなるのは、Spannerの強みでもある分散データベースであることが影響しています。 Spannerのデータは「スプリット*1」と呼ばれる単位に分割され、複数のサーバーに分散して配置されています。そして、実はインデックスは内部的には別のテーブルとして管理されています。つまり、ベーステーブルとは別のスプリット群として管理されているため、インデックスを引いた後にベーステーブルを読みに行く場合、別のサーバーへ取得しに行く可能性があります。このため、ベーステーブルへ追加でアクセスするコストが分散型でないRDBより大きくなります。

PostgreSQLなどのRDBでもカバリングインデックスはありますが、Spannerはこのような固有の事情があるため、よりカバリングインデックスが重要なようです。

チューニングのパターン3：全文検索インデックス・WITH句

Spannerには全文検索のためのSearch Indexという機能があります。通常のSecondary Indexがカラムの値をそのままインデックスとして保持するのに対し、Search Indexはテキストをトークン化して転置インデックスを構築します。これによりSEARCH_SUBSTRINGなどの関数で部分一致検索が可能になります。

Search Indexはテキストのトークン列を保持する専用カラム（TOKENLIST 型）をインデックス対象とします。定義はCREATE SEARCH INDEX構文で行います。

-- まずテーブルに TOKENLIST 型カラムを追加しておく
ALTER TABLE table_name
  ADD COLUMN tokens TOKENLIST
    AS (TOKENIZE_SUBSTRING(text_column)) HIDDEN;

-- Search Index を作成する
CREATE SEARCH INDEX full_text_idx
  ON table_name(tokens);

-- クエリでは SEARCH_SUBSTRING を使って部分一致検索
SELECT id
FROM table_name
WHERE SEARCH_SUBSTRING(tokens, @keyword);

また、Search Indexを定義する際にPARTITION BYを指定することも重要でした。データが論理的な単位ごとに独立している場合は、その単位でパーティションを切ることで検索効率が大幅に向上します。

-- PARTITION BY でジョブごとにパーティション分割
CREATE SEARCH INDEX full_text_idx
  ON table_name(tokens)
  PARTITION BY job_id;

ちなみに、PARTITION BY句を使う場合はON句ではなく、WHERE句側でやらないとエラーになります。（罠）

-- これはエラーになる
-- エラー: SEARCH_SUBSTRING is not supported in this query. Possible reasons are: There is no appropriate search index to use, or the function is used in unsupported query shapes. You can get more details on why a specific search index cannot be used by adding @{force_index} hint to the query.
SELECT *
FROM table_a
LEFT JOIN table_b@{FORCE_INDEX=full_text_idx}
ON table_a.id = table_b.id AND table_a.job_id = table_b.job_id
WHERE table_a.job_id = @job_id AND SEARCH_SUBSTRING(table_b.tokens, @keyword)

-- こう書く必要がある
SELECT *
FROM table_a
LEFT JOIN table_b@{FORCE_INDEX=full_text_idx}
ON table_a.id = table_b.id
WHERE
  table_a.job_id = @job_id
  AND table_b.job_id = @job_id
  AND SEARCH_SUBSTRING(table_b.tokens, @keyword)

チューニングのパターン4：WITH句とARRAYサブクエリによるクエリ構造の整理

複数テーブルをJOINして集計してORDER BYで並び替えるような複雑なクエリでは、GROUP BY + LEFT JOINの組み合わせで思ったよりパフォーマンスが出ないことがありました。

このパターンで扱ったテーブル構造は、次のような「ジョブとその処理結果明細」の親子関係です。

-- 親テーブル：ジョブの実行結果（1ジョブにつき1レコード）
CREATE TABLE parent_table (
  id             STRING(36)  NOT NULL,
  foreign_key_id STRING(36)  NOT NULL,  -- 外部キー（どのジョブに紐づくか）
  some_value     STRING(256),
  is_success     BOOL        NOT NULL,
  some_sort_key  STRING(36)  NOT NULL,
  created_at     TIMESTAMP   NOT NULL,
) PRIMARY KEY (id);

-- 子テーブル：結果の明細（1親レコードにつき0〜N件）
CREATE TABLE child_table (
  parent_id  STRING(36)  NOT NULL,  -- 親テーブルの id
  sequence   INT64       NOT NULL,  -- 明細の順序
  code       STRING(64)  NOT NULL,
  message    STRING(512) NOT NULL,
) PRIMARY KEY (parent_id, sequence),
  INTERLEAVE IN PARENT parent_table ON DELETE CASCADE;

child_tableはparent_tableにインターリーブしています。Spannerのインターリーブとは、親行と子行を物理的に同じスプリットに配置する仕組みです。 異なるスプリットに属する行をJOINするには、サーバー間のリモートアクセスが発生します。

ここで役立つのがインターリーブです。INTERLEAVE IN PARENTを指定すると、親行と子行が同じキープレフィックスを持つ限り、物理的に同じスプリットに配置されます。つまり、親行と子行をサーバー間通信なしにローカルで完結して取得することができます。

さて、話を戻して元々のクエリは次のような構造でした。親テーブルと子テーブルをLEFT JOINし、GROUP BY で親レコードごとにまとめつつ、ARRAY_AGGで子レコードのカラムを配列として集約するアプローチです。

-- 元のクエリ
SELECT
  parent.id,
  ANY_VALUE(parent.some_value) AS some_value,
  ANY_VALUE(parent.is_success)  AS is_success,
  COALESCE(
    ARRAY_AGG(child.code    IGNORE NULLS ORDER BY child.sequence),
    ARRAY<STRING>[]
  ) AS codes,
  COALESCE(
    ARRAY_AGG(child.message IGNORE NULLS ORDER BY child.sequence),
    ARRAY<STRING>[]
  ) AS messages,
 CASE
    WHEN status = 'STATUS_FAILED'  THEN 1
    WHEN status = 'STATUS_SUCCESS' THEN 2
    ELSE                                3
  END AS sort_param,
FROM
  parent_table@{FORCE_INDEX=some_index} AS parent
LEFT JOIN child_table AS child
  ON child.parent_id = parent.id
WHERE parent.foreign_key_id = @foreign_key_id
GROUP BY
  parent.id
ORDER BY
  sort_param ASC,
  parent.some_sort_key ASC
LIMIT @limit OFFSET @offset

代替として効果的だったのが、CTE（WITH 句）でベースデータを取得し、ARRAYサブクエリで1対多の子データを内包するアプローチです。

-- CTE でメインテーブルのデータを先に取得（子レコードは ARRAYサブクエリで内包）
WITH base AS (
  SELECT
    parent.id,
    parent.some_value,
    parent.is_success,
    ARRAY(
      SELECT AS STRUCT child.sequence, child.code, child.message
      FROM child_table child
      WHERE child.parent_id = parent.id
      ORDER BY child.sequence ASC
    ) AS children
  FROM parent_table@{FORCE_INDEX=some_index} AS parent
  WHERE parent.foreign_key_id = @foreign_key_id
)
-- その後 baseを集計・ソート
SELECT
  id,
  some_value,
  ARRAY(SELECT code    FROM UNNEST(children) ORDER BY sequence) AS codes,
  ARRAY(SELECT message FROM UNNEST(children) ORDER BY sequence) AS messages,
  CASE
    WHEN is_success                              THEN 'STATUS_SUCCESS'
    WHEN children[SAFE_OFFSET(0)].code = 'SKIP' THEN 'STATUS_SKIPPED'
    ELSE                                              'STATUS_FAILED'
  END AS status
FROM base
ORDER BY
  CASE
    WHEN status = 'STATUS_FAILED'  THEN 1
    WHEN status = 'STATUS_SUCCESS' THEN 2
    ELSE                                3
  END ASC,
  some_sort_key ASC
LIMIT @limit OFFSET @offset

データ量がまだ少なかったためインパクトとしてわずかではありますが、クエリに300~400ms程度かかっていたところから150ms〜250ms程度に短縮されました。

今回はFORCE_INDEX=some_indexでインデックスの読み取りを強制するように親テーブルを設定していますが、FORCE_INDEX=_BASE_TABLEとしてフルスキャンした方がインターリーブを最大限活かせてより速い可能性があります。前述の通りインデックスは別テーブル管理のため、子テーブルのデータは持っていません。つまりインデックスを使ってしまうとインターリーブの強みを最大限活かせません。実際に今回のケースだと、さらにクエリ実行が70ms~100ms程度に短縮されることを確認しています。

しかし、親テーブル（parent_table）に入っている行数が今後多くなる見込みであることと、絞り込み条件が実際はもう少し複雑なため、将来的に悪化してしまう可能性を考慮して、今回はインデックスを使う判断に倒しています。これは今後よりテーブルの行数が増えていったときに、どう変化するか改めて確認してみたいと考えています。

JOIN + GROUP BYのクエリ、CTE + ARRAYのクエリのいずれにおいても、最大CPU時間・最大レイテンシともに、子テーブルであるchild_tableの取得がボトルネックでした。 興味深いことに、JOIN + GROUP BYクエリだと100msほどかかっているのですが、CTE + ARRAYクエリだと60msほどに短縮されました。 これはインターリーブの強みを活かせているとみています。インデックスを使うようにFORCE_INDEXを親テーブルに設定していますが、すべての使うカラムをSTORINGでインデックスに追加していないため、ベーステーブルへのアクセスが必要になっています。 そのため、今回のようにインデックスを使っていてもインターリーブの強みが生きているのだと考えられます。

また、JOIN + GROUP BYクエリとCTE + ARRAYクエリのもう1つの大きな違いとして、Hash aggregateの有無です。Hash aggregateはGROUP BYや集約関数を使うと行われる処理です。Spannerではより高速な処理として、Stream aggregateがあるのですが、これはキー列もしくはインデックスに列があり、並び替え済みの入力が必要になります。今回のケースだとソート順序を動的に決定する必要があり、ソート順序のインデックスを作成できず、Stream aggregateで処理される条件が揃っていませんでした。このHash aggregateに60ms~70msかかっており、さらに2回実行されていたため、合計で100ms~120msも処理に時間がかかっていました。CTE + ARRAYクエリだとGROUP BYを使用していないため、その分だけ速くなったようです。

チューニングに取り組む上で大切にしたこと

今回のチューニング作業を通じて、Spannerでのクエリチューニングに限らず、いくつか「これは大事」と感じた考え方があります。

① 実行計画を必ず確認する

クエリが速い・遅いの前に、なぜそうなっているかを実行計画で確認することが基本で、SpannerではCloud ConsoleのQuery Insightsが非常に役立ちました。フルスキャンが起きているかどうか、どのインデックスが使われているかが視覚的に確認できます。

② 変更前後の数値を記録する

「なんとなく速くなった気がする」ではなく、Before / Afterの実行時間を数値で記録するようにしました。PRの説明にも実行計画のスクリーンショットと計測結果を添付し、レビュアーに判断材料を渡すことを意識しました。

③ 「最善手ではないが今はこれで十分」という判断も大切

例えば、集計値をテーブルに永続化すれば根本的に速くなるケースでも、「今のデータ量ならサブクエリで都度集計しても十分速い」という判断をすることもありました。過剰なパフォーマンス最適化はコードを複雑にするため、現状の要件に対して十分な性能を出すことを判断軸にするのが重要だと感じました。

また、現在のデータ量では最速だが、増えた時にかえって性能が悪化する可能性も考慮して判断することが重要だと感じました。

インターンを通じて感じたこと

私はプロダクト開発チームの立ち上げから参加させていただいたこともあり、企業での0→1を内定者期間中に体験できたのは、とてもいい体験でした。「自分が書いたコードが実際に動くプロダクトとしてリリースされる」という感覚は、学生プロジェクトとは全く違う緊張感と達成感がありました。

また、チームの方々がレビューで「なぜこのアプローチを選んだのか」「他の選択肢はどうか」と丁寧に問いかけてくれたことで、技術的な判断の引き出しが増えた気がします。

おわりに

Cloud Spannerのパフォーマンスチューニングは、一般的なRDBの知識が前提になりつつも、Spanner固有の仕様を理解しないと的外れなアプローチになりやすい分野だと感じました。Spannerを使う方の参考になれば幸いです。

インターンとしてこのような経験ができたことにチームに感謝しています。入社後もこのチームでプロダクトを育てていけるのが楽しみです。

Sansan技術本部ではカジュアル面談を実施しています

Sansan技術本部では中途の方向けにカジュアル面談を実施しています。Sansan技術本部での働き方、仕事の魅力について、現役エンジニアの視点からお話しします。「実際に働く人の話を直接聞きたい」「どんな人が働いているのかを事前に知っておきたい」とお考えの方は、ぜひエントリーをご検討ください。