【GaussDB(for MySQL)】 Big IN 查询优化

作者：华为云开发者联盟

2024-05-09
广东
本文字数：4015 字
阅读完需：约 13 分钟

本文分享自华为云社区《【MySQL技术专栏】GaussDB(for MySQL) Big IN查询优化》，作者：GaussDB 数据库。

背景介绍

在生产环境中，经常会遇到客户业务的 SQL 语句进行过滤查询，然后进行聚合处理，并且 IN 谓词列表中包含几千甚至上万个常量值。如下所示，此类语句的执行时间非常长。

MySQL 优化

开源 MySQL 在处理列 IN (const1, const2, .... )时，如果列上面有索引，优化器会选择 Range scan 进行扫描，否则会使用全表扫描方式。range_optimizer_max_mem_size 系统变量控制范围优化过程分析中可使用的最大内存。如果 IN 谓词的列表元素非常多，IN 中每个的内容都会被视为 OR 每个，OR 大约占用 230 字节，如果元素个数很多，则使用更多的内存。如果使用内存会超过定义的最大内存，会使范围优化失效，优化器将改变策略，如转换为全表扫描，从而引发查询的性能下降。

对于这个优化问题，可以通过调整 range_optimizer_max_mem_size 来处理。range_optimizer_max_mem_size 定义的内存是会话级别的，每个会话执行该类型的语句，都会占用相同的内存，在大并发场景下，会导致实例内存占用过高，实例 OOM 风险。

对于范围查询，MySQL 定义了 eq_range_index_dive_limit 系统变量，来控制在处理等值范围查询时，优化器是否进行索引潜水（index div）。索引潜水是利用索引完成元组数的说明，可以得到更准确的信息，从而做出更好的查询策略优化，但是运行时间也长。在 IN 组合数超过一定数量的时候就不适用索引跳水，系统采用静态索引统计信息值来选择索引，这种方法得到的结果一定准确。这可能导致 MySQL 无法很好的利用索引，导致性能回退。

GaussDB(for MySQL)的 Big IN 优化

GaussDB(for MySQL)Big IN 性能问题的方法将大 IN 谓词转换为 IN 子查询。因此解决，IN 谓词的形式为：

column IN (const1, const2, ....)

复制代码

转换为对应的 IN 子查询：

column IN (SELECT ... FROM temporary_table)

复制代码

经过上述的变化，IN 函数查询变成了一个 IN 子查询，并且该子查询是非相关子查询。

对于 IN 非相关子查询，MySQL 优化器提供了半连接物化策略进行优化处理。半连接物化策略就是把子查询结果物化成临时表，然后和外观进行连接。如下图所示：

串联可以有两个顺序：

Materialization-scan：表示从物化表到外观，对物化表进行全表扫描。
Materialization-lookup ：表示从外观到物化表，在物化表中查找数据的时候可以使用主建进行查找。

物化扫描

执行子查询，走索引 auto_distinct_key，同时对结果进行去重；
将上一步的结果保存在临时表模板 1 里；
从临时表中取一行数据，到外观中找到满足补充条件的行；
步骤重复 3，直到遍历临时表结束。

物化查找

先执行子查询；
将上一步得到的结果保存到临时表中；
从外观中取出一行数据，到物化临时表中去查找满足补充条件的行，走物化表的主键，每次扫描 1 行；
重复 3，直至浏览整个外观。

优化器会根据内部外观的大小来选择不同的串联顺序。真实场景中，一般查询的表的数据量很大，上千万甚至上亿；IN 列表中的元素个数远小于表数量，优化器会选择 Materialization-scan 方式进行扫描，外观查询时如果走主键索引，则优化后的总的扫描行数为 N，当 M 远大于 N 时，性能提升会非常明显。

使用方法

rds_in_predicate_conversion_threshold 参数是修改 IN 谓词底部该查询功能开关，当 SQL 语句的 IN 谓词列表中的元素个数超过参数的取值时，将启动该优化策略。通过该变量的值来使用该功能。下面一个简单的例子说明优化的使用：

表结构

create table t1(id int, a int, key idx1(a));

复制代码

查询语句

select * from t1 where a in (1,2,3,4,5);

复制代码

