MySql深分頁問題解決

2023-02-05 14:03:27

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;