背景

一天晚上 10 點半，下班后愉快的坐在在回家的地鐵上，心里想著周末的生活怎么安排。

突然電話響了起來，一看是我們的一個運維同學，頓時緊張了起來，本周的版本已經發(fā)布過了，這時候打電話一般來說是線上出問題了。

果然，溝通的情況是線上的一個查詢數(shù)據(jù)的接口被瘋狂的失去理智般的調用，這個操作直接導致線上的 MySQL 集群被拖慢了。

好吧，這問題算是嚴重了，匆匆趕到家后打開電腦，跟同事把 Pinpoint 上的慢查詢日志撈出來。

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

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

這太神奇了，而且我們頁面上的分頁單頁數(shù)量也不是 500，而是 25 條每頁，這個絕對不是人為的在功能頁面上進行一頁一頁的翻頁操作，而是數(shù)據(jù)被刷了（說明下，我們生產環(huán)境數(shù)據(jù)有 1 億+）。

詳細對比日志發(fā)現(xiàn)，很多分頁的時間是重疊的，對方應該是多線程調用。

通過對鑒權的 Token 的分析，基本定位了請求是來自一個叫做 ApiAutotest 的客戶端程序在做這個操作，也定位了生成鑒權 Token 的賬號來自一個 QA 的同學。立馬打電話給同學，進行了溝通和處理。

分析

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

我們在查看前幾頁的時候，發(fā)現(xiàn)速度非常快，比如 limit 200，25，瞬間就出來了。但是越往后，速度就越慢，特別是百萬條之后，卡到不行，那這個是什么原理呢。

1select*fromt_namewherec_name1='xxx'orderbyc_name2limit2000000,25;

這種查詢的慢，其實是因為 limit 后面的偏移量太大導致的。

比如像上面的 limit 2000000，25，這個等同于數(shù)據(jù)庫要掃描出 2000025 條數(shù)據(jù)，然后再丟棄前面的 20000000 條數(shù)據(jù)，返回剩下 25 條數(shù)據(jù)給用戶，這種取法明顯不合理。

大家翻看《高性能 MySQL》第六章：查詢性能優(yōu)化，對這個問題有過說明：分頁操作通常會使用 limit 加上偏移量的辦法實現(xiàn)，同時再加上合適的 order by 子句。

但這會出現(xiàn)一個常見問題：當偏移量非常大的時候，它會導致 MySQL 掃描大量不需要的行然后再拋棄掉。

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

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

/*部門表,存在則進行刪除*/
droptableifEXISTSdep;
createtabledep(
idintunsignedprimarykeyauto_increment,
depnomediumintunsignednotnulldefault0,
depnamevarchar(20)notnulldefault"",
memovarchar(200)notnulldefault""
);

/*員工表,存在則進行刪除*/
droptableifEXISTSemp;
createtableemp(
idintunsignedprimarykeyauto_increment,
empnomediumintunsignednotnulldefault0,
empnamevarchar(20)notnulldefault"",
jobvarchar(9)notnulldefault"",
mgrmediumintunsignednotnulldefault0,
hiredatedatetimenotnull,
saldecimal(7,2)notnull,
comndecimal(7,2)notnull,
depnomediumintunsignednotnulldefault0
);

/*產生隨機字符串的函數(shù)*/
DELIMITER$
dropFUNCTIONifEXISTSrand_string;
CREATEFUNCTIONrand_string(nINT)RETURNSVARCHAR(255)
BEGIN
DECLAREchars_strVARCHAR(100)DEFAULT'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLAREreturn_strVARCHAR(255)DEFAULT'';
DECLAREiINTDEFAULT0;
WHILEiDO
SETreturn_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SETi=i+1;
ENDWHILE;
RETURNreturn_str;
END$
DELIMITER;


/*產生隨機部門編號的函數(shù)*/
DELIMITER$
dropFUNCTIONifEXISTSrand_num;
CREATEFUNCTIONrand_num()RETURNSINT(5)
BEGIN
DECLAREiINTDEFAULT0;
SETi=FLOOR(100+RAND()*10);
RETURNi;
END$
DELIMITER;

/*建立存儲過程：往emp表中插入數(shù)據(jù)*/
DELIMITER$
dropPROCEDUREifEXISTSinsert_emp;
CREATEPROCEDUREinsert_emp(INSTARTINT(10),INmax_numINT(10))
BEGIN
DECLAREiINTDEFAULT0;
/*setautocommit=0把autocommit設置成0，把默認提交關閉*/
SETautocommit=0;
REPEAT
SETi=i+1;
INSERTINTOemp(empno,empname,job,mgr,hiredate,sal,comn,depno)VALUES((START+i),rand_string(6),'SALEMAN',0001,now(),2000,400,rand_num());
UNTILi=max_num
ENDREPEAT;
COMMIT;
END$
DELIMITER;
/*插入500W條數(shù)據(jù)*/
callinsert_emp(0,5000000);

