收获不止是oracle 阅读提纲(思维导图)

最新推荐文章于 2024-05-28 15:20:48 发布

布Coder

最新推荐文章于 2024-05-28 15:20:48 发布

阅读量724

点赞数 21

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/haizhuang_bu/article/details/136259107

版权

ORACLE
- 1. 数据库(database)
  - 组成
    - 表空间 tablespace
    - 段 segement
      - segement 创建成功之后会分配初始的区域 extent
    - 区 extent
    - 块 block
      - 数据头 (header)
        
        块地址 block address
        
        段属性(表|索引)
      - 表目录
        
        所在表的信息
      - 行目录
        
        插入行的地址
      - 可用空间
        
        块中剩余空间
      - 行数据区
  - 表
    - 全局临时表
      - 全局临时表
        
        特性
        
        1. 高效删除记录
        
        2. 不同会话独立
      - 全局事物表
    - 普通表
      - T 表
        
        T 表段
        
        T 索引段(具有索引后)
      - 只查询索引段信息,就不访问表
    - 外部表
    - 分区表
      - 范围分区
        
        partition by range
        
        全局索引
        
        局部索引
        
        create index index_name on table(column) local;
    - 索引组织表
      - 索引结构
        
        root 根节点
        
        branch 茎节点
        
        left 叶节点
        
        主要存储 key column value rowId
      - 特点
        
        1. 索引高度较低
        
        2. 索引存储列值
        
        3. 索引本身有序
      - table access by index rowid (是否回表)
      - 聚合因子决定查询速度基本有序
      - 位图索引 bitMap (重复基数高的情况下高效)
        
        1. 列有大量重复
        
        2. 更新情况极少
      - 函数索引
        
        建立函数索引
        
        create index indexName on table(to_upper(column))
        
        避免列运算
        
        不执行索引, 尽量使用value 运算
- 2. 实例 (instance)
  - SGA(共享区)
    - 组成
      - 共享池(share pool)
      - 数据缓存db cache
      - 日志缓存 log buffer
      - PMON(processes monitor) 进程监控
      - SMON (System Monitor) 系统监视器
      - LCKn
      - RECO (Distributed Database Recovery) 分布式数据库恢复
      - CKPT
      - DBWR
      - LGWR
      - ARCH
    - 查询语句
      - show parameter sga
  - PGA(私有区)
    - 组成
      - 1. 保存用户的链接信息,会话属性绑定变量
      - 2. 保存用户权限信息
      - 3. 排序区 (内存不够时在磁盘上排序)
    - 查询语句
      - show parameter pga
- 3. update 更新语句
  - 执行步骤
    - 1. 查询当前要更新的数据是否存在数据缓存区(dbCache),不存在从磁盘中读取到数据缓存区
    - 2. 在回滚事物段、开辟一个事务槽
    - 3. 在数据缓存区、创建修改数据的前镜像、前镜像数据也会写入到磁盘文件中(回滚表空间的数据文件),
    - 4. 记录日志缓存区
    - 5. 此时用户执行了提交、日志缓存区立刻记录这个提交信息、回滚事务段标记为非激活INACTIVE状态、表示允许重写
    - 6. 若是执行回滚操作、需要将前镜像数据从回滚段中读取出来、修改数据缓存区dbCache、完成回滚
- 4. undo日志 (反向操作记录)
  - delete 产生的undo日志最多、需要记录删除数据的所有信息
  - insert 最少、只需要记录插入数据的rowid 反向生成delete
  - update 需要记录表更新字段就可以
- 5. redo 日志 (记录数据操作、防止崩溃后引起数据丢失)
- 6. 一致性读
  - 原理
    - SCN (system change number) 只会增加不会减少的递增数字、存在oracle 最小单位块中、某块变更SCN就会递增
    - 回滚事务段记录事务槽、如果事务未提交|回滚、该块就存在活动事务、数据读取到这个块就可以识别这种情况
    - 首先看发起的SCN是否大于当前查询块的SCN，如果小于，毫无疑问从回滚段获取前镜像数据。如果 SCN 确实大于当前查询块的SCN，还要确保该块没有活动事务，否则还是要去前镜像查找
  - 只能读到查询开始时、某一时刻的数据
