LBW's Wiki Pages

# 理论知识

# 事务

定义： 一系列在数据库上执行的行为。事务是DBMS中最基础的单位；事务不可分割。

# 事务的四大特性

  1. 原子性。事物包含的所有操作要么全部成功，要么全部失败回滚。
  2. 一致性。事物必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事物执行之前和执行之后必须处于一致性状态。举例：假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
  3. 隔离性。隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。关于事务的隔离性数据库提供了多种隔离级别，稍后会介绍到。
  4. 持久性。持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

# 隔离性

下面重点介绍一下隔离性。没有隔离性会造成以下问题：

  1. 脏读。脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
  2. 不可重复读。不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发生了不可重复读。不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。
  3. 幻读（虚读）。幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）（也可以说不可重复读的重点是修改，而幻读的重点在新增或删除）。

正因为如此，就有了“隔离级别”的概念。

在谈论隔离级别之间，首先我们要知道，隔离的越严实，效率就会越低。因此很多时候，我们需要在二者之间寻找一个平衡点。SQL 标准的事务隔离级别有四种，它们分别是：

  • 读未提交（read uncommited）：一个事务还未提交时，它做的变更就能被别的事务看到。
  • 读提交（read commited）：一个事务提交之后，它做的变更才会被其他事务看到。（这种隔离级别可以防止脏读）
  • 可重复读（repeatable read）：一个事务执行过程中看到的事务，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。（这种隔离级别可以防止脏读，不可重复读）
  • 串行化（serializable）：顾名思义，对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。（这种隔离级别可以防止脏读，不可重复读和幻读）

这样讲比较抽象，我们看一个例子。假设数据表 T 中只有一列，其中一行的值为 1，下面是按照时间顺序执行两个事务的行为。

database-isolation

我们看看在不同的隔离级别下，事务 A 会有哪些不同的返回结果。

  • 若隔离级别是 读未提交，则 V1 的值就是 2。此时虽然事务 B 还没有提交，但是结果已经被 A 看到了。因此，V2，V3 也都是2.
  • 若隔离级别是 读提交，则 V1 的值 是 1，V2 的值是 2. 事务 B 的更新在提交后才能被 A 看到，所以，V3 也是2.
  • 若隔离级别是 可重复读，则 V1，V2 的值 都是 1，V3 是 2. 之所以 V2 还是 1，因为此时事务 A 还没有提交，所以它所读到的数据应该是不变的。
  • 若隔离级别是 串行化，则在事务 B 执行“将 1 改成 2”时，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度来看，V1，V2 的值是 1，V3 的值是 2.

在实现上，数据库里会创建一个视图（MVCC，多版本并发控制），访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务执行期间都在用这个视图。在“读提交“隔离级别下，这个视图是在每个 SQL 语句开始执行时创建的。这里需要注意的是，“读未提交”隔离级别下，直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

我们可以看到，在不同的隔离级别下，数据库的行为是有所不同的。Oracle 的数据库默认隔离级别是“读提交”，而 MySQL 的默认隔离级别是“可重复读”。因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别是一样的，要记得将 MySQL 的隔离级别设置为“读提交“。

虽然 MySQL 的 Innodb 默认隔离级别是“可重复读”，但是通过多版本并发控制（MVCC）的机制来避免了幻读的问题。

# 数据库的五个范式(主要理解前三个范式)

首先看看关系模式中的三种依赖：完全函数依赖，部分函数依赖，传递函数依赖。

  • 完全函数依赖：设X,Y是关系R的两个属性集合，X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖于X。 例子：学生基本信息表R（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在R关系中，（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以姓名完全函数依赖与（学号，班级）；
  • 部分函数依赖：设X,Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X。 举个例子：学生基本信息表R中（学号，身份证号，姓名）当然学号属性取值是唯一的，在R关系中，（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以姓名部分函数依赖与（学号，身份证号）；
  • 传递函数依赖：设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖于X。 例子：在关系R(学号 ,宿舍, 费用)中，(学号)->(宿舍),宿舍！=学号，(宿舍)->(费用),费用!=宿舍，所以符合传递函数的要求。
  1. 1NF（第一范式）：数据库表中的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。
  2. 2NF（第二范式）：如果关系模型R为第一范式，并且R中的每一个非主属性完全函数依赖于R的某个候选键，则称R为第二范式模式。
  3. 3NF（第三范式）：如果关系模型R是第二范式，且每个非主属性都不传递依赖于R的候选键，则称R是第三范式的模式。
  4. BCNF范式。如果关系模型R是第一范式，且每个属性都不传递依赖于R的候选键，那么称R为BCNF的模式。
  5. 4NF（第四范式）：设R是一个关系模型，D是R上的多值依赖集合。如果D中存在凡多值依赖X->Y时，X必是R的超键，那么称R是第四范式的模式。

# 索引

定义：索引是对数据库表中一个或多个列（例如，employee 表的姓名 (name) 列）的值进行排序的结构。

目前大部分数据库系统都使用 B树 或者 B+树 来作为索引结构

一般来说，应该在这些列上创建索引：

  • 经常需要搜索的列上，可以加快搜索的速度
  • 作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
  • 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度

问题： 如何知道索引是否起作用？

答：在Mysql中，explain显示了如何使用索引来处理select语句及连接表。可以帮助选择更好的索引。例如: explain select xxx from xxx

关于索引，详细信息见索引章节

JDBC →