全部產品
Search
文件中心

PolarDB:Limit Offset下推

更新時間:Jul 06, 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下推功能的效能對比如下:最佳化效果