MySQL百萬資料深度分頁優化思路分析

業務場景

一般在項目開發中會有很多的統計資料需要進行上報分析，一般在分析過後會在背景展示出來給營運和産品進行分頁檢視，最常見的一種就是根據日期進行篩選。這種統計資料随着時間的推移資料量會慢慢的變大，達到百萬、千萬條資料隻是時間問題。

瓶頸再現

建立了一張user表，給create_time字段添加了索引。并在該表中添加了100w條資料。

我們這裡使用limit分頁的方式查詢下前5條資料和後5條資料在查詢時間上有什麼差別。

查詢前10條基本上不消耗什麼時間

我們從第50w+開始取資料的時候，查詢耗時1秒。

SQL_NO_CACHE

這個關鍵詞是為了不讓SQL查詢走緩存。

同樣的SQL語句，不同的分頁條件，兩者的性能差距如此之大，那麼随着資料量的增長，往後頁的查詢所耗時間按理會越來越大。

問題分析

回表

我們一般對于查詢頻率比較高的字段會建立索引。索引會提高我們的查詢效率。我們上面的語句使用了SELECT * FROM user，但是我們并不是所有的字段都建立了索引。當從索引檔案中查詢到符合條件的資料後，還需要從資料檔案中查詢到沒有建立索引的字段。那麼這個過程稱之為回表。

覆寫索引

如果查詢的字段正好建立了索引了，比如 SELECT create_time FROM user，我們查詢的字段是我們建立的索引，那麼這個時候就不需要再去資料檔案裡面查詢，也就不需要回表。這種情況我們稱之為覆寫索引。

IO

回表操作通常是IO操作，因為需要根據索引查找到資料行後，再根據資料行的主鍵或唯一索引去聚簇索引中查找具體的資料行。聚簇索引一般是存儲在磁盤上的資料檔案，是以在執行回表操作時需要從磁盤讀取資料，而磁盤IO是相對較慢的操作。

LIMTI 2000,10 ？

你有木有想過LIMIT 2000,10會不會掃描1-2000行，你之前有沒有跟我一樣，覺得資料是直接從2000行開始取的，前面的根本沒掃描或者不回表。其實這樣的寫法，一個完整的流程是查詢資料，如果不能覆寫索引，那麼也是要回表查詢資料的。

現在你知道為什麼越到後面查詢越慢了吧！

問題總結

我們現在知道了LIMIT 遇到後面查詢的性能越差，性能差的原因是因為要回表，既然已經找到了問題那麼我們隻需要減少回表的次數就可以提升查詢性能了。

解決方案

既然覆寫索引可以防止資料回表，那麼我們可以先查出來主鍵id（主鍵索引），然後将查出來的資料作為臨時表然後 JOIN 原表就可以了，這樣隻需要對查詢出來的5條結果進行資料回表，大幅減少了IO操作。

優化前後性能對比

我們看下執行效果：

• 優化前：1.4s
• 優化後：0.2s

查詢耗時性能大幅提升。這樣如果分頁資料很大的話，也不會像普通的limit查詢那樣慢。

三種優化方案，查詢效率直接提升10倍

1. 準備資料

先建立一張使用者表，隻在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 '建立時間',  PRIMARY KEY (`id`),  KEY `idx_create_time` (`create_time`)) ENGINE=InnoDB COMMENT='使用者表';           

然後往使用者表中插入100萬條測試資料，這裡可以使用存儲過程：

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. 驗證深分頁問題

每頁10條，當我們查詢第一頁的時候，速度很快：

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

在不到0.01秒内直接傳回了，是以沒顯示出執行時間。

當我們翻到第10000頁的時候，查詢效率急劇下降：

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

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

耗時主要花在哪裡了？

1. 需要掃描前10條資料，資料量較大，比較耗時
2. create_time是非聚簇索引，需要先查詢出主鍵ID，再回表查詢，通過主鍵ID查詢出所有字段

畫一下回表查詢流程：

1. 先通過create_time查詢出主鍵ID

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

别問為什麼B+樹的結構是這樣的？問就是規定。

可以看一下前兩篇文章。

然後我們就針對這兩個耗時原因進行優化。

3. 優化查詢

3.1 使用子查詢

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

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

不過這樣查詢會報錯，說是子查詢中不支援使用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);           

執行時間縮短到0.05秒，減少了0.12秒，相當于查詢性能提升了3倍。

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

我們用explain檢視一下執行計劃就明白了：

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，表示用到了覆寫索引，是以子查詢無需回表查詢，加快了查詢效率。

3.2 使用inner join關聯查詢

把子查詢的結果當成一張臨時表，然後和原表進行關聯查詢。

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 使用分頁遊标（推薦）

實作方式就是：當我們查詢第二頁的時候，把第一頁的查詢結果放到第二頁的查詢條件中。

例如：首先查詢第一頁

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

然後查詢第二頁，把第一頁的查詢結果放到第二頁查詢條件中：

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

這樣相當于每次都是查詢第一頁，也就不存在深分頁的問題了，推薦使用。

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

這樣的查詢方式雖然好用，但是又帶來一個問題，就是跳轉到指定頁數，隻能一頁頁向下翻。

是以這種查詢隻适合特定場景，比如資訊類APP的首頁。

網際網路APP一般采用瀑布流的形式，比如百度首頁、頭條首頁，都是一直向下滑動翻頁，并沒有跳轉到制定頁數的需求。

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

傳參中帶了上一頁的查詢結果。

響應資料中，傳回了下一頁查詢條件。

是以這種查詢方式的應用場景還是挺廣的，趕快用起來吧。

知識點總結：