高级SQL优化之LIMIT子句下推

数据库 其他数据库
本文所使用的执行计划可视化工具为PawSQL Explain Visualizer , 支持MySQL、PostgreSQL、openGauss等数据库。

问题定义

经常听到有人说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`只有一个表引用,且是一个子查询
  • 子查询为单个查询或是`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优化服务。
责任编辑:姜华 来源: PawSQL
相关推荐

2023-11-07 07:50:55

LIMIT子句下推优化

2023-11-28 07:54:18

2023-10-09 07:42:49

PawSQL数据库管理

2024-07-17 08:15:09

2010-09-26 14:46:19

SQL WHERE子句

2010-09-26 09:50:36

SQL Where子句

2018-01-09 16:56:32

数据库OracleSQL优化

2023-11-03 07:29:51

QL优化分组字段顺序优化

2023-10-16 07:49:25

PawSQL数据库

2010-11-09 10:10:08

SQL Server

2010-09-26 14:40:25

SQL FROM子句

2023-02-26 23:43:43

MySQL数据库分页查询

2021-07-16 23:01:03

SQL索引性能

2018-04-19 09:02:14

SQL ServerSQL性能优化

2011-06-08 15:08:38

MySQLWhere优化

2021-05-11 09:01:37

SQL优化索引

2010-11-25 10:12:02

MySQL查询优化

2021-07-26 18:23:23

SQL策略优化

2011-08-24 17:23:10

2009-05-12 10:55:06

SQLFOR XML模式
点赞
收藏

51CTO技术栈公众号