数据库（数据库引擎、数据库范式、事务的四大特性ACID）

你的名字 2023-10-14 14:49 36阅读 0赞

### Mysql数据库引擎 ###

<table> 
 <thead> 
  <tr> 
   <th>名称</th> 
   <th>事务</th> 
   <th>外键</th> 
   <th>索引</th> 
   <th>适用范围</th> 
   <th>优势、特点</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Myisam</td> 
   <td>不支持</td> 
   <td>不支持</td> 
   <td>B+树</td> 
   <td>读操作远多于写操作，不需要事务支持</td> 
   <td>访问速度快，对事务完整性没有要求，保存了表的行数</td> 
  </tr> 
  <tr> 
   <td>Innodb</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>B+树</td> 
   <td>并发度较高、需要支持事务的场景</td> 
   <td>占用更多的空间以保留数据和索引，不保存表的行数</td> 
  </tr> 
  <tr> 
   <td>Memory</td> 
   <td></td> 
   <td></td> 
   <td>B树、HASH</td> 
   <td>很快的读写速度，安全性要求较低，表小</td> 
   <td>使用内存来创建表、处理速度快，但安全性不高。</td> 
  </tr> 
  <tr> 
   <td>Merge</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td>是一组MyISAM表的组合</td> 
  </tr> 
 </tbody> 
</table>

（1）、ISAM：该引擎在读取数据方面速度很快，而且不占用大量的内存和存储资源；但是ISAM不支持事务处理、不支持外键、不能够容错、也不支持索引。该引擎在包括MySQL 5.1及其以上版本的数据库中不再支持。

（2）、MyISAM存储引擎：**mysql默认的引擎（mysql 5.1版本以前），不支持事务、也不支持行级锁和外键。**因此当执行Insert插入和Update更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。它优势是访问速度快，对事务完整性没有要求，和Innodb相比，MyIASM引擎是保存了表的行数，于是当进行select count(\*) from table语句时，可以直接的读取已经保存的值而不需要进行扫描全表。以select，insert为主的应用基本上可以用这个引擎来创建表。  
  支持3种不同的存储格式，分别是：静态表；动态表；压缩表。  
  静态表：表中的字段都是非变长字段，这样每个记录都是固定长度的，优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格）ps：在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。  
  动态表：记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能。  
  压缩表：因为每个记录是被单独压缩的，所以只有非常小的访问开支。

（3）、**InnoDB存储引擎（在新版本的mysql中成为默认存储引擎）：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束，以及自动增长列的支持。**它的设计的目标就是处理大数据容量的数据库系统。它本身实际上是基于Mysql后台的完整的系统。Mysql运行的时候，Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是，该引擎是不支持全文搜索的。同时，启动也比较的慢，它是不会保存表的行数的。对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。

(4)、MEMORY存储引擎：Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。  
  MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围：  
  Hash索引优点：Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到叶节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引；  
  Hash索引缺点：那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持。  
  Memory类型的存储引擎主要用于那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。对存储引擎为memory的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

（5）、MERGE存储引擎：Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。

#### MyISAM和InnoDB的区别 ####

Mysql5.1之前的默认引擎是MyISAM，mysql5.1之后的默认引擎是InnoDB。  
  1、事务处理上方面  
  MyISAM 强调的是性能，查询的速度比 InnoDB 类型更快，但是不提供事务支持。InnoDB 提供事务支持。  
  2、表主键  
  MyISAM：允许没有主键的表存在。InnoDB：如果没有设定主键，就会自动生成一个 6 字节的主键(用户不可见)。  
  3、外键  
  MyISAM 不支持外键，InnoDB 支持外键。  
  4、全文索引  
  MyISAM 支持全文索引，InnoDB 不支持全文索引。innodb 从 mysql 5.6 版本开始提供对全文索引的支持。  
  5、锁  
  MyISAM 只支持表级锁，InnoDB 支持行级锁和表级锁，默认是行级锁，行锁大幅度提高了多用户并发操作的性能。innodb 比较适合于插入和更新操作比较多的情况，而 myisam 则适合用于频繁查询的情况。另外，InnoDB 表的行锁也不是绝对的，如果在执行一个 SQL 语句时，MySQL 不能确定要扫描的范围，InnoDB 表同样会锁全表，例如 update table set num=1 where name like “%aaa%”。  
  6、表的具体行数  
  MyISAM：select count(\*) from table，MyISAM 只要简单的读出保存好的行数。因为MyISAM 内置了一个计数器，count(\*)时它直接从计数器中读。  
  InnoDB：不保存表的具体行数，也就是说，执行 select count(\*) from table 时，InnoDB要扫描一遍整个表来计算有多少行。    
  
