当前位置: 首页 > 科技观察

粗心!一起MySQL分页引发的线上事故...

时间:2023-03-19 12:16:04 科技观察

一、背景一天晚上10:30,下班后兴高采烈地坐在回家的地铁上,思考着周末的生活该如何安排。突然电话响了。一看是我们运维同学,顿时紧张起来。本周的版本已经发布。一般来说,这个时候打电话是有在线问题的。果然,沟通情况是一个查询数据的在线接口被疯狂无理调用。这个操作直接导致线上的MySql集群变慢。好吧,这个问题被认为是严重的。赶回家后,我打开电脑,和同事一起在Pinpoint上翻出了慢查询日志。看到一个很奇怪的查询,如下:1POSTdomain/v1.0/module/method?order=condition&orderType=desc&offset=1800000&limit=500domain,module,method都是别名,代表的域,模块和实例方法名界面中,下面的offset和limit代表的是分页操作的偏移量和每页的页数,也就是说学生在翻(1800000/500+1=3601)页。初步查找日志,发现有8000多个这样的调用。这太神奇了,我们页面的分页单页数不是500,而是每页25个条目。这绝对不是功能页面人为的翻页操作,而是刷了数据(注意,我们的生产环境数据有1亿+)。详细对比日志,发现很多paging的时间是重叠的,对方应该是多线程调用。通过对authenticationtoken的分析,基本定位到请求来自一个叫ApiAutotest的客户端程序做这个操作,同时也定位到生成authenticationtoken的账号来自一个QA同学。马上给同学打电话,沟通处理。2.分析其实对于我们的MySQL查询语句来说,整体效率还是不错的。有一些联表查询优化,简单的查询内容也有,关键条件字段和排序字段都有索引。问题是他一页一页地搜索。页码越靠后,扫描的数据越多,速度越慢。我们查看前几页的时候,发现速度很快,比如limit200,25,瞬间就出来了。但是越往前,速度就越慢,尤其是一百万次之后,卡就不行了,请问这是什么原理。我们翻页看后面查询的sql:1select*fromt_namewherec_name1='xxx'orderbyc_name2limit2000000,25;位移过大造成的。比如像上面的limit2000000,25,这相当于从数据库中扫描出2000025条数据,然后丢弃之前的20000000条数据,将剩下的25条数据返回给用户,这显然是不合理的.请参考《高性能MySQL》章节:查询性能优化,其中解释了这个问题:分页操作通常使用limit加offset,以及适当的orderby子句来实现。但这有一个通病:当偏移量很大时,会导致MySQL扫描很多不需要的行,然后丢弃。3.数据模拟嗯,如果你理解了问题的原理,那就试着解决吧。它涉及数据敏感性。让我们模拟这种情况并构建一些数据进行测试。1.创建两张表:employee表和department表/*department表,存在则删除*/droptableifEXISTSdep;createtabledep(idintunsignedprimarykeyauto_increment,depnomediumintunsignednotnulldefault0,depnamevarchar(20)notnulldefault"",memovarchar(200)notnulldefault"");/*employee表,如果存在则删除*/droptableifEXISTSemp;createtableemp(idintunsignedprimarykeyauto_increment,empnomediumintunsignednotnulldefault0,empnamevarchar(20)notnulldefault"",jobvarchar(9)notnulldefault"",mgrmediumintunsignednotnulldefault0,hireddatetimenotnull,saldecimal(7,2)notnull,comndecimal(7,2)notnull,depnomediumintunsignednotnulldefault0);2.创建两个函数:生成随机字符串和随机数/*生成随机字符串的函数*/DELIMITER$dropFUNCTIONifEXISTSrand_string;创建函数rand_string(nINT)RETURNSVARCHAR(255)BEGINDECLAREchars_strVARCHAR(100)DEFAULT'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';声明return_strVARCHAREF(255)LT'';声明iINT默认值0;WHILEi=(selectidfromemporderbyidlimit100,1)orderbya.idlimit25;/*子查询得到偏移量4800000位置的id,取25*/SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depnamefromempaleftjoindepbona.depno=b.depnowherea.id>=(selectidfromemporderbyidlimit4800000,1)orderbya.idlimit25;执行结果执行效率比之前有了很大的提升:[SQL]SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depnamefromempaleftjoindepbona。depno=b.depnowherea.id>=(selectidfromemporderbyidlimit100,1)orderbya.idlimit25;受影响的行:0时间:0.106s[SQL]SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depnamefromempaleftjoindepbona.depno=b.depnowherea.id>=(selectidfromemporderbyidlimit4800000,1)orderbya.idlimit25;受影响行数:0时间:1.541s2,重新定义起始位置记住上次搜索结果的主键位置,避免使用offset。/*记住上次分页最后一条数据的id是100,这里跳过100,从101开始扫表*/SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefromempaleftjoindepbona.depno=b.depnowherea.id>100orderbya.idlimit25;/*记住上次分页最后一条数据的id是4800000,这里跳过4800000,从4800001开始扫表*/SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b。depnamefromempaleftjoindepbona.depno=b.depnowherea.id>4800000orderbya.idlimit25;执行结果[SQL]SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefromempaleftjoindepbona.depno=b.depnowherea.id>100orderbya.idlimit25;受影响的行:0时间:0.001s[SQL]SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefromempaleftjoindepbona.depno=b.depnowherea.id>4800000orderbya.idlimit25;影响行数:0时间:0.000s这个效率是最好的,不管怎么分页,耗时基本是一样的,因为条件执行完后,只扫描了25条数据。但是有一个问题,它只适合逐页分页,这样才能记住上一页的最后一个Id。如果用户跳转到页面,就会出现问题。比如刚看完25页,马上跳到35页,数据就会出错。这种适合的场景类似于百度搜索或者腾讯新闻,滚轮下拉,一直拉动加载。这种延迟加载将确保数据不会被跳过。3.降级策略我在网上看了一个阿里dba同学分享的解决方案:配置limit的offset和获取次数的最大值。如果超过这个最大值,将返回空数据。因为他认为你超过这个值,就不再是寻呼,而是刷数据了。如果确定要查找数据,则应输入适当的条件以缩小范围,而不是一次一页地翻页。这和我同事的想法大致相同:请求时,如果偏移量大于某个值,则先返回4xx错误。6.总结晚上我们应用了上面的第三种方案来限制offset的电流。如果超过某个值,则返回空值。第二天,使用第一种方案和第二种方案进一步优化程序和数据库脚本。按理说,做任何功能都要考虑极端情况,设计能力要覆盖极端边界测试。此外,还应考虑一些限流和降级。比如工具的多线程调用,短时间内8000次调用,可以使用计数服务判断并反馈给用户调用过于频繁,直接砍掉。哎,我大意了,大半夜的,QA同学不讲武功了。