专栏首页方丈的寺院SQL IN 一定走索引吗?

SQL IN 一定走索引吗?

摘要

IN 一定走索引吗?那当然了,不走索引还能全部扫描吗?好像之前有看到过什么Exist,IN走不走索引的讨论。但是好像看的太久了,又忘记了。哈哈,如果你也忘记了MySQL中IN是如何查询的,就来复习下吧。

问题

问题要从之前的统计 店铺数关注人数说起

SELECT shop_id, count(user_Id) as attentionNumber
FROM shop_attention
WHERE shop_id IN
<foreach collection="shopIds" item="shopId" separator="," open="(" close=")">
    #{shopId}
</foreach>
GROUP BY shopId

当时是从缓存的角度来分析如何进行优化。有兴趣看这篇微服务化后缓存怎么做

将这个查询收敛,应用端做了缓存后,确实没什么大问题了。但是随着店铺关注数的增加,慢SQL开始出现了

在我们的业务中,将100ms的SQL查询定义为慢查询,需要优化的。优化不了必须要控制查询频次。同时超过5s的数据库操作会被kill掉,防止拖垮整个数据库,导致相关应用都受到牵连。

该SQL执行时间耗时已经几百ms了,必须要优化了。阿里云对这个SQL的检测报告时

  1. 扫描行数和返回行数比例超过了100
  2. 使用了groupby函数,注意检查groupby是否用到了索引

分析

首先可以确定的是,group by 的 shop_id字段肯定是建了索引的,那么扫描行数和返回行数比例为什么这么大呢?

先复习下分析查询语句的三大要素

  1. 响应时间,意思很明确,不多解释了
  2. 扫描行数 整个查询过程中扫描了多少行
  3. 返回行数 查询结果命中的行数 一般来说扫描行数和返回行数一样,是最好的,但是这是理想情况,事实并非如此。关联查询/范围排序查询时都会使得扫描行数大于返回行数。一般这个比例要控制在10以下,否则可能会有性能问题。

题外话,我一直觉得mysql explain的展示字段不如mongo的直观。mongo索引原理同mysql一样,有兴趣的可以看下Mongo Index分析

那么现在问题来了,为什么这个查询扫描行数/返回行数比例这么大呢。

那么就explain 一下了

实验1

SELECT shop_id, count(user_Id) as attentionNumber
FROM shop_attention
WHERE shop_id IN(1,2,3)
GROUP BY shopId

结果

type

possible_keys

key

key_length

ref

rows

Extras

range

idx_shop

idx_shop

8

null

16000

Using index condition

和我预想的一样,类型是 range走了shopId的索引,没毛病。那怎么扫描行数/返回行数比例这么大的。

实验2

再试一把,将IN的范围增大了。

SELECT shop_id, count(user_Id) as attentionNumber
FROM shop_attention
WHERE shop_id IN(1,2,3,4,5,6,7,8,9)
GROUP BY shopId

结果

type

possible_keys

key

key_length

ref

rows

Extras

index

idx_shop

idx_shop

8

null

303000

Using where

结果不一样了,类型是 index,也就是没有走范围扫描,而是走的是索引扫描。

实验3

强制走索引

SELECT shop_id, count(user_Id) as attentionNumber
FROM shop_attention force index(idx_shop)
WHERE shop_id IN(1,2,3,4,5,6,7,8,9)
GROUP BY shopId

结果

type

possible_keys

key

key_length

ref

rows

Extras

range

idx_shop

idx_shop

8

null

29000

Using Index Condition

这时候走的是范围扫描,而不是索引扫描。但是你会发现这次的执行时间并不没有比·上一次的执行时间短

mysql对这个查询进行了优化,使其不走范围扫描。而是走的是索引扫描。那么必然会随着IN的条件越来越多, 扫描的行数越多,执行的时间越长。

所以这个问题的优化的办法呢,就是在应用端做切割,分批去查。每次查N个,保证每次的查询都很快。

总结

根据实际的情况,需要控制IN查询的范围。原因有以下几点

  1. IN 的条件过多,会导致索引失效,走索引扫描
  2. IN 的条件过多,返回的数据会很多,可能会导致应用堆内内存溢出。

所以必须要控制好IN的查询个数

关注公众号【方丈的寺院】,第一时间收到文章的更新,与方丈一起开始技术修行之路

本文分享自微信公众号 - 方丈的寺院(gh_c98f244e174d),作者:cnstonefang

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-06-16

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 如何避免写出烂的业务代码(2)-领域对象与领域服务

    为什么把这么小的点拿出来讲,最开始在讨论中领域对象与领域服务时,觉得行为放在service/entity中区别不大,只是一个放置位置的问题,并不影响到代码的抽象...

    方丈的寺院
  • 数据迁移(2) - 如何快速迁移

    在上一篇中我们介绍了数据迁移的套路,但是没有介绍具体的方案,这篇着重介绍下具体的数据迁移方案

    方丈的寺院
  • 如何避免写出烂的业务代码(2)- DDD整改

    何避免写出烂的业务代码(1)一文中介绍过如何避免写出烂的业务代码,这边谈一谈领域驱动模型的实践

    方丈的寺院
  • ASP.NET MVC的Action Filter

    一年前写了一篇短文ASP.NET MVC Action Filters,整理了Action Filter方面的资源,本篇文章详细的描述Action Filter...

    张善友
  • Bluehost 美国站和 Bluehost 中国站的关系

    魏艾斯博客www.vpsss.net
  • Bluehost 美国站和 Bluehost 中国站的区别

    魏艾斯博客www.vpsss.net
  • vue.js中滚动条加载更多数据

    本文章参考:http://www.cnblogs.com/ssrsblogs/p/6108423.html

    lin_zone
  • 一个Mini的ASP.NET Core框架的实现

      在2019年1月的微软技术(苏州)俱乐部成立大会上,蒋金楠老师(大内老A)分享了一个名为“ASP.NET Core框架揭秘”的课程,他用不到200行的代码实...

    Edison Zhou
  • 我的领域驱动设计运用实例 - 领域啊领域

    断断续续的也有在闲余时间接触领域驱动设计的相关知识,因为目前在工作中更多的还只是一名 crud boy,因此目前也只是对其中的某些知识点有知晓,实际使用的比较少...

    程序员宇说
  • 美一口气公布3份对华征税商品排除清单,涉及437项商品

    当地时间17日,美国贸易代表办公室(USTR)公布了3份对华征税商品排除清单通知,其中涉及超过400项商品,还包括美方宣布的3批加征关税的商品。

    镁客网

扫码关注云+社区

领取腾讯云代金券