同事乱用分页导致MySQL崩溃，我真的醉了...

背景一天晚上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同学。马上给同学打电话，沟通处理。分析其实对于我们的MySQL查询语句来说，整体效率还是不错的。有一些联表查询优化，简单的查询内容也有，关键条件字段和排序字段都有索引。问题是他一页一页地查询。他找到的页面越多，扫描的数据就越多，速度也就越慢。我们查看前几页的时候，发现速度很快，比如limit200，25，瞬间就出来了。但是越往前，速度就越慢，尤其是一百万次之后，卡就不行了，请问这是什么原理。我们先看看翻到后面的查询sql是什么样子的：1select*fromt_namewherec_name1='xxx'orderbyc_name2limit2000000,25;位移过大造成的。比如像上面的limit2000000,25，这相当于从数据库中扫描出2000025条数据，然后丢弃之前的20000000条数据，将剩下的25条数据返回给用户，这显然是不合理的.请参考《高性能MySQL》章节：查询性能优化，其中解释了这个问题：分页操作通常使用limit加offset，以及适当的orderby子句来实现。但这有一个通病：当偏移量很大时，会导致MySQL扫描很多不需要的行，然后丢弃。数据模拟不错。如果你明白问题的原理，那就试着解决它。它涉及数据敏感性。让我们模拟这种情况并构建一些数据进行测试。1.创建两张表：employee表和department表1/*部门表，存在则删除*/2droptableifEXISTSdep;3createtabledep(4idintunsignedprimarykeyauto_increment,5depnomediumintunsignednotnulldefault0,6depnamevarchar(20)notnulldefault"",7memovarchar(200)notnulldefault""8);910/*employee表，如果存在则删除*/11droptableifEXISTSemp;12createtableemp(13idintunsignedprimarykeyauto_increment,14empnomediumintunsignednotnulldefault0,15empnamevarchar(20)notnulldefault"",16jobvarchar(9)notnulldefault”,17mgrmediumintunsignednotnulldefault0,18hiredatedatetimenotnull,19saldecimal(7,2)notnull,20comndecimal(7,2)notnull,21depnomediumintunsignednotnulldefault022);2.创建两个函数：generateRandomstringandrandomnumber1/*Functiontogeneraterandomstring*/2DELIMITER$3dropFUNCTIONifEXISTSrand_string;4创建函数rand_string(nINT)RETURNSVARCHAR(255)5BEGIN6DECLAREchars_strVARCHAR(100)DEFAULT'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';7DECLAREreturn_strVARCHAR(255)DEFAULT'';8DECLAREiINTDEFAULT0;9WHILEi=(selectidfromemporderbyidlimit100,1)5orderbya.idlimit25;67/*sub查询获取offset为4800000的职位id，取25*/8SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname9fromempa在这个位置leftjoindepbona.depno=b.depno10wherea.id>=(selectidfromemporderbyidlimit4800000,1)11orderbya.idlimit25;执行结果的执行效率比之前有明显提升Boost:1[SQL]2SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname3fromempaleftjoindepbona.depno=b.depno4wherea.id>=(selectidfromemporderbyidlimit100,1)5orderbya.idlimit25;6行受影响：07时间：0.106s89[SQL]10SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname11fromempaleftjoindepbona。depno=b.depno12wherea.id>=(selectidfromemporderbyidlimit4800000,1)13orderbya.idlimit25;14受影响的行：015时间：1.541s2.重新定义起始位置记住上次搜索结果的主键位置，避免使用偏移offset1/*记住最后一页最后一条数据的id为100，这里跳过100，开始扫描表from101*/2SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname3fromempaleftjoindepbona.depno=b.depno4其中a.id>100orderbya.idlimit25;56/*记住最后分页最后一条数据的id是4800000，这里跳过4800000，从4800001表开始扫描*/7SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname8fromempaleftjoindepbona.depno=b.depno9wherea.id>480000010orderbya.idlimit25;执行结果1[SQL]2SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname3fromempaleftjoindepbona.depno=b.depno4wherea.id>100orderbya.idlimit25;5受影响的行数：06时间：0.001s78[SQL]9SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname10来自empaleftjoindepbona.depno=b.depno11wherea.id>480000012orderbya.idlimit25;13影响行数：014时间：0.000s这个效率是最好的。不管怎么分页，耗时基本是一样的，因为条件执行后，只扫描了25条数据，但是有这个问题，只适合逐页分页，所以可以记住上一页的lastId。如果用户跳转到页面，就会出现问题。比如刚看完25页，马上跳到35页，数据就会出错。《_MySQL 开发?36 条军规_》有必要看一下。这种适合的场景类似于百度搜索或者腾讯新闻，滚轮下拉，一直拉动加载。这种延迟加载将确保数据不会被跳过。3.降级策略我在网上看了一个阿里dba同学分享的解决方案：配置limit的offset和获取次数的最大值。如果超过这个最大值，将返回空数据。因为他认为你超过这个值，就不再是寻呼，而是刷数据了。如果确定要查找数据，则应输入适当的条件以缩小范围，而不是一次一页地翻页。这和我同事的想法大致相同：请求时，如果偏移量大于某个值，则先返回4xx错误。总结晚上，我们应用了上面的第三种解决方案来限制偏移量的电流。如果超过某个值，则返回空值。第二天，使用第一种方案和第二种方案进一步优化程序和数据库脚本。按理说，做任何功能都要考虑极端情况，设计能力要覆盖极端边界测试。此外，还应考虑一些限流和降级。比如工具的多线程调用，短时间内8000次调用，可以使用计数服务判断并反馈给用户调用过于频繁，直接砍掉。哎，我大意了，大半夜的，QA同学不讲武功了。但这是一次美好的经历。