【华为云MySQL技术专栏】GaussDB(for MySQL)查询优化之Limit Offset下推
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)
针对上文所述的查询示例Q1、Q2、Q3。图4展示了开启与关闭Limit Offset下推功能的性能对比:
图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引擎之间交互的数据量,减少二级索引回表的开销,显著提高查询性能。
- 点赞
- 收藏
- 关注作者
评论(0)