1. 树叶云首页
  2. 技术资讯

大意了！一次MySQL分页导致的线上事故……

运维••技术资讯

一、背景

一天晚上10点半，下班后愉快的坐在回家的地铁上，心里想着周末的生活怎么安排。

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

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

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

1POSTdomain/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是怎样的：

1 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、创建两个表：员工表和部门表

/*部门表,存在则进行删除 */
droptableifEXISTSdep;

createtabledep(

idintunsignedprimarykeyauto_increment,

depnomediumintunsignednotnulldefault0,

depnamevarchar(20) notnulldefault"",

memovarchar(200) notnulldefault""

);

/*员工表,存在则进行删除*/

droptableifEXISTSemp;

createtableemp(

idintunsignedprimarykeyauto_increment,

empnomediumintunsignednotnulldefault0,

empnamevarchar(20) notnulldefault"",

jobvarchar(9) notnulldefault"",

mgrmediumintunsignednotnulldefault0,

hiredatedatetimenotnull,

saldecimal(7,2) notnull,

comndecimal(7,2) notnull,

depnomediumintunsignednotnulldefault0

);

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

/* 产生随机字符串的函数*/
DELIMITER$

dropFUNCTIONifEXISTSrand_string;

CREATEFUNCTIONrand_string(nINT) RETURNSVARCHAR(255)

BEGIN

DECLAREchars_strVARCHAR(100) DEFAULT'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

DECLAREreturn_strVARCHAR(255) DEFAULT'';

DECLAREiINTDEFAULT0;

WHILEi<nDO

SETreturn_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));

SETi=i+1;

ENDWHILE;

RETURNreturn_str;

END$

DELIMITER;




/*产生随机部门编号的函数*/

DELIMITER$

dropFUNCTIONifEXISTSrand_num;

CREATEFUNCTIONrand_num() RETURNSINT(5)

BEGIN

DECLAREiINTDEFAULT0;

SETi=FLOOR(100+RAND()*10);

RETURNi;

END$

DELIMITER;

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

/*建立存储过程：往emp表中插入数据*/
DELIMITER$

dropPROCEDUREifEXISTSinsert_emp;

CREATEPROCEDUREinsert_emp(INSTARTINT(10),INmax_numINT(10))

BEGIN

DECLAREiINTDEFAULT0;

/*set autocommit =0 把autocommit设置成0，把默认提交关闭*/

SETautocommit=0;

REPEAT

SETi=i+1;

INSERTINTOemp(empno,empname,job,mgr,hiredate,sal,comn,depno) VALUES ((START+i),rand_string(6),'SALEMAN',0001,now(),2000,400,rand_num());

UNTILi=max_num

ENDREPEAT;

COMMIT;

END$

DELIMITER;

/*插入500W条数据*/

callinsert_emp(0,5000000);

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

/*建立存储过程：往dep表中插入数据*/
DELIMITER$

dropPROCEDUREifEXISTSinsert_dept;

CREATEPROCEDUREinsert_dept(INSTARTINT(10),INmax_numINT(10))

BEGIN

DECLAREiINTDEFAULT0;

SETautocommit=0;

REPEAT

SETi=i+1;

INSERTINTOdep( depno,depname,memo) VALUES((START+i),rand_string(10),rand_string(8));

UNTILi=max_num

ENDREPEAT;

COMMIT;

END$

DELIMITER;

/*插入120条数据*/

callinsert_dept(1,120);

5、建立关键字段的索引

这边是跑完数据之后再建索引，会导致建索引耗时长，但是跑数据就会快一些。

/*建立关键字段的索引:排序、条件*/
CREATEINDEXidx_emp_idONemp(id);

CREATEINDEXidx_emp_depnoONemp(depno);

CREATEINDEXidx_dep_depnoONdep(depno);

四、测试

1、测试数据

/*偏移量为100，取25*/
SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname

fromempaleftjoindepbona.depno=b.depnoorderbya.iddesclimit100,25;

/*偏移量为4800000，取25*/

SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname

fromempaleftjoindepbona.depno=b.depnoorderbya.iddesclimit4800000,25;

2、执行结果

[SQL]
SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname

fromempaleftjoindepbona.depno=b.depnoorderbya.iddesclimit100,25;

受影响的行: 0

时间: 0.001s

[SQL]

SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname

fromempaleftjoindepbona.depno=b.depnoorderbya.iddesclimit4800000,25;

受影响的行: 0

时间: 12.275s

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

五、解决方案

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

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

/*子查询获取偏移100条的位置的id，在这个位置上往后取25*/
SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname

fromempaleftjoindepbona.depno=b.depno

wherea.id>= (selectidfromemporderbyidlimit100,1)

orderbya.idlimit25;

