MySQL 亿级数据分页的优化

亿级数据分页优化:MySQL、Elasticsearch与MongoDB实战 数据库分页是系统开发中的基础功能,其核心原理是通过LIMIT和OFFSET实现数据分段获取。随着数据量增长到亿级规模,传统分页方式会产生严重的性能问题,主要体现在全表扫描导致的IO消耗和内存压力。在MySQL中可以通过延迟关联和主键边界技术优化,Elasticsearch推荐使用Search After替代from/size,MongoDB则建议避免skip()操作。这些优化方案在电商商品列表、日志查询系统等场景中尤为重要,能有效解决深度分页带来的性能瓶颈。实际测试表明,优化后的分页查询性能可提升数十倍,特 阅读详情

点击上方“芋道源码”,选择“设为星标”

管她前浪,还是后浪?

能浪的浪,才是好浪!

每天 8:55 更新文章,每天掉亿点点头发...

源码精品专栏

 

来源:cnblogs.com/wzh2010/p/14316920.html


背景

下班后愉快的坐在在回家的地铁上,心里想着周末的生活怎么安排。

突然电话响了起来,一看是我们的一个开发同学,顿时紧张了起来,本周的版本已经发布过了,这时候打电话一般来说是线上出问题了。

果然,沟通的情况是线上的一个查询数据的接口被疯狂的失去理智般的调用,这个操作直接导致线上的MySql集群被拖慢了。

好吧,这问题算是严重了,下了地铁匆匆赶到家,开电脑,跟同事把Pinpoint上的慢查询日志捞出来。看到一个很奇怪的查询,如下

POST  domain/v1.0/module/method?order=condition&orderType=desc&offset=1800000&limit=500

domain、module 和 method 都是化名,代表接口的域、模块和实例方法名,后面的offset和limit代表分页操作的偏移量和每页的数量,也就是说该同学是在 翻第(1800000/500+1=3601)页。初步捞了一下日志,发现 有8000多次这样调用。

这太神奇了,而且我们页面上的分页单页数量也不是500,而是 25条每页,这个绝对不是人为的在功能页面上进行一页一页的翻页操作,而是数据被刷了(说明下,我们生产环境数据有1亿+)。详细对比日志发现,很多分页的时间是重叠的,对方应该是多线程调用。

通过对鉴权的Token的分析,基本定位了请求是来自一个叫做ApiAutotest的客户端程序在做这个操作,也定位了生成鉴权Token的账号来自一个QA的同学。立马打电话给同学,进行了沟通和处理。

分析

其实对于我们的MySQL查询语句来说,整体效率还是可以的,该有的联表查询优化都有,该简略的查询内容也有,关键条件字段和排序字段该有的索引也都在,问题在于他一页一页的分页去查询,查到越后面的页数,扫描到的数据越多,也就越慢。

我们在查看前几页的时候,发现速度非常快,比如 limit 200,25,瞬间就出来了。但是越往后,速度就越慢,特别是百万条之后,卡到不行,那这个是什么原理呢。先看一下我们翻页翻到后面时,查询的sql是怎样的:

select * from t_name where c_name1='xxx' order by c_name2 limit 2000000,25;

这种查询的慢,其实是因为limit后面的偏移量太大导致的。比如像上面的 limit 2000000,25 ,这个等同于数据库要扫描出 2000025条数据,然后再丢弃前面的 20000000条数据,返回剩下25条数据给用户,这种取法明显不合理。

图片

大家翻看《高性能MySQL》第六章:查询性能优化,对这个问题有过说明:

分页操作通常会使用limit加上偏移量的办法实现,同时再加上合适的order by子句。但这会出现一个常见问题:当偏移量非常大的时候,它会导致MySQL扫描大量不需要的行然后再抛弃掉。

数据模拟

那好,了解了问题的原理,那就要试着解决它了。涉及数据敏感性,我们这边模拟一下这种情况,构造一些数据来做测试。

1、创建两个表:员工表和部门表

/*部门表,存在则进行删除 */
drop table if EXISTS dep;
create table dep(
    id int unsigned primary key auto_increment,
    depno mediumint unsigned not null default 0,
    depname varchar(20) not null default "",
    memo varchar(200) not null default ""
);

