【华为云MySQL技术专栏】GaussDB(for MySQL)查询优化之Limit Offset下推

GaussDB数据库 2024-08-16

207

1. 背景介绍

在社区版MySQL中，使用LIMIT OFFSET的SELECT语句时，存储引擎层返回所有满足WHERE条件的行给SQL层处理，SQL层过滤offset行，返回n行数据。随着offset的增加，查询的时长也会显著增长。当offset达到百万级别的时候，查询耗时往往超出了业务可接受的范畴。

SELECT * FROM lineitem LIMIT 10000000,10;
或者
SELECT * FROM lineitem LIMIT 10 OFFSET 10000000;

为提升此类SQL语句的性能，GaussDB(for MySQL)引入了Limit Offset下推优化策略，即将offset的计算任务下推至InnoDB存储引擎，从而避免offset范围内的行被转换和传输到SQL引擎层，并从以下两个场景实现加速：

1）节省InnoDB存储引擎和SQL引擎层之间的多次交互；

2）当查询语句访问二级索引，需要回表获取其他的列信息时，InnoDB层对offset提前过滤可以消除回表的性能开销。

2. 原理介绍

GaussDB(for MySQL)推出的两个新特性，通过OP和RCR的结合，将LIMIT OFFSET的SELECT大数据量查询的性能提升一到两个数量级。

• Offset Pushdown( offset下推，下文简称OP)

• Redundant Condition Removal (冗余条件删除，下文简称 RCR)

2.1 Offset Pushdown

OP赋予GaussDB(for MySQL)存储引擎InnoDB处理offset的能力。当OP启用时，SQL层会评估offset是否可以下推至存储引擎进行处理，并将下推信息传递给存储引擎。SQL层不再对存储引擎返回的行进行offset 处理，取而代之的是存储引擎层直接跳过offset 范围内的行，仅返回后续行，即查询所需要的行。

首先，通过启用OP，offset 范围内的行不会再传输到SQL层，从而节省了存储引擎和SQL层之间多次来回交互时间；其次，对于非覆盖索引扫描（non-covering index，即查询访问二级索引之后还必须访问表），直接跳过offset范围内的行，可以节省对这些行回表访问的开销。这种对offset 的提前处理的方式，可以节省数据处理时间，特别是当offset 非常大时。

OP是否生效取决于WHERE条件能否完全下推到存储引擎处理。如果WHERE条件能够完全下推到存储引擎，并使其能够基于索引进行筛选，减少需要处理的数据量，那么，OP就能有效地优化查询性能。

2.2 Redundant Condition Removal

RCR的优化思路：当进行索引范围扫描时，SQL 层通常会对存储引擎返回的行执行冗余检查，因为它不知道存储引擎已经执行了这些检查，而RCR 就是让 SQL 层了解到这一点。为了使 OP 成为可能，除了要求WHERE条件能够被存储引擎独立且完整地评估，SQL 层还必须了解这点，从而避免冗余检查。

OP功能的实现方式与索引条件下推 (Index Condition Pushdown，ICP) 类似。对于某些查询，ICP通过将整个 WHERE 子句下推到存储引擎来启用 OP。而RCR在 ICP 执行之前会先评估查询条件是否冗余，并移除冗余条件，以确保ICP不会处理冗余的条件检查。RCR很好地补充了OP特性的适用范围，允许更多查询使用 OP。

3. 场景约束

• 只支持单表的SELECT查询，查询使用的表必须是InnoDB表。

• SELECT查询语句的WHERE条件可全部下推到引擎层。

• 不支持SELECT DISTINCT、HAVING、GROUP BY、ROLLUP、聚集函数、WINDOW FUNCITON以及文件排序。

• 不支持涉及多个分区的分区表查询。

• RCR支持<，>，=，<=，>=，BETWEEN，IFNULL。

4. 流程介绍

在SQL层的优化器阶段，判断是否满足Limit Offset下推的条件。如果满足，则设置offset的值为0，并通知InnoDB层需跳过的offset的值，即SQL层对InnoDB层返回的结果不再进行offset的过滤。

在InnoDB层，row_search_mvcc函数中根据SQL层传递的下推offset值，判断是否需要跳过当前row。如果判定需要则跳过，继续读取下一行，依次类推。(图1示意了Limit Offset下推查询优化的处理流程)

图1 Limit Offset下推查询优化的处理流程图

4.1 RCR生效OP场景

GaussDB(for MySQL)做了RCR优化，这使得SQL层能够感知InnoDB层返回的记录都是经过过滤的，这意味着SQL层不需要再次过滤。该优化扩展了Offset Pushdown的生效范围，如图2所示。

图2 RCR生效Offset Pushdown

