GreatSQL优化技巧：半连接(semijoin)优化

别让心失明

何为半连接？

半连接是在GreatSQL内部采用的一种执行子查询的方式，semi join不是语法关键字，不能像使用inner join、left join、right join这种语法关键字一样提供给用户来编写SQL语句。
两个表t1表和t2表进行半连接的含义是：对于t1表的某条记录来说，我们只关心在t2表中是否存在与之匹配的记录，而不关心有多少条记录与之匹配，最终的结果集中只保留t1表的记录。
前面文章也提到过，含in、exists子查询的语句通常会采用半连接方式执行查询，但这不绝对，也有一些情况不适用半连接。比如:
(1)外查询的where子句中，存在其他搜索条件使用OR操作符与IN子查询的条件连接起来
(2)IN子查询位于Select子句中
(3)IN子查询中含有union的情况
(4)IN子查询中含group by、having或聚合函数的情况
GreatSQL执行半连接的优化策略

本文实验使用数据库版本为GreatSQL 8.0.32-25。
创建两张实验表来说明。

1. greatsql> create table t1(
2. c1 varchar(30),
3. c2 int
4. );
5. greatsql> create table t2(
6. id int primary key,
7. c1 varchar(30),
8. key idx_c1(c1)
9. );
10. --插入几条测试数据
11. greatsql> insert into t1 values('a',1);
12. greatsql> insert into t1 values('b',3);
13. greatsql> insert into t1 values('a',5);
14. greatsql> insert into t1 values('c',7);
15. greatsql> insert into t1 values('d',9);
16. greatsql> insert into t2 values(1,'a');
17. greatsql> insert into t2 values(2,'a');
18. greatsql> insert into t2 values(3,'b');
19. greatsql> insert into t2 values(4,'b');
20. greatsql> insert into t2 values(5,'c');
21. greatsql> insert into t2 values(6,'b');

复制代码

GreatSQL执行半连接的方式大致有以下5种：
1.Table pullout（子查询中的表上拉）

当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的FROM子句中，并把子查询的查询条件合并到外层查询的搜索条件中。所以选择这种方式是有先决条件的，子查询的查询列表处必须只有主键或唯一索引列。有没有选择这种方式，可以通过执行explain展示计划后，使用show warnings命令查看优化器改写后的语句。
例如下面这个语句：

1. select * from t1 where c2 in (select id from t2 where t2.c1='b');

复制代码

这个语句种子查询的id列是t2表的主键列，满足这种方式的先决条件，看一下执行计划。

