一个由于MySQL分页导致的线上事故
今天给大家分享个生产事故，一个由于 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 = (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.depnamefrom emp a left join dep b on a.depno = b.depnowhere 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.depnamefrom emp a left join dep b on a.depno = b.depnowhere a.id > 4800000order by a.id limit 25;受影响的行: 0时间: 0.000s
这个效率是最好的，无论怎么分页，耗时基本都是一致的，因为他执行完条件之后，都只扫描了 25 条数据。
但是有个问题，只适合一页一页的分页，这样才能记住前一个分页的最后 id。如果用户跳着分页就有问题了，比如刚刚刷完第 25 页，马上跳到 35 页，数据就会不对。
这种的适合场景是类似百度搜索或者腾讯新闻那种滚轮往下拉，不断拉取不断加载的情况。这种延迟加载会保证数据不会跳跃着获取。
③降级策略
看了网上一个阿里的 dba 同学分享的方案：配置 limit 的偏移量和获取数一个最大值，超过这个最大值，就返回空数据。
因为他觉得超过这个值你已经不是在分页了，而是在刷数据了，如果确认要找数据，应该输入合适条件来缩小范围，而不是一页一页分页。
这个跟我同事的想法大致一样：request 的时候如果 offset 大于某个数值就先返回一个 4xx 的错误。
小结
当晚我们应用上述第三个方案，对 offset 做一下限流，超过某个值，就返回空值。第二天使用第一种和第二种配合使用的方案对程序和数据库脚本进一步做了优化。合理来说做任何功能都应该考虑极端情况，设计容量都应该涵盖极端边界测试。
另外，该有的限流、降级也应该考虑进去。比如工具多线程调用，在短时间频率内 8000 次调用，可以使用计数服务判断并反馈用户调用过于频繁，直接给予断掉。
哎，大意了啊，搞了半夜，qa 同学不讲武德。
SICK一级代理北京康瑞明科技有限公司张淑芳
智能家电产业将迎来快速发展阶段
智能称重物联网平台解决方案
英国脱欧对数字货币有没有造成影响
智能硬件泡沫：巨头进场才能彻底催熟产业
一个由于MySQL分页导致的线上事故
大容量电气双层电容器DMF系列的用途
import模块的导入方法
电线电缆在运行中出现发热现象的原因是什么
5G和TSN的网络融合存在的三大难点
苹果第二颗自研处理器曝光，专为桌面设计
关于采样率，您真正需要了解的内容
锂电池保护板同口与分口的区别 锂电池保护板电路图原理
iPad Pro不如iPad Air4 成为过气产品了吗？
RFID智能文件流转柜的功能及参数
Python学习要点：自定义序列实现切片功能
独立服务器和vps之间的区别是什么
LED市场争夺激烈，Micro LED时代“忽近忽远”
高通对抗Intel战略：砸24亿收购芯片制造商CSR
使用烧结铜的功率元件封装技术 可靠性提高10倍
背景
一天晚上 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 = (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.depnamefrom emp a left join dep b on a.depno = b.depnowhere 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.depnamefrom emp a left join dep b on a.depno = b.depnowhere a.id > 4800000order by a.id limit 25;受影响的行: 0时间: 0.000s
这个效率是最好的，无论怎么分页，耗时基本都是一致的，因为他执行完条件之后，都只扫描了 25 条数据。
但是有个问题，只适合一页一页的分页，这样才能记住前一个分页的最后 id。如果用户跳着分页就有问题了，比如刚刚刷完第 25 页，马上跳到 35 页，数据就会不对。
这种的适合场景是类似百度搜索或者腾讯新闻那种滚轮往下拉，不断拉取不断加载的情况。这种延迟加载会保证数据不会跳跃着获取。
③降级策略
看了网上一个阿里的 dba 同学分享的方案：配置 limit 的偏移量和获取数一个最大值，超过这个最大值，就返回空数据。
因为他觉得超过这个值你已经不是在分页了，而是在刷数据了，如果确认要找数据，应该输入合适条件来缩小范围，而不是一页一页分页。
这个跟我同事的想法大致一样：request 的时候如果 offset 大于某个数值就先返回一个 4xx 的错误。
小结
当晚我们应用上述第三个方案，对 offset 做一下限流，超过某个值，就返回空值。第二天使用第一种和第二种配合使用的方案对程序和数据库脚本进一步做了优化。合理来说做任何功能都应该考虑极端情况，设计容量都应该涵盖极端边界测试。
另外，该有的限流、降级也应该考虑进去。比如工具多线程调用，在短时间频率内 8000 次调用，可以使用计数服务判断并反馈用户调用过于频繁，直接给予断掉。
哎，大意了啊，搞了半夜，qa 同学不讲武德。
SICK一级代理北京康瑞明科技有限公司张淑芳
智能家电产业将迎来快速发展阶段
智能称重物联网平台解决方案
英国脱欧对数字货币有没有造成影响
智能硬件泡沫：巨头进场才能彻底催熟产业
一个由于MySQL分页导致的线上事故
大容量电气双层电容器DMF系列的用途
import模块的导入方法
电线电缆在运行中出现发热现象的原因是什么
5G和TSN的网络融合存在的三大难点
苹果第二颗自研处理器曝光，专为桌面设计
关于采样率，您真正需要了解的内容
锂电池保护板同口与分口的区别 锂电池保护板电路图原理
iPad Pro不如iPad Air4 成为过气产品了吗？
RFID智能文件流转柜的功能及参数
Python学习要点：自定义序列实现切片功能
独立服务器和vps之间的区别是什么
LED市场争夺激烈，Micro LED时代“忽近忽远”
高通对抗Intel战略：砸24亿收购芯片制造商CSR
使用烧结铜的功率元件封装技术 可靠性提高10倍