Java程序猿之数据库理论

r囧r小猫 2022-05-15 13:37 171阅读 0赞

## 一、事务的四大特性 ACID ##

![20181029095434899.png][]

*  只有满足一致性，事务的执行结果才是正确的。
 *  在无并发的情况下，事务串行执行，隔离性一定能够满足。此时要只要能满足原子性，就一定能满足一致性。
 *  在并发的情况下，多个事务并发执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
 *  事务满足持久化是为了能应对数据库奔溃的情况。

### 1.1 原子性 Atomicity ###

原子性是指**事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败**。比如在同一个事务中的SQL语句，要么全部执行成功，要么全部执行失败。

回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

### 1.2 一致性 Consistency ###

**事务必须使数据库从一个一致性状态变换到另外一个一致性状态。** 以转账为例子，A向B转账，假设转账之前这两个用户的钱加起来总共是2000，那么A向B转账之后，不管这两个账户怎么转，A用户的钱和B用户的钱加起来的总额还是2000，这个就是事务的一致性。

### 1.3 隔离性 Isolation ###

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，**多个并发事务之间要相互隔离**。

即要达到这么一种效果：对于任意两个并发的事务 T1 和 T2，在事务 T1 看来，T2 要么在 T1 开始之前就已经结束，要么在 T1 结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

### 1.4 持久性 Durability ###

**一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。**

可以通过数据库备份和恢复来实现，在系统发生奔溃时，使用备份的数据库进行数据恢复。

## 二、并发一致性问题 ##

### 2.1 丢失修改 ###

T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

![20181029095604396.png][]

### 2.2 脏读 ###

如果一个事务中对数据进行了更新，但事务还没有提交，另一个事务可以 “看到” 该事务没有提交的更新结果，这样造成的问题就是，如果第一个事务回滚，那么，第二个事务在此之前所 “看到” 的数据就是一笔脏数据。（脏读又称无效数据读出， **一个事务读取另外一个事务还没有提交的数据叫脏读**。）

**例子：**

1.  Mary 的原工资为 1000, 财务人员将 Mary 的工资改为了 8000 (但未提交事务)
2.  Mary 读取自己的工资，发现自己的工资变为了 8000，欢天喜地！
3.  而财务发现操作有误，回滚了事务，Mary 的工资又变为了1000
4.  像这样，Mary记取的工资数8000是一个脏数据。

**解决办法：**  
　　把数据库的事务隔离级别调整到 READ\_COMMITTED

**图解：**  
　　T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

![20181029095827539.png][]

### 2.3 不可重复读 ###

是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是`不可重复读`。（同时操作，事务1分别读取事务2操作时和提交后的数据，读取的记录内容不一致。**不可重复读是指在同一个事务内，两个相同的查询返回了不同的结果。** ）

**例子：**

1.  在事务1中，Mary 读取了自己的工资为1000，操作并没有完成
2.  在事务2中，这时财务人员修改了 Mary 的工资为 2000，并提交了事务
3.  在事务1中，Mary 再次读取自己的工资时，工资变为了2000

**解决办法：**  
　　如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。把数据库的事务隔离级别调整到REPEATABLE\_READ

**图解：**  
　　T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

![20181029100120874.png][]

### 2.4 幻读 ###

事务 T1 读取一条指定的 Where 子句所返回的结果集，然后 T2 事务新插入一行记录，这行记录恰好可以满足T1 所使用的查询条件。然后 T1 再次对表进行检索，但又看到了 T2 插入的数据。 （**和可重复读类似，但是事务 T2 的数据操作仅仅是插入和删除，不是修改数据，读取的记录数量前后不一致**）。**幻读的重点在于新增或者删除 (数据条数变化)**，同样的条件，第1次和第2次读出来的记录数不一样。

**例子：**

1.  事务1，读取所有工资为 1000 的员工（共读取 10 条记录 ）
2.  这时另一个事务向 employee 表插入了一条员工记录，工资也为 1000
3.  事务1再次读取所有工资为 1000的 员工（共读取到了 11 条记录，这就产生了幻读）

**解决办法：**  
　　如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题。把数据库的事务隔离级别调整到 SERIALIZABLE\_READ

