线上千万级大表排序该如何优化?

2023-11-04 22:20

本文主要是介绍线上千万级大表排序该如何优化?,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

前言

  前段时间应急群有客服反馈,会员管理功能无法按到店时间、到店次数、消费金额 进行排序。经过排查发现是Sql执行效率低,并且索引效率低下。遇到这样的情况我们该如何处理呢?今天我们聊一聊Mysql大表查询优化。

应急问题

  商户反馈会员管理功能无法按到店时间、到店次数、消费金额 进行排序,一直转圈圈或转完无变化,商户要以此数据来做活动,比较着急,请尽快处理,谢谢。

线上数据量

merchant_member_info 7000W条数据。
member_info 3000W。

不要问我为什么不分表,改动太大,无能为力。

问题SQL如下

SELECTmui.id,mui.merchant_id,mui.member_id,DATE_FORMAT(mui.recently_consume_time,'%Y%m%d%H%i%s') recently_consume_time,IFNULL(mui.total_consume_num, 0) total_consume_num,IFNULL(mui.total_consume_amount, 0) total_consume_amount,(CASEWHEN u.nick_name IS NULL THEN'会员'WHEN u.nick_name = '' THEN'会员'ELSEu.nick_nameEND) AS 'nickname',u.sex,u.head_image_url,u.province,u.city,u.country
FROMmerchant_member_info mui
LEFT JOIN member_info u ON mui.member_id = u.id
WHERE1 = 1
AND mui.merchant_id = '商户编号'
ORDER BYmui.recently_consume_time DESC / ASC
LIMIT 0,10

出现的原因

  经过验证可以按照“到店时间”进行降序排序,但是无法按照升序进行排序主要是查询太慢了。主要原因是:虽然该查询使用建立了recently_consume_time索引,但是索引效率低下,需要查询整个索引树,导致查询时间过长。

DESC 查询大概需要4s,ASC 查询太慢耗时未知。

为什么降序排序快和而升序慢呢?

  因为是对时间建立了索引,最近的时间一定在最后面,升序查询,需要查询更多的数据,才能过滤出相应的结果,所以慢。

解决方案

目前生产库的索引

调整索引

  需要删除index_merchant_user_last_time索引,同时将index_merchant_user_merchant_ids单例索引,变为 merchant_id,recently_consume_time组合索引。

调整结果(准生产)

调整前后结果对比(准生产)

 测试数据

merchant_member_info 有902606条记录。
member_info 表有775条记录。

SQL执行效率

优化前

优化后

type由index -> ref

ref由 null -> const

TOP优化前优化后
到店时间-降序0.274s0.003s
到店时间-升序11.245s0.003s

调整索引需要执行的SQL

执行的注意事项:
由于表中的数据量太大,请在晚上进行执行,并且需要分开执行。 # 删除近期消费时间索引
ALTER TABLE merchant_member_info DROP INDEX index_merchant_user_last_time;# 删除商户编号索引
ALTER TABLE merchant_member_info DROP INDEX index_merchant_user_merchant_ids;# 建立商户编号和近期消费时间组合索引
ALTER TABLE merchant_member_info ADD INDEX idx_merchant_id_recently_time (`merchant_id`,`recently_consume_time`);

经询问,重建索引花了30分钟。

最终的分页查询优化

  上面的sql虽然经过调整索引,虽然能达到较高的执行效率,但是随着分页数据的不断增加,性能会急剧下降。

分页数据查询时间优化后
limit 0,100.003s0.002s
limit 10,100.005s0.002s
limit 100,100.009s0.002s
limit 1000,100.044s0.004s
limit 9000,100.247s0.016s
最终的sql

优化思路:先走覆盖索引定位到,需要的数据行的主键值,然后INNER JOIN 回原表,取到其他数据。

