MySQL中IN的取值范围较大时会导致索引失效

这篇具有很好参考价值的文章主要介绍了MySQL中IN的取值范围较大时会导致索引失效。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一:分析MySQL In查询为什么所有不生效

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

navicat可视化工具使用explain函数查看sql执行信息

1.1 场景1:当IN中的取值只有一个主键时MySQL中IN的取值范围较大时会导致索引失效,数据库与mybatis,mysql

我们只需要注意一个最重要的type 的信息很明显的提现是否用到索引:

type结果值从好到坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

all:全表扫描

index:另一种形式的全表扫描,只不过他的扫描方式是按照索引的顺序

range:有范围的索引扫描,相对于index的全表扫描,他有范围限制,因此要优于index

ref: 查找条件列使用了索引而且不为主键和unique。其实,意思就是虽然使用了索引,但该索引列的值并不唯一,有重复。这样即使使用索引快速查找到了第一条数据,仍然不能停止,要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表,因为索引是有序的,即便有重复值,也是在一个非常小的范围内扫描。

const:通常情况下,如果将一个主键放置到where后面作为条件查询,mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化,这个取决于优化器

一般来说,得保证查询至少达到range级别,最好能达到ref,type出现index和all时,表示走的是全表扫描没有走索引,效率低下,这时需要对sql进行调优。

当extra出现Using filesor或Using temproary时,表示无法使用索引,必须尽快做优化。

possible_keys:sql所用到的索引

key:显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL

rows: 显示MySQL认为它执行查询时必须检查的行数。

1.2 场景2:扩大IN中的取值范围MySQL中IN的取值范围较大时会导致索引失效,数据库与mybatis,mysql

 此时仍然走了索引,但是效率降低了

1.3 场景3:继续扩大IN的取值范围

 MySQL中IN的取值范围较大时会导致索引失效,数据库与mybatis,mysql

看上面的图,发现此时已经没有走索引了,而是全表扫描。

在说一下结论

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

By the way:如果使用了 not in,则不走索引。

二:MySQL 需要 IN查询但是很慢怎么办 ?

从上文得知我们的IN查询索引不生效,以及不生效的原因。

2.1 这是一个常用的IN查询文章来源地址https://www.toymoban.com/news/detail-618560.html


SELECT id, order_index, data_order_start, update_time, create_time, gov_frame_id 
FROM gov_price_category_detail 
WHERE 
gov_frame_id IN ( 
	SELECT id FROM gov_price_frame WHERE deleted=1 AND is_spider=0 AND city IN ( '长沙市' ) GROUP BY id 
) 
AND deleted=1 
AND data_order_start < 51 

MySQL中IN的取值范围较大时会导致索引失效,数据库与mybatis,mysql 

2.2 我们把IN查询 改造成 inner 查询


SELECT gcd.id,  gcd.order_index, gcd.data_order_start, gcd.update_time, gcd.create_time, gcd.gov_frame_id 
FROM gov_price_category_detail gcd , ( SELECT  gp.id FROM gov_price_frame gp WHERE  gp.deleted=1 AND  gp.is_spider=0 AND  gp.city IN ( '长沙市' ) GROUP BY  gp.id ) gpf
WHERE 
gpf.id = gcd.gov_frame_id
AND gcd.deleted=1 
AND gcd.data_order_start < 51 

MySQL中IN的取值范围较大时会导致索引失效,数据库与mybatis,mysql 

3.3 优化后速度对比

原始SQL速度信息: 

MySQL中IN的取值范围较大时会导致索引失效,数据库与mybatis,mysql

 优化后SQL速度信息:

MySQL中IN的取值范围较大时会导致索引失效,数据库与mybatis,mysql

 

一:分析MySQL In查询为什么所有不生效

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

navicat可视化工具使用explain函数查看sql执行信息

1.1 场景1:当IN中的取值只有一个主键时MySQL中IN的取值范围较大时会导致索引失效,数据库与mybatis,mysql

我们只需要注意一个最重要的type 的信息很明显的提现是否用到索引:

type结果值从好到坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

all:全表扫描

index:另一种形式的全表扫描,只不过他的扫描方式是按照索引的顺序

range:有范围的索引扫描,相对于index的全表扫描,他有范围限制,因此要优于index

ref: 查找条件列使用了索引而且不为主键和unique。其实,意思就是虽然使用了索引,但该索引列的值并不唯一,有重复。这样即使使用索引快速查找到了第一条数据,仍然不能停止,要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表,因为索引是有序的,即便有重复值,也是在一个非常小的范围内扫描。

const:通常情况下,如果将一个主键放置到where后面作为条件查询,mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化,这个取决于优化器

一般来说,得保证查询至少达到range级别,最好能达到ref,type出现index和all时,表示走的是全表扫描没有走索引,效率低下,这时需要对sql进行调优。

当extra出现Using filesor或Using temproary时,表示无法使用索引,必须尽快做优化。

possible_keys:sql所用到的索引

key:显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL

rows: 显示MySQL认为它执行查询时必须检查的行数。

