MYSQL因IN的范围太大导致索引失效问题

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MYSQL因IN的范围太大导致索引失效问题

背景

最近发现有个用于统计的门店串码激活数量的SQL特别慢,将其摘出来大致如下

SELECT a.sku_id as skuId,a.store_id as storeId,
count(*) as saleQty
FROM all_imei_info a
where
    a.activated_time >= 1675530000000
    and a.activated_time <= 1675616399999
    and a.store_id in ('1','2',....'23401')
group by a.sku_id,a.store_id

这张表中的activated_time和store_id均有索引,但是先线上explain时却是走的全表扫描。

当初写这个SQL的开发人员,本意是想按天统计当下所有门店的一个销量情况,但是错就错在,他先在外层将所有区域查出来,再放到统计SQL的IN语句里面,这样就会导致索引失效。

在整个系统中有2w多个门店,而这个定时任务就是要每天把所有的门店都跑一下,所以说sotre_id in 就是把所有的storeId都放进来了。而mysql有个阈值,决定了阈值之下使用索引查询,而超过阈值,网上说当in的条件命中的数量超过30%时,索引失效,走全表扫描。

后面放弃使用in的方式,直接改为连表查询,即可正常使用索引,速度快的飞起。

SELECT a.sku_id as skuId,a.store_id as storeId,
count(*) as saleQty
FROM all_imei_info a
where
    a.activated_time >= 1675530000000
    and a.activated_time <= 1675616399999
    and a.store_id in (select store_id from store_table where is_del = 0)
group by a.sku_id,a.store_id

MySQL中IN数据范围不同导致索引使用不同

EXPLAIN:explain 命令获取 select 语句的执行计划,通过 explain我们可以知道以下信息:表的读取顺序,数据读取操作的类型,哪些索引可以使用,哪些索引实际使用了,表之间的引用,每张表有多少行被优化器查询等信息

其中explian结果中的type字段很明显提现是否用到索引。

常见的扫描方式:

 

system:系统表,少量数据,往往不需要进行磁盘 IO
const:常量连接(通常情况下,如果将一个主键放置到where后面作为条件查询,mysql优化器就能把这次查询优化转化为一个常量。)
eq_ref:主键索引 (primary key) 或者非空唯一索引 (unique not null) 等值扫描
ref:非主键非唯一索引等值扫描(查找条件列使用了索引而且不为主键和unique。)
range:范围扫描(有范围的索引扫描,相对于index的全表扫描,他有范围限制,因此要优于index)
index:索引树扫描(另一种形式的全表扫描,只不过他的扫描方式是按照索引的顺序)
ALL:全表扫描 (full table scan)
其中:MySQL索引扫描方式由快到慢依次为:
system > const > eq_ref > ref > range > index > ALL

下面展示查询sql及结果

当IN只有一个主键时:

结果: type:const,走的主键索引。

当IN多个主键时:结果:type:range,此时仍然走了索引,但是效率降低了。

当IN范围继续扩大时:

结果:type:all,没有走索引了,而是全表扫描。

结论:IN肯定会走索引,但是当IN的取值范围较大时会导致索引失效,走全表扫描。

原因是:mysql有个阈值,决定了阈值之下使用索引查询,而超过阈值则退化,优化器选择索引下潜。

MySQL优化器决定使用某个索引执行查询的仅仅是因为:使用该索引时的成本足够低。

 

相关文章:

https://blog.csdn.net/dmedaa/article/details/124245351

 

本篇文章如有帮助到您,请给「翎野君」点个赞,感谢您的支持。


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
199 66
|
18天前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
118 9
|
3月前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
1天前
|
SQL 存储 关系型数据库
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
40 22
 MySQL秘籍之索引与查询优化实战指南
|
3天前
|
存储 关系型数据库 MySQL
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
21 10
|
23天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
61 18
|
15天前
|
存储 Oracle 关系型数据库
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
48 8
|
22天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
22 7
|
21天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
52 5
|
25天前
|
存储 关系型数据库 MySQL
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
105 7