MySQL 索引在 in 语句中失效的情况

【摘要】本文记录一下 MySQL 索引在 in 语句中失效的情况。

以下是会用到的数据：

1. CREATE TABLE `student` (

2.   `id` int(11) NOT NULL AUTO_INCREMENT,

3.   `name` varchar(255) DEFAULT NULL,

4.   PRIMARY KEY (`id`)

5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

7. INSERT INTO `student` VALUES ('1', '刘一');

8. INSERT INTO `student` VALUES ('2', '陈二');

9. INSERT INTO `student` VALUES ('3', '张三');

10. INSERT INTO `student` VALUES ('4', '李四');

11. INSERT INTO `student` VALUES ('5', '王五');

12. INSERT INTO `student` VALUES ('6', '赵六');

13. INSERT INTO `student` VALUES ('7', '孙七');

14. INSERT INTO `student` VALUES ('8', '周八');

15. INSERT INTO `student` VALUES ('9', '吴九');

16. INSERT INTO `student` VALUES ('10', '郑十');

正常情况1：走索引

1. mysql> explain select * from `student` where id in (1,2,3,4);

2. +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

3. | id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |

4. +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

5. |  1 | SIMPLE      | student | range | PRIMARY       | PRIMARY | 4       | NULL |    4 | Using where |

6. +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

失效情况1：不走索引

当in后面的值超过一定个数后，就会分析消耗，最后判断不走索引，认为扫描全表比使用索引快，因此自然不会使用索引。

1. mysql> explain select * from `student` where id in (1,2,3,4,5);

2. +----+-------------+---------+------+---------------+------+---------+------+------+-------------+

3. | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |

4. +----+-------------+---------+------+---------------+------+---------+------+------+-------------+

5. |  1 | SIMPLE      | student | ALL  | PRIMARY       | NULL | NULL    | NULL |   10 | Using where |

6. +----+-------------+---------+------+---------------+------+---------+------+------+-------------+

此时，我们继续往表中插入数据：

1. INSERT INTO `student`(name) VALUE('hello'),('hello'),('hello'),('hello'),('hello'),('hello'),('hello'),('hello');

此时上一条中的失效情况1又变成了正常情况，如下：

1. mysql> explain select * from `student` where id in (1,2,3,4,5);

2. +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

3. | id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |

4. +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

5. |  1 | SIMPLE      | student | range | PRIMARY       | PRIMARY | 4       | NULL |    5 | Using where |

6. +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

然而，我们再继续往 in 中添加数据(1,2,3,4,5,6)，此时又不走索引了：

1. mysql> explain select * from `student` where id in (1,2,3,4,5,6);

2. +----+-------------+---------+------+---------------+------+---------+------+------+-------------+

3. | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |

4. +----+-------------+---------+------+---------------+------+---------+------+------+-------------+

5. |  1 | SIMPLE      | student | ALL  | PRIMARY       | NULL | NULL    | NULL |   18 | Using where |

6. +----+-------------+---------+------+---------------+------+---------+------+------+-------------+

最后总结：MySQL 在 in 语句是否走索引与表中数据量大小和 in 语句中个数有关；

当 in() 中个数较少时，表中数据量较多时，会走索引；

当 in() 中个数较多时，或者达到已经长度时, MySQL 会认为全表扫描比使用索引快，所以不走索引。

我要打赏