假设有一个二级索引(a,b,c)，WHERE条件中的范围条件如下：

• a > x

• a = x AND b > y

• a = x AND b = y AND c > z

相关SQL语句如下：

create table t0(a int,b int,c int,index a_b_c(a,b,c));
insert into t0 values(1,2,3),(4,5,6),(7,8,9);
mysql> explain select * from t0 where a > 1 limit 10000,1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | t0    | NULL       | range | a_b_c         | a_b_c | 5       | NULL |    2 |   100.00 | Using offset pushdown; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select * from t0 where a = 1 and b > 1 limit 10000,1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | t0    | NULL       | range | a_b_c         | a_b_c | 10      | NULL |    1 |   100.00 | Using offset pushdown; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)


mysql> explain select * from t0 where a = 1 and b = 1 and c > 1 limit 10000,1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | t0    | NULL       | range | a_b_c         | a_b_c | 15      | NULL |    1 |   100.00 | Using offset pushdown; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

如上SQL语句的WHERE条件经过RCR，SQL层去除冗余的条件，生效OP，提升性能。

4.2 ICP生效OP场景

对于ICP生效的SQL语句，SQL层在判断满足OP的约束条件之后，会向InnoDB层获取n条记录，InnoDB层则会跳过满足ICP条件的p条记录，把满足ICP条件的n行记录返回给SQL层，如图3所示。

图3 ICP生效Offset Pushdown的处理流程图

具体步骤如下：

a) InnoDB 获取一个record;

b) 如果该record可见，则跳转c)，如果不确定，检查ICP是否匹配，如果匹配，则跳转到e), 如果不匹配，则跳转到a);

c) 如果记录被标记为已删除，跳转到a), 如果没有被标记为已删除，跳转到d);

d) 检查ICP是否匹配，如果不匹配，则跳转到a)，如果匹配，跳转到f);

e) InnoDB使用聚集索引检查MVCC版本，如果检查record是可见的，则跳转到f)，如果不是，则跳转到a);

f) 如果跳过的记录个数小于Limit Offset下推的值，则跳转到a)获得下一个record;

g) 返回record给SQL层，SQL层发送结果给客户端;

举例如下：

create table t1(a int, b int, INDEX(b));
insert into t1 values(4,4),(5,5),(6,6);
set rds_empty_redundant_check_in_range_scan = off;
mysql> explain select a,b from t1 where b>2 limit 100 offset 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | b             | b    | 5       | NULL |    3 |   100.00 | Using offset pushdown; Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

5. 使用方法

除了使用特性开关来生效或者不生效Limit Offset下推优化，还可以使用hint：

OFFSET_PUSHDOWN(table_name)：生效Limit Offset下推优化
NO_OFFSET_PUSHDOWN(table_name)：不生效Limit Offset下推优化

示例如下：

基于TPCH的Schema进行举例，特性开关打开或者使用hint方式可以生效，执行EXPLAIN SQL查看执行计划时，Extra列会展示为Using offset pushdown。

1）特性开关打开：

mysql> 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)

2）使用hint：

mysql> 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)


mysql> 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)

6. 性能对比

采用TPC-H测试模型，Scale Factor(Gigabytes)为10的数据量，测试如下三种场景，lineitem表结构如下：

CREATE TABLE `lineitem` (
  `L_ORDERKEY` int NOT NULL,
  `L_PARTKEY` int NOT NULL,
  `L_SUPPKEY` int NOT NULL,
  `L_LINENUMBER` int NOT NULL,
  `L_QUANTITY` decimal(15,2) NOT NULL,
  `L_EXTENDEDPRICE` decimal(15,2) NOT NULL,
  `L_DISCOUNT` decimal(15,2) NOT NULL,
  `L_TAX` decimal(15,2) NOT NULL,
  `L_RETURNFLAG` char(1) NOT NULL,
  `L_LINESTATUS` char(1) NOT NULL,
  `L_SHIPDATE` date NOT NULL,
  `L_COMMITDATE` date NOT NULL,
  `L_RECEIPTDATE` date NOT NULL,
  `L_SHIPINSTRUCT` char(25) NOT NULL,
  `L_SHIPMODE` char(10) NOT NULL,
  `L_COMMENT` varchar(44) NOT NULL,
  KEY `i_l_partkey` (`L_PARTKEY`),
  KEY `i_l_partkey_suppkey` (`L_PARTKEY`,`L_SUPPKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1）如下SQL语句为Q1，访问主表且无谓词条件：

mysql> 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)

2）带有谓词条件的查询，如下SQL语句为Q2，访问二级索引的Range查询，同时需要回表获取其他列的信息。

mysql> 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)

3）带有谓词条件的查询，如下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)