問題定義

深分頁指的是翻頁請求中的頁碼數非常大,OFFSET數值非常大的情況。如果直接使用LIMIT offset, limit的方式進行分頁,那么在OFFSET超過10000時,性能會明顯下降。原因是LIMIT語句會先獲取符合條件的offset+n行數據，然后再丟棄掉前offset行，返回后n行數據。也就是說limit 10000,10，就會掃描100010行，而limit 0,10，只掃描10行。這里需要回表10010次，大量的時間都在回表這個上面。

select * from orders where O_ORDERSTATUS ='1' order by O_ORDERKEY limit 10000, 10;

解決方案

優化的重點就在減少回表上，采用的方法是子查詢+覆蓋索引，對于以上的SQL，可以重寫為：

select o.*
from orders as o, (select o.O_ORDERKEY
                 from orders as o where o.O_ORDERSTATUS = '1'
                 order by o.O_ORDERKEY limit 10 offset 10000) as orders_dt
where o.O_ORDERKEY = orders_dt.O_ORDERKEY

適用條件：

• 查詢是一個單表查詢
• 查詢涉及的字段大于4（如果小于4，可以創建一個覆蓋索引滿足避免回表的目的）
• 此表上存在唯一性索引
• 查詢中無分組聚集
• offset超過指定閾值（默認10000）

性能評估

• 優化前SQL的執行計劃如下，執行時間為10.819ms，代價為1883.95:

• 改寫后的執行計劃如下，執行時間為1.889ms，代價為1986.78

從執行計劃可以看到，左側就是通過索引獲得滿足符合條件的十個記錄編號（主鍵），然后和數據表關聯獲取所需的字段。雖然代價估計是更大了，但是時間的執行時間卻變小了，性能提升了534.96%。

在PawSQL中實現了深分頁的自動優化，用戶可以指定深分頁的深度，當分頁的深度超過指定閾值，PawSQL可以自動進行此優化。

關于PawSQL

PawSQL專注數據庫性能優化的自動化和智能化，支持MySQL，PostgreSQL，Opengauss等，提供的SQL優化產品包括：

• PawSQL Cloud，在線自動化SQL優化工具，支持SQL審查，智能查詢重寫、基于代價的索引推薦，適用于數據庫管理員及數據應用開發人員。
• PawSQL Advisor，IntelliJ 插件, 適用于數據應用開發人員，可以IDEA/DataGrip應用市場通過名稱搜索“PawSQL Advisor”安裝。
• PawSQL Engine, 是PawSQL系列產品的后端優化引擎，可以以docker鏡像的方式獨立安裝部署，并通過http/json的接口提供SQL優化服務。