**图解：**  
　　T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

![20181029100710886.png][]

## 三、事务隔离级别 ##

<table> 
 <thead> 
  <tr> 
   <th align="left">读数据一致性及允许的并发副作用隔离级别</th> 
   <th align="left">读数据一致性</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">未提交读</td> 
   <td align="left">最低级别，只能保证不读取物理上损坏的数据</td> 
   <td>Y</td> 
   <td>Y</td> 
   <td>Y</td> 
  </tr> 
  <tr> 
   <td align="left">已提交读</td> 
   <td align="left">语句级</td> 
   <td>N</td> 
   <td>Y</td> 
   <td>Y</td> 
  </tr> 
  <tr> 
   <td align="left">可重复读</td> 
   <td align="left">事务级</td> 
   <td>N</td> 
   <td>N</td> 
   <td>Y</td> 
  </tr> 
  <tr> 
   <td align="left">可序列化</td> 
   <td align="left">最高级别，事务级</td> 
   <td>N</td> 
   <td>N</td> 
   <td>N</td> 
  </tr> 
 </tbody> 
</table>

### 3.1 未提交读 Read Uncommitted ###

最低的隔离等级，允许其他事务看到没有提交的数据，**会导致脏读**。

### 3.2 已提交读 Read Committed ###

**被读取的数据可以被其他事务修改**，这样可能导致不可重复读。也就是说，事务读取的时候获取读锁，但是在读完之后立即释放(不需要等事务结束)，而写锁则是事务提交之后才释放，释放读锁之后，就可能被其他事务修改数据。该等级也是 SQL Server 默认的隔离等级。

### 3.3 可重复读 Repeated Read ###

**所有被选中获取的数据都不能被修改**，这样就可以避免一个事务前后读取数据不一致的情况。**但是却没有办法控制幻读**，因为这个时候其他事务不能更改所选的数据，但是可以增加数据，**即前一个事务有读锁但是没有范围锁**，为什么叫做可重复读等级呢？那是因为该等级解决了下面的不可重复读问题。该等级也是 MySQL 默认的隔离等级。  
　　  
引申：现在主流数据库都使用 MVCC 并发控制，使用之后RR（可重复读）隔离级别下是不会出现幻读的现象。

### 3.4 串行化 Serializable ###

所有事务一个接着一个的执行，这样**可以避免幻读 (phantom read)**，对于基于锁来实现并发控制的数据库来说，串行化要求在执行范围查询的时候，需要获取范围锁，如果不是基于锁实现并发控制的数据库，则检查到有违反串行操作的事务时，需回滚该事务。

## 四、MyISAM VS InnoDB ##

<table> 
 <thead> 
  <tr> 
   <th>对比项</th> 
   <th>MyISAM</th> 
   <th>InnoDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主外键</td> 
   <td>N</td> 
   <td>Y</td> 
  </tr> 
  <tr> 
   <td>事务</td> 
   <td>N</td> 
   <td>Y</td> 
  </tr> 
  <tr> 
   <td>行表锁</td> 
   <td>表锁，即使操作一条记录也会锁住整个表</td> 
   <td>行锁，操作时只锁某一行，不对其他行有影响</td> 
  </tr> 
  <tr> 
   <td>缓存</td> 
   <td>只缓存索引，不缓存真实数据</td> 
   <td>不仅缓存索引还缓存真实数据，内存要求较高，且内存大小对性能有决定性的影响</td> 
  </tr> 
  <tr> 
   <td>表空间</td> 
   <td>小</td> 
   <td>大</td> 
  </tr> 
  <tr> 
   <td>关注点</td> 
   <td>性能</td> 
   <td>事务</td> 
  </tr> 
  <tr> 
   <td>默认安装</td> 
   <td>Y</td> 
   <td>Y</td> 
  </tr> 
 </tbody> 
</table>

## 五、锁机制 ##

### 5.1 乐观锁、悲观锁 ###

**1.乐观锁**  
顾名思义，就是很乐观，**每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据**，可以使用版本号等机制。

乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write\_condition机制的其实都是提供的乐观锁。

**2.悲观锁**  
顾名思义，就是很悲观，**每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁**。

