目錄

• 1. 問題描述
• 2. 問題分析
• 3. 驗(yàn)證測(cè)試
• 3.1 創(chuàng)建兩個(gè)表
• 3.2 創(chuàng)建兩個(gè)函數(shù)
• 3.3 編寫存儲(chǔ)過(guò)程
• 3.4 編寫存儲(chǔ)過(guò)程
• 3.5 創(chuàng)建索引
• 3.6 驗(yàn)證測(cè)試
• 4. 解決方案
• 4.1 使用索引覆蓋+子查詢優(yōu)化
• 4.2 起始位置重定義
• 4.3 降級(jí)策略
• 5. 梳理總結(jié)

1. 問題描述

日常開發(fā)中經(jīng)常會(huì)涉及到數(shù)據(jù)查詢分頁(yè)的問題，一般情況下都是根據(jù)前端傳入頁(yè)數(shù)與頁(yè)碼通過(guò)mysql的limit方式實(shí)現(xiàn)分頁(yè)，對(duì)于數(shù)據(jù)量較小的情況下沒有問題，但是如果數(shù)據(jù)量很大，深分頁(yè)可能導(dǎo)致查詢效率低下，接口超時(shí)的情況。

2. 問題分析

其實(shí)對(duì)于我們的 MySQL 查詢語(yǔ)句來(lái)說(shuō)，整體效率還是可以的，該有的聯(lián)表查詢優(yōu)化都有，該簡(jiǎn)略的查詢內(nèi)容也有，關(guā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 是怎樣的：

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ù)庫(kù)要掃描出 2000025 條數(shù)據(jù)，然后再丟棄前面的 20000000 條數(shù)據(jù)，返回剩下 25 條數(shù)據(jù)給用戶，這種取法明顯不合理。

3. 驗(yàn)證測(cè)試

3.1 創(chuàng)建兩個(gè)表

-- 創(chuàng)建兩個(gè)表：?jiǎn)T工表和部門表
-- 部門表,存在則進(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
);

注意說(shuō)明

• mediumint是MySQL數(shù)據(jù)庫(kù)中的一種整型，比INT小，比SMALLINT大，
• 取值范圍為：-8388608到8388607，無(wú)符號(hào)的范圍是0到16777215。
• 中等大小的整數(shù)，一位大小為3個(gè)字節(jié)。

3.2 創(chuàng)建兩個(gè)函數(shù)

-- 創(chuàng)建兩個(gè)函數(shù)：生成隨機(jī)字符串和隨機(jī)編號(hào)
-- 產(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ī)部門編號(hào)的函數(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;

注意說(shuō)明
-- 執(zhí)行函數(shù)問題，This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de
-- 這是我們開啟了bin-log, 我們就必須指定我們的函數(shù)是否是，DETERMINISTIC 不確定的， NO SQL 沒有SQl語(yǔ)句，當(dāng)然也不會(huì)修改數(shù)據(jù)
-- 在MySQL中創(chuàng)建函數(shù)時(shí)出現(xiàn)這種錯(cuò)誤的解決方法：set global log_bin_trust_function_creators=TRUE;
set global log_bin_trust_function_creators=TRUE;

3.3 編寫存儲(chǔ)過(guò)程

-- 編寫存儲(chǔ)過(guò)程，模擬 100W 的員工數(shù)據(jù)。
-- 建立存儲(chǔ)過(guò)程：往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ù)，時(shí)間有點(diǎn)久，耐心等待，1409s
?call insert_emp(0,5000000);

-- 查詢部門員工表
select * from emp LIMIT 1,10;

3.4 編寫存儲(chǔ)過(guò)程

-- 編寫存儲(chǔ)過(guò)程，模擬 120 的部門數(shù)據(jù)
-- 建立存儲(chǔ)過(guò)程：往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);

-- 查詢部門員工表
select * from dep;

3.5 創(chuàng)建索引

-- 建立關(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); 

3.6 驗(yàn)證測(cè)試

-- 驗(yàn)證測(cè)試
-- 偏移量為100，取25，Time: 0.011s
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，Time: 10.242s
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;

4. 解決方案

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

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

-- 子查詢獲取偏移100條的位置的id，在這個(gè)位置上往后取25，Time: 0.04s
?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，Time: 1.549s
?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;

4.2 起始位置重定義

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

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

但是有個(gè)問題，只適合一頁(yè)一頁(yè)的分頁(yè)，這樣才能記住前一個(gè)分頁(yè)的最后 id。如果用戶跳著分頁(yè)就有問題了，比如剛剛刷完第 25 頁(yè)，馬上跳到 35 頁(yè)，數(shù)據(jù)就會(huì)不對(duì)。這種的適合場(chǎng)景是類似百度搜索或者騰訊新聞那種滾輪往下拉，不斷拉取不斷加載的情況。這種延遲加載會(huì)保證數(shù)據(jù)不會(huì)跳躍著獲取。

-- 記住了上次的分頁(yè)的最后一條數(shù)據(jù)的id是100，這邊就直接跳過(guò)100，從101開始掃描表，Time: 0.006s
?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;

-- 記住了上次的分頁(yè)的最后一條數(shù)據(jù)的id是4800000，這邊就直接跳過(guò)4800000，從4800001開始掃描表，Time: 0.046s
?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;

4.3 降級(jí)策略

看了網(wǎng)上一個(gè)阿里的 DBA 同學(xué)分享的方案：配置 limit 的偏移量和獲取數(shù)一個(gè)最大值，超過(guò)這個(gè)最大值，就返回空數(shù)據(jù)。
因?yàn)樗X得超過(guò)這個(gè)值你已經(jīng)不是在分頁(yè)了，而是在刷數(shù)據(jù)了，如果確認(rèn)要找數(shù)據(jù)，應(yīng)該輸入合適條件來(lái)縮小范圍，而不是一頁(yè)一頁(yè)分頁(yè)。

5. 梳理總結(jié)

深分頁(yè)問題從理論上來(lái)說(shuō)是存在的場(chǎng)景，但是從實(shí)際的業(yè)務(wù)場(chǎng)景考慮，深分頁(yè)很多情況下缺少具體的業(yè)務(wù)場(chǎng)景做支撐，試想哪個(gè)業(yè)務(wù)會(huì)從480W頁(yè)面，查詢25條數(shù)據(jù)，如果需要搜索某條數(shù)據(jù)，使用最多的應(yīng)該根據(jù)條件類型過(guò)濾吧。

每種方案各有優(yōu)缺點(diǎn)，具體采用那種解決方案需要結(jié)合具體的業(yè)務(wù)場(chǎng)景，如果根據(jù)實(shí)際業(yè)務(wù)場(chǎng)景不需要深分頁(yè)，可以采用降級(jí)策略，設(shè)置分頁(yè)參數(shù)閾值。如果確實(shí)需要深分頁(yè)問題可以覆蓋子+子查詢優(yōu)化或者通過(guò)偏移量查詢，如果能獲取到偏移量的前提下優(yōu)先選擇偏移量的方案，否則采用覆蓋索引+子查詢。

無(wú)論是否深分頁(yè)都應(yīng)該考慮限流降級(jí)的問題，而且要考慮短時(shí)間內(nèi)重復(fù)調(diào)用的問題，可以限制每秒執(zhí)行次數(shù)，避免用戶誤點(diǎn)以及調(diào)用頻繁帶來(lái)的數(shù)據(jù)安全問題。

到此這篇關(guān)于MySql深分頁(yè)問題解決的文章就介紹到這了,更多相關(guān)MySql深分頁(yè)內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！