读收获不止oracle--表设计有感

最新推荐文章于 2023-01-02 22:50:45 发布

诛仙89

最新推荐文章于 2023-01-02 22:50:45 发布

阅读量374

点赞数

分类专栏： oracle 文章标签：数据库 oracle 表设计

oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

表更新日志开销大

首先说下日志：
当SQL(更新、删除、插入)执行后，点击commit或者不conmmit，oracle都不会把SGA里面的数据缓冲区里面的数据立刻刷到数据库文件中的，而是采取的批量刷入。由进场CKPT来控制DBWR来写入。
这样就会产生很多其他的问题，比如突然断电，SGA里面的数据缓冲区里面的数据就会没有了，如果没有日志，这样用户提交的数据永远也刷不到数据文件里面去了。所以在执行SQL后，就会记录日志，首先写到日志缓冲区里面去，然后LGWR进场把日志写到日志文件中去。如果断电之后想恢复，只要调用日志，从新执行一遍就行。这个过程叫做redo

如果用户执行Sql后，想回滚呢，所以oracle在执行sql后在回滚表空间会分配到内存，同时在数据缓冲区里面会记录前镜像(SQL执行之前的数据)，这些前镜像数据也会刷入到回滚表空间的数据文件中，也是CKPT决定什么时候刷入，这一些列动作都会记录日志，并且写到日志缓冲区里面，并且由LGWR写到日志文件里面。当分配表空间和记录前镜像都做好了，才允许更改数据缓冲区里面的数据，然后等待CKPT讲数据缓冲区的数据刷到数据文件中。
如果用户想回滚，就取出前镜像(这个时候前镜像应该是在回滚表空间的数据文件中，即使断电也没问题)，然后完成回滚。这个过程叫undo 看以看出来undo里面的很多操作都会写成日志，就是怕数据丢失，比如前镜像里面的数据还没刷入到回滚表空间数据文件中断电了，用户想回滚，但是钱镜像丢失了怎么办！所有所有的undo操作都需要 redo保护

创建试图能查看当前日志量：

create or replace view v_redo_size as
select a.name ,(b.value/1024/1024)as sizesMB
from v$statname a,v$mystat b
where a.statistic#=b.STATISTIC# and a.name='redo size';

查询之后发现日志量是0.04578MB

create table t as select * from dba_objects;

创建表然后插入数据后日志量是：0.1055MB 创建和插入大概只有0.6Mb
然后执行

delete  t;

删除后产生redo值:25.57MB 大概有24MB，可见删除操作产生的redo最多，因为删除不仅要记录rowid，可以在重新删除一次，还要记录undo操作的redo，删除产生的undo最多，因为删除的反向操作是插入，想要插入就要记录要插入的所有字段。肯定很多undo，而undo是需要redo保护的，所以对应也产生了很多redo。

执行更新操作

update t set object_id = rownum;

产生的redo量也是不到1MB

现在又一个问题，如果这个表只是作为一个中间表，即使数据丢失也没问题，那么产生这么多日志量无疑就是浪费性能，这也就引出了 全局临时表

delete无法释放空间

执行

select * from t;

  0  db block gets
5749  consistent gets
cost CPU  282

可以看到逻辑读是consistent gets+db block gets = 5749次逻辑读

执行delete t;
//然后在执行
select * from t;

     0  db block gets
  1030  consistent gets
  cost CPU  282

可以看到逻辑读竟然还是1030，这就说明普通表无法里面回收内存

SQL> truncate table t;
SQL> select * from t;
  1  db block gets
 40  consistent gets

截断表，但是普通表truncate 不能带where 条件；
这也引出了另外一种表分区表，结合truncate 可以加分区条件可以立马回收内存，分区表的一个好处

表记录检索太慢

如果普通堆表，数据量非常大，那么第一个解决办法是索引，一把双刃剑
另外一个解决办法是创建分区表。根据经常检索的条件创建分区表，
这里写图片描述

这里思考一个问题，分区表同样也有坏处，如果查询的条件不是分区条件，反而会让检索变慢吧这里待补充！！！！！！

索引回表开销很大

查找索引后如果所有字段都在索引中，那么就不需要回表，那么很大可能是需要回表的
这里可以看到另外一种表 索引组织表

有序插入却难有序读出

这种非常常见，想要有序
1 采用order by 这样会增加很多消耗，性能降低，但是如果order by里面的字段是在所有中就可以消除排序。但是索引也不能太多
2 采用有序散列聚族表。

上面虽然列出来了很多普通表的缺点，但是普通表是能满足大部分需求的，那些有特点的表的使用是有局限，一定要了解要局限才能使用。这点非常重要

全局临时表

全局临时表有两种
一种是基于事物的 on commit delete rows;
一种是基于session的 on commit preserve rows;

创建基于session的全局临时表

create global temporary table t_temp_session on commit preserve rows

创建基于transaction的全局临时表

 create global temporary table t_temp_transaction on commit delete  rows

观察delete操作产生的redo量
都是插入同样的数据，删除同样的数据
普通表

SQL> select * from v_redo_size;

NAME                                                                SIZESMB
---------------------------------------------------------------- ----------
redo size                                                        47.1520653

SQL> delete t;

已删除72015行。
SQL> select * from v_redo_size;

NAME                                                                SIZESMB
---------------------------------------------------------------- ----------
redo size                                                        72.6249542

大概产生了25MB

基于session

SQL> select * from v_redo_size;

NAME                                                                SIZESMB
---------------------------------------------------------------- ----------
redo size                                                        26.1089325

已用时间:  00: 00: 00.00
SQL> delete t_temp_session;

已删除72015行。

已用时间:  00: 00: 00.46
SQL> select * from v_redo_size;

NAME                                                                SIZESMB
---------------------------------------------------------------- ----------
redo size                                                        47.0722733

产生了大概 21MB

基于事物的

SQL> select * from v_redo_size;

NAME                                                                SIZESMB
---------------------------------------------------------------- ----------
redo size                                                        73.0083351

已用时间:  00: 00: 00.00
SQL> delete t_temp_transaction;

已删除72015行。

已用时间:  00: 00: 00.50
SQL> select * from v_redo_size;

NAME                                                                SIZESMB
---------------------------------------------------------------- ----------
redo size                                                        93.9719315

产生了大概20MB

可以看到全局临时表产生的日志量没有普通表多

基于事物的全局临时表的高效删除

//首先插入数据
SQL> insert into t_temp_transaction select * from dba_objects;

已创建72015行。
SQL> select count(*) from t_temp_transaction;

  COUNT(*)
----------
     72015

//查看日志量
SQL> select * from v_redo_size;

NAME                                                                SIZESMB
---------------------------------------------------------------- ----------
redo size                                                        94.3680611


/提交
commit；

//查看数据  发现已经删除了
SQL> select count(*) from t_temp_transaction;

  COUNT(*)
----------
         0
//再次查看日志量  反向日志量基本没增加 这才是高效删除
SQL> select * from v_redo_size;

NAME                                                                SIZESMB
---------------------------------------------------------------- ----------
redo size                                                        94.3681793