/*子查询获取偏移4800000条的位置的id，在这个位置上往后取25*/

SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname

fromempaleftjoindepbona.depno=b.depno

wherea.id>= (selectidfromemporderbyidlimit4800000,1)

orderbya.idlimit25;

执行结果

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

[SQL]
SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname

fromempaleftjoindepbona.depno=b.depno

wherea.id>= (selectidfromemporderbyidlimit100,1)

orderbya.idlimit25;

受影响的行: 0

时间: 0.106s



 [SQL]

SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname

fromempaleftjoindepbona.depno=b.depno

wherea.id>= (selectidfromemporderbyidlimit4800000,1)

orderbya.idlimit25;

受影响的行: 0

时间: 1.541s

2、起始位置重定义

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

/*记住了上次的分页的最后一条数据的id是100，这边就直接跳过100，从101开始扫描表*/
SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname

fromempaleftjoindepbona.depno=b.depno

wherea.id>100orderbya.idlimit25;



/*记住了上次的分页的最后一条数据的id是4800000，这边就直接跳过4800000，从4800001开始扫描表*/

SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname

fromempaleftjoindepbona.depno=b.depno

wherea.id>4800000

orderbya.idlimit25;

执行结果

[SQL]
SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname

fromempaleftjoindepbona.depno=b.depno

wherea.id>100orderbya.idlimit25;

受影响的行: 0

时间: 0.001s


[SQL]

SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname

fromempaleftjoindepbona.depno=b.depno

wherea.id>4800000

orderbya.idlimit25;

受影响的行: 0

时间: 0.000s

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

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

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

3、降级策略

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

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

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

六、小结

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

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

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

哎，大意了啊，搞了半夜，QA同学不讲武德。

文章来源网络，作者：运维，如若转载，请注明出处：https://shuyeidc.com/wp/288636.html<

赞 (0)
运维的头像运维
00
上一篇2025-05-16 10:19
下一篇 2025-05-16 10:20

相关推荐

  • 如何制作个人主题技术资讯

    个人主题怎么制作？

    制作个人主题是一个将个人风格、兴趣或专业领域转化为视觉化或结构化内容的过程，无论是用于个人博客、作品集、社交媒体账号还是品牌形象，核心都是围绕“个人特色”展开，以下从定位、内容规划、视觉设计、技术实现四个维度,详细拆解制作个人主题的完整流程，明确主题定位：找到个人特色的核心主题定位是所有工作的起点，需要先回答……

    运维的头像运维
    2025-11-20
    0
  • 如何管理社群营销技术资讯

    社群营销管理关键是什么？

    社群营销的核心在于通过建立有温度、有价值、有归属感的社群，实现用户留存、转化和品牌传播，其管理需贯穿“目标定位-内容运营-用户互动-数据驱动-风险控制”全流程,以下从五个维度展开详细说明：明确社群定位与目标社群管理的首要任务是精准定位，需明确社群的核心价值（如行业交流、产品使用指导、兴趣分享等）、目标用户画像……

    运维的头像运维
    2025-11-20
    0
  • 香港公司如何网站备案技术资讯

    香港公司网站备案需要什么材料？

    香港公司进行网站备案是一个涉及多部门协调、流程相对严谨的过程，尤其需兼顾中国内地与香港两地的监管要求，由于香港公司注册地与中国内地不同，其网站若主要服务内地用户或使用内地服务器，需根据服务器位置、网站内容性质等，选择对应的备案路径（如工信部ICP备案或公安备案），以下从备案主体资格、流程步骤、材料准备、注意事项……

    运维的头像运维
    2025-11-20
    0
  • 如何企业上云推广技术资讯

    如何企业上云推广

    企业上云已成为数字化转型的核心战略,但推广过程中需结合行业特性、企业痛点与市场需求，构建系统性、多维度的推广体系，以下从市场定位、策略设计、执行落地及效果优化四个维度，详细拆解企业上云推广的实践路径，精准定位：明确目标企业与核心价值企业上云并非“一刀切”的方案，需先锁定目标客户群体，提炼差异化价值主张，客户分层……

    运维的头像运维
    2025-11-20
    0
  • PS如何设计搜索框技术资讯

    PS设计搜索框的实用技巧有哪些？

    在PS中设计一个美观且功能性的搜索框需要结合创意构思、视觉设计和用户体验考量，以下从设计思路、制作步骤、细节优化及交互预览等方面详细说明，帮助打造符合需求的搜索框，设计前的规划明确使用场景：根据网站或APP的整体风格确定搜索框的调性，例如极简风适合细线条和纯色，科技感适合渐变和发光效果，电商类则可能需要突出搜索……

    运维的头像运维
    2025-11-20
    0

发表回复

您的邮箱地址不会被公开。必填项已用 * 标注