【OceanBase】25.查询改写之基于规则的查询改写

1.查询改写概念

数据库中的查询改写（query rewrite）把一个 SQL 改写成另外一个更加容易优化的 SQL。
◼ 基于规则的查询改写总是会把 SQL 往“好”的方向进行改写，从而增加该SQL的优化空间
◼ 基于规则的查询改写并不能总是把 SQL 往“好”的方向进行改写，所以需要代价模型来判断
◼ 基于代价的改写之后可能又会重新触发基于规则的改写，所以整体上采用
迭代式的方式进行改写

	语法语义前端
		|
	-----------------	
	|基于规则改写   |
	|  |    |       | (基于代价的改写框架)
	|基于代价改写   |
	-----------------
	   |    |
	 物理优化
		|
	  执行

注:基于规则改写 和 基于代价改写 互相迭代最终生成完美的执行计划。

2.基于规则的查询改写

基于规则的查询改写方式主要包括：
◼ 子查询相关改写
⚫ 视图合并、子查询展开、ANY/ALL 使用 MAX/MIN 改写
◼ 外联接消除
◼ 简化条件改写
⚫ HAVING 条件消除、等价关系推导、恒真/假消除
◼ 非SPJ的改写
⚫ 冗余排序消除、LIMIT 下压、DISTINCT 消除、MIN/MAX 改写

--查询改写类型
视图合并 (Oracle里面有这个)
子查询展开(Oracle里面有这个)
any/all 使用 MAX/MIN 改写
外连接消除
having条件消除
等价关系推导
恒真/假消除
limit 下压
distinct 消除
MIN/MAX 改写

3.子查询相关改写

子查询改写的方式主要包括视图合并、子查询展开和将 ANY/ALL 使用 MAX/MIN 改写等
优化器对于子查询一般使用嵌套执行的方式，也就是父查询每生成一行数据后，都需要执行一次子查询。
使用这种方式需要多次执行子查询，执行效率很低。对于子查询的优化方式，一般会将其改写为联接操作，
可大大提高执行效率，主要优点如下：
◼ 可避免子查询多次执行
◼ 优化器可根据统计信息选择更优的连接顺序和连接方法
◼ 子查询的连接条件、过滤条件改写为父查询的条件后，优化器可以进行进一步优化，比如条件下压等

子查询改写思路:
将NESTED LOOP 的连接方式改为 HASH JOIN;NESTED LOOP 连接方式对于驱动表数据
过大的场景执行效率极低，仅适合驱动表数据结果集少的场景。

4.子查询相关改写-视图合并

视图合并是指将代表一个视图的子查询合并到包含该视图的查询中，视图合并后，
有助于优化器增加联接顺序的选择、访问路径的选择以及进一步做其他改写操作，从而选择更优的执行计划。

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT);
CREATE TABLE t3 (c1 INT PRIMARY KEY, c2 INT);

--SQL_A：
SELECT t1.c1, v.c1
FROM t1, (SELECT t2.c1, t3.c2 FROM t2, t3 WHERE t2.c1 = t3.c1) v
WHERE t1.c2 = v.c2;

--SQL_B：
SELECT t1.c1, t2.c1 FROM t1, t2, t3
WHERE t2.c1 = t3.c1
AND t1.c2 = t3.c2;

--创建相关测试表
SQL_A 不进行改写， 可选连接顺序有：
•t1, v(t2,t3)
•t1, v(t3,t2)
•v(t2,t3), t1
•v(t3,t2), t1

--视图合并改写SQL_B后， 可选连接顺序有：
•t1, t2, t3
•t1, t3, t2
•t2, t1, t3
•t2, t3, t1
•t3, t1, t2
•t3, t2, t1

5.子查询展开：子查询展开为semi-join/anti-join

子查询展开是指将 where 条件中子查询提升到父查询中，并作为连接条件与父查询并列进行展开。 
一般涉及的子查询 表达式有 not in、in、not exist、exist、any、all。

