深度分頁介紹

查詢偏移量過大的場景我們稱為深度分頁，這會導致查詢性能較低，例如：

# MySQL 在無法利用索引的情況下跳過1000000條記錄后，再獲取10條記錄
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10

深度分頁問題的原因

當查詢偏移量過大時，MySQL 的查詢優化器可能會選擇全表掃描而不是利用索引來優化查詢。這是因為掃描索引和跳過大量記錄可能比直接全表掃描更耗費資源。

不同機器上這個查詢偏移量過大的臨界點可能不同，取決于多個因素，包括硬件配置（如 CPU 性能、磁盤速度）、表的大小、索引的類型和統計信息等。

MySQL 的查詢優化器采用基于成本的策略來選擇最優的查詢執行計劃。它會根據 CPU 和 I/O 的成本來決定是否使用索引掃描或全表掃描。如果優化器認為全表掃描的成本更低，它就會放棄使用索引。不過，即使偏移量很大，如果查詢中使用了覆蓋索引（covering index），MySQL 仍然可能會使用索引，避免回表操作。

深度分析優化建議

這里以 MySQL 數據庫為例介紹一下如何優化深度分頁。

范圍查詢

當可以保證 ID 的連續性時，根據 ID 范圍進行分頁是比較好的解決方案：

# 查詢指定 ID 范圍的數據
SELECT * FROM t_order WHERE id > 100000 AND id <= 100010 ORDER BY id
# 也可以通過記錄上次查詢結果的最后一條記錄的ID進行下一頁的查詢：
SELECT * FROM t_order WHERE id > 100000 LIMIT 10

這種基于 ID 范圍的深度分頁優化方式存在很大限制：

1. ID 連續性要求高: 實際項目中，數據庫自增 ID 往往因為各種原因（例如刪除數據、事務回滾等）導致 ID 不連續，難以保證連續性。
2. 排序問題: 如果查詢需要按照其他字段（例如創建時間、更新時間等）排序，而不是按照 ID 排序，那么這種方法就不再適用。
3. 并發場景: 在高并發場景下，單純依賴記錄上次查詢的最后一條記錄的 ID 進行分頁，容易出現數據重復或遺漏的問題。

子查詢

我們先查詢出 limit 第一個參數對應的主鍵值，再根據這個主鍵值再去過濾并 limit，這樣效率會更快一些。

阿里巴巴《Java 開發手冊》中也有對應的描述：

利用延遲關聯或者子查詢優化超多分頁場景。

# 通過子查詢來獲取 id 的起始值，把 limit 1000000 的條件轉移到子查詢
SELECT * FROM t_order WHERE id >= (SELECT id FROM t_order where id > 1000000 limit 1) LIMIT 10;

工作原理:

1. 子查詢 (SELECT id FROM t_order where id > 1000000 limit 1) 會利用主鍵索引快速定位到第 1000001 條記錄，并返回其 ID 值。
2. 主查詢 SELECT * FROM t_order WHERE id >= ... LIMIT 10 將子查詢返回的起始 ID 作為過濾條件，使用 id >= 獲取從該 ID 開始的后續 10 條記錄。

不過，子查詢的結果會產生一張新表，會影響性能，應該盡量避免大量使用子查詢。并且，這種方法只適用于 ID 是正序的。在復雜分頁場景，往往需要通過過濾條件，篩選到符合條件的 ID，此時的 ID 是離散且不連續的。

當然，我們也可以利用子查詢先去獲取目標分頁的 ID 集合，然后再根據 ID 集合獲取內容，但這種寫法非常繁瑣，不如使用 INNER JOIN 延遲關聯。

延遲關聯

延遲關聯與子查詢的優化思路類似，都是通過將 LIMIT 操作轉移到主鍵索引樹上，減少回表次數。相比直接使用子查詢，延遲關聯通過 INNER JOIN 將子查詢結果集成到主查詢中，避免了子查詢可能產生的臨時表。在執行 INNER JOIN 時，MySQL 優化器能夠利用索引進行高效的連接操作（如索引掃描或其他優化策略），因此在深度分頁場景下，性能通常優于直接使用子查詢。

-- 使用 INNER JOIN 進行延遲關聯
SELECT t1.*
FROM t_order t1
INNER JOIN (SELECT id FROM t_order where id > 1000000 LIMIT 10) t2 ON t1.id = t2.id;

工作原理:

1. 子查詢 (SELECT id FROM t_order where id > 1000000 LIMIT 10) 利用主鍵索引快速定位目標分頁的 10 條記錄的 ID。
2. 通過 INNER JOIN 將子查詢結果與主表 t_order 關聯，獲取完整的記錄數據。

除了使用 INNER JOIN 之外，還可以使用逗號連接子查詢。

-- 使用逗號進行延遲關聯
SELECT t1.* FROM t_order t1,
(SELECT id FROM t_order where id > 1000000 LIMIT 10) t2
WHERE t1.id = t2.id;

注意: 雖然逗號連接子查詢也能實現類似的效果，但為了代碼可讀性和可維護性，建議使用更規范的 INNER JOIN 語法。

覆蓋索引

索引中已經包含了所有需要獲取的字段的查詢方式稱為覆蓋索引。

覆蓋索引的好處：

• 避免 InnoDB 表進行索引的二次查詢，也就是回表操作: InnoDB 是以聚集索引的順序來存儲的，對于 InnoDB 來說，二級索引在葉子節點中所保存的是行的主鍵信息，如果是用二級索引查詢數據的話，在查找到相應的鍵值后，還要通過主鍵進行二次查詢才能獲取我們真實所需要的數據。而在覆蓋索引中，二級索引的鍵值中可以獲取所有的數據，避免了對主鍵的二次查詢（回表），減少了 IO 操作，提升了查詢效率。
• 可以把隨機 IO 變成順序 IO 加快查詢效率: 由于覆蓋索引是按鍵值的順序存儲的，對于 IO 密集型的范圍查找來說，對比隨機從磁盤讀取每一行的數據 IO 要少的多，因此利用覆蓋索引在訪問時也可以把磁盤的隨機讀取的 IO 轉變成索引查找的順序 IO。

# 如果只需要查詢 id, code, type 這三列，可建立 code 和 type 的覆蓋索引
SELECT id, code, type FROM t_order
ORDER BY code
LIMIT 1000000, 10;

??注意:

• 當查詢的結果集占表的總行數的很大一部分時，MySQL 查詢優化器可能選擇放棄使用索引，自動轉換為全表掃描。
• 雖然可以使用 FORCE INDEX 強制查詢優化器走索引，但這種方式可能會導致查詢優化器無法選擇更優的執行計劃，效果并不總是理想。

總結

本文總結了幾種常見的深度分頁優化方案:

1. 范圍查詢: 基于 ID 連續性進行分頁，通過記錄上一頁最后一條記錄的 ID 來獲取下一頁數據。適合 ID 連續且按 ID 查詢的場景，但在 ID 不連續或需要按其他字段排序時存在局限。
2. 子查詢: 先通過子查詢獲取分頁的起始主鍵值，再根據主鍵進行篩選分頁。利用主鍵索引提高效率，但子查詢會生成臨時表，復雜場景下性能不佳。
3. 延遲關聯 (INNER JOIN): 使用 INNER JOIN 將分頁操作轉移到主鍵索引上，減少回表次數。相比子查詢，延遲關聯的性能更優，適合大數據量的分頁查詢。
4. 覆蓋索引: 通過索引直接獲取所需字段，避免回表操作，減少 IO 開銷，適合查詢特定字段的場景。但當結果集較大時，MySQL 可能會選擇全表掃描。