高級(jí)SQL優(yōu)化之LIMIT子句下推

作者：其實(shí)我是一個(gè)木匠 2023-10-23 09:19:47

本文所使用的執(zhí)行計(jì)劃可視化工具為PawSQL Explain Visualizer , 支持MySQL、PostgreSQL、openGauss等數(shù)據(jù)庫(kù)。

問題定義

經(jīng)常聽到有人說(shuō)LIMIT影響SQL查詢性能，其實(shí)單純的LIMIT子句不會(huì)影響SQL性能，如果有影響，也是好的影響，特別是子查詢中l(wèi)imit語(yǔ)句，可以限制中間結(jié)果集的大小，從而為減少后續(xù)處理的數(shù)據(jù)量。本文來(lái)討論如何對(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), 原來(lái)需要完成全部的236914行的索引掃描，執(zhí)行時(shí)間為114ms；而優(yōu)化后由于限制了30行，所以可以掃描了7967行后就可以提前結(jié)束，掃描時(shí)間也減少到2.99ms，提升了38倍。
對(duì)于上游節(jié)點(diǎn)(節(jié)點(diǎn)#4)，原來(lái)需要對(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ù)庫(kù)默認(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