1、問(wèn)題
| 1 | 1POST domain/v1.0/module/method?order=condition&orderType=desc&offset=1800000&limit=500 |
|---|---|
domain拴还、module 和 method 都是化名跨晴，代表接口的域、模塊和實(shí)例方法名片林，后面的offset和limit代表 分頁(yè)操作的偏移量 和 每頁(yè)的數(shù)量端盆， 也就是說(shuō)該用戶是在 翻第（1800000/500+1=3601）頁(yè)。初步撈了一下日志费封，發(fā)現(xiàn) 有8000多次這樣調(diào)用焕妙。
這太神奇了，而且頁(yè)面上的分頁(yè)單頁(yè)數(shù)量也不是500孝偎，而是 25條每頁(yè)访敌，這個(gè)絕對(duì)不是人為的在功能頁(yè)面上進(jìn)行一頁(yè)一頁(yè)的翻頁(yè)操作，而是數(shù)據(jù)被刷了（說(shuō)明下衣盾，生產(chǎn)環(huán)境數(shù)據(jù)有1億+）寺旺。詳細(xì)對(duì)比日志發(fā)現(xiàn)爷抓，很多分頁(yè)的時(shí)間是重疊的，對(duì)方應(yīng)該是多線程調(diào)用阻塑。
2蓝撇、分析
對(duì)于MySQL查詢語(yǔ)句來(lái)說(shuō)，整體效率還是可以的陈莽，該有的聯(lián)表查詢優(yōu)化都有渤昌，該簡(jiǎn)略的查詢內(nèi)容也有，關(guān)鍵條件字段和排序字段該有的索引也都在走搁，問(wèn)題在于他一頁(yè)一頁(yè)的分頁(yè)去查詢独柑，查到越后面的頁(yè)數(shù)，掃描到的數(shù)據(jù)越多私植，也就越慢忌栅。
我們?cè)诓榭辞皫醉?yè)的時(shí)候，發(fā)現(xiàn)速度非城冢快索绪，比如 limit 200,25，瞬間就出來(lái)了贫悄。但是越往后瑞驱，速度就越慢，特別是百萬(wàn)條之后窄坦，卡到不行唤反，那這個(gè)是什么原理呢。先看一下我們翻頁(yè)翻到后面時(shí)鸭津，查詢的sql是怎樣的：
這種查詢的慢拴袭，其實(shí)是因?yàn)閘imit后面的偏移量太大導(dǎo)致的。 比如像上面的 limit 2000000,25 曙博，這個(gè)等同于數(shù)據(jù)庫(kù)要掃描出 2000025條數(shù)據(jù)，然后再丟棄前面的 20000000條數(shù)據(jù)怜瞒，返回剩下25條數(shù)據(jù)給用戶父泳，這種取法明顯不合理。
查詢性能優(yōu)化中吴汪，分頁(yè)操作通常會(huì)使用limit加上偏移量的辦法實(shí)現(xiàn)惠窄，同時(shí)再加上合適的order by子句。但這會(huì)出現(xiàn)一個(gè)常見(jiàn)問(wèn)題：當(dāng)偏移量非常大的時(shí)候漾橙，它會(huì)導(dǎo)致MySQL掃描大量不需要的行然后再拋棄掉杆融。
三、數(shù)據(jù)模擬
那好霜运，了解了問(wèn)題的原理脾歇，那就要試著解決它了蒋腮。涉及數(shù)據(jù)敏感性，我們這邊模擬一下這種情況藕各，構(gòu)造一些數(shù)據(jù)來(lái)做測(cè)試池摧。
1、創(chuàng)建兩個(gè)表：?jiǎn)T工表和部門表
/*部門表,存在則進(jìn)行刪除 */
drop tableifEXISTS dep;
create table dep(
id int unsigned primary key auto_increment,
depno mediumint unsigned not null default0,
depname varchar(20) not null default "",
memo varchar(200) not null default ""
);
/*員工表,存在則進(jìn)行刪除*/
drop tableifEXISTS emp;
create table emp(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default0,
empname varchar(20) not null default"",
job varchar(9) not null default"",
mgr mediumint unsigned not null default0,
hiredate datetime not null,
sal decimal(7,2) not null,
comn decimal(7,2) not null,
depno mediumint unsigned not null default0
);
2激况、創(chuàng)建兩個(gè)函數(shù)：生成隨機(jī)字符串和隨機(jī)編號(hào)
產(chǎn)生隨機(jī)字符串的函數(shù)
DELIMITER $
DROP FUNCTIONifEXISTS 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 DEFAULT0;
WHILE
i < n DOSET 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ī)部門編號(hào)的函數(shù)
DELIMITER $
DROP FUNCTIONifEXISTS rand_num;
CREATE FUNCTION rand_num ( ) RETURNS INT ( 5 ) BEGIN
DECLARE
i INT DEFAULT0;
SET i = FLOOR( 100+ RAND( ) * 10 );
RETURN i;
END $DELIMITER;
3作彤、編寫存儲(chǔ)過(guò)程，模擬500W的員工數(shù)據(jù)
/*建立存儲(chǔ)過(guò)程：往emp表中插入數(shù)據(jù)*/
DELIMITER $
DROP PROCEDUREifEXISTS insert_emp;
CREATE PROCEDURE insert_emp ( IN START INT ( 10 ), IN max_num INT ( 10 ) ) BEGINDECLARE i INT DEFAULT0;
/*set autocommit =0 把a(bǔ)utocommit設(shè)置成0乌逐，把默認(rèn)提交關(guān)閉*/
SET autocommit = 0;
REPEATSET 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_numEND
REPEAT
;
COMMIT;
END $DELIMITER;
/*插入500W條數(shù)據(jù)*/
CALL insert_emp ( 0, 5000000 );
4竭讳、編寫存儲(chǔ)過(guò)程，模擬120的部門數(shù)據(jù)
/*建立存儲(chǔ)過(guò)程：往dep表中插入數(shù)據(jù)*/
DELIMITER $
DROP PROCEDUREifEXISTS insert_dept;
CREATE PROCEDURE insert_dept ( IN START INT ( 10 ), IN max_num INT ( 10 ) ) BEGINDECLARE i INT DEFAULT0;
SET autocommit = 0;
REPEATSET i = i + 1;
INSERT INTO dep ( depno, depname, memo )
VALUES
( ( START + i ), rand_string ( 10 ), rand_string ( 8 ) );
UNTIL i = max_numEND
REPEAT
;
COMMIT;
END $DELIMITER;
/*插入120條數(shù)據(jù)*/
CALL insert_dept ( 1, 120 );
5浙踢、建立關(guān)鍵字段的索引
這邊是跑完數(shù)據(jù)之后再建索引绢慢，會(huì)導(dǎo)致建索引耗時(shí)長(zhǎng)，但是跑數(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);
四、測(cè)試
1奋岁、測(cè)試數(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;
2闻伶、執(zhí)行結(jié)果
[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom emp a left join dep b on a.depno = b.depno order by a.id desc limit100,25;
受影響的行:0時(shí)間:0.001s
[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom emp a left join dep b on a.depno = b.depno order by a.id desc limit4800000,25;
受影響的行:0時(shí)間:12.275s
因?yàn)閽呙璧臄?shù)據(jù)多滨攻，所以這個(gè)明顯不是一個(gè)量級(jí)上的耗時(shí)。
五蓝翰、解決方案
1光绕、使用索引覆蓋+子查詢優(yōu)化
因?yàn)槲覀冇兄麈Iid，并且在上面建了索引畜份，所以可以先在索引樹(shù)中找到開(kāi)始位置的 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 limit100,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 limit4800000,1)
order by a.id limit25;
-
執(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 limit100,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 limit4800000,1)
order by a.id limit 25;
受影響的行:0時(shí)間:1.541s
2、起始位置重定義
記住上次查找結(jié)果的主鍵位置钙态，避免使用偏移量 offset慧起。
/*記住了上次的分頁(yè)的最后一條數(shù)據(jù)的id是100，這邊就直接跳過(guò)100册倒，從101開(kāi)始掃描表*/
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 >100o
rder by a.id limit 25;
/*記住了上次的分頁(yè)的最后一條數(shù)據(jù)的id是4800000蚓挤，這邊就直接跳過(guò)4800000，從4800001開(kāi)始掃描表*/
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è)效率是最好的，無(wú)論怎么分頁(yè)灿意，耗時(shí)基本都是一致的估灿，因?yàn)樗麍?zhí)行完條件之后，都只掃描了25條數(shù)據(jù)脾歧。
但是有個(gè)問(wèn)題甲捏，只適合一頁(yè)一頁(yè)的分頁(yè)，這樣才能記住前一個(gè)分頁(yè)的最后Id鞭执。如果用戶跳著分頁(yè)就有問(wèn)題了司顿，比如剛剛刷完第25頁(yè)，馬上跳到35頁(yè)兄纺，數(shù)據(jù)就會(huì)不對(duì)大溜。
這種的適合場(chǎng)景是類似百度搜索或者騰訊新聞那種滾輪往下拉，不斷拉取不斷加載的情況估脆。這種延遲加載會(huì)保證數(shù)據(jù)不會(huì)跳躍著獲取钦奋。
3、降級(jí)策略
還有一種方案參考：配置limit的偏移量和獲取數(shù)一個(gè)最大值疙赠，超過(guò)這個(gè)最大值付材，就返回空數(shù)據(jù)。
因?yàn)樗X(jué)得超過(guò)這個(gè)值你已經(jīng)不是在分頁(yè)了圃阳，而是在刷數(shù)據(jù)了厌衔，如果確認(rèn)要找數(shù)據(jù)，應(yīng)該輸入合適條件來(lái)縮小范圍捍岳，而不是一頁(yè)一頁(yè)分頁(yè)富寿。
request的時(shí)候 如果offset大于某個(gè)數(shù)值就先返回一個(gè)4xx的錯(cuò)誤。
六锣夹、小結(jié)
使用第三個(gè)方案页徐，對(duì)offset做一下限流，超過(guò)某個(gè)值银萍，就返回空值变勇。第二天使用第一種和第二種配合使用的方案對(duì)程序和數(shù)據(jù)庫(kù)腳本進(jìn)一步做了優(yōu)化。
合理來(lái)說(shuō)做任何功能都應(yīng)該考慮極端情況贴唇，設(shè)計(jì)容量都應(yīng)該涵蓋極端邊界測(cè)試贰锁。
另外，該有的限流滤蝠、降級(jí)也應(yīng)該考慮進(jìn)去。比如工具多線程調(diào)用授嘀，在短時(shí)間頻率內(nèi)8000次調(diào)用物咳，可以使用計(jì)數(shù)服務(wù)判斷并反饋用戶調(diào)用過(guò)于頻繁，直接給予斷掉蹄皱。
