高级SQL优化之LIMIT子句下推-益华科技

问题定义

经常听到有人说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, 101.2.3.4.5.6.7.8.9.

重写之后的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, 101.2.3.4.5.6.7.8.9.10.11.12.

适用条件

外查询有一个`LIMIT`子句。外查询没有`GROUP BY`子句。外查询的`FROM`只有一个表引用，且是一个子查询。子查询为单个查询或是`UNION/UNION ALL`连接的IT技术网多个子查询。

性能验证

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优化服务。云服务器