記一次MySQL分頁導(dǎo)致的線上事故...
大家好，我是二哥的小弟小二呀。
今天給大家分享個生產(chǎn)事故，一個由于MySQL分頁導(dǎo)致的線上事故，事情是這樣的～
背景
一天晚上10點(diǎn)半，下班后愉快的坐在在回家的地鐵上，心里想著周末的生活怎么安排。
突然電話響了起來，一看是我們的一個運(yùn)維同學(xué)，頓時緊張了起來，本周的版本已經(jīng)發(fā)布過了，這時候打電話一般來說是線上出問題了。
果然，溝通的情況是線上的一個查詢數(shù)據(jù)的接口被瘋狂的失去理智般的調(diào)用，這個操作直接導(dǎo)致線上的MySql集群被拖慢了。
好吧，這問題算是嚴(yán)重了，匆匆趕到家后打開電腦，跟同事把Pinpoint上的慢查詢?nèi)罩緭瞥鰜??？吹揭粋€很奇怪的查詢，如下
1?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條每頁，這個絕對不是人為的在功能頁面上進(jìn)行一頁一頁的翻頁操作，而是數(shù)據(jù)被刷了（說明下，我們生產(chǎn)環(huán)境數(shù)據(jù)有1億+）。詳細(xì)對比日志發(fā)現(xiàn)，很多分頁的時間是重疊的，對方應(yīng)該是多線程調(diào)用。
通過對鑒權(quán)的Token的分析，基本定位了請求是來自一個叫做ApiAutotest的客戶端程序在做這個操作，也定位了生成鑒權(quán)Token的賬號來自一個QA的同學(xué)。立馬打電話給同學(xué)，進(jìn)行了溝通和處理。
分析
其實(shí)對于我們的MySQL查詢語句來說，整體效率還是可以的，該有的聯(lián)表查詢優(yōu)化都有，該簡略的查詢內(nèi)容也有，關(guān)鍵條件字段和排序字段該有的索引也都在，問題在于他一頁一頁的分頁去查詢，查到越后面的頁數(shù)，掃描到的數(shù)據(jù)越多，也就越慢。
我們在查看前幾頁的時候，發(fā)現(xiàn)速度非?？?，比如 ?limit 200,25，瞬間就出來了。但是越往后，速度就越慢，特別是百萬條之后，卡到不行，那這個是什么原理呢。先看一下我們翻頁翻到后面時，查詢的sql是怎樣的：
1?select?*?from?t_name?where?c_name1='xxx'?order?by?c_name2?limit?2000000,25;
這種查詢的慢，其實(shí)是因為limit后面的偏移量太大導(dǎo)致的。比如像上面的 limit 2000000,25 ，這個等同于數(shù)據(jù)庫要掃描出 2000025條數(shù)據(jù)，然后再丟棄前面的 20000000條數(shù)據(jù)，返回剩下25條數(shù)據(jù)給用戶，這種取法明顯不合理。
大家翻看《高性能MySQL》第六章：查詢性能優(yōu)化，對這個問題有過說明：
分頁操作通常會使用limit加上偏移量的辦法實(shí)現(xiàn)，同時再加上合適的order by子句。但這會出現(xiàn)一個常見問題：當(dāng)偏移量非常大的時候，它會導(dǎo)致MySQL掃描大量不需要的行然后再拋棄掉。
數(shù)據(jù)模擬
那好，了解了問題的原理，那就要試著解決它了。涉及數(shù)據(jù)敏感性，我們這邊模擬一下這種情況，構(gòu)造一些數(shù)據(jù)來做測試。
1、創(chuàng)建兩個表：員工表和部門表
/*部門表,存在則進(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)建兩個函數(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?????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ù)之后再建索引，會導(dǎo)致建索引耗時長，但是跑數(shù)據(jù)就會快一些。
/*建立關(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
時間:?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
因為掃描的數(shù)據(jù)多，所以這個明顯不是一個量級上的耗時。
解決方案
1、使用索引覆蓋+子查詢優(yōu)化
因為我們有主鍵id，并且在上面建了索引，所以可以先在索引樹中找到開始位置的 id值，再根據(jù)找到的id值查詢行數(shù)據(jù)。
?/*子查詢獲取偏移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;
執(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
?時間:?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??
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
時間:?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?
這個效率是最好的，無論怎么分頁，耗時基本都是一致的，因為他執(zhí)行完條件之后，都只掃描了25條數(shù)據(jù)。
但是有個問題，只適合一頁一頁的分頁，這樣才能記住前一個分頁的最后Id。如果用戶跳著分頁就有問題了，比如剛剛刷完第25頁，馬上跳到35頁，數(shù)據(jù)就會不對。
這種的適合場景是類似百度搜索或者騰訊新聞那種滾輪往下拉，不斷拉取不斷加載的情況。這種延遲加載會保證數(shù)據(jù)不會跳躍著獲取。
3、降級策略
看了網(wǎng)上一個阿里的dba同學(xué)分享的方案：配置limit的偏移量和獲取數(shù)一個最大值，超過這個最大值，就返回空數(shù)據(jù)。
因為他覺得超過這個值你已經(jīng)不是在分頁了，而是在刷數(shù)據(jù)了，如果確認(rèn)要找數(shù)據(jù)，應(yīng)該輸入合適條件來縮小范圍，而不是一頁一頁分頁。
這個跟我同事的想法大致一樣：request的時候 如果offset大于某個數(shù)值就先返回一個4xx的錯誤。
小結(jié)
當(dāng)晚我們應(yīng)用上述第三個方案，對offset做一下限流，超過某個值，就返回空值。第二天使用第一種和第二種配合使用的方案對程序和數(shù)據(jù)庫腳本進(jìn)一步做了優(yōu)化。
合理來說做任何功能都應(yīng)該考慮極端情況，設(shè)計容量都應(yīng)該涵蓋極端邊界測試。
另外，該有的限流、降級也應(yīng)該考慮進(jìn)去。比如工具多線程調(diào)用，在短時間頻率內(nèi)8000次調(diào)用，可以使用計數(shù)服務(wù)判斷并反饋用戶調(diào)用過于頻繁，直接給予斷掉。
二哥的編程知識星球 （點(diǎn)擊了解詳情）正式上線了，來和 170 多名 小伙伴一起打怪升級吧！這是一個 Java 學(xué)習(xí)指南 + 編程實(shí)戰(zhàn)的私密圈子，你可以向二哥提問、幫你制定學(xué)習(xí)計劃、跟著二哥一起做實(shí)戰(zhàn)項目，沖沖沖。
沒有什么使我停留——除了目的，縱然岸旁有玫瑰、有綠蔭、有寧靜的港灣，我是不系之舟。
推薦閱讀：
