Oracle数据库设计与SQL优化

定义数据库表格之间的关系

外键的使用



外来关键字字段的数据类型必须和主关键字 的相同 在没有特别原因的情况下，不要让外来关键 字为空 使用谨慎，可能导致处理上的麻烦
第四节 建立索引

索引的益处

避免全表扫描，加快数据访问的速度 提高查询的效率 索引文件要占用磁盘空间，如果有大量的索引，索 引文件会比数据文件更快地达到最大的文件尺寸 索引文件增加了插入和删除，以及更新索引列中的 值的时间，降低了大多数涉及写入的操作的时间
定义数据库表格之间的关系

建立关系


一对一的关系：通过主键建立 一对多的关系：通过组合键建立，且其中一 个键是另一张表的外键 多对多的关系

数据库不支持多对多的关系 需要通过建立中间表的形式建立 中间表有组合键，且都为另一张表的外键
学生表 学生 系名 性别 Char(10) Char(10) Char(4)

数据库设计的4个阶段：

分析用户要求

收集和分析用户的要求，包括数据要求、加工要求和种种 限制条件等 用一个“概念性数据模型”将用户的数据要求明确地表达 出来

建立概念性数据模型


逻辑设计

设计数据的结构
设计数据模式的一些物理细节

物理设计

基本概念

逻辑设计的步骤



确定数据元素 规范化数据表 建立数据表间关系和索引 建立数据库模式

索引的弊端


建立索引

确定和挑选索引列的准则： 搜索的索引列：所要选择的最适合索引的列是出现在Where字 句中的列，或连接字句中指定的列，而不是出现在SELECT关 键字后的选择列表中的列 对经常出现在WHERE子句中的列加上索引 使用唯一索引：要考虑某列中值的分布，对于唯一值的列， 索引的效果最好，而具有多个重复值的列，其索引效果最差。 使用短索引：短索引索引节省大量的索引空间，也可能会使 得查询更快 较小的索引涉及的磁盘I/O较少，较短的值比较起来更快 高速缓存中的块能能容纳更多的较小键值，增加了找到行 而不用读取索引中较多块的可能性，从而提高效率。 不要过度索引：每个额外的索引都要占用额外的磁盘空间， 并降低写操作的性能，在修改表的内容时，索引必须进行更 新，有时可能需要重构。因此一个索引很少利用或从不使用， 会不必要的减缓表的修改速度
学生 张三
系名 建筑
课名 哲学 制图 军事
教师 孔子 鲁班 孙膑 孙子 孔子 墨子 孟子
李四
电子
军事 哲学
王五 赵六
建筑 机械
哲学 哲学
军事
钱七 机械 哲学
岳飞
孟子
军事
孙八 机械 哲学
孙子
孟子
反规范化
SELECT a.学生, b.教师,c.课名, d.系主任 FROM 学生表 a,选修表 b,课程表 c,系主任表 d WHERE a.学生 = b.学生 AND b.课号 = c.课号 AND a.系名 = d.系名
存储
数据模式
数据结构
建立数据库模式

建立分级数据库模式（数据库用户）

数据存储模式：

存储应用的所有数据结构和数据 根据需要将数据访问的权限授权给存储过程模式

其中大部分数据只授予SELECT权限即可 如课程表

数据管理员负责数据存储模式的管理 应用开发员无权直接访问数据存储模式
建立数据库模式

存储过程模式

存储应用逻辑所需的存储过程 根据需要将存储过程的执行权限授予执行模式 应用开发员只能访问


应用自身的存储模式 数据模式授权访问的数据

普通用户无权直接访问数据存储模式 普通用户通过执行模式执行存储过程 通过执行一个执行模式可以执行多个应用的存储过程

执行模式

第六节 反规范化
建立索引

举例

选课应用经常需要操作
查找一个学生的选课结果 SELECT 课程 FROM 选课表 WHERE 学生=‘张三’ 查找某门可的选课学生 SELECT 学生 FROM 选课表 Hale Waihona Puke BaiduHERE 课程=‘制图’ 为选课表的学生列和课程列分别建立索引

第五节 建立数据库模式



