mysql 查询使用 in 索引失效的问题？

Question

表中的数据是这样的：

mysql> select * from tbl_student;
+----+----------+----------+---------------------+
| id | name     | class_id | create_time         |
+----+----------+----------+---------------------+
|  1 | James    |        1 | 2018-01-21 13:54:50 |
|  2 | Wade     |        1 | 2018-01-21 13:55:02 |
|  3 | Bosh     |        1 | 2018-01-21 14:04:45 |
|  4 | Paul     |        1 | 2018-01-21 14:04:54 |
|  5 | kobe     |        1 | 2018-01-21 14:05:14 |
|  6 | Rooney   |        2 | 2018-01-21 15:02:06 |
|  7 | Pogeba   |        2 | 2018-01-21 15:02:20 |
|  8 | Martia   |        2 | 2018-01-21 15:47:47 |
|  9 | Lingard  |        2 | 2018-01-21 15:48:15 |
| 10 | Simeone  |        2 | 2018-01-21 15:48:28 |
| 11 | Messi    |        2 | 2018-01-21 15:48:36 |
| 12 | Tina     |        2 | 2018-01-21 15:49:04 |
| 13 | Manu     |        2 | 2018-01-21 15:55:29 |
| 14 | Iversion |        1 | 2018-01-21 15:55:44 |
| 15 | Mata     |        1 | 2018-01-21 15:56:12 |
+----+----------+----------+---------------------+
15 rows in set (0.00 sec)

给字段 name 建立了索引

mysql> explain select * from tbl_student where name in ('Pogeba', 'Rooney');
+----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table       | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tbl_student | NULL       | range | name          | name | 51      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

当 in 有两个值的时候索引有效

mysql> explain select * from tbl_student where name in ('Pogeba', 'Rooney', 'Paul');
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_student | NULL       | ALL  | name          | NULL | NULL    | NULL |   15 |    20.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

当 in 里面有三个值的时候索引就失效了，这是为什么？

Answer 1

mysql查询优化器认为全表扫描时如果速度大于使用索引，就会不用索引，你可以使用FORCE INDEX强制mysql使用索引

Answer 2

可以看看showporcess或者执行计划看mysql到底将语句优化成什么样