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

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