こんにちは！
Web バックエンド担当の松村です。
DB に触れる中で「聞いたことはある」「なんとなく使ったことあるけどよく分からない」となりそうな INDEX について使い方を紹介します。
INDEX とは？
DB における INDEX とは、テーブルへの検索を高速化するために参照される仕組みです。
INDEX がない場合、テーブルへの検索は先頭行から全てレコードを読み取ります(フルスキャン)が、INDEX を参照すればテーブル全体を検索せずに対象レコードの位置を特定(インデックススキャン)することができます。
INDEX は索引と訳せますが、言葉の通り、本の索引や目次をイメージすると良いでしょう。
メリット
前述した通り、「テーブルへの検索を高速化」できることです。
レコードが増えれば増えるほどテーブルへの処理が重くなり、サービスに悪影響を与えかねません。 INDEX は DB のパフォーマンスを向上させ、サービスの質やユーザーからの評価を高めることに繋がります。
ただし、レコードの少ないテーブルに対しては効果があまりないということを注意してください。 あくまで目安ですが、レコード件数が1万件を超えると高速化が顕著になります。
使用方法
DB エンジンはいくつか存在しますが、今回は MySQL を例に使用方法を見ていきましょう。 INDEX を使用することを俗に「 INDEX を張る1」と言います。
0. 前提
例として、以下のような users テーブルを対象に INDEX を張ってみます。 10万件のレコードが挿入されています。
| id | name | age | is_manager | |
|---|---|---|---|---|
| 1 | 田中 | ---@example.com | 20 | 1 |
| 2 | 佐藤 | ---@example.com | 40 | 1 |
| 3 | 鈴木 | ---@example.com | 36 | 0 |
| ・ | ||||
| ・ | ||||
| ・ | ||||
| 100000 | 松村 | ---@example.com | 58 | 0 |
この users テーブルに対し以下2つのクエリを実行する想定で進めます。
A... id が100000のレコードを取得する
B... 40歳でマネージャーであるレコードを取得する
1. 実行計画を確認する
INDEX を張る前に現状のパフォーマンスを確認しましょう。
EXPLAINを用いると、クエリがどのように実行されるのかを知ることができます。
これを「実行計画」と言い、参照する INDEX や実際の調査対象となったレコード数を確認できます。
EXPLAINの出力カラムについて、今回注目するのは以下の2点です。
それ以外のカラムを詳しく知りたい方は公式ドキュメントを確認してください。
| 注目するカラム | 意味 |
|---|---|
| key | 実際に参照された INDEX |
| rows | 調査される行の見積もり |
AとBそれぞれのクエリに対して、EXPLAINの結果は以下です。 INDEX を張っていないため、どちらも INDEX が参照されず、97940件も調査対象となっています。
取得にはおおよそ0.06秒かかりました。
かなり速い取得ができていますが、1つの処理で何度もクエリを発行したり、レコード数やカラム数の増加によって処理時間は線形に増えていきます。 サービスのリリース時や成長時に問題となるため、しっかりと使いましょう。
-- A のクエリ mysql> EXPLAIN SELECT * FROM users WHERE id = 10000; +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 97940 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
-- B のクエリ mysql> EXPLAIN SELECT * FROM users WHERE age = 40 AND is_manager = 1; +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 97940 | 3.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
2. INDEX を張る
以下のようにして、クエリの条件となるカラムに対して INDEX を作成します。
CREATE INDEX query_a_index ON users(id); CREATE INDEX query_b_index ON users(age, is_manager);
また、テーブルを作成する際の制約で INDEX を作成することも可能です。
CREATE TABLE users ( id INT, name VARCHAR(30), email VARCHAR(30), age INT, is_manager TINYINT, PRIMARY KEY (id) -- PRIMARY KEY 制約も INDEX );
3. 実行計画の変化を確認する
以下が INDEX を張った後のEXPLAINと実際の実行結果です。
参照された INDEX がkeyに表示され、調査対象のレコード(rows)は1件に減っています。
もともと取得時間は遅くありませんでしたが、結果として0.00066秒や0.0014秒と短縮することができました。
-- A のクエリ mysql> EXPLAIN SELECT * FROM users WHERE id = 10000; +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | users | NULL | ref | query_a_index | query_a_index | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
-- B のクエリ mysql> EXPLAIN SELECT * FROM users WHERE age = 40 AND is_manager = 1; +----+-------------+-------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | users | NULL | ref | query_b_index | query_b_index | 7 | const,const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+---------------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
気を付けるポイント
カラムの順序を考える
複数のカラムを指定した複合 INDEX (今回だとquery_b_index)の場合、カーディナリティに基づいた順序を考慮する必要があります。
考慮することで、INDEX をより効果的にすることができます。
また、カーディナリティとは、カラム内データの一意な数で、query_b_indexのカラムで例えると以下です。
高い順にカラムを指定することで、調査対象となるレコードを少なくすることができます。
| カラム | 範囲 | カーディナリティ |
|---|---|---|
| age | 整数、年齢なので 0 ~ 100 程度と広い | 高い |
| is_manager | 真偽値、0 or 1 のどちらかなので狭い | 低い |
-- 良い例 CREATE INDEX query_b_index ON users(age, is_manager); -- 悪い例 CREATE INDEX query_b_index ON users(is_manager, age);
INDEXを参照されない場合がある
せっかく INDEX を作成しても、参照されないことがあります。
LIKEを用いた曖昧検索をしていたり、IS NULLやIS NOT NULLを条件にしたり2することで発生します。
レコード数が少ないテーブルに張らない
レコード数が少ない場合、インデックススキャンよりもフルスキャンの方が大抵速いです。 サービスの規模やデータ数の想定を意識しましょう。
終わりに
DB のチューニングはサービスの評価に大きく影響します。 ぜひ、INDEX を使って最高のプロダクトを作ってください。
次回の「Web エンジニアなら知っておきたい」シリーズもお楽しみに。
参考
- インデックスやリンクの"はる"は「張る」か「貼る」なのか↩
- DB エンジンによって異なる↩
