一次Mysql使用IN大数据量的优化记录
作者：chenjunbiao 发布时间：2024-01-29 07:49:19
mysql版本号是5.7.28，表A有390W条记录，使用InnoDB引擎，其中varchar类型字段mac已建立索引，索引方法为B-tree。B表仅有5000+条记录。
有一条SQL指令是这样写的：
SELECT * FROM A WHERE mac IN("aa:aa:aa:aa:aa:aa","bb:bb:bb:bb:bb:b",...此外省略900+条)
通过查询出来的结果耗时294.428s。没错，将近5分钟。
使用EXPLAIN分析下：
访问类型type是range，且已命中索引，rows行也只有587776，可为什么查询耗时要这么久？
mac的索引方法使用了B-tree，那对比下它与HASH的区别，简单地总结下：B-tree索引可以用于进行 =,>,>=,<,<=和between的计算，而HASH只能进行等值运算，不能进行范围查找。那IN是等值运算，两种索引方法都适用。即然这样，把mac的索引方法修改为HASH，同样的查询耗时为。
既然调整索引方法并不能明显地提升语句的查询性能，那只能从语句本身中进行处理。其实明眼人刚开始一看就知道，SELECT * 是很耗性能的，那我们只查业务上需要的字段，语句调整为：
SELECT id,mileage FROM A WHERE mac IN("aa:aa:aa:aa:aa:aa","bb:bb:bb:bb:bb:b",...此外省略900+条)
耗时并没有明显的提升。
竟然IN的方式这么难优化，是不是可以放弃使用LEFT JOIN呢？语句调整为：
SELECT a.id,a.mileage FROM A a LEFT JOIN B b ON b.mac = a.mac WHERE b.create_time >= '2020-01-01'
耗时超过5分钟，放弃。
我们知道，在条件量少的情况，EXISTS和IN的效果没有显示的差别。但条件多的时候，IN要比EXISTS的效率也高，来试下EXISTS：
SELECT id,mileage FROM A a WHERE EXISTS(SELECT mac FROM B WHERE create_time >= '2020-01-01' AND mac = a.mac)
耗时也是超过5分钟，IN的效率确实要比EXISTS高，放弃。
所以最后的结论是，如果IN后接大数据量的String，要慎重。
在项目中我把mac作为唯一标识建立与id的对应表，在A表使用mac_id代替mac，查询的时候使用IN(1,2,3...)。效率会提高一些。当前使用NoSQL也是一种方式。
来源：https://segmentfault.com/a/1190000025135805
0
投稿猜你喜欢
- 废话不多说，估计只有我这个菜鸟废了2个小时才搞出来，主要是我想了太多方法来实现，最后都因为这因为那的原因失败了间接说明自己对可变与不可变类型
sqlalchemy的常用数据类型数据类型python数据类型说明Integerint整形Stringstr字符串Floatfloat浮点型- 本文实例讲述了JavaScript实现同步于本地时间的动态时间显示方法。分享给大家供大家参考。具体分析如下：动态显示时间的例子非常简单，了解
- 本文实例讲述了mysql聚簇索引的页分裂。分享给大家供大家参考，具体如下：在MySQL中，MyISAM采用的是非聚簇索引的，InnoDB存储
- 1.limit函数的语法和用法（1）常用且简单的语法和用法①语法：limit n 即limit <参数>具体语法：select
- 错误代码错误消息说 明ASP0100Out of memory内存不足（不能分配要求的内存）ASP0101Unexpected
- 简介： 我们在这世上，选择什么就成为什么，人生的丰富多彩，得靠自己成就。你此刻的付出，决定了你未来成为什么样的人，当你改变不了世界
- 这个是捕获键盘事件输入状态的js代码，它可以判断你敲打了键盘的那个键，ctrl、shift，26个字母等等，返回具体键盘值。Javascri
- 阅读：Mootools常用方法扩展（三） 继续Mootools常用方法扩展，这次是Window类上的扩展，也就是全局函数。方法：$param
- 一、将数据写入opengauss前提准备：成功opengauss数据库，并创建用户jack，创建数据库datasets。数据准备：所用数据以
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN&
- 大家好，我是早起。最近在知乎上看到这样一个问题题主表示pandas用起来很乱，事实真的如此吗？本文就将先如何利用pandas来行数据转换/编
- 问题Django 报错 [2019-09-16 16:47:27,981] - Broken pipe from ('127.0.0
- 如图:其中Num是自增长列,Operation是分类标签,count是汇总数据 代码如下:select Num=row_numb
- HTML中使背景图片自适应浏览器大小实例详解解决办法：1、图片不够大，又background属性不能拉伸图片； 2、只能用个div，把其z-
- 素数简介质数又称素数。一个大于1的自然数，除了1和它自身外，不能被其他自然数整除的数叫做质数；否则称为合数。方法1def primeNUM(
- 1、字符串的索引与获取字符串的索引方式与列表的索引方式是一样的。只不过列表是每个元素的自身就有一个索引位置，而字符串是每个字符就有一个索引位
- 目录技术背景加速场景基于Numba的GPU加速总结概要技术背景GPU加速是现代工业各种场景中非常常用的一种技术，这得益于GPU计算的高度并行
- 行高的概念看上去很简单——文字行的高度，其实，行高所涉及到的基础知识，对于今后理解其它属性也很重要。大片密密麻麻的文字往往会让人觉得乏味，因
- 在编程时你一定碰到过时间触发的事件，在VB中有timer控件，而asp中没有，假如你要不停地查询数据库来等待一个返回结果的话，我想你一定知道
