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