深分页指的是翻页请求中的页码数非常大,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
适用条件:
从执行计划可以看到,左侧就是通过索引获得满足符合条件的十个记录编号(主键),然后和数据表关联获取所需的字段。虽然代价估计是更大了,但是时间的执行时间却变小了,性能提升了534.96%。
在PawSQL中实现了深分页的自动优化,用户可以指定深分页的深度,当分页的深度超过指定阈值,PawSQL可以自动进行此优化。
PawSQL专注数据库性能优化的自动化和智能化,支持MySQL,PostgreSQL,Opengauss等,提供的SQL优化产品包括: