開(kāi)發(fā)經(jīng)常遇到分頁(yè)查詢的需求，但是當(dāng)翻頁(yè)過(guò)多的時(shí)候，就會(huì)產(chǎn)生深分頁(yè)，導(dǎo)致查詢效率急劇下降。有沒(méi)有什么辦法，能解決深分頁(yè)的問(wèn)題呢？本文總結(jié)了三種優(yōu)化方案，查詢效率直接提升10倍，一起學(xué)習(xí)一下。

開(kāi)發(fā)經(jīng)常遇到分頁(yè)查詢的需求，但是當(dāng)翻頁(yè)過(guò)多的時(shí)候，就會(huì)產(chǎn)生深分頁(yè)，導(dǎo)致查詢效率急劇下降。

有沒(méi)有什么辦法，能解決深分頁(yè)的問(wèn)題呢？

本文總結(jié)了三種優(yōu)化方案，查詢效率直接提升10倍，一起學(xué)習(xí)一下。

1. 準(zhǔn)備數(shù)據(jù)

先創(chuàng)建一張用戶表，只在create_time字段上加索引：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `create_time` timestamp NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB COMMENT='用戶表';

然后往用戶表中插入100萬(wàn)條測(cè)試數(shù)據(jù)，這里可以使用存儲(chǔ)過(guò)程：

drop PROCEDURE IF EXISTS insertData;
DELIMITER $$
create procedure insertData()
begin
 declare i int default 1;
   while i <= 100000 do
         INSERT into user (name,create_time) VALUES (CONCAT("name",i), now());
         set i = i + 1; 
   end while; 
end $$

call insertData() $$

2. 驗(yàn)證深分頁(yè)問(wèn)題

每頁(yè)10條，當(dāng)我們查詢第一頁(yè)的時(shí)候，速度很快：

select * from user 
where create_time>'2022-07-03' 
limit 0,10;

在不到0.01秒內(nèi)直接返回了，所以沒(méi)顯示出執(zhí)行時(shí)間。

當(dāng)我們翻到第10000頁(yè)的時(shí)候，查詢效率急劇下降：

select * from user 
where create_time>'2022-07-03' 
limit 100000,10;

執(zhí)行時(shí)間變成了0.16秒，性能至少下降了幾十倍。

耗時(shí)主要花在哪里了？

• 需要掃描前10條數(shù)據(jù)，數(shù)據(jù)量較大，比較耗時(shí)
• create_time是非聚簇索引，需要先查詢出主鍵ID，再回表查詢，通過(guò)主鍵ID查詢出所有字段

畫(huà)一下回表查詢流程：

1. 先通過(guò)create_time查詢出主鍵ID

2. 再通過(guò)主鍵ID查詢出表中所有字段

別問(wèn)為什么B+樹(shù)的結(jié)構(gòu)是這樣的？問(wèn)就是規(guī)定。

可以看一下前兩篇文章。

然后我們就針對(duì)這兩個(gè)耗時(shí)原因進(jìn)行優(yōu)化。

3. 優(yōu)化查詢

3.1 使用子查詢

先用子查詢查出符合條件的主鍵，再用主鍵ID做條件查出所有字段。

select * from user 
where id in (
  select id from user 
  where create_time>'2022-07-03' 
  limit 100000,10
);

不過(guò)這樣查詢會(huì)報(bào)錯(cuò)，說(shuō)是子查詢中不支持使用limit。

我們加一層子查詢嵌套，就可以了：

select * from user 
where id in (
 select id from (
    select id from user 
    where create_time>'2022-07-03' 
    limit 100000,10
 ) as t
);

執(zhí)行時(shí)間縮短到0.05秒，減少了0.12秒，相當(dāng)于查詢性能提升了3倍。

為什么先用子查詢查出符合條件的主鍵ID，就能縮短查詢時(shí)間呢？

我們用explain查看一下執(zhí)行計(jì)劃就明白了：

explain select * from user 
where id in (
 select id from (
    select id from user 
    where create_time>'2022-07-03' 
    limit 100000,10
 ) as t
);

可以看到Extra列顯示子查詢中用到Using index，表示用到了覆蓋索引，所以子查詢無(wú)需回表查詢，加快了查詢效率。

3.2 使用inner join關(guān)聯(lián)查詢

把子查詢的結(jié)果當(dāng)成一張臨時(shí)表，然后和原表進(jìn)行關(guān)聯(lián)查詢。

select * from user 
inner join (
   select id from user 
    where create_time>'2022-07-03' 
    limit 100000,10
) as t on user.id=t.id;

查詢性能跟使用子查詢一樣。

3.3 使用分頁(yè)游標(biāo)（推薦）

實(shí)現(xiàn)方式就是：當(dāng)我們查詢第二頁(yè)的時(shí)候，把第一頁(yè)的查詢結(jié)果放到第二頁(yè)的查詢條件中。

例如：首先查詢第一頁(yè)

select * from user 
where create_time>'2022-07-03' 
limit 10;

然后查詢第二頁(yè)，把第一頁(yè)的查詢結(jié)果放到第二頁(yè)查詢條件中：

select * from user 
where create_time>'2022-07-03' and id>10 
limit 10;

這樣相當(dāng)于每次都是查詢第一頁(yè)，也就不存在深分頁(yè)的問(wèn)題了，推薦使用。

執(zhí)行耗時(shí)是0秒，查詢性能直接提升了幾十倍。

這樣的查詢方式雖然好用，但是又帶來(lái)一個(gè)問(wèn)題，就是跳轉(zhuǎn)到指定頁(yè)數(shù)，只能一頁(yè)頁(yè)向下翻。

所以這種查詢只適合特定場(chǎng)景，比如資訊類APP的首頁(yè)。

互聯(lián)網(wǎng)APP一般采用瀑布流的形式，比如百度首頁(yè)、頭條首頁(yè)，都是一直向下滑動(dòng)翻頁(yè)，并沒(méi)有跳轉(zhuǎn)到制定頁(yè)數(shù)的需求。

不信的話，可以看一下，這是頭條的瀑布流：

傳參中帶了上一頁(yè)的查詢結(jié)果。

響應(yīng)數(shù)據(jù)中，返回了下一頁(yè)查詢條件。

所以這種查詢方式的應(yīng)用場(chǎng)景還是挺廣的，趕快用起來(lái)吧。

知識(shí)點(diǎn)總結(jié)：

到此這篇關(guān)于解決MySQL深分頁(yè)低效率問(wèn)題的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評(píng)論