sqlite-ops

SQLite の操作

SQLite は、さまざまなホストで利用できる単一のエンジンです。sqlite3 CLI、Python、node:sqlite、better-sqlite3、Bun、Cloudflare D1、libSQL/Turso のいずれを通じて利用しても、SQL のセマンティクス、クエリプランナー、プラグマは同じです。異なるのは、ドライバーのインターフェースと運用環境（ファイルの所有者、「接続」にかかるコスト、PRAGMA を実行できるかどうかなど）です。まずエンジンについて考え、そのうえでホストごとのセクションを確認し、異なる注意点を把握してください。

Where does the problem live?
│
├─ A statement is slow, or scans too much
│  └─ EXPLAIN QUERY PLAN first, always → references/query-performance.md
│
├─ "database is locked" / SQLITE_BUSY / writers blocking readers
│  └─ WAL + busy_timeout + BEGIN IMMEDIATE → references/concurrency-durability.md
│
├─ Wrong data got in, or a constraint didn't fire
│  └─ Type affinity, STRICT, foreign_keys=OFF → references/schema-design.md
│
├─ Search / JSON / geo / analytics feature question
│  └─ FTS5, JSON, R-tree, window fns → references/feature-modules.md
│
├─ Running on a managed/edge engine (D1, Turso)
│  └─ references/d1-edge.md + references/hosts.md
│
└─ Corruption, size, backup, VACUUM
   └─ references/operations.md

計測の心得（最適化を始める前にお読みください）

SQLite の「最適化」の多くは、計測されていません。次の 4 つのルールを、破られる頻度が高い順に示します。

1. ツール呼び出しではなく、ステートメントを計測してください。 すでに別のクエリを送信しているバッチに含まれる高コストな集約処理は、追加のラウンドトリップが発生しないため、呼び出し単位の計測では見えません。それでも、リクエストごとにテーブル全体をスキャンしています。複数の部分からなるステートメントは分解し、それぞれを個別に計測してください。
2. レイテンシとスキャンした行数の両方を報告してください。 この 2 つは独立して変化します。最適化によってレイテンシが約 25 倍短縮されても、読み取り行数はほとんど変わらないことがあります（D1 のような従量課金エンジンでは、読み取り行数がコストを左右する指標です。references/d1-edge.md を参照してください）。
3. CLI の実時間を信用しないでください。 プロセスの起動時間が大部分を占めます。エンジンが報告する時間（CLI の .timer on、D1 の meta.timings.sql_duration_ms）を使ってください。
4. 10 回以上実行し、中央値と範囲を報告してください。 初回の実行はコールド状態です。ある計測セッションでは、同じステートメントで中央値が 171 ms だったのに対し、コールド状態の実行は 2,495 ms でした。数千行を読み取る場合、初回に 1.5～1.7 倍の時間がかかることは珍しくありませんでした。

# sqlite3 CLI: engine-reported timing, not shell time
sqlite3 app.db '.timer on' "SELECT count(*) FROM q_product WHERE org LIKE '%acme%';"

# What the planner thinks the data looks like (empty = ANALYZE never ran)
sqlite3 app.db 'SELECT * FROM sqlite_stat1;'

インデックスを作成する前に、効果を確かめる

このスキルで最も効果が大きく、デプロイゲート内でスキーマ作業を完結させる方法です。既存のインデックスがカバーするカラムに対して、同じ形式のステートメントを実行します。 テーブル、行数、述語の形式は同じにし、カラムだけを変更します。その差が、本番データを使ってスキーマへの書き込みを一切行わずに計測した、予想される効果です。

-- Hypothesis: a covering index on (org, product_id) makes this fast.
-- Unindexed control (what you have today):
SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%';

-- Proof shot: same shape, over a column an existing index already covers.
-- If this is fast, the index is worth writing. If it isn't, the index is not your problem.
SELECT DISTINCT org FROM q_product WHERE org LIKE '%acme%';

実例では、効果を確かめるための実行結果は 6.75 ms、比較対象は 171.83 ms でした。本番スキーマに手を加えずに、インデックスを作成する根拠として十分な差です。

EXPLAIN QUERY PLAN — 60 秒で読み解く

遅いステートメントを調べるときは、まず EXPLAIN QUERY PLAN（EQP）を実行してください。低コストで安全な読み取り専用コマンドであり、任意の SQL を実行できるすべてのホストで利用できます。

EXPLAIN QUERY PLAN
SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%';