数据库模式是一组表、试图、同义词、 存储过程的集合 在Oracle数据库中，数据库模式就是我们 通常所说的数据库用户 数据模式定义了数据库访问者所拥有的 数据库的访问权限
原学生表
学生
Char(10)
系名 系主任
性别 入学时间 学生表 学生 系名 性别 Char(10) Char(10) Char(4)
Char(10) Char(10)
Char(4) Datetime
系所表 系名 Char(10)
系主任
Char(10)
入学时间 Datetime
数据库范式


Boyce-Codd范式（BCNF）：如果关系模式R（U， F）的所有属性（包括主属性和非主属性）都 不传递依赖于R的任何候选关键字，那么称关 系R是属于BCNF的。或是关系模式R，如果每个 决定因素都包含关键字（而不是被关键字所包 含），则RCNF的关系模式。 BCNF范式的含义：



表格必须符合1NF和2NF的要求 一定不能存在依赖于非主键的字段。 这条规则实际上是3NF的一个子规则 捕捉可能会通过进程产生的依赖性
数据库范式

规范化的规则能够被简化成下面几点：



每个字段必须尽量小 每个字段只能包含一个数据项 每条记录都必须是唯一的 注意重复的条目 每个字段都必须完全支持主键，而且只支持 主键
选课表
学生 课号 课名 Char(10) Char(10) Char(20)
系主任
性别
Char(10)
Char(4)
入学时间
Datetime
教师
Char(10)
数据库范式


第二范式：如果关系模式R（U，F）中的所有 非主属性都完全依赖于任意一个候选关键字， 则称关系R 是属于第二范式的 第二范式的含义
选课表 学生 课号 教师 Char(10) Char(10) Char(10)
课号 课名
Char(10) Char(20)
数据库范式


第三范式（3NF）：如果关系模式R（U，F）中 的所有非主属性对任何候选关键字都不存在传 递信赖，则称关系R是属于第三范式的 第三范式的含义：

表格必须符合1NF和2NF的要求 所有的字段都必须相互独立，不存在传递关系 任何描述非主键字段的字段都必须被移动到另一个 表格里
第三章 SQL优化的基本概念

SQL 模型中有3类语句：


定义（DDL）:Creat 操作（DML）:Select 权限控制: Grant select（减少表的长度）的优化 project（减少表的字段数目）的优化 join（有公共字段的多表合并 的优化

SQL优化通常指对DML语句的优化
第一节 数据库范式


数据库范式是一组数据库结构设计的标准规则， 用以保证规范化的数据库设计。 应用数据库范式数据库，可以检验数据库设计 的可靠性，找出影响数据完整性和难于维护的 设计问题 数据的范式共有七条，在大多数情况下前四条 就够用了

第一范式 第二范式 第三范式 BCNF范式
外键
选课表 学生 课号 Char(10) Char(10) Char(20) Char(10)
入学时间 Datetime 课程表 课号 课名 Char(10)
外键
课名 教师
Char(20)
定义数据库表格之间的关系

主键的使用



主键必须唯一的识别每一记录 一个记录的主键不能为空 当生成记录时，主键的值必须存在 主键必须保持稳定——不能更改主键的域 主键必须简洁，不要包含过分的属性 主键的值不能改变

反规范化是指有意设计不符合第三范式的数据库，以提高 性能或简化最终用户报表 性能考虑 符合第三范式的数据库查询往往比第一范式、第二范 式需要更多的表连接 非常消耗CPU和磁盘I/O资源的 特殊的报表 特殊报表是由最终用户执行的非结构化报表和查询 防止最终用户因连接多个表造成的迷惑不解 又是可将试图看作一种反规范化表

安全上的目标


时间上的目标

数据库设计的基本概念

数据库设计和程序设计的关系

一个软件系统包括两方面的问题 数据 对数据进行处理

需求分析阶段

分析用户的数据要求 分析用户的功能要求 设计数据的结构 设计程序模块的结构；

设计阶段



编程阶段 要考虑数据和算法等
数据库设计的基本概念
第二节定义数据库表格之间的关系

关系的类型



