0%

数据库与内存数据库实验报告

一、实验前准备

机器配置

image-20190114060005845

时间计算标准

SQL执行过程

首先,本实验的目的是优化数据库,减少数据库语句执行的时间,在此之前,我们要明白一点数据库执行时间这句话包含了哪些东西。我们从数据库执行一条SQL语句的过程来看,对于MySQL、Oracle、TimesTen这些具有内部优化的数据库来说,一般的执行步骤是:

image-20190113214926175

而我们的关注点应放在语句执行这一步骤上。

语句执行步骤进一步深入

MySQL

MySQL的执行时间为以下项目的加和:

State Desription
1. Checking permissions 检查用户的权限
2. Opening tables 打开表
3. Init 初始化过程
4. System lock 获取锁
5. Optimizing 优化SQL语句
6. Statistics 分析SQL语句
7. Preparing 准备执行条件
8. Executing 执行SQL语句
9. Sending data 进行磁盘的IO以及数据的发送返回
10. End 执行结束
11. Closing tables 关闭表
12. Freeing items 释放资源
13. Cleaning up 清理缓存以及临时空间
Oracle

一条SQL语句在进入语句执行这一步骤之后,若不在高速缓存中,数据库会从数据文件中把所在位置移动到高速缓存中而后返回给客户端。这也就意味着,同一条语句在以后的执行中都只从高速缓存取数据(前提是高速缓存未被清除)。这样想的话,我们要做的优化应该是一条SQL语句在第一次进入数据库时数据库作出的应答。

那么,我们通过数据库工具来查看执行的SQL语句的时间应该是不准的:因为我们不知道这条语句是不是第一次执行,或者说我们不知道高速缓存中有没有我们需要的数据。这里我们选择使用Oracle的执行计划来看SQL语句的准确的执行过程以及其开销。如下图:

image-20190113211220925

我们的关注点在上图中的COST,cost是Oracle里判定效率的唯一标准,Oracle的优化器会计算当前SQL语句的最低cost方案,而后为其选择执行计划。Oracle中定义了语句的一次执行开销cost = CPU cost + IO cost,对于cost,我们可以理解为一次过程所需要访问的Block数量,那么执行时间就是t = Block数量 * Block处理时间

后续实验过程中的Oracle部分我们都是通过执行计划及cost来做对比。为此我们写了一个procedure来记录一条语句执行计划中记录的cost:

-- 计算query的cost
create or replace procedure calc_cost(query_ varchar2, func_ number, desc_ varchar2) is
cpu_cost number := 0;
io_cost number := 0;
cost_ number := 0;
-- 一条SQL语句的唯一标识
hash_v number := 0;
-- 获取上述标识
select_v_sql varchar2(255) := 'select hash_value into :x from v$sql a where a.SQL_TEXT like '':y''';
-- 获取cost
select_v_sql_plan varchar2(255) := 'select max(cpu_cost) , max(io_cost) into :x :y from V$SQL_PLAN a where hash_value=:z';
-- 结果保存
insert_result varchar2(255) := 'insert into t_cost_record values(:x,:y,:z,:a,:b,:c)';
begin
execute immediate select_v_sql using hash_v, query_;
execute immediate select_v_sql_plan using cpu_cost, io_cost, cost_;
execute immediate insert_result using id_seq.nextval, func_, cpu_cost, io_cost, cost_, desc_;
end;
TimesTen

对于TimesTen来说,不如Oracle的优化器来的智能,它完全靠速度制胜。Oracle中我们讨论了执行时间t = Block数量 * Block处理时间,TimesTen就是在Block处理时间上有很大的优势。遗憾的是TimesTen中没有作为本身的高速缓存这一说,这也就意味着一条SQL语句进入TimesTen时都要经过SQL Prepare -> SQL Execution -> SQL Fetch这一完整的过程,如下:

image-20190114052126486

二、MySQL实验过程

功能:查询电影评论平均分排行前一百的电影

SQL语句

select  m.name_, sum(c1.score) as movie_avg_comment_score
from movie m , comment_1 c1
where m.id_ = c1.movie_id
group by m.name_
order by movie_avg_comment_score desc
limit 100;

仅有主键索引

执行之后得到如下的时间消耗:

time_3.2_4min

这个时间相比其他数据库慢得多(oracle 约4s),不符合预期的耗时,且在执行时mysqld的cpu占用率非常高。于是根据以下步骤查看sql执行慢的原因。

MySQL进程表

使用show processlist命令查看正在执行的sql语句列表:

process_list

可以看到当前执行的语句就是我们的目标语句,并且没有其他语句在与当前查询语句竞争资源,所以应该把语句执行过慢点原因定位到查询语句本身。

解释执行计划

通过查看process list得知对应语句有问题之后,使用describe命令查看当前SQL语句的执行计划,MySQL的执行计划与其他相关参数:

explain_3.2

可以看到在执行计划中,movie表有可选的主码索引,但是在这个场景中mysql并没有选择使用主码索引,没有使用索引是导致时间过慢点一个原因,于是可以考虑在电影名字字段上建立索引。

执行过程

为了进一步查看SQL语句具体的系统能耗分布,我们选择使用profiling来分析我们SQL语句的执行过程,在没有创建其他索引的情况下我们得到如下的时间消耗分析:

detail_3.2

我们可以看到其中能耗占比最高的是 Sending data项,查看官方文档相关解释:

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

