Mysql深度分页优化的一个实践

这篇具有很好参考价值的文章主要介绍了Mysql深度分页优化的一个实践。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

问题简述:

最近在工作中遇到了大数据量的查询场景, 日产100w左右明细, 会查询近90天内的数据, 总数据量约1亿, 业务要求支持分页查询与导出.

无论是分页或导出都涉及到深度分页查询, mysql通过limit/offset实现的深度分页查询会存在全表扫描的问题, 比如offset=1000w, limit=10, 那么mysql会依次加载1000w条数据进行查找, 然后扔掉前1000w条, 然后返回找到的第1000w后的10条, 这种显然是傻瓜式的实现,显然会对内存和IO带来大量的消耗, 可想而知其耗时肯定会随着数据量的加大而上涨, 给个示例: 来源于[1]

Mysql深度分页优化的一个实践,mysql,数据库

问题原因是mysql针对这种深度分页的近似全表扫描的操作导致: 参考[2]:

Mysql深度分页优化的一个实践,mysql,数据库

那么如何优化呢? 确保深度分页时耗时稳定, 与页码无关, 与数据量规模无关:

  1. 前端加一些限制: 限定不能任意跳转, 只能进行上一页与下一页的翻页操作, 业务能接受这种逻辑
  2. 后端针对翻页查询操作, 会记录上一次的id最大值与最小值, 在查询时通过标签过滤的方式过滤掉已翻页的数据, offset始终值为0
  3. 针对导出逻辑, 则很简单, 都无需考虑上一页, 一直下一页翻页处理即可. 

这种处理也叫做标签记录法, 就是利用索引过滤来规避掉全表扫描问题的一种优化方式, 另外一种是子查询, 类似的操作, 只是即无需记录上次查询的结果, 每次查询时都重新查询下指定偏移量的最后一条记录, 将其ID作为过滤项或边界值, 然后进行查询, 本质是标签记录法, 换汤不换药

借用下[2]中的子查询的例子: 

select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1) LIMIT 10;

 我觉得还是标签记录法性能时最好的,  一次查询解决. 就是要想办法维护下边界值, 尽量避免随机跳转. 

另外近似深度分页的概念: 内存分页, 内存分页也是一种全表扫描的操作, 但是在应用层的过滤处理逻辑, 先获取全部数据, 然后在应用程序中对数据做处理, 数据量较小且sql逻辑复杂时会采取这种方式, sql中不建议加入太多代码逻辑, 调试与维护困难不说, 容易造成慢sql查询. 

参考: 

[1]:MySQL分页查询优化

[2]: MySQL深度分页-CSDN博客文章来源地址https://www.toymoban.com/news/detail-802870.html

到了这里,关于Mysql深度分页优化的一个实践的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 面试官:Mysql千万级大表如何进行深度分页优化?

    假如有一张千万级的订单表,这张表没有采用分区分表,也没有使用ES等技术,分页查询进行到一定深度分页之后(比如1000万行后)查询比较缓慢,我们该如何进行优化? 订单表结构如下: 其中 Mysql 版本为8.0。我们使用Python脚本向表中插入2000万条数据。 导出数据时我们需

    2024年02月19日
    浏览(46)
  • MySQL大数据量分页查询方法及其优化

    ---方法1: 直接使用数据库提供的SQL语句 ---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N ---适应场景: 适用于数据量较少的情况(元组百/千级) ---原因/缺点: 全表扫描,速度会很慢 且 有的数据库 结果集返回不稳定 (如某次返回1,2,3,另外的一次返回2,1,3). Limit限制的是从

    2024年02月15日
    浏览(36)
  • MySQL分页查询详解:优化大数据集的LIMIT和OFFSET

    最近在工作中,我们遇到了一个需求,甲方要求直接从数据库导出一个业务模块中所有使用中的工单信息。为了实现这一目标,我编写了一条SQL查询语句,并请求DBA协助导出数据。尽管工单数量并不多,只有3000多条,但每个工单都包含了大量的信息。DBA进行了多次导出操作,

    2024年02月10日
    浏览(52)
  • python在Django中,如何使用MySQL数据库进行分页怎么实现

    在Django中,使用MySQL数据库进行分页是一个相对简单的任务。Django的ORM提供了内置的分页功能,你只需要使用Paginator类即可。以下是一个基本的示例,展示了如何在Django中使用MySQL进行分页: 首先,确保你已经安装了Django和MySQL,并且已经配置了Django项目以使用MySQL数据库。

    2024年04月29日
    浏览(47)
  • SQL Server、MySQL和Oracle数据库分页查询的区别与联系

    摘要:本文将通过一个现实例子,详细解释SQL Server、MySQL和Oracle这三种常见关系型数据库在分页查询方面的区别与联系。我们将提供具体场景下的SQL语句示例,并解释每个数据库的分页查询用法以及优化方法,帮助读者更好地选择适合自己需求的分页查询方式。 假设我们有一

    2024年02月11日
    浏览(55)
  • 实用数据库开发实践MySQL——数据模型

    目录 第1关 关系模型 关系型数据模型 关系模型基本术语 关系模型的数据操纵与完整性约束 数据操纵 完整性约束 关系模型优缺点 优点 缺点 实验 头歌实验代码 第2关 层次模型 层次型数据模型 层次模型的数据操纵与完整性约束 数据操纵 完整性约束 层次模型优缺点 优点 缺

    2024年02月07日
    浏览(56)
  • mysql数据库优化

    数据库的性能调优和优化是指通过优化数据库结构、SQL语句的编写以及服务器硬件和操作系统等方面的配置,来提高数据库的响应速度和稳定性,以满足业务需求。 结合实际需求,从以下四个方面进行讲解。 一、数据库设计优化 二、SQL语句优化 三、索引优化 四、硬件和操

    2024年01月17日
    浏览(38)
  • 【性能优化】MySql数据库查询优化方案

    了解系统运行效率提升的整体解决思路和方向 学会MySQl中进行数据库查询优化的步骤 学会看慢查询、执行计划、进行性能分析、调优 ​关于这个问题,我们通常首先考虑的是硬件升级,毕竟服务器的内存、CPU、磁盘IO速度 、网络速度等都是制约我们系统快慢的首要因素。硬

    2024年02月03日
    浏览(59)
  • MySQL数据库——MySQL优化服务器,提高MySQL的运行速度!

    MySQL是一种广泛使用的关系型数据库管理系统,优化MySQL服务器可以显著提高数据库的性能和运行速度。在下面的回答中,我将介绍一些常见的MySQL优化策略和技术,以帮助提高MySQL服务器的性能。 1、优化数据库设计: 正确设计数据库结构,使用适当的数据类型、索引和约束

    2024年02月07日
    浏览(69)
  • 云数据库MySQL多人协同开发实践

    本文分享自天翼云开发者社区《云数据库MySQL多人协同开发实践》,作者:不知不觉 随着云计算技术的快速发展,云数据库作为云计算的重要组成部分,为企业提供了高效、灵活和可靠的数据存储和管理服务。其中,MySQL作为一款流行的开源关系型数据库,在云数据库领域具

    2024年02月04日
    浏览(53)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包