**数据库范式**

数据库的“范式”，指的是设计数据库的规则。按照一定的规则设计出数据库的表和关系，能够避免在一些情况下的查询出错，并具有良好的结构。**总的来说，随着范式等级的提高，数据表属性之间的依赖关系越来越小，数据冗余越来越低。但同时，数据关系变得更加复杂，**访问一个具体数据的关系层次增加。所以像设计模式一样，不应盲目追求范式等级，应根据具体需求来选择范式。  
  几个概念：  
  1、超键（super key）：能够唯一标识一条记录的属性或属性集。  
  2、候选键（candidate key）：能够唯一标识一条记录的最小属性集，候选键是没有多余属性的超键，候选键可能有多个。  
  3、主键（主码、primary key）：某个能够唯一标识一条记录的最小属性集，选取某个候选键为主键。  
  4、外键（foreign key）：子数据表中出现的父数据表的主键，称为子数据表的外键。  
  5、代理键：当不适合用任何一个候选键作为主键时（如数据太长等），添加一个没有实际意义的键作为主键，这个键就是代理键。（如常用的序号1、2、3）

部分函数依赖：设X，Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X。  
  举个例子：学生基本信息表R中（学号，身份证号，姓名）当然学号属性取值是唯一的，在R关系中，（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以姓名部分函数依赖与（学号，身份证号）；  
  完全函数依赖：设X，Y是关系R的两个属性集合，X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖于X。  
  例子：学生基本信息表R（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在R关系中，（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以姓名完全函数依赖与（学号，班级）；  
  传递函数依赖：设X，Y，Z是关系R中互不相同的属性集合，存在X→Y(Y !→X)，Y→Z，则称Z传递函数依赖于X。  
  例子：在关系R(学号，宿舍，费用)中，(学号)->(宿舍)，宿舍！=学号，(宿舍)->(费用)，费用!=宿舍，所以符合传递函数的要求；

<table> 
 <thead> 
  <tr> 
   <th>范式等级</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1NF</td> 
   <td>每一列都是原子项，不可分割</td> 
  </tr> 
  <tr> 
   <td>2NF</td> 
   <td>非主键属性均完全依赖于主属性，消除部分依赖</td> 
  </tr> 
  <tr> 
   <td>3NF</td> 
   <td>所有非主键属性之间没有依赖关系，消除传递依赖</td> 
  </tr> 
  <tr> 
   <td>BCNF</td> 
   <td>所有属性均不传递依赖于任何候选键</td> 
  </tr> 
  <tr> 
   <td>4NF</td> 
   <td>表中不包含超过一个多值属性，消除多值依赖</td> 
  </tr> 
  <tr> 
   <td>5NF</td> 
   <td>将表拆分为二元关系，一定会损失信息</td> 
  </tr> 
 </tbody> 
</table>