该线程正在读取和处理SELECT语句的行,并将数据发送到客户端。 由于在此状态期间发生的操作往往会执行大量磁盘访问(读取),因此它通常是给定查询生命周期中运行时间最长的状态

所以这个与我们的磁盘IO的速度以及网络的传输速度有关,磁盘的IO除了受到硬件本身的限制之外还会与数据库的索引有关,更换性能更好的磁盘或者建立适当的索引以减少磁盘IO数量都可以提高查询语句的执行速度。

建立索引

根据以上分析过程得到的结论,我们在电影表的名字字段上建立合适的索引,我们在mysql中选择了B-Tree索引。

建立索引之后再查看相同SQL语句的执行计划:

explain_3.2_index

key字段上的值从原来的null 变成了我们刚刚创建的索引。

执行该SQL语句,并在结束后使用Profiling查看优化后的执行时间:

sending data: 从磁盘读取数据,将数据返回,表示磁盘IO

create index:使用临时表来处理select语句

detail_index_3.2

可以看到Sending data的值明显小于优化前,总的执行时间也变为优化前的1/5,所以增加索引能够在很大程度上加快查询的速度。

实验结论

综合其他实验,在大数据的处理上MySQL数据库的性能远不如ORACLE及TIMESTEN数据库,有数十倍的耗时差距,而且MySQL作为一个轻量级的数据库,支持的索引类型也少于其他两个数据库,在SQL语句的优化方面也不如ORACLE数据库那般强大。所以在当前的实验环境下我们更倾向于使用ORACLE数据库与TIMESTEN数据库进行对比。

三、Oracle实验过程

实验1:SQL各子句条件顺序对查询效率的影响

查询语句

SELECT T_MOVIE.NAME_, T_MOVIE.YEAR_ FROM T_MOVIE,T_MOVIE_REGION,T_REGION
WHERE T_REGION.ID_=T_MOVIE_REGION.REGION_ID AND T_MOVIE.ID_=T_MOVIE_REGION.MOVIE_ID
AND T_REGION.NAME_='美国' AND T_MOVIE.SCORE_>6;

实验方式

通过对MySQL、Oracle、TimesTen中SQL语句中select、from、where子句的排序顺序进行调换,观察执行计划的改变

实验结果

  1. SELECT子句中,结果集的排序方式不会影响执行计划
  2. FROM子句中,各个表的排序方式不会影响执行计划
  3. WHERE子句中,各个条件的排序方式不会影响执行计划,优化器会首先将筛选条件应用于表进行过滤,最后逐次执行表的连接。

分析

自Oracle6以来,一直采用RBO(Rule-Based Optimization 基于规则的优化器),其基于一套严格死板的使用规则。由于其对于规则的崇尚性,SQL语句的写法则尤为重要。而自Oracle8以来,Oracle引入了一种新的优化方式,即CBO(Cost-Based Optimization 基于代价的优化器),从Oracle 10g开始RBO被完全舍弃。使用CBO优化器时,对SQL语句的要求变得没有那么苛刻,优化器会选择开销比较小的方式执行,而不由用户所写的表的顺序、条件的顺序决定。MySQL与TimesTen的优化器也是如此,有其自己的选择。

连接方式和连接顺序

连接顺序:连接顺序表明以哪张表为驱动表来连接其他表的先后顺序。即以某张表为基点,根据其中的信息再去访问其他的表。

连接方式:简单来讲,就是两个表获得满足条件的数据时的连接过程。主要有三种表连接方式,嵌套循环(NESTED LOOPS)、哈希连接(HASH JOIN)和排序-合并连接(SORT MERGE JOIN)。

排序-合并连接

假设有查询:select a.name, b.name from table_A a join table_B b on (a.id = b.id)

内部连接过程:

a) 生成 row source 1 需要的数据,按照连接操作关联列(如示例中的a.id)对这些数据进行排序

b) 生成 row source 2 需要的数据,按照与 a) 中对应的连接操作关联列(b.id)对数据进行排序

c) 两边已排序的行放在一起执行合并操作(对两边的数据集进行扫描并判断是否连接)

延伸:

如果示例中的连接操作关联列 a.id,b.id 之前就已经被排过序了的话,连接速度便可大大提高,因为排序是很费时间和资源的操作,尤其对于有大量数据的表。

故可以考虑在 a.id,b.id 上建立索引让其能预先排好序。不过遗憾的是,由于返回的结果集中包括所有字段,所以通常的执行计划中,即使连接列存在索引,也不会进入到执行计划中,除非进行一些特定列处理(如仅仅只查询有索引的列等)。

排序-合并连接的表无驱动顺序,谁在前面都可以;

排序-合并连接适用的连接条件有: < <= = > >= ,不适用的连接条件有: <> like

嵌套循环

内部连接过程:

a) 取出 row source 1 的 row 1(第一行数据),遍历 row source 2 的所有行并检查是否有匹配的,取出匹配的行放入结果集中

b) 取出 row source 1 的 row 2(第二行数据),遍历 row source 2 的所有行并检查是否有匹配的,取出匹配的行放入结果集中

c) ……

若 row source 1 (即驱动表)中返回了 N 行数据,则 row source 2 也相应的会被全表遍历 N 次。

因为 row source 1 的每一行都会去匹配 row source 2 的所有行,所以当 row source 1 返回的行数尽可能少并且能高效访问 row source 2(如建立适当的索引)时,效率较高。

