高级SQL优化系列之深分页优化

2023年 10月 9日 57.6k 0

问题定义

深分页指的是翻页请求中的页码数非常大,OFFSET数值非常大的情况。如果直接使用LIMIT offset, limit的方式进行分页,那么在OFFSET超过10000时,性能会明显下降。原因是LIMIT语句会先获取符合条件的offset+n行数据,然后再丢弃掉前offset行,返回后n行数据。也就是说limit 10000,10,就会扫描100010行,而limit 0,10,只扫描10行。这里需要回表10010次,大量的时间都在回表这个上面。

select * from orders where O_ORDERSTATUS ='1' order by O_ORDERKEY limit 10000, 10;

解决方案

优化的重点就在减少回表上,采用的方法是子查询+覆盖索引,对于以上的SQL,可以重写为:

select o.*
from orders as o, (select o.O_ORDERKEY
                 from orders as o where o.O_ORDERSTATUS = '1'
                 order by o.O_ORDERKEY limit 10 offset 10000) as orders_dt
where o.O_ORDERKEY = orders_dt.O_ORDERKEY

适用条件:

  • 查询是一个单表查询
  • 查询涉及的字段大于4(如果小于4,可以创建一个覆盖索引满足避免回表的目的)
  • 此表上存在唯一性索引
  • 查询中无分组聚集
  • offset超过指定阈值(默认10000)

性能评估

  • 优化前SQL的执行计划如下,执行时间为10.819ms,代价为1883.95:

图片

  • 改写后的执行计划如下,执行时间为1.889ms,代价为1986.78

图片

从执行计划可以看到,左侧就是通过索引获得满足符合条件的十个记录编号(主键),然后和数据表关联获取所需的字段。虽然代价估计是更大了,但是时间的执行时间却变小了,性能提升了534.96%。

在PawSQL中实现了深分页的自动优化,用户可以指定深分页的深度,当分页的深度超过指定阈值,PawSQL可以自动进行此优化。

图片

关于PawSQL

PawSQL专注数据库性能优化的自动化和智能化,支持MySQL,PostgreSQL,Opengauss等,提供的SQL优化产品包括:

  • PawSQL Cloud,在线自动化SQL优化工具,支持SQL审查,智能查询重写、基于代价的索引推荐,适用于数据库管理员及数据应用开发人员。
  • PawSQL Advisor,IntelliJ 插件, 适用于数据应用开发人员,可以IDEA/DataGrip应用市场通过名称搜索“PawSQL Advisor”安装。
  • PawSQL Engine, 是PawSQL系列产品的后端优化引擎,可以以docker镜像的方式独立安装部署,并通过http/json的接口提供SQL优化服务。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论