1. greatsql> explain select * from t1 where c2 in (select id from t2 where t2.c1='b');
2. +----+-------------+-------+------------+------+----------------+--------+---------+-------+------+----------+--------------------------------------------+
3. | id | select_type | table | partitions | type | possible_keys  | key    | key_len | ref   | rows | filtered | Extra                                      |
4. +----+-------------+-------+------------+------+----------------+--------+---------+-------+------+----------+--------------------------------------------+
5. |  1 | SIMPLE      | t2  | NULL       | ref  | PRIMARY,idx_c1 | idx_c1 | 123     | const |    3 |   100.00 | Using index                                |
6. |  1 | SIMPLE      | t1  | NULL       | ALL  | NULL           | NULL   | NULL    | NULL  |    4 |    25.00 | Using where; Using join buffer (hash join) |
7. +----+-------------+-------+------------+------+----------------+--------+---------+-------+------+----------+--------------------------------------------+
8. 2 rows in set, 1 warning (0.00 sec)
10. greatsql> show warnings;
11. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12. | Level | Code | Message                                                                                                                                                                                            |
13. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
14. | Note  | 1003 | /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t2` join `test`.`t1` where ((`test`.`t1`.`c2` = `test`.`t2`.`id`) and (`test`.`t2`.`c1` = 'b')) |
15. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
16. 1 row in set (0.01 sec)

复制代码

从warning信息可以看出，优化器改执行连接方式是，t1表与t2表通过内连接来关联，原子查询内部t2表的过滤条件放到了整个语句where条件的后面，原语句与优化器执行的语句之所以等价，是因为子查询的查询列id列是主键列，不会有重复值，跟外表t1使用inner join连接后，不会造成关联后结果集数据量的放大。一般情况下子查询的查询列表处只有主键或者唯一索引列时都会转化为这种方式来执行。对于这种业务，无论开发者怎么编写SQL，使用inner join 也好，exists也好，最后优化器执行方式可能都是一样的。
可以看一下将原语句改造为inner join 与 exists语句的执行计划，是不是都是一样的。

1. greatsql> explain select * from t1 where exists (select 1 from t2 where t2.id=t1.c2 and t2.c1='b');
2. +----+-------------+-------+------------+------+----------------+--------+---------+-------+------+----------+--------------------------------------------+
3. | id | select_type | table | partitions | type | possible_keys  | key    | key_len | ref   | rows | filtered | Extra                                      |
4. +----+-------------+-------+------------+------+----------------+--------+---------+-------+------+----------+--------------------------------------------+
5. |  1 | SIMPLE      | t2  | NULL       | ref  | PRIMARY,idx_c1 | idx_c1 | 123     | const |    3 |   100.00 | Using index                                |
6. |  1 | SIMPLE      | t1  | NULL       | ALL  | NULL           | NULL   | NULL    | NULL  |    4 |    25.00 | Using where; Using join buffer (hash join) |
7. +----+-------------+-------+------------+------+----------------+--------+---------+-------+------+----------+--------------------------------------------+
8. 2 rows in set, 2 warnings (0.00 sec)
10. greatsql> show warnings;
11. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12. | Level | Code | Message                                                                                                                                                                                            |
13. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
14. | Note  | 1276 | Field or reference 'test.t1.c2' of SELECT #2 was resolved in SELECT #1                                                                                                                           |
15. | Note  | 1003 | /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t2` join `test`.`t1` where ((`test`.`t1`.`c2` = `test`.`t2`.`id`) and (`test`.`t2`.`c1` = 'b')) |
16. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
17. 2 rows in set (0.00 sec)
19. greatsql> explain select t1.* from t1 inner join t2 on t1.c2=t2.id where t2.c1='b';
20. +----+-------------+-------+------------+------+----------------+--------+---------+-------+------+----------+--------------------------------------------+
21. | id | select_type | table | partitions | type | possible_keys  | key    | key_len | ref   | rows | filtered | Extra                                      |
22. +----+-------------+-------+------------+------+----------------+--------+---------+-------+------+----------+--------------------------------------------+
23. |  1 | SIMPLE      | t2  | NULL       | ref  | PRIMARY,idx_c1 | idx_c1 | 123     | const |    3 |   100.00 | Using index                                |
24. |  1 | SIMPLE      | t1  | NULL       | ALL  | NULL           | NULL   | NULL    | NULL  |    4 |    25.00 | Using where; Using join buffer (hash join) |
25. +----+-------------+-------+------------+------+----------------+--------+---------+-------+------+----------+--------------------------------------------+
26. 2 rows in set, 1 warning (0.01 sec)
28. greatsql> show warnings;
29. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
30. | Level | Code | Message                                                                                                                                                                                            |
31. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
32. | Note  | 1003 | /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`c2` = `test`.`t2`.`id`) and (`test`.`t2`.`c1` = 'b')) |
33. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
34. 1 row in set (0.00 sec)

复制代码

这种执行方式本质上已经转换为内连接了。
2.FirstMatch（首次匹配）

这种方式先取外层查询的一条记录，到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将外层查询的记录放入到最终结果集中并且停止查找匹配更多的记录，如果找不到，则把该外层查询的记录丢弃掉，然后再开始取下一条外层查询中的记录，这个过程一直持续到外层查询获取不到记录为止。
看一个简单语句的执行计划

1. select * from t1 where c1 in (select c1 from t2);

复制代码