嵌套循环的表有驱动顺序,注意选择合适的驱动表。嵌套循环连接有一个其他连接方式没有的好处是:可以先返回已经连接的行,而不必等所有的连接操作处理完才返回数据,这样可以实现快速响应。

应尽可能使用限制条件(Where过滤条件)使驱动表(row source 1)返回的行数尽可能少,同时在匹配表(row source 2)的连接操作关联列上建立唯一索引(UNIQUE INDEX)或是选择性较好的非唯一索引,此时嵌套循环连接的执行效率会变得很高。若驱动表返回的行数较多,即使匹配表连接操作关联列上存在索引,连接效率也不会很高。

哈希连接

哈希连接只适用于等值连接(即连接条件为 = )

HASH JOIN对两个表做连接时并不一定是都进行全表扫描,其并不限制表访问方式;

内部连接过程简述:

a) 取出 row source 1(驱动表,在HASH JOIN中又称为Build Table) 的数据集,然后将其构建成内存中的一个 Hash Table(Hash函数的Hash KEY就是连接操作关联列),创建Hash位图(bitmap)

b) 取出 row source 2(匹配表)的数据集,对其中的每一条数据的连接操作关联列使用相同的Hash函数并找到对应的 a) 里的数据在 Hash Table 中的位置,在该位置上检查能否找到匹配的数据

实验2:B树索引与位图索引的比较

sql语句

-- 小基数
SELECT T_MOVIE.NAME_, T_MOVIE.YEAR_
FROM T_MOVIE,T_MOVIE_REGION,T_REGION
WHERE T_REGION.ID_=T_MOVIE_REGION.REGION_ID
AND T_MOVIE.ID_=T_MOVIE_REGION.MOVIE_ID
AND T_REGION.NAME_='美国'
AND T_MOVIE.SCORE_>6;
-- 大基数
SELECT T_MOVIE.NAME_, T_MOVIE.YEAR_
FROM T_ACTOR,T_ACT,T_MOVIE
WHERE T_ACTOR.NAME_='Tom Byron'
AND T_MOVIE.SCORE_>6
AND T_ACTOR.ID_=T_ACT.ACTOR_ID
AND T_MOVIE.ID_=T_ACT.MOVIE_ID;

索引语句

-- B树
CREATE INDEX IX_MOVIE_SCORE ON T_MOVIE(SCORE_);
CREATE INDEX IX_MOVIE_NAME ON T_MOVIE(NAME_);
CREATE INDEX IX_ACTOR_NAME ON T_ACTOR(NAME_);
-- BitMap
CREATE BITMAP INDEX IXBM_MOVIE_NAME ON T_MOVIE(NAME_);
CREATE BITMAP INDEX IXBM_MOVIE_SCORE ON T_MOVIE(SCORE_);
CREATE BITMAP INDEX IXBM_ACTOR_NAME ON T_ACTOR(NAME_);

查询消耗

B树索引(小基数)

B树索引

位图索引(小基数)

位图索引

不加索引(大基数)

image-20190113230831573

B树索引(大基数)

image-20190113230717837

位图索引(大基数)

image-20190113230627090

分析

即使在字段基数较大的情况下,位图索引依然有比B树索引更好的表现。但是有个问题,创建位图索引时所需的时间更长。此外,由于表中该字段的更改都会导致对位图的修改,所以位图索引不适用于并发的情况。

实验3:Oracle优化器对索引的选择

关于索引

索引类型
  • B树索引(默认的索引)
> CREATE INDEX IX_MOVIE_SCORE ON T_MOVIE(SCORE_);
>
  • 位图索引:以位图的形式存储每个值对应的的一组rowid
> CREATE BITMAP INDEX IXBM_REGION_NAME ON T_REGION(NAME_);
>
  • 基于函数的索引:利于对某个字段查询时需要同时使用函数或计算的情景
> CREATE INDEX upper_ix ON employees (UPPER(last_name)); 
>
  • 分区索引:本地分区索引的分区完全依赖于其索引所在表,而全局分区索引的分区机制和表分区可能一样也可能不一样

    • range范围分区
    >   CREATE INDEX cost_ix ON sales (amount_sold)
    > GLOBAL PARTITION BY RANGE (amount_sold)
    > (PARTITION p1 VALUES LESS THAN (1000),
    > PARTITION p2 VALUES LESS THAN (2500),
    > PARTITION p3 VALUES LESS THAN (MAXVALUE));
    >
  • hash哈希分区

    >   CREATE INDEX cust_last_name_ix ON customers (cust_last_name)
    > GLOBAL PARTITION BY HASH (cust_last_name)
    > PARTITIONS 4;
    >
  • list列表分区:一个分区对应指定列的特定的值,以列举的方式进行分区
  • 组合分区(range-hash,range-list)
什么时候用索引

对于Oracle的CBO来说,只有在使用索引能提高效率(估算的效率)时才会使用索引。对于程序员自己进行数据库管理的时候,一般有:

需要使用索引来优化查询的情况:

  • 一个属性的值分布非常广,变化的范围跨度很大。
  • 一般来说,常常需要被用在SQL语句的where中的限制条件的属性最好为其建立索引。
  • 表经常被访问且需要访问的数据量仅占一部分。

