数据库理论_数据库的视图理论-优快云博客

本文链接：https://blog.youkuaiyun.com/zheng_ze_qi/article/details/104872512

本文深入讲解数据库核心概念，包括视图、事务、索引、存储引擎、锁机制、存储过程和数据库范式等内容，旨在帮助读者理解数据库设计与优化的关键要素。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

一. 视图

1. 视图的基本概念：

视图是从一个或几个基本表（或视图）中导出的虚拟的表。
视图看上去非常像数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。

2. 视图的作用：

1、视图能够简化用户的操作
2、视图使用户能以多钟角度看待同一数据
3、视图对重构数据库提供了一定程度的逻辑独立性
4、视图能够对机密数据提供安全保护
5、适当的利用视图可以更清晰的表达查询
简单性。看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其它数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上。
```
--- 创建计算机系学生的视图
create view_tatal_students
as select id, name, sex
from students where sdept = 'CS'
```

3. 视图和表的区别是什么？

表直接将数据存储在磁盘上，视图是将SQL语句存储到磁盘上；
视图是建立在表的基础上，表存储数据库中的数据，而视图显示已经在表中的数据的外观；
视图本身没有数据，只保存了SQL语句；
次使用视图时会去执行SQL语句在它的基表中查询数据，而表却是实实在在的保存着数据
删除视图，表不受影响，而删除表，视图不再起作用。

二. 数据库事务

1. 什么是数据库事务：

数据库事务是构成单一逻辑工作单元的操作集合，要么全部执行成功，要么全部不执行。

2. 数据库事务的特性：（ACID）

① 原子性：是指事务中的所有操作作为一个整体像原子一样不可分割，要么全部成功，要么全部失败。
② 一致性：事务的执行成果必须使数据库从一个一致性状态到另一个一致性状态。一致性状态是指：Ⅰ. 系统的状态必须满足数据的完整性约束（主码，参照完整性，check 约束等）；Ⅱ. 系统的状态反应数据库本应描述的现实世界的真实状态，比如转账前后两个账户的金额总和应该保持不变；
③ 隔离性：并发执行的事务不会相互影响，其对数据库的影响和它们串行执行时一样。比如多个账户同时往一个账户转账，左后账户的结果应该和它们按先后顺序转账的结果一样；
④ 持久性：事务一旦提交，其对数据库的更新就是持久的，任何事务或故障都不会导致数据丢失。

3. 原子性是通过什么机制实现的：

通过日志来实现的，比如 MySQL中的 InnoDB引擎使用undo log 。
在操作任何数据之前，首先将数据被分到一个地方，这个存储数据备份的地方称为undo log ，然后进行数据的修改，如果出现了错误或者用于执行了rollback语句，系统可以利用undo log中的备份数据将数据恢复到事务开始前的状态。
undo log是逻辑日志
①：当delete一条记录时， undo log中会记录一条对应的insert记录；
②：当insert一条记录时， undo log中会记录一条对应的delete记录；
③：当update一条记录时， undo log中会记录一条对应的update记录；

4. 持久性是通过什么机制实现的：

通过日志来实现的，比如 MySQL 中的 InnoDB 引擎使用 redo log 。
和 undo log 相反，redo log记录的是新数据的备份，在事务提交之前，只要将redo log持久化即可，不需要将数据持久化；
当系统奔溃时，虽然数据没有持久化，但是redo log已经持久化，系统可以根据redo log中的内容，将所有数据恢复到最新的状态；

5. 隔离性是通过什么机制实现的：

通过锁来实现的，在MySQL的InnoDB中，锁可以分为两类，共享锁和排他锁。
① 共享锁：共享锁将数据变为只读形式，不能进行更新，所以也称为读取锁定。
② 排他锁：

6. 一致性是通过什么机制实现的：

ACID中，一致性是事务的根本追求，而某些情况下，比如事务的并发执行或者事务故障或系统奔溃等，会对事务的一致性造成破坏。
数据库系统通过并发控制技术和日志恢复技术来避免这种情况的发生。
并发控制技术保证了事务的隔离性，使数据库的一致性不会因并发执行的操作而破坏；
日志恢复技术包括了事务的原子性，使一致性不会因为事务故障而被破坏，同时也使已提交的数据库的修改不会因系统奔溃而丢失，即保证了事务的持久性。
换句话说，事务的一致性是通过原子性，持久性，隔离性来实现的。

7. MySQL 的隔离级别：

