数据库复习

一、基本概念

数据库：长期存储在计算机内、有组织、可共享的大量数据的集合，数据库中的数据按照一定的数据模型组织、储存，具有较低的冗余度、较高的数据独立性和易扩展性。

  • 数据库分类：关系数据库、非关系型数据库（NoSQL、MongoDB）、图数据库（Neo4j）、内存数据库（Redis）

  • 数据库管理系统：位于用户与操作系统之间的数据管理软件

  • 数据库系统：由数据库、数据库管理系统、应用程序和数据库管理员组成的存储、管理、处理和维护数据的系统

数据库有什么类型的数据库、关系型数据库的特点

特点：

  1. 结构化数据存储：使用关系来组织和存储数据，数据以行和列的形式组织，表格之间通过键值进行关联。
  2. SQL语言：使用SQL来进行数据的查询、插入、更新和删除等操作。
  3. 事务支持：支持ACID特性，保证数据库操作的可靠性和一致性。
  4. 数据完整性：关系型数据库支持各种约束，可以确保数据的完整性和一致性。
  5. 复杂查询支持：关系型数据库支持复杂的查询操作，包括联合查询、嵌套查询、聚合函数、子查询等

二、数据库体系结构

2.1 三级模式

  • 外模式：
  • 它是数据库用户(包括应用程序员和最终用户)能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图；
  • 一个数据库可以有多个外模式。
  • 模式(逻辑模式)：
  • 它是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图；
  • 一个数据库只有一个模式。
  • 内模式(存储模式)：
  • 它是数据物理结构和存储方式的描述，是数据在数据库内部的组织方式；
  • 一个数据库只有一个内模式。

2.2 两级映射

  • 外模式/模式映像：它定义了外模式与模式之间的对应关系；
  • 当模式改变时(例如增加新的关系、新的属性、改变属性的数据类型等)，由数据库管理员对各个外模式/模式的映像作相应改变，可以使外模式保持不变，从而应用程序不必修改，保证了数据的逻辑独立性。
  • 模式/内模式映像：它定义了数据全局逻辑结构与存储结构之间的对应关系。
  • 当数据库的存储结构改变时，由数据库管理员对模式/内模式映像作相应改变，可以使模式保持不变，从而应用程序也不必改变。保证了数据的物理独立性。

三、数据库规范化理论

3.1 码的相关概念

  • 码、候选码：关系中唯一确定一个元组的某个属性(组)；
  • 主码：若一个关系中有多个候选码，则选定其中一个为主码
  • 包含在任何一个候选码中的属性称为主属性。
  • 不包含在任何候选码中的属性称为非主属性

3.2 关系的完整性

  • 实体完整性：主键主属性不能取空值
  • 参照完整性：只能取空值或主键上的值
  • 用户定义完整性

3.3 关系范式

  • BCNF：每个属性都不部分依赖也不传递依赖于R的候选键

  • 当一个表中的非主属性互相独立时（3NF），这些非主属性不应该有多值。

1.第一范式(1NF)

关系模式R的每个关系r的属性值都是不可分的原子值，出生日期，不能分成年月日

2.第二范式(2NF)

每个非主属性都完全依赖于主属性，解决完全函数依赖和部分函数依赖问题

举个例子：关系模式如图所示，sno为学号，cno为课程号

| sno | cno | grade |
| —– | —- | —– |
| 95001 | 1 | 58 |
| 95004 | 1 | 58 |
| 95002 | 2 | 90 |
| 95003 | 2 | 85 |

该关系模式中存在函数依赖：sno→grade,但该关系模式的主键是sno、cno，因此有(sno,cno)→grade，即存在非主属性对主键的部分依赖关系，所以此关系模式不满足第二范式要求。

3.第三范式(3NF)

目的是消除非主属性对键的传递函数依赖。

【Student 表】

| 学生编号 | 学生姓名 | 户口所在地 | 户口所在省 |
| :——: | :——: | :——–: | :——–: |
| 1 | 张小成 | 合肥 | 安徽 |
| 2 | 李百义 | 南京 | 江苏 |
| 3 | 王右佐 | 保定 | 河北 |

