当需要查询大量数据的时候，引擎层访问数据、SQL层的交互和计算都会占用大量的开销。PolarDB MySQL版会将Limit Offset下推到引擎层进行计算，极大的减少性能开销，提升查询性能。
前提条件
集群版本需为PolarDB MySQL版8.0版本且修订版本需满足如下条件：
8.0.1.1.16或以上。
8.0.2.2.0 或以上。
如何查看集群版本，请参见查询版本号。
背景信息
社区MySQL的Limit操作完全由SQL层完成，数据从引擎层读取后需要交给SQL层处理，然后过滤掉Offset。当查询二级索引，需要访问主表列的时候，引擎层还会先回表获取所有需要的列信息。而对于SQL层无条件过滤的场景（包含SQL层谓词完全下推到引擎层），Limit操作处理OffSet的数据不需要经过任何计算就会被过滤掉，引擎层与SQL层的交互和回表的代价会导致分页查询随着分页数增加而越来越慢。PolarDB MySQL版会把Limit Offset下推到引擎层，这些数据直接在引擎中扫描过滤，且选择二级索引时这些数据不需要进行回表。
使用限制
查询SQL中的Offset取值要大于512。
您可以配置ignore_polar_optimizer_rule为ON，忽略该限制。具体操作请参见设置集群参数和节点参数。
参数名称 | 级别 | 描述 |
ignore_polar_optimizer_rule | Global、Session | 忽略Polar优化限制规则的控制开关。
|
使用方法
通过系统参数loose_optimizer_switch中的limit_offset_pushdown开启Limit Offset下推优化功能。具体操作请参见设置集群参数和节点参数。
参数名称 | 级别 | 描述 |
loose_optimizer_switch | Global、Session | 查询优化的总控制开关。其中，计算下推的子控制开关如下：
|
示例
本文基于TPCH的Schema进行举例。当开启Limit Offset下推功能，执行EXPLAIN SQL查看执行计划时，Extra列会展示为Using limit-offset pushdown。
普通Limit Offset场景
下面的示例中查询Q1访问主表且无谓词条件
EXPLAIN SELECT * FROM lineitem LIMIT 10000000, 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: lineitem partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 59440464 filtered: 100.00 Extra: Using limit-offset pushdown带有谓词条件的Limit Offset场景
对于带有谓词条件的查询，如果谓词条件能够完全体现在引擎的范围扫描中，那么谓词条件就会被谓词完全下推功能移除。此时Limit Offset也可以被下推。
下面的示例中查询Q2访问主键，且包含主键范围条件
EXPLAIN SELECT * FROM lineitem WHERE l_orderkey > 10 AND l_orderkey < 60000000 LIMIT 10000000, 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: lineitem partitions: NULL type: range possible_keys: PRIMARY,i_l_orderkey,i_l_orderkey_quantity key: PRIMARY key_len: 4 ref: NULL rows: 29720232 filtered: 100.00 Extra: Using limit-offset pushdown下面的示例中查询Q3访问二级索引，且包含二级索引范围条件，同时需要回表获取其他列的信息
EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 LIMIT 5000000, 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: lineitem partitions: NULL type: range possible_keys: i_l_partkey,i_l_suppkey_partkey key: i_l_suppkey_partkey key_len: 5 ref: NULL rows: 11123302 filtered: 100.00 Extra: Using limit-offset pushdown
带有Order by且可以利用索引排序的Limit Offset场景
在标准的分页查询中，需要使用Order by明确查询结果顺序。对于选择的索引提供Order排序的场景，在SQL层谓词被移除后，也可以将Limit Offset下推。
EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 ORDER BY l_partkey LIMIT 5000000, 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: lineitem partitions: NULL type: range possible_keys: i_l_partkey,i_l_suppkey_partkey key: i_l_suppkey_partkey key_len: 5 ref: NULL rows: 11123302 filtered: 100.00 Extra: Using limit-offset pushdown
性能效果
基于TPCH 10 scale的数据，针对上文所述的查询示例Q1、Q2、Q3。开启与关闭Limit Offset下推功能的性能对比如下：
- 本页导读 （0）