传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

### 5.2 表锁 ###

**1.表共享读锁（Table Read Lock）**  
对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作。

**2.表独占写锁（Table Write Lock）**  
对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。

### 5.3 行锁 ###

**1. 共享锁（S）**  
共享锁又称为`读锁`。若事务T对数据对象A加上 S 锁，则事务T可以读A 但不能修改A，其他事务只能再对A加 S 锁，而不能加X锁，直到T释放A上的 S 锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

**2. 排他锁（X）**  
排它锁又称为`写锁`。若事务T对数据对象A加上 X 锁，则允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。这就保证了其他事务在T释放A上的锁之前不能再读取和修改A。

**3. 意向共享锁（IS）**  
表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的 IS 锁。如果需要对记录 A 加共享锁，那么此时 InnoDB 会先找到这张表，对该表加意向共享锁之后，再对记录 A 添加共享锁。

**4. 意向排他锁（IX）**  
类似上面，表示事务准备给数据行加入排他锁，也就是说事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。如果需要对记录 A 加排他锁，那么此时 InnoDB 会先找到这张表，对该表加意向排他锁之后，再对记录 A 添加排他锁。

## 六、索引 ##

索引（Index）是帮助MySQL高效获取数据的数据结构，可以被理解为**排好序的快速查找数据结构**。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

我们通常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、次要索引、覆盖索引、混合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引。

### 6.1 索引的特点 ###

1.  可以**加快数据库的检索速度**
2.  **降低数据库插入、修改、删除等维护的速度**
3.  **只能创建在表上，不能创建到视图上**
4.  既可以直接创建又可以间接创建
5.  可以在优化隐藏中使用索引
6.  使用查询处理器执行SQL语句，**在一个表上，一次只能使用一个索引**

### 6.2 索引的优点 ###

1.  **创建唯一性索引，保证数据库表中每一行数据的唯一性**
2.  **大大加快数据的检索速度**，这是创建索引的最主要的原因
3.  **加速数据库表之间的连接**，特别是在实现数据的参考完整性方面特别有意义
4.  在使用分组和排序子句进行数据检索时，同样可以**显著减少查询中分组和排序的时间**
5.  通过使用索引，可以**在查询中使用优化隐藏器，提高系统的性能**

### 6.3 索引的缺点 ###

1.  **创建索引和维护索引要耗费时间**，这种时间随着数据量的增加而增加
2.  **索引需要占用物理空间**，除了数据表占用数据空间之外，每一个索引还要占一定的物理空间，如果建立聚簇索引，那么需要的空间就会更大
3.  当对表中的数据进行增加、删除和修改的时候，**索引也需要维护，降低数据维护的速度**

### 6.4 不会使用索引的时机 ###

1.  如果MySQL估计**使用全表扫秒比使用索引快**，则不适用索引。

select * from table_name where key>1 and key<90;
    //如果列key均匀分布在1和100之间，这个查询使用索引就不是很好

1.  如果**条件中有or**，即使其中有条件带索引也不会使用。

select * from table_name where key1='a' or key2='b';
    //在key1上有索引而在key2上没有索引，则该查询也不会走索引

1.  复合索引，如果**索引列不是复合索引的第一部分**，则不使用索引（即不符合最左前缀）。

select * from table_name where key2='b';
    //符合索引为(key1,key2),则查询将不会使用索引

1.  **如果like是以 % 开始的**，则该列上的索引不会被使用。

select * from table_name where key1 like '%a';
    //该查询即使key1上存在索引，也不会被使用如果列类型是字符串，那一定要在条件中使用引号引起来，否则不会使用索引

1.  **如果列为字符串，则where条件中必须将字符常量值加引号**，否则即使该列上存在索引，也不会被使用。

select * from table_name where key1=1;
    //如果key1列保存的是字符串，即使key1上有索引，也不会被使用。

### 6.5 适合建立索引的时机 ###

1.  为经常出现在关键字order by、group by、distinct后面的字段，建立索引。
2.  在union等集合操作的结果集字段上，建立索引。
3.  经常用作查询选择 where 后的字段，建立索引。
4.  在经常用作表连接 join 的属性上，建立索引。
5.  考虑使用索引覆盖。对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。

