数据库复习

最新推荐文章于 2025-12-24 21:55:42 发布

原创最新推荐文章于 2025-12-24 21:55:42 发布 · 371 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

数据库专栏收录该内容

1 篇文章

订阅专栏

本文深入探讨数据库设计三大范式，详解视图、索引、事务管理等核心概念，提供SQL优化策略及数据库结构优化建议，助您提升数据库性能。

一、三大范式

定义：

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段x → 非关键字段y

说明：

前提第三范式满足第二范式满足第一范式

第一范式：字段是最小的的单元不可再分

学生信息组成学生信息表，有年龄、性别、学号等信息组成。这些字段都不可再分，所以它是满足第一范式的

第二范式：满足第一范式,表中的字段必须完全依赖于全部主键而非部分主键。

其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的
学号为1024的同学，姓名为Java3y，年龄是22岁。姓名和年龄字段都依赖着学号主键。

第三范式：满足第二范式，非主键外的所有字段必须互不依赖

就是数据只在一个地方存储，不重复出现在多张表中，可以认为就是消除传递依赖
比如，我们大学分了很多系（中文系、英语系、计算机系……），这个系别管理表信息有以下字段组成：系编号，系主任，系简介，系架构。那我们能不能在学生信息表添加系编号，系主任，系简介，系架构字段呢？不行的，因为这样就冗余了，非主键外的字段形成了依赖关系(依赖到学生信息表了)！正确的做法是：学生表就只能增加一个系编号字段。

参考链接：

https://www.zhihu.com/question/24696366
http://www.cnblogs.com/CareySon/archive/2010/02/16/1668803.html

二、视图

视图是一种虚表
视图建立在已有表的基础上, 视图赖以建立的这些表称为基表
向视图提供数据内容的语句为 SELECT 语句,可以将视图理解为存储起来的 SELECT 语句
视图向用户提供基表数据的另一种表现形式
视图没有存储真正的数据，真正的数据还是存储在基表中
程序员虽然操作的是视图，但最终视图还会转成操作基表
一个基表可以有0个或多个视图

作用：

操作员想看到什么样的数据，我们就给他们什么样的数据。一方面就能够让他们只关注自己的数据，另一方面，我们也保证数据表一些保密的数据不会泄露出来。

使用视图可以让我们专注与逻辑，但不提高查询效率

三、索引

是一种快速查询表中内容的机制，类似于新华字典的目录
运用在表中某个些字段上，但存储时，独立于表之外
索引表把数据变成是有序的

rowid特点

位于每个表中，但表面上看不见，例如：desc emp是看不见的
只有在select中，显示写出rowid，方可看见
它与每个表绑定在一起，表亡，该表的rowid亡，二张表rownum可以相同，但rowid必须是唯一的
rowid是18位大小写加数字混杂体，唯一代表该条记录在DBF文件中的位置
rowid可以参与=/like比较时，用’'单引号将rowid的值包起来，且区分大小写
rowid是联系表与DBF文件的桥梁

索引的特点

（1）索引一旦建立, Oracle管理系统会对其进行自动维护, 而且由Oracle管理系统决定何时使用索引
（2）用户不用在查询语句中指定使用哪个索引
（3）在定义primary key或unique约束后系统自动在相应的列上创建索引

（4）用户也能按自己的需求，对指定单个字段或多个字段，添加索引

需要注意的是：Oracle是自动帮我们管理索引的，并且如果我们指定了primary key或者unique约束，系统会自动在对应的列上创建索引…

什么时候【要】创建索引

（1）表经常进行 SELECT 操作
（2）表很大(记录超多)，记录内容分布范围很广
（3）列名经常在 WHERE 子句或连接条件中出现

什么时候【不要】创建索引

（1）表经常进行 INSERT/UPDATE/DELETE 操作
（2）表很小(记录超少)
（3）列名不经常作为连接条件或出现在 WHERE 子句中

索引优缺点：

索引加快数据库的检索速度
索引降低了插入、删除、修改等维护任务的速度(虽然索引可以提高查询速度，但是它们也会导致数据库系统更新数据的性能下降，因为大部分数据更新需要同时更新索引)
唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
索引需要占物理和数据空间

索引分类：

唯一索引：唯一索引不允许两行具有相同的索引值

主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空

聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个

非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

深入参考：

https://kb.cnblogs.com/page/45712/
https://www.cnblogs.com/drizzlewithwind/p/5707058.html

四、事物

事务简单来说：一个Session中所进行所有的操作，要么同时成功，要么同时失败

ACID — 数据库事务正确执行的四个基本要素：
包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易。

注意：当Connection遇到一个未处理的SQLException时，系统会非正常退出，事务也会自动回滚，但如果程序捕获到了异常，是需要在catch中显式回滚事务的。

