关于mysql:一次线上MySQL分页事故搞了半夜

2次阅读

共计 6784 个字符，预计需要花费 17 分钟才能阅读完成。

明天给大家分享个生产事变，一个因为 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 扫描大量不须要的行而后再摈弃掉。

数据模仿

那好，理解了问题的原理，那就要试着解决它了。波及数据敏感性，咱们这边模仿一下这种状况，结构一些数据来做测试。

①创立两个表：员工表和部门表

/部门表, 存在则进行删除 /
drop table if EXISTS dep;
create table dep(
    id int unsigned primary key auto_increment,
    depno mediumint unsigned not null default 0,
    depname varchar(20) not null default “”,
    memo varchar(200) not null default “”
);

/员工表, 存在则进行删除/
drop table if EXISTS emp;
create table emp(
    id int unsigned primary key auto_increment,
    empno mediumint unsigned not null default 0,
    empname varchar(20) not null default “”,
    job varchar(9) not null default “”,
    mgr mediumint unsigned not null default 0,
    hiredate datetime not null,
    sal decimal(7,2) not null,
    comn decimal(7,2) not null,
    depno mediumint unsigned not null default 0
);

②创立两个函数：生成随机字符串和随机编号

/ 产生随机字符串的函数/
DELIMITER $
drop FUNCTION if EXISTS rand_string;
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT ‘abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’;
    DECLARE return_str VARCHAR(255) DEFAULT ”;
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
    SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    SET i = i+1;
    END WHILE;
    RETURN return_str;
END $
DELIMITER;

/产生随机部门编号的函数/
DELIMITER $
drop FUNCTION if EXISTS rand_num;
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100+RAND()*10);
    RETURN i;
END $
DELIMITER;

③编写存储过程，模仿 500W 的员工数据

/建设存储过程：往 emp 表中插入数据 /
DELIMITER $
drop PROCEDURE if EXISTS insert_emp;
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
     DECLARE i INT DEFAULT 0;
     /set autocommit =0 把 autocommit 设置成 0，把默认提交敞开/
     SET autocommit = 0;
     REPEAT
     SET i = i + 1;
     INSERT INTO emp(empno,empname,job,mgr,hiredate,sal,comn,depno) VALUES ((START+i),rand_string(6),’SALEMAN’,0001,now(),2000,400,rand_num());
     UNTIL i = max_num
     END REPEAT;
     COMMIT;
END $
DELIMITER;
/ 插入 500W 条数据/
call insert_emp(0,5000000);

④编写存储过程，模仿 120 的部门数据

/建设存储过程：往 dep 表中插入数据 /
DELIMITER $
drop PROCEDURE if EXISTS insert_dept;
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
     DECLARE i INT DEFAULT 0;
     SET autocommit = 0;
     REPEAT
     SET i = i+1;
     INSERT  INTO dep(depno,depname,memo) VALUES((START+i),rand_string(10),rand_string(8));
     UNTIL i = max_num
     END REPEAT;
     COMMIT;
END $
DELIMITER;
/ 插入 120 条数据/
call insert_dept(1,120);

⑤建设关键字段的索引，这边是跑完数据之后再建索引，会导致建索引耗时长，然而跑数据就会快一些。

/建设关键字段的索引: 排序、条件/
CREATE INDEX idx_emp_id ON emp(id);
CREATE INDEX idx_emp_depno ON emp(depno);
CREATE INDEX idx_dep_depno ON dep(depno);

测试

测试数据：

/偏移量为 100，取 25/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;
/偏移量为 4800000，取 25/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;

执行后果：

[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;
受影响的行: 0
工夫: 0.001s
[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;
受影响的行: 0
工夫: 12.275s

因为扫描的数据多，所以这个显著不是一个量级上的耗时。

解决方案

①应用索引笼罩 + 子查问优化

因为咱们有主键 id，并且在下面建了索引，所以能够先在索引树中找到开始地位的 id 值，再依据找到的 id 值查问行数据。

/子查问获取偏移 100 条的地位的 id，在这个地位上往后取 25/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 100,1)
order by a.id limit 25;

/子查问获取偏移 4800000 条的地位的 id，在这个地位上往后取 25/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 4800000,1)
order by a.id limit 25;

执行后果

执行效率相比之前有大幅的晋升：

[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 100,1)
order by a.id limit 25;
受影响的行: 0
工夫: 0.106s

[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 4800000,1)
order by a.id limit 25;
受影响的行: 0
工夫: 1.541s

②起始地位重定义

记住上次查找后果的主键地位，防止应用偏移量 offset：

/记住了上次的分页的最初一条数据的 id 是 100，这边就间接跳过 100，从 101 开始扫描表/
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;

/记住了上次的分页的最初一条数据的 id 是 4800000，这边就间接跳过 4800000，从 4800001 开始扫描表/
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 4800000
order by a.id limit 25;

执行后果：

[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;
受影响的行: 0
工夫: 0.001s

[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 4800000
order by a.id limit 25;
受影响的行: 0
工夫: 0.000s

这个效率是最好的，无论怎么分页，耗时根本都是统一的，因为他执行完条件之后，都只扫描了 25 条数据。

然而有个问题，只适宜一页一页的分页，这样能力记住前一个分页的最初 id。如果用户跳着分页就有问题了，比方刚刚刷完第 25 页，马上跳到 35 页，数据就会不对。

这种的适宜场景是相似百度搜寻或者腾讯新闻那种滚轮往下拉，一直拉取一直加载的状况。这种提早加载会保证数据不会跳跃着获取。

③降级策略

看了网上一个阿里的 DBA 同学分享的计划：配置 limit 的偏移量和获取数一个最大值，超过这个最大值，就返回空数据。

因为他感觉超过这个值你曾经不是在分页了，而是在刷数据了，如果确认要找数据，应该输出适合条件来放大范畴，而不是一页一页分页。

这个跟我共事的想法大抵一样：request 的时候如果 offset 大于某个数值就先返回一个 4xx 的谬误。

小结

当晚咱们利用上述第三个计划，对 offset 做一下限流，超过某个值，就返回空值。第二天应用第一种和第二种配合应用的计划对程序和数据库脚本进一步做了优化。正当来说做任何性能都应该思考极其状况，设计容量都应该涵盖极其边界测试。

另外，该有的限流、降级也应该思考进去。比方工具多线程调用，在短时间频率内 8000 次调用，能够应用计数服务判断并反馈用户调用过于频繁，间接给予断掉。

哎，粗心了啊，搞了中午，QA 同学不讲武德。

文章起源：【JAVA 日知录】

正文完

mysql

发表至： mysql

2022-05-25

0

关于mysql:MSql忘记密码后如何修改密码

关于mysql:mysql数据库中常用的概念

关于mysql:Mysql中将时间戳转为Date

关于mysql:MySqlint10-与-int-unsigned-之前的区别

关于java:字节二面什么是伪共享

关于mysql:一次线上MySQL分页事故搞了半夜

站内搜索