不适合用索引的情况:

  • 表很小
  • 表经常被更新
  • 属性不经常作为where中的限制条件的属性存在
  • 查询得到的数据占总量的很大部分

对于数据经常更新的情况,DBA要定时进行索引的重构(rebuild)以维持索引的可用性。

影响优化器决策的因素
  • 进行全表扫描需要读取的数据块数量;
  • 进行索引查询需要读取的数据块数量,这主要是基于对WHERE子句谓词返回的记录数目估计;
  • 进行全表扫描时多块读的相关开销,以及为满足索引查询进行的单块读的开销;
  • 内存中对缓存中的索引块和数据块数目的假设。
索引失效的可能原因

以下是一些常见的定义了索引当Oracle并未使用的原因:

  • 不等于情况,即“<>”
  • 字符串匹配like中百分号在第一位的情况,即“%XXX”
  • 表没有进行分析更新统计信息
  • 使用复合索引但单独引用且非复合索引的第一属性
  • 对索引进行计算,此时需要建立索引函数
  • 属性为字符串但在where中没有加引号
  • 使用not in,not exists
  • 使用了其他索引

强制使用索引

如果想要强制使用索引,则可以在查询语句的select单词后加上/*+index (tablename indexname)*/,这样可以规定Oracle选择使用indexname的索引的执行计划。该方法已在前面实验中使用,不再赘述。

sql语句

SELECT T_MOVIE.NAME_, T_MOVIE.YEAR_
FROM T_MOVIE,T_MOVIE_REGION,T_REGION
WHERE T_REGION.ID_=T_MOVIE_REGION.REGION_ID
AND T_MOVIE.ID_=T_MOVIE_REGION.MOVIE_ID
AND T_REGION.NAME_='美国'
AND T_MOVIE.SCORE_>6;

查询消耗

不用索引(不论是B树索引还是位图索引都不使用)

3-不用索引

强制使用B树索引

3-强制B树

强制使用位图索引

3-强制位图

sql语句

SELECT T_MOVIE.NAME_, T_MOVIE.YEAR_
FROM T_MOVIE,T_MOVIE_REGION,T_REGION
WHERE T_REGION.ID_=T_MOVIE_REGION.REGION_ID
AND T_MOVIE.ID_=T_MOVIE_REGION.MOVIE_ID
AND T_REGION.NAME_='美国'
AND T_MOVIE.SCORE_>9;

查询消耗

B树索引(未使用)

3-B树

位图索引

3-位图

分析

由此可见,即使在有索引的情况下,oracle优化器也可能选择不使用索引。CBO优化器会对每种执行计划计算一个COST,并采用COST最小的执行计划。如果一个表有索引或多种索引,其会选择最好的一种索引方式扫描表,或者甚至不用索引而用全局扫描方式。

另外对于符合筛选条件的数据,当占全表的比例越小、数据量越小时,使用索引的可能性越大。如在这次实验中,条件为”T_MOVIE.SCORE_ >9”时会使用索引,而”T_MOVIE.SCORE_ >6”时不会。

此外,由于位图索引导致的COST要小于B树索引,因此在相同的查询中,使用位图索引的可能性比B树索引更大。

实验4:Oracle分区索引

sql语句

SELECT T_COMMENT.SUMMARY_,T_COMMENT.SCORE_,T_COMMENT.TIME_
FROM T_COMMENT,T_MOVIE
WHERE T_MOVIE.ID_=T_COMMENT.MOVIE_ID
AND T_COMMENT.SCORE_>6
AND T_MOVIE.NAME_='Blindsided';

实验结果

  1. 未分区表+无索引

    5-1-1

  2. 未分区表+B树索引

    5-2

  3. 未分区表+位图索引

    5-3

  4. 分区表+无索引

    5-4-1

  5. 分区表+全局不分区B树索引

    5-5-1

  6. 分区表+本地(哈希分区)B树索引

    5-6

  7. 分区表+本地(哈希分区)位图索引

    5-7

  8. 分区表+全局哈希分区索引

    5-8

  9. 分区表+全局范围分区索引

    5-9

分析

  1. 在未建索引时,分区表的COST是未分区表的十倍多。原因是分区所依据的键(字段)不是直接的查询条件——我们以评论表的movie_id字段为依据建哈希分区表,但在查询的时候并不直接以movie_id为查询条件。导致连接表的时候,需要访问多个分区,反而造成COST大大增长。

    后来我们重新设计一个以movie_id为查询条件的sql语句,结果显示分区表的COST大约是未分区表的1/4(一共分了4个区),证明在以分区依据的字段为直接查询条件时,分区表能够体现比较好的性能,能够避免对一部分数据的访问。

  2. 在分区表上建索引比在未分区表上建索引后的开销更小,不论分区表上的索引是全局还是本地,不论是否是分区索引。在我们的实验场景中,尽管movie_id不是直接的查询条件而是join表的条件,但是在添加索引后,依然能够大大减少join表的开销从而提升效率。

  3. 在我们的实验场景中,全局的分区索引,不论是哈希分区还是范围分区,COST是一样的。

  4. 本地索引的效率略微比全局索引的效率好。根据查到的资料,本地索引的可维护性好,能够自动维护,不需要人工干预,但因把索引分成多个分区导致每次的索引访问都需要遍历所有索引分区,所以索引访问性能下降。因此比较适合OLAP系统。而全局索引的可维护性差,分区表发生改变时,需要用命令手动更新索引,但索引访问性能比本地分区索引要好。因此比较适合OLTP系统。

