# LIMIT OFFSET下推 #### 操作场景 在社区版MySQL中，LIMIT(N)/OFFSET(P)的SELECT语句会使引擎层返回所有满足WHERE条件的行给SQL层处理，SQL层会丢弃OFFSET对应的P行，然后返回N行数据。当查询二级索引需要访问主表列时，引擎层会先返回表以获取所有需要的列信息。如果OFFSET的P远大于LIMIT的N，这将导致引擎层向SQL层反馈大量数据进行处理。 TaurusDB提供的LIMIT OFFSET下推功能可以将LIMIT OFFSET的处理下推到引擎层，从而提升查询效率。 #### 约束限制 - 仅支持单表的SELECT查询。 - SELECT查询语句的WHERE条件可全部下推到引擎层。 - 不支持SELECT DISTINCT、HAVING、GROUP BY、ROLLUP、聚集函数、窗口函数以及文件排序。 - 不支持涉及多个分区的分区表查询。 #### 使用方法 - [通过参数控制] 通过参数optimizer_switch设置LIMIT OFFSET下推功能。具体操作请参见[修改实例参数](https://support.huaweicloud.com/usermanual-taurusdb/taurusdb_configuration.html)。 表1参数说明 | **参数名** **称** | **级别** | **描述** | |:---|:---|:---| | optimizer_switch | Global，Session | 查询优化的总控制开关。 offset_pushdown：LIMIT OFFSET下推优化开关，默认值为OFF。 - ON：开启LIMIT OFFSET下推优化开关。 - OFF：关闭LIMIT OFFSET下推优化开关。 | - [通过HINT控制] - OFFSET_PUSHDOWN(*table_name*)：生效单条SQL语句的LIMIT OFFSET下推优化。 - NO_OFFSET_PUSHDOWN(*table_name*)：不生效LIMIT OFFSET下推优化。 #### 示例 - 通过修改参数打开LIMIT OFFSET下推优化功能，执行EXPLAIN SQL查看执行计划时，Extra列会展示为Using offset pushdown。 ``` EXPLAIN SELECT * FROM lineitem LIMIT 10000000,10; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 1 | SIMPLE | lineitem | NULL | ALL | NULL | NULL | NULL | NULL | 59281262 | 100.00 | Using offset pushdown 1 row in set, 1 warning (0.00 sec) ``` - 使用HINT开启LIMIT OFFSET下推优化。 ``` EXPLAIN SELECT /*+ OFFSET_PUSHDOWN() */ * FROM lineitem LIMIT 10000000,10; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 1 | SIMPLE | lineitem | NULL | ALL | NULL | NULL | NULL | NULL | 59281262 | 100.00 | Using offset pushdown | 1 row in set, 1 warning (0.00 sec) ``` - 使用HINT关闭LIMIT OFFSET下推优化。 ``` EXPLAIN SELECT /*+ NO_OFFSET_PUSHDOWN() */ * FROM lineitem LIMIT 10000000,10; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | 1 | SIMPLE | lineitem | NULL | ALL | NULL | NULL | NULL | NULL | 59281262 | 100.00 | NULL | 1 row in set, 1 warning (0.00 sec) ``` #### 性能测试 - 如下SQL语句为Q1，访问主表且无谓词条件。 ``` EXPLAIN SELECT * FROM lineitem LIMIT 10000000,10; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | 1 | SIMPLE | lineitem | NULL | ALL | NULL | NULL | NULL | NULL | 59281262 | 100.00 | Using offset pushdown | 1 row in set, 1 warning (0.00 sec) ``` - 如下SQL语句为Q2，带有谓词条件的查询。访问二级索引，且包含二级索引范围条件，同时需要回表获取其他列的信息。 ``` EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 LIMIT 5000000, 10; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | 1 | SIMPLE | lineitem | NULL | range | i_l_partkey_suppkey,i_l_partkey | i_l_partkey | 4 | NULL | 10949662 | 100.00 | Using offset pushdown; Using index condition | 1 row in set, 1 warning (0.00 sec) ``` - 如下SQL语句为Q3, 带有谓词条件的查询，带有Order by且可以利用索引排序。 ``` mysql> EXPLAIN SELECT * FROM lineitem WHERE l_partkey > 10 AND l_partkey < 200000 ORDER BY l_partkey LIMIT 5000000, 10; | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | | 1 | SIMPLE | lineitem | NULL | range | i_l_partkey_suppkey,i_l_partkey | i_l_partkey | 4 | NULL | 10949662 | 100.00 | Using offset pushdown; Using index condition | 1 row in set, 1 warning (0.00 sec) ``` 基于TPCH 10 scale的数据，针对上述的查询示例Q1、Q2、Q3。开启与关闭LIMIT OFFSET下推功能的性能对比如下。 图1性能对比 ![](https://support.huaweicloud.com/kerneldesc-taurusdb/figure/zh-cn_image_0000002539705673.png "点击放大")