大意了,一次MySQL分页导致的线上事故...

记一次 MySQL 启动导致的事故 MySQL 启动存在端口被监听, 但服务没完全启动的情况. 背景 MySQL 启动时会进行 第一次 初始化数据库, 等待 MySQL 完全启动后, 再进行 第二次 初始化数据库. 第一次 初始化数据库使用 --skip-networking (这个选项表示禁止 MySQL 的 TCP/IP 连接方式) 启动 MySQL 进行初始化, 初始化完成后会关闭 --skip-networking 选项重新启动 MySQL. 第二次 初始化数据库会设置 root 密码. 判断 MySQL 已完全启动的方式 尝试主动 阅读详情

今天给大家分享个生产事故,一个由于MySQL分页导致的线上事故,事情是这样的~

背景

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

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

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

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

1 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是怎样的:

1 select * from t_namewhere c_name1='xxx' order by c_name2 limit 2000000,25;

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

图片image-20220421082735727

大家翻看《高性能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同学不讲武德。

Java后端面试官
Java后端全栈面试题精选,数据库、消息队列、架构.....你想要知道的都在这里!
15篇原创内容
来源:https://mp.weixin.qq.com/s/AqA9SmHtoXDASEA4btlHHA
电路板功能测试系统-FCT for PCBA 电路板功能测试系统用于电路板组件(PCBA)的功能测试(FCT),满足自动化大批量生产的要求。  ● 用户根据自己需要定义测试步骤,灵活方便  ● 适合工业自动化大批量生产应用  ● 系统测试精度高,稳定可靠   一、电路板功能测试系统概述 电路板测试过程通常由多个步骤组成,每单个步骤中需要对多个物理量(电压、电流、电阻、频率、波形等)进行测试。在每个步骤测试时 阅读详情

相关推荐

平衡电桥检测高压绝缘电阻的原理

检测绝缘漏电的高压拓扑如下:BMU需要连接BAT+,BAT-和PE线,并测量电池总压(Ubatt)和负母线电压(Un),并计算出正母线电压(Up)。

booksyhay的专栏 1万+

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

今天给大家分享个生产事故,一个由于MySQL分页导致的线上事故,事情是这样的~一、背景一天晚上10点半,下班后愉快的坐在回家的地铁上,心里想着周末的生活怎么安排。突然电话响了起来,一看是我们的一个运维同学,顿时紧张了起来,本周的版本已经发布过了,这时候打电话一般来说是线上出问题了。果然,沟通的情况是线上的一个查询数据的接口被疯狂的失去理智般的调用,这个操作直接导致线上的MySql集群被拖慢了。好吧,这问题算是严重了,匆匆赶到家后打开电脑,跟同事把Pinpoint上的慢查询日志捞出来。看到一个很奇怪的查询,如

uuqaz的博客 206

CANoe实战:LIN Slave一致性测试全流程解析

本文详细解析了使用CANoe进行LIN Slave一致性测试的全流程,包括测试概述、环境搭建、模块集成、测试用例详解及报告分析。通过实际案例分享,帮助工程师掌握LIN 2.0/2.1/2.2、SAE J2602和ISO 17987等标准的自动化测试方法,提升汽车电子从节点的可靠性和兼容性验证效率。

weixin_42665725的博客 533

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

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

yangjnsjbad的博客 134

百度网盘开放平台OAuth 2.0授权全流程解析

本文详细解析了百度网盘开放平台OAuth 2.0授权全流程,包括创建应用、获取凭证、授权码模式详解以及access_token的使用与刷新。通过实际案例和代码示例,帮助开发者快速掌握接口调用技巧,避免常见错误,确保安全高效地接入百度网盘API。

wind6的博客 1271

Calibre电子书阅读器个性化样式定制指南

本文详细介绍了如何通过Calibre电子书阅读器的用户样式表功能进行深度个性化样式定制。从修改全局字体、行距、背景配色(包括深色/浅色模式)到美化代码块、引用等特殊元素,本指南提供了完整的CSS代码示例与实战技巧,帮助用户打造专属的舒适阅读环境,显著提升电子书阅读体验。

vv45678的博客 922

一次 MySQL 线上事故,被教育了

目录背景分析数据模拟测试解决方案小结今天给大家分享个生产事故,一个由于 MySQL 分页导致的线上事故,事情是这样的~背景一天晚上 10 点半,下班后愉快的坐在在回家的地铁上,心里想着周末的生活怎么安排。突然电话响了起来,一看是我们的一个运维同学,顿时紧张了起来,本周的版本已经发布过了,这时候打电话一般来说是线上出问题了。果然,沟通的情况是线上的一个查询数据的接口被疯狂的...

hnjsjsac的博客 886

AIDC储能技术:AI数据中心的高效能源解决方案

储能技术作为现代数据中心的核心支撑系统,通过电化学能量存储与释放实现电力供需平衡。其核心原理在于利用电池管理系统(BMS)实现毫秒级响应,配合光伏等可再生能源构成光储融合系统。在AI算力爆发的背景下,AIDC储能系统展现出三大技术价值:应对脉冲式电力需求、保障供电连续性、提升绿色能源占比。典型应用场景包括大模型训练中心、超算设施等对电力质量要求苛刻的环境。当前行业聚焦10C超高倍率电芯和直流侧耦合架构等创新方案,其中磷酸铁锂电池凭借三维极耳设计和液冷系统等技术突破,成为AIDC储能的主流选择。