实验5:Oracle使用复合索引

SQL语句

select T_COMMENT_1.SUMMARY_, T_COMMENT_1.SCORE_
from T_COMMENT_1, T_MOVIE
where T_MOVIE.ID_ = T_COMMENT_1.MOVIE_ID and T_COMMENT_1.SCORE_ > 7 and T_MOVIE.NAME_ = 'The Notebook';

第一次查询:T_COMMENT_1上只有主键的唯一索引。

第二次查询:在MOVIE_ID上建立一个B-tree索引COMMENT_1_MOVIE。

create index COMMENT_1_MOVIE on T_COMMENT_1(MOVIE_ID);

第三次查询:使第二次的索引invisible,在SCORE_上建立一个B-tree索引T_COMMENT_SCORE_INDEX。

alter index COMMENT_1_MOVIE invisible;
create index T_COMMENT_SCORE_INDEX on T_COMMENT_1(SCORE_);

第四次查询:将第二次和第三次的索引都保持为visible,在MOVIE_ID和SCORE_上建立一个复合索引COMMENT_1_MOVIE_SCORE。

alter index COMMENT_1_MOVIE visible;
create index COMMENT_1_MOVIE_SCORE on T_COMMENT_1(MOVIE_ID, SCORE_);

实验结果

第一次查询:

屏幕快照 2019-01-13 下午10.48.45

全表扫描,花销很大

第二次查询:

屏幕快照 2019-01-13 下午10.47.49

利用在MOVIE_ID上的索引,在T_COMMENT_1中访问的数据量和花销都大幅度下降。

第三次查询:

屏幕快照 2019-01-13 下午10.50.07

如果只有在SCORE_上的索引,根据CBO,Oracle并没有使用这个索引,而是依旧使用全表扫描,可知该索引并没有提升性能。

易知,如果在这个时候将MOVIE_ID上的索引设为visible,Oracle会使用MOVIE_ID上的索引。

第四次查询:

屏幕快照 2019-01-13 下午10.57.10

Oracle使用了复合索引,尽管在当前问题下COST花销与只有MOVIE_ID的索引差不多,但是其访问的记录数(CARDINALITY)显著减小,体现了复合索引给查询带来的性能提升。

实验6:物化视图对SQL查询性能的提升

SQL语句

原始查询语句:

select T_DIRECTOR.NAME_, T_MOVIE.NAME_ MOVIE_NAME, AVG(T_COMMENT_1.SCORE_) SCORE
from T_DIRECTOR, T_DIRECT, T_MOVIE, T_COMMENT_1
where T_DIRECTOR.ID_ = T_DIRECT.DIRECTOR_ID and T_DIRECT.MOVIE_ID = T_MOVIE.ID_ and T_COMMENT_1.MOVIE_ID = T_MOVIE.ID_ and T_DIRECTOR.NAME_ like '黑泽明%'
group by T_DIRECTOR.NAME_, T_DIRECTOR.ID_, T_MOVIE.NAME_, T_MOVIE.ID_, T_MOVIE.YEAR_ ;

创建一个普通视图

CREATE VIEW DIRECTOR_MOVIE_FAKE
AS
select T_DIRECTOR.NAME_, T_DIRECTOR.ID_, T_MOVIE.NAME_ MOVIE_NAME, T_MOVIE.ID_ MOVIE_ID, T_MOVIE.YEAR_, AVG(T_COMMENT_1.SCORE_) SCORE
from T_DIRECTOR, T_DIRECT, T_MOVIE, T_COMMENT_1
where T_DIRECTOR.ID_ = T_DIRECT.DIRECTOR_ID and T_DIRECT.MOVIE_ID = T_MOVIE.ID_ and T_COMMENT_1.MOVIE_ID = T_MOVIE.ID_
group by T_DIRECTOR.NAME_, T_DIRECTOR.ID_, T_MOVIE.NAME_, T_MOVIE.ID_, T_MOVIE.YEAR_;

使用普通视图进行查询:

select NAME_, MOVIE_NAME, SCORE from DIRECTOR_MOVIE_FAKE where NAME_ like '黑泽明%';

创建物化视图

CREATE MATERIALIZED VIEW DIRECTOR_MOVIE
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
select T_DIRECTOR.NAME_, T_DIRECTOR.ID_, T_MOVIE.NAME_ MOVIE_NAME, T_MOVIE.ID_ MOVIE_ID, T_MOVIE.YEAR_, AVG(T_COMMENT_1.SCORE_) SCORE
from T_DIRECTOR, T_DIRECT, T_MOVIE, T_COMMENT_1
where T_DIRECTOR.ID_ = T_DIRECT.DIRECTOR_ID and T_DIRECT.MOVIE_ID = T_MOVIE.ID_ and T_COMMENT_1.MOVIE_ID = T_MOVIE.ID_
group by T_DIRECTOR.NAME_, T_DIRECTOR.ID_, T_MOVIE.NAME_, T_MOVIE.ID_, T_MOVIE.YEAR_;

设置创建时生成数据按需要刷新刷新方式为FORCE

根据视图进行如上查询:

select NAME_, MOVIE_NAME, SCORE from DIRECTOR_MOVIE where NAME_ like '黑泽明%';

由于物化视图与表类似,可以给其建立索引,以下给导演名建立索引