设置 set rds_in_predicate_conversion_threshold = 0 和 set range_optimizer_max_mem_size=1 关闭大 IN 谓词优化功能和范围扫描优化策略，查看上述查询语句的执行计划，结果如下：

> set rds_in_predicate_conversion_threshold = 0;  > set range_optimizer_max_mem_size=1;  > explain select * from t1 where a in (1,2,3,4,5);  结果如下：+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  |  1 | SIMPLE      | t3    | NULL       | ALL  | key1          | NULL | NULL    | NULL |    3 |    50.00 | Using where |  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+  1 row in set, 2 warnings (0.00 sec)  show warnings;  +---------+------+---------------------------------------------------------------------------------------------------------------------------+  | Level   | Code | Message                                                                                                                   |  +---------+------+---------------------------------------------------------------------------------------------------------------------------+  | Warning | 3170 | Memory capacity of 1 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.   |  | Note    | 1003 | /* select#1 */ select `test`.`t3`.`id` AS `id`,`test`.`t3`.`a` AS `a` from `test`.`t3` where (`test`.`t3`.`a` in (3,4,5)) |  +---------+------+---------------------------------------------------------------------------------------------------------------------------+  2 rows in set (0.00 sec)

复制代码

发现上述语句执行的时候报了警告，警告的信息显示因为范围优化过程中使用的内存超过了 range_optimizer_max_mem_size 导致对于该语句没有使用范围限制优化。从而导致扫描的类型变成了 ALL，变为全表扫描。

设置 set rds_in_predicate_conversion_threshold = 3 开启大 IN 谓词优化选项，表示当 IN 谓词列表元素超过 3 个的时候，启动大 IN 队列查询优化策略。执行 EXPLAIN FORMAT=TREE 语句可以查看优化是否生效。

> set rds_in_predicate_conversion_threshold = 3;  > explain format=tree select * from t1 where a in (1,2,3,4,5);  +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  | EXPLAIN                                                                                                                                                                                                                                                        |  +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  | -> Nested loop inner join  (cost=0.70 rows=1)      -> Filter: (t1.a is not null)  (cost=0.35 rows=1)          -> Table scan on t1  (cost=0.35 rows=1)      -> Single-row index lookup on <in_predicate_2> using <auto_distinct_key> (a=t1.a)  (cost=0.35 rows=1)   |  +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  1 row in set (0.00 sec)

复制代码

执行计划中的<in_predicate_*>（*为数字）表为 Big INTool 中构造的临时表，存储了 IN 谓词列表中的所有数据。

使用限制

Big IN 优化支持的查询语句包括以下语句列表：

选择
插入...选择
替换...选择
支持观点
准备好的 STMT

约束与限制

Big IN 转子查询，借助 mysql 提供的子查询优化方案来实现性能，因此在使用上有如下限制，否则反而会降低性能。

不支持无法使用索引的场景
只支持常量 IN LIST（包括 NOW(), ?等不涉及表查询的语句）
不支持存储过程/函数/触发器
不支持不在

典型场景测试对比

表测试结构如下：

CREATE TABLE `sbtest1` (    `id` int NOT NULL AUTO_INCREMENT,    `k` int NOT NULL DEFAULT '0',    `c` char(120) NOT NULL DEFAULT '',    `pad` char(60) NOT NULL DEFAULT '',    PRIMARY KEY (`id`),    KEY `k_1` (`k`)  ) ENGINE=InnoDB;  表的数据量为1000w。> select count(*) from sbtest1;  +----------+  | count(*) |  +----------+  | 10000000 |  +----------+

复制代码

查询语句如下，其中条件字段是有索引，IN 列表里包含 1 万个常量数字。

select count(*) from sbtest1 where k in (2708275,5580784,7626186,8747250,228703,4589267,5938459,6982345,2665948,4830545,4929382,8723757,354179,1903875,5111120,5471341,7098051,3113388,2584956,6550102,2842606,2744112,7077924,4580644,5515358,1787655,6391388,6044316,2658197,5628504,413887,6058866,3321587,1430333,445303,7373496,9133196,6760595,4735642,4756387,9845147,9362192,7271805,4351748,6625915,3813276,4236692,8308973,4407131,9481423,3301846,432577,810938,3830320,6120078,6765157,6456566,6649509,1123840,2906490,9965014,3725748, ... );

复制代码

性能对比如下图所示：