【SQLite】インデックスとEXPLAIN QUERY PLANの読み方

SQL
2023.06.16

こんにちは、かつコーチです。

ここからは上級者向けの内容として、インデックスとBスツリー、EXPLAIN QUERY PLANの読み方といった前提知識を踏まえて解説していきます。

トランザクションとロックの挙動を理解したところで、今回はクエリを速くするための具体的な武器、インデックスの効かせ方とその検証方法を扱います。

SQLiteはインデックスの仕組み自体は他のRDBMSと大きく変わりませんが、確認手段や運用上のクセに独自性があります。

SQLiteにおけるインデックスの基礎

暗黙的に作られるインデックス

SQLiteでは、PRIMARY KEYやUNIQUE制約を付けたカラムに対して、自動的にインデックスが作成されます。

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,      -- 自動でインデックス相当の扱いになる
  order_code TEXT UNIQUE,      -- UNIQUE制約でインデックスが自動作成される
  member_id INTEGER
);

ただしINTEGER PRIMARY KEYは、SQLite内部で行の識別子であるrowidそのものとして扱われる特別なケースです。

これは単なるインデックス以上に、テーブルの物理構造そのものと直結しているため、最も高速にアクセスできるカラムになります。

一方member_idのような外部キーに相当するカラムには、明示的にインデックスを作らない限りインデックスは付きません。

CREATE INDEX idx_orders_member_id ON orders (member_id);

MySQLでは外部キー制約を付けると自動でインデックスが作られますが、SQLiteでは外部キー制約を付けてもインデックスは自動作成されない点に注意が必要です。

複合インデックスとカラムの並び順

複数カラムにまたがる複合インデックスを作る場合、カラムの並び順がそのままインデックスの使われやすさに直結します。

CREATE INDEX idx_orders_member_status ON orders (member_id, status);

-- このインデックスが効くクエリ
SELECT * FROM orders WHERE member_id = 10 AND status = 'shipped';
SELECT * FROM orders WHERE member_id = 10;

-- statusだけの絞り込みにはこのインデックスは効かない
SELECT * FROM orders WHERE status = 'shipped';

左側のカラムから順に絞り込みが行われる仕組み（左方一致）なので、複合インデックスを設計する際は、検索条件で必ず使われるカラムを左に置くのが基本方針です。

EXPLAIN QUERY PLANの読み方

インデックスが実際に使われているかを確認する

インデックスを作ったつもりでも、実際のクエリで使われているとは限りません。

その確認に使うのがEXPLAIN QUERY PLANです。

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE member_id = 10 AND status = 'shipped';

QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_member_status (member_id=? AND status=?)

SEARCH ... USING INDEXと表示されていれば、そのインデックスが使われていることを意味します。

一方、インデックスが使われず全件走査になっている場合は、以下のように表示されます。

QUERY PLAN
`--SCAN orders

SCANはテーブル全体を先頭から走査していることを示しており、行数が多いテーブルではこれがパフォーマンス劣化の直接原因になります。

SCANとSEARCHの違いから読み取れること

EXPLAIN QUERY PLANの出力で最初に見るべきポイントは、SCANかSEARCHかの違いです。

出力意味対応の方向性
SCAN テーブル名テーブル全体を走査条件カラムにインデックスを追加する
SEARCH テーブル名 USING INDEX ...インデックスを使って絞り込み基本的に問題なし
SEARCH テーブル名 USING COVERING INDEX ...インデックスだけで結果が完結（テーブル本体を見に行かない）最も効率がよい状態

COVERING INDEXという表示が出た場合、そのクエリはインデックスに含まれる情報だけで完結しており、テーブル本体へのアクセスすら発生していません。

SELECTするカラムをインデックスに含める（カバリングインデックス化する）ことで、この状態を意図的に作り出すこともできます。

-- member_idでの検索結果としてstatusだけ欲しい場合
CREATE INDEX idx_orders_covering ON orders (member_id, status);

EXPLAIN QUERY PLAN
SELECT status FROM orders WHERE member_id = 10;
-- => SEARCH orders USING COVERING INDEX idx_orders_covering (member_id=?)

つまずきやすいポイント：ANALYZE不足によるプラン選択ミス

統計情報が古いままインデックスの効果を誤判定する

SQLiteのクエリプランナーは、テーブルの行数やデータの偏りに関する統計情報（sqlite_stat1テーブル）を参照して、インデックスを使うかどうかを判断します。

❌ Before：ANALYZEを一度も実行せず統計情報が空のまま運用する

CREATE INDEX idx_orders_status ON orders (status);
-- 大量データを投入した後もANALYZEを実行しない

私が大量データを投入したテスト用DBでこの状態のまま検証していたとき、明らかにインデックスが効くはずのクエリでSCANが選ばれてしまい、原因調査に時間を使ったことがあります。

統計情報がない状態では、SQLiteは経験的なヒューリスティックでプランを選ぶため、想定と異なる判断をすることがあります。

✅ After：データ投入後にANALYZEを実行し統計情報を更新する

ANALYZE orders;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'shipped';
-- => SEARCH orders USING INDEX idx_orders_status (status=?)

ANALYZEを実行すると、SQLiteは実際のデータ分布をsqlite_stat1に記録し、以降のクエリプランがより実態に即したものになります。

大量データの投入・削除を行った後は、インデックスの効果を過信せず、ANALYZEとEXPLAIN QUERY PLANをセットで確認する習慣をつけることをおすすめします。

応用：インデックスの張りすぎに注意する

インデックスは検索を速くする一方で、書き込み（INSERT・UPDATE・DELETE）のたびにインデックス自体も更新するコストがかかります。

「とりあえず全カラムにインデックスを張る」という設計は、書き込みの多いテーブルではむしろパフォーマンス劣化の原因になります。

-- 使われていないインデックスの一覧イメージを確認する運用例
SELECT name, tbl_name FROM sqlite_master WHERE type = 'index';

EXPLAIN QUERY PLANで実際のクエリを一通り確認し、SCANになっているクエリにだけ的を絞ってインデックスを追加するのが、現実的な運用方針です。

まとめ

この記事のポイント

  • PRIMARY KEY・UNIQUEには自動でインデックスが付くが、外部キー相当のカラムには付かない
  • 複合インデックスは左方一致が基本。検索条件で必ず使うカラムを左に置く
  • EXPLAIN QUERY PLANでSCANかSEARCHかを確認し、インデックスの効果を検証する
  • 統計情報が古いとプランナーが誤判定することがある。データ投入後はANALYZEを実行する

次に読むべき記事

インデックスによる読み取り最適化が分かったところで、次は読み書きの同時実行性を大きく改善するWALモードの仕組みを見ていきましょう。

→ 次の記事：WALモードとは？読み書きを両立させる仕組み

タグ: SQLite, 上級者向け, パフォーマンス

タイトルとURLをコピーしました