分享:一次深夜优化亿级数据分页的奇妙经历

来都来了 2021-03-11 09:54:33 ⋅ 75 阅读

原文:https://cnblogs.com/wzh2010/p/14316920.html

背景

1月22号晚上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 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;
复制代码

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 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);
复制代码

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_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
复制代码
因为扫描的数据多,所以这个明显不是一个量级上的耗时。 

解决方案

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做一下限流,超过某个值,就返回空值。第二天使用第一种和第二种配合使用的方案对程序和数据库脚本进一步做了优化。
合理来说做任何功能都应该考虑极端情况,设计容量都应该涵盖极端边界测试。
另外,该有的限流、降级也应该考虑进去。比如工具多线程调用,在短时间频率内8000次调用,可以使用计数服务判断并反馈用户调用过于频繁,直接给予断掉。
哎,大意了啊,搞了半夜,QA同学不讲武德。不过这是很美好的经历了。

 

码字不易,欢迎关注,欢迎转载
作者:翁智华
本文采用「CC BY 4.0」知识共享协议进行许可,转载请注明作者及出处。

全部评论: 0

    我有话说:

    为什么说作为程序员分库必要性一定要掌握?

      互联网大厂程序员必须掌握海量数据和高并发问题处理技能,期望进入大厂程序员一定要仔细看这篇! MySQL 分库表是做什么? 相信很多程序员对 MySQL 都比较熟悉了,目前国内

    什么情况下才需要分库表?

    我请教一下,我模拟测试,搞了几张大表都是3千万左右数据,带主键查询秒响应,jmeter插入能够达到每秒6、700条左右,我业务量每秒就是300条左右插入,这个场景下是否不需要考虑分库问题?

    分享单体架构改造成微服务架构实践

    从5个方面设计这次微服务方案,以及经验总结!

    文看懂mycat配置--数据库读写分离分库

    数据库处理本身优化也是非常重要。主从、热备、...

    「轻阅读」亿用户分布式数据存储解决方案

    分布式数据库和分布式存储是分布式系统中难度最大、挑战最大,也是最容易出问题地方。互联网公司只有解决分布式数据存储问题,才能支撑更多亿用户涌入。

    数据量下 MyBatis PageHelper 查询性能问题解决办法

    前因 项目一直使用是PageHelper实现功能,项目前期数据量较少一直没有什么问题。随着业务扩增,数据库扩增PageHelper出现了明显性能问题。 几十万甚至上百万单表数据查询性能缓慢

    搞对数据库连接池,这从100优化到3ms!阿里架构师都说好

    我在研究HikariCP(一个数据库连接池)时无意间在HikariCPGithub wiki上看到了篇文章(即前面给出链接),这篇文章有力地消除了我一直以来疑虑,看完之后感觉神清气爽。故在此

    Java Web实战篇-轻松提高千万数据库查询效率

    通过优化数据库设计、java后台和数据库优化达到提高千万数据查询效率。

    微信小程序电商实战-首(上)

    篇:微信小程序电商实战-入门篇 嗨,大家好!经过近两周精心准备终于开始微信小程序电商实战之路喽。那么最终会做成什么样呢?好了,不啰嗦了 我们先看首长什么样吧!   首效果图

    推荐款前端数据源管理工具 algeb

    ALGEB 简介 这是一个比较抽象库,开始可能比较难理解。我写它初衷,是创建可响应数据请求管理。在传统数据请求中,我们只是把携带ajax代码堆函数放在一起,这样就可以调用接口。但是这种

    IntelliJ IDEA 开启很慢,运行不流畅,大项目卡顿?招配置解决!

    来源:Java面试题精选 、前言 IDEA默认启动配置主要考虑低配置用户,参数不高(默认最低128m,最高512m),导致启动慢,然后运行也不流畅,这里我们需要优化下启动和运行配置;但是在工作中

    阿里技术:聊聊从单机至亿流量大型网站系统架构演进过程

    网站初期,我们经常会在单机上跑我们所有程序和软件。此时我们使用一个容器,如tomcat、jetty、jboos,然后直接使用JSP/servlet技术......

    数据结构

    结构,简单理解就是关系。严格点说,结构是指各个组成部分相互搭配和排列方式。在现实世界中,不同数据元素之间不是独立,而是存在特定关系,我们将这些关系成为结构。 数据结构:是相互之间存在

    40亿条/秒!Flink流批一体在阿里双11首落地背后

    地达到了每秒 40 亿记录,数据体量也达到了...

    挖那些让公司网站瘫痪SQL“终结者”

    条慢查询会造成什么后果?之前我一直觉得不就是返回数据会慢一些么,用户体验变差? 其实远远不止,我经历过几线上事故,有就是由条 SQL 慢查询导致。 那条 SQL 查询耗时达到 2

    分库表这样玩,可以永不迁移数据、避免热点

    中大型项目中,一旦遇到数据量比较大,小伙伴应该都知道就应该对数据进行拆了。有垂直和水平两种。