作者：翁智华

出处：https://www.cnblogs.com/wzh2010/

背景

一天晚上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,r