全部产品
Search
文档中心

云原生数据库 PolarDB:Limit Offset下推

更新时间:Mar 26, 2024

当需要查询大量数据的时候,引擎层访问数据、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_ruleON,忽略该限制。具体操作请参见设置集群参数和节点参数

参数名称

级别

描述

ignore_polar_optimizer_rule

Global、Session

忽略Polar优化限制规则的控制开关。

  • ON:开启忽略Polar优化限制规则开关。

  • OFF(默认值):关闭忽略Polar优化限制规则开关。

使用方法

通过系统参数loose_optimizer_switch中的limit_offset_pushdown开启Limit Offset下推优化功能。具体操作请参见设置集群参数和节点参数

参数名称

级别

描述

loose_optimizer_switch

Global、Session

查询优化的总控制开关。其中,计算下推的子控制开关如下:

  • limit_offset_pushdown:Limit Offset下推优化开关

    • ON(默认值):开启Limit Offset下推优化开关。

    • OFF:关闭Limit Offset下推优化开关。

  • detach_range_condition:谓词完全下推优化开关

    • ON(默认值):开启谓词完全下推优化开关。

    • OFF:关闭谓词完全下推优化开关。

示例

本文基于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下推功能的性能对比如下:优化效果