0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

どこにインデックスを貼るとか、ちゃんとインデックスを使うwhere句とか

Last updated at Posted at 2024-01-07

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 |
+----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------+

前方一致検索は、インデックスが使われる。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?