背景
一天早晨10点半，上班后欢快的坐在在回家的地铁上，心里想着周末的生存怎么安顿。
忽然电话响了起来，一看是咱们的一个开发同学，登时缓和了起来，本周的版本曾经公布过了，这时候打电话一般来说是线上出问题了。
果然，沟通的状况是线上的一个查问数据的接口被疯狂的失去理智般的调用，这个操作间接导致线上的MySql集群被拖慢了。
好吧，这问题算是重大了，下了地铁匆匆赶到家，开电脑，跟共事把Pinpoint上的慢查问日志捞进去。看到一个很奇怪的查问，如下
1 POST domain/v1.0/module/method?order=condition&orderType=desc&offset=1800000&limit=500domain、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_name where c_name1='xxx' order by c_name2 limit 2000000,25;这种查问的慢，其实是因为limit前面的偏移量太大导致的。比方像下面的 limit 2000000,25 ，这个等同于数据库要扫描出 2000025条数据，而后再抛弃后面的 20000000条数据，返回剩下25条数据给用户，这种取法显著不合理。
大家翻看《高性能MySQL》第六章：查问性能优化，对这个问题有过阐明：
分页操作通常会应用limit加上偏移量的方法实现，同时再加上适合的order by子句。但这会呈现一个常见问题：当偏移量十分大的时候，它会导致MySQL扫描大量不须要的行而后再摈弃掉。
数据模仿
那好，理解了问题的原理，那就要试着解决它了。波及数据敏感性，咱们这边模仿一下这种状况，结构一些数据来做测试。
1、创立两个表：员工表和部门表
1 /*部门表,存在则进行删除 */ 2 drop table if EXISTS dep; 3 create table dep( 4 id int unsigned primary key auto_increment, 5 depno mediumint unsigned not null default 0, 6 depname varchar(20) not null default "", 7 memo varchar(200) not null default "" 8 ); 9 10 /*员工表,存在则进行删除*/11 drop table if EXISTS emp;12 create table emp(13 id int unsigned primary key auto_increment,14 empno mediumint unsigned not null default 0,15 empname varchar(20) not null default "",16 job varchar(9) not null default "",17 mgr mediumint unsigned not null default 0,18 hiredate datetime not null,19 sal decimal(7,2) not null,20 comn decimal(7,2) not null,21 depno mediumint unsigned not null default 022 );2、创立两个函数：生成随机字符串和随机编号
1 /* 产生随机字符串的函数*/ 2 DELIMITER $ 3 drop FUNCTION if EXISTS rand_string; 4 CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) 5 BEGIN 6 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 7 DECLARE return_str VARCHAR(255) DEFAULT ''; 8 DECLARE i INT DEFAULT 0; 9 WHILE i < n DO10 SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));11 SET i = i+1;12 END WHILE;13 RETURN return_str;14 END $15 DELIMITER;16 17 18 /*产生随机部门编号的函数*/19 DELIMITER $ 20 drop FUNCTION if EXISTS rand_num;21 CREATE FUNCTION rand_num() RETURNS INT(5)22 BEGIN23 DECLARE i INT DEFAULT 0;24 SET i = FLOOR(100+RAND()*10);25 RETURN i;26 END $27 DELIMITER;3、编写存储过程，模仿500W的员工数据
1 /*建设存储过程：往emp表中插入数据*/ 2 DELIMITER $ 3 drop PROCEDURE if EXISTS insert_emp; 4 CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10)) 5 BEGIN 6 DECLARE i INT DEFAULT 0; 7 /*set autocommit =0 把autocommit设置成0，把默认提交敞开*/ 8 SET autocommit = 0; 9 REPEAT10 SET i = i + 1;11 INSERT INTO emp(empno,empname,job,mgr,hiredate,sal,comn,depno) VALUES ((START+i),rand_string(6),'SALEMAN',0001,now(),2000,400,rand_num());12 UNTIL i = max_num13 END REPEAT;14 COMMIT;15 END $16 DELIMITER;17 /*插入500W条数据*/18 call insert_emp(0,5000000);4、编写存储过程，模仿120的部门数据
1 /*建设存储过程：往dep表中插入数据*/ 2 DELIMITER $ 3 drop PROCEDURE if EXISTS insert_dept; 4 CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10)) 5 BEGIN 6 DECLARE i INT DEFAULT 0; 7 SET autocommit = 0; 8 REPEAT 9 SET i = i+1;10 INSERT INTO dep( depno,depname,memo) VALUES((START+i),rand_string(10),rand_string(8));11 UNTIL i = max_num12 END REPEAT;13 COMMIT;14 END $15 DELIMITER;16 /*插入120条数据*/17 call insert_dept(1,120);5、建设关键字段的索引，这边是跑完数据之后再建索引，会导致建索引耗时长，然而跑数据就会快一些。
1 /*建设关键字段的索引:排序、条件*/2 CREATE INDEX idx_emp_id ON emp(id);3 CREATE INDEX idx_emp_depno ON emp(depno);4 CREATE INDEX idx_dep_depno ON dep(depno); 测试
测试数据
1 /*偏移量为100，取25*/2 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname3 from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;4 /*偏移量为4800000，取25*/5 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname6 from emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25; 执行后果
1 [SQL] 2 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname 3 from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25; 4 受影响的行: 0 5 工夫: 0.001s 6 [SQL] 7 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname 8 from emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25; 9 受影响的行: 010 工夫: 12.275s因为扫描的数据多，所以这个显著不是一个量级上的耗时。
解决方案
1、应用索引笼罩+子查问优化
因为咱们有主键id，并且在下面建了索引，所以能够先在索引树中找到开始地位的 id值，再依据找到的id值查问行数据。
1 /*子查问获取偏移100条的地位的id，在这个地位上往后取25*/ 2 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname 3 from emp a left join dep b on a.depno = b.depno 4 where a.id >= (select id from emp order by id limit 100,1) 5 order by a.id limit 25; 6 7 /*子查问获取偏移4800000条的地位的id，在这个地位上往后取25*/ 8 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname 9 from emp a left join dep b on a.depno = b.depno10 where a.id >= (select id from emp order by id limit 4800000,1)11 order by a.id limit 25;执行后果
执行效率相比之前有大幅的晋升：
1 [SQL] 2 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname 3 from emp a left join dep b on a.depno = b.depno 4 where a.id >= (select id from emp order by id limit 100,1) 5 order by a.id limit 25; 6 受影响的行: 0 7 工夫: 0.106s 8 9 [SQL]10 SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname11 from emp a left join dep b on a.depno = b.depno12 where a.id >= (select id from emp order by id limit 4800000,1)13 order by a.id limit 25;14 受影响的行: 015 工夫: 1.541s 2、起始地位重定义
记住上次查找后果的主键地位，防止应用偏移量 offset
1 /*记住了上次的分页的最初一条数据的id是100，这边就间接跳过100，从101开始扫描表*/ 2 SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname 3 from emp a left join dep b on a.depno = b.depno 4 where a.id > 100 order by a.id limit 25; 5 6 /*记住了上次的分页的最初一条数据的id是4800000，这边就间接跳过4800000，从4800001开始扫描表*/ 7 SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname 8 from emp a left join dep b on a.depno = b.depno 9 where a.id > 480000010 order by a.id limit 25;执行后果
1 [SQL] 2 SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname 3 from emp a left join dep b on a.depno = b.depno 4 where a.id > 100 order by a.id limit 25; 5 受影响的行: 0 6 工夫: 0.001s 7 8 [SQL] 9 SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname10 from emp a left join dep b on a.depno = b.depno11 where a.id > 480000012 order by a.id limit 25;13 受影响的行: 014 工夫: 0.000s 这个效率是最好的，无论怎么分页，耗时根本都是统一的，因为他执行完条件之后，都只扫描了25条数据。
然而有个问题，只适宜一页一页的分页，这样能力记住前一个分页的最初Id。如果用户跳着分页就有问题了，比方刚刚刷完第25页，马上跳到35页，数据就会不对。《_MySQL 开发 36 条军规_》有必要看下。
这种的适宜场景是相似百度搜寻或者腾讯新闻那种滚轮往下拉，一直拉取一直加载的状况。这种提早加载会保证数据不会跳跃着获取。
3、降级策略
看了网上一个阿里的dba同学分享的计划：配置limit的偏移量和获取数一个最大值，超过这个最大值，就返回空数据。
因为他感觉超过这个值你曾经不是在分页了，而是在刷数据了，如果确认要找数据，应该输出适合条件来放大范畴，而不是一页一页分页。
这个跟我共事的想法大抵一样：request的时候 如果offset大于某个数值就先返回一个4xx的谬误。
小结
当晚咱们利用上述第三个计划，对offset做一下限流，超过某个值，就返回空值。第二天应用第一种和第二种配合应用的计划对程序和数据库脚本进一步做了优化。
正当来说做任何性能都应该思考极其状况，设计容量都应该涵盖极其边界测试。
另外，该有的限流、降级也应该思考进去。比方工具多线程调用，在短时间频率内8000次调用，能够应用计数服务判断并反馈用户调用过于频繁，间接给予断掉。
哎，粗心了啊，搞了中午，QA同学不讲武德。
不过这是很美妙的经验了。