プランの行	意味	評価
SEARCH t USING INDEX ix (col=?)	B ツリーで検索し、一致する行だけにアクセスします	最良
SEARCH t USING COVERING INDEX ix	インデックスだけで検索し、必要なカラムがすべて含まれているため、テーブルは読み取られません	最良
SCAN t USING COVERING INDEX ix	全体を走査しますが、幅の広い行ではなく、コンパクトなインデックスエントリを走査します	多くの場合問題ありません。以下を参照
SCAN t USING INDEX ix	インデックス全体を走査し、該当する各行を取得します	要注意：インデックスの効果がほとんどありません
SCAN t	テーブル全体をスキャンします	テーブルが非常に小さい場合を除き、改善してください
USE TEMP B-TREE FOR ORDER BY	順序を提供するインデックスがないため、ソートしています	コストの兆候
USE TEMP B-TREE FOR GROUP BY	グループ化のために同様の処理をしています	コストの兆候
CORRELATED SCALAR SUBQUERY	外側の行ごとにサブクエリを再実行します	通常、問題の根本原因です

最も重要な違い： SCAN … USING COVERING INDEX は失敗ではありません。カバリングスキャンは、幅の広い行をメモリに読み込む代わりに、コンパクトなインデックスエントリを読み取ります。これは、インデックス検索できない述語を高速化する方法そのものです。

詳細： ./references/query-performance.md — インデックス設計、カラムの順序、部分インデックスと式インデックス、ANALYZE／sqlite_stat1、およびクエリプランナーの最適化を妨げる要因の全一覧。

インデックスを使えない述語の落とし穴（実例）

このアドバイスの根拠となったD1の実測例（LIKE '%x%' に通常のインデックスを作成しても何も変わらない理由、修正方法、修正前後の数値）と、sqlite_stat1 の有無によるプランナーの確認については、references/covering-index-worked-example.md を参照してください。

インデックス設計一覧

述語の形	インデックス化可能か	作成するもの
col = ?、col IN (…)、col > ?、BETWEEN	はい	col の B-tree
a = ? AND b = ?	はい	複合インデックス (a, b) — 等価条件の列を先にする
a = ? ORDER BY b	はい	複合インデックス (a, b) — 一時 B-tree を不要にする
col LIKE 'x%'（前方一致）	col が BINARY 照合順序の TEXT 型なら可能	col の B-tree
col LIKE '%x%'（先頭にワイルドカード）	シーク不可	テーブルスキャンをカバリングするか、FTS5 のトライグラムを使う
lower(col) = ?	通常のインデックスでは不可	式インデックス ON t(lower(col))
行の 2% に該当する status = 'open'	はい	部分インデックス WHERE status = 'open'
json_extract(doc,'$.k') = ?	通常のインデックスでは不可	式インデックス、または生成列とインデックス

効果のあるルール： カバリングインデックスでは、フィルター対象の列を先に、射影対象の列を後に配置します。関数ではなく列にインデックスを作成します（式インデックスの場合を除く）。追加するインデックスはすべて書き込みの負担になるため、追加前に確認してください。

同時実行性と耐久性 — 80/20

症状	原因	対処方法
SQLITE_BUSY	別の接続がロックを保持しており、この接続は待機をあきらめました	PRAGMA busy_timeout = 5000; を設定し、書き込みトランザクションを短く保つ
SQLITE_LOCKED	同じ接続内（または共有キャッシュ内）での競合	コードを修正してください — 再試行ループは永久に回り続けます
トランザクション中に「database is locked」	後で書き込みを行う BEGIN（DEFERRED）の読み取り → アップグレード時のデッドロック。再試行できません	書き込みを行うトランザクションには BEGIN IMMEDIATE を使う
書き込み中に読み取りがブロックされる	ロールバックジャーナルモード	PRAGMA journal_mode = WAL;（設定は一度だけ。設定は保持されます）
-wal ファイルが際限なく大きくなる	長時間実行される読み取りがチェックポイントを妨げています	読み取りを終了または更新し、PRAGMA wal_checkpoint(TRUNCATE); を実行する

PRAGMA journal_mode = WAL;      -- persistent; survives reconnect
PRAGMA busy_timeout = 5000;     -- per-connection; set on EVERY connection
PRAGMA foreign_keys = ON;       -- per-connection, OFF by default — see below
PRAGMA synchronous = NORMAL;    -- safe with WAL; FULL only if you fear power loss

詳しくはこちら： ./references/concurrency-durability.md — WAL の内部動作、DEFERRED のアップグレード時のデッドロック、synchronous の各レベル、チェックポイントの停滞、複数プロセスからのアクセス。

スキーマ — 気づきにくい3つのバグ

