MySQL中 IN 到底走不走索引？

文章目录

前言
数据库表结构
查询sql
EXPLAIN介绍
- EXPLAIN 的输出每列解释
强制走索引
查询时添加条件(复合索引字段)
- 查询小时
- 查询分钟
总结

前言

在 MySQL 中，IN 语句是否能够利用索引取决于多个因素，包括但不限于查询的具体形式、表的统计信息、索引的选择度等。以下通过几个案例来帮助理解 IN 语句与索引使用的关系。

数据库表结构

CREATE TABLE `device_record_gongdi` (`id` varchar(32) NOT NULL COMMENT '主键',`device_code` varchar(32) NOT NULL COMMENT '设备code',`device_type` tinyint(1) NOT NULL COMMENT '设备类型(0:微站设备 1:工地扬尘监测)',`time_type` tinyint(1) DEFAULT NULL COMMENT '时间类型(1:一分钟 2:五分钟 3:一小时)',`cn` varchar(4) DEFAULT NULL COMMENT '时间类型编码： 2011 分钟、2051 5分钟、2061 小时',`aqi` int(11) DEFAULT NULL,`level` varchar(50) DEFAULT NULL COMMENT '等级',`pollutions` varchar(255) DEFAULT NULL COMMENT '首要污染物',`zhzs` double(11,3) DEFAULT NULL COMMENT '综合指数',`pm25_avg` double(6,2) unsigned DEFAULT NULL COMMENT 'PM25 指定时间内平均值',`pm10_avg` double(6,2) unsigned DEFAULT NULL COMMENT 'PM10 指定时间内平均值',`so2_avg` double(6,2) unsigned DEFAULT NULL COMMENT 'SO2 指定时间内平均值',`no2_avg` double(6,2) unsigned DEFAULT NULL COMMENT 'NO2 指定时间内平均值',`co_avg` double(6,2) unsigned DEFAULT NULL COMMENT 'CO 指定时间内平均值',`data_time` datetime DEFAULT NULL COMMENT '数据采集时间',`create_time` datetime DEFAULT NULL COMMENT '创建时间',PRIMARY KEY (`id`),KEY `gd_data_code_type_time` (`device_code`,`time_type`,`data_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='工地数据表';

复合索引(联合索引)：(device_code,time_type,data_time)

查询sql

使用EXPLAIN查看是否走索引

 EXPLAINSELECT *FROM device_record_gongdiWHERE device_code in ('ZR802241106062','ZR802240801012');

在这里插入图片描述
可以看到没有走索引

EXPLAIN介绍

EXPLAIN 关键字在 MySQL 中是一个非常有用的工具，它可以帮助我们了解 MySQL 如何执行SQL语句。通过使用 EXPLAIN，可以获取 MySQL 执行查询的计划，这包括如何连接表、使用哪些索引、是否进行了文件排序或临时表等详细信息。

EXPLAIN 的输出每列解释

id: 表示查询中每个部分的选择标识符。如果查询比较简单，可能只有一个 id。对于复杂查询（如子查询），可能会有多个 id。
select_type: 显示对应行是简单还是复杂 SELECT 类型。例如：
- SIMPLE: 简单查询，不包含子查询或 UNION。
- PRIMARY: 最外层的查询。
- SUBQUERY: 子查询中的第一个 SELECT。
- DERIVED: 派生表（在 FROM 子句中的子查询）。
table: 显示该行引用的表名。
type: 显示连接类型，从最佳到最差依次为：system, const, eq_ref, ref, range, index, 和 ALL。理想情况下，应尽量避免 ALL 类型，因为它表示全表扫描。
possible_keys: 显示 MySQL 在查询过程中可以使用的索引。
key: 实际上被 MySQL 选择用来加速查询的索引。如果没有索引被使用，则值为 NULL。
key_len: 被选中索引使用的字节数，可以通过这个值来判断是否使用了复合索引的部分字段。
ref: 显示与索引比较的列或常量。
rows: 估算出找到所需的记录所需要读取的行数，值越小越好。
Extra: 包含不适合在其他列中显示的额外信息，比如“Using where”, “Using index”, “Using temporary”, “Using filesort”等。

本文主要观察type、key和rows列。

强制走索引

通过使用force index让sql强制走索引

 EXPLAINSELECT *FROM device_record_gongdi force index (gd_data_code_type_time)WHERE device_code in ('ZR802241106062','ZR802240801012');

在这里插入图片描述
可以看到强制走索引后，rows值没有减少，仍然为382258行。
继续向下看

查询时添加条件(复合索引字段)

表中time_type为时间类型，1代表一分钟数据，2代表五分钟数据，3代表一小时数据。
表中创建了复合索引：(device_code,time_type,data_time)

查询小时

 EXPLAINSELECT *FROM device_record_gongdiWHERE device_code in ('ZR802241106062','ZR802240801012')AND time_type = 3;

在这里插入图片描述
可以看到in又走了索引，rows扫描的行数降到了3013

查询分钟

 EXPLAINSELECT *FROM device_record_gongdiWHERE device_code in ('ZR802241106062','ZR802240801012')AND time_type = 1;EXPLAINSELECT *FROM device_record_gongdi force index (gd_data_code_type_time)WHERE device_code in ('ZR802241106062','ZR802240801012')AND time_type = 1;