1. ホーム
  2. データベース
  3. エスキューエルライト

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

2022-01-24 04:48:16

I. インデックスの作成

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

コピーコード コードは以下の通りです。

    sqlite> CREATE TABLE testtable (first_col integer,second_col integer);
    -- Creates the simplest index that is based on a field in a table.
    sqlite> CREATE INDEX testtable_idx ON testtable(first_col);
    -- Creates a federated index that is based on multiple fields of a table, with the ability to specify a sort rule (ascending/descending) for each field.
    sqlite> CREATE INDEX testtable_idx2 ON testtable(first_col ASC,second_col DESC);
    -- Creates a unique index that has the same rules as the table's uniqueness constraint, i.e., NULL is different from any value, including NULL itself.
    sqlite> CREATE UNIQUE INDEX testtable_idx3 ON testtable(second_col DESC);
    sqlite> .indices testtable
    testtable_idx
    testtable_idx2   
    testtable_idx3

    .indicesコマンドの出力からわかるように、3つのインデックスがすべて正常に作成されました。

II. インデックスの削除

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

コピーコード コードは以下の通りです。

    sqlite> DROP INDEX testtable_idx;
    -- If deleting a non-existent index will cause the operation to fail, you can use the "IF EXISTS" clause if you are unsure and don't want the error to be thrown.
    sqlite> DROP INDEX testtable_idx;
    Error: no such index: testtable_idx
    sqlite> DROP INDEX IF EXISTS testtable_idx;

III. インデックスの再構築

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

コピーコード コードは以下の通りです。

    -- Indexes in all databases in the current connection attached are rebuilt.
    sqlite> REINDEX;
    -- Rebuild all indexes of the testtable table in the current primary database.
    sqlite> REINDEX testtable;
    -- Rebuild the index with the name testtable_idx2 in the current master database.
    sqlite> REINDEX testtable_idx2;

IV. データ分析

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

コピーコード コードは以下の通りです。

    -- If no parameters are specified after the ANALYZE command, all tables and indexes in the Attached database in the current connection are analyzed.
    sqlite> ANALYZE;
    -- If a database is specified as an ANALYZE parameter, then all tables and indexes under that database will be analyzed and statistics will be generated.
    sqlite> ANALYZE main;
    -- If a table or index in the database is specified as an ANALYZE parameter, then that table and all its associated indexes will be parsed.
    sqlite> ANALYZE main.testtable;
    sqlite> ANALYZE main.testtable_idx2;

V. データのクリーンアップ

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

コピーコード コードは以下の通りです。

    sqlite> VACUUM;