當需要查詢大量資料的時候，引擎層訪問資料、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下推功能的效能對比如下：