EXPLAIN SELECT * FROM t1 WHERE t1.c1 IN (SELECT t2.c2 FROM t2)\G;
*************************** 1. row ***************************
Query Plan: 
=======================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
---------------------------------------
|0 |HASH SEMI JOIN|   |495  |3931|
|1 | TABLE SCAN   |t1 |1000 |499 |
|2 | TABLE SCAN   |t2 |1000 |433 |
=======================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2]), filter(nil),
equal_conds([t1.c1 = t2.c2]), other_conds(nil)
1 - output([t1.c1], [t1.c2]), filter(nil),
access([t1.c1], [t1.c2]), partitions(p0)
2 - output([t2.c2]), filter(nil),
access([t2.c2]), partitions(p0)

HASH SEMI JOIN:此处子查询已展开为半连接

两表结构：
t1 (c1 INT, c2 INT)
t2 (c1 INT PRIMARY KEY, c2 INT)
其中t2.c2 不具有唯一性，改为 semi join，改写后执行计划如上图所示

--子查询展开的方式如下：
◼ 改写条件使生成的联接语句能够返回与原始语句相同的行
◼ 展开为半联接（SEMI JOIN / ANTI JOIN）

6.子查询展开：子查询展开为内连接

子查询展开是指将 where 条件中子查询提升到父查询中，并作为连接条件与父查询并列进行展开。 
一般涉及的子查询表达式有 not in、in、not exist、exist、any、all。
EXPLAIN SELECT * FROM t1 WHERE t1.c1 IN (SELECT t2.c1 FROM t2)\G;
*************************** 1. row ***************************
Query Plan:
====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
------------------------------------
|0 |HASH JOIN  |   |1980 |3725|  --此处子查询已展开为内连接
|1 | TABLE SCAN|t2 |1000 |411 |
|2 | TABLE SCAN|t1 |1000 |499 |
====================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2]), filter(nil),
equal_conds([t1.c1 = t2.c1]), other_conds(nil)
1 - output([t2.c1]), filter(nil),
access([t2.c1]), partitions(p0)
2 - output([t1.c1], [t1.c2]), filter(nil),
access([t1.c1], [t1.c2]), partitions(p0)


上 页 的 SQL中 如 果 将 t2.c2 改 为t2.c1，由于 t2.c1 为主键，子查询输出具有唯一性，
此时可以直接转换为内连接，转换后的执行计划如左图所示：
select * from t1 where t1.c1 in (select t2.c1 from  t2);
      |
select t1.* from t1, t2 where t.c1 = t2.c1;

7.外连接消除

外联接操作可分为左外联接、右外联接和全外联接。在联接过程中，由于外联接左右顺序不能变换，
优化器对联接顺序的选择会受到限制。
外连接消除是指将外连接转换成内连接，从而可以提供更多可选择的连接路径，供优化器考虑。
外连接消除需要存在“空值拒绝条件”，即 where 条件中，存在当内表生成的值为 null 时，
使得输出为 false 的条件。

SELECT t1.c1, t2.c2 FROM t1 LEFT JOIN t2 ON t1.c2 = t2.c2; 
--如上所示这个是一个外联接，在其输出行中 t2.c2 可能为 NULL

--如果加一个条件t2.c2 > 5，则通过该条件过滤后，t2.c1输出不可能为 NULL
SELECT t1.c1, t2.c2 FROM t1 LEFT JOIN t2 ON t1.c2 = t2.c2 WHERE t2.c2 > 5;
              |
SELECT t1.c1, t2.c2 FROM t1 INNER JOIN t2 ON t1.c2 = t2.c2 WHERE t2.c2 > 5;
--此时可以将外联接转换为内联接

8.总结 

查询改写的思路:

(1)将NESTED LOOP 的连接方式改写为HASH连接(hash semi join,hash join,hasah anti join).

(2)外连接转换为内连接，取到更多的连接方式，得到更多的执行路径。