SQLite データベースのメンテナンス

はじめに

この実験（Lab）では、SQLite データベースを最適なパフォーマンスで維持する方法を学びます。VACUUM コマンドを使用してスペースを再利用したり、インデックスを再構築してクエリを高速化したり、テーブル統計を分析して SQLite がより適切な判断を下せるようにするなど、重要なテクニックについて説明します。それでは始めましょう！

データベースとサンプルテーブルの作成

メンテナンスに入る前に、SQLite データベースといくつかのデータを含むサンプルテーブルを作成しましょう。これにより、作業対象ができます。

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

mydb.db という名前の SQLite データベースを作成するには、次のコマンドを実行します。

sqlite3 mydb.db

このコマンドは、データベースファイルが存在しない場合は作成し、SQLite コマンドラインツールを開いてデータベースに接続します。sqlite> プロンプトが表示されます。

次に、ユーザー情報を格納する users という名前のテーブルを作成しましょう。次の SQL コマンドを実行します。

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

このコマンドは、id (各ユーザーを一意に識別する整数)、name (ユーザーの名前)、および email (ユーザーのメールアドレス) の 3 つの列を持つテーブルを作成します。PRIMARY KEY 制約により、各 id が一意であることが保証されます。

次に、いくつかのサンプルデータを users テーブルに挿入しましょう。

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com');

これらのコマンドは、users テーブルに 3 つの行を追加します。

テーブルとデータが正常に作成されたことを確認するには、次のコマンドを実行します。

SELECT * FROM users;

期待される出力：

1|Alice|alice@example.com
2|Bob|bob@example.com
3|Charlie|charlie@example.com

この出力は、users テーブルの内容を示しています。

データ削除のシミュレーション

VACUUM の効果を示すために、データの削除をシミュレートする必要があります。これにより、データベースファイルに断片化が発生する可能性があります。

users テーブルから行を削除してみましょう。

DELETE FROM users WHERE id = 2;

このコマンドは、id が 2 の行 (Bob のレコード) を削除します。

削除を確認するには、次のコマンドを実行します。

SELECT * FROM users;

期待される出力：

1|Alice|alice@example.com
3|Charlie|charlie@example.com

Bob のレコードがテーブルに存在しなくなったことがわかります。この削除により、データベースファイルにギャップが生じます。VACUUM は、このギャップに対処するのに役立ちます。

VACUUM を実行して領域を再利用する

次に、VACUUM コマンドを使用してデータベースファイルをデフラグ（defragment）し、削除されたレコードによって残されたスペースを再利用しましょう。

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

VACUUM;

このコマンドは、データベースファイル全体を書き換え、データを統合し、空きスペースを削除します。

VACUUM を実行しても、特定の出力は表示されませんが、データベースを最適化するためにバックグラウンドで動作しています。

SQLite シェルを終了するには、次を実行します。

.exit

これでターミナルに戻ります。

インデックスの作成

インデックスは、クエリの高速化に不可欠です。 users テーブルの email 列にインデックスを作成してみましょう。

まず、SQLite データベースに再度接続します。

sqlite3 mydb.db

次に、次のコマンドを使用してインデックスを作成します。

CREATE INDEX idx_users_email ON users (email);

このコマンドは、email 列に idx_users_email という名前のインデックスを作成します。SQLite は、このインデックスを使用して、メールアドレスに基づいてユーザーをすばやく検索します。

インデックスの再構築

時間が経つにつれて、特に多くのデータ変更の後、インデックスは断片化（fragmentation）する可能性があります。インデックスを再構築すると、効率が向上します。

先ほど作成したインデックスを再構築するには、REINDEX コマンドを使用します。

REINDEX idx_users_email;

このコマンドは、idx_users_email インデックスを再構築し、現在のデータに合わせて最適化されていることを保証します。

SQLite シェルを終了するには、次を実行します。

.exit

これでターミナルに戻ります。

テーブル統計の分析

SQLite は、クエリを最適化するためにデータに関する統計情報を使用します。これらの統計情報を定期的に更新することをお勧めします。

まず、SQLite データベースに再度接続します。

sqlite3 mydb.db

users テーブルを分析するには、次のコマンドを実行します。

ANALYZE users;

このコマンドは、users テーブルに関する統計情報を収集します。クエリオプティマイザ（query optimizer）は、この情報を使用してクエリのパフォーマンスを向上させることができます。

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

.exit

これでターミナルに戻ります。

まとめ

この実験（Lab）では、SQLite データベースで不可欠なメンテナンス作業を実行する方法を学びました。 VACUUM コマンドを使用してスペースを再利用し、クエリのパフォーマンスを向上させるためにインデックスを作成および再構築し、SQLite がクエリを最適化するのに役立つようにテーブル統計を分析しました。これらのテクニックは、データベースをスムーズかつ効率的に実行し続けるために非常に重要です。