数据库结构简述

本文主要对基本的数据库结构(Oracle)做一个简介,希望对各位全面的了解数据库有所帮助。

数据库对象

表是关系型数据存储对象的基本结构

视图

基于一个表或多个表或视图的逻辑表,本身不包含数据,通过它可以对表里面的数据进行查询和修改

视图的优点

  • 减少对数据库的访问,因为视图可以对字段有选择性的选取
  • 用户通过简单的查询可以从复杂查询中得到结果
  • 维护数据的独立性,试图可从多个表检索数据
  • 对于相同的数据可产生不同的视图

索引

索引是可以提高查询性能的数据结构

特点

  • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
  • 可以大大加快数据的检索速度,这也是创建索引的最主要原因
  • 可以加速表和表之间的链接
  • 可使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间

不足

  • 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
  • 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大
  • 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,会降低数据的维护速度

类型

B树索引

  • 适合大量的增删改(OLTP应用),保证较少的磁盘IO
  • 每个结点都是数据块
  • 叶子块数据是排序的,从左到右递增
  • 不能包含OR操作符的查询
  • 适合高基数的列(唯一值多)
  • 典型的树状结构
  • 大多都是物理上一层、两层或三层不定,逻辑上三层

哈希索引(HASH)

  • 适合非范围数据查找
  • 可能是数据库中读取数据的最快方法
  • ALTER CLUSTER命令不能改变HASH键的数目

位图索引(bitmap)

  • 适合决策支持系统和数据仓库
  • 做 UPDATE 代价非常高
  • 很适合 OR 操作符的查询
  • 基数比较少的时候才能建位图索引
使用限制
  • 基于规则的优化器不会考虑位图索引
  • 当执行ALTER TABLE语句并修改包含有位图索引的列时,会使位图索引失效
  • 位图索引不包含任何列数据,并且不能用于任何类型的完整性检查
  • 位图索引不能被声明为唯一索引
  • 位图索引的最大长度为30
  • 不要在繁重的OLTP环境中使用位图索引

索引(组织)表

  • 索引组织表会把表的存储结构改成B树结构,以表的主键进行排序。
  • ROWID不会被关联到表的行上
  • 对基于主键值的精确匹配或范围匹配以及UPDATE和DELETE语句,由于行在物理上有序,索引组织表有 很好的表现
  • 键列的值在表和索引中都没有重复,存储所需要的空间少
  • 若不会频繁的根据主键列查询数据,则需要在索引组织表中的其他列上创建二级索引

反转键索引

  • 为了解决多个用户对集中在少数快上的索引进行修改,容易引起数据块等待的问题
  • 使有序数列随机化,生成的B-Tree更平衡,读取层级少,从而降低了热数据块
  • 适合磁盘容量有限,同时还要执行大量的有序载入
  • 不能对位图索引

基于函数的索引

  • 使执行了函数的查询能使用上索引
  • 需要付出额外的索引的存储空间
  • 必须初始化参数 QUERY_REWRITE_ENABLED

分区索引

  • 将一个索引分成多个片段,可以访问更小的片段,也可以把这些片段分别存放在不同的磁盘驱动器上,能够提供更多可以提高性能和可维护性的可能
  • B-Tree和位图索引都可以被分区,而HASH索引不可以被分区
分区方法
  • 表被分区而索引未被分区
  • 表未被分区而索引被分区
  • 表和索引都被分区
类型
  • 本地分区索引
    • 有前缀索引
    • 无前缀索引
  • 全局分区索引
    • 有前缀索引
    • 无前缀索引

存储过程和函数

过程和函数都以编译后的形式存放在数据库中,函数有返回值,过程没有返回值,但两者最根本的区别是他们的调用方式。过程作为一个独立的执行语句调用,函数以合法的表达式的方式调用。

包是将过程、函数和数据结构捆绑在一起的容器。

构成

  • 包头
  • 包体

触发器

触发器是一种自动执行相应数据库变化的程序。可以设置为在触发器事件之前或之后执行。
能够触发触发器事件包括下面几种:

  • DML事件:数据库操纵语言
  • DDL事件:数据库定义语言
  • 数据库事件

数据字典

数据字典包含数据库的元数据。

完整性约束(规则)

用于增强数据的完整性

  • CHECK:用于限制列中的值的范围
  • NOT NULL:强制列不接受NULL值
  • UNIQUE:唯一标识数据库表中的每条记录,每张表可以有多个
  • PRIMARY KEY:主键约束,唯一标识,主键列不能包含NULL值,每张表只能有一个主键
  • FOREIGN KEY:外键约束,指向另一张表中的主键列,预防破坏表之间连接的动作,防止非法数据插入外键列,外键必须是它指向的那个表中的值之一

用户与权限

用户

  • 对数据库的访问,需要以适当用户身份通过验证,并具有相关权限来完成一系列动作
  • SCHEMA:是某个用户所拥有所有对象的集合

权限

系统权限

允许用户执行特定的数据库动作,如创建表、创建索引、连接实例等
常用的系统权限:

  • CREATE SESSION 创建会话
  • CREATE SEQUENCE 创建序列
  • CREATE SYNONYM 创建同名对象
  • CREATE [ANY] TABLE 创建表
  • DROP TABLE 删除表
  • CREATE PROCEDURE 创建存储过程
  • CREATE USER 创建用户
  • DROP USER 删除用户
  • CREATE [ANY] VIEW 创建视图

