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

问题定义

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