★悩み★
・SQLite3においてインデックス(index)の作成ってできる？手順は？
・SQLite3でインデックス(index)を作成すると参照速度は速くなる？
・作成したインデックスの再作成、またはカラムの追加ってできる？
こういった「悩み」に答えます。
★本記事の内容★
① SQLite3でインデックス(index)を作成するための準備を解説
② SQLite3でインデックス(index)を作成する方法と効果を解説
➂ 既存インデックスの再作成および変更手順を解説
これからご紹介する「SQLite3でインデックス(index)を作成する方法」を実践したことで、筆者は5分以内でSQLite3のテーブルにインデックスを作成できました。
記事の前半では「SQLite3でインデックス(index)を作成するための準備」を解説します。
記事の後半では「SQLite3でインデックス(index)を作成する方法と参照速度の変化」を紹介します。
この記事を読み終えることで、「SQLite3のあるテーブルに対してインデックスの貼り方を理解」だけでなく、「CREATE INDEX文の構文」も把握した状態になります。
★SQLite3とは★
SQLiteは、「小型」／「高速」／「自己充足的」／「高信頼性」なSQLデータベースエンジンを実装したC言語ライブラリです。
ちなみに、SQLiteのメリットとデメリットに関して知りたい方には、以下の記事がオススメです。
>> 【要点】SQLiteとは？を解消！初心者向けに特徴から使い方を解説
ちなみに、筆者が約2週間でSQLite3を学んだ方法や手順について以下で公開しています。皆様の学習の参考になれば嬉しいです。
>> SQLite3に0から入門し基本操作を習得するまでのロードマップ
SQLite3でインデックス(index)を作成するための準備
「SQLite3でインデックス(index)を作成するための準備」に関してご紹介します。
★SQLite3でインデックス(index)を作成するための準備の流れ★
手順1：SQLite3をインストール
手順2：インストールしたSQLite3の動作確認
手順3：SQLite3内にテーブルを作成
手順4：SQLite3内に大量データを追加(データ登録)
手順5：大量データを登録したテーブルの参照速度を計測
上記手順を実施することで、「SQLite3でインデックス(index)を作成するための準備」ができます。
上記の各手順は、以下の日時と環境で動作確認済みです。
動作確認済み日時：2022年2月22日
動作確認済み環境：Ubuntu 20.04.3 LTS
以降で、上記「SQLite3でインデックス(index)を作成するための準備の流れ」の各手順に関してご説明します。
手順1：SQLite3をインストール
「手順1：SQLite3をインストール」に関して解説します。
SQLite3のテーブルにインデックス(index)を作成するためにも、SQLite3をインストールしましょう。
「SQLite3をインストールする手順」に関しては、お使いのパソコンごとに以下の記事を参照しインストールしましょう。
>>【コピペOK】SQLite3をインストールする手順【Linux向け】
>>【コピペOK】SQLite3をインストールする手順【Ubuntu向け】
>>【最短5分】SQLiteをWindowsへインストールする方法(6枚で図解)
>>【コピペOK】SQLite3をインストールする手順【MacOS向け】
以上で、「手順1：SQLite3をインストール」は完了です。
手順2：インストールしたSQLite3の動作確認
「手順2：インストールしたSQLite3の動作確認」に関して解説します。
動作確認として、インストールしたSQLite3に接続できることを確認しましょう。
「SQLite3に接続する方法」に関しては、以下の記事「SQLite3へ接続する方法」の箇所をご覧ください。
以上で、「手順2：インストールしたSQLite3の動作確認」は完了です。
手順3：SQLite3内にテーブルを作成
「手順3：SQLite3内にテーブルを作成」に関して解説します。
SQLite3のテーブルに対してインデックスを作成するため、SQLite3内にテーブルを作成しましょう。
「SQLite3内にテーブルを作成」に関しては、以下の記事をご覧ください。
以上で、「手順3：SQLite3内にテーブルを作成」は完了です。
以降の手順では、上記の記事で作成した「meibo」テーブルを想定として、手順を解説します。
手順4：SQLite3内に大量データを追加(データ登録)
「手順4：SQLite3内に大量データを追加(データ登録)」に関して解説します。
「インデックスを作成したらどのぐらい参照速度が向上するのか？」を検証するために、大量データをSQLite3に格納しましょう。
「SQLite3内にテーブルを作成」に関しては、以下の記事「手順4：SQLite3内に大量データを追加(データ登録)」の箇所をご覧ください。
上の記事に記載された「limit 10000」をより大きな値(例：limit 100000000)に変更することで、「インデックス有無での参照速度の差」がより顕著になります。
以上が、「手順4：SQLite3内に大量データを追加(データ登録)」となります。
手順5：大量データを登録したテーブルの参照速度を計測
「手順5：大量データを登録したテーブルの参照速度を計測」に関して解説します。
インデックスを作成していない状況で、大量データを登録したテーブルの参照速度を計測しましょう。
大量データを登録したテーブルの参照速度を計測するために、以下のコマンドを順に実行してください。
# ↓ SQLite3に接続 ↓
$ sqlite3 testdb.sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
# ↓ SQLite3内に存在するテーブルの定義を確認 ↓
sqlite> .schema
CREATE TABLE meibo (id integer, name text);
# ↓ 登録されたデータの数を確認 ↓
sqlite> select count(*) from meibo;
100000000
# ↓ クエリの所要時間を表示する設定 ↓
sqlite> .timer ON
# ↓ インデックス作成なしでの参照速度を計測 ↓
sqlite> select * from meibo where name = "tom50000";
50000|tom50000
Run Time: real 4.120 user 3.764187 sys 0.351849
sqlite>
コマンド実行後、「Run Time」が表示された場合、「正常にインデックス作成なしでの参照速度を計測できた」と判断できます。
★timerコマンドのリファレンス★
timerのコマンドリファレンスは、以下に公開されています。
timerコマンドを使うことで、クエリの所要時間を表示できます。
参照：timerのコマンドリファレンス(公式サイト)
以上が、「手順5：大量データを登録したテーブルの参照速度を計測」となります。
上記の流れで、SQLite3でインデックス(index)を作成するための準備ができました。
SQLite3でインデックス(index)を作成する方法と効果
「SQLite3でインデックス(index)を作成する方法と効果」に関して解説します。
SQLite3でテーブルにインデックス(index)を作成するためには、「CREATE INDEX文」を使います。
CREATE INDEX文の「構文」は、以下となります。インデックスは、単一カラムから複数カラム(複合カラム)を対象として作成することができます。
CREATE INDEX インデックス名 ON テーブル名(カラム名1, カラム名2, ...);
上記の構文をもとにして、以下の「CREATE INDEX文」を実行し、インデックスありでの参照速度を計測してみましょう。
# ↓ SQLite3に接続 ↓
$ sqlite3 testdb.sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
# ↓ SQLite3内に存在するテーブルの定義を確認 ↓
sqlite> .schema
CREATE TABLE meibo (id integer, name text);
# ↓ 登録されたデータの数を確認 ↓
sqlite> select count(*) from meibo;
100000000
# ↓ テーブルにインデックスを作成 ↓
sqlite> CREATE INDEX meibo_name_index on meibo(name);
# ↓ クエリの所要時間を表示する設定 ↓
sqlite> .timer ON
# ↓ インデックス作成ありでの参照速度を計測 ↓
sqlite> select * from meibo where name = "tom50000";
50000|tom50000
Run Time: real 0.003 user 0.000653 sys 0.000000
sqlite>
コマンド実行後、「Run Time」が表示された場合、「正常にインデックス作成ありでの参照速度を計測できた」と判断できます。
今回の検証例では、テーブルにインデックスを作成することで、参照速度が「4.120秒」から「0.003秒」に向上しました。
★CREATE INDEXのリファレンス★
CREATE INDEXのコマンドリファレンスは、以下に公開されています。
参照：CREATE INDEXのコマンドリファレンス(公式サイト)
以上が、「SQLite3でインデックス(index)を作成する方法と効果」となります。
SQLite3において、テーブルの参照速度を速くしたい場合、テーブルにインデックスを作成することを検討しましょう。
既存インデックスの再作成および変更手順
既存インデックスの再作成および変更手順に関してご紹介します。
★ご紹介事項★
・既存インデックスを再作成する方法
・既存インデックスにカラムを追加する方法
・既存インデックスからあるカラムをインデックスから削除する方法
上記の各項目に関して解説します。
既存インデックスを再作成する方法
照合シーケンスの定義変更(COLLATE句部分の変更)があった場合、既存インデックスを再作成する必要があります。その場合、「REINDEX」文を使います。
REINDEX文の「構文」は、以下となります。
reindex;
reindex 照合シーケンスの名称;
reindex スキーマ名.テーブル名;
reindex スキーマ名.インデックス名;
★REINDEXのリファレンス★
REINDEXのコマンドリファレンスは、以下に公開されています。
参照：REINDEXのコマンドリファレンス(公式サイト)
以上が、「既存インデックスを再作成する方法」となります。
既存インデックスにカラムを追加する方法
データ量の増加に伴い、既存インデックスに対して「カラムを追加したい」という要望がよく挙がります。
SQLite3の公式ドキュメントを調査したところ、「alter index」のような既存インデックスに対して、新しいカラムを追加するようなコマンドは用意されていません。
>> SQLite3のindexに関連するドキュメント記事一覧
そのため、既存インデックスを削除して、再度、CREATE INDEX文を実行する必要があります。
既存インデックスの削除方法に関しては、以下の記事をご覧ください。
>> SQLite3で作成済みインデックスを削除する1つの方法
既存インデックスからあるカラムをインデックスから削除する方法
データ量の減少に伴い、既存インデックスに対して「不必要なカラムを削除しファイル容量を削減したい」という要望がよく挙がります。
SQLite3の公式ドキュメントを調査したところ、「alter index」のような既存インデックスからあるカラムのみを削除するようなコマンドは用意されていません。
>> SQLite3のindexに関連するドキュメント記事一覧
そのため、既存インデックスを削除して、再度、CREATE INDEX文を実行する必要があります。
SQLite3は、既存インデックスを更新するコマンドがありません。そのため、既存インデックスを変更したい場合、インデックスを再構築する必要があります。
【まとめ】SQLite3でインデックス(index)を作成する方法
いかがでしたでしょうか？「CREATE INDEX文を実行して、SQLiteのテーブルにインデックスを作成し、参照速度が速くなること」を解説し、以下の悩みを解決しました。
★悩み★
・SQLite3においてインデックス(index)の作成ってできる？手順は？
・SQLite3でインデックス(index)を作成すると参照速度は速くなる？
・作成したインデックスの再作成、またはカラムの追加ってできる？
ぜひあなたも本記事を参考に「SQLite3のテーブルにインデックスを作成」を実践してください。
コメント