|
|
本文分享自华为云社区《【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, ....)
- column IN (SELECT ... FROM temporary_table)
串联可以有两个顺序：
- 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; > 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)
设置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)
使用限制
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 | +----------+
- 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倍。
点击关注，第一时间了解华为云新鲜技术~
来源:https://www.cnblogs.com/huaweiyun/p/18181368
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！ |
本帖子中包含更多资源
您需要 登录 才可以下载或查看，没有账号？立即注册
x
|
