目錄

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

1. 問題描述

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

2. 問題分析

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

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

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

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

3. 驗證測試

3.1 創建兩個表

-- 創建兩個表：員工表和部門表
-- 部門表,存在則進行刪除
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數據庫中的一種整型，比INT小，比SMALLINT大，
• 取值范圍為：-8388608到8388607，無符號的范圍是0到16777215。
• 中等大小的整數，一位大小為3個字節。

3.2 創建兩個函數

-- 創建兩個函數：生成隨機字符串和隨機編號
-- 產生隨機字符串的函數
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;

-- 產生隨機部門編號的函數
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;

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

3.3 編寫存儲過程

-- 編寫存儲過程，模擬 100W 的員工數據。
-- 建立存儲過程：往emp表中插入數據
?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條數據，時間有點久，耐心等待，1409s
?call insert_emp(0,5000000);

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

3.4 編寫存儲過程

-- 編寫存儲過程，模擬 120 的部門數據
-- 建立存儲過程：往dep表中插入數據
?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條數據
?call insert_dept(1,120);

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

3.5 創建索引

-- 建立關鍵字段的索引，這邊是跑完數據之后再建索引，會導致建索引耗時長，但是跑數據就會快一些。
-- 建立關鍵字段的索引:排序、條件
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 使用索引覆蓋+子查詢優化

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

-- 子查詢獲取偏移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。

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

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

-- 記住了上次的分頁的最后一條數據的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;

-- 記住了上次的分頁的最后一條數據的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 降級策略

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

5. 梳理總結

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

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

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

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