【ITニュース解説】Subtleties of SQLite Indexes

SQLiteなどのデータベースでは、データがテーブルという形式で保存される。テーブル内の特定のデータを探す際、インデックスがなければ、データベースはテーブルの先頭からすべての行を一つずつ確認していくしかない。これはデータ量が増えると処理に非常に時間がかかる。データベースのインデックスは、特定のデータを高速に見つけ出すための仕組みである。

インデックスは、主に「B-Tree（ビー・ツリー）」というデータ構造を用いて実装される。B-Treeは、データを効率的に並べ替え、階層的な構造で管理することで、大量のデータの中から目的のデータを素早く検索できるようにする。根から枝へとたどっていくことで、特定のデータがどこにあるかを効率的に特定できる。

SQLiteでテーブルを作成する際、通常は「主キー」を設定する。主キーはテーブル内の各行を一意に識別するための特別な列であり、ユーザーIDや商品コードなどがこれにあたる。SQLiteは主キーに対して自動的にインデックスを作成する。これは、主キーによる検索が非常に頻繁に行われるため、常に高速にアクセスできるようにするためである。主キーのインデックスは、データの物理的な並び順とも関連付けられ、高速なデータアクセスを可能にする。

主キー以外の列にもインデックスを作成できる。これを「セカンダリインデックス」と呼ぶ。例えば、ユーザーのメールアドレスや商品のカテゴリ名など、頻繁に検索されるが主キーではない列にセカンダリインデックスを設定する。セカンダリインデックスは、インデックスが作成された列の値と、それに対応する行の主キー（またはROWIDと呼ばれる内部的な識別子）をペアで保存する。

セカンダリインデックスを使ってデータを検索する際、データベースはまずセカンダリインデックスで目的の値を持つエントリを探す。そして、そのエントリから取得した主キー（またはROWID）を使って、実際のテーブル本体からその行全体を取得するという二段階の処理を行う。このため、セカンダリインデックスだけの情報では、クエリが要求するすべてのデータを得られない場合がある。

複数の列を組み合わせて一つのインデックスとすることも可能で、これを「複合インデックス」と呼ぶ。例えば、姓と名でユーザーを探す場合、(姓, 名)という複合インデックスを作成できる。複合インデックスは、定義された列の順序に従って効率的に機能する。もし(姓, 名)のインデックスがある場合、姓だけで検索するクエリには有効だが、名だけで検索するクエリにはあまり有効ではないことが多い。これは、インデックスが左から順に並べられているためである。

インデックスの効果は、そのインデックスがどれだけ「選択的（カーディナリティが高い）」かによって大きく変わる。選択性が高いとは、その列の値がユニークに近いことを意味する。例えば、性別の列（男性、女性の二択）にインデックスを作成しても、検索対象が半分に絞られるだけで、あまり効果的ではない。しかし、ユーザーIDのようなほぼユニークな値を持つ列であれば、インデックスは非常に効果的に機能する。

データベースがインデックスを利用するかどうかは、クエリの内容によって判断される。主に「WHERE句」でインデックスが設定された列が使われている場合にインデックスが利用される。例えば、「WHERE age = 30」のような条件である。また、「ORDER BY句」でデータの並べ替えが必要な場合にも、インデックスが利用されることがある。インデックスはすでにソートされた順序でデータを保持しているため、インデックスを使うことでデータベースが改めてデータを並べ替える手間を省き、処理を高速化できる。

注意が必要なのは、「LIKE句」で部分一致検索を行う場合である。「LIKE 'prefix%'」（前方一致）の場合はインデックスが利用されやすいが、「LIKE '%suffix'」（後方一致）や「LIKE '%middle%'」（中間一致）の場合はインデックスが利用されにくい。また、インデックスが設定された列に「関数」を適用すると（例: WHERE SUBSTR(name, 1, 1) = 'A'）、インデックスが使われなくなることが多い。

クエリによっては、テーブル本体にアクセスすることなく、インデックスだけで必要な情報をすべて取得できる場合がある。これを「カバリングインデックス」と呼ぶ。例えば、「SELECT id, name FROM users WHERE age = 30」というクエリがあり、(age, id, name)という複合インデックスが存在する場合、データベースはテーブル本体にアクセスすることなく、インデックスからidとnameを取得できるため、非常に高速な検索が可能になる。

作成したSQLクエリが本当にインデックスを利用して高速に実行されているかを確認するには、「EXPLAIN QUERY PLAN」というコマンドを使う。このコマンドをクエリの先頭に付けると、データベースがそのクエリをどのように実行しようとしているかの計画（実行計画）を表示する。この実行計画を見ることで、インデックスが使われているか、どのインデックスが使われているか、あるいは全件スキャン（テーブル全体を調べること）になっているかなどを確認し、クエリの性能を改善するためのヒントを得られる。

インデックスはデータベースの検索性能を劇的に向上させる強力なツールだが、デメリットも存在する。インデックスを作成すると、そのインデックス自体がディスク容量を消費する。また、データの追加、更新、削除といった書き込み処理のたびに、インデックスも更新する必要があるため、書き込み性能が低下する可能性がある。そのため、不必要に多くのインデックスを作成することは避け、よく利用されるクエリに合わせて最適なインデックス設計を行うことが重要である。

データベースを扱うシステムエンジニアにとって、インデックスの仕組みと適切な使い方を理解することは、高性能なシステムを構築するための基礎となる知識である。