事务隔离级别

数据库定义了4个隔离级别：

Serializable【可避免脏读，不可重复读，虚读】
Repeatable read【可避免脏读，不可重复读】
Read committed【可避免脏读】
Read uncommitted【级别最低，什么都避免不了】

分别对应Connection类中的4个常量

TRANSACTION_READ_UNCOMMITTED
TRANSACTION_READ_COMMITTED
TRANSACTION_REPEATABLE_READ
TRANSACTION_SERIALIZABLE

脏读：一个事务读取到另外一个事务未提交的数据

不可重复读：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改

虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

五、SQL 约束

NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique
约束。
PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK: 用于控制字段的值范围。

六、存储过程

存储过程就像我们编程语言中的函数一样，封装了我们的代码(PLSQL、T-SQL)。

存储过程的优点：

能够将代码封装起来
保存在数据库之中
让编程语言进行调用
存储过程是一个预编译的代码块，执行效率比较高一个存储过程替代大量T_SQL语句
可以降低网络通信量，提高通信速率

存储过程的缺点：

每个数据库的存储过程语法几乎都不一样，十分难以维护（不通用）
业务逻辑放在数据库上，难以迭代

七、数据库优化

SQL优化

在我们书写SQL语句的时候，其实书写的顺序、策略会影响到SQL的性能，虽然实现的功能是一样的，但是它们的性能会有些许差别。

1. 选择最有效率的表名顺序

数据库的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表将被最先处理

在FROM子句中包含多个表的情况下：
选择记录条数最少的表放在最后
如果有3个以上的表连接查询：
如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推。

2. WHERE子句中的连接顺序

数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。

3. SELECT子句中避免使用*号

我们当时学习的时候，“*”号是可以获取表中全部的字段数据的。但是它要通过查询数据字典完成的，这意味着将耗费更多的时间
使用*号写出来的SQL语句也不够直观。

4. 用TRUNCATE替代DELETE

这里仅仅是：删除表的全部记录，除了表结构才这样做。
DELETE是一条一条记录的删除，而Truncate是将整个表删除，保留表结构，这样比DELETE快

5. 多使用内部函数提高SQL效率

例如使用mysql的concat()函数会比使用||来进行拼接快，因为concat()函数已经被mysql优化过了。
6. 使用表或列的别名
如果表或列的名称太长了，使用一些简短的别名也能稍微提高一些SQL的性能。毕竟要扫描的字符长度就变少了。
7. 多使用commit
comiit会释放回滚点…
8. 善用索引
索引就是为了提高我们的查询数据的，当表的记录量非常大的时候，我们就可以使用索引了。
9. SQL写大写
我们在编写SQL 的时候，官方推荐的是使用大写来写关键字，因为Oracle服务器总是先将小写字母转成大写后，才执行
10. 避免在索引列上使用NOT
因为Oracle服务器遇到NOT后，他就会停止目前的工作，转而执行全表扫描
11. 避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描，这样会变得变慢
12.用 >= 替代 >

低效：
SELECT * FROM EMP WHERE DEPTNO > 3   
首先定位到DEPTNO=3的记录并且扫描到第一个DEPT大于3的记录
高效：
SELECT * FROM EMP WHERE DEPTNO >= 4  
直接跳到第一个DEPT等于4的记录

13.用IN替代OR

  select * from emp where sal = 1500 or sal = 3000 or sal = 800;
  select * from emp where sal in (1500,3000,800);

14. 总是使用索引的第一个列

如果索引是建立在多个列上，只有在它的第一个列被WHERE子句引用时，优化器才会选择使用该索引。当只引用索引的第二个列时，不引用索引的第一个列时，优化器使用了全表扫描而忽略了索引

数据库结构优化

范式优化：比如消除冗余（节省空间。。）
反范式优化：比如适当加冗余等（减少join）
拆分表：垂直拆分和水平拆分

Oracle和Mysql的区别

在Mysql中，一个用户下可以创建多个库：
而在Oracle中，Oracle服务器是由两部分组成
数据库实例【理解为对象，看不见的】
数据库【理解为类，看得见的】
一个数据库实例可拥有多个用户，一个用户默认拥有一个表空间。
表空间是存储我们数据库表的地方，表空间内可以有多个文件。
当我们使用Oracle作为我们数据库时，我们需要指定用户、表空间来存储我们所需要的数据！

参考资料：

http://blog.youkuaiyun.com/xlgen157387/article/details/46899031
http://blog.youkuaiyun.com/beauty_1991/article/details/51209107
https://zhuanlan.zhihu.com/p/23713529
http://blog.youkuaiyun.com/wickedvalley/article/details/51527551
http://blog.youkuaiyun.com/zhugewendu/article/details/73550414