### 6.6 索引分类 ###

#### 6.6.1 单值索引 ####

即一个索引只包含单个列，一个表可以有多个单值索引。

#### 6.6.2 唯一索引 ####

索引列的值必须唯一，但允许有空值。

#### 6.6.3 联合索引 ####

两个或更多个列上的索引被称作`联合索引`，联合索引又叫复合索引。对于复合索引：Mysql 从左到右的使用索引中的字段，**一个查询可以只使用索引中的一部份，但只能是最左侧部分**。

例如索引是key index (a,b,c)，可以支持\[a\]、\[a,b\]、\[a,b,c\] 3种组合进行查找，但不支 \[b,c\] 进行查找。当最左侧字段是常量引用时，索引就十分有效。

## 七、JOIN ##

例如我们有两张表，Orders 表通过外键 Id\_P 和 Persons 表进行关联。

![20181029104719466.png][]

### 7.1 inner join ###

在两张表进行连接查询时，只保留两张表中完全匹配的结果集。

![inner join][]

我们使用 inner join 对两张表进行连接查询，sql如下：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons
    INNER JOIN Orders
    ON Persons.Id_P=Orders.Id_P
    ORDER BY Persons.LastName

查询结果集如下，此种连接方式 Orders 表中 Id\_P 字段在 Persons 表中找不到匹配的，则不会列出来。  
![20181029104912547.png][]

### 7.2 left join ###

在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

![left join][]

我们使用 left join 对两张表进行连接查询，sql如下：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons
    LEFT JOIN Orders
    ON Persons.Id_P=Orders.Id_P
    ORDER BY Persons.LastName

查询结果如下，可以看到，左表（Persons表）中 LastName 为 Bush 的行的 Id\_P 字段在右表（Orders表）中没有匹配，但查询结果仍然保留该行。

![20181029105058750.png][]

### 7.3 right join ###

在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

![right join][]

我们使用right join对两张表进行连接查询，sql如下：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons
    RIGHT JOIN Orders
    ON Persons.Id_P=Orders.Id_P
    ORDER BY Persons.LastName

查询结果如下，Orders 表中最后一条记录 Id\_P 字段值为 65，在左表中没有记录与之匹配，但依然保留。

![20181029105211560.png][]

### 7.4 full join ###

在两张表进行连接查询时，返回左表和右表中所有没有匹配的行。

![full join][]

我们使用 full join 对两张表进行连接查询，sql如下：

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
    FROM Persons
    FULL JOIN Orders
    ON Persons.Id_P=Orders.Id_P
    ORDER BY Persons.LastName

查询结果如下，查询结果是 left join 和 right join 的并集。

![20181029105333178.png][]

[20181029095434899.png]: /images/20220503/c80ed08df1844a5fa6670185e19624cb.png
[20181029095604396.png]: /images/20220503/d21415f9b7ba4c13b1d8b3df5d12c159.png
[20181029095827539.png]: /images/20220503/d91c6c0f29724485bd3b2fa00d800a63.png
[20181029100120874.png]: /images/20220503/9e44f55f80044b4884308c24d070049c.png
[20181029100710886.png]: /images/20220503/5a59495e0e5e46a08b16ecf1f223165f.png
[20181029104719466.png]: /images/20220503/8d780143aed846e18507844d9c48bcf3.png
[inner join]: /images/20220503/e728d8f5801d4dcbb39f4dc014b56500.png
[20181029104912547.png]: /images/20220503/9b8007c904704367ae671f296711644b.png
[left join]: /images/20220503/e69bd2478ece4680a09ecc24704dfb34.png
[20181029105058750.png]: /images/20220503/42b410f0cb3245c0815fa49ae7b646c7.png
[right join]: /images/20220503/a2c21a09aac24832a15015c6ae7bd72c.png
[20181029105211560.png]: /images/20220503/ccc916ecd2924fbbb89581172d926db0.png
[full join]: /images/20220503/a40ac60be5a049bf80a74c239a1c6d8b.png
[20181029105333178.png]: /images/20220503/d586a9478c8049538fcb3141a989e831.png