深夜优化MySQL亿级数据分页的精彩体验

背景晚上10点30分1月22日，下班后，我兴高采烈地坐在回家的地铁上，想着如何安排周末的生活。突然电话响了。一看是我们的开发同学，顿时紧张起来。本周的版本已经发布。一般来说，这个时候打电话是有在线问题的。果然，沟通情况是一个查询数据的在线接口被疯狂无理调用。这个操作直接导致线上的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的时间是重叠的，对方应该是多线程调用。通过对authenticationtoken的分析，基本定位到请求来自一个叫ApiAutotest的客户端程序做这个操作，同时也定位到生成authenticationtoken的账号来自一个QA同学。马上给同学打电话，沟通处理。分析其实对于我们的MySQL查询语句来说，整体效率还是不错的。有一些联表查询优化，简单的查询内容也有，关键条件字段和排序字段都有索引。问题是他一页一页地查询。他找到的页面越多，扫描的数据就越多，速度也就越慢。我们查看前几页的时候，发现速度很快，比如limit200，25，瞬间就出来了。但是越往前，速度就越慢，尤其是一百万次之后，卡就不行了，请问这是什么原理。我们翻页看后面的查询SQL：select*fromt_namewherec_name1='xxx'orderbyc_name2limit2000000,25;这个查询的慢实际上是由于限制后面的大偏移量造成的。比如像上面的limit2000000,25，这相当于从数据库中扫描出2000025条数据，然后丢弃之前的20000000条数据，将剩下的25条数据返回给用户，这显然是不合理的.请参考《高性能MySQL》章节：查询性能优化，其中解释了这个问题：分页操作通常使用limit加offset，以及适当的orderby子句来实现。但这有一个通病：当偏移量很大时，会导致MySQL扫描很多不需要的行，然后丢弃。数据模拟不错。如果你明白问题的原理，那就试着解决它。它涉及数据敏感性。让我们模拟这种情况并构建一些数据进行测试。1.Createtwotables:employeetableanddepartmenttable/*Departmenttable,ifitexists,deleteit*/droptableifEXISTSdep;createtabledep(idintunsignedprimarykeyauto_increment,depnomediumintunsignednotnulldefault0,depnamevarchar(20)notnulldefault"",memovarchar(200)notnulldefault"");/*Employeetable,ifitexists,deleteit*/droptableifEXISTSemp;createtableemp(idintunsignedprimarykeyauto_increment,empnomediumintunsignednotnulldefault0,empnamevarchar(20)notnulldefault"",jobvarchar(9)notnulldefault"",mgrmediumintunsignednotnulldefault0,hiredatedatetimenotnull,saldecimal(7,2ndecimnot))notnull,depnomediumintunsignednotnulldefault0);2、创建两个函数：生成随机字符串和随机编号/*产生随机字符串的函数*/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.depnowhereea.id>=(selectidfrommemporderbyidlimit100,1)orderbya.idlimit25;受影响的行：0Time：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.541s2.重新定义起始位置记住上次搜索结果的主键位置，避免使用偏移offset/*记住最后一页最后一条数据的id为100,这里跳过100,从101*/SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrommempaleftjoindepbona.depno=b.depnowherea开始扫描表.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.depnamefromemmpaleftjoindepbona.depno=b.depnowherea.id>4800000orderbya.idlimit25;影响行数：0时间：0.000s这个效率是最好的，不管怎么分页，消耗的时间基本是一样的，因为他执行完条件后，只扫描了25条数据，但是有问题，只适合逐页，这样可以记住上一页的最后一个Id。如果用户跳转到页面，就会出现问题。比如刚看完25页，马上跳到35页，数据就会出错。这种适合的场景类似于百度搜索或者腾讯新闻，滚轮下拉，一直拉动加载。这种延迟加载将确保数据不会被跳过。3.降级策略我在网上看了一个阿里dba同学分享的解决方案：配置limit的offset和获取次数的最大值。如果超过这个最大值，将返回空数据。因为他认为你超过这个值，就不再是寻呼，而是刷数据了。如果确定要查找数据，则应输入适当的条件以缩小范围，而不是一次一页地翻页。这和我同事的想法大致相同：请求时，如果偏移量大于某个值，则先返回4xx错误。总结晚上，我们应用了上面的第三种解决方案来限制偏移量的电流。如果超过某个值，则返回空值。第二天，使用第一种方案和第二种方案进一步优化程序和数据库脚本。按理说，做任何功能都要考虑极端情况，设计能力要覆盖极端边界测试。此外，还应考虑一些限流和降级。比如工具的多线程调用，短时间内8000次调用，可以使用计数服务判断并反馈给用户调用过于频繁，直接砍掉。哎，我大意了，大半夜的，QA同学不讲武功了。但这是一次美好的经历。