SQLite PRAGMA チューニング - データベース最適化

はじめに

この実験（Lab）では、SQLite PRAGMA チューニングを探求し、データベースのパフォーマンスと信頼性を最適化します。PRAGMA ステートメントを使用して、SQLite の動作の重要な側面を構成する方法を学びます。ジャーナルモードの設定、外部キーチェックの有効化、整合性チェックの実行、およびキャッシュサイズの調整について説明します。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/init_db("Create SQLite Database") sqlite/SQLiteGroup -.-> sqlite/key_set("Set Primary Key") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/verify_table("Check Table Existence") sqlite/SQLiteGroup -.-> sqlite/check_version("Get SQLite Version") subgraph Lab Skills sqlite/init_db -.-> lab-552554{{"SQLite PRAGMA チューニング"}} sqlite/key_set -.-> lab-552554{{"SQLite PRAGMA チューニング"}} sqlite/get_all -.-> lab-552554{{"SQLite PRAGMA チューニング"}} sqlite/query_where -.-> lab-552554{{"SQLite PRAGMA チューニング"}} sqlite/verify_table -.-> lab-552554{{"SQLite PRAGMA チューニング"}} sqlite/check_version -.-> lab-552554{{"SQLite PRAGMA チューニング"}} end

データベースの作成とジャーナルモードの設定

このステップでは、SQLite データベースを作成し、そのジャーナルモードを設定します。ジャーナルモードは、SQLite がトランザクションを処理し、データの整合性を確保する方法を制御します。

まず、LabEx VM でターミナルを開きます。デフォルトのパスは /home/labex/project です。

test.db という名前の SQLite データベースを作成し、次のコマンドを使用して SQLite シェルに入ります。

sqlite3 test.db

このコマンドは、データベースファイル test.db を作成し（存在しない場合）、SQLite コマンドラインツールを開きます。次のようなプロンプトが表示されます。

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

次に、ジャーナルモードを設定しましょう。SQLite は、DELETE、TRUNCATE、PERSIST、MEMORY、WAL、および OFF を含む、いくつかのジャーナルモードを提供します。WAL (Write-Ahead Logging) は、パフォーマンスと信頼性のバランスが取れています。

次の SQL コマンドを実行して、ジャーナルモードを WAL に設定します。

PRAGMA journal_mode=WAL;

このコマンドは、Write-Ahead Logging を使用するようにデータベースを構成します。WAL は、変更をデータベースに適用する前に別の WAL ファイルに書き込むことで、並行性とパフォーマンスを向上させます。

ジャーナルモードを確認するには、次を実行します。

PRAGMA journal_mode;

期待される出力：

wal

これにより、ジャーナルモードが WAL に設定されていることが確認されます。

外部キーサポートの有効化

このステップでは、SQLite データベースで外部キーサポートを有効にします。外部キーは、テーブル間の関係を強制し、データの整合性を維持するのに役立ちます。

SQLite シェル内で（前のステップで終了した場合は、sqlite3 test.db で再接続します）、次のコマンドを実行します。

PRAGMA foreign_keys = ON;

このコマンドは、現在のデータベース接続に対して外部キーの強制を有効にします。データベースへの新しい接続ごとに外部キーを有効にする必要があることに注意してください。

外部キーサポートが有効になっていることを確認するには、次を実行します。

PRAGMA foreign_keys;

期待される出力：

この出力は、外部キーサポートが有効になっていることを確認します。

外部キー関係を持つテーブルの作成

外部キーサポートが有効になったので、その機能を示すために、外部キー関係を持つ 2 つのテーブルを作成しましょう。

次の SQL コマンドを実行して、users テーブルと orders テーブルを作成します。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

これらのコマンドは、次の 2 つのテーブルを作成します。

users: id (主キー) と name の列を持つユーザー情報を格納します。
orders: id (主キー)、user_id (users.id を参照する外部キー)、および amount の列を持つ注文情報を格納します。

FOREIGN KEY (user_id) REFERENCES users(id) 句は、orders テーブルと users テーブルの間の関係を確立します。これにより、orders テーブルの user_id は、users テーブルの id 列に存在する必要があります。

外部キー制約のテスト

このステップでは、外部キー制約をテストして、無効なデータがデータベースに挿入されるのをどのように防ぐかを確認します。

まず、users テーブルにユーザーを挿入します。

INSERT INTO users (id, name) VALUES (1, 'Alice');

このコマンドは、id が 1 で name が 'Alice' の新しいユーザーを users テーブルに挿入します。

次に、users テーブルに存在しない user_id を持つ注文を orders テーブルに挿入しようとします。

INSERT INTO orders (user_id, amount) VALUES (999, 100.0);

外部キーチェックが有効になっているため、このコマンドは次のエラーメッセージで失敗します。

Error: FOREIGN KEY constraint failed

これは、外部キー制約が正しく機能し、存在しないユーザーの注文を作成できないようにしていることを示しています。

次に、既存の user_id を持つ有効な注文を挿入します。

INSERT INTO orders (user_id, amount) VALUES (1, 100.0);

このコマンドは、user_id 1 が users テーブルに存在するため、成功します。

整合性チェックの実行

このステップでは、SQLite データベースの整合性チェックを実行して、不整合やエラーがないことを確認します。

SQLite シェル内で次のコマンドを実行します。

PRAGMA integrity_check;

このコマンドは、データベース全体に不整合やエラーがないかチェックします。データベースが正常な場合、出力は ok になります。

期待される出力：

ok

データベースが破損している場合、出力には破損の種類と場所を示すエラーメッセージが含まれます。

キャッシュサイズの調整と終了

この最後のステップでは、SQLite が使用するキャッシュサイズを調整し、SQLite シェルを終了します。

キャッシュサイズは、SQLite が最近アクセスしたデータを保存するために使用するメモリ量を決定します。キャッシュサイズを大きくすると、特に読み込み負荷の高いワークロードの場合にパフォーマンスが向上します。

次のコマンドを実行して、キャッシュサイズを 4MB (4096 KB) に設定します。

PRAGMA cache_size = 4096;

このコマンドは、現在のデータベース接続のキャッシュサイズを 4MB に設定します。

キャッシュサイズを確認するには、次を実行します。

PRAGMA cache_size;

期待される出力：

最後に、SQLite シェルを終了します。

.exit

このコマンドは、データベース接続を閉じて、コマンドラインに戻ります。

まとめ

この実験（Lab）では、SQLite PRAGMA のチューニングについて学習しました。パフォーマンスと信頼性を向上させるためのジャーナルモード（journal mode）の設定、データ整合性を強化するための外部キー（foreign key）サポートの有効化、外部キー関係を持つテーブルの作成、外部キー制約（foreign key constraints）のテスト、データベースの破損を特定するための整合性チェックの実行、およびメモリ使用量を最適化するためのキャッシュサイズの調整方法を学びました。これらのスキルは、SQLite データベースを効果的に操作するための確固たる基盤となります。