高級SQL優(yōu)化之LIMIT子句下推
問題定義
經(jīng)常聽到有人說LIMIT影響SQL查詢性能，其實單純的LIMIT子句不會影響SQL性能，如果有影響，也是好的影響，特別是子查詢中l(wèi)imit語句，可以限制中間結(jié)果集的大小，從而為減少后續(xù)處理的數(shù)據(jù)量。本文來討論如何對LIMIT子句進(jìn)行下推優(yōu)化。
和謂詞下推優(yōu)化類似，Limit子句下推優(yōu)化通過盡可能地下壓Limit子句，提前過濾掉部分?jǐn)?shù)據(jù), 減少中間結(jié)果集的大小，減少后續(xù)計算需要處理的數(shù)據(jù)量, 以提高查詢性能。
譬如如下的案例，在外查詢有一個Limit子句，可以將其下推至內(nèi)層查詢執(zhí)行：
select *
from (select c_nationkey nation, 'C' as type, count(1) num
from customer
group by c_nationkey
union
select s_nationkey nation, 'S' as type, count(1) num
from supplier
group by nation) as nation_s
order by nation limit 20, 10重寫之后的SQL如下:
select *
from (
(select customer.c_nationkey as nation, 'C' as `type`, count(1) as num
from customer
group by customer.c_nationkey
order by customer.c_nationkey limit 30)
union
(select supplier.s_nationkey as nation, 'S' as `type`, count(1) as num
from supplier
group by supplier.s_nationkey
order by supplier.s_nationkey limit 30)) as nation_s
order by nation_s.nation limit 20, 10適用條件
- 外查詢有一個`LIMIT`子句。
- 外查詢沒有`GROUP BY`子句。
- 外查詢的`FROM`只有一個表引用，且是一個子查詢。
- 子查詢?yōu)閱蝹€查詢或是`UNION/UNION ALL`連接的多個子查詢。
性能驗證
1、優(yōu)化前的執(zhí)行計劃
2、優(yōu)化后的執(zhí)行計劃
3、優(yōu)化過程解析
從優(yōu)化后的執(zhí)行計劃我們可以看到，在UNION操作之前兩個子查詢分別新增了一個LIMIT節(jié)點，限制了中間結(jié)果的返回行數(shù)為30行(offset + limit), 對上下游節(jié)點都有性能提升的影響。
- 對于下游節(jié)點，譬如節(jié)點#10(優(yōu)化前為節(jié)點#8), 原來需要完成全部的236914行的索引掃描，執(zhí)行時間為114ms；而優(yōu)化后由于限制了30行，所以可以掃描了7967行后就可以提前結(jié)束，掃描時間也減少到2.99ms，提升了38倍。
- 對于上游節(jié)點(節(jié)點#4)，原來需要對38806行數(shù)據(jù)進(jìn)行去重;而在優(yōu)化后，只需要對60行數(shù)據(jù)進(jìn)行去重；執(zhí)行時間從43.3ms減少到0.1ms，提升了433倍。
單純由于LIMIT子句下推，整體的執(zhí)行時間從176.93ms減少為3.54ms，整體性能提升了4898.02%。
PawSQL對于LIMIT子句的優(yōu)化
PawSQL針對所有數(shù)據(jù)庫默認(rèn)開啟LIMIT子句下推優(yōu)化。
- 自動優(yōu)化：用戶輸入待優(yōu)化SQL后，PawSQL對其進(jìn)行自動化優(yōu)化，案例SQL的優(yōu)化詳情如下。
- 當(dāng)然你也可以在創(chuàng)建優(yōu)化任務(wù)時隨時關(guān)閉或開啟此優(yōu)化。
關(guān)于PawSQL
本文所使用的執(zhí)行計劃可視化工具為PawSQL Explain Visualizer , 支持MySQL、PostgreSQL、openGauss等數(shù)據(jù)庫。
PawSQL專注數(shù)據(jù)庫性能優(yōu)化的自動化和智能化，支持MySQL，PostgreSQL，Opengauss等，提供的SQL優(yōu)化產(chǎn)品包括
- PawSQL Cloud，在線自動化SQL優(yōu)化工具，支持SQL審查，智能查詢重寫、基于代價的索引推薦，適用于數(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鏡像的方式獨立安裝部署，并通過http/json的接口提供SQL優(yōu)化服務(wù)。
