SQLとインデックスと

なぜ書いたか

SQLに苦手意識が強くて、今までなんと無く書いてしまっていたから。そろそろ真面目に理解しようと思って

インデックスとは？

データベースのインデックス（Index）は、データベース内のテーブルの特定の列（または複数の列）に対して、データの検索やクエリの高速化を支援するために作成されるデータ構造です。インデックスは、データベース内の特定の値がどこに格納されているかを示すための参照テーブルのようなものであり、効率的なデータアクセスを可能にします。

ChatGPT

どうやってインデックスつけるの？

プライマリキーを設定する or 以下のDDLで設定する。
（プライマリキーが設定されたカラムには自動でインデックスが貼られる）

CREATE INDEX [ name ] ON [ ONLY ] table_name
    ( column_name [, ...] )

-- sample
-- create index test_index on test_table (test_column);

ほんとにインデックスって効果あります？？

いざ、検証。
usersテーブルを作ってダミーデータを1000件ほどぶちこんでみる。

テーブル構成

テーブルの中身

EXPLAINで実行計画を見てみると、インデックスが使われているのがわかる。

explain select * from users where id = 1;

=> Index Scan using user_pk on users  (cost=0.28..8.29 rows=1 width=76)
=>   Index Cond: (id = 1)

条件を変えてみても、インデックスが貼られているカラムが含まれていればインデックスは使われている。先にインデックススキャンが走り、その後にgenderでフィルタされている様子。

explain select * from users where id = 1 and gendar = 'Male';

=> Index Scan using user_pk on users  (cost=0.28..8.29 rows=1 width=76)
=>   Index Cond: (id = 1)
=>   Filter: ((gendar)::text = 'Male'::text)

ぶっちゃけインデックスのありがたみがまだ分からないよね

ということで、あえて遅そうなLIKE検索を使ってみる。インデックスが貼られていないカラムに対してLIKE検索をかけると、1000行でも地味に時間がかかる。

explain analyse select * from users where email like '%.jp';

=> Seq Scan on users  (cost=0.00..26.50 rows=51 width=76) (actual time=0.358..1.165 rows=47 loops=1)
=>   Filter: ((email)::text ~~ '%.jp'::text)
=>   Rows Removed by Filter: 953
=> Planning Time: 7.316 ms
=> Execution Time: 1.794 ms

emailカラムにインデックスを貼ってみると・・・

インデックス作成後実行結果

速くなったように見えますよね？　はい、何も変わってません。おそらくキャッシュを見に行っているので速度は上がっていますが、インデックスは使用されていません。LIKE検索＆部分一致になっていることが原因です。

参考：https://stackoverflow.com/questions/61422684/postgres-like-doesnt-use-index

＜＜WIP!!＞＞