mysql in 可以索引吗_【MySQL】使用In时是否使用索引？

一、结论

MySQL 的 in 查询在 5.5 以上的版本中存储引擎都是 innodb 的，正常情况下会走索引的！如果是 5.5 之前的版本确实不会走索引的，在 5.5 之后的版本，MySQL 做了优化。MySQL 在 2010 年发布 5.5 版本中，优化器对 in 操作符可以自动完成优化，针对建立了索引的列可以使用索引，没有索引的列还是会走全表扫描。

MySQL使用了基于成本的优化器，在小表中，按索引访问实际上比表扫描更昂贵(I/O方面)，MySQL的优化器可能会考虑到这一点。所以表中的记录数量会影响mysql是否使用索引。

二、测试

1、创建表

CREATE TABLE `tuser` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(100) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`sex` char(1) DEFAULT NULL,

`address` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_name_age` (`name`,`age`),

KEY `idx_sex` (`sex`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

复制代码

2、测试1

测试sql语句

EXPLAIN

SELECT * FROM `tuser` where id in (1)

复制代码

执行计划：

可以看到执行计划中并没有使用索引，因为表中没有数据，in集合中只有一个值，此时相当于SQL语句：

SELECT * FROM `tuser` where id =1

复制代码

可以直接判断出没有命中表中的数据，也就没有使用索引的必要。

3、测试2

测试sql语句

EXPLAIN

SELECT * FROM `tuser` where id in (1,2)

复制代码

很明显使用到了主索引。

3、测试3

测试SQL

EXPLAIN

SELECT * FROM `tuser` where id in (1,2,3)

复制代码

当in集合中有三个元素的时候就不使用索引了，因为表中的数量量太少所以使用索引的代价全表扫描还要大，所以直接放弃使用索引。此时我们向表中插入数据：

insert into tuser(id,name,age,sex,address) values (1,'zhangsan',20,'1','北京');

insert into tuser(id,name,age,sex,address) values (2,'lisi',16,'1','上海');

insert into tuser(id,name,age,sex,address) values (3,'wangwu',34,'1','杭州');

insert into tuser(id,name,age,sex,address) values (4,'wangxin',26,'2','广州');

insert into tuser(id,name,age,sex,address) values (5,'wudi',18,'2','上海');

复制代码

再次执行相同的执行计划：

本次计划就使用了索引。

此时再向集合中添加一个元素：

EXPLAIN

SELECT * FROM `tuser` where id in (1,2,3,4)

复制代码

可见此次的执行计划又不使用索引了，mysql还是基于成本考虑，在小表的情况下是全表扫描还是使用索引，取决于集合中的数据量。

4、测试4

当表中数据量庞大时，测试数据表中数量：1310720

测试SQL

EXPLAIN

SELECT * FROM `tuser2` where id in (select id from tuser2 where id < (select count(id)*0.6 from tuser2))

复制代码

当in集合中的数据量很庞大是依然会使用索引。