- 7. 查询各个空间大小语句
  - PGA
    - show parameter pga
  - SGA
    - show parameter sga
  - shared_pool 共享池 sga自适应、无需人为干预
    - show parameter shared_pool_size
  - dbCache 数据缓存区 sga自适应、无需人为干预
    - show parameter db_cache_size
  - 日志缓存区 log
    - show parameter log_buffer
- 8. 优化
  - 批量操作优化
    - 批量存储过程优化
      - 1. 减少sql 解析步骤、采用动态绑定 :x using ... 方式,减少sql解析
      - 2. 去除不必要的动态sql写法 execute immediate
      - 3. 批量commit, 批量操作,将commit 放在批量之后、避免每次提交commit 触发 LGWR 触发日志缓存写入redo
    - 批量操作一条一条改为一个批次
      - insert into t select rownum from dual connect by level <= 10000
    - 通过create table 方式跳过数据缓存区 dataBuffer、直接将数据写入磁盘: 直接路径读写方式
      - create table t as select rownum from dual connect by level <= 10000
    - 适用多cpu 并行跑、设置日志关闭nologging
      - create table t nologging paraller 16 as select rownum from dual connect by level <= 10000
- 逻辑结构
  - tablesapce 表空间
    - 系统表空间
    - 回滚段表空间
    - 临时表空间
    - 用户表空间
  - segment 段只要数据表创建成功,oracle 就默认为它提供了若干初始数据拓展 init extent
    - 自定义存储拓展
      - create table 语句中使用storage 指定存储参数
  - extent 区最小的拓展单位,8 block
  - block 块 size 8kb,oracle 最小逻辑单位
  - OLAP 返回大量数据的情况,一般建议block尽量大
  - OLTP 返回少量数据的情况,建议block 设置尽量小,如果设置太大、容易发生热点块竞争
- 表设计
  - delete,truncate 区别
    - delete
      - 删除并不释放内存空间、空块依旧保留、查询还是会访问、插入时会优先插入、如果是删除和插入都很多的情况,基本不会存在大量空块
    - truncate
      - 表空间和块都会释放,不会记录日志
  - 表类型
    - 普通表
    - 分区表 (减少访问路径)
      - 类型
        
        范围分区
        
        create table t (...) partition by range (column) ( partition p1 value less than 100, partition p1_max value less than maxValue, )
        
        列表分区
        
        create table t (...) partition by list (column) ( partition p1 values (100), partition p1_other values(default), )
        
        hash 分区
        
        create table t (...) partition by hash (column) partitions 12;
        
        组合分区
      - 优点
        
        高效的分区消除
        
        强大的分区操作
        
        分区truncate好快捷
        
        分区数据迁移方便
        
        分区再切割
        
        alter table t split partition p_max at (to_date('2023-02-01','yyyy-MM-dd')) into (partition p_20230201,partition p_max);
        
        分区合并
        
        alter table t merge partitions p1,P_MAX into partition p_max;
        
        分区新增
        
        alter table t add partition p2 values less than (100)
        
        分区删除
        
        alter table t drop partition p2;
      - 与普通表区别
        
        普通表只有一个段、分区表有多个段
      - 分区索引
        
        索引类型
        
        全局索引
        
        局部索引
        
        create index idx_name on t(col1) local
        
        索引失效原因
        
        truncate 操作导致索引失效,添加update global indexes 避免索引失效
        
        局部索引效率低
        
        没有使用到分区条件、导致局部索引没有全局索引效率高
    - 临时表
      - 类型
        
        基于会话的全局临时表
        
        create global temporary table t (...) on commit preserve rows
        
        基于事务的全局临时表
        
        create global temporary table t (...) on commit delete rows
      - 优点
        
        高效删除
        
        不同会话独立
    - 索引组织表
      - 建表语句添加 organization index 关键字
      - 避免回表操作、适用于少更新,多查询场景
    - 蔟表 (有序表)
