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

文章探讨了数据库查询改写的概念,包括基于规则和代价的改写策略,重点介绍了子查询改写(视图合并、展开和MAX/MIN替换)、外连接消除以及连接方式的优化,目的是提高查询执行效率和优化器的选择范围。
摘要由CSDN通过智能技术生成

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)外连接转换为内连接,取到更多的连接方式,得到更多的执行路径。

  • 9
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值