目錄

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

1. 問題描述

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

2. 問題分析

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

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

select * from t_name where c_name1="xxx" order by c_name2 limit 2000000,25;

這種查詢的慢，其實是因為 limit 后面的偏移量太大導致的。
比如像上面的 limit 2000000，25，這個等同于數(shù)據(jù)庫要掃描出 2000025 條數(shù)據(jù)，然后再丟棄前面的 20000000 條數(shù)據(jù)，返回剩下 25 條數(shù)據(jù)給用戶，這種取法明顯不合理。

3. 驗證測試

3.1 創(chuàng)建兩個表

-- 創(chuàng)建兩個表：員工表和部門表
-- 部門表,存在則進行刪除
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 ""
);

-- 員工表,存在則進行刪除
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
);

注意說明

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

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

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

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

3.3 編寫存儲過程

-- 編寫存儲過程，模擬 100W 的員工數(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 把autocommit設置成0，把默認提交關閉*/
? ? ?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ù)，時間有點久，耐心等待，1409s
?call insert_emp(0,5000000);

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

3.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);

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

3.5 創(chuàng)建索引

-- 建立關鍵字段的索引，這邊是跑完數(shù)據(jù)之后再建索引，會導致建索引耗時長，但是跑數(shù)據(jù)就會快一些。
-- 建立關鍵字段的索引:排序、條件
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 驗證測試

-- 驗證測試
-- 偏移量為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)化

因為我們有主鍵 id，并且在上面建了索引，所以可以先在索引樹中找到開始位置的 id 值，再根據(jù)找到的 id 值查詢行數(shù)據(jù)。

-- 子查詢獲取偏移100條的位置的id，在這個位置上往后取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，在這個位置上往后取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 起始位置重定義

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

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

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

-- 記住了上次的分頁的最后一條數(shù)據(jù)的id是100，這邊就直接跳過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;

-- 記住了上次的分頁的最后一條數(shù)據(jù)的id是4800000，這邊就直接跳過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 降級策略

看了網(wǎng)上一個阿里的 DBA 同學分享的方案：配置 limit 的偏移量和獲取數(shù)一個最大值，超過這個最大值，就返回空數(shù)據(jù)。
因為他覺得超過這個值你已經(jīng)不是在分頁了，而是在刷數(shù)據(jù)了，如果確認要找數(shù)據(jù)，應該輸入合適條件來縮小范圍，而不是一頁一頁分頁。

5. 梳理總結

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

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

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

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