- 索引
  - 结构
    - 根 root
    - 茎 branch
    - 叶 leaf
      - 存储 key column value(索引列具体值) , 以及具体定位到数据位置的rowId
  - 特点
    - 高度较低
    - 索引存储列值
    - 索引本身有序
  - 优化
    - 分区表用不到分区条件、局部索引效率会比全局索引慢
    - count(*) 优化、需要注意索引列是否允许为空,空值存在无法走索引
    - sum/avg 优化,避免索引字段为空
    - max,min (index full scan)不需要在意空值情况. 需要注意一起使用会导致索引失效
      - 解决方式, select max,min from (select max(obj) from a ,select min(obj) from a)
    - 索引查询避免回表操作(在不需要回表字段的情况下)
    - 索引不包含查询列可以考虑组合索引
      - 最左匹配原则
    - 聚合因子影响回表速度
      - 排查表聚合因子情况: select index_name,blevel,leaf_blocks,num_rows,distinct_keys,clustering_factor from user_ind_statistics where table_name in ('t')
    - 活用索引有序特性
      - order by 排序索引优化,为排序字段添加索引
      - distinct 排重优化
        
        distinct 采用hash unique 算法,如果将查询改为排重字段的等值查询会消除排序
      - 索引全扫 index full scan 慢于索引快速全扫 index fast full scan
    - union 与 union all
      - union 会对结果去重,排序
      - union all 替换 union 避免排序
    - 主外键设计
      - 特点
        
        主键本身就是一种索引
        
        主键所在列是唯一的
        
        有效现在外键依赖表的完整性
      - 级联删除
        
        外键基础上增加 on delete cascade 关键字
    - 组合索引
      - 适当场合避免回表
      - 组合列返回越少越高效 (组合列查询返回结果)
      - 组合查询列 (等值查询列在前更高效),全部为等职查询的情况效率一直
      - 需考虑单列查询情况,若是某个字段经常被使用,需要将经常使用的字段放在最前(合理)
    - 索引越多插入,更新越慢
    - 控制索引数量
      - 监控索引使用情况
        
        select * from v$object_usage;
    - 位图索引,允许空值
      - create bitmap index index_name on t(col)
      - count * , 索引 + 位图 > 索引 > 无索引
      - 位图索引列、在被其他进程操作、且操作值相同的情况下,会被阻塞影响并发
        
        例如
        
        create bitmap index age_idx on t (age);
        
        start transaction ; insert into t (age) values(1); 不提交
        
        insert into t (age) values(1); 被上面进程阻塞,无法成功
      - 满足场景
        
        位图索引列大量重复
        
        表极少更新
    - 列操作导致索引失效
      - 函数索引失效
        
        create index idx1 on t(name);
        
        select * from t where upper(name) = 'bhz' 这种情况不会走索引
        
        改造: create index idx1 on t(upper(name));
      - 列运算导致索引失效
        
        create index idx2 on t (age);
        
        select * from t where age + 1 = 20; 索引失效
        
        避免列运算
  - 一个列可以有多个不同类型索引需要加 invisible 关键字
    - create index idx1 on t(id); create index idx2 on t(id); 报错 create bitmap index idx2 on t(id) invisible; 不报错
  - 分区索引添加 indexing [on | off]
    - 查询条件确认分区、无索引走圈表扫描
    - 查询条件确认分区、范围内有索引、直接使用索引
    - 未确认分区、有索引分区使用索引、无索引分区走全表扫描
- 表连接
  - 类型
    - 嵌套连接 use_nl
      - /*+leading (t1) use_nl (t2) */
      - 驱动表查询条数就是被驱动表的访问次数
    - 散列连接 use_hash
      - /*+leading (t1) use_hash(t2) */
      - 在散列连接中,驱动表和被驱动表都会被访问0次或者1次
      - 不支持驱动与被驱动表的 <> ,like,> , <条件查询,默认会变为嵌套连接
    - 排序合并连接 use_merge
      - /*+ordered use_merge(t2)*/
      - 没有驱动与被驱动的概念
      - 排序合并连接、不支持 <> ,like
  - 哪种连接不需要排序
    - 嵌套链接
  - 适合嵌套连接场景
    - 两表关联返回记录不多
    - 连接条件为不等值查询
  - 优化
    - 驱动表连接条件有索引
    - 被驱动表查询条件有索引
- alter system scope
  - memory
    - 只改变当前实例,重启后失效
  - both
    - 立即生效,重启后还有效
  - spfile
    - 不改变当前实例,重启后生效
      - log buffer。只有重启后才会生效,设置为其他属性会报错
- 清空共享池 alter system flush shared_pool
- 查询sql语句执行记录
  - select * from v$sql;