1. PRAGMA foreign_keys はデフォルトで OFF です。 接続ごとに設定が必要です。REFERENCES 句は解析され、保存されますが、何も行いません。これは SQLite アプリケーションでよくある、気づきにくいデータ整合性のバグです。
2. 型アフィニティは型そのものではありません。 TEXT 列には整数も問題なく格納できます。宣言された型は、変換方法に関する指針です。宣言した型を強制したい場合は、STRICT テーブル（SQLite 3.37+）を使用してください。
3. ALTER TABLE には制限があります。 列の追加と名前の変更はサポートされていますが、削除、型の変更、制約の変更には、12ステップの再作成手順が必要です。

CREATE TABLE product (
    id       INTEGER PRIMARY KEY,
    org      TEXT NOT NULL,
    price    REAL NOT NULL,
    doc      TEXT,
    -- indexable projection of a JSON field
    sku      TEXT GENERATED ALWAYS AS (json_extract(doc, '$.sku')) VIRTUAL
) STRICT;

詳しくはこちら： ./references/schema-design.md（アフィニティ、STRICT、生成列、WITHOUT ROWID、制約）と ./references/migration-patterns.md（12ステップの ALTER 手順、バージョン管理されたマイグレーションランナー）。

機能モジュールの概要

ニーズ	使用するもの	備考
部分文字列／あいまいテキスト検索	trigram トークナイザーを使った FTS5	大規模環境での LIKE '%x%' に対する実質的な解決策です
単語／フレーズ検索とランキング	FTS5 + bm25()	外部コンテンツテーブルを使えばコーパスの重複を避けられます
半構造化ドキュメント	json_extract / -> / ->>、JSONB（3.45+）	生成列または式インデックスでインデックスを作成します
バウンディングボックス／区間の重なり	R-tree 仮想テーブル	コンパイル時のモジュールです。利用可能か確認してください
累計、ランキング、ギャップ	ウィンドウ関数（3.25+）	PostgreSQL と同じ構文です
挿入または更新	ON CONFLICT … DO UPDATE（3.24+）	excluded.col は挿入候補の行を参照します
書き込んだデータの読み戻し	RETURNING（3.35+）	アトミックなジョブ取得パターンを単一ステートメントで実現できます

詳細：./references/feature-modules.md。

ホスト

エンジンは同じですが、接続の仕組みは異なります。

ホストごとの接続モデルと注意点：ホストの概要。D1 ではコストは読み取り行数で、バインドパラメーターの上限は 100 です。また、PRAGMA は利用できません。

詳細：ホストごとの接続方法と注意点については、./references/hosts.md を参照してください。

特に D1 では、標準の SQLite に相当するものがなく、見落とされやすいプラットフォーム機能が 3 つあります。

wrangler d1 insights <db> --sort-type=sum --sort-by=reads --limit=10   # rank REAL queries by cost
wrangler d1 time-travel info <db>                                      # 30-day point-in-time restore point
# Sessions API (env.DB.withSession(bookmark)) - read replicas, sequential consistency

./references/d1-edge.md では、これらに加えて、読み取り行数に基づくコスト、検証済みの制限事項一覧、エラーカタログ、インポート／エクスポートを解説しています。本番環境でのインシデントパターン、つまりタイムアウトした migrations apply --remote が実際には適用されていたケース、batch() が対象行 0 件のスコープ付き UPDATE を成功として扱うケース、読み取りレプリケーションを有効化する段階的なロールアウト方法については、./references/d1-production-patterns.md を参照してください。

運用

sqlite3 app.db 'PRAGMA quick_check;'        # fast structural check
sqlite3 app.db 'PRAGMA integrity_check;'    # full check — slow on big DBs
sqlite3 app.db "VACUUM INTO 'backup.db';"   # consistent backup, no downtime, defragmented
sqlite3 app.db '.dump' > backup.sql         # portable text backup
sqlite3 app.db 'PRAGMA optimize;'           # run before closing a long-lived connection

書き込み中のライブデータベースファイルを cp でコピーすることは絶対に避けてください。VACUUM INTO、バックアップ API、または .dump を使用してください。

詳細：./references/operations.md — 破損の原因と復旧、VACUUM と VACUUM INTO の比較、ページ／キャッシュのサイズ設定、サイズ分析。

トリアージスクリプト

scripts/eqp-triage.py は EXPLAIN QUERY PLAN の結果を読み取り、データベースに対してステートメントを実行するか、パイプで渡されたプランテキストを受け取り、各行を修正のヒントとともに重大度別に分類します。該当箇所が見つかった場合（ドメインシグナル）は終了コード 10 を返し、プランに問題がない場合は 0 を返します。

# Run against a database file (uses Python's bundled sqlite3 — no external binary needed)
python3 scripts/eqp-triage.py --db app.db \
  --sql "SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%'"

# More: triage a plan piped from D1 or a log, and --json output -> references/eqp-triage.md

注意点