CREATE BITMAP INDEX DIRECTOR_MOVIE_INAME_INDEX ON DIRECTOR_MOVIE (NAME_);

再次使用物化视图查询

select NAME_, MOVIE_NAME, SCORE from DIRECTOR_MOVIE where NAME_ like '黑泽明%';

实验结果

使用原始查询:

屏幕快照 2019-01-14 上午12.27.37

具有极大的花销。

创建视图后的查询:

屏幕快照 2019-01-14 上午12.30.46

其执行计划与原始查询一致。

创建物化视图后的查询:

屏幕快照 2019-01-14 上午12.33.27

直接在物化视图中进行查询,执行计划即为简单,花销大幅度减小。

给物化视图创建索引后的查询:

屏幕快照 2019-01-14 上午12.35.22

建立索引后通过范围索引扫描该物化视图进行查询,其COST数字小得令人惊奇。

分析

1.建立普通视图并不能提升性能。因为普通是虚拟的,对视图的操作实际都转变为了对各表的SQL操作,其与原始查询完全一致。

2.物化视图是一种物理表,对于物化视图的查询是直接的,跟表一样。因此建立物化视图可以大幅度减小花销,但是同时,物化视图也会产生大量的维护成本。因此程序员应该根据实际情况建立物化视图以优化查询。

3.物化视图同样可以增添索引,增加索引后Oracle对物化视图可以通过索引进行扫描,进一步提高效率。

物化视图与普通视图

视图只是一种虚拟表。实际上,对视图的查询真正转换成了相应的SQL语句再对各表进行连接查询,因此其性能提升有限,只是方便了使用

而物化视图是实质化的视图,是物理表,可以像表一样进行查询,建立索引,占用真正的存储空间,需要被刷新。

刷新模式

on demand:顾名思义,仅在该物化视图“需要”被刷新了,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性;

on commit:提交触发,一旦基表有了commit,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致。一般用这种方法在操作基表时速度会比较慢。

创建物化视图时未作指定,则Oracle按 on demand 模式来创建。

刷新方法

完全刷新(COMPLETE): 会删除表中所有的记录(如果是单表刷新,可能会采用TRUNCATE的方式),然后根据物化视图中查询语句的定义重新生成物化视图。

快速刷新(FAST): 采用增量刷新的机制,只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。对于增量刷新选项,如果在子查询中存在分析函数,则物化视图不起作用。

FORCE方式: 这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件,如果满足则进行快速刷新,否则进行完全刷新。

实验7:Oracle In Memory性能分析

Sql语句

SELECT T_MOVIE.NAME_, SUM(T_COMMENT_2.SCORE_) s FROM T_MOVIE,T_COMMENT_2 WHERE T_MOVIE.ID_=T_COMMENT_2.MOVIE_ID GROUP BY T_MOVIE.NAME_ ORDER BY s DESC;

设置In Memory

ALTER TABLE T_MOVIE.NAME_ IN MEMORY;

实验结果

原始查询

no-inmemory

In Memory查询:

inmemory

结果分析

遗憾的是与想象的不同,Oracle和Oracle In Memory在COST上面结果相同,但是事实上在我们同样的实验环境下测试二者时间时,In Memory确实会比Oracle好很多。其实简单思考一下,这是应该的,前面我们说过执行时间t = Block数量 * Block处理时间,不难知道差距还是出在Block处理时间上。

实验8:Oracle执行计划浅析(Oracle表的访问方式)

对T_MOVIE表进行查询,其本身有在其主码(ID_)上的UNIQUE INDEX和LENGTH_上的B-tree INDEX。

根据UNIQUE INDEX(ID_)返回唯一记录

select * from T_MOVIE where ID_ = 20050;

屏幕快照 2019-01-13 下午9.14.59

使用的是索引唯一扫描

根据ID_返回少部分记录

select * from T_MOVIE where ID_ < 10;

屏幕快照 2019-01-13 下午9.25.22

使用的是索引范围扫描

根据LENGTH_返回大量数据

select * from T_MOVIE where LENGTH_ <100;

image-20190114004746346

全查询MOVIE_和TYPE_返回其ID_

全查询MOVIE_:

select ID_ from T_MOVIE;

屏幕快照 2019-01-13 下午9.32.45

采用的是索引快速扫描(因为数据量较多)

屏幕快照 2019-01-13 下午9.38.39

且返回结果无顺序(从578开始,一段有序,即代表是一个索引数据块)。

全查询TYPE_:

select ID_ from T_TYPE;

屏幕快照 2019-01-13 下午9.35.11

采用的是索引全扫描(因为数据量较小)

屏幕快照 2019-01-13 下午9.37.57

返回结果有顺序

执行计划中的访问方式

访问方式即分为全表扫描(TABLE ACCESS FULL)和各种类型索引扫描(TABLE INDEX SCAN)。Oracle会根据表和索引的信息,推算执行的SQL语句从表中取多少数据以及这些数据是怎么分布的。

TABLE ACCESS FULL(全表扫描)

Oracle会读取表中所有的行,并检查每一行是否满足SQL语句中的 where限制条件。全表扫描时可以使用多块读(即一次I/O读取多块数据块)操作来提升吞吐量。数据量太大的表不建议使用全表扫描,除非本身需要取出的数据较多,占到表数据总量的 5% ~ 10% 或以上

