實戰！聊聊如何解決MySQL深分頁問題

gongmeng發表於2023-03-15
MySql

文章轉載於田螺大哥的這篇文章 mp.weixin.qq.com/s/vj3dSl2mxxQeNl2...

前言

我們日常做分頁需求時，一般會用limit實現，但是當偏移量特別大的時候，查詢效率就變得低下。本文將分四個方案，討論如何最佳化MySQL百萬資料的深分頁問題，並附上最近最佳化生產慢SQL的實戰案例。

實戰！聊聊如何解決MySQL深分頁問題

limit深分頁為什麼會變慢？

CREATE TABLE account (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵Id',
  name varchar(255) DEFAULT NULL COMMENT '賬戶名',
  balance int(11) DEFAULT NULL COMMENT '餘額',
  create_time datetime NOT NULL COMMENT '建立時間',
  update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (id),
  KEY idx_name (name),
  KEY idx_update_time (update_time) //索引
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='賬戶表';

假設深分頁的執行SQL如下：

select id,name,balance from account where update_time> '2020-09-19' limit 100000,10;

這個SQL的執行時間如下：

實戰！聊聊如何解決MySQL深分頁問題
執行完需要0.742秒，深分頁為什麼會變慢呢？如果換成 limit 0,10，只需要0.006秒哦

實戰！聊聊如何解決MySQL深分頁問題
我們先來看下這個SQL的執行流程：

  1. 透過普通二級索引樹idx_update_time，過濾update_time條件，找到滿足條件的記錄ID。

  2. 透過ID，回到主鍵索引樹，找到滿足記錄的行，然後取出展示的列（回表）

  3. 掃描滿足條件的100010行，然後扔掉前100000行，返回。

實戰！聊聊如何解決MySQL深分頁問題
上圖為SQL的執行流程
執行計劃如下：

實戰！聊聊如何解決MySQL深分頁問題

SQL變慢原因有兩個：

  1. limit語句會先掃描offset+n行，然後再丟棄掉前offset行，返回後n行資料。也就是說limit 100000,10，就會掃描100010行，而limit 0,10，只掃描10行。

  2. limit 100000,10 掃描更多的行數，也意味著回表更多的次數。

透過子查詢最佳化

因為以上的SQL，回表了100010次，實際上，我們只需要10條資料，也就是我們只需要10次回表其實就夠了。因此，我們可以透過減少回表次數來最佳化。

回顧B+ 樹結構

那麼，如何減少回表次數呢？我們先來複習下B+樹索引結構哈~

InnoDB中，索引分主鍵索引（聚簇索引）和二級索引

  • 主鍵索引，葉子節點存放的是整行資料

  • 二級索引，葉子節點存放的是主鍵的值。

實戰！聊聊如何解決MySQL深分頁問題

把條件轉移到主鍵索引樹

如果我們把查詢條件，轉移回到主鍵索引樹，那就可以減少回表次數啦。轉移到主鍵索引樹查詢的話，查詢條件得改為主鍵id了，之前SQL的update_time這些條件咋辦呢？抽到子查詢那裡嘛~

子查詢那裡怎麼抽的呢？因為二級索引葉子節點是有主鍵ID的，所以我們直接根據update_time來查主鍵ID即可，同時我們把 limit 100000的條件，也轉移到子查詢，完整SQL如下：

select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1) LIMIT 10;寫漏了，可以補下時間條件在外面

查詢效果一樣的，執行時間只需要0.038秒！

實戰！聊聊如何解決MySQL深分頁問題

我們來看下執行計劃

實戰！聊聊如何解決MySQL深分頁問題

由執行計劃得知，子查詢 table a查詢是用到了idx_update_time索引。首先在索引上拿到了聚集索引的主鍵ID,省去了回表操作，然後第二查詢直接根據第一個查詢的 ID往後再去查10個就可以了!

實戰！聊聊如何解決MySQL深分頁問題
因此，這個方案是可以的~

INNER JOIN 延遲關聯

延遲關聯的最佳化思路，跟子查詢的最佳化思路其實是一樣的：都是把條件轉移到主鍵索引樹，然後減少回表。不同點是，延遲關聯使用了inner join代替子查詢。
最佳化後的SQL如下：

SELECT  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.update_time >= '2020-09-19' ORDER BY a.update_time LIMIT 100000, 10) AS  acct2 on acct1.id= acct2.id;

查詢效果也是槓桿的，只需要0.034秒

實戰！聊聊如何解決MySQL深分頁問題
執行計劃如下：

實戰！聊聊如何解決MySQL深分頁問題
查詢思路就是，先透過idx_update_time二級索引樹查詢到滿足條件的主鍵ID，再與原表透過主鍵ID內連線，這樣後面直接走了主鍵索引了，同時也減少了回表。

標籤記錄法

limit 深分頁問題的本質原因就是：偏移量（offset）越大，mysql就會掃描越多的行，然後再拋棄掉。這樣就導致查詢效能的下降。

其實我們可以採用標籤記錄法，就是標記一下上次查詢到哪一條了，下次再來查的時候，從該條開始往下掃描。就好像看書一樣，上次看到哪裡了，你就摺疊一下或者夾個書籤，下次來看的時候，直接就翻到啦。

假設上一次記錄到100000，則SQL可以修改為：

select  id,name,balance FROM account where id > 100000 order by id limit 10;

這樣的話，後面無論翻多少頁，效能都會不錯的，因為命中了id索引。但是這種方式有侷限性：需要一種類似連續自增的欄位。

使用between…and…

很多時候，可以將limit查詢轉換為已知位置的查詢，這樣MySQL透過範圍掃描between...and，就能獲得到對應的結果。

如果知道邊界值為100000，100010後，就可以這樣最佳化：

select  id,name,balance FROM account where id between 100000 and 100010 order by id;
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章