weixin_29189377的博客 119

一次线上MySQL分页事故,搞了半夜...

往期热门文章:1、因为造轮子,我一个月就转正了 2、用了Stream后,代码反而越写越丑? 3、一不小心节约了 591 台机器! 4、你见过哪些目瞪口呆的 Java 代码技巧? 5、笑死!程序员延寿指南开源了文章来源:【公众号:JAVA日知录】目录背景分析数据模拟测试解决方案小结今天给大家分享个生产事故,一个由于 MySQL 分页导致的线上事故,事情是这样的~背景一天晚上...

Java后端技术 284

【ArcGIS微课1000例】0068:ArcGIS加载world imagery高清卫星影像教程

本文介绍了如何在ArcGIS中加载World Imagery高清卫星影像作为谷歌影像的替代方案。通过添加ArcGIS Server并输入在线图集URL(https://services.arcgisonline.com/ArcGIS/rest/services/World_Imagery/MapServer),即可连接并预览包含中国影像、青海湖、东方明珠等全球高清卫星图。该教程提供了详细的操作步骤图示,帮助用户快速获取优质底图数据,适用于地理信息分析和可视化应用。

智慧空间实验室 5425

从源码编译CesiumForUnreal

从源码编译CesiumForUnreal插件

专注GIS开发 5644

SQL调优:记一次慢查询导致的线上事故排查

向下继续去翻监控盘,InnoDB Buffer Pool的指标异常比较可疑,并且其他指标都没有显著异常,这证明接收的请求没有明显变化。在节点式应用兼容版本中已经拆分成8个子表的image表新增了两个字段,其中result字段默认值为1,并且目前几乎所有已有的查询条件都需要补充“result = 1”的过滤条件。当使用了where语句,SELECT COUNT(*) 时可能仍会读取全部数据,刚好Buffer Pool不存在这些数据,这就导致查询进行大量磁盘IO,导致CPU、磁盘IO升高。

qq_45867699的博客 987

AUTOSAR从入门到精通-汽车虚拟控制器(vECU)(二)

虚拟控制器的概念其实一直都存在,可能稍微有点差异,与此相关的有主机厂或供应商的ECU系统仿真或或控制策略开发工作。简而言之,虚拟控制器是一种软件工具,用于模拟ECU的功能和性能,无需实际的ECU硬件载体。source: 什么是虚拟ECU?- dSPACE空间虚拟控制器的原理主要是基于软件仿真技术,它可以模拟各种传感器的输入,执行器的输出和控制器的算法逻辑,它也可以模拟与实际控制器之间的通信接口,以便与其他ECU进行交互,如CAN总线通信等。

getusushu的博客 907

线上MySQL分页事故,搞了半夜...

今天给大家分享个生产事故,一个由于 MySQL 分页导致的线上事故,事情是这样的~ 背景 一天晚上 10 点半,下班后愉快的坐在在回家的地铁上,心里想着周末的生活怎么安排。 突然电话响了起来,一看是我们的一个运维同学,顿时紧张了起来,本周的版本已经发布过了,这时候打电话一般来说是线上出问题了。 果然,沟通的情况是线上的一个查询数据的接口被疯狂的失去理智般的调用,这个操作直接导致线上的 MySQL 集群被拖慢了。 好吧,这问题算是严重了,匆匆赶到家后打开电脑,跟同事把 Pinpo

哈利路亚的收藏夹 167

IMSL CNL 7.0.0 (x86-64)Crack

IMSL CNL 7.0.0 (x86-64)Crack intel的算法库破解文件,这是C++版本的破解文件

Camera2Demo

在Camera2 的基础上进行封装,使得Camera2一堆初始化逻辑与Activity分离,降低耦合度,完成预览拍照并保存的操作

记一次 MySQL JSON 字段索引失效,导致的百万级数据查询雪崩事故复盘

你是否在项目中用过 MySQL 的 `JSON` 字段?它灵活好用,但稍有不慎就可能埋下巨大的性能地雷。本文以“默语”博主的身份,复盘一次因 `JSON` 字段索引设计不当,导致线上百万级数据查询变慢,最终引发连锁反应导致服务雪崩的真实事故。文章将从现象、排查、原理到最终解决,手把手带你走一遍排查流程,深入浅出地讲解 MySQL `JSON` 索引的核心知识点,让你彻底搞懂这个“最熟悉的陌生人”,避免重蹈覆辙。

沉淀所学,分享所思,让热爱与成长同行。商务记录AI实战经验,助力开发者快速成长。 热爱AI,希望做出有影响力的技术成果。 技术点亮生活,分享连接价值与机会。 专注AI落地实战,陪你走好技术每一步。 2737

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

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

上一篇: JavaScript常用的简洁高级技巧
下一篇: 100000 行级别数据的 Excel 导入优化之路
码趣资源网
博客等级 码龄4年 269粉丝 518原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明:

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

余额充值