1. greatsql> explain select * from t1 where c1 in (select c1 from t2);
2. +----+-------------+-------+------------+------+---------------+--------+---------+--------------+------+----------+-------------------------------+
3. | id | select_type | table | partitions | type | possible_keys | key    | key_len | ref          | rows | filtered | Extra                         |
4. +----+-------------+-------+------------+------+---------------+--------+---------+--------------+------+----------+-------------------------------+
5. |  1 | SIMPLE      | t1  | NULL       | ALL  | NULL          | NULL   | NULL    | NULL         |    4 |   100.00 | Using where                   |
6. |  1 | SIMPLE      | t2  | NULL       | ref  | idx_c1        | idx_c1 | 123     | test.t1.c1 |    2 |   100.00 | Using index; FirstMatch(t1) |
7. +----+-------------+-------+------------+------+---------------+--------+---------+--------------+------+----------+-------------------------------+
8. 2 rows in set, 1 warning (0.01 sec)
10. greatsql> show warnings;
11. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12. | Level | Code | Message                                                                                                                                                                  |
13. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
14. | Note  | 1003 | /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`c1` = `test`.`t1`.`c1`) |
15. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
16. 1 row in set (0.00 sec)

复制代码

从warning信息可以看到 semi join 的字样，优化器使用半连接方式执行的子查询。从执行计划可以看到 extra 列有FirstMatch(t1) 的字样，表示对t1表外查询传入的每个c1值在t2表上都进行了首次匹配，这种方式也是我最初理解的in子查询的含义，只关心有无匹配上，不关心匹配上多少。
3.LooseScan（松散扫描）

LooseScan是使用子查询的查询列上的索引，只针对相同索引列值的第一条记录，去外查询找对应的记录。使用了这种优化方式的半连接，在explain的计划的Extra列会有LooseScan字样。
还是上面的语句，使用semijoin的hint干涉优化器，使其选择LooseScan的优化策略。

1. select /*+ semijoin(@subq1 loosescan) */  * from t1 where c1 in (select /*+ qb_name(subq1)*/ c1 from t2 );

复制代码

1. greatsql> explain select /*+ semijoin(@subq1 loosescan) */  * from t1 where c1 in (select /*+ qb_name(subq1)*/ c1 from t2 );            
2. +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------------------------+
3. | id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                                      |
4. +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------------------------+
5. |  1 | SIMPLE      | t2  | NULL       | index | idx_c1        | idx_c1 | 123     | NULL |    6 |    50.00 | Using index; LooseScan                     |
6. |  1 | SIMPLE      | t1  | NULL       | ALL   | NULL          | NULL   | NULL    | NULL |    5 |    20.00 | Using where; Using join buffer (hash join) |
7. +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------------------------+
8. 2 rows in set, 1 warning (0.01 sec)
10. greatsql> show warnings;
11. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12. | Level | Code | Message                                                                                                                                                                                                      |
13. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
14. | Note  | 1003 | /* select#1 */ select /*+ SEMIJOIN(@`subq1` LOOSESCAN) */ `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t1`.`c1` = `test`.`t2`.`c1`) |
15. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
16. 1 row in set (0.00 sec)

复制代码

从执行计划可以看出，子查询的表t2作为驱动表，t2表的c1列上有索引，对表t2进行访问时，使用其c1列的索引，对相同的索引列值只取第一条记录去t1表中找对应记录，将所有外查询表t1对应的记录都加入到最终结果集，可以理解为对子查询t2表的索引扫描方式是跳跃式的。
4.Duplicate Weedout重复值消除

这种方式是借助临时表来消除重复值，explain展示计划时，在extra列会出现Start temporary 和 End temporary的字样。
还是上面的语句，我们使用semijoin的hint干涉优化器，使其选择dupsweedout优化策略。

