文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2024-04-02 19:55

关注

项目场景:

Mysql大表查询优化,理论上千万级别以下的数据量Mysql单表查询性能处理都是可以的。

问题描述:

在我们线上环境中,出现了mysql几千万级别的日志查询、几百万级别的黑名单库查询分页查询及条件查询都慢的问题,针对Mysql表优化做了一些优化处理。

原因分析:
首先说一下日志查询,在Mysql中如果索引加的比较合适,走索引情况下千万级别查询不会超过一秒,Mysql查询的速度和检索的数据条数有关。在Mybatis中,分页查询是先执行Count记录总数,再执行limit a,b 的方式来进行的,而Mysql的Count计数方式是将所有的数据过滤一遍进行累加,因此当日志表数据过千万时,统计一次就是十几秒钟的时间(这里是服务器环境,本地情况下甚至是几分钟)。
limit a,b的方式也一样,Mysql查询时会先一条一条数到第a条,然后向后再数b条作为查询结果,因此当起始行数越来越大时查询同样会变得很慢,也就是当你点第一页时可能一下就查出来了,当你点最后一页的时候可能几十秒才能查出来。
黑名单库查询优化同理,也是需要通过条件优化。
在进行大批量数据落库时,使用的Mybatis批量插入,发现当批次数据超过3000时速度会急剧变慢,这是一个Mybatis娘胎里自带的问题,也需要进行解决。

解决方案:

这里只简单说明优化的几个方向。

1. 千万级别日志查询的优化

  1. 首先说下日志查询,重点是优化无条件是分页查询,在无条件时,不使用MyBatis的分页插件,而是自己手写一个分页查询,由于MySql的count耗时过长,我们先优化他。
  2. 优化Count:日志表的数据只增,不会出现中间某条删除,所以他的数据可以理解成是连续的,我们可以在内存中直接进行计数,记录count总数,或者给表添加一个自增的ID字段,直接select max(id)就是总数量,这样count查询的效率会提升到毫秒级别。
  3. 自定义分页查询:分页查询中使用优化后的count记录总数,然后使用(page - 1)* pageSize + 1公式计算出当前页的最小ID,然后将limit a,b 的Sql语句改为where ID > 最小ID limit b的方式,这样查询就会走索引先将小于最小ID的数据过滤掉,再进行查询,经过第二步和第三步的优化后分页查询效率缩短到了一秒内,并且不会随着页数的增长而变慢。
  4. 条件查询:条件查询只能设置合适的索引,另外慎用like '%条件%‘的方式进行匹配查询,这样会导致索引失效全局检索,模糊查询尽量使用like '条件%' 的方式进行最左匹配,也可以使用explain+sql语句 的方式来查看sql语句的执行效率,是否走了所有啥的来针对性的优化,加好合适的索引、优化查询语句后通常一千万以内的数据查询效率会在3秒内。

粘出自定义分页查询结果封装:

 // 手动count
                Integer total = logPushService.queryBackCount(resMap);//查询数量
                // 手动查询结果
                List<InterceptInfo> ls = logPushService.queryBackByPage(resMap);
                PageInfo pageInfo = new PageInfo();
                pageInfo.setTotal(total);
                pageInfo.setPageSize(limit);
                pageInfo.setList(ls);
                pageInfo.setPageNum(pn);

2. 几百万黑名单库的查询优化

 <select id="query_COUNT" parameterType="java.util.Map" resultType="java.lang.Long">
        SELECT
        count(0)
        from nms_intercept_info${map.week}
        where 1=1
        <if test="map.id!=null and map.id!='' ">
            AND id>#{map.id}
        </if>
        <if test="map.url!=null and map.url!='' ">
            AND spliturl=#{map.url}
        </if>
        <if test="map.startTime!=null and map.startTime!='' ">
            AND time <![CDATA[>=]]> #{map.startTime}
        </if>
        <if test="map.endTime!=null and map.endTime!='' ">
            AND time <![CDATA[<=]]> #{map.endTime}
        </if>
        <if test="map.type!=null and map.type!='' ">
            AND bigType = #{map.type}
        </if>
    </select>

3. Mybatis批量插入处理问题

Mybatis批量插入语句中的类集合大小不能超过五千,三千是最佳,这是测试出来的结果,考虑到的原因是Mybatis会将类做反射,这个太影响效率,因此批量插入时要注意这个,如果你能够三千三千的批量处理就限制一下,不要让每批数据超过3000,数据量过大时也可以使用异步非阻塞的方式来插入。
异步非阻塞代码(只是步骤样例,存在代码缺失):

    // 执行全量HMD导入任务的线程池
    public final static ExecutorService importHasPool = Executors.newFixedThreadPool(10);
    public final static CompletionService<DoExcelResult> importHasPoolService = new ExecutorCompletionService<>(importHasPool);
    
   public synchronized DoExcelResult example() {
            // 开始执行导入
            // 写到这里面方法最后会自动关闭
            long startTime = System.currentTimeMillis();
            // 定义一个集合,记录Callable的执行结果,Callable是带返回值的Runable
            List<Future<DoExcelResult>> futures = new ArrayList<>();
            while ((str = reader.readLine()) != null) {
                if (list.size() > 5000) { // 5000插入一次
                    List<String> list1 = CollectionUtil.copyDepth(list);
                    list.clear();
                    // BlackInfoHasImportlCallable是实现了Callable接口的实现类,Callable是带返回值的Runable
                    Future submit = SysThreadPoolCenter.importHasPoolService.submit(new BlackInfoHasImportlCallable(list1, blacklistInfoMapper));
                    futures.add(submit);
                }
            }
            
            // 等待执行结果
            for (Future<DoExcelResult> future : futures) {
                try {
                // 2. futrue.get时会获取返回值,线程没执行完毕就等待等待执行结果
                    DoExcelResult doExcelResult = future.get();
                    result.setSuccessNum(result.getSuccessNum() + doExcelResult.getSuccessNum());
                    result.setContinueNum(result.getContinueNum() + doExcelResult.getContinueNum());
                    result.setErrorNum(result.getErrorNum() + doExcelResult.getErrorNum());
                } catch (Exception e) {
                    log.error(e);
                }
            }
            // 循环结束代表所有线程执行完毕
            result.setTimeCon((System.currentTimeMillis() - startTime)/1000.0);
            BlacklistService.isDoing = false;
        } catch (Exception e) {
            BlacklistService.isDoing = false;
            log.error(e);
        }
        BlacklistService.isDoing = false;
        return result;
    }

限制每批3000条:

 if (ls.size() >= 3000) {
            //每次保存3000
            double sum = Math.ceil(ls.size() / 3000f);
            for (int i = 0; i < sum; i++) {
                total += blacklistDao.saveBatch(ls.subList(i * 3000, ((i + 1) * 3000) > ls.size() ? ls.size() : (i + 1) * 3000));
            }
        } else {
            total = blacklistDao.saveBatch(ls);
        }

如果你数据库用的不是mysql,而是CK或者其他的大数据处理数据库,批量插入可能要求每秒几万条几十万条,这时就不再适合使用Myabtis框架了,建议使用JDBC连接的方式,自己写代码拼接sql语句,再使用jdbc连接执行(使用线程池),效率上会快很多。

到此这篇关于Mysql大数据量查询优化思路详析的文章就介绍到这了,更多相关Mysql大数据量查询优化内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