引言
查问剖析中常常应用子查问语句，数据库为了进步子查问的执行性能，往往会把子查问语句改写成半连贯（子查问晋升办法参见本系列第二篇：子查问晋升首篇）。
例如，咱们须要查问 2022-08-01 到 2022-08-02 之间已排片的电影，能够通过 IN 子查问查看电影是否在排片期内。查问 SQL 如 Q1 所示。
-- 影片表
MOVIE(movie_id primary key，movie_name, release_date)
-- 排片表
PLAY(play_id, movie_id, play_time, price, seats)
Q1:
SELECT movie_name
FROM movie
WHERE movie_id IN (SELECT movie_id
FROM play
WHERE play_time BETWEEN DATE'2022-08-01'
AND DATE'2022-08-02');
Q2:
SELECT movie_name
FROM movie LEFT SEMI
JOIN (
SELECT movie_id
FROM play
WHERE play_time BETWEEN date'2022-08-01'
AND date'2022-08-02' )play
ON movie.movie_id = play.movie_id;
对于查问 Q1，OceanBase 会做子查问晋升改写，改写成等价的查问 Q2，应用半连贯来计算子查问。对于新的查问，优化器能够抉择 hash left semi join、hash right semi join、merge left semi join、merge right semi join、nested loop left semi join 五种连贯算法执行。下图展现了其中一种执行打算。
Query Plan:
==========================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------
|0 |HASH SEMI JOIN| |30 |910000|
|1 | TABLE SCAN |MOVIE|1000000 |460000|
|2 | SUBPLAN SCAN |PLAY |30 |46 |
|3 | TABLE SCAN |PLAY |30 |46 |
==========================================思考一种业务场景：movie 表的数据量达 100w，2022-08-01 到 2022-08-02 之间已排片的电影约 30 部。下面五种连贯算法都须要扫描 movie 表的全副数据，扫描老本比拟高。而咱们晓得 movie 表的主键为 movie_id，如果咱们可能先查问出 2022-08-01 到 2022-08-02 之间已排片的 movie_id，再去 movie 表查问 movie_name，就可能应用 movie 表的主键索引，执行 30 次主键索引扫描即可实现查问。
为了可能依照最优打算执行 Q1 查问，咱们须要以 play 表作为驱动表，并且应用 index nested loop join 的算法，把 movie_id 的连贯条件转换为 movie 表的索引扫描条件。打算如下所示。
Query Plan:
=====================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
-----------------------------------------------------
|0 |NESTED LOOP RIGHT SEMI JOIN| |30 |91 |
|1 | SUBPLAN SCAN |PLAY |30 |96 |
|2 | TABLE SCAN |PLAY |30 |96 |
|3 | TABLE GET |MOVIE|1 |46 |
=====================================================但咱们晓得，数据库没有 NESTED LOOP RIGHT SEMI JOIN 的算法实现，那咱们要怎么改写这条 SQL，使数据库可能反对这种优化打算呢？为此，OceanBase 引入了一个改写规定：半连贯转内连贯，将满足肯定条件的半连贯查问转换为内连贯查问，优化器就能够针对上述场景生成最优的 index nested loop join 打算。
半连贯转内连贯
介绍半连贯转内连贯规定前，咱们先理解下半连贯的执行逻辑。还是以 Q1 为例进行阐明，从 movie 表中读取一行数据，而后从 play 表内查找指定 movie_id 的数据，如果存在，则执行数据，否则不输入。从形容中咱们能够晓得，对于 movie 表中给定的一行数据，无论 play 表存在多少条数据与指定的 movie_id 雷同，查问都只输入一行数据。
而内连贯对于符合条件的每一条数据都会输入，也就是说，如果半连贯间接转内连贯，执行后果可能会反复输入屡次。为了保障改写不扭转查问语义，咱们须要对 play 的 movie_id 去重，保障 movie 表的每行数据在 play 表中只匹配一行数据，改写后的查问如 Q3 所示。
Q3:
SELECT movie_name
FROM movie INNER JOIN
JOIN (
SELECT DISTINCT movie_id
FROM play
WHERE play_time BETWEEN date'2022-08-01'
AND date'2022-08-02' )play
ON movie.movie_id = play.movie_id;对于新的查问 Q3，优化器能够尝试 movie hash join play、play hash join movie、movie merge join play、play merge join movie、movie nested loop join play、play nested loop join movie 这六种连贯算法执行，比原来多了一种。此时，优化器能够生成之前形容的最优打算。
================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
------------------------------------------------
|0 |NESTED-LOOP JOIN | |30 |46 |
|1 | SUBPLAN SCAN |PLAY |30 |46 |
|2 | MERGE DISTINCT | |30 |46 |
|3 | SORT | |30 |46 |
|4 | TABLE SCAN |PLAY |30 |46 |
|5 | TABLE GET |MOVIE|1 |7 |
================================================留神到改写之后的查问比原来的查问多了一次去重计算，Q3 查问并不是在所有场景下都比 Q2 查问更优，因而，OceanBase 的半连贯转内连贯改写是一种基于代价的改写，即优化器会比照改写前后最优打算的代价，如果代价升高了，才会利用改写，否则不会改写查问。
优化点
上文咱们介绍了半连贯转内连贯次要是减少去重计算来保障语义的正确性，也正因为减少了去重计算，改写之后的查问并不总是比改写之前的查问更优。
咱们能够思考一下，是否所有场景都须要加去重计算？答案是否定的，在有些场景下，咱们能够把半连贯间接转成内连贯，例如：play 表的 movie_id 自身就有惟一束缚，或者 play 表只有一行数据满足条件。在这些场景下，咱们能够不增加去重计算，这也意味着改写之后的查问总是比改写之前的查问更优，不须要额定比拟代价。
改写陷阱
在之前的介绍中，咱们没有阐明数据类型对改写规定的影响，实际上半连贯转内连贯对数据类型是有要求的。通过一个例子阐明，对于查问 Q4，如果须要改写成内连贯，改写的 SQL 如 Q5 所示。
create table t1(c1 int);
insert into t1 values(0);
create table t2(c1 varchar(20));
insert into t2 values('0.0');
insert into t2 values('0.1');
Q4:
SELECT *
FROM t1
WHERE c1 IN (SELECT c1
FROM t2);
Q5:
SELECT t1.c1
FROM t1
INNER JOIN (SELECT DISTINCT c1
FROM t2)t2
ON t1.c1 = t2.c1; 下面的改写正确吗？对于 Q4，后果是一行数据：0，对于 Q5，后果是两行数据：0, 0。为什么呢？在对 t2 表的 c1 列去重时，应用的是 varchar(20) 类型，’0.0’ 与 ’0.1’ 属于不同的数据，不会产生去重操作，与 t1 表连贯时须要把 varchar(20) 类型的数据转换成 int 类型比拟，此时 ’0.0’ 与 ’0.1’ 转换成了 0 与 0，导致执行后果不正确。
为了防止数据类型影响改写的正确性，咱们须要在改写时，对数据类型做适当的解决，你能够思考一下怎么是正确的改写查问。
总结
本文次要介绍 OceanBase 的半连贯转内连贯改写，以及这个改写的优化点、容易被疏忽的谬误。OceanBase 会把满足肯定条件的半连贯转换成内连贯，使优化器可能尝试更多的打算，生成的查问打算可能更优。