MySQL中in到底走不走索引?

2021年6月16日 1点热度 0条评论 来源: 一个技术渣渣

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中文网

 

    原文作者:一个技术渣渣
    原文地址: https://blog.csdn.net/kevinxxw/article/details/109567275
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。