目录
前言
以分布式数据库管理系统Oracle为例,通过实施于具体分布式应用场景的查询,以案例的方式进一步熟悉Oracle产品的优化机制与方法。
在分布式数据库环境下,实施不同的优化策略,创建应用场景,并导入一定量的模拟数据,学会使用Oracle的提示(hint)功能以指定不同的查询优化计划,并对相应策略做出解释。
0 软件版本
OS:一台Windows10的电脑和在其上搭建一台CentOS7的虚拟机
数据库:Oracle19c
图形化工具:Navicat for Oracle
(建议直接从官网下载,也可以从我之前的博客中获取,里面讲述如何安装和使用这些软件)
1 应用场景的创建,以及数据的生成与导入
1.1 创建应用场景
OraStar公司在总部保存供应商的信息,在生产部门保存每批进货的零件产品的信息。
(1)在公司总部节点1上,新建供应商表
CREATE TABLE supplier (
supNo NUMBER (2) PRIMARY KEY,
supName VARCHAR2 (14),
loc VARCHAR2 (13)
);
插入五条供应商数据到总部的供应商信息表中:
INSERT INTO SUPPLIER VALUES (1,'supplier01','中国上海');
INSERT INTO SUPPLIER VALUES (2,'supplier02','中国北京');
INSERT INTO SUPPLIER VALUES (3,'supplier03','中国武汉');
INSERT INTO SUPPLIER VALUES (4,'supplier04','日本东京');
INSERT INTO SUPPLIER VALUES (5,'supplier05','美国纽约');
再次查看供应商表:
(2)在生产部门节点2上,新建零件产品表
CREATE TABLE product (
pNo VARCHAR2(50) NOT NULL,
pName VARCHAR2 (15),
buyDate DATE,
price NUMBER (7, 2),
supNo NUMBER (2)
);
此处由于需生产大量零件产品数据,故采用了利用flinkStream随机生成了五万条数据,程序代码如下:
import org.apache.flink.core.fs.FileSystem.WriteMode
import org.apache.flink.streaming.api.functions.source.{RichSourceFunction, SourceFunction}
import org.apache.flink.streaming.api.scala.{DataStream, StreamExecutionEnvironment}
import org.apache.flink.streaming.api.scala._
import java.util.concurrent.TimeUnit
import java.util.{Date, UUID}
import scala.util.Random
object ProductRandom {
//样例类
case class
Product(pNo: String, pName: String, buyDate: Date, price: Number, supNo: Number)
def main(args: Array[String]): Unit = {
//TODO:1.environment
val env: StreamExecutionEnvironment = StreamExecutionEnvironment.getExecutionEnvironment
//TODO:2.source,生产随机订单数据
val sourceDStream: DataStream[Product] = env.addSource(new ProductSourceFunction)
//TODO:3.Transformation
//TODO:4.Sink,将数据写入excel表
sourceDStream.writeAsCsv("src\\main\\resources\\Product.csv", WriteMode.OVERWRITE)
//TODO:5.Execution
env.execute("ProductRandom Job")
}
class ProductSourceFunction extends RichSourceFunction[Product] {
//定义变量
private var count: Long = 0L
private var isRunning: Boolean = true
override def run(sourceContext: SourceFunction.SourceContext[Product]): Unit = {
// 使用while循环生成50000个订单(零件产品编号pNo、零件名称pName、购买时间buyDate、零件价格price、供应商编号supNo )
while (isRunning && count < 50000) {
// 随机生成零件产品编号pNo(UUID)
val pNo: String = UUID.randomUUID().toString
// 随机生成零件名称pName(product1-product100)
val pName: String = "product" + (Random.nextInt(10) + 1)
// 购买时间buyDate为当前系统时间
val buyDate: Date = new Date(System.currentTimeMillis())
// 随机生成零件价格price(100~1000)
val price: Double = Random.nextInt(900) + 101
// 随机生成供应商编号supNo(1-5)
val supNo: Double = Random.nextInt(5) + 1
// 收集数据
sourceContext.collect(Product(pNo, pName, buyDate, price, supNo))
// 统计生成的订单数
count += 1
// 每隔1毫秒生成一个订单k
TimeUnit.MILLISECONDS.sleep(1)
// TimeUnit.MILLISECONDS.sleep(1)
}
}
override def cancel(): Unit = {
isRunning = false
}
}
}
可以看到生成了五万条模拟数据:
然后将生成的product.csv文件导入生产部门的零件信息表中:
(3)在节点1上,新建到生产部门的数据链
create database link link1 connect to scott identified by tiger using '网络服务名';
注:若数据库链此前建立过,可不需再重复建立。
1.2 发起查询请求
总部用户希望查找产品名字为“product5”的供货商的信息和对应的供货信息。
SELECT
*
FROM
SUPPLIER t1,
PRODUCT @link1 t2
WHERE
T2.supNo = T1.SUPNO AND T2.pName = 'product5';
2 基于查询实例,采取不同优化策略,并对相应的优化策略做出解释
2.1 以不同的优化策略实施查询
(1)以优化器默认的方式发起查询,并对相应的优化策略做出解释
explain plan for
SELECT
*
FROM
SUPPLIER t1,
PRODUCT @link1 t2
WHERE
T2.supNo = T1.SUPNO
AND T2.pName = 'product5';
SELECT * FROM TABLE(dbms_xplan.display);
上述查询语句的执行计划如下:
可以看到CBO优化器默认采用了嵌套循环的连接方式来进行查询操作。
嵌套循环有驱动表和被驱动表的概念,驱动顺序不同执行计划差异非常大,驱动表只被访问一次,被驱动表被访问多次。嵌套循环访问表的次数直接受驱动表的返回记录数的影响。因此应当让实际返回记录数(A-Rows)小的表作为驱动表,返回记录数大的表作为被驱动表。
(2)以/*+hint*/
的方式提示优化器采取哈希连接的连接策略
explain plan for
SELECT
/*+USE_HASH(t1,t2)*/
*
FROM
SUPPLIER t1,
PRODUCT @link1 t2
WHERE
T2.supNo = T1.SUPNO
AND T2.pName = 'product5';
SELECT * FROM TABLE(dbms_xplan.display);
可以看出,CBO为这个查询选择了**散列连接(Hash Join)**的连接策略。
散列连接选择两个表中数据量较小的一个(SUPPLIER)做全表扫描,并在内存中形成一个散列表保存SUPPLIER表的数据。然后Oracle 扫描数据量较大的表( PRODUCT),对于其每一行的SUPNO,查找散列表中相匹配的行,返回连接映像。
由于散列表保存在一块Oracle 的私有内存中,可以避免某些不必要的锁操作,所以访问速度非常快。散列连接非常适合表的数据量一大一小,而且小表的数据量完全可以保存到内存中的连接案例。
(3)以/*+hint*/
的方式提示优化器采取嵌套循环的连接策略
explain plan for
SELECT
/*+USE_NL(t1,t2)*/
*
FROM
SUPPLIER t1,
PRODUCT @link1 t2
WHERE
T2.supNo = T1.SUPNO
AND T2.pName = 'product5';
SELECT * FROM TABLE(dbms_xplan.display);
可以看出,CBO为这个查询选择了嵌套循环(Nested Loop)连接策略。
嵌套循环连接策略,外表驱动内表,外表的每一行都会在内表中进行匹配。与Hash Join不同的是,没有使用内表来生成HashTable,因此内表最好有索引
(4)以/*+hint*/
的方式提示优化器采取排序合并的连接策略
explain plan for
SELECT
/*+USE_MERGE(T1,T2)*/
*
FROM
SUPPLIER t1,
PRODUCT @link1 t2
WHERE
T2.supNo = T1.SUPNO
AND T2.pName = 'product5';
SELECT * FROM TABLE(dbms_xplan.display);
可以看出,CBO为这个查询选择了排序合并(Merge Join)连接策略。
排序合并连接可以联接来自两个独立源的行。哈希联接通常比排序合并联接性能更好。但是,如果同时存在以下两种情况,则排序合并联接的性能可能优于哈希联接:
- 行源已排序。
- 不必执行排序操作。
3 解释Oracle的CBO是如何利用并列内联视图的方式提高分布式查询的性能
查询语句:
-- EXPLAIN PLAN FOR
SELECT
V.PNO,
V.PNAME,
E .SUPNO,
E .SUPNAME
FROM
(
SELECT
PNO,PNAME,PRICE,SUPNO
FROM
PRODUCT @link1
) V,
SUPPLIER E
WHERE
V.SUPNO = E .SUPNO;
-- SELECT * FROM TABLE (dbms_xplan.display);
查看执行计划:
内联视图指的是一种嵌入式的Select语句,用于替换主Select语句中的表。
并列内联视图是指从同一个数据库中的多个表上执行Select语句所获得的内联视图。
对于分布式查询,Oracle数据库采用并列内联视图(Collocated Inline View)来降低网络间传输的数据量,并减少对远程数据库的访问。
发起相关查询实例,利用Oracle的提示(hint)功能给查询语句指定查询计划:
(1)利用NO_Merge提示
添加如下NO_MERGE提示,避免将一个内联视图合并到一个潜在的非并列SQL语句:
/*+NO_MERGE(V)*/
使用NO_MERGE 提示,可以避免将一个内联视图合并到一个潜在的非并列SQL语句中。
(2)利用DRIVING_SITE提示
添加如下DRIVING_SITE提示,指定SQL语句的执行地点:
/*+DRIVING_SITE(E)*/
总结
在分布式数据库环境下,实施不同的优化策略,创建应用场景,并导入一定量的模拟数据,学会使用Oracle的提示(hint)功能以指定不同的查询优化计划,并对相应策略做出解释。
后续会继续更新有关Oracle分布式数据库的内容!
(注:第19次发文,如有错误和疑问,欢迎在评论区指出!)
——2021.11.12