下班后，愉快的坐在在回家的地鐵上缝驳，心里想著周末的生活怎么安排。

突然電話響了起來，一看是我們的一個(gè)開發(fā)同學(xué)用狱，頓時(shí)緊張了起來运怖，本周的版本已經(jīng)發(fā)布過了，這時(shí)候打電話一般來說是線上出問題了夏伊。

果然摇展，溝通的情況是線上的一個(gè)查詢數(shù)據(jù)的接口被瘋狂的失去理智般的調(diào)用，這個(gè)操作直接導(dǎo)致線上的MySql集群被拖慢了溺忧。

好吧咏连，這問題算是嚴(yán)重了，下了地鐵匆匆趕到家鲁森，開電腦祟滴，跟同事把Pinpoint上的慢查詢?nèi)罩緭瞥鰜怼刀森？吹揭粋€(gè)很奇怪的查詢踱启，如下

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

domain、module 和 method 都是化名研底，代表接口的域埠偿、模塊和實(shí)例方法名，后面的offset和limit代表分頁操作的偏移量和每頁的數(shù)量榜晦，也就是說該同學(xué)是在 翻第（1800000/500+1=3601）頁冠蒋。初步撈了一下日志，發(fā)現(xiàn) 有8000多次這樣調(diào)用乾胶。

這太神奇了抖剿，而且我們頁面上的分頁單頁數(shù)量也不是500，而是 25條每頁识窿，這個(gè)絕對不是人為的在功能頁面上進(jìn)行一頁一頁的翻頁操作斩郎，而是數(shù)據(jù)被刷了（說明下，我們生產(chǎn)環(huán)境數(shù)據(jù)有1億+）喻频。詳細(xì)對比日志發(fā)現(xiàn)缩宜，很多分頁的時(shí)間是重疊的，對方應(yīng)該是多線程調(diào)用甥温。

通過對鑒權(quán)的Token的分析锻煌，基本定位了請求是來自一個(gè)叫做ApiAutotest的客戶端程序在做這個(gè)操作，也定位了生成鑒權(quán)Token的賬號來自一個(gè)QA的同學(xué)姻蚓。立馬打電話給同學(xué)宋梧，進(jìn)行了溝通和處理。

分析

其實(shí)對于我們的MySQL查詢語句來說狰挡，整體效率還是可以的捂龄，該有的聯(lián)表查詢優(yōu)化都有释涛，該簡略的查詢內(nèi)容也有，關(guān)鍵條件字段和排序字段該有的索引也都在跺讯，問題在于他一頁一頁的分頁去查詢枢贿，查到越后面的頁數(shù)，掃描到的數(shù)據(jù)越多刀脏，也就越慢局荚。

我們在查看前幾頁的時(shí)候，發(fā)現(xiàn)速度非秤郏快耀态，比如 limit 200,25，瞬間就出來了暂雹。但是越往后首装，速度就越慢，特別是百萬條之后杭跪，卡到不行仙逻，那這個(gè)是什么原理呢。先看一下我們翻頁翻到后面時(shí)涧尿，查詢的sql是怎樣的：

select * from t_name where c_name1='xxx' order by c_name2 limit 2000000,25;

這種查詢的慢系奉，其實(shí)是因?yàn)閘imit后面的偏移量太大導(dǎo)致的。比如像上面的 limit 2000000,25 姑廉，這個(gè)等同于數(shù)據(jù)庫要掃描出 2000025條數(shù)據(jù)缺亮，然后再丟棄前面的 20000000條數(shù)據(jù)，返回剩下25條數(shù)據(jù)給用戶桥言，這種取法明顯不合理萌踱。

大家翻看《高性能MySQL》第六章：查詢性能優(yōu)化，對這個(gè)問題有過說明：

分頁操作通常會(huì)使用limit加上偏移量的辦法實(shí)現(xiàn)号阿，同時(shí)再加上合適的order by子句并鸵。但這會(huì)出現(xiàn)一個(gè)常見問題：當(dāng)偏移量非常大的時(shí)候，它會(huì)導(dǎo)致MySQL掃描大量不需要的行然后再拋棄掉扔涧。

推薦：idea

數(shù)據(jù)模擬

那好能真，了解了問題的原理，那就要試著解決它了扰柠。涉及數(shù)據(jù)敏感性，我們這邊模擬一下這種情況疼约，構(gòu)造一些數(shù)據(jù)來做測試卤档。

1、創(chuàng)建兩個(gè)表：員工表和部門表

/*部門表,存在則進(jìn)行刪除 */
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 ""
);

/*員工表,存在則進(jìn)行刪除*/
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
);

2程剥、創(chuàng)建兩個(gè)函數(shù)：生成隨機(jī)字符串和隨機(jī)編號

/* 產(chǎn)生隨機(jī)字符串的函數(shù)*/
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;

/*產(chǎn)生隨機(jī)部門編號的函數(shù)*/
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;

