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

本文分享自华为云社区《【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 :表示从外观到物化表,在物化表中查找数据的时候可以使用主建进行查找。

物化扫描

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

物化查找

  1. 先执行子查询;
  2. 将上一步得到的结果保存到临时表中;
  3. 从外观中取出一行数据,到物化临时表中去查找满足补充条件的行,走物化表的主键,每次扫描1行;
  4. 重复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, ... );
性能对比如下图所示:

可以看出in-list优化后比原有的方式性能提高了36倍。

点击关注,第一时间了解华为云新鲜技术~

 

热门相关:萌妻鲜嫩:神秘老公晚上见   诱妻入怀:老公,手下留情!   绝天武帝   夫人每天都在线打脸   我真不是学神