データベースのインデックスについて、
どのくらい効果があるものなのか試してみました。
データベースはPostgres、
確認には付属のpgAdmin4を使用しています。
インデックスの作成はこちらのサイトを参考にしました。
https://www.postgresql.jp/docs/9.4/sql-createindex.html
テーブルは以下の内容で作成
create table testtable
(id integer,
name varchar(20),
code char(10),
description text);
テストデータは以下の形式で100万件のレコードをcsvファイルで用意
1,test1,cd1,NTPMJFVLCT
2,test2,cd2,YKNGYTBJTH
3,test3,cd3,GWEINNHHWH
4,test4,cd4,ETDSUDLYSO
5,test5,cd5,RASSDIITKV
6,test6,cd6,YRANXGOYEP
7,test7,cd7,JWNROFEWOM
8,test8,cd8,ORDMKAAIVB
9,test9,cd9,QPVCHULHSA
10,test10,cd10,IQJYJCNQTL
11,test11,cd11,UITUXBIUNN
…
確認したインデックスの使用方法は、
単一列でのインデックス、複数列でのインデックス、部分インデックスの３つになります。
単一列でのインデックス
description列を対象にインデックスを設定します。
CREATE INDEX index1 ON testtable (description);
確認用のSQLはdescriptionの内容をor条件で1000件分指定して取得するものになります。
select * from testtable where
description = 'OXTWJGUVWC' or
description = 'QWOOPYRYAO' or
description = 'KPFTEIPHJC' or
…
インデックス設定前
インデックス設定後
取得時間が8.072秒から0.136秒まで大幅に短縮できました。
複数列でのインデックス
name列とcode列を対象にインデックスを設定します。
CREATE INDEX index2 ON testtable (name, code);
確認用SQLはnameとcodeの内容をor条件で1000件分指定して取得。
select * from testtable where
(name = 'test300000' and code = 'cd300000') or
(name = 'test300001' and code = 'cd300001') or
(name = 'test300002' and code = 'cd300002') or
…
インデックス設定前
インデックス設定後
こちらも9.839秒から0.071秒まで短縮できました。
name列とcode列どちらか片方のみでのインデックスでも効果はありましたが、
両方指定したほうが若干早くなるようです。
name列のみでインデックス設定後
code列のみでインデックス設定後
部分インデックス
id列の1000以下を対象に部分インデックスを設定します。
CREATE INDEX index3 ON testtable (id) where id >= 1000;
確認用SQLはidの取得対象をor条件で個別に設定。
select * from testtable where
id =1 or
id =2 or
id =3 or
…
id:1～1000まで取得
id:1001～2000まで取得
id:1～1001まで取得
部分インデックスがの範囲がid1～1000までのため、
範囲対象外のレコードを対象にするとインデックスが使用されなくなりました。
id:1001のレコード1件を取得対象に含めただけで、取得時間が大幅に変わっています。
以上です。
単純に設定したインデックスでも、大きな効果があるのが分かりました。
複数列でのインデックスについても今回は単純な条件とテーブルだったため、単一列でのインデックスとそこまで差はありませんでしたが、複雑な条件やデータ構成などでより複数列インデックスの方がより有効になりそうです。