在这张表中通常学生编号就能确定学生户口所在地，而通过户口所在地即可确定户口所在省。即学生编号→户口所在地→户口所在省，所以 学生编号 与 户口所在省 是传递依赖关系，户口所在省就是冗余的。

四、事务

4.1 ACID特性

事务是数据库操作的逻辑单位，可以由一个或多个数据库操作组成，这些操作要么全部执行成功，要么全部失败，要么不执行。

  • 原子性：事务是数据库的逻辑工作单位，事务中包括的诸操作要么都做，要么都不做
  • 一致性：事务执行的结果必须是使数据库从一个一致性状态变换到另一个一致性状态
  • 隔离性：对并发执行而言，一个事务的执行不能被其他事务干扰
  • 持续性：一个事务一旦提交，它对数据库中数据的改变就应该是永久性的

4.2 并发一致性情况

丢失数据：

  1. 两个事务都对一个数据进行修改，一个先修改，另一个随后修改，第二个修改覆盖了第一个的修改。

  2. 两个事务更新相同的数据源，如果第一个事务被提交，第二个却被撤销，那么连同第一个事务做的更新也被撤销。(脏写)

读脏数据：假设事务 A 更新了一行数据的值为 A 值，此时事务 B 去查询了一下这行数据的值，看到的值是 A 值

接着，事务 B 拿着刚才查询到的 A 值做各种业务处理。事务 A 突然回滚了事务，导致它刚才功能的 A 值没了，此时那行数据的值回滚为 NULL 值。然后事务 B 紧接着此时再次查询那行数据的值，看到的居然是 NULL 值。

它的本质是事务 B 去查询了事务 A 修改过的数据，但是此时事务 A 还没提交，所以事务 A 随时会回滚导致事务 B 再次查询就读不到刚才事务 A 修改的数据了，这就是脏读。

不可重复读：T1读取一个数据，T2对该数据做了修改。如果 T1再次读取这个数据，此时读取的结果和第一次读取的结果不同。一个事务对同一行数据重复读取两次，但是却得到了不同的结果。

不可重复读，针对的是已经提交的事务修改的值，被你事务给读到了，你事务内多次查询，多次读到的是别的已经提交的事务修改过的值，这就导致不可重复读。

幻读：事务 A 根据条件查询得到了 N 条数据，但此时事务 B 删除或者增加了 M 条符合事务 A 查询条件的数据，这样当事务 A 再次进行查询的时候真实的数据集已经发生了变化，但是A却查询不出来这种变化，因此产生了幻读。归根到底还是数据集发生了改变，查询得到的数据集与真实的数据集不匹配

解决方案：

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。产生并发不一致性问题的主要原因是破坏了事务的隔离性。解决方法是通过并发控制来保证隔离性，并发控制可以通过封锁来实现。

五、索引

索引是对数据库表中一或多个列的值进行排序的结构，是帮助高效获取数据的数据结构。

1、数据库创建索引的好处：

  1. 通过创建唯一性的索引，可以保证数据库表中每一行数据的唯一性。
  2. 可以大大加快数据的检索速度。
  3. 可以加速表和表之间的连接。
  4. 在使用分组和排序子句进行数据检索时，同样可以显著的减少查询中查询中分组和排序的时间。
  5. 在查询中使用优化隐藏器，提高系统的性能。

2、增加索引的不利的方面：

  1. 创建索引和维护索引需要消耗时间，这种时间随着数量的增加而增加

  2. 索引需要占物理空间，除了数据表占据数据空间之外，每一个索引还要占一定的物理空间，聚簇索引空间更大。

  3. 数据增加，删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

所以，添加索引不一定可以增快查询速度。

3、应该哪些列建立索引：

  1. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。

  2. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。

  3. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。

  4. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

  5. 在经常使用在where子句中的列上面创建索引，加快条件的判断速度。

