数据库结构简述

本文主要对基本的数据库结构（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语句没有完成。针对这种困境，可以用自治事务来解决。自治事务从当前事务开始，在其自身的语境中执行。它们能独立地被提交或重新运行，而不影响正在运行的事务。正因为这样，它们成了编写错误日志表格的理想形式。在事务中检测到错误时，可以在错误日志表格中插入一行并提交它，然后在不丢失这次插入的情况下回滚主事务。

锁

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

基本的锁类型

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