3劝枣、編寫存儲過程汤踏，模擬500W的員工數(shù)據(jù)

/*建立存儲過程：往emp表中插入數(shù)據(jù)*/
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 把a(bǔ)utocommit設(shè)置成0，把默認(rèn)提交關(guān)閉*/
    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條數(shù)據(jù)*/
call insert_emp(0,5000000);

4舔腾、編寫存儲過程溪胶，模擬120的部門數(shù)據(jù)

/*建立存儲過程：往dep表中插入數(shù)據(jù)*/
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條數(shù)據(jù)*/
call insert_dept(1,120);

5、建立關(guān)鍵字段的索引稳诚，這邊是跑完數(shù)據(jù)之后再建索引哗脖，會(huì)導(dǎo)致建索引耗時(shí)長，但是跑數(shù)據(jù)就會(huì)快一些扳还。

/*建立關(guān)鍵字段的索引:排序才避、條件*/
CREATE INDEX idx_emp_id ON emp(id);
CREATE INDEX idx_emp_depno ON emp(depno);
CREATE INDEX idx_dep_depno ON dep(depno); 

測試

測試數(shù)據(jù)

/*偏移量為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; 

執(zhí)行結(jié)果

[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
時(shí)間: 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
時(shí)間: 12.275s

因?yàn)閽呙璧臄?shù)據(jù)多桑逝，所以這個(gè)明顯不是一個(gè)量級上的耗時(shí)。

解決方案

1俏让、使用索引覆蓋+子查詢優(yōu)化

因?yàn)槲覀冇兄麈Iid楞遏，并且在上面建了索引，所以可以先在索引樹中找到開始位置的 id值首昔，再根據(jù)找到的id值查詢行數(shù)據(jù)寡喝。

/*子查詢獲取偏移100條的位置的id，在這個(gè)位置上往后取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沙廉，在這個(gè)位置上往后取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;

執(zhí)行結(jié)果

執(zhí)行效率相比之前有大幅的提升：

[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
時(shí)間: 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
時(shí)間: 1.541s

2拘荡、起始位置重定義

記住上次查找結(jié)果的主鍵位置，避免使用偏移量 offset

/*記住了上次的分頁的最后一條數(shù)據(jù)的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;

/*記住了上次的分頁的最后一條數(shù)據(jù)的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;

執(zhí)行結(jié)果

[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
時(shí)間: 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
時(shí)間: 0.000s

這個(gè)效率是最好的蟋定，無論怎么分頁，耗時(shí)基本都是一致的草添，因?yàn)樗麍?zhí)行完條件之后驶兜，都只掃描了25條數(shù)據(jù)。

但是有個(gè)問題远寸，只適合一頁一頁的分頁抄淑，這樣才能記住前一個(gè)分頁的最后Id。如果用戶跳著分頁就有問題了驰后，比如剛剛刷完第25頁肆资，馬上跳到35頁，數(shù)據(jù)就會(huì)不對灶芝。

這種的適合場景是類似百度搜索或者騰訊新聞那種滾輪往下拉郑原，不斷拉取不斷加載的情況唉韭。這種延遲加載會(huì)保證數(shù)據(jù)不會(huì)跳躍著獲取。

3犯犁、降級策略

看了網(wǎng)上一個(gè)阿里的dba同學(xué)分享的方案：配置limit的偏移量和獲取數(shù)一個(gè)最大值属愤，超過這個(gè)最大值，就返回空數(shù)據(jù)酸役。

因?yàn)樗X得超過這個(gè)值你已經(jīng)不是在分頁了住诸，而是在刷數(shù)據(jù)了，如果確認(rèn)要找數(shù)據(jù)簇捍，應(yīng)該輸入合適條件來縮小范圍只壳，而不是一頁一頁分頁。

這個(gè)跟我同事的想法大致一樣：request的時(shí)候 如果offset大于某個(gè)數(shù)值就先返回一個(gè)4xx的錯(cuò)誤暑塑。

小結(jié)

當(dāng)晚我們應(yīng)用上述第三個(gè)方案吼句，對offset做一下限流，超過某個(gè)值事格，就返回空值惕艳。第二天使用第一種和第二種配合使用的方案對程序和數(shù)據(jù)庫腳本進(jìn)一步做了優(yōu)化。

合理來說做任何功能都應(yīng)該考慮極端情況驹愚，設(shè)計(jì)容量都應(yīng)該涵蓋極端邊界測試远搪。

另外，該有的限流逢捺、降級也應(yīng)該考慮進(jìn)去谁鳍。比如工具多線程調(diào)用，在短時(shí)間頻率內(nèi)8000次調(diào)用劫瞳，可以使用計(jì)數(shù)服務(wù)判斷并反饋用戶調(diào)用過于頻繁倘潜，直接給予斷掉。

哎志于，大意了啊涮因，搞了半夜，QA同學(xué)不講武德伺绽。不過這是很美好的經(jīng)歷了养泡。