# IN谓词转子查询 #### 操作场景 社区MySQL在处理column IN (const1, const2, .... )时，如果column上有索引，那么通常优化器会选择Range scan进行扫描。在进行Range scan分析时，如果IN后面的列表非常大，使用的内存会超过range_optimizer_max_mem_size定义的最大内存，导致Range scan失效，从而引发查询的性能下降。 为了解决该问题，通常有以下几种方案，但每种方案都存在一定的弊端： - 可以增加允许使用的最大内存，但是该内存是Session级别的，即每个Session进行该查询都会占用同样的内存，可能导致用户实例发生OOM。 - 如果允许执行 range optimizer，但是IN值过多超过eq_range_index_dive_limit限制，会导致无法进行index dive，而只能走索引统计信息。由于大量Value所对应的统计信息比较简单，很有可能出现估算不准确的情况，导致性能回退。 因此，TaurusDB支持IN谓词转子查询功能。对于满足[前提条件]的复杂查询，通过该功能优化器可以将某些大的IN谓词转换为IN子查询，随后优化器会进一步考虑是否将其转换为semijoin，以提升复杂查询的执行性能。 #### 转换过程 原查询： ``` select ... from lineitem where l_partkey in (...) ``` 转换后的查询： ``` select ... from lineitem where l_partkey in (select tb._col_1 from (values (9628136),(19958441),...) tb) ``` #### 前提条件 - TaurusDB内核版本为2.0.42.230600或以上的版本。内核版本的查询方法请参见[如何查看云数据库 TaurusDB实例的版本号](https://support.huaweicloud.com/taurusdb_faq/taurusdb_faq_0141.html)。 - IN列表中的元素个数超过rds_in_predicate_conversion_threshold参数设置的个数。 #### 支持的查询语句 - SELECT - INSERT ... SELECT - REPLACE ... SELECT - 支持视图，PREPARED STMT #### 约束限制 - 只支持常量IN LIST（包括NOW()等不涉及表查询的语句）。 - 不支持STORED PROCEDURE/FUNCTION/TRIGGER。 - 不支持NOT IN，以及无法使用索引的场景。 #### 使用方法 您可以通过"rds_in_predicate_conversion_threshold"参数设置IN谓词转子查询功能。 表1参数说明 | **参数名称** | **级别** | **描述** | |:---|:---|:---| | rds_in_predicate_conversion_threshold | Global | IN谓词转子查询功能控制开关。当SQL语句的IN列表中的元素个数超过该参数的取值时，则SQL语句进行转换，将IN谓词转换为子查询。 默认为0，表示关闭该功能。 | #### 示例 原查询： ``` explain select * from t 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 | t | NULL | ALL | idx1 | NULL | NULL | NULL | 5 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ``` ``` explain format=tree select * from t where a in (1,2,3,4,5); +-------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------+ | -> Filter: (t.a in (1,2,3,4,5)) (cost=0.75 rows=5) -> Table scan on t (cost=0.75 rows=5) | +-------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) ``` 转化后： ``` set rds_in_predicate_conversion_threshold=3; Query OK, 0 rows affected (0.00 sec) ``` ``` explain select * from t 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 | t | NULL | ALL | idx1 | NULL | NULL | NULL | 5 | 100.00 | Using where | | 1 | SIMPLE | | NULL | eq_ref | | | 5 | test.t.a | 1 | 100.00 | IN-list converted | +----+-------------+------------------+------------+--------+---------------------+---------------------+---------+----------+------+---- 2 rows in set, 1 warning (0.00 sec) ``` ``` explain format=tree select * from t where a in (1,2,3,4,5); +---------------------------------------------------------------------------------------------------------------------------------------- | EXPLAIN +---| -> Nested loop inner join (cost=2.50 rows=5) -> Filter: (t.a is not null) (cost=0.75 rows=5) -> Table scan on t (cost=0.75 rows=5) -> Single-row index lookup on using (a=t.a) (cost=0.27 rows=1) | ``` 使用explain，在执行计划中的table列存在\（\*为数字），该表即为构造的临时表，其中存储了IN查询中的所有数据。 也可以通过查看optimize trace，trace中存在in_to_subquery_conversion相关信息。 ``` | explain format=tree select * from t where a in (1,2,3,4,5) | { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "IN_uses_bisection": true }, { "in_to_subquery_conversion": { "item": "(`t`.`a` in (1,2,3,4,5))", "steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", "columns": 1, "row_length": 5, "key_length": 5, "unique_constraint": false, "makes_grouped_rows": false, "cannot_insert_duplicates": true, "location": "TempTable" } } }, ``` #### 性能测试 使用sysbench模型测试。 1. 准备1000w数据。 ``` sysbench /usr/share/sysbench/oltp_read_only.lua --tables=1 --report-interval=10 --table-size=10000000 --mysql-user=root --mysql-password=123456 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sbtest --time=300 --max-requests=0 --threads=200 prepare ``` 2. 查询带1万个IN。 ``` select count(*) from sbtest1 where id/k in (... ...); ``` 性能对比如下表所示： 表2性能数据 | **测试方法** | **开启转换** | **关闭转换（不适用range_opt）** | **性能对比** | |:---|:---|:---|:---| | 带索引 | 0.09秒 | 2.48秒 | 提升26.56倍 | 图1耗时对比 ![](https://support.huaweicloud.com/kerneldesc-taurusdb/figure/zh-cn_image_0000002507705994.png "点击放大")