对象权限

允许用户操纵一些特定的对象,如读取视图、更新某些列、执行存储过程等
常见的对象权限:

  • ALTER 修改
  • DELETE 删除
  • EXECUTE 执行
  • INDEX 索引
  • INSERT 插入
  • SELECT 选择
  • UPDATE 更新

总结

  • 使用CREATE USER 语句创建用户,ALTER USER语句修改用户,DROP USER username会删除用户及其所拥有的所有对象的数据
  • 系统权限允许用户在数据库中执行特定的操作,如执行DDL语句。

WITH ADMIN OPTION 使得该用户具有将自身获得的权限授予其他用户的功能
但回收对象权限时,不会从其他账户级联取消曾被授予的相同权限

  • 对象权限允许用户对数据库对象执行特定的操作,如执行DML语句。

WITH GRANT OPTION 使得该用户具有将自身获得的对象权限授予其他用户的功能
但回收对象权限时,会从其他账户级联取消曾被授予的相同权限

  • 系统权限与对象权限授予时的语法差异为对象权限使用了ON object_name 子句
  • PUBLIC 为所有用户授权
  • ALL:对象权限中的所有对象权限

角色

角色就是相关权限的命令的集合。使用角色的主要目的就是为了简化权限的管理

预定义角色

CONNECT 角色

具有一般应用开发人员需要的大部分权限
包括:

  • Alter session
  • Create cluster
  • Create database link
  • Create session
  • Create table
  • Create view
  • Create sequence
  • ……

RESOURCE 角色

具有应用开发人员所需要的其他权限,如
包括:

  • Create procedure
  • Create trigger
  • ……

DBA 角色

DBA角色具有所有的系统权限,及with admin option选项,默认的dba用户为sys和system,他们可以将任何系统权限授予其他用户。但是DBA角色不具备sysdba和sysoper的特权(启动和关闭数据库)

自定义角色

  • 建立角色
    • 建立不验证的角色
    • Create role 角色名 not identified
  • 建立数据库验证的角色
    • Create role 角色名 identified by 密码
  • 角色授权
    • Grant insert,update,delete on object_name to角色名
  • 分配角色给某个用户
    • Grant 角色名 to 用户名 with admin option

删除角色

Drop role 角色名

事务和锁

##事务

事务是数据库管理系统执行过程中的一个逻辑单位,由一个有限的对数据库的读/写操作序列构成

存在的目的

  • 为数据库操作提供了一个从失败中回复到正常状态的方法,同时提供了数据库即使在异常状态下仍然能保持一致性的方法
  • 当多个应用程序在并发访问数据库时,可以在这些应用程序之间提供一个隔离方法,以防止彼此的操作互相干扰

ACID特性

  • 原子性(Atomicity)
    事务作为一个整体被执行,包含在其中的对数据库的操作要么全部被执行,要么都不执行
  • 一致性(Consistency)
    状态前后一致,满足完整性约束
  • 隔离性(Isolation)
    多个事务并发执行时,一个事务的执行不应影响其他事务的执行
  • 持久性(Durability)
    已被提交的事务对数据库的修改应该永久保存在数据库中

事务的隔离级别

  • Read uncommitted 未提交读,容易造成脏读。事务B读取了事务A尚未提交的数据。
  • Read committed 已提交读,容易造成不可重复读。事务A事先读取了数据正准备做一些操作,事务B紧接着更新了数据,并提交了事务,而事务A再次使用该数据时,数据已经发生了改变。不可重复读的重点是修改,同样条件读取过的数据,再次读取出来发现值不一样了。
  • Read Repeatable 可重复读,可能造成幻读。事务A读取数据并打算做一些操作,此时事务B更新了数据并提交,事务A再读取时数据发生了变化。幻读的重点在于新增或删除,同样的条件,第一次和第二次读出来的记录数不一样。从总的结果看,幻读和不可重复读都表现为两次读取的结果不一致。但如果从控制的角度来看,对于后者只需要锁住满足条件的记录,对于前者,要锁住满足条件及其相近的记录。
  • Serializable 序列化。最高的事务隔离级别,同时代价也花费最高,性能很低,一般很少使用。在该级别下,事务顺序执行,不仅可以避免脏读、不可重复读,还避免了幻读。

自制事务

事务的“要么全部完成,要么什么都没完成”的本性会使将错误信息记入数据库表中变得很困难,因为当事务失败重新运行时,用来编写日志条目的INSERT语句没有完成。针对这种困境,可以用自治事务来解决。自治事务从当前事务开始,在其自身的语境中执行。它们能独立地被提交或重新运行,而不影响正在运行的事务。正因为这样,它们成了编写错误日志表格的理想形式。在事务中检测到错误时,可以在错误日志表格中插入一行并提交它,然后在不丢失这次插入的情况下回滚主事务。

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。

##基本的锁类型

  • 共享锁
    • 可以被其他事务读取,但不能修改
  • 排它锁
    • 其他事务不能对它读取和修改
  • 粒度
    • 行级锁
    • 表级锁