/*建立存儲過程：往dep表中插入數(shù)據(jù)*/
DELIMITER$
dropPROCEDUREifEXISTSinsert_dept;
CREATEPROCEDUREinsert_dept(INSTARTINT(10),INmax_numINT(10))
BEGIN
DECLAREiINTDEFAULT0;
SETautocommit=0;
REPEAT
SETi=i+1;
INSERTINTOdep(depno,depname,memo)VALUES((START+i),rand_string(10),rand_string(8));
UNTILi=max_num
ENDREPEAT;
COMMIT;
END$
DELIMITER;
/*插入120條數(shù)據(jù)*/
callinsert_dept(1,120);

/*建立關鍵字段的索引:排序、條件*/
CREATEINDEXidx_emp_idONemp(id);
CREATEINDEXidx_emp_depnoONemp(depno);
CREATEINDEXidx_dep_depnoONdep(depno);

測試

/*偏移量為100，取25*/
SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname
fromempaleftjoindepbona.depno=b.depnoorderbya.iddesclimit100,25;
/*偏移量為4800000，取25*/
SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname
fromempaleftjoindepbona.depno=b.depnoorderbya.iddesclimit4800000,25;

[SQL]
SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname
fromempaleftjoindepbona.depno=b.depnoorderbya.iddesclimit100,25;
受影響的行:0
時間:0.001s
[SQL]
SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname
fromempaleftjoindepbona.depno=b.depnoorderbya.iddesclimit4800000,25;
受影響的行:0
時間:12.275s

因為掃描的數(shù)據(jù)多，所以這個明顯不是一個量級上的耗時。

解決方案

①使用索引覆蓋+子查詢優(yōu)化

/*子查詢獲取偏移100條的位置的id，在這個位置上往后取25*/
SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname
fromempaleftjoindepbona.depno=b.depno
wherea.id>=(selectidfromemporderbyidlimit100,1)
orderbya.idlimit25;

/*子查詢獲取偏移4800000條的位置的id，在這個位置上往后取25*/
SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname
fromempaleftjoindepbona.depno=b.depno
wherea.id>=(selectidfromemporderbyidlimit4800000,1)
orderbya.idlimit25;

執(zhí)行結果

[SQL]
SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname
fromempaleftjoindepbona.depno=b.depno
wherea.id>=(selectidfromemporderbyidlimit100,1)
orderbya.idlimit25;
受影響的行:0
時間:0.106s

[SQL]
SELECTa.empno,a.empname,a.job,a.sal,b.depno,b.depname
fromempaleftjoindepbona.depno=b.depno
wherea.id>=(selectidfromemporderbyidlimit4800000,1)
orderbya.idlimit25;
受影響的行:0
時間:1.541s

②起始位置重定義

/*記住了上次的分頁的最后一條數(shù)據(jù)的id是100，這邊就直接跳過100，從101開始掃描表*/
SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
fromempaleftjoindepbona.depno=b.depno
wherea.id>100orderbya.idlimit25;

/*記住了上次的分頁的最后一條數(shù)據(jù)的id是4800000，這邊就直接跳過4800000，從4800001開始掃描表*/
SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
fromempaleftjoindepbona.depno=b.depno
wherea.id>4800000
orderbya.idlimit25;

[SQL]
SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
fromempaleftjoindepbona.depno=b.depno
wherea.id>100orderbya.idlimit25;
受影響的行:0
時間:0.001s

[SQL]
SELECTa.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
fromempaleftjoindepbona.depno=b.depno
wherea.id>4800000
orderbya.idlimit25;
受影響的行:0
時間:0.000s

這個效率是最好的，無論怎么分頁，耗時基本都是一致的，因為他執(zhí)行完條件之后，都只掃描了 25 條數(shù)據(jù)。

但是有個問題，只適合一頁一頁的分頁，這樣才能記住前一個分頁的最后 id。如果用戶跳著分頁就有問題了，比如剛剛刷完第 25 頁，馬上跳到 35 頁，數(shù)據(jù)就會不對。

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

③降級策略

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

因為他覺得超過這個值你已經不是在分頁了，而是在刷數(shù)據(jù)了，如果確認要找數(shù)據(jù)，應該輸入合適條件來縮小范圍，而不是一頁一頁分頁。

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

小結

當晚我們應用上述第三個方案，對 offset 做一下限流，超過某個值，就返回空值。第二天使用第一種和第二種配合使用的方案對程序和數(shù)據(jù)庫腳本進一步做了優(yōu)化。合理來說做任何功能都應該考慮極端情況，設計容量都應該涵蓋極端邊界測試。

另外，該有的限流、降級也應該考慮進去。比如工具多線程調用，在短時間頻率內 8000 次調用，可以使用計數(shù)服務判斷并反饋用戶調用過于頻繁，直接給予斷掉。

哎，大意了啊，搞了半夜，QA 同學不講武德。

審核編輯 ：李倩