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

举报
GaussDB 数据库 发表于 2024/08/16 09:32:08 2024/08/16
【摘要】 GaussDB(for MySQL)的Limit Offset下推优化,旨在解决带有Limit Offset查询语句的性能问题。通过将Limit Offset下推到存储引擎层,降低存储引擎和SQL引擎之间交互的数据量,减少二级索引回表的开销,显著提高查询性能。

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.png

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

4.1 RCR生效OP场景

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

2.png

图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.png

图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. 使用方法

5.PNG

除了使用特性开关来生效或者不生效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)

针对上文所述的查询示例Q1、Q2、Q3。4展示了开启与关闭Limit Offset下推功能的性能对比:

4.PNG

图4 开启与关闭Limit Offset下推功能性能对比

基于TPC-H测试模型,Scale Factor(Gigabytes)为10的数据量,Q1提升5.56倍,Q2提升33.07倍,Q3提升33.02倍。


7. 总结

本文介绍了GaussDB(for MySQL)的Limit Offset下推优化,旨在解决带有Limit Offset查询语句的性能问题。通过将Limit Offset下推到存储引擎层,降低存储引擎和SQL引擎之间交互的数据量,减少二级索引回表的开销,显著提高查询性能。

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。