やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3構文　index（create/drop）

SQLite3構文　index（create/drop）

SQLite3

　インデックスの生成・削除。

成果物

情報源

インデックスとは

　データを高速に取得する方法。B木など何らかのアルゴリズムを用いる。

　インデックス用データを保存するディスクスペースが必要。また、データ挿入時にインデックス用データを更新する処理も必要。

　公式からは説明を見つけられなかった。

メリット

  • 検索が速くなる

デメリット

  • 挿入が遅くなる
  • ファイルサイズ肥大化

Index操作

やってみる

create table T(A int);

sqlite3 :memory: \ "create table T(A int);" \

create index

0.sql

create table T(A int);
create index idx_T_A on T(A);
.indexes

　インデックスの生成を確認した。

sqlite3 :memory: ".read 0.sql"

idx_T_A

　なぜか以下だと出ない。

sqlite3 :memory: < 0.sql





　対話モードでも確認できた。



sqlite3







sqlite> create table T(A int);
sqlite> create index idx_T_A on T(A);
sqlite> .indexes
idx_T_A








create unique index



1.sql



create table T(A int);
create unique index idx_T_A on T(A);
.indexes







idx_T_A





　インデックスの生成を確認した。



UNIQUE制約エラー確認



1_unique.sql



create table T(A int);
create unique index idx_T_A on T(A);
insert into T values(0);
insert into T values(0);
.indexes







Error: near line 4: UNIQUE constraint failed: T.A





　テーブルの列にunique制約を付与したときと同じ反応。



　もしかして、テーブルの列にunique制約をつけるより、uniqueインデックスを作ったほうがいいのかな？　drop可能だし、検索の高速化に使えるし。



後からUNIQUE制約追加



1_unique_add.sql



create table T(A int);
insert into T values(0);
insert into T values(0);
create unique index idx_T_A on T(A);
.indexes







Error: near line 4: UNIQUE constraint failed: T.A





　重複データがある状態で、その後にUNIQUE制約つきインデックスを追加してみた。追加後にエラーが出た。



rm -f a.db
sqlite3 a.db \
"create table T(A int);" \
"insert into T values(0);" \
"insert into T values(0);" \
"create unique index idx_T_A on T(A);" \
".indexes" \
"select * from T;"







sqlite3 a.db "select * from T;"
rm -f a.db







0
0




　だが、当然テーブルには重複したデータが残っている。






create index if not exists



2.sql



create table T(A int);
create index if not exists idx_T_A on T(A);
.indexes







idx_T_A





　インデックスの生成を確認した。



存在時にエラーにならないことを確認



2_exists.sql



create table T(A int);
create index if not exists idx_T_A on T(A);
create index if not exists idx_T_A on T(A);
.indexes







idx_T_A





　インデックスの生成を確認した。



存在時にエラーになることを確認



　if not existsを使わずに同名のインデックスを作成する。



2_error.sql



create table T(A int);
create index idx_T_A on T(A);
create index idx_T_A on T(A);
.indexes







Error: near line 3: index idx_T_A already exists





　だから作成するときはif not existsつけたほうがいい。






create index where






3.sql



create table T(A int);
create index idx_T_A on T(A) where is idx_T_A not null;
.indexes







idx_T_A





　インデックスの生成を確認した。



　部分インデックスとは、条件に一致したレコードだけを対象にしたインデックス。たとえば上記コードにて、ほとんどの行のAの値がNULLだとしたら、ディスクスペースがかなり節約できるらしい。






drop index



4.sql



create table T(A int);
create index idx_T_A on T(A);
.indexes
drop index idx_T_A;
.indexes







idx_T_A





　インデックスの削除を確認した。






drop index if exists



5.sql



create table T(A int);
create index idx_T_A on T(A);
.indexes
drop index if exists idx_T_A;
.indexes











　インデックスの削除を確認した。



存在しないのに削除しようとしてもエラーなし



5_not_exists.sql



create table T(A int);
.indexes
drop index if exists idx_T_A;
.indexes











存在しないのに削除しようとするとエラー



　if existsを使わずにやるとエラー。



5_error.sql



create table T(A int);
.indexes
drop index idx_T_A;
.indexes







Error: near line 3: no such index: idx_T_A





　だから削除するときはif existsつけたほうがいい。



まとめ







create
drop
効果






if not exists
if exists
定義存在エラー回避




unique

一意制約追加（drop可）




where

対象データの選別








速さについて



　古いけどDBMSの速さについて以下のような検証をみつけた。SQLite3が圧倒な大差をつけてトップ。これをみるともうSQLite3以外ありえない。Oracleは許可なくベンチマーク公開禁止とか怖い。論外。






どうすれば速くなる？



　思うのだが、selectで1000件返すときに非同期で10件ずつ返すように実装できないのだろうか？　プログラミング言語ならできるのだが、SQLにも欲しい。あれば体感速度があがる。



　時間かかるのはそこじゃないのかな？　件数が増えると最低限必要な時間が長くなるのが問題なのかな？



対象環境






$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux





前回まで