一对一：在关系的每一边，这两个表格都只有一条 记录。每个关键字的值都只和关系表里的一条记录 （或者没有记录）相关。 一对多：主关键字表格只包含有一条记录，这条记 录和关系表里的无记录、一条记录或者多条记录相 关。 多对多：两个表格里的每条记录都可以和另一个表 格里任意数量的记录（或者无记录）相关。
普通用户
操作
操作
数据管理员
操作
存储
数据结构 存储过程
应用开发员
数据库模式 （数据库用户）
存储

单一数据库模式的缺陷



数据访问不安全 存储过程访问不安全 管理角色不明确
建立数据库模式
用户
存储过程的调用
执行模式
存储过程的授 权执行
无任何存储信息
存储过程模式1
表的授权 访问存储
存储过程模式2
存储
存储过程


表格必须按照第一范式来规范 所有的字段必须引用（或者描述）主键值 如果主键基于一个以上的字段，那么每个非主键字 段必须依赖于组合键，而不仅仅是一个非键的字段 不依赖主键的非主键字段应该被移动到另一个表格 里去
原选课表
学生 课号 课名 教师
Char(10) Char(10) Char(20) Char(10) 课程表
第四章 基础的SQL优化

SQL语句执行的过程
1. 2. 3. 4. 5. 6.
7.
8.
计算语句值 共享池中有无与此语句值相同的语句？ 共享池中有与此语句字符完全匹配的语句？ 准备要运行的SQL语句 为新语句在共享池中创建空间 将语句存放在共享池中 修改共享池图，标明语句的值和在共享池中的位 置 执行准备好的SQL语句
基础的SQL优化

共享SQL语句



在第一次解析之后, ORACLE将SQL语句存放数据库 全局区域（SGA区）的共享池中，并可以被所有的 数据库用户共享 执行一个SQL语句，如果它和之前的执行过的语句 完全相同, ORACLE将直接从共享池中获得被解析的 语句及最好的执行路径 理想的语句是只执行1、2、3和8步来进行处理。只 经过1、2、3、8的SQL语句要比经过1～8步的语句 更为有效大大提高了SQL的执行性能，节省了内存 的使用
第二章 规范的数据库设计

未合理设计的数据库所带来的问题



冗余数据 有限的记录信息 不一致的数据 不规则的更新 不规则的删除 不规则的插入
学生 系名 系主任 性别 入学时间 课号1 课名1 教师1 课号2 课名2 教师2 课号3 课名3 教师3
Char(10) Char(10) Char(10) Char(4) Datetime Char(10) Char(20) Char(10) Char(10) Char(20) Char(10) Char(10) Char(20) Char(10)



SQL优化的基本概念

SQL调整在Oracle数据库调整中的地位
服务器、网络、硬盘 SGA 后台处理 表、索引 环境调整 Oracle实例调整 Oracle对象调整
Oracle SQL 调整
基本概念
性能调整中不同的操作产生的性能收益
100 80 60 40 20 0 设计 SQL 环境 实例 设计 SQL 环境 实例
数据库设计与SQL优化
清华大学计算中心
课程安排

第一章 第二章 第三章 第四章 第五章
数据库设计的基本概念 规范的数据库设计 SQL优化的基本概念 基本的SQL优化 高级SQL优化
第一章 数据库设计的基本概念

数据库设计的目的

空间上的目标

消除冗余数据 占用较小的存储空间 消除数据间的不一致性。 保护数据的完整性 高效的数据访问
反规范化

反规范化技术




复制数据：减少处理一个查询所需连接的表的数量， 从而减少CPU以及磁盘I/O的开销。 摘要数据 ：减少乃至消除在查询中进行初步摘要 数据所需的操作步骤 水平分区 ：水平分区时将一个表在记录级拆成两 个分开的表，从而减少每个表中的行数 垂直分区 垂直分区时将一个表在列级拆成两个分 开的表，从而减少每个表的列数
数据库范式


第一范式：在关系模式R中的每一个具体关系r 中，如果每个属性值 都是不可再分的最小数 据单位，则称R是第一范式的关系 第一范式有三个含义


无多值项目和重复组 每个字段都是原子型的，也就是说每个字段必须包 含可能的最小数据元素 以及表格含有关键字
数据库范式 学生表
学生 系名 Char(10) Char(10)