一不留神，半夜就做了10亿级的数据页优化！

图片来自Pexels突然电话响了，是我们开发同学之一，我一下子紧张起来。这周的版本已经发布了，这时候的调用一般是线上的问题。果然，沟通情况是一个查询数据的在线接口被疯狂无理调用。这个操作直接导致线上MySQL集群变慢。好吧，这个问题被认为是严重的。下了地铁就赶紧回家，打开电脑，和同事一起在Pinpoint上翻出慢查询日志。看到一个很奇怪的查询，如下：POSTdomain/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的时间是重叠的，对方应该是多线程调用。通过对鉴权Token的分析，基本定位请求来自一个叫ApiAutotest的客户端程序做这个操作，同时也定位生成鉴权Token的账号来自一个QA同学。马上给同学打电话，沟通处理。分析其实对于我们的MySQL查询语句来说，整体效率还是不错的。有一些联表查询优化，简单的查询内容也有，关键条件字段和排序字段都有索引。问题是他一页一页地搜索。页码越靠后，扫描的数据越多，速度越慢。我们查看前几页的时候，发现速度很快，比如limit200、25，瞬间就出来了。但是越往前，速度就越慢，尤其是一百万次之后，卡就不行了，请问这是什么原理。我们翻页看后面查询的SQL：select*fromt_namewherec_name1='xxx'orderbyc_name2limit2000000,25;这个查询的慢实际上是由于限制后面的大偏移量造成的。比如像上面的limit2000000,25。这相当于从数据库中扫描出2,000,025条数据，然后丢弃前面的20,000,000条数据，将剩下的25条数据返回给用户。这种方法显然是不合理的。这个问题的解释请参考《高性能 MySQL》的第6章“查询性能优化”：分页操作通常使用limit加offset，以及合适的orderby子句来实现。但这有一个通病：当偏移量很大时，会导致MySQL扫描很多不需要的行，然后丢弃。数据模拟不错。如果你明白问题的原理，那就试着解决它。它涉及数据敏感性。让我们模拟这种情况并构建一些数据进行测试。①创建两张表：员工表和部门表。/*部门表，如果存在则删除*/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,defaultcomndecimal(7,2)notnullin,tundiumin,dep两个函数：生成随机字符串和随机数。/*产生随机字符串的函数*/DELIMITER$dropFUNCTIONifEXISTSrand_string;CREATEFUNCTIONrand_string(nINT)RETURNSVARCHAR(255)BEGINDECLAREchars_strVARCHAR(100)DEFAULT'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';DECLAREreturn_strVARCHAR(255)DEFAULT'';DECLAREiINTDEFAULT0;WHILEi=(selectidfromemporderbyidlimit100,1)orderbya.idlimit25;/*子查询获取位置偏移4800000项的id，从该位置向后取25*/SELECTa.empno,a.empname,a。工作，a.sal，b.depno，b.depnamefromemmpaleftjoindepbona.depno=b.depnowherea.id>=(selectidfrommemporderbyidlimit4800000,1)orderbya.idlimit25;执行结果，执行效率比之前有了很大的提升：[SQL]SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrommempaleftjoindepbona.depno=b.depnowherea.id>=(selectidfrommemporderbyidlimit100,1)orderbya.idlimit25;受影响的行：0时间：0.106s[SQL]SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrommempaleftjoindepbona.depno=b.depnowhereea.id>=(selectidfrommemporderbyidlimit4800000,1)orderbya.idlimit25;受影响行数：0时间：1.541s②重新定义起始位置记住上次搜索结果的主键位置，避免使用offsetoffset:/*记住上次分页最后一条数据的id为100,就跳到这里100之后,从101*/SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefromemmpaleftjoindepbona.depno=b.depnowhere开始扫描表a.id>100orderbya.idlimit25;/*记住上次分页最后一条数据的id是4800000，这里我们跳过4800000，从4800001开始扫描表*/SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrommempaleftjoindepbona.depno=b.depnowherea.id>4800000orderbya.idlimit25;执行结果：[SQL]SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrommempaleftjoindepbona.depno=b.depnowherea.id>100orderbya.idlimit25;受影响的行：0Time:0.001s[SQL]SELECTa.id,a.empno,a。empname,a.job,a.sal,b.depno,b.depnamefrommempaleftjoindepbona.depno=b.depnowherea.id>4800000orderbya.idlimit25;影响行数：0时间：0.000s这个效率是最好的，无论怎么分页，耗时基本一样，因为条件执行完后，只扫描了25条数据，但是有一个问题，就是只适合逐页的，这样可以记住上一页的最后一个Id。如果用户跳过页面，就会出现问题。比如刚翻到第25页，马上跳到第35页，数据就会出错。这种适合的场景类似于百度搜索或者腾讯新闻，滚轮下拉，一直拉动加载。这种延迟加载将确保数据不会被跳过。③降级策略我在网上看了一个阿里DBA同学分享的解决方案：配置limit的offset和获取次数的最大值。如果超过这个最大值，将返回空数据。因为他认为你超过这个值，就不再是寻呼，而是刷数据了。如果确定要查找数据，则应输入适当的条件以缩小范围，而不是一次一页地翻页。这和我同事的想法大致相同：请求时，如果偏移量大于某个值，则先返回4xx错误。总结晚上，我们应用了上面的第三种解决方案来限制偏移量的电流。如果超过某个值，则返回空值。第二天，使用第一种方案和第二种方案进一步优化程序和数据库脚本。按理说，做任何功能都要考虑极端情况，设计能力要覆盖极端边界测试。此外，还应考虑一些限流和降级。比如工具被多个线程调用，短时间内调用了8000次，可以使用计数服务判断调用太频繁，反馈给用户，直接停止。嘿，我不小心。半夜过后，QA同学不谈武功。但这是一次美好的经历。作者：翁志华编辑：陶家龙来源：cnblogs.com/wzh2010/p/14316920.html