今日は、データベースのインデックスについてお話ししたいと思います。ご存じの通り、SQLインデックスはクエリの検索パフォーマンスを向上させ、データベースの効率を高めるためのものです。
インデックス作成
例：CREATE INDEX idx_name ON Student (name);
複合カラムインデックスもよく使われています。
例：
CREATE INDEX idx_name_class ON Student (name, class);
インデックス削除
例：DROP INDEX idx_name ON Student;
Indexを作成したら、EXPLAIN でindexの使用状況を確認できます。実行したら、以下のような結果が得られます。
EXPLAIN SELECT * FROM Student WHERE name = 'John';
+----+-------------+---------+------------+------+---------------+----------+-----+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | Student | NULL | ref | idx_name | idx_name | 767 | const| 1 | 100.00 | Using index|
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
説明：
- id: クエリの選択部分の識別子。外部のクエリの場合は1です。
- select_type: SELECTのタイプ。この場合はSIMPLEで、サブクエリやUNIONのない単純なSELECT。
- table: アクセスされているテーブル。
- type: 使用されているアクセス方法のタイプ（例：フルテーブルスキャンの場合はALL）。
- possible_keys: 使用可能なインデックス。
- key: オプティマイザによって実際に選択されたインデックス。
- key_len: 使用されたキーの長さ。
- ref: インデックス名で指定された列または定数。
- rows: クエリを実行するためにMySQLが調査する行の推定数。
- filtered: テーブル条件によってフィルタリングされた行の割合。
- Extra: クエリの実行に関する追加情報。
初めてのindexなら、私はまずpossible_keysにこのindexが考えられるかどうかを確認します。次に、実際に選択されたindexとクエリを実行する情報を確認します。
最も重要なのは rows 列です。なぜなら、これはMySQLがクエリに回答するために処理する必要がある行の数を示しています。extra の情報もクエリの実行方法をより理解しやすくします。例えば、extra に ‘
using index condition; using where;‘ と書かれている場合、インデックスを使用した後に結果をフィルタリングする必要があったことを示します。
複数の複合カラムインデックスを作成して、運用する時、考え通りに動かないことがありますね。explainを実行すると、possible_keysにはあるのに、使われていませんといったケースが発生することがあります。また、使われていても、クエリの効率が上がらないこともあります。
それは、複合カラムインデックスに設定されたカラムの順序のためかもしれません。
この順序と実行されたクエリ条件の順番が異なる場合、使われてもらっていない状況があります。そのため、複合カラムインデックスを作成する際は、順序が重要です。頻繁にクエリの条件として使用される列が左側にあるほど良いです。
また、列の一意性が高いほど、より多くの値をフィルタリングでき、検索の速度が向上します。列の分散性も考慮する必要があります。分散性は、各値の数量の差異を示します。
迷った時、Index Hints というものを使ってクエリにするINDEXを指定して違うIndexを使用してexplainで結果を考えることも便利と思います。
USE INDEX はindexを使用することを提案しますが、必ずしも指定通りには使用されません。FORCE INDEX は指定したINDEXのみを使用します。
例：
SELECT *FROM Student USE INDEX (idx_age)WHERE age > 20;