TABLE ACCESS ROWID(通过ROWID的表存取)

ROWID是由Oracle自动加在表中每行最后的一列伪列,表中并不会物理存储ROWID的值。程序员可以像使用其它列一样使用它,但不能对该列的值进行增、删、改操作。一旦一行数据插入后,则其对应的ROWID在该行的生命周期内是唯一的,即使发生行迁移,该行的ROWID值也不变。

ROWID可以被视为每条记录的“指针”。它指出了该行所在的数据文件、数据块以及行在该块中的位置,所以通过ROWID可以快速定位到目标数据上,这也是Oracle中存取单行数据最快的方法

TABLE ACCESS BY INDEX SCAN(索引扫描)

在索引块中,既存储每个索引的键值,也存储具有该键值的行的ROWID。因此索引扫描其实分为两步:扫描索引得到对应的ROWID;通过ROWID定位到具体的行读取数据。

索引扫描主要分为以下几种:

INDEX UNIQUE SCAN 索引唯一扫描

对应UNIQUE INDEX(唯一性索引)的扫描方式,其只会应用在返回一条记录的情况下。该点在之前的实验中已经描述。

INDEX RANGE SCAN 索引范围扫描

主要是使用在需要返回多行记录的情况下,常见为以下三种:

  • 在唯一索引列上使用了范围操作符(如:> < <> >= <= between)
  • 在组合索引上,只使用部分列进行查询(查询时必须包含前导列,否则会走全表扫描)
  • 对非唯一索引列上进行的任何查询

如果在查询的过程中需要访问的记录数很多,分布很广,这个时候Oracle会根据CBO原则认为使用索引的花销可能比全表扫描大,会使用全表扫描。

INDEX FULL SCAN 索引全扫描

进行全索引扫描时,查询出的数据都必须从索引中可以直接得到。其常发生在要查询的列包含唯一索引且需要对表中的所有数据都要查询。索引全扫描返回的结果有顺序。

INDEX FAST FULL SCAN 索引快速全扫描

索引快速全扫描与索引全扫描类似,只是其在查找索引时会用一种更为快速的方式(简单来说是根据索引块的物理顺序而省去较为繁琐的逻辑顺序),其更适合于数据量大的表进行全查询,其一个特点就是返回的记录不按照顺序。

四、TimesTen实验过程

实验概述

调用自己改写的 AliTT11.sql,查看 SQLPrepare,SQLExecute,FetchLoop 的查询时间;

所有实验中,查询时间分为增加索引前、增加索引后、按照 timesten 建议添加索引三类,针对每一类时间分别有第一次执行时间和之后的平均查询时间两种;

在首次执行查询语句时,timesten首先需要对语句进行预编译,因此首次执行的 SQLPrepare 时间相比之后的时间较长,之后的准备时间就相应缩短了很多。

实验1

实验内容

某地区评分6以上的所有电影的名字和上映时间

查询语句

SELECT DBIM.T_MOVIE.NAME_, DBIM.T_MOVIE.YEAR_
FROM DBIM.T_MOVIE, DBIM.T_MOVIE_REGION, DBIM.T_REGION
WHERE DBIM.T_REGION.ID_ = DBIM.T_MOVIE_REGION.REGION_ID
AND DBIM.T_MOVIE.ID_ = DBIM.T_MOVIE_REGION.MOVIE_ID
AND DBIM.T_REGION.NAME_ = '美国'
AND DBIM.T_MOVIE.SCORE_ > 6;

添加的索引

表明 列名 索引类型 是否唯一
Movie id_ hash unique
Movie score_ range
Region id_ hash unique
Movie_region region_id hash
Movie_region movie_id hash

查询时间

时间类型 Before1 Before2 After1 After2 建议1 建议2 提高百分比
SQLPrepare 0.001845 0.000059 0.000878 0.000054 0.000807 0.000055
SQLExecute 0.075809 0.061819 0.000037 0.000025 0.000034 0.000025 99.96%
FetchLoop 0.000004 0.000002 0.000002 0.000001 0.000003 0.000002
  • 执行计划 (before)

11b

  • 执行计划 (after)

11a

原因分析

添加索引后速度大大提升,因为在 region 表中指定了查询条件,添加索引后可以快速从表项中匹配到指定条件的项;在添加之前,timesten 自动帮我们在 movie 表上的 id 字段上添加了临时哈希索引,除此之外,我们额外为几个 where 条件语句的查询字段都增加了索引, 因此提高了效率。

执行计划

  • before

    在两层嵌套循环中,顺序执行在region表中的查询、region表与联系表的join,循环结束后生成一个指定地区内的所有电影联系表;内层嵌套完成后,通过散列索引匹配movie表与内存循环生成的联系表,join筛选后生成结果列表

  • after

    添加索引之后,过程与添加之前相同,但由于内层循环内使用散列索引而不是顺序执行,因此查询速度比较快,加上没有临时创建索引的时间开销,所以相比之下大大提高了查询效率。

实验2

实验内容

所有地区全部电影的平均评分排行榜(前100)

查询语句

SELECT * FROM (SELECT DBIM.T_REGION.NAME_, SUM(DBIM.T_MOVIE.SCORE_) s
FROM DBIM.T_REGION, DBIM.T_MOVIE_REGION, DBIM.T_MOVIE
WHERE DBIM.T_MOVIE.ID_ = DBIM.T_MOVIE_REGION.MOVIE_ID
AND DBIM.T_REGION.ID_ = DBIM.T_MOVIE_REGION.REGION_ID
GROUP BY DBIM.T_REGION.NAME_
ORDER BY s DESC) WHERE ROWNUM < 101;

