クラウド Mac で検証する iOS SQLite の複合インデックスと走査予算

テスト用データベースでは一瞬で開いていた顧客の注文一覧が、過去データを取り込んだ途端に重くなる。クラウド Mac で複数案件を納品する受託開発チームなら、マシンを何度も替えて処理時間を測るより、まず固定データを使った実行計画の検証を受け入れ基準にしたいところです。固定データならインデックスの不足を切り分けやすくなります。一方、マシンを替えた計測では、キャッシュや負荷の変動を最適化の成果と取り違えがちです。本記事では、注文一覧のクエリを例に、コマンドラインでの事前検証から iOS アプリの実際の接続での再確認まで、一連の手順を組み立てます。

一、インデックスを検討する前にクエリの仕様を固定する

一覧の取得条件を、特定の顧客の完了済み注文のみと定めます。作成日時の降順に並べ、日時が同じ場合は ID の降順とし、1 回の取得件数は最大 20 件にします。テストでは処理時間だけでなく、絞り込み、並び順、件数をすべて確認します。

パラメーターは、注文が存在する顧客、存在しない顧客、注文が極端に集中している顧客の 3 パターンを用意します。少量で均等に分布したデータはロジックの検証には向いていますが、実際の業務データの分布を代表するものではありません。本番データでは、少数の大口顧客がレコードの大半を占めることもあります。

sqlite_version()、テーブル定義、インデックス定義、統計情報の生成方法を記録します。Mac 標準のコマンドラインツールとアプリでは、使用するエンジンが異なる可能性があります。特にプロジェクト側で SQLite を組み込んでいる場合、両者の実行計画を同じ検証結果として扱ってはいけません。

実行計画はオプティマイザーの判断結果であり、安定したテキストインターフェースではありません。出力全体の完全一致を、バージョンをまたぐ合否判定の条件にしないでください。

二、顧客のデータベースに触れないフィクスチャを作る

次のスクリプトをターミナルで実行します。独立した一時ディレクトリを作成し、本番データベースには接続しません。データの ID は固定されているため、繰り返し実行しても論理的に同じ内容が得られます。納品時に再実行しやすいよう、SQL ファイルとデータベースは同じ場所に保存します。

set -eu
work="$(mktemp -d)"
cat > "$work/check.sql" <<'SQL'
.bail on
SELECT sqlite_version();
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  created_at INTEGER NOT NULL
);
WITH RECURSIVE seq(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM seq WHERE x < 10000
)
INSERT INTO orders
SELECT x, x % 100,
       CASE WHEN x % 3 = 0 THEN 'done' ELSE 'open' END,
       x / 10
FROM seq;
EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE customer_id = 7 AND status = 'done'
ORDER BY created_at DESC, id DESC LIMIT 20;
CREATE INDEX orders_customer_status_time
ON orders(customer_id, status, created_at DESC, id DESC);
ANALYZE;
EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE customer_id = 7 AND status = 'done'
ORDER BY created_at DESC, id DESC LIMIT 20;
SQL
sqlite3 "$work/fixture.sqlite" < "$work/check.sql"
printf '%s\n' "$work"

まずターミナルの出力を保存し、その後で実際のクエリを実行して結果を確認します。ここでは、第 2 ソートキーが確実に機能するかを検証するため、作成日時が同じレコードを意図的に作っています。最新の 1 件だけを確認していると、同じ日時のレコード間で並び順が揺れる問題を見逃します。

三、計画の文字列ではなくアクセスパスを読む

インデックスがない状態では、通常、注文テーブルの走査と、並べ替え用の一時構造の作成が計画に現れます。インデックスを作成した後は、顧客とステータスを使って検索しているか、追加の並べ替えがまだ必要かを確認します。具体的な表記は SQLite のバージョンによって変わるため、常に同じ出力になると想定してはいけません。

複合インデックスでは、等価条件に使う列を先に置き、その後にソート対象の列を続けます。狙いは、エンジンが対象範囲に沿って先頭の 20 件を読み取れるようにすることです。このインデックスは、本例で取得する列もすべてカバーしています。ただし、後からサイズの大きい列を取得対象に加えると、カバリングが成立しなくなる場合があります。その際は過去の報告を流用せず、改めて受け入れ検証を行います。

SCAN が出ただけで不合格にしてはいけません。カバリングインデックスを順番に走査することと、大きなテーブルを 1 行ずつ読みながら絞り込むことは別です。逆に、SEARCH が出ても処理量が少ないとは限りません。選択性の低い条件では、多数のレコードを読む可能性が残ります。

インデックスは頻繁に使う一覧の最適化に適していますが、すべての列に無差別に追加すべきものではありません。書き込みとストレージのコストが増えるためです。変更を提出する際は、挿入、更新、削除のテストも実行し、読み取りの最適化によって納品要件の予算を超えていないか確認します。

四、実際の接続で走査予算を確認する

コマンドラインでの検証に通ったら、フィクスチャをアプリのテスト環境に取り込み、プロジェクトで実際に使用しているデータベースのラッパー経由で、同じパラメーター化クエリを実行します。バインドするパラメーターの型は統一してください。また、ステートメントは最後まで読み切る必要があります。最初の 1 行を取得した時点で計測を打ち切ってはいけません。

下位の接続にアクセスできる場合は、実行前に sqlite3_stmt_status で SQLITE_STMTSTATUS_FULLSCAN_STEP をゼロにリセットし、実行後に値を読み取ります。さらに、SQLITE_STMTSTATUS_SORT でソート操作の回数を確認します。前者は全表走査に関連するステップ数を数えます。後者は処理時間でも、ソートした行数でもありません。

この 2 つのカウンターがどちらもゼロでも、クエリのコストがゼロとは限りません。大量のインデックスアクセスが発生している可能性はあります。必要に応じて SQLITE_STMTSTATUS_VM_STEP も記録し、取得件数と合わせて判断します。ラッパーがステートメントハンドルを公開していない場合は、まずテスト用のアダプター層を追加してください。アプリケーションログから処理時間を抽出しても、走査統計の代わりにはなりません。

予算は、固定フィクスチャでレビュー済みのベースラインから定めます。一般的な閾値をそのまま流用してはいけません。計測のたびにカウンターをリセットし、プリペアドステートメントの再利用によって前回の実行分が加算されないようにします。

五、データ分布の変化を納品前の確認に組み込む

受け入れ検証用には、少なくとも 2 種類のデータベースを残します。均等に分布したデータベースで基本的なアクセスパスを確認し、偏りのあるデータベースで大口顧客を再現します。どちらでも、上限 20 件、安定した並び順、結果が空の場合、ステータスパラメーターを変えた場合を確認します。

統計情報は実行計画に影響します。新規作成したデータベースと、ANALYZE 実行後の結果をそれぞれ記録してください。製品の実行時に統計情報を生成しないのであれば、統計情報がそろった理想的な状態だけで受け入れ検証を済ませてはいけません。また、見栄えのよい計画を得るためにインデックスを強制指定し、実際に選ばれるアクセスパスを隠すことも避けます。

最後に、SQL、エンジンのバージョン、データ分布の説明、インデックス定義、実行計画、カウンターの値を保存します。新たなリグレッションが発生したら、まずこれらの記録を比較し、その後で実行時間を確認します。クラウド Mac を変更した後や、プロジェクトの納期が延びた後でも、同じフィクスチャを根拠に、変更によってアクセスパスが変わったのか、それとも単にキャッシュが温まった状態に当たっただけなのかを説明できます。

よくある質問