今天給大家分享個(gè)生產(chǎn)事故，一個(gè)由于 MySQL 分頁導(dǎo)致的線上事故，事情是這樣的～
背景
一天晚上 10 點(diǎn)半，下班后愉快的坐在在回家的地鐵上谦纱，心里想著周末的生活怎么安排。
突然電話響了起來君编，一看是我們的一個(gè)運(yùn)維同學(xué)，頓時(shí)緊張了起來川慌，本周的版本已經(jīng)發(fā)布過了吃嘿，這時(shí)候打電話一般來說是線上出問題了。
果然梦重，溝通的情況是線上的一個(gè)查詢數(shù)據(jù)的接口被瘋狂的失去理智般的調(diào)用兑燥，這個(gè)操作直接導(dǎo)致線上的 MySQL 集群被拖慢了。
好吧琴拧，這問題算是嚴(yán)重了降瞳，匆匆趕到家后打開電腦，跟同事把 Pinpoint 上的慢查詢?nèi)罩緭瞥鰜怼?/p>
看到一個(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)?limit 后面的偏移量太大導(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 掃描大量不需要的行然后再拋棄掉。
數(shù)據(jù)模擬
那好致扯，了解了問題的原理肤寝，那就要試著解決它了。涉及數(shù)據(jù)敏感性抖僵，我們這邊模擬一下這種情況鲤看，構(gòu)造一些數(shù)據(jù)來做測試。
①創(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
);
②創(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;
③編寫存儲(chǔ)過程耍群，模擬 500W 的員工數(shù)據(jù)
/*建立存儲(chǔ)過程：往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);
④編寫存儲(chǔ)過程，模擬 120 的部門數(shù)據(jù)
/*建立存儲(chǔ)過程：往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);
⑤建立關(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í)。
解決方案
①使用索引覆蓋+子查詢優(yōu)化
因?yàn)槲覀冇兄麈I id赖临，并且在上面建了索引，所以可以先在索引樹中找到開始位置的 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?
②起始位置重定義
記住上次查找結(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ì)跳躍著獲取亏吝。
③降級策略
看了網(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)用過于頻繁，直接給予斷掉金句。