添加的索引

表明 列名 索引类型 是否唯一
Movie id_ hash unique
Movie score_ range
Region name_ hash unique
Region id_ hash unique
Movie_region region_id hash
Movie_region movie_id hash

查询时间

时间类型 Before(1) Before(2) After(1) After(2) 建议(1) 建议(2) 提高百分比
SQLPrepare 0.001253 0.000081 0.001004 0.000054 0.000985 0.000056
SQLExecute 0.353111 0.335902 0.337983 0.313458 0.313004 0.312695 7%
FetchLoop 0.000045 0.000020 0.000018 0.000018 0.000018 0.000017
  • 执行计划 (before)

12b

  • 执行计划 (after)

12a

原因分析

添加索引之前,timesten 自动在 movie 和 region 表的 id 字段上都设置了相应的哈希索引,而我们添加索引后与添加之前的执行计划中的索引项没有差别,因此效率几乎没有变化,加上 sum 聚合操作、group by、order by 操作都要进行费时间的全表扫描,所以需要较长时间完成查询。

实验1和2分析总结

指定条件的查询:

  • 建立索引之前

    timesten在某个相对较小的表上建立临时索引(散列索引或范围索引),在其他表上进行顺序扫描,执行查询语句中的条件匹配,建立索引的过程会造成时间上的消耗;

  • 建立索引之后

    自己建立的索引覆盖timesten优化建立的索引,由于索引提前建立,因此没有建立索引带来的额外时间开销,而且在此类查询中我们在所有查询涉及字段上都建立了索引(tt自身优化通常只在一个表上建立索引),所以与建立索引之前相比有极大的性能提升。

聚合查询:

  • 执行计划Before:

    先顺序扫描关系表act的记录字段id,利用临时HASH索引 actor.id_,将act表中对应记录与act的记录通过字段相连;对(这些/该)拼接记录逐条利用临时HASH索引 movie.id ,接上movie表中符合条件的记录字段。

  • 执行计划After:

    先顺序扫描关系表movie的记录字段id ,利用HASH索引 act.id_,将act表中对应记录与act的记录通过字段相连;针对第一次hash检索出的 act.id,再对(这些/该)拼接记录逐条利用临时HASH索引 actor.id ,接上actor表中符合条件的记录字段。

  • 主要原因在于:第一次顺序扫描的关系表act,外码引用actor表的主码(1:1),movie表(1:1),hash索引查询唯一记录快;第二次顺序扫描的表为movie表,将对应多条act表里的记录(1:many),对应多个演员(1:many)。

实验3:AWT

创建 AWT 直写缓存组

  • 缓存表
    • t_moive
    • t_comment_1
  • 选择理由
    • 动态缓存组适用于不从 oracle 中预加载数据的场景
    • Movie 表和评论表体量较大,不需要从 oracle 中提前加载

测试 AWT 修改数据

  • 修改电影评论表

  • 修改语句

    UPDATE DBIM.T_COMMENT_1
    SET SUMMARY_='A'
    WHERE DBIM.T_COMMENT_1.SCORE_>8
    AND DBIM.T_COMMENT_1.MOVIE_ID = 1;
  • 踩坑

    • update语句指定修改的表名后,set字段不需要再次声明表名(否则报错)
    • 修改数据前要开启 replication agent
    • 执行update语句后要提交事务

实验4:查看不同数据类型对查询效率的影响

表字节大小

表名 行数 字节大小 有数据类型映射的字节大小 节约百分比
Movie 292352 47301232(nomapping) 26293000(optimal) 45%
Comment 9805336 2528884600(nomapping) 404967952(optimal) 84%

压缩设置

image-20190114062154430

数据类型映射结果

字段 noMapping standardMapping aggressive
Region id_ NUMBER(11,0) TT_BIGINT TT_SMALINT
Region name_ VARCHAR(255 BYTE) VARCHAR(255 BYTE) VARCHAR(80 BYTE)

结果分析

  • 对相同的表来说,从 oracle 导入 timesten 中如果不进行压缩(nomapping),与进行最优化数据类型映射+aggressive mapping + optimal compression 相比,大约浪费了45%的空间;
  • 对于不同数量级的表来说,千万数量级的 comment 表不进行压缩时浪费84%所有的空间,比十万数量级的 movie 表浪费的空间多了接近一倍。

实验5:根据优化建议建立索引

SQL语句

Command> call ttIndexAdviceCaptureOutput(0);
< 6, create index T_MOVIE_i1 on DBIM.T_MOVIE(ID_); >
< 7, create index T_COMMENT_1_i2 on DBIM.T_COMMENT_1(MOVIE_ID,SCORE_); >
2 rows found.

实验对象

  • 实验3.2语句

实验结果

  • Before:自己建立索引后的查询时间
  • After:根据 timesten 查询优化建议建立索引后的查询时间
时间类型 Before(1) Before(2) After(1) After(2) 提高百分比
SQLPrepare 0.001527 0.000049 0.000822 0.000049
SQLExecute 4.139655 3.556375 3.301318 3.302092 7.18%
FetchLoop 0.000047 0.000027 0.000018 0.000017