4、哪些列不应该创建索引：

  1. 在查询中很少使用的列不应该创建索引。

  2. 对于那些只有很少数据值的列也不应该增加索引（比如性别，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度）。

  3. 对于那些定义为text，image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

  4. 当修改性能远大于检索性能时，不应该创建索引，因为修改性能和检索性能是矛盾的。

    假设有一个在线购物网站的订单数据库。用户每分钟会生成大量新的订单，这些订单被频繁插入到数据库中。同时，网站还提供了各种查询功能，例如根据订单号查找订单、根据用户ID查找订单等等。

    在这种情况下，如果数据库的修改操作（插入新订单）远远超过了查询操作，且这些修改操作对于网站的核心业务至关重要，那么可能不适合创建过多的索引。因为创建索引会增加插入新订单的开销，可能会影响订单的实时性。相反，如果数据库的查询操作占主导地位，那么创建适当的索引将能够显著提高查询性能，因此索引是必要的。

5、基本的索引类型：

唯一性索引保证在索引列中的全部数据是唯一的，不会包含冗余数据。

复合索引就是一个索引创建在两个列或者多个列上，可以减少一在一个表中所创建的索引数量。

六、SQL

增

Insert Into Student Values (‘95020’, ‘陈冬’, ‘IS’, 18);

删

Delete From Student Where Sno=‘95019’

改

Update Student Set Sage = 22 Where Sno=‘95001’

查

Select Sname From Student  where Sdept=‘CS’;

七、锁

锁是数据库管理系统中用来控制对数据库资源的访问的机制，它可以防止并发事务之间对相同数据的冲突访问。

  • 共享锁(S锁)：用于只读操作，锁定共享的资源。共享锁不会阻止其他用户读，但是阻止其他的用户写和修改。

  • 更新锁(U锁)：用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。

  • 独占锁(X锁，也叫排他锁)：一次只能有一个独占锁用在一个资源上，并且阻止其他所有的锁包括共享缩。写是独占锁，可以有效的防止“脏读”。

八、其它

如何优化数据库？提高查询的效率（索引）？

  1. 创建合适的索引：
  2. 在经常用于查询条件的列上创建索引，尤其是在 WHERE 子句中经常用于过滤数据的列。
  3. 考虑创建组合索引，将多个列组合在一起创建索引，以支持涉及多个列的查询条件。
  4. 考虑使用全文索引来支持全文搜索操作，特别是在大型文本数据中。
  5. 定期分析和重新建立索引：
  6. 定期对数据库进行索引分析，查看索引的使用情况和性能状况。
  7. 根据索引分析结果，对不再使用或性能下降的索引进行重建或删除，以确保索引的有效性和性能。
  8. 避免使用通配符查询：尽量避免使用通配符（如 %）开头的查询条件，因为这样的查询条件无法使用索引，影响查询性能。
  9. 优化查询语句：编写高效的查询语句，避免在查询中使用不必要的子查询、重复的条件和不必要的关联操作。
  10. 分区表：对于大型表，可以考虑将表进行分区，根据特定的分区键将数据分布到不同的物理存储区域，以加速查询操作。
  11. 定期清理和优化数据库：定期清理不再使用的索引、无效的统计信息和临时数据，以减少数据库存储空间的占用和提高性能。

事务回滚，如何解决数据的不一致？事务的ACID特性怎么保证？（REDO/UNDO机制）

事务回滚是保证原子性的重要机制之一。当事务发生错误或被取消时，事务回滚可以将数据库恢复到事务开始前的状态，以保证数据库的一致性和可靠性。

要解决数据的不一致性，可以采取以下措施：

  • 合理设计数据库结构，使用约束和规则来确保数据的一致性。
  • 使用事务和锁机制来控制并发访问，防止数据的不一致性。
  • 实施严格的访问权限控制，限制用户对数据的访问和修改。
  • 定期进行数据清理和校验，及时发现和修复数据的不一致性问题。

事务的 ACID 特性通过 REDO和 UNDO机制来保证：

  • REDO 机制确保持久性，即使在系统崩溃后，也能通过重新执行事务日志中记录的操作来恢复数据库到一致状态。
  • UNDO 机制用于回滚事务，以确保原子性。如果事务执行失败或被取消，UNDO 机制可以撤销事务对数据库的修改，将数据库恢复到事务开始前的状态。