MySQL中in到底走不走索引？

explain介绍

mysql中explain关键字可以模拟MySQL优化器执行SQL语句，是一个可以很好的分析SQL语句或表结构的性能瓶颈。

explain的使用方法：explain + sql语句，下面我们先来执行下explain语句

EXPLAIN SELECT * FROM `user` WHERE created_time > "2020-03-08";

执行结果如下：

 

可以看到有几个返回参数：id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtererd、Extra。

下面先介绍下这些参数的含义

id // 选择标识符
select_type // 表示查询的类型
table // 输出结果集的表
partitions // 匹配的分区
type // 表示表的连接类型，
possible_keys // 表示查询时，可能使用的索引
key // 表示实际使用的索引
key_len // 索引字段的长度
ref // 列与索引的比较
rows // 扫描出的行数(估算的行数)
filtered // 按表条件过滤的行百分比
Extra // 执行情况的描述和说明

我们把比较重要的参数提取出来进行详细讲解一下：

• type列

表示连接类型，类型有ALL、index、range、 ref、eq_ref、const、system、NULL，这几种类型从左到右，性能越来越高。一般一个好的sql语句至少要达到range级别。all级别应当杜绝

ALL：全表扫描，应当避免该类型
index：索引全局扫描，index与ALL区别为index类型只遍历索引树
range：检索索引一定范围的行
ref：非唯一性索引扫描，返回匹配某个单独值的所有行
eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描
const：表示通过一次索引就找到了结果，常出现于primary key或unique索引
system：system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，是最高的登记

• key列

表示实际使用到的索引，如果为NULL，则没有使用索引

• key_len列

表示使用索引长度

• rows列

表示根据sql情况，预估表的扫描行数

• extra列

表示详细说明，注意该值包含十分重要的信息。一般该列存在下列值，常见的不太友好的值有：Using filesort, Using temporary

Using where // 表示不用读取表中所有信息，仅通过索引就可以获取所需数据，即使用列覆盖索引
Using temporary // 表示需要使用临时表来存储结果集，常见于排序和分组查询，如：group by ; order by
Using filesort // 表示无法利用索引完成的排序
Using join buffer // 表示使用了连接缓存，如果出现了这个值，建议根据查询的具体情况可能需要添加索引来改进能。
Impossible where // 表示where语句会一直false，导致没有符合条件的行（通过收集统计信息不可能存在结果）
Select tables optimized away // 这个值意味着sql优化到不能在优化了
No tables used // Query语句中使用from dual 或不含任何from子句

好了，我们对explain执行计划做了一个基本的介绍，下面我们来看看in到底会不会走索引

构建测试条件

创建表如下：

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(120) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表';

插入数据

INSERT INTO `test`.`test`(`id`, `name`, `age`) VALUES (1, 'xiaoming', 18);

执行explain执行计划

EXPLAIN SELECT * FROM test WHERE name  in ("lisi")

查看结果

 

可以看到in确实走了所以 idx_name，那是不是in永远都会走索引呢？

我们通过存储过程插入10000条数据

DELIMITER //
DROP PROCEDURE IF EXISTS insertTestData;
CREATE PROCEDURE insertTestData () BEGIN
	DECLARE i INT;	
	SET i = 0;
	WHILE i < 10000 DO
	  INSERT INTO test(`name`, `age`) VALUES (CONCAT('xiaoming', CONCAT( i, '' )), 18);
		SET i = i + 1;
	END WHILE;
END //

CALL insertTestData();
DELIMITER ;

此时我们再看下是不是in继续走索引

EXPLAIN SELECT * FROM test WHERE name  in ("lisi","xiaoming1")

发现依旧走索引

 

此时我们再插入2000条"lisi"这样的数据

DELIMITER //
DROP PROCEDURE IF EXISTS insertTestData;
CREATE PROCEDURE insertTestData () BEGIN
	DECLARE i INT;	
	SET i = 0;
	WHILE i < 2000 DO
	  INSERT INTO test(`name`, `age`) VALUES ('lisi', 18);
		SET i = i + 1;
	END WHILE;
END //
CALL insertTestData();
DELIMITER ;

执行依旧in走索引，那是不是意味着in一定走索引呢？

神奇的界限

当我们再继续执行2次插入2000条"lisi"，即数据库有6000条name=“lisi”的数据时，神奇的发现in并不走索引了，如下图

 

结论

in通常是走索引的，当in后面的数据在数据表中超过30%（上面的例子的匹配数据大约6000/16000 = 37.5%）的匹配时，会走全表扫描，即不走索引，因此in走不走索引和后面的数据有关系。

附：

MySQL中explain用法和结果分析（详解）-mysql教程-PHP中文网