**第一范式（1NF）：每一个属性都不能再分割，都是原子项。**  
  说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。减少了数据的冗余，节省存储空间，某些情况下减少了数据访问的层数，提高数据访问速度。  
  **第二范式（2NF）：满足第一范式，非主键属性均完全依赖于主键，不能只是部分依赖主键。**  
  **2NF消除了属性对主键的部分函数依赖。**可以在一定程度上消除冗余，节省存储空间。如果存在部分函数依赖，则可能存在数据冗余。在多条记录中，主键中的某一个属性可能是一样的，而如果有其他数据项函数依赖于这个不变的属性，则这些数据项也将是一样的。  
  **第三范式（3NF）：满足第一、二范式，要求每列都与主键有直接关系，不存在传递依赖。所有非主键属性之间没有函数依赖关系。**  
  既然存在函数依赖，某些数据项就能够通过其他数据项计算得出，很可能存在数据冗余。值得注意的是，在一些情况下，存在这种数据冗余有意义的。如果在表格中存储着某些运算的结果，我们在使用这些结果时就不用进行运算了，节省了运算时间，是一种“空间换时间”的做法。从这里也可以看出，应用范式并不能够保证最好的效果，需要根据应用需求进行合理取舍。  
  **BC范式（boyce-codd范式，BCNF）：满足1NF、2NF、3NF，所有属性（包含主键属性和非键主属性）都不传递依赖于任何候选键。**  
  BC范式在3NF的基础上，要求主键属性也不能传递依赖于任何候选键。当主键是复合键时，主键的某个属性可能会依赖于某个候选键。此时，关系能够符合3NF，因为并不是“非主键”属性依赖于某个非主键属性。但此关系并不符合BC范式。  
  **第四范式：要求把同一表内的多对多关系删除。**（表中不能包含一个实体的两个或多个多值属性）  
  所谓多值属性，指的是某个属性可以包含多个值。这个属性的（多个）取值，被另一个属性决定。也就是说，一旦确定了某个属性，另一个属性的多个取值就一起确定了。第四范式在第三范式的基础上，消除多值依赖。（一个表只允许一个多值依赖），消除了多值依赖，可以降低数据冗余，增删改都简单了，减少数据处理复杂度。  
  **第五范式：满足1NF、2NF、3NF、4NF，如果将表中的多元关系分解成一个一个的二元关系，一定会丢失信息。**  
  第五范式在4NF的基础上，进一步消除依赖。第五范式的要求明确，如果不用这个表就不能正确说明数据之间的联系。所以符合5NF的表已经没有任何多余依赖的存在了。所以第五范式是一个比较理想的范式。

### 53、数据库事务的四大特性（ACID） ###

如果一个数据库声称支持事务的操作，那么该数据库必须要具备以下四个特性：  
  **1、原子性（Atomicity）**  
  原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。  
  **2、一致性（Consistency）**  
  一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。  
  拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。  
  **3、隔离性（Isolation）**  
  隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。  
  即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。  
  **4、持久性（Durability）**  
  持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。  
  例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务已经正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

现在重点来说明下事务的隔离性，当多个线程都开启事务操作数据库中的数据时（并发事务），数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，在介绍数据库提供的各种隔离级别之前，我们先看看如果不考虑事务的隔离性，会发生的几种问题：  
  **1、更新丢失**  
  两个事务T1和T2读入同一数据并修改，T2提交的结果覆盖了T1提交的结果，导致T1的修改被丢失。  
  **2、脏读**  
  脏读是指在一个事务处理过程里读取了另一个未提交的事务（或者事务由于某种原因被撤销）中的数据。  
  当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下：  
  update account set money = money + 100 where name = ’B’;(此时A通知B)  
  update account set money = money - 100 where name = ’A’;  
  当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。  
  **3、不可重复读**  
  不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。  
  例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。  
  不可重复读和脏读的区别是，**脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。**  
  **4、虚读(幻读)**  
  幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。  
  **幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。**  
  现在来看看MySQL数据库为我们提供的四种隔离级别：  
  ①、Serializable(串行化)：可避免脏读、不可重复读、幻读的发生。  
  ②、Repeatable read(可重复读)：可避免脏读、不可重复读的发生。  
  ③、Read committed(读已提交)：可避免脏读的发生。  
  ④、Read uncommitted (读未提交)：最低级别，任何情况都无法保证。  
  以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中默认的隔离级别为Repeatable read(可重复读)。