Limit Offset下推

当需要查询大量数据的时候，引擎层访问数据、SQL层的交互和计算都会占用大量的开销。PolarDB MySQL版会将Limit Offset下推到引擎层进行计算，极大的减少性能开销，提升查询性能。

前提条件

集群版本需为PolarDB MySQL版8.0版本且修订版本需满足如下条件：
8.0.1.1.16或以上。
8.0.2.2.0 或以上。

使用限制

查询SQL中的Offset取值要大于512。

您可以配置ignore_polar_optimizer_rule为ON，忽略该限制。

使用方法

通过系统参数loose_optimizer_switch中的limit_offset_pushdown开启Limit Offset下推优化功能。

示例

本文基于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也可以被下推。
  下面的示例中查询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
复制