大家好，我是半夏之沫 😁😁 一名金融科技領域的JAVA系統研發😊😊
我希望將自己工作和學習中的經驗以最樸實，最嚴謹的方式分享給大家，共同進步👉💓👈
👉👉👉👉👉👉👉👉💓寫作不易，期待大家的關注和點贊💓👈👈👈👈👈👈👈👈
👉👉👉👉👉👉👉👉💓關注微信公眾號【技術探界】 💓👈👈👈👈👈👈👈👈

前言

前同事剛參加完字節的二面，向我反饋了一道MySQL 深分頁的優化題目，起初我以為這只是一道很常規的深分頁的題目，但是聽完字節面試官的追問，才發現 水很深。

參考資料

正文

一. 題目説明

請優化如下SQL語句，其中tb表的b列存在索引。

SELECT * FROM tb WHERE tb.b = 5 LIMIT 30000,5;

前同事給出的答案如下。

SELECT * FROM tb WHERE id IN (SELECT id FROM tb WHERE tb.b = 5 LIMIT 30000,5);

此時面試官進行了兩個問題追加。

1. 優化方案的SQL語句能真實執行嗎；
2. 還有其它的SQL優化手段嗎。

前同事一個沒答上來，隨即面試官又追加了一個問題。

1. 請説一下一開始給到你的SQL語句為什麼會慢。

那麼到這裏，可以發現字節面試官其實真正的目的是想問為什麼深分頁會慢，這個問題你有仔細想過嗎，如果問到你，你會怎麼回答。

二. 深分頁演示

創建如下一張表。

CREATE TABLE blog_post (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主鍵',
    uid VARCHAR(255) NOT NULL COMMENT '唯一標識',
    like_count INT NOT NULL DEFAULT '0' COMMENT '點贊統計',
    collect_count INT NOT NULL DEFAULT '0' COMMENT '收藏統計',
    recommend TINYINT(1) NOT NULL DEFAULT '0' COMMENT '推薦標識',
    KEY like_count_index(like_count)
);

向表中插入100W條數據，統計如下。

+----------+
| COUNT(*) |
+----------+
|  1100010 |
+----------+

執行如下一條深分頁的查詢SQL。

SELECT 
  * 
FROM
  blog_post
WHERE
  like_count > 0
LIMIT 500000,10;

耗時如下。

10 rows in set (1.34 sec)

很簡單的一條查詢語句結果耗時達到了1.34秒。

三. 深分頁為什麼慢

還是以如下這條語句來分析。

SELECT 
  * 
FROM
  blog_post
WHERE
  like_count > 0
LIMIT 500000,10;

上述這條SQL語句在執行時，MySQL的Server層和InnoDB存儲引擎層存在如下這樣的交互。

通過上圖得到如下兩個信息。

1. 存在大量回表操作。因為不滿足覆蓋索引，所以需要先通過二級索引獲取到主鍵Id，然後回表從主鍵索引中獲取到完整記錄，而回表操作是隨機IO，會存在大量耗時的情況；
2. limit 條件判斷髮生在 Server 層。Server層準備將數據返回給客户端時，才會去判斷limit的條件。

現在就可以回答為什麼深分頁慢了，首先慢的深分頁通常沒有使用到覆蓋索引，此時為了得到完整記錄就需要回表，而回表是隨機磁盤IO，速度慢消耗大，這就是深分頁慢的 根本原因。其次深分頁場景裏limit的偏移量通常很大，並且limit的判斷髮生在Server層，這就導致存在大量無效的回表，即前50W條記錄都是通過二級索引再回表到主鍵索引得到的，但實際這50W條件記錄都不是被需要的，做的這50W次回表都是無意義的。

總結下來就是。

1. 沒有使用覆蓋索引存在大量回表操作；
2. limit 的判斷髮生在 Server 層導致大量回表操作都是無意義的。

四. 優化深分頁

既然明確了深分頁為什麼慢，那麼現在就可以很好的針對原因從SQL層面進行優化。

既然 根本原因 是存在大量無效回表，那麼我們可以着手消滅掉回表操作或者減少回表操作。

首先可以將SQL優化如下。

SELECT 
  like_count 
FROM
  blog_post
WHERE
  like_count > 0
LIMIT 500000,10;

將查詢字段修改為like_count，此時深分頁就算再深，不用回表，速度也是十分可觀的，此時查詢耗時如下。

10 rows in set (0.13 sec)

速度提升十分可觀，但假如一定要查詢完整記錄呢，此時可以將SQL優化如下。

SELECT 
  *
FROM
  blog_post
INNER JOIN
  (SELECT id FROM blog_post WHERE like_count > 0 LIMIT 500000,10) AS bpid
ON
  blog_post.id = bpid.id;

上述語句的查詢耗時如下。

10 rows in set (0.17 sec)

速度提升也是十分可觀，但其實無論哪種優化方式，從SQL層面入手的話，思路都是將回表操作進行了減少甚至消除。

總結

深分頁其實就是使用limit時偏移量很大，而被偏移的數據其實都是不被需要的，如果在獲取這些數據時耗時很高，那麼這些很高的耗時是白白浪費的，那麼什麼情況下會出現獲取這些數據耗時很高呢，最常見的就是沒有使用到覆蓋索引的情況，此時每一條被偏移的數據都要回表一次才能得到完整數據，耗時自然就很高了。

所以如果一定要深分頁，最好的優化方案就是避免出現回表，也就是利用覆蓋索引。

大家好，我是半夏之沫 😁😁 一名金融科技領域的JAVA系統研發😊😊
我希望將自己工作和學習中的經驗以最樸實，最嚴謹的方式分享給大家，共同進步👉💓👈
👉👉👉👉👉👉👉👉💓寫作不易，期待大家的關注和點贊💓👈👈👈👈👈👈👈👈
👉👉👉👉👉👉👉👉💓關注微信公眾號【技術探界】 💓👈👈👈👈👈👈👈👈