xxxx18一20岁hd第一次

共事乱用分页 MySQL 卡爆,我确凿醉了...

发布日期:2022-06-18 17:11    点击次数:191

共事乱用分页 MySQL 卡爆,我确凿醉了...

 

配景

一天晚上10点半,放工后知足的坐在在回家的地铁上,心里想着周末的生计奈何安排。

蓦的电话响了起来,一看是咱们的一个开采同学,顿时弥留了起来,本周的版块也曾发布过了,这时候打电话一般来说是线上出问题了。

竟然,调换的情况是线上的一个查询数据的接口被豪恣的失去庄重简易般的调用,这个操作径直导致线上的MySql集群被拖慢了。

好吧,这问题算是严重了,下了地铁匆忙赶到家,开电脑,跟共事把Pinpoint上的慢查询日记捞出来。看到一个很奇怪的查询,如下 

1 POST  domain/v1.0/module/method?order=condition&orderType=desc&offset=1800000&limit=500 

domain、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 0 22 ); 

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 DO  10     SET return_str = CONCAT(return_str,SUBSTRING(chars_str,
特黄 做受又硬又粗又大视频FLOOR(1+RAND()*52),1));  11     SET ii = 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; 

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     REPEAT  10     SET ii = 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); 

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 ii = 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,成熟yⅰn荡的美妇a片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  0 时间: 12.275s 

因为扫描的数据多,是以这个彰着不是一个量级上的耗时。另外,MySQL 系列口试题和谜底一路整理好了,微信搜索Java时候栈,在后台发送:口试,不错在线阅读。

管束决议

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页,数据就会不对。《MySQL 开采 36 条军规》有必要看下。

这种的相宜场景是类似百度搜索或者腾讯新闻那种滚轮往下拉,不停拉取不停加载的情况。这种延长加载会保证数据不会进步着获得。

3、左迁政策

看了网上一个阿里的dba同学共享的决议:配置limit的偏移量和获得数一个最大值,卓著这个最大值,就复返空数据。

因为他以为卓著这个值你也曾不是在分页了,而是在刷数据了,淌若阐发要找数据,应该输入合适要求来削弱范畴,而不是一页一页分页。

这个跟我共事的想法粗略同样:request的时候 淌若offset大于某个数值就先复返一个4xx的无理。

小结

当晚咱们愚弄上述第三个决议,对offset做一下限流,卓著某个值,就复返空值。第二天神用第一种和第二种合营使用的决议对门径和数据库剧本进一步做了优化。

合理来说做任何功能都应该计议极点情况,研究容量都应该涵盖极点领域测试。另外,面貌公众号Java时候栈,在后台回话:口试,不错获得我整理的 Java 系列口试题和谜底,格外无缺。

另外,该有的限流、左迁也应该计议进去。比如用具多线程调用,在短时间频率内8000次调用,不错使用计数做事判断并响应用户调用过于世俗,径直赐与断掉。

哎,豪迈了啊,搞了深宵,QA同学不讲武德。

不外这是很美好的资格了。 

 



栏目分类



Powered by xxxx18一20岁hd第一次 @2013-2022 RSS地图 HTML地图