事务具有隔离性，理论上来说事务之间的执行不应该相互产生影响，其对数据库的影响应该和它们串行执行时一样，然而完全的隔离性会导致系统的并发性很低，降低对资源的利用率，因而实际上对隔离性的要求会有所放宽。
SQL标准为事务定义了不同的隔离级别，从低到高分别是：
$\left\{ \begin{aligned} 读未提交 (Read\ Uncommitted) \\ 读已提交 (Read\ Committed) \\ 可重复读 (Repeatable\ Read) \\ 串行化 (Serializable) \end{aligned} \right.$
脏读/不可重复读/幻读的概念：
①：脏读：对于两个事务T1与T2，T1读取了已经被T2更新但是还没有提交的字段之后，若此时T2回滚，T1读取的内容就是临时并且无效的。或者简单来说，脏读就是指一个线程中的事务读取到了另外一个线程中未提交的数据。
②：不可重复读：不可重复读: 对于两个事务T1和T2，T1读取了一个字段，然后T2更新了该字段并提交之后，T1再次提取同一个字段，值便不相等了。
③：幻读：对于两个事务T1、T2，T1从表中读取数据，然后T2进行了INSERT操作并提交，当T1再次读取的时候，结果不一致的情况发生。
不同的隔离级别可能导致不同的并发异常，如下表：

事务的隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化	不可能	不可能	不可能

其中，MySQL默认的隔离级别是可重复读。
级别越高，数据越安全，但性能越低。
创建事务的MySQL语法:
①：隐式事务：事务没有明显的开启或者结束的标志，MySQL中，默认是开启自动提交的，可以通过以下语句查看：
```
select @@autocommit;
```
在autocommit = 1的情况下，针对SELECT、UPDATE、DELETE、INSERT等DQL及DML语句的执行，MySQL会自动提交该事务，如果关闭就需要手动提交或者回滚来完成操作。
②：显式事务：与隐式事务想反，有明显的开启或结束标志。可以通过以下语句来设置：
```
set autocommit = 0;
```
在autocommit = 0的情况下，在事务未结束之前，操作是有效的且更改了数据实体，那么如果有多个事务参与，肯定会出现各种各样的数据不一致的情况，这就类似多个线程在没有锁的情况下修改同一个全局变量。
Serializable级别就类似加锁的方式，同一时刻支持多个事务并发，但是针对DML(Update\Insert\Delete)操作时，当前发起操作的事务会被阻塞，直到其他事务Commit或者Rollback才会继续执行事务语句。可见效率十分低下。

三. 索引

1. 索引的概念：

索引是帮助MySQL高效获取数据的排好序的数据结构。
索引底层的数据结构常见的有以下几种：二叉树，红黑树，Hash 表，B-Tree；
常见的MySQL主要有两种结构：Hash索引和B+ Tree索引，我们使用的是InnoDB引擎，默认的是B+树。

2. B+Tree索引和Hash索引区别？

哈希索引适合等值查询，但是无法进行范围查询
+哈希索引没办法利用索引完成排序
哈希索引不支持多列联合索引的最左匹配规则
如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

3. MyISAM 存储引擎实现：

①：实现结构：B+ 树；
②：B+ 树的叶子节点是<key, value>形式，其中，key是索引，value是索引所在那一行的磁盘文件地址。
③：索引文件和数据文件分开存储，即MyISAM是非聚集索引，有xxx.frm文件，xxx.MYD文件，xxx.MYI文件。
InnoDB的数据文件本身就是索引文件。MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

4. InnoDB 存储引擎实现：

①：表数据文件本身就是按B+ 树组织的一个索引结构文件，xxx.IBD文件；
②：聚集索引叶节点，包含了完整的数据记录；
③：这也就是为什么InnoDB必须要有主键，且推荐使用自增的整型主键。

5. 为什么 InnoDB 必须要有主键？并且推荐用整型的自增主键？

因为InnoDB本身的文件组织形式就是按照B+ 树组织的一个索引结构文件，所以必须需要一个主键才可以组织起来，如果用户使用InnoDB存储引擎建立表的时候，没有指定主键，则MySQL会自动的帮你找到一个合适的唯一索引作为主键，若找不到符合条件唯一索引条件的字段时，会生成类似于ROW_ID的虚拟列充当该InnoDB表的主键；
整型的存储比字段类型要小，而且应为是InnoDB存储引擎使用的是B+Tree数据结构，在进行查询数据是需要对每个元素进行比较，而整型的对比效率是高于其他数据结构的，字符串等。
InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

4. 索引的设计原则？

5. 什么情况下索引会失效？

①：如果条件中有or，即使其中有条件带索引也不会使用 (这也是为什么尽量少用or的原因)；要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引；

四. 数据库引擎

数据库三大引擎：InnoDB、MyISAM、MEMORY；

五. 锁

1. 概念：

锁是计算机协调多个进程或线程并发访问某一资源的机制。
MySQL的锁机制比较简单，不同的搜索引擎支持不同的锁机制。
比如MyISAM支持表级锁；
InnoDB既支持表级锁，又支持行级锁，但默认情况下使用行级锁。

2. 表级锁和行级锁的概念：

表级锁：开销小，加锁快，不会出现死锁，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢，会出现死锁，锁粒度最小，发生锁冲突的概率最低，并发成都也最高。
很难说哪种锁更好，只能就具体应用的特点来说哪种锁更合适。
表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如·web应用`；
行级锁更适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的业务。

3. MyISAM 表级锁

MyISAM的表级锁有两种模式：
$\left\{ \begin{aligned} 表共享读锁 (Table\ Read\ Lock) \\ 表独占写锁 (Table\ Write\ Lock) \end{aligned} \right.$
对于MyISAM表的读锁，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
对于MyISAM表的写锁，则会堵塞其他用户对同一表的读和写操作。
MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁；在执行更新操作之前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要使用命令来显示加锁。
MyISAM的读操作于写操作之间，以及写写操作之间是串行的。
MyISAM默认的锁调度是写优先，由于表锁粒度大，读写之间又是串行的，因此，如果更新操作比较多，MyISAM表可能会出现严重的表等待。

MyISAM的写操作示例：
当一个线程获得对一个表的写锁之后，只有持有锁的线程可以对表进行更新操作，其他线程的读写操作都会阻塞，知道锁释放为止。
```
--- 写锁定
lock table student write;  
--- 执行操作
--- 释放锁
unlock tables;
```

MyISAM的读阻塞写示例：
一个Session使用lock table table_name read 给表加读锁，这个Session可以锁定表记录中的记录，但更新和访问其他表都会提示错误，同时，另一个Session可以查询该表的记录，但更新就会出现锁等待。

4. InnoDB 行级锁

共享锁(S)：又称读锁，允许一个事务去读一行，并阻止其他事务获得相同数据集的排他锁；
若数据T对数据A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，但不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁前不能对A做任何修改。
排他锁(X)：又称写锁，允许获得排他锁的事务更新数据，并阻止其他事务获取对相同数据集的共享锁和排他写锁。若事务T对数据对象加上X锁，事务T可以读取A也可以修改A，但其他事务不能再对数据A加上任何锁，直到事务T释放A上的X锁。
MySQL的InnoDB引擎默认的修改数据语句Insert, Delete, Update都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。

5. InnoDB 行级锁的实现方式：
InnoDB加锁是通过给索引上的索引项加锁来实现的；
这一点于Oracle不同，Oracle是通过在数据块中对相应数据行加锁来实现的；
InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则默认使用表级锁。

六. 存储过程

1. 什么是存储过程？

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

2. 存储过程的优缺点？

优点：
①：存储过程是预编译过的，执行效率高。
②：存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
③：安全性高，执行存储过程需要有一定权限的用户。
④：存储过程可以重复使用，可减少数据库开发人员的工作量。
缺点：移植性差

七. 数据库范式

1. 第一范式

数据表中的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性，而不是集合。比如：有一个列是三年一班，则可拆分为三年和一班；

2. 第二范式

数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖，也即实体的每个非主键属性完全函数依赖于主键属性。
部分依赖：当主键由两个或两个以上字段构成，而表中的某些信息通过主键的一个字段就能唯一确定，我们称这样的依赖关系为部分依赖。
学生课程表（学号，姓名，专业，课程号，课程名，成绩），该表中一个学生可以选多门课，一门课有多个学生。学号和课程号可以唯一确定一条记录，因此用学号和课程号做主键。
表中的姓名、专业通过主键中的学号就能唯一确定，而课程名通过课程号唯一确定，这就是部分依赖，这样的设计不符合第二范式。

不符合第二范式会带来以下问题：
①：数据信息冗余，比如表中专业被存储了很多次；
②：增删改会出现问题，比如准耶名字更改了，要修改很多地方。

3. 第三范式

在满足第二范式的基础上，在实体中不存在非主键属性传递函数依赖于主键属性。（表中字段[非主键]不存在对主键的传递依赖）。
比如学生宿舍表（学号，姓名，性别，系号，系名，宿舍号，宿舍电话）。
学号–>姓名，性别，系号–>决定系名，宿舍号–>决定宿舍电话，也有学号–>系名，学号–>宿舍电话。
在这样一张表中则存在着传递依赖。也就是系名依赖系号，系号依赖学号，那么间接的系名依赖学号，宿舍号、宿舍电话和学号之间也有同样的关系。这样设计表的同样会带来数据冗余，操作异常等问题。
同样可以用关系分解的分解的方法来消除传递依赖，将这张表分成三张表。