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、聚集函数、窗口函数以及文件排序。
• 不支持涉及多个分区的分区表查询。

使用方法

示例

• 通过修改参数打开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 性能对比