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

学习笔记 马富天 2020-02-10 15:32:02 71 0

【摘要】本文记录一下 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;
  6. INSERT INTO `student` VALUES ('1', '刘一');
  7. INSERT INTO `student` VALUES ('2', '陈二');
  8. INSERT INTO `student` VALUES ('3', '张三');
  9. INSERT INTO `student` VALUES ('4', '李四');
  10. INSERT INTO `student` VALUES ('5', '王五');
  11. INSERT INTO `student` VALUES ('6', '赵六');
  12. INSERT INTO `student` VALUES ('7', '孙七');
  13. INSERT INTO `student` VALUES ('8', '周八');
  14. INSERT INTO `student` VALUES ('9', '吴九');
  15. 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 会认为全表扫描比使用索引快,所以不走索引。

版权归 马富天个人博客 所有

本文标题:《MySQL 索引在 in 语句中失效的情况》

本文链接地址:http://www.mafutian.com/437.html

转载请务必注明出处,小生将不胜感激,谢谢! 喜欢本文或觉得本文对您有帮助,请分享给您的朋友 ^_^

0

0

上一篇《 MySQL 中的 using 用法 》 下一篇《 使用 Logstash 将 MySQL 数据同步到 Elasticsearch(版本6.5.1) 》

暂无评论

评论审核未开启
表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情
验证码

TOP10

  • 浏览最多
  • 评论最多