/*员工表,存在则进行删除*/
drop table if EXISTS emp;
create table emp(
    id int unsigned primary key auto_increment,
    empno mediumint unsigned not null default 0,
    empname varchar(20) not null default "",
    job varchar(9) not null default "",
    mgr mediumint unsigned not null default 0,
    hiredate datetime not null,
    sal decimal(7,2) not null,
    comn decimal(7,2) not null,
    depno mediumint unsigned not null default 0
);

2、创建两个函数:生成随机字符串和随机编号

/* 产生随机字符串的函数*/
DELIMITER $
drop FUNCTION if EXISTS rand_string;
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
    SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    SET i = i+1;
    END WHILE;
    RETURN return_str;
END $
DELIMITER;


/*产生随机部门编号的函数*/
DELIMITER $
drop FUNCTION if EXISTS rand_num;
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100+RAND()*10);
    RETURN i;
END $
DELIMITER;

3、编写存储过程,模拟500W的员工数据

/*建立存储过程:往emp表中插入数据*/
DELIMITER $
drop PROCEDURE if EXISTS insert_emp;
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    /*set autocommit =0 把autocommit设置成0,把默认提交关闭*/
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO emp(empno,empname,job,mgr,hiredate,sal,comn,depno) VALUES ((START+i),rand_string(6),'SALEMAN',0001,now(),2000,400,rand_num());
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $
DELIMITER;
/*插入500W条数据*/
call insert_emp(0,5000000);

4、编写存储过程,模拟120的部门数据

/*建立存储过程:往dep表中插入数据*/
DELIMITER $
drop PROCEDURE if EXISTS insert_dept;
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i+1;
    INSERT  INTO dep( depno,depname,memo) VALUES((START+i),rand_string(10),rand_string(8));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $
DELIMITER;
/*插入120条数据*/
call insert_dept(1,120);

5、建立关键字段的索引,这边是跑完数据之后再建索引,会导致建索引耗时长,但是跑数据就会快一些。

/*建立关键字段的索引:排序、条件*/
CREATE INDEX idx_emp_id ON emp(id);
CREATE INDEX idx_emp_depno ON emp(depno);
CREATE INDEX idx_dep_depno ON dep(depno);

测试

测试数据

/*偏移量为100,取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;
/*偏移量为4800000,取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;

执行结果

[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;
受影响的行: 0
时间: 0.001s
[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;
受影响的行: 0
时间: 12.275s

因为扫描的数据多,所以这个明显不是一个量级上的耗时。

解决方案

1、使用索引覆盖+子查询优化

因为我们有主键id,并且在上面建了索引,所以可以先在索引树中找到开始位置的 id值,再根据找到的id值查询行数据。

/*子查询获取偏移100条的位置的id,在这个位置上往后取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 100,1)
order by a.id limit 25;

/*子查询获取偏移4800000条的位置的id,在这个位置上往后取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 4800000,1)
order by a.id limit 25;

执行结果

执行效率相比之前有大幅的提升:

[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 100,1)
order by a.id limit 25;
受影响的行: 0
时间: 0.106s

[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 4800000,1)
order by a.id limit 25;
受影响的行: 0
时间: 1.541s

2、起始位置重定义

记住上次查找结果的主键位置,避免使用偏移量 offset

/*记住了上次的分页的最后一条数据的id是100,这边就直接跳过100,从101开始扫描表*/
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;

/*记住了上次的分页的最后一条数据的id是4800000,这边就直接跳过4800000,从4800001开始扫描表*/
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 4800000
order by a.id limit 25;

执行结果