1.2 场景2:扩大IN中的取值范围MySQL中IN的取值范围较大时会导致索引失效,数据库与mybatis,mysql

 此时仍然走了索引,但是效率降低了

1.3 场景3:继续扩大IN的取值范围

 MySQL中IN的取值范围较大时会导致索引失效,数据库与mybatis,mysql

看上面的图,发现此时已经没有走索引了,而是全表扫描。

在说一下结论

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

By the way:如果使用了 not in,则不走索引。

二:MySQL 需要 IN查询但是很慢怎么办 ?

从上文得知我们的IN查询索引不生效,以及不生效的原因。

2.1 这是一个常用的IN查询

到了这里,关于MySQL中IN的取值范围较大时会导致索引失效的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 解决pandas的concat函数导致索引失效的方法

    最近在写数据的时候看到用一个concat函数进行整合,但是下面这段代码之后就碰上个很奇怪的地方 这段代码首先就是用dfs记录了每一组数据,最后使用concat函数进行连接。在这之后我希望在特定位置插入一列数据 一共有三种类型的文本,10条数据,在df格式下前面也有索引。

    2024年02月11日
    浏览(44)
  • MySQL查询条件OR导致模糊查询失效

    起因:查出来的列表数据,通过查询条件过滤,发现过滤条件并不起作用 检查发现:where查询条件里面有or的过滤条件,用来进行权限控制,模糊查询条件本来是起效果的,但是OR的条件一执行,就有多了些数据 代码: 期望的结果:先查出来所有符合要求的,然后再根据过滤

    2024年01月22日
    浏览(46)
  • mysql 查询优化 、索引失效

    查询优化 物理查询优化 通过索引和表连接方式等技术来进行优化,这里重点需要掌握索引的使用 逻辑查询优化 通过SQL 等价变换 提升查询效率,直白一点就是说,换一种查询写法执行效率可能更高 索引失效 计算、函数、类型转换(自动或手动)导致索引失效 select sql_no_c

    2024年02月10日
    浏览(49)
  • MySQL 索引失效详解

    一、MySQL索引失效原因汇总 隐式的类型转换,索引失效 查询条件包含or,可能导致索引失效 like通配符可能导致索引失效 查询条件不满足联合索引的最左匹配原则 在索引列上使用mysql的内置函数 对索引进行列运算(如,+、-、*、/) 索引字段上使用 (! = 或者 ),索引可能失效 索

    2024年02月02日
    浏览(45)
  • MySQL索引失效的七大场景

    模型数或运算快 这里我们以MySQL自带的world数据库中的country表为例。 向name列添加索引 查看country表索引 测试like完全匹配 可以看到type级别是index。索引失效。 在 MySQL 中,使用 EXPLAIN 语句可以查看查询语句的执行计划,了解 MySQL 如何执行查询。其中,EXPLAIN 语句的结果集中的

    2023年04月22日
    浏览(52)
  • Mysql中索引优化和失效

    要了解索引优化和索引失效的场景就要先了解什么是索引 索引是一种有序的存储结构,按照单个或者多个列的值进行排序,以提升搜索效率。 索引的类型 UNIQUE唯一索引 不可以出现相同的值,可以有NULL值。 INDEX普通索引 允许出现相同的索引内容。 PRIMARY KEY主键索引 不允许出

    2024年02月20日
    浏览(46)
  • Mysql索引失效的场景分析

    前言: 日常使用Mysql做一些业务时,发现很慢,跟踪日志返现是有慢查询语句,于是使用explain查看执行计划发现是没有使用到索引,一般这些情况都不是java框架导致的,一般框架里都会根据主键或者指定的条件去做简单的查询,复杂的查询都是通过sql原生写法来实现的,这

    2023年04月20日
    浏览(44)
  • 第20章:MySQL索引失效案例

    1. 全值匹配我最爱 当SQL查询 创建3个索引 当前优化器会选择跟where条件匹配最高的idx_age_classid_name索引,直接查询出对应的主键值然后回表查询,此时的效率最高。所以部分索引失效,因为使用的部分索引,会查询多个主键值还需要回表继续判断,效率低。 2. 最佳左前缀规则

    2024年02月11日
    浏览(40)
  • 详解MySQL索引失效的几种情况

    MySQL索引是提高查询效率的重要手段。索引失效会导致查询效率下降,甚至全表扫描,影响数据库性能。以下是可能导致MySQL索引失效的情况: 当where语句中使用 or 操作符并且 or 两边的条件涉及到至少两个字段时,MySQL无法使用索引,会转向全表扫描。因此,应尽量避免使用

    2024年02月01日
    浏览(47)
  • MySQL索引3——Explain关键字和索引优化(SQL提示、索引失效、索引使用规则)

    目录 Explain 索引性能分析 Id ——select的查询序列号 Select_type——select查询的类型 Table——表名称 Type——select的连接类型 Possible_key ——显示可能应用在这张表的索引 Key——实际用到的索引 Key_len——实际索引使用到的字节数 Ref    ——索引命中的列或常量 Rows——预

    2024年02月14日
    浏览(57)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包