問題定義

經常聽到有人說LIMIT影響SQL查詢性能，其實單純的LIMIT子句不會影響SQL性能，如果有影響，也是好的影響，特別是子查詢中limit語句，可以限制中間結果集的大小，從而為減少后續處理的數據量。本文來討論如何對LIMIT子句進行下推優化。

和謂詞下推優化類似，Limit子句下推優化通過盡可能地下壓Limit子句，提前過濾掉部分數據, 減少中間結果集的大小，減少后續計算需要處理的數據量, 以提高查詢性能。

譬如如下的案例，在外查詢有一個Limit子句，可以將其下推至內層查詢執行：

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`只有一個表引用，且是一個子查詢。

1. 子查詢為單個查詢或是`UNION/UNION ALL`連接的多個子查詢。

性能驗證

1、優化前的執行計劃

2、優化后的執行計劃

3、優化過程解析

從優化后的執行計劃我們可以看到，在UNION操作之前兩個子查詢分別新增了一個LIMIT節點，限制了中間結果的返回行數為30行(offset + limit), 對上下游節點都有性能提升的影響。

• 對于下游節點，譬如節點#10(優化前為節點#8), 原來需要完成全部的236914行的索引掃描，執行時間為114ms；而優化后由于限制了30行，所以可以掃描了7967行后就可以提前結束，掃描時間也減少到2.99ms，提升了38倍。
• 對于上游節點(節點#4)，原來需要對38806行數據進行去重;而在優化后，只需要對60行數據進行去重；執行時間從43.3ms減少到0.1ms，提升了433倍。

單純由于LIMIT子句下推，整體的執行時間從176.93ms減少為3.54ms，整體性能提升了4898.02%。

PawSQL對于LIMIT子句的優化

PawSQL針對所有數據庫默認開啟LIMIT子句下推優化。

• 自動優化：用戶輸入待優化SQL后，PawSQL對其進行自動化優化，案例SQL的優化詳情如下。

• 當然你也可以在創建優化任務時隨時關閉或開啟此優化。

關于PawSQL

本文所使用的執行計劃可視化工具為PawSQL Explain Visualizer , 支持MySQL、PostgreSQL、openGauss等數據庫。

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

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