[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;
受影响的行: 0
时间: 0.001s

[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 4800000
order by a.id limit 25;
受影响的行: 0
时间: 0.000s

这个效率是最好的,无论怎么分页,耗时基本都是一致的,因为他执行完条件之后,都只扫描了25条数据。

但是有个问题,只适合一页一页的分页,这样才能记住前一个分页的最后Id。如果用户跳着分页就有问题了,比如刚刚刷完第25页,马上跳到35页,数据就会不对。

这种的适合场景是类似百度搜索或者腾讯新闻那种滚轮往下拉,不断拉取不断加载的情况。这种延迟加载会保证数据不会跳跃着获取。

3、降级策略

看了网上一个阿里的dba同学分享的方案:配置limit的偏移量和获取数一个最大值,超过这个最大值,就返回空数据。

因为他觉得超过这个值你已经不是在分页了,而是在刷数据了,如果确认要找数据,应该输入合适条件来缩小范围,而不是一页一页分页。

这个跟我同事的想法大致一样:request的时候 如果offset大于某个数值就先返回一个4xx的错误。

小结

当晚我们应用上述第三个方案,对offset做一下限流,超过某个值,就返回空值。第二天使用第一种和第二种配合使用的方案对程序和数据库脚本进一步做了优化。

合理来说做任何功能都应该考虑极端情况,设计容量都应该涵盖极端边界测试。

另外,该有的限流、降级也应该考虑进去。比如工具多线程调用,在短时间频率内8000次调用,可以使用计数服务判断并反馈用户调用过于频繁,直接给予断掉。

哎,大意了啊,搞了半夜,QA同学不讲武德。不过这是很美好的经历了。

- END -

欢迎加入我的知识星球,一起探讨架构,交流源码。加入方式,长按下方二维码噢:

已在知识星球更新源码解析如下:

最近更新《芋道 SpringBoot 2.X 入门》系列,已经 20 余篇,覆盖了 MyBatis、Redis、MongoDB、ES、分库分表、读写分离、SpringMVC、Webflux、权限、WebSocket、Dubbo、RabbitMQ、RocketMQ、Kafka、性能测试等等内容。

提供近 3W 行代码的 SpringBoot 示例,以及超 4W 行代码的电商微服务项目。

获取方式:点“在看”,关注公众号并回复 666 领取,更多内容陆续奉上。

文章有帮助的话,在看,转发吧。
谢谢支持哟 (*^__^*)
BQ25713寄存器配置全解析:从基础原理到笔记本充电实战 在电源管理系统中,寄存器配置是实现精细化控制的核心技术。其原理是通过I2C等总线向芯片内部特定地址写入二进制代码,直接设定其工作参数与行为逻辑,从而实现对电压、电流、功率及保护阈值的底层控制。这项技术的价值在于,它能将通用电源管理芯片灵活适配于千差万别的具体应用,是提升系统性能、可靠性与能效的关键。在便携设备、嵌入式系统等场景中,精准的寄存器配置决定了充电效率、动态响应和故障保护能力。本文聚焦于**BQ25713**这款经典充电管理芯片,深入剖析其**ChargeOption**、**ProchotOpti 阅读详情

相关推荐

UE5游戏逆向实战:用FModel提取.pak文件中的3D模型(附Dumper-7避坑指南)

本文详细介绍了如何使用FModel工具从UE5游戏的.pak文件中提取3D模型,并提供了Dumper-7的避坑指南。内容涵盖FModel工具链配置、UE5特有的unversioned properties问题解决方案、Dumper-7实战流程以及模型后处理技巧,帮助开发者高效获取游戏资产。

weixin_30872499的博客 425

一次深夜优化,MySQL亿级数据分页的奇妙经历

下班后,愉快的坐在在回家的地铁上,心里想着周末的生活怎么安排。 突然电话响了起来,一看是我们的一个开发同学,顿时紧张了起来,本周的版本已经发布过了,这时候打电话一般来说是线上出问题了。果...

架构师小秘圈 280

AT91SAM9260搭建LINUX操作系统

一、 编写目的 2 二、 系统搭建 2 1. 下载相关软件包 2 2. Bootstrap编译 3 3. U-BOOT编译 5 4. LINUX编译 7 5. 通过U-BOOT的TFTP命令从TFTP服务器下载系统镜像 9 6. 文件系统制作 10 7. 挂载NFS作为根文件系统 10 三、 总结 14 一、编写目的 本文档用于记录AT91SAM9260 LINUX学

lshiceman的专栏 7415

使用Mybatis分页插件(PageHelper)导致数据量大时查询慢的解决方法

描述: 在项目中使用MybatisPlus分页插件分页查询十分缓慢,但是在数据库中执行速度却很快? 该数据库的表共有10000条数据,分页查询时,在数据库中是很快,但是在使用Mybatis分页插件的时候居然8s多! 原因: PageHelper在做分页查询的时候,select count(0) 会先把原来的查询语句全部重新查询一遍(未做数量限制),然后再执行select count(0)操作,导致查询速度慢。例如: select count(*) from ("这里面的把sql又多重复查了一遍") user

慢积跬步的博客 1万+

Unity半透明物体景深失效?教你用双材质球方案完美解决(附完整Shader代码)

本文深入剖析了Unity中半透明物体导致景深效果失效的根本原因,即半透明物体默认不写入摄像机深度图。针对此问题,文章详细介绍了稳定高效的“双材质球”解决方案,通过为物体附加一个仅写入深度信息的不可见材质,完美修复景深效果,并提供了完整的Shader代码与实现脚本。

weixin_29010003的博客 384

从FedAvg到实战:手把手教你用PyTorch复现联邦学习经典论文实验(MNIST/CIFAR-10)

本文详细介绍了如何使用PyTorch复现联邦学习经典算法FedAvg在MNIST和CIFAR-10数据集上的实验。从环境搭建、数据非IID划分到模型实现和参数聚合,手把手教你掌握联邦学习的核心技术与实战技巧,帮助开发者和研究人员快速实现隐私保护的分布式机器学习方案。

weixin_29053695的博客 256

大数据量 Mybatis 分页插件Count语句优化

前言 当在大数量的情况下,进行分页查询,统计总数时,会自动count一次,这个语句是在我们的查询语句的基础上嵌套一层,如: SELECT COUNT(*) FROM (主sql) 这样在数据量大的情况下,会出问题,很容易cpu就跑满了 #优化 在mapper.xml中自定义count查询,使用自定义的查询速度会快些 参考资料: https://github.com/pagehelper/My...

kris 1万+

绿盾注册机 使用手册

在很多涉及工程设计的单位(如建筑、模具、电路、广告等),电子图纸的应用极为普遍,而电子图纸的丢失、被盗对企业利益的影响是显而易见的,如果被竞争对手获取,甚至可能造成致命的打击。因此,对于这类企业,电子图纸的保密显得至关重要。绿盾信息安全管理软件充分考虑了电子图纸的各种可能传播途径,制定了针对性的解决方案,从而保证了电子图纸的安全。 我们总结了电子图纸的各种传播途径以及相应的对策,有如下几点:

1000

mysql千万数据处理实践(java)

mysql千万数据处理实践 背景 线上一张数据表(数据量2500W)因为前期设计问题,导致某些关联关系没有整理,需要从这张表中重新整理对应的映射关系。 A 主表(2500W)关联字段 a ,映射字段 b B 主表关联表 (6000W) 关联字段 c , d C 映射对应主表(80W) 关联字段 c D 映射对应主表(140W) 关联字段 d select distinct C.c,A.b from A left join B on A.a = B.a left join C on B.c = C.c //

QFann的博客 1933

Cleo Synchronization接口任意文件读取漏洞CVE-2024-50623 附POC

微信公众号搜索:南风漏洞复现文库该文章 南风漏洞复现文库 公众号首发Cleo。

nnn2188185的博客 482

基于STM32、HAL库、I2C接口的姿态传感器/陀螺仪之ICM-42670-P驱动 C语言程序设计

/ 设置加速度计和陀螺仪的工作模式。// 读取加速度计和陀螺仪数据。

corlin6688的博客 1184

java使用线程池批量插入mysql数据

表示线程的存活时间,举例:一个工地设置了20个人(最大线程数),当工地活干完了有10个人处于空闲时间,当空闲时间达到了我们设定的时间就进行辞退(销毁)。:直接丢弃新任务,抛出异常,当有多个任务时,只要任务超出了设定任务的最大线程数加阻塞数时,就会抛出异常,没有超出的线程正常执行,超出报异常后面的不执行。DiscardPolicy:直接丢弃掉,不会抛出异常,最大线程数加阻塞数如果只要10,那么前10个线程会正常执行,后面加入的线程会被丢弃。的最老的任务,并将新的任务加入到。: 线程存活时间单位。.......

qq_38935605的博客 4955

【信息科学与工程学】【物理/化学科学和工程技术】第八篇 光学07

对于光源-探测器距离 ρ, 稳态漫反射率 R(ρ) ≈ (1/(4π)) [ z₀ (μ_eff + 1/r₁) exp(-μ_eff r₁)/r₁² + (z₀ + 2z_b) (μ_eff + 1/r₂) exp(-μ_eff r₂)/r₂² ], 其中 μ_eff = √(3μ_a(μ_a+μ_s’)), z₀=1/μ_s’, z_b 与边界条件有关。通过测量光纤各点的布里渊频移 ν_B(z) 来得到应变/温度分布:Δν_B(z) = C_ν,ε Δε(z) + C_ν,T ΔT(z)。

weixin_49199313的博客 335

MySQL分页查询的深度优化如何优雅地处理亿级数据分页?

优雅地处理MySQL亿级数据分页,关键在于转变思路,从依赖高开销偏移量的传统方式,转向基于游标的精准定位分页。结合覆盖索引、复合索引、查询缓存、数据分区等一系列优化手段,可以构建出在高并发、大数据量场景下依然保持高性能和可扩展性的分页系统。这些策略的核心思想在于最大限度地利用索引,减少不必要的数据扫描和IO操作,从而实现对海量数据的快速访问。

463

Outlook Express 6 (OE6) for Win7/Win8/Win10

英文版 Outlook Express6.0, 兼容Win7/Win10/WinXP,解压就可用。

IEM模型,地表粗糙度模拟

利用微波反演土壤水分时候,利用IEM模型可以实现对随机地表的模拟

mysql上亿数据秒级查询_MySQL大量数据分页查询,如何优化?

作者:悠悠icnblogs.com/youyoui/p/7851007.html一 背景大部分开发和DBA同行都对分页查询非常非常了解,看帖子翻页需要分页查询,搜索商品也需要分页查询。那么问题来了,遇到上千万或者上亿的数据量怎么快速的拉取全量,比如大商家拉取每月千万级别的订单数量到自己独立的ISV做财务统计;或者拥有百万千万粉丝的公众大号,给全部粉丝推送消息的场景。本文讲讲个人的优化分页...

weixin_39984442的博客 8046

ET2012新版免狗免安装绿色软件

ET2012新版免狗免安祖昂绿色软件。,新界面,新视觉,

优化MySQL亿级数据分页

1月22号晚上10点半,下班后愉快的坐在在回家的地铁上,心里想着周末的生活怎么安排。 突然电话响了起来,一看是我们的一个开发同学,顿时紧张了起来,本周的版本已经发布过了,这时候打电话一般来说是线上出问题了。 果然,沟通的情况是线上的一个查询数据的接口被疯狂的失去理智般的调用,这个操作直接导致线上的MySql集群被拖慢了。 好吧,这问题算是严重了,下了地铁匆匆赶到家,开电脑,跟同事把Pinpoint上的慢查询日志捞出来。看到一个很奇怪的查询,如下 POST domain/v1.0/module/m.

Pastxu的小屋 440

lwip-2.0.3

lwip是瑞典计算机科学院(SICS)的Adam Dunkels 开发的一个小型开源的TCP/IP协议栈。实现的重点是在保持TCP协议主要功能的基础上减少对RAM 的占用。

亿级数据分页优化:MySQL、ES与MongoDB实战方案

数据库分页查询是系统开发中的基础需求,但当数据量达到亿级时,传统LIMIT OFFSET方式会导致严重的性能问题。其核心原理在于OFFSET需要扫描并丢弃大量数据,造成资源浪费。通过游标分页(MySQL)、search_after(Elasticsearch)和范围查询(MongoDB)等技术,可以实现O(1)时间复杂度的深度分页。这些优化方案在电商订单查询、金融交易系统等高并发场景中尤为重要,能有效降低数据库CPU负载,将响应时间从秒级优化到毫秒级。针对混合架构场景,还可采用二级索引回表、分页缓存等通用解

weixin_34174105的博客 310

一次深夜 MySQL 优化,亿级数据分页的奇妙经历

>>号外:关注“Java精选”公众号,回复“2021面试题”,领取免费资料!“Java精选面试题”小程序,3000+ 道面试题在线刷,最新、最全 Java 面试题!背景下班后愉...

Java精选 311

一次深夜优化MySQL亿级数据分页的奇妙经历

点击上方☝码猿技术专栏轻松关注,设为星标!及时获取有趣有料的技术作者:翁智华cnblogs.com/wzh2010/p/14316920.html背景1月22号晚上10点半,下班后愉快的...

码猿技术专栏 309
上一篇: 没错,使用 Nacos 踩坑了
下一篇: 华为宣布:免费培养2000名Java开发者
公众号-芋道源码
博客等级 码龄10年 1639粉丝 377原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值