数据库基础知识（1）

事务

数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，是DBMS当中用户程序的任何一次执行，事务是DBMS能看到的基本修改单元。事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。

原子性(Atomicity)

原子性是指每个事务内的所有操作对于数据库要么全部成功，要么全部取消。如果事务执行失败，那么状态回到事务发生之前（即事务回滚）。

一致性(Consistency)

一致性是指事务使得数据库从一个一致状态转变到另一个一致状态。可以理解为是事务对数据完整性约束的遵循。这些约束可能包括主键约束、外键约束或是一些用户自定义约束。事务执行的前后都是合法的数据状态，不会违背任何的数据完整性。

当然也有对开发者的要求，就是不能写出错误的事务逻辑，比如银行的转账不能只加钱不减钱，这是应用层面的一致性要求。

隔离性(Isolation)

隔离性是指各个事务的执行结果不会对其他事务执行造成影响，多个事务之间的结果一定满足一个串行执行的结果。

隔离性的保证通过加互斥锁来实现，一个在执行的事务会拿到锁，这时其他事务因拿不到锁而等待，只有当前事务执行成功或者失败回滚之后才会放锁。

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

  1. 脏读(Dirty read)：事务可以读到其他事务修改但尚未提交的数据，对这种数据做的操作可能是无效的，因此称为“脏读”。
  2. 不可重复读(Unrepeatable read)：一个事务多次读取一个数据的过程中，可能有其他事务修改了该数据，导致多次读取的结果不相同，因此称为“不可重复读”。
  3. 幻读(Phantom read)：幻读与不可重复读类似，但幻读针对的是一组数据的数量变化导致的问题。当前事务对该组数据进行操作时，由于其他事务插入或删除该组数据中的某些数据（比如表里删去一行），导致前后读取的结果不一致，好像发生了幻觉一样，因此称为“幻读”。

SQL 标准定义了四个隔离级别：

  1. 读取未提交(Read Uncommited)
    • 最低级别的隔离，事务可以读取到尚未提交的事务的修改。可能会导致脏读、幻读或不可重复读。
  2. 读取已提交(Read Commited)
    • 事务智能读取到其他事务已提交的修改。可以阻止脏读，但不能阻止幻读和不可重复读。
  3. 可重复读(Repeatable Read)
    • 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但不能阻止幻读。
  4. 串行化(Serializable)
    • 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

持久性(Durability)

持久性是指被提交的事务对数据库中的数据的改变是永久的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

范式

  1. 第一范式(1NF)：无重复的列。
    在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。
  2. 第二范式(2NF)：属性完全依赖于主键
    第二范式是在第一范式的基础上建立起来的，即满足第二范式必须先满足第一范式。第二范式要求数据库表中的每个实例或记录必须可以被唯一地区分。也就是存在某一个属性，使得表中的每一个实例都可以根据该属性实现区分，不存在两个所有属性都一样的实例。
  3. 第三范式(3NF)：任何非主属性不依赖于其它非主属性
    第三范式是第二范式的一个子集，即满足第三范式必须满足第二范式。简而言之，第三范式要求一个关系中不包含已在其它关系已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。
  4. BCNF：主属性不能对主键子集依赖
    巴斯-科德范式（BCNF）是第三范式（3NF）的一个子集，即满足BCNF必须满足3NF。在3NF基础上，任何主属性不能对主键子集依赖（在3NF基础上消除对主码子集的依赖）。主属性是指可以作为该数据表候选码的属性，当选定某一组主属性作为候选码时，其余主属性不依赖于候选码的部分属性则满足BCNF。

索引

索引的工作原理

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

  • 索引的优点：

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

  • 索引的缺点：

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

  • 推荐建立索引的列：

    1. 在经常需要搜索的列上，可以加快搜索的速度；
    2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
    3. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
    4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
    5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
    6. 在经常在 where 子句中使用的列上面创建索引，加快条件的判断速度。

  • 不应该创建索引的的列：

    1. 对于那些在查询中很少使用或者参考的列不应该创建索引
      这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
    2. 对于那些只有很少数据值的列也不应该增加索引
      这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
    3. 对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引
      这是因为，这些列的数据量要么相当大，要么取值很少。
    4. 当修改性能远远大于检索性能时，不应该创建索引
      这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

索引结构——B+树与B-树

MySQL用B+树作索引，而MongoDB用B-树作索引。这是由于两者的应用场景不同导致的。

先简单介绍一下B-树和B+树的结构以及区别。

B-树和B+树都是多路非二叉树，B-树的每个子节点既存索引，又存数据，搜索时就相当于二分查找；B-树的查找时间并不稳定，在O(1)~O(logN)间波动；B-树适合单次查询，其结构没法满足范围查询的要求。

而B+树的每个非叶子节点只存索引用来存索引，所有的数据均保存在叶子节点中；所有的叶子节点形成了双向链表，适合作范围查询；尽管B+树的单次查询时间都是O(logN)，但是因为非叶子节点只存索引的缘故，B+树的高度也相对更低，所以总体查询时间并不比B-树差。

再来说说MySQL和MongoDB的应用场景。MongoDB是文档型的数据库，是一种 nosql，它使用类 Json 格式保存数据，这种情况下数据与数据之间的关联并不大，查询时仅仅只需要查询到单个数据即可。

而MySQL是关系型数据库，数据之间的关系十分紧密，区间访问是最常见的一种情况。

由此可见，针对MySQL和MongoDB的应用场景，MySQL适合用B+树，而MongoDB适合用B-树。

索引的种类

根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。

  1. 唯一索引
    唯一索引是不允许其中任何两行具有相同索引值的索引。
    当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓

  2. 主键索引
    数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。
    在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

  3. 聚集索引
    在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

聚簇索引与非聚簇索引

索引是通过二叉树的数据结构来描述的。简单来说，聚簇索引和非聚簇索引的区别在于：聚簇索引的叶节点就是数据节点；而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

详细可见聚集索引和非聚集索引的区别

参考链接

c++面试宝典
深入浅出数据库索引原理
数据库范式解析
聚集索引和非聚集索引的区别
为什么Mongodb索引用B树，而Mysql用B+树