ミライトデザイン Advent Calendar 2023 の19日目の記事です。
昨日は @__tomotomon さんの Fork の使い方の記事でした。
はじめに
この記事は、同じチームで働いているほげさんのインデックスの記事と @Nyokki さんが社内勉強会で教えていただいた内容を、ここはおさえておきたいと思った内容をまとめ直して記事にしたものです。
もっと詳しく知りたい人は下記のほげさんの記事を読むか、@Nyokki さんに聞いてください。
目的
DB インデックスを簡単に理解して、分からない状態から脱却するのが目的です。
Web のシステムで処理が遅い遅い時って大体、ネットワークか、ファイル IO か、DB あたりな気がします。
DB インデックスを利用することで DB の検索速度を爆速にできるかもしれないので、理解しておくといざという時役に立つかもしれません。
インデックスとは
そもそもインデックスとはなんでしょうか？
言葉そのものの意味で言うと、インデックスとは「索引」や「見出し」と行った意味になります。
DB インデックスもほとんど同じです。
技術書とかの本である単語を探したい時に、本をはじめのページから全てめくって探し始めるとかなり時間がかかってしまいますよね。
索引があると、単語が五十音順に並んでいて、何ページ目に書いてあるか教えてくれるので、探す時間が格段に短くなります。
単語でインデックスが作成されている状態です。
本であれば、検索したいのは主に単語でしょうから、単語のインデックスがあればいいですが、これが DB のユーザーテーブルだったら、ID、名前、年齢などで検索したい場合が考えられます。
なので、検索したいカラムでインデックスを作成しておくと、検索スピードを上げることができます。
インデックスの種類
この記事では深掘りはしないので、インデックスの種類によって得意なことが変わってくるんだなくらいに思っておいていただければ。
インデックスには使用しているアルゴリズムによっていくつか種類があります。
- ハッシュインデックス
- Bツリーインデックス
- ビットマップインデックス
アルゴリズムによって得意なことが変わってきます。
例えばハッシュインデックスなら、ハッシュを利用して検索するので、完全一致は早いですが、部分一致には使えません。
最もメジャーなインデックスは、Bツリーインデックスなのでこの記事では、インデックス = Bツリーインデックスとして記載しています。
インデックスが使用される検索
・ 完全一致
SELECT * FROM users WHERE user_id='00800001';
・ 前方一致
SELECT * FROM users WHERE name LIKE 'miraito%';
・ 範囲検索
SELECT * FROM users WHERE age BETWEEN 18 AND 24;
インデックスが使用されない検索
・ 中間一致と後方一致
SELECT * FROM users WHERE name LIKE '%miraito%';
SELECT * FROM users WHERE name LIKE '%miraito';
完全一致と前方一致が得意で、中間一致と後方一致が苦手なのは、索引でも同じなのでイメージが付きやすいのではないでしょうか。
索引でも、頭の文字がわかってないと効率的に探せないですよね。
・ 否定検索（!=, <>, NOT IN）
SELECT * FROM users WHERE name <> 'miraito';
・ クエリ内でインデックスカラムが変更されている場合
SELECT * FROM scores WHERE score + 10 > 1050;
SELECT * FROM users WHERE UPPER(name) = 'MIRAITO';
どちらの場合も、インデックスで管理されているはずの値を変更してしまっています。
結果、インデックスに無い値で検索をしようとしていることになるので、遅くなってしまうようです。
ただ、上記のクエリは工夫することでインデックスを利用した検索にすることができます。
SELECT * FROM scores WHERE score > 1050 - 10;
SELECT * FROM users WHERE name = LOWER('MIRAITO');
インデックスカラムの値をそのまま使うことで、インデックスを利用した検索ができるようになります。
注意が必要なカラム
・ text 型
MySQL と PostgreSQL どちらも text 型にインデックスを貼ることはできるようですが、注意が必要な場合があるようです。
そもそも text 型でインデックスが利用できる検索方法（完全一致、前方一致）よりも、中間一致で検索したい場合が多そうなので、全文検索エンジンの利用などを検討した方が良さそうです。
どのカラムにインデックスを作成すればいいのか
作成すれば検索やソートが早くなる便利なインデックスですが、ただ適当に作成すればいいというものでもありません。
インデックスを効率的に作成するにはカーディナリティが高いカラムに作成する必要があります。
カーディナリティと聞くと難しそうな気がしますが、たいして難しい話ではありません。
- カーディナリティが低い例
- user の性別
- カーディナリティが高い例
- user の ID
登録できる性別が男女の２択だった場合は、性別で検索するとざっくり 1/2 まで絞り込めますよね。
性別カラムにインデックスを作成した場合、インデックスの効果が得られるのは 1/2 までということになります。
user の ID だった場合はインデックスを利用して一意にまで絞りこむことができます。
誕生日 にインデックスを作成すれば、おそよ 1/365 にまで絞りこめるので、性別よりも効果的と言えます。
マルチカラムインデックス
例えば、ユーザーのステータス（無料会員、有料会員、プレミアム会員みたいな）とユーザーの名前が保存されているテーブルがあったとして、
ステータスインデックス と 名前インデックス という２つのインデックスを同時に利用するといったことはできませんが、
名前 - ステータス インデックス を作成することができます。
マルチカラムインデックスには優先順位があります。
名前 - ステータス インデックスと ステータス - 名前 インデックスでは検索結果の速度が異なることがあるということです。
名前 - ステータス インデックスではまず、名前で絞り込まれた後に、ステータスで絞り込まれるのに対して、
ステータス - 名前 インデックスではステータスで絞り込まれた後に、名前で絞り込まれます。
ステータスと名前では、名前の方がカーディナリティが高そうですよね。
カーディナリティの高い名前で先に絞り込んだ後に、ステータスで絞り込んだ方が良さそうなので、名前 - ステータス インデックスを作成した方が効率的です。
インデックスを作成することによるデメリット
インデックスを作成した方が検索が早くなるのであれば、とりあえず全てのカラムにインデックスを作成してしまえばいいと思うかもしれませんが（私は思いました）、インデックスを大量に作成してしまうことによるデメリットも存在します。
インデックスを大量に作成してしまうことに対するデメリットは、INSERT / UPDATE / DELETE などが遅くなってしまうことです。
インデックスもデータとして保存されているので、DB の内容が更新されると合わせてインデックスの更新も必要になります。
そのため、更新系のクエリは遅くなってしまいます。
とりあえず作成しとくは NG。
インデックスのメンテナンス
ここでは深く触れませんが、インデックスもデータとして管理されているので、インデックスの情報が狂ってしまうこともあるようです。
インデックスが効いているか確認するには？
ここまでインデックスの勉強をしてきたので、インデックスが本当に効いているか確認したいですよね？
SQL には書いたクエリがどんな動きをするのか教えてくれる実行計画というものがあります。
例えば、 user のデータが入った user テーブルがあったとして、
まず、user テーブルに入っている user の情報を name で検索してみます。
mysql> SELECT * FROM user WHERE name="neemdhsxvw";
+------------+------+--------+
| name | age | gender |
+------------+------+--------+
| neemdhsxvw | 29 | F |
+------------+------+--------+
1 row in set (0.22 sec)
実行計画が見たいクエリの前に EXPLAIN とつければ確認することができます。
実際にクエリが実行されるわけでは無いので安心です。
mysql> EXPLAIN SELECT * FROM user WHERE name="neemdhsxvw";
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 997458 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
ここでは type と key を確認します。
Type
-
どうやってデータにアクセスするかが書いてあります。
- ref
- インデックスを使って検索。
- ALL
- フルテーブルスキャン。インデックスを利用していない。
- ref
-
他にも種類があるので調べてみてください。
row
- だいたいどれくらいの行の中から探すことになりそうか書いてあります。
- 実際の値ではなくあくまで予想です。
- 上記で記載した、インデックスの情報が狂っている場合などは全然違ったりするかもしれません。
今回だと type が ALL になっているので、インデックスを利用しないフルスキャンになっています。
row も 997,458 件から探すようになっています。
name にインデックスを追加してもう一度実行計画を確認します。
-- user テーブルの name にインデックスを追加
mysql> ALTER TABLE user ADD INDEX idx_name(name);
Query OK, 0 rows affected (1.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- もう一度、実行計画を取得
mysql> EXPLAIN SELECT * FROM user WHERE name="neemdhsxvw";
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | idx_name | idx_name | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
今度は type が ref (インデックスを使った検索)、rows は 1 件になっています。
実際に実行して見ます。
mysql> SELECT * FROM user WHERE name="neemdhsxvw";
+------------+------+--------+
| name | age | gender |
+------------+------+--------+
| neemdhsxvw | 29 | F |
+------------+------+--------+
1 row in set (0.00 sec)
速度も 0.22 sec から 0.00 sec になっていて早くなっているのもわかります。