問題定義

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

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

解決方案

優(yōu)化的重點(diǎn)就在減少回表上，采用的方法是子查詢+覆蓋索引，對于以上的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

適用條件：

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

性能評估

• 優(yōu)化前SQL的執(zhí)行計(jì)劃如下，執(zhí)行時(shí)間為10.819ms，代價(jià)為1883.95:

• 改寫后的執(zhí)行計(jì)劃如下，執(zhí)行時(shí)間為1.889ms，代價(jià)為1986.78

從執(zhí)行計(jì)劃可以看到，左側(cè)就是通過索引獲得滿足符合條件的十個(gè)記錄編號（主鍵），然后和數(shù)據(jù)表關(guān)聯(lián)獲取所需的字段。雖然代價(jià)估計(jì)是更大了，但是時(shí)間的執(zhí)行時(shí)間卻變小了，性能提升了534.96%。

在PawSQL中實(shí)現(xiàn)了深分頁的自動(dòng)優(yōu)化，用戶可以指定深分頁的深度，當(dāng)分頁的深度超過指定閾值，PawSQL可以自動(dòng)進(jìn)行此優(yōu)化。

關(guān)于PawSQL

PawSQL專注數(shù)據(jù)庫性能優(yōu)化的自動(dòng)化和智能化，支持MySQL，PostgreSQL，Opengauss等，提供的SQL優(yōu)化產(chǎn)品包括：

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