一次Mysql使用IN大数据量的优化记录

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也是一种方式。

总结

到此这篇关于一次Mysql使用IN大数据量优化的文章就介绍到这了,更多相关Mysql使用IN大数据量优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL中对于not in和minus使用的优化

    优化前: select count(t.id) from test t where t.status = 1 and t.id not in (select distinct a.app_id from test2 a where a.type = 1 and a.rule_id in (152, 153, 154)) 17:20:57 laojiu>@plan PLAN_TABLE_OUTPUT ----------------------------------------- Plan ha

  • MySql如何使用not in实现优化

    最近项目上用select查询时使用到了not in来排除用不到的主键id一开始使用的sql如下: select s.SORT_ID, s.SORT_NAME, s.SORT_STATUS, s.SORT_LOGO_URL, s.SORT_LOGO_URL_LIGHT from SYS_SORT_PROMOTE s WHERE s.SORT_NAME = '必听经典' AND s.SORT_ID NOT IN ("SORTID001") limit 1; 表中的数据较多时这个sql的执行

  • MySQL之select in 子查询优化的实现

    下面的演示基于MySQL5.7.27版本 一.关于MySQL子查询的优化策略介绍: 子查询优化策略 对于不同类型的子查询,优化器会选择不同的策略. 1. 对于 IN.=ANY 子查询,优化器有如下策略选择: semijoin Materialization exists 2. 对于 NOT IN.<>ALL 子查询,优化器有如下策略选择: Materialization exists 3. 对于 derived 派生表,优化器有如下策略选择: derived_merge,将派生表合并到外部查询

  • MySQL中or、in、union与索引优化详析

    本文缘起自<一分钟了解索引技巧>的作业题. 假设订单业务表结构为: order(oid, date, uid, status, money, time, -) 其中: oid,订单ID,主键 date,下单日期,有普通索引,管理后台经常按照date查询 uid,用户ID,有普通索引,用户查询自己订单 status,订单状态,有普通索引,管理后台经常按照status查询 money/time,订单金额/时间,被查询字段,无索引 - 假设订单有三种状态:0已下单,1已支付,2已完成 业务需求,查询

  • MYSQL IN 与 EXISTS 的优化示例介绍

    优化原则:小表驱动大表,即小的数据集驱动大的数据集. ############# 原理 (RBO) ##################### select * from A where id in (select id from B) 等价于: for select id from B for select * from A where A.id = B.id 当B表的数据集必须小于A表的数据集时,用in优于exists. select * from A where exists (selec

  • 一次Mysql使用IN大数据量的优化记录

    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

  • Mysql大数据量查询优化思路详析

    Mysql大数据量查询优化思路详析

    目录 1. 千万级别日志查询的优化 2. 几百万黑名单库的查询优化 3. Mybatis批量插入处理问题 项目场景: Mysql大表查询优化,理论上千万级别以下的数据量Mysql单表查询性能处理都是可以的. 问题描述: 在我们线上环境中,出现了mysql几千万级别的日志查询.几百万级别的黑名单库查询分页查询及条件查询都慢的问题,针对Mysql表优化做了一些优化处理. 原因分析:首先说一下日志查询,在Mysql中如果索引加的比较合适,走索引情况下千万级别查询不会超过一秒,Mysql查询的速度和检索

  • 详解Mysql数据库平滑扩容解决高并发和大数据量问题

    详解Mysql数据库平滑扩容解决高并发和大数据量问题

    目录 1 停机方案 2 停写方案 3 平滑扩容之双写方案(中小型数据) 4 平滑扩容之2N方案大数据量问题解决 4.1 扩容问题 4.2 解决方案 4.3 双主架构思想 4.4 环境部署 5 数据库秒级平滑2N扩容实践 5.1 新增数据库VIP 5.2 应用服务增加动态数据源 5.3 解除原双主同步 5.4 安装MariaDB扩容服务器 5.5 增加KeepAlived服务实现高可用 5.6 清理数据并验证 1 停机方案 发布公告 停止服务 离线数据迁移(拆分,重新分配数据) 数据校验 更改配置

  • 30个mysql千万级大数据SQL查询优化技巧详解

    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用

  • 分布式难题ElasticSearch解决大数据量检索面试

    分布式难题ElasticSearch解决大数据量检索面试

    目录 引言 1.面试官: 我看你简历有写项目里有使用了ES,哪些场景用到了ES? 2.面试官: 那使用了ES后结果如何? 3.面试官: 关于ES的一些概念名字你了解多少?如索引,文档,倒排索引这些东西你是怎么理解的? 最重要的倒排索引 总结 关于ES的特性是使用场景概括: 引言 如果你的项目里有超过千万上亿级别的数据,且数据日增量较大需要高性能检索时,如订单数据,你该怎么办? 作为面试官,你需要找一个能解决这个问题的人!为应聘者,你该如何回答面试官这个问题? 你可以了解下使用搜索引擎框架,Ela

  • 大数据量时提高分页的效率

    大数据量时提高分页的效率

    如我们在之前的教程里讨论的那样,分页可以通过两种方法来实现: 默认分页– 你仅仅只用选中data Web control的 智能标签的Enable Paging ; 然而,当你浏览页面的时候,虽然你看到的只是一小部分数据,ObjectDataSource 还是会每次都读取所有数据 自定义分页– 通过只从数据库读取用户需要浏览的那部分数据,提高了性能. 显然这种方法需要你做更多的工作. 默认的分页功能非常吸引人,因为你只需要选中一个checkbox就可以完成了.但是它每次都读取所有的数据,这种方式

  • php 大数据量及海量数据处理算法总结

    下面的方法是我对海量数据的处理方法进行了一个一般性的总结,当然这些方法可能并不能完全覆盖所有的问题,但是这样的一些方法也基本可以处理绝大多数遇到的问题.下面的一些问题基本直接来源于公司的面试笔试题目,方法不一定最优,如果你有更好的处理方法,欢迎与我讨论. 1.Bloom filter 适用范围:可以用来实现数据字典,进行数据的判重,或者集合求交集 基本原理及要点: 对于原理来说很简单,位数组+k个独立hash函数.将hash函数对应的值的位数组置1,查找时如果发现所有hash函数对应位都是1说明

  • 大数据量高并发的数据库优化详解

    如果不能设计一个合理的数据库模型,不仅会增加客户端和服务器段程序的编程和维护的难度,而且将会影响系统实际运行的性能.所以,在一个系统开始实施之前,完备的数据库模型的设计是必须的. 一.数据库结构的设计 在一个系统分析.设计阶段,因为数据量较小,负荷较低.我们往往只注意到功能的实现,而很难注意到性能的薄弱之处,等到系统投入实际运行一段时间后,才发现系统的性能在降低,这时再来考虑提高系统性能则要花费更多的人力物力,而整个系统也不可避免的形成了一个打补丁工程. 所以在考虑整个系统的流程的时候,我们必须

  • 大数据量，海量数据处理方法总结

    下面的方法是我对海量数据的处理方法进行了一个一般性的总结,当然这些方法可能并不能完全覆盖所有的问题,但是这样的一些方法也基本可以处理绝大多数遇到的问题.下面的一些问题基本直接来源于公司的面试笔试题目,方法不一定最优,如果你有更好的处理方法,欢迎与我讨论. 1.Bloom filter 适用范围:可以用来实现数据字典,进行数据的判重,或者集合求交集 基本原理及要点: 对于原理来说很简单,位数组+k个独立hash函数.将hash函数对应的值的位数组置1,查找时如果发现所有hash函数对应位都是1说明

  • 针对Sqlserver大数据量插入速度慢或丢失数据的解决方法

    我的设备上每秒将2000条数据插入数据库,2个设备总共4000条,当在程序里面直接用insert语句插入时,两个设备同时插入大概总共能插入约2800条左右,数据丢失约1200条左右,测试了很多方法,整理出了两种效果比较明显的解决办法: 方法一:使用Sql Server函数: 1.将数据组合成字串,使用函数将数据插入内存表,后将内存表数据复制到要插入的表. 2.组合成的字符换格式:'111|222|333|456,7894,7458|0|1|2014-01-01 12:15:16;1111|222

随机推荐