インデックスについて学んだので、備忘録です。
インデックスとは
索引。dbの検索性能を上げる
なぜ検索性能が上がるのかというと、インデックスをつけたカラムでは、レコードが作成される際にソートが行われ、検索したい文字列や数字を全レコード確認することなく、発見することができるから。
メリットとデメリット
メリット
- レコードの検索やソートの速度が上がる
デメリット
- レコード作成時にソートしないといけない関係で、書き込みの時間が増える
例
ブログなどの何かしらの記事を投稿するpostsテーブルについて考える。レコードは以下の感じ。
postsテーブル
| id | body | created_at |
|---|---|---|
| 1 | 本文です。 | 2022-01-01 12:00:00 |
| 2 | 本文です。 | 2022-01-01 12:01:00 |
| 3 | 本文です。 | 2022-01-01 12:02:00 |
| 4 | 本文です。 | 2022-01-01 12:03:00 |
ここで投稿された時間が新しい順に表示したいとする。sqlは以下の感じ。
select * from posts order by created_at desc
そうなった場合、ここでは全レコードをチェックしそれぞれ比較するなどの作業をdbが行なった上で並べ変えられたものが表示される。上記の例のように4レコードのみであれば、比較、表示はすぐなのだが、これが数千数万、時には何百万とレコードがあった場合に、パフォーマンスに著しい影響が出てしまう。
そこで、出てくるのがインデックス。
今回はcreated_atでソートしたいので、created_atにインデックスをつける。
こうすると、どうなるのかというと、新たにpostが作成された時に作成された順にあらかじめ並べておいたものをdbが保持するので、今回の例のようなcreated_atでソートしたい場合には、その保持されたソートを呼んでくるだけで済むので、該当のレコードの発見やソートを行って表示される速度が、何もしていないときと比べて違ってくる。
ソートについては、「レコード作成の段階でソートが行われる」ので、sqlでorder byがあっても素早く表示できるというのはなんとなく直感でわかるのだが、「素早く該当レコードを発見できる」というのが個人的にわからなかったのでそれについても以下で補足を。
インデックスをつけると、「レコード作成の段階でソートが行われる」というのがあったが、これが直接検索性能の向上の手助けをしている。
例えば以下のようなsqlがあったとする。postsテーブルから作成日時が'2022-01-01 12:02:00'のレコードを取得するsql。
select * from posts where created_at = '2022-01-01 12:02:00'
この場合、もしインデックスがなければ全レコードのcreated_atを見ていき、該当するものを見つけるという作業をdbがしないといけなくなる。
しかし、インデックスがついていれば(dbのアルゴリズム的な部分については全くの無知なので内部でどうなっているのかわからないが)、指定したcreated_atの近辺のレコードから検索を始め、すぐに該当のレコードを発見することができる。
これにより検索性能が上がったと言える。
ここまでのまとめ
- インデックスをつけるとレコード作成段階でソートが行われたものもdbが保持してくれるので、検索速度やソートの速度が上がる
複合インデックス
上記の一つのカラムにインデックスをつけるだけでは、意味がない時がある。そんな場合に複合インデックスというものを使う。これはその名の通り、複数のカラムにインデックスをつけることで、検索性能をあげるもの。
例
単一のインデックスの時と同様に、postsテーブルについて考える。ただし、今回はどのユーザーが投稿したのかを判断するためのuser_idカラムを持っているとする。
postsテーブル
| id | body | user_id | created_at |
|---|---|---|---|
| 1 | 本文です。 | 1 | 2022-01-01 12:00:00 |
| 2 | 本文です。 | 1 | 2022-01-01 12:01:00 |
| 3 | 本文です。 | 2 | 2022-01-01 12:02:00 |
| 4 | 本文です。 | 3 | 2022-01-01 12:03:00 |
| 5 | 本文です。 | 1 | 2022-01-01 12:04:00 |
| 6 | 本文です。 | 3 | 2022-01-01 12:05:00 |
まず、created_atにのみインデックスをつけていると、user_idに関係なくcreated_atの順にレコードがソートされたものが保持される。
例えば、user_idが1のレコードをcreated_atの順に取り出したいとなった場合、インデックスがcreated_atだけだと、
1. 全部のレコードを参照し、created_at降順で取り出す
2. 取り出した全部のレコードに対してuser_idが1のレコードを探す
という処理が発生する。これは2段階両方において全レコードへの参照が起きていて、効率的とは言えない。
できれば、全レコードの参照の回数を減らしたい。
ここで、全レコードへの参照の回数を減らせる方法があるとすれば、
1. 先にuser_idが1のpostを取り出して、
2. そのpostを降順でソートする
この方法が考えられる。
そこで、例えばrailsなら
add_index :posts, [:user_id, :created_at]
のように複合インデックスをつけることができる。
こうすれば、レコード登録時にuser_idとcreated_atでソートされるので、今回の例のようにuser_idを指定しつつ、created_atでソートして表示のような場合のレコード取り出しも高速に行えるようになる。今回の例で用いたpostsテーブルの複合インデックスをつけた場合にdbで保持されるレコードは以下のようになる。
| id | body | user_id | created_at |
|---|---|---|---|
| 1 | 本文です。 | 1 | 2022-01-01 12:00:00 |
| 2 | 本文です。 | 1 | 2022-01-01 12:01:00 |
| 5 | 本文です。 | 1 | 2022-01-01 12:04:00 |
| 3 | 本文です。 | 2 | 2022-01-01 12:02:00 |
| 4 | 本文です。 | 3 | 2022-01-01 12:03:00 |
| 6 | 本文です。 | 3 | 2022-01-01 12:05:00 |
user_idの数字順に並べて、同じuser_idがある場合はcreated_atが古い順に並べられる。
まとめ
- よく検索されたりソートされるカラムにインデックスをつけておくことで、レコード登録時にソートされるので、検索速度が上がる
- 複数条件で絞り込んで検索する場合は複合インデックスを検討すべき。参照すべきレコードを減らすことができるので、結果的に検索速度が上がる。
- インデックスにはデメリットもあり、レコード登録時にソートが行われる分書き込みに多少差が出る。
- レコードの数が多くなるほど効果を発揮する。
- 絞り込みまくって検索されることしかないテーブルなどはインデックスの意味がない可能性もある。