SELECTmui.id,mui.merchant_id,mui.member_id,DATE_FORMAT(mui.recently_consume_time,'%Y%m%d%H%i%s') recently_consume_time,IFNULL(mui.total_consume_num, 0) total_consume_num,IFNULL(mui.total_consume_amount, 0) total_consume_amount,(CASEWHEN u.nick_name IS NULL THEN'会员'WHEN u.nick_name = '' THEN'会员'ELSEu.nick_nameEND) AS 'nickname',u.sex,u.head_image_url,u.province,u.city,u.country
FROMmerchant_member_info mui
INNER JOIN (SELECTidFROMmerchant_member_infoWHEREmerchant_id = '商户ID'ORDER BYrecently_consume_time DESCLIMIT 9000,10
) AS tmp ON tmp.id = mui.id
LEFT JOIN member_info u ON mui.member_id = u.id

结尾

  如果觉得对你有帮助,可以多多评论,多多点赞哦,也可以到我的主页看看,说不定有你喜欢的文章,也可以随手点个关注哦,谢谢。

  我是不一样的科技宅,每天进步一点点,体验不一样的生活。我们下期见!

这篇关于线上千万级大表排序该如何优化?的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/345558

相关文章

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

Python通过模块化开发优化代码的技巧分享

《Python通过模块化开发优化代码的技巧分享》模块化开发就是把代码拆成一个个“零件”,该封装封装,该拆分拆分,下面小编就来和大家简单聊聊python如何用模块化开发进行代码优化吧... 目录什么是模块化开发如何拆分代码改进版:拆分成模块让模块更强大:使用 __init__.py你一定会遇到的问题模www.

Mybatis 传参与排序模糊查询功能实现

《Mybatis传参与排序模糊查询功能实现》:本文主要介绍Mybatis传参与排序模糊查询功能实现,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友跟随小编一起看看吧... 目录一、#{ }和${ }传参的区别二、排序三、like查询四、数据库连接池五、mysql 开发企业规范一、#{ }和${ }传参的

SpringBoot首笔交易慢问题排查与优化方案

《SpringBoot首笔交易慢问题排查与优化方案》在我们的微服务项目中,遇到这样的问题:应用启动后,第一笔交易响应耗时高达4、5秒,而后续请求均能在毫秒级完成,这不仅触发监控告警,也极大影响了用户体... 目录问题背景排查步骤1. 日志分析2. 性能工具定位优化方案:提前预热各种资源1. Flowable

SpringBoot3实现Gzip压缩优化的技术指南

《SpringBoot3实现Gzip压缩优化的技术指南》随着Web应用的用户量和数据量增加,网络带宽和页面加载速度逐渐成为瓶颈,为了减少数据传输量,提高用户体验,我们可以使用Gzip压缩HTTP响应,... 目录1、简述2、配置2.1 添加依赖2.2 配置 Gzip 压缩3、服务端应用4、前端应用4.1 N

Spring Boot + MyBatis Plus 高效开发实战从入门到进阶优化(推荐)

《SpringBoot+MyBatisPlus高效开发实战从入门到进阶优化(推荐)》本文将详细介绍SpringBoot+MyBatisPlus的完整开发流程,并深入剖析分页查询、批量操作、动... 目录Spring Boot + MyBATis Plus 高效开发实战:从入门到进阶优化1. MyBatis

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Python如何使用__slots__实现节省内存和性能优化

《Python如何使用__slots__实现节省内存和性能优化》你有想过,一个小小的__slots__能让你的Python类内存消耗直接减半吗,没错,今天咱们要聊的就是这个让人眼前一亮的技巧,感兴趣的... 目录背景:内存吃得满满的类__slots__:你的内存管理小助手举个大概的例子:看看效果如何?1.

一文详解SpringBoot响应压缩功能的配置与优化

《一文详解SpringBoot响应压缩功能的配置与优化》SpringBoot的响应压缩功能基于智能协商机制,需同时满足很多条件,本文主要为大家详细介绍了SpringBoot响应压缩功能的配置与优化,需... 目录一、核心工作机制1.1 自动协商触发条件1.2 压缩处理流程二、配置方案详解2.1 基础YAML

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分