誤り	問題になる理由	対処法
LIKE '%x%' 用にインデックスを追加する	先頭にワイルドカードがあるとインデックス検索できません	カバリングインデックス、または FTS5 の trigram を使います
シェルのストップウォッチで時間を計る	CLI／ドライバーの起動時間が支配的になります	エンジンが報告する時間を使い、10 回以上の中央値を取ります
ステートメントではなくツール呼び出しの時間を計る	まとめて実行されるステートメントが見えません	分解して各部分の時間を計ります
REFERENCES が強制されると思い込む	接続ごとに foreign_keys が OFF です	接続のたびに PRAGMA foreign_keys = ON を実行します
宣言した型が強制されると思い込む	型付けではなく型アフィニティです	STRICT テーブルを使います
SQLITE_LOCKED を再試行する	同一接続内の競合は解消されません	コードパスを修正します
BEGIN の後に書き込む	DEFERRED から書き込みへの昇格はデッドロックし、再試行できません	BEGIN IMMEDIATE を使います
ライブデータベースに cp を実行する	不完全なコピーになります	VACUUM INTO／バックアップ API を使います
SELECT * を使う	カバリングインデックスの効果が失われ、読み取る各行の幅が広がります	必要な列だけを指定します
「速度向上」のために VACUUM を実行する	ファイル全体を書き換え、2 倍の空き容量が必要になり、ロックを保持します	PRAGMA optimize／対象を絞ったインデックス作業を行います
1 回の低速実行だけを信頼する	初回実行に 1.5～1.7 倍のペナルティが発生するのはよくあることです	10 回以上の中央値を取り、範囲も報告します
パラメーター上限を回避するためにリテラルを埋め込む	それがインジェクションの原因になります	作業を分割し、バインドパラメーターを使い続けます
タイムアウトしたリモートマイグレーションを再実行する	適用済みの可能性があり、エラーは応答に関するものです	まず読み取り専用でスキーマの状態を確認します — ./references/d1-production-patterns.md
コミット済みの batch() をステートメントごとの成功とみなす	条件付き UPDATE が 0 行に一致してもエラーにはなりません	meta.changes を確認します。スコープ付き書き込みで 0 件なら 403／競合です

参照ファイル

参照先	読み込むタイミング
./references/query-performance.md	遅いステートメントに関する内容：EQP、インデックス設計、ANALYZE、プランナーが期待どおりに動作しない場合、測定方法
./references/d1-edge.md	Cloudflare D1：読み取り行数に基づく料金、d1 insights、Sessions API／レプリケーション、Time Travel、制限、エラー
./references/d1-production-patterns.md	D1 の本番運用：タイムアウトしたマイグレーションの検証、batch() における 0 行書き込みの検証、レプリカへのオプトイン方式によるレプリケーションの段階的導入
./references/concurrency-durability.md	ロック、WAL、busy_timeout、トランザクションモード、チェックポイント、耐久性
./references/schema-design.md	アフィニティ、STRICT、外部キー、生成列、WITHOUT ROWID、制約
./references/schema-patterns.md	すぐに使えるテーブル設計：状態、キャッシュ、イベントログ、キュー、セッション、重複排除
./references/migration-patterns.md	バージョン管理されたマイグレーション、12 ステップの ALTER 手順、ホスト固有のランナー
./references/feature-modules.md	FTS5、JSON／JSONB、R-tree、ウィンドウ関数、upsert、RETURNING
./references/hosts.md	ホストごとの接続手順とドライバー特有の注意点（Python、Node、Bun、D1、libSQL）
./references/async-patterns.md	Python の aiosqlite を詳しく解説：非同期 CRUD、バッチ処理、プーリング
./references/operations.md	整合性チェック、破損からの復旧、VACUUM、バックアップ、サイズとページのチューニング
./references/testing.md	インメモリデータベースとファイルデータベース、フィクスチャ、決定的なシードデータの投入、マイグレーションのテスト
./references/covering-index-worked-example.md	上記の推奨事項の根拠となった、D1 のカバリングインデックスの測定例。統計情報の有無によるプランナーの比較
./references/eqp-triage.md	eqp-triage.py のその他の呼び出し例：D1 またはログからパイプで渡したプラン、--json 出力

関連項目

スキル	組み合わせるタイミング
sql-ops	ベンダーに依存しない SQL：CTE、ウィンドウ関数、JOIN 戦略
perf-ops	より広範なパフォーマンスワークフロー：プロファイリング、負荷テスト、実施前後の手順
cloudflare-ops	D1 データベース周辺の Workers、バインディング、デプロイ
postgres-ops	ワークロードが SQLite の単一ライターモデルでは対応しきれなくなった場合
python-database-ops	SQLite 上の SQLAlchemy／ORM レイヤー