SQLiteパフォーマンスを極める：基礎を超えて

SQLiteは世界で最も広くデプロイされているデータベースです。あなたの携帯電話（iOSとAndroidの両方）、ブラウザ、Mac、そしておそらく電子レンジの中にさえあります。デフォルトでは、SQLiteは安全で互換性があるように構成されており、必ずしも爆速ではありません。本格的なデスクトップアプリケーションやデータ集約型のプロセスにSQLiteを使用している場合は、その仕組みを理解する必要があります。

この投稿では、HarborDBで数百万行をミリ秒単位でクエリするために使用したテクニックを紹介します。

1. まず最初に：WALを有効にする

他になにもしなくても、これだけはやってください。Write-Ahead Logging（WAL）モードは、SQLiteの並行性を劇的に変化させます。デフォルトでは、SQLiteはロールバックジャーナルを使用します。つまり、あるプロセスが書き込んでいる間は、誰も読み取ることができません（またはその逆、ロックモードによって異なります）。

WALモードでは以下が可能になります：

同時読み取りと書き込み。
高速な書き込み（「チェックポイント」がシーケンシャルであるため）。

有効にする方法：

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

注意： PRAGMA synchronous = NORMAL は、ハードディスクが物理的に破損したり電源が落ちたりしない限り安全です。デスクトップアプリにとっては、完璧なトレードオフです。

2. 一括挿入とトランザクション

初心者の最も一般的な間違いは、トランザクションなしでループ内で行を挿入することです。

遅い方法（Pythonの例）：

# これを実行してはいけません。永遠にかかります。
for row in data:
    cursor.execute("INSERT INTO users VALUES (?)", row)
    conn.commit() # すべての行で自動コミット！

SQLiteは、挿入ごとに物理ディスクの回転（またはSSDの同期コマンド）を待機する必要があります。

速い方法：

conn.execute("BEGIN TRANSACTION")
for row in data:
    cursor.execute("INSERT INTO users VALUES (?)", row)
conn.commit()

速度差：多くの場合、100倍から1000倍高速です。

3. インデックス：賢く使う

インデックスは、データの順序付きコピーです。これにより読み取りは非常に高速になりますが、書き込みは少し遅くなります（テーブルとインデックスの両方を更新する必要があるため）。

黄金のインデックス・ヒント

推測する前に EXPLAIN QUERY PLAN を使用してください。

EXPLAIN QUERY PLAN SELECT * FROM logs WHERE severity = 'ERROR';

SCAN TABLE と表示された場合、やり方が間違っています（すべての行を読み取っています）。 SEARCH TABLE USING INDEX と表示された場合、正しい軌道に乗っています。

インデックスの作成：

CREATE INDEX idx_logs_severity ON logs(severity);

4. パワーユーザー向けPRAGMA最適化

HarborDBで使用している、あまり知られていないコマンドをいくつか紹介します：

PRAGMA temp_store = MEMORY; これはSQLiteに、一時テーブルとインデックスをディスクに書き込むのではなく、RAMに保持するように強制します。RAMに余裕がある場合、これは大きなメリットです。
PRAGMA mmap_size = 30000000000; これは、DBファイルへのアクセスにメモリマップI/Oを使用します。OSがキャッシュを処理します。大量の読み取りの場合、これによりI/Oレイテンシをほぼなくすことができます。
PRAGMA cache_size = -2000; SQLiteがメモリに保持するページ数を設定します。負の数はキロバイトを意味します（ここでは約2MB）。正の数はページ数を意味します。大きなクエリの場合はこれを増やしてください。

5. アプリケーションファースト・アーキテクチャ

時々、問題はSQLではなく、コードにあります。 HarborDBでは、UIの応答性を維持しながら、重い読み取り操作をバックグラウンドスレッドにオフロードします。WALモードが有効になっているため、ユーザーがUIに小さな変更（書き込み）を行っても、バックグラウンドスレッドはブロックされません。

まとめ

SQLiteは「おもちゃ」のデータベースではありません。適切に扱えば、大規模なワークロードを処理できる強力なツールです。

WALを有効にする。
書き込みは常にトランザクションでラップする。
WHERE句にはインデックスを使用する。
PRAGMAでメモリを最適化する。

最適化頑張ってください！