SQLiteチュートリアル(V): インデックス作成とデータ解析・クリーニング

I. インデックスの作成

    SQLiteでインデックスを作成するためのSQL構文は、以下の使用例にもあるように、基本的に他のほとんどのリレーショナルデータベースと同じです。

    インデックスの削除は、ビューの削除と非常に似ていて、意味も同じなので、こちらも例だけご紹介します。

    インデックスの再構築は、既に存在するインデックスを削除し、元のルールに基づいてインデックスを再構築するために使用します。ここで重要なのは、REINDEX文の後にデータベース名が与えられていない場合、現在の接続下にあるすべてのAttachedデータベースのすべてのインデックスが再構築されることです。データベース名とテーブル名が指定された場合、そのテーブルのすべてのインデックスが再構築され、インデックス名のみが指定された場合、現在のデータベース内の指定されたインデックスが再構築されます。

    PostgreSQL と同様に、SQLite でも ANALYZE コマンドを使用して、データテーブルやインデックス内のデータを分析し、その統計情報を SQLite 内部のシステムテーブルに格納します。これにより、クエリオプティマイザは分析された統計情報に基づいて最適なクエリ実行パスを選択し、クエリ全体の効率を向上させることができるようになります。次の例をご覧ください。

    PostgreSQL の VACUUM コマンドと比較すると、機能面でも実装の仕方でも非常に似ていますが、PostgreSQL はより細かい粒度を提供するのに対し、SQLite はコマンドをデータベースにしか適用できず、データベース内の指定したデータテーブルやインデックスに対して正確ではなくなっていますが、これはまさに PostgreSQL でできることである、という違いがあります。
    データベース内の1つまたは複数のデータテーブルで、挿入、更新、削除が多発すると、削除されたデータによって多くのディスクスペースが占有され、SQLiteはVACUUMコマンドを実行するまでそれらをOSに戻しません。このようなデータテーブルのデータ保存は非常に分散しているため、クエリ実行時の一括 IO 読み込みがうまくいかず、クエリ効率に影響を与える。
    SQLite では、現在の接続のメインデータベースのみクリーンアップがサポートされており、他の Attached データベースはサポートされていません。VACUUMコマンドは、PostgreSQLと同じ戦略でデータクリーニングを完了します。つまり、現在のデータベースファイルと同じサイズの新しいデータベースファイルを作成し、そのデータベースファイル内のデータを整理して新しいファイルにインポートします インポートが完了したら、新しいデータベースファイルは適切なサイズに縮小されます。このコマンドの実行方法は、以下のように非常に簡単である。