TL;DR
- インデックスは、検索する項目や結合キーとなる項目に貼る。
- インデックスは、値が一意になる項目や取りうる値の種類が多い項目に貼る。
- 検索でレコード数が絞り込める項目に貼る。
- 複合インデックスの項目記述順に合わせて、where句の順番を指定すると、インデックスが使われる。
- or条件はインデックスが使われない。
- 前方一致検索は、インデックスが使われる。
- 例外もあるので、ちゃんとインデックスが使われるかどうかは、explainで確認する。
以下、explainを使って検証してみる。
検証用のテーブルやインデックス
create table t_test1 (
id int not null,
col1 varchar(30) not null,
col2 varchar(30) not null,
col3 varchar(30) not null,
col4 varchar(30) not null,
test2_id int,
primary key(id),
foreign key(test2_id) references t_test2(id)
);
create table t_test2 (
id int not null,
col1 varchar(30) not null,
primary key(id)
);
CREATE INDEX idx1 ON t_test1(col1);
CREATE INDEX idx2 ON t_test1(col2, col3, col4);
t_test1には900万件、t_test2には10件、データを投入した。
値が一意になる項目をインデックスを使って検索
mysql> explain select * from t_test1 where id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_test1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
typeがconstとなっており、これは、一意なインデックスを参照していることを意味する。
rowsは1となっており、1レコードに絞り込まれたことを意味する。
一意なインデックスを使って、1レコードに絞り込まれたことがわかる。
取りうる値の種類が多い項目をインデックスを使って検索
mysql> explain select * from t_test1 where col1 = 'data1-1';
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_test1 | NULL | ref | idx1 | idx1 | 122 | const | 1000 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
typeがrefとなっており、これは、インデックスを参照していることを意味する。
keyがidx1となっており、インデックスidx1が使われたことを意味する。
rowsは1000となっており、1000レコードに絞り込まれたことを意味する。
インデックスを参照して、1000レコードに絞り込まれたことがわかる。
インデックスを使って結合する
mysql> explain select * from t_test1 t1 inner join t_test2 t2 on t1.test2_id = t2.id;
+----+-------------+-------+------------+------+---------------+----------+---------+---------------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+---------------+---------+----------+-------+
| 1 | SIMPLE | t2 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 10 | 100.00 | NULL |
| 1 | SIMPLE | t1 | NULL | ref | test2_id | test2_id | 5 | test_db.t2.id | 1119155 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+---------------+---------+----------+-------+
t_test2 t2を元にt_test1 t1を結合するexplainになっている。
t2の1レコードにつき、1119155レコード読んでいる。
インデックスが使われない場合、900万レコード読むことになるので、インデックスが効いていることになる。
where句の記述順によるインデックスが使われる/使われない
idx2の記述順に完全一致
mysql> explain select * from t_test1 where col2 = 'data2-1' and col3 = 'data3-1' and col4 = 'data4-1';
+----+-------------+---------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | t_test1 | NULL | ref | idx2 | idx2 | 366 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
typeがrefとなっており、これは、インデックスを参照していることを意味する。
rowsは1となっており、行数が1レコードに絞り込まれたことを意味する。
インデックスを使って、1レコードに絞り込まれたことがわかる。
idx2のcol2,col3まで記述順と一致
mysql> explain select * from t_test1 where col2 = 'data2-1' and col3 = 'data3-1';
+----+-------------+---------+------------+------+---------------+------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | t_test1 | NULL | ref | idx2 | idx2 | 244 | const,const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+-------------+------+----------+-------+
これもインデックスが使われる。
idx2のcol2,col4でwhere句を記述
mysql> explain select * from t_test1 where col2 = 'data2-1' and col4 = 'data4-1';
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | t_test1 | NULL | ref | idx2 | idx2 | 122 | const | 1 | 10.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
これもインデックスが使われる。
ただし、refにconstが1つしかない。col2とcol4が使われているならば、const,constとなる。
つまり、col2のみインデックスが使われている。
col3をスキップしたため、col4もインデックスが使われていない。
idx2のcol3でwhere句を記述
mysql> explain select * from t_test1 where col3 = 'data3-1';
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | t_test1 | NULL | ALL | NULL | NULL | NULL | NULL | 8953245 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
8953245レコード参照しており、これはインデックスが使われない。
idx2のcol3,col4でwhere句を記述
mysql> explain select * from t_test1 where col3 = 'data3-1' and col4 = 'data4-1';
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | t_test1 | NULL | ALL | NULL | NULL | NULL | NULL | 8953245 | 1.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
8953245レコード参照しており、これもインデックスが使われない。
idx2のcol3,col2でwhere句を記述
mysql> explain select * from t_test1 where col3 = 'data3-1' and col2 = 'data2-1';
+----+-------------+---------+------------+------+---------------+------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | t_test1 | NULL | ref | idx2 | idx2 | 244 | const,const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+-------------+------+----------+-------+
複合インデックスと記述順が違うため、インデックスが使われない想定だが、使われている。
オプティマイザが良しなに順番変更して解釈してくれた模様。
or条件でwhere句を記述
mysql> explain select * from t_test1 where col2 = 'data2-1' or col3 = 'data3-1';
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | t_test1 | NULL | ALL | idx2 | NULL | NULL | NULL | 8953245 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
インデックスが使われていない。
or条件だと、col2の検索とは別にcol3を検索しないといけないからであろう。
前方一致検索
mysql> explain select * from t_test1 where col1 like 'data1-1%';
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------+
| 1 | SIMPLE | t_test1 | NULL | range | idx1 | idx1 | 122 | NULL | 2161394 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------+
前方一致検索は、インデックスが使われる。