作者:翁智华
出处:https://www.cnblogs.com/wzh2010/
背景
1 POST domain/v1.0/module/method?order=condition&orderType=desc&offset=1800000&limit=500
分析
1 select * from t_name where c_name1='xxx' order by c_name2 limit 2000000,25;
大家翻看《高性能MySQL》第六章:查询性能优化,对这个问题有过说明:
数据模拟
那好,了解了问题的原理,那就要试着解决它了。涉及数据敏感性,我们这边模拟一下这种情况,构造一些数据来做测试。
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 0
22 );
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 DO
10 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 BEGIN
23 DECLARE i INT DEFAULT 0;
24 SET i = FLOOR(100+RAND()*10);
25 RETURN i;
26 END $
27 DELIMITER;
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 REPEAT
10 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_num
13 END REPEAT;
14 COMMIT;
15 END $
16 DELIMITER;
17 /*插入500W条数据*/
18 call insert_emp(0,5000000);
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_num
12 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.depname
3 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.depname
6 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 受影响的行: 0
10 时间: 12.275s
因为扫描的数据多,所以这个明显不是一个量级上的耗时。另外,MySQL 系列面试题和答案全部整理好了,微信搜索互联网架构师,在后台发送:面试,可以在线阅读。
解决方案
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.depno
10 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.depname
11 from emp a left join dep b on a.depno = b.depno
12 where a.id >= (select id from emp order by id limit 4800000,1)
13 order by a.id limit 25;
14 受影响的行: 0
15 时间: 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 > 4800000
10 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.depname
10 from emp a left join dep b on a.depno = b.depno
11 where a.id > 4800000
12 order by a.id limit 25;
13 受影响的行: 0
14 时间: 0.000s
这个效率是最好的,无论怎么分页,耗时基本都是一致的,因为他执行完条件之后,都只扫描了25条数据。
但是有个问题,只适合一页一页的分页,这样才能记住前一个分页的最后Id。如果用户跳着分页就有问题了,比如刚刚刷完第25页,马上跳到35页,数据就会不对。
这种的适合场景是类似百度搜索或者腾讯新闻那种滚轮往下拉,不断拉取不断加载的情况。这种延迟加载会保证数据不会跳跃着获取。
3、降级策略
看了网上一个阿里的dba同学分享的方案:配置limit的偏移量和获取数一个最大值,超过这个最大值,就返回空数据。
因为他觉得超过这个值你已经不是在分页了,而是在刷数据了,如果确认要找数据,应该输入合适条件来缩小范围,而不是一页一页分页。
这个跟我同事的想法大致一样:request的时候 如果offset大于某个数值就先返回一个4xx的错误。
小结
当晚我们应用上述第三个方案,对offset做一下限流,超过某个值,就返回空值。第二天使用第一种和第二种配合使用的方案对程序和数据库脚本进一步做了优化。
合理来说做任何功能都应该考虑极端情况,设计容量都应该涵盖极端边界测试。另外,关注公众号互联网架构师,在后台回复:2T,可以获取我整理的 Java 系列面试题和答案,非常齐全。
另外,该有的限流、降级也应该考虑进去。比如工具多线程调用,在短时间频率内8000次调用,可以使用计数服务判断并反馈用户调用过于频繁,直接给予断掉。
哎,大意了啊,搞了半夜,QA同学不讲武德。
不过这是很美好的经历了。