问题定义
深分页指的是翻页请求中的页码数非常大,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优化服务。