1. greatsql> explain select /*+ semijoin(@subq1 dupsweedout)*/ * from t1 where c1 in (select /*+ qb_name(subq1)*/ c1 from t2);
2. +----+-------------+-------+------------+------+---------------+--------+---------+--------------+------+----------+---------------------------------------------+
3. | id | select_type | table | partitions | type | possible_keys | key    | key_len | ref          | rows | filtered | Extra                                       |
4. +----+-------------+-------+------------+------+---------------+--------+---------+--------------+------+----------+---------------------------------------------+
5. |  1 | SIMPLE      | t1  | NULL       | ALL  | NULL          | NULL   | NULL    | NULL         |    4 |   100.00 | Using where                                 |
6. |  1 | SIMPLE      | t2  | NULL       | ref  | idx_c1        | idx_c1 | 123     | test.t1.c1 |    2 |   100.00 | Using index; Start temporary; End temporary |
7. +----+-------------+-------+------------+------+---------------+--------+---------+--------------+------+----------+---------------------------------------------+
8. 2 rows in set, 1 warning (0.00 sec)
10. greatsql> show warnings;
11. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12. | Level | Code | Message                                                                                                                                                                                                        |
13. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
14. | Note  | 1003 | /* select#1 */ select /*+ SEMIJOIN(@`subq1` DUPSWEEDOUT) */ `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`c1` = `test`.`t1`.`c1`) |
15. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
16. 1 row in set (0.00 sec)

复制代码

例如：t1表的记录（'b',3）,可以匹配上t2表的两条记录(3,'b'),(4,'b')，为了消除关联结果的重复值，可以想象建立这样一个临时表：

1. create table tmp(rowid int primary key);

复制代码

当把t1表的记录加入到结果集时，先把这条记录的rowid加入到临时表中，如果添加成功，说明这条记录并没有加入到最后的结果集，如果添加失败，则说明t1表的这条记录已经加入到最终结果集了
个人感觉这种方式比其他方式效率低。
5.Semi-join Materialization（半连接物化）

先把IN 子句中的不相关子查询进行物化，然后再将外层查询的表与物化表进行连接。子查询内部有分组聚合运算时通常会先进行物化处理。
还是上面的语句，使用semijoin的hint干涉优化器，使其选择materialization的优化策略。

1. select /*+ semijoin(@subq1 materialization) */  * from t1 where c1 in (select /*+ qb_name(subq1)*/ c1 from t2 );

复制代码

1. greatsql> explain select /*+ semijoin(@subq1 materialization) */  * from t1 where c1 in (select /*+ qb_name(subq1)*/ c1 from t2 );
2. +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------+------+----------+-------------+
3. | id | select_type  | table       | partitions | type   | possible_keys       | key                 | key_len | ref          | rows | filtered | Extra       |
4. +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------+------+----------+-------------+
5. |  1 | SIMPLE       | t1        | NULL       | ALL    | NULL                | NULL                | NULL    | NULL         |    5 |   100.00 | Using where |
6. |  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 123     | test.t1.c1 |    1 |   100.00 | NULL        |
7. |  2 | MATERIALIZED | t2        | NULL       | index  | idx_c1              | idx_c1              | 123     | NULL         |    6 |   100.00 | Using index |
8. +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------+------+----------+-------------+
9. 3 rows in set, 1 warning (0.00 sec)
11. greatsql> show warnings;
12. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13. | Level | Code | Message                                                                                                                                                                                                            |
14. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15. | Note  | 1003 | /* select#1 */ select /*+ SEMIJOIN(@`subq1` MATERIALIZATION) */ `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t2`) where (`<subquery2>`.`c1` = `test`.`t1`.`c1`) |
16. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
17. 1 row in set (0.00 sec)

复制代码

从执行计划可以看出，先对子查询t2表做了物化表处理，物化表会生成自动索引，外查询表t1再与物化表做Nest loop连接。
补充说明
对于上面的语句 select * from t1 where c1 in (select c1 from t2);，优化器默认选择了firstmatch方式，其他方式都是使用hint来干涉的优化器的选择，可以看到这个hint包含两部分，一个是使用qb_name()给子查询分配一个名称，一个是使用semijoin([@query_block_name] [strategy]),指定子查询块使用半连接策略，可以指定多个策略。同时semijoin的优化策略的选择还受优化开关参数optimize_switch的影响，该参数里有semijoin，loosescan，firstmatch，duplicateweedout的开关，默认都是开启的，所以也可以使用优化开关来干涉优化器的选择。
优化举例

1. select count(*)
2.   from t1 a
3. where substr(a.modifytime, 1, 8) = '20240301'
4.    and a.sospecnumber in
5.        (select a.sospecnumber
6.           from t1 a
7.          where substr(a.modifytime, 1, 8) < '20240301');

复制代码

这条SQL只涉及一张表t1，表中数据200万左右，modify_time为字符类型，存储从2009年开始的时间串。看一下该表的索引情况。

1. greatsql> show index from t1;
2. +-------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3. | Table | Non_unique | Key_name         | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
4. +-------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5. | t1    |          1 | idx_sospecnumber |            1 | SOSPECNUMBER | A         |         133 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
6. | t1    |          1 | idx_modifytime   |            1 | MODIFYTIME   | A         |      634186 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
7. +-------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
8. 2 rows in set (0.01 sec)

复制代码

explain的执行计划如下：

1. greatsql> explain
2.     -> select count(*)
3.     ->   from t1 a
4.     ->  where substr(a.modifytime, 1, 8) ='20240301'
5.     ->    and a.sospecnumber  in
6.     ->        (select a.sospecnumber
7.     ->           from t1 a
8.     ->          where substr(a.modifytime, 1, 8) < '20240301') ;
9. +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+---------+----------+-------------+
10. | id | select_type  | table       | partitions | type   | possible_keys       | key                 | key_len | ref                 | rows    | filtered | Extra       |
11. +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+---------+----------+-------------+
12. |  1 | SIMPLE       | a           | NULL       | ALL    | idx_sospecnumber    | NULL                | NULL    | NULL                | 2426414 |   100.00 | Using where |
13. |  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 131     | test.a.SOSPECNUMBER |       1 |   100.00 | NULL        |
14. |  2 | MATERIALIZED | a           | NULL       | ALL    | idx_sospecnumber    | NULL                | NULL    | NULL                | 2426414 |   100.00 | Using where |
15. +----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+---------+----------+-------------+
16. 3 rows in set, 1 warning (0.00 sec)

复制代码

优化器选择的半连接优化策略是物化的方式。
explain analyze的实际计划如下：

1. greatsql> explain analyze
2.     -> select count(*)
3.     ->   from t1 a
4.     ->  where substr(a.modifytime, 1, 8) ='20240301'
5.     ->    and a.sospecnumber  in
6.     ->        (select a.sospecnumber
7.     ->           from t1 a
8.     ->          where substr(a.modifytime, 1, 8) < '20240301') \G
9. *************************** 1. row ***************************
10. EXPLAIN: -> Aggregate: count(0)  (cost=1177497474524.58 rows=1) (actual time=4442.499..4442.500 rows=1 loops=1)
11.     -> Nested loop inner join  (cost=588748984584.98 rows=5887484899396) (actual time=4438.967..4442.408 rows=1346 loops=1)
12.         -> Filter: ((substr(a.MODIFYTIME,1,8) = '20240301') and (a.SOSPECNUMBER is not null))  (cost=252003.98 rows=2426414) (actual time=1550.096..1552.027 rows=1346 loops=1)
13.             -> Table scan on a  (cost=252003.98 rows=2426414) (actual time=0.050..1189.136 rows=2493198 loops=1)
14.         -> Single-row index lookup on <subquery2> using <auto_distinct_key> (sospecnumber=a.SOSPECNUMBER)  (cost=494645.48..494645.48 rows=1) (actual time=2.147..2.147 rows=1 loops=1346)
15.             -> Materialize with deduplication  (cost=494645.38..494645.38 rows=2426414) (actual time=2888.845..2888.845 rows=165 loops=1)
16.                 -> Filter: (a.SOSPECNUMBER is not null)  (cost=252003.98 rows=2426414) (actual time=0.215..1927.315 rows=2487547 loops=1)
17.                     -> Filter: (substr(a.MODIFYTIME,1,8) < '20240301')  (cost=252003.98 rows=2426414) (actual time=0.214..1745.562 rows=2487547 loops=1)
18.                         -> Table scan on a  (cost=252003.98 rows=2426414) (actual time=0.211..1235.738 rows=2493198 loops=1)
20. 1 row in set (4.45 sec)

复制代码

优化分析：
这条SQL总体耗时4.45s，耗时主要分布在两处：
一处消耗在外表的查询，对t1进行了全表扫描，回表过滤后剩余1346行数据，耗时1552ms，此处虽然modifytime列有索引，但是因为在条件列上施加了substr函数，导致索引用不上，改为modifytime like '20240301%'的方式，也表示了查询2024年3月1日的数据，同时用上了索引。
另一处消耗在子查询的物化上，子查询结果集有2487547行数据，表扫描、过滤、物化整个过程耗时约2888ms，对大结果集进行物化消耗比较大，同时IN子查询的查询列sospecnumber列上是有索引的，虽然选择性不好，但是这个子查询的含义是只需要判断子查询结果集中有无记录能匹配上，而不关心匹配上多少条，所以这种情况采用first match方式比较好。
SQL改写如下：

1. select /*+ semijoin(@subq firstmatch)*/
2. count(*)
3.   from t1 a
4. where a.modifytime like '20240301%'
5.    and a.sospecnumber in
6.        (select /*+ qb_name(subq)*/
7.          a.sospecnumber
8.           from t1 a
9.          where substr(a.modifytime, 1, 8) < '20240301')

复制代码

改写后执行计划如下：

1. *************************** 1. row ***************************
2. EXPLAIN: -> Aggregate: count(0)  (cost=11052513.72 rows=1) (actual time=157.570..157.570 rows=1 loops=1)
3.     -> Nested loop semijoin  (cost=8596909.70 rows=24556040) (actual time=0.203..157.450 rows=1346 loops=1)
4.         -> Filter: (a.SOSPECNUMBER is not null)  (cost=606.05 rows=1346) (actual time=0.057..7.610 rows=1346 loops=1)
5.             -> Index range scan on a using idx_modifytime over ('20240301' <= MODIFYTIME <= '20240301????????????????????????????????????????????????'), with index condition: (a.MODIFYTIME like '20240301%')  (cost=606.05 rows=1346) (actual time=0.055..7.406 rows=1346 loops=1)
6.         -> Filter: (substr(a.MODIFYTIME,1,8) < '20240301')  (cost=83255911.06 rows=18244) (actual time=0.111..0.111 rows=1 loops=1346)
7.             -> Index lookup on a using idx_sospecnumber (SOSPECNUMBER=a.SOSPECNUMBER)  (cost=83255911.06 rows=18244) (actual time=0.111..0.111 rows=1 loops=1346)
9. 1 row in set, 1 warning (0.16 sec)

复制代码

改写后耗时0.16s，性能提升近30倍，在对子查询通过索引idx_sospecnumber搜索数据时，查到一条就会停止继续搜索了。
结语

GreatSQL的 IN 子查询适用于半连接时，优化器提供了5种优化策略：Table pullout、FirstMatch、LooseScan、Duplicate weedout、materialize。
一般外查询表结果集小，子查询结果集太大时，不希望通过物化这种方式来执行连接，因为物化表的代价太大，可能通过FirstMatch或者LooseScan很快就可以执行出结果了。那反之外查询结果集大，子查询结果集小时，通过物化表这种方式可能就会取得很好的效果。很多时候都不用过多干涉优化器做选择，但是如果懂得原理，当优化器选错的时候我们也可以通过hint来稳定计划，让SQL保持高效的执行。

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/18139862
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！