关于数据库知识点的深度探析

面试中吃了几次数据库上面的亏，做个总结（关系型数据库）

数据库知识点

感觉面试的必问的一块内容了，但一开始重新看数据库的时候，就看了下数据库的一些属性，SQL规则，有哪几种锁，ACID，索引。
结果到了面试的时候就发现完全不是这么简单了...
一个一个来，慢慢补充。

数据库事务（ACID怎么实现的，隔离级别，实际应用）
锁（什么时候怎么加）
索引（有几种，怎么用）
存储结构（数据放在哪，存储过程）
SQL执行过程
数据库模块

数据库事务

定义:数据库事务是构成单一逻辑工作单元的操作集合（通俗的说就是SQL执行单元）

事务四大特征（ACID）

原子性：事务是最小单位，不可再分
上面是废话，说的现实一点应该是，只有事务中所有的操作都成功，事务才会提交。如果某个失败，则必须要会退到事务执行之前的状态，执行成功的SQL需要被撤销。
如何实现？ --回滚日志
想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，而在 MySQL 中，恢复机制是通过回滚日志（undo log）实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。

回滚日志除了能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息，它还能够在整个系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。
回滚日志并不能将数据库物理地恢复到执行语句或者事务之前的样子；它是逻辑日志，当回滚日志被使用时，它只会按照日志逻辑地将数据库中的修改撤销掉看，可以理解为，我们在事务中使用的每一条 INSERT 都对应了一条 DELETE，每一条 UPDATE 也都对应一条相反的 UPDATE 语句。
持久性：一旦事务提交成功后，事务中所有的数据操作都必须被持久化到数据库中
即数据能够被安全存储在磁盘上。
注意：当事务已经被提交之后，就无法再次回滚了，唯一能够撤回已经提交的事务的方式就是创建一个相反的事务对原操作进行『补偿』，这也是事务持久性的体现之一。
如何实现？ --重做日志
与原子性一样，事务的持久性也是通过日志来实现的，MySQL 使用重做日志（redo log）实现事务的持久性，重做日志由两部分组成，一是内存中的重做日志缓冲区，因为重做日志缓冲区在内存中，所以它是易失的，另一个就是在磁盘上的重做日志文件，它是持久的。
当我们在一个事务中尝试对数据进行修改时，它会先将数据从磁盘读入内存，并更新内存中缓存的数据，然后生成一条重做日志并写入重做日志缓存，当事务真正提交时，MySQL 会将重做日志缓存中的内容刷新到重做日志文件，再将内存中的数据更新到磁盘上。
在 InnoDB 中，重做日志都是以 512 字节的块的形式进行存储的，同时因为块的大小与磁盘扇区大小相同，所以重做日志的写入可以保证原子性，不会由于机器断电导致重做日志仅写入一半并留下脏数据。
除了所有对数据库的修改会产生重做日志，因为回滚日志也是需要持久存储的，它们也会创建对应的重做日志，在发生错误后，数据库重启时会从重做日志中找出未被更新到数据库磁盘中的日志重新执行以满足事务的持久性。

回滚日志和重做日志

到现在为止我们了解了 MySQL 中的两种日志，回滚日志（undo log）和重做日志（redo log）；在数据库系统中，事务的原子性和持久性是由事务日志（transaction log）保证的，在实现时也就是上面提到的两种日志，前者用于对事务的影响进行撤销，后者在错误处理时对已经提交的事务进行重做，它们能保证两点：

发生错误或者需要回滚的事务能够成功回滚（原子性）；
在事务提交后，数据没来得及写会磁盘就宕机时，在下次重新启动后能够成功恢复数据（持久性）；

在数据库中，这两种日志经常都是一起工作的，我们可以将它们整体看做一条事务日志，其中包含了事务的 ID、修改的行元素以及修改前后的值。

事务日志

隔离性
事务的隔离性是数据库处理数据的基础之一，如果没有数据库的事务之间没有隔离性，就会发生在级联回滚（并行情况下的事务依赖）等问题，造成性能上的巨大损失。
如何实现？ --隔离级别
所以说数据库的隔离性和一致性其实是一个需要开发者去权衡的问题，为数据库提供什么样的隔离性层级也就决定了数据库的性能以及可以达到什么样的一致性；
1. RAED UNCOMMITED：使用查询语句不会加锁，可能会读到未提交的行（Dirty Read）；
2. READ COMMITED：只对记录加记录锁，而不会在记录之间加间隙锁，所以允许新的记录插入到被锁定记录的附近，所以再多次使用查询语句时，可能得到不同的结果（Non-Repeatable Read）；
3. REPEATABLE READ：多次读取同一范围的数据会返回第一次查询的快照，不会返回不同的数据行，但是可能发生幻读（Phantom Read）；
4. SERIALIZABLE：InnoDB 隐式地将全部的查询语句加上共享锁，解决了幻读的问题；
大部分的数据库中都使用了 READ COMMITED 作为默认的事务隔离级别，但是 MySQL 使用了 REPEATABLE READ 作为默认配置；

隔离级别的实现

锁
MySQL 和常见数据库中的锁都分为两种，共享锁（Shared）和互斥锁（Exclusive），前者也叫读锁，后者叫写锁。

读锁与写锁

读锁保证了读操作可以并发执行，相互不会影响，而写锁保证了在更新数据库数据时不会有其他的事务访问或者更改同一条记录造成不可预知的问题。

时间戳
除了锁，另一种实现事务的隔离性的方式就是通过时间戳，例如 PostgreSQL 会为每一条记录保留两个字段；读时间戳中保存了所有访问该记录的事务中的最大时间戳，而记录行的写时间戳中保存了将记录改到当前值的事务的时间戳。

时间戳

使用时间戳实现事务的隔离性时，往往都会使用乐观锁，先对数据进行修改，在写回时再去判断当前值，也就是时间戳是否改变过，如果没有改变过，就写入，否则生成一个新的时间戳并再次更新数据。

多版本和快照隔离
通过维护多个版本的数据，数据库可以允许事务在数据被其他事务更新时对旧版本的数据进行读取，很多数据库都对这一机制进行了实现；因为所有的读操作不再需要等待写锁的释放，所以能够显著地提升读的性能，MySQL 和 PostgreSQL 都对这一机制进行自己的实现，也就是 MVCC，虽然各自实现的方式有所不同，MySQL 就通过文章中提到的回滚日志实现了 MVCC，保证事务并行执行时能够不等待互斥锁的释放而直接获取数据。

一致性
数据库对于 ACID 中的一致性的定义是这样的：如果一个事务原子地在一个一致的数据库中独立运行，那么在它执行之后，数据库的状态一定是一致的。对于这个概念，它的第一层意思就是对于数据完整性的约束，包括主键约束、引用约束以及一些约束检查等等，在事务的执行的前后以及过程中不会违背对数据完整性的约束，所有对数据库写入的操作都应该是合法的，并不能产生不合法的数据状态。
也就是说，数据库 ACID 中的一致性对事务的要求不止包含对数据完整性以及合法性的检查，还包含应用层面逻辑的正确。

锁

这里又要分 MyISAM 和 InnoDB，表锁与行锁，共享锁和排他锁，意向锁，记录锁，间隙锁，意向锁等。锁详解
意向锁：

意向共享锁（IS）事务打算给数据行共享锁;
事务在给一个数据行加共享锁前必须先取得该表的IS锁
意向排他锁（IX）事务打算给数据行加排他锁;
事务在给一个数据行加排他锁前必须先取得该表的IX锁

间隙锁（Next-Key锁）：
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据的索引项加锁;
对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁（Next-Key锁）.
间隙锁的目的：

防止幻读,以满足相关隔离级别的要求
满足其恢复和复制的需要
在使用范围条件检索并锁定记录时；InnoDB 这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待;
因此,在实际开发中,尤其是并发插入较多的应用;我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。

死锁：
注意：ＭyISAM表锁是deadlock free的，这是因为ＭyISAM总是一次性获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。
但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了InnoDB发生死锁是可能的。
发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并退回，另一个事务获得锁，继续完成事务。
这里列举两种死锁情况：

一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。
用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。

可以设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会。

数据库索引为什么采用B+树？

B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历。

数据库是怎么实现锁的？

在InnoDB内部用uint32类型数据表示锁的类型, 最低的 4 个 bit 表示 lock_mode, 5-8 bit 表示 lock_type(目前只用了 5 和 6 位，大小为 16 和 32 ，表示 LOCK_TABLE 和 LOCK_REC), 剩下的高位 bit 表示行锁的类型record_lock_type;

record_lock_type	lock_type	lock_mode

lock_mode:
lock_is/lock_ix/lock_s/lock_x
（表级意向共享锁，表级意向排他锁，行共享锁，行排他锁）
record_lock_type：
LOCK_ORDINARY(next-key lock，锁住记录本身和记录之前的 gap，当用RR隔离级别的时候，为了防止当前读语句的幻读使用)
LOCK_GAP（间隙锁，只锁住索引记录之间或者第一条索引记录前或者最后一条索引记录之后的范围，并不锁住记录本身）
LOCK_REC_NOT_GAP（记录锁，仅锁住记录行，不锁范围）
LOCK_INSERT_INTENTION（插入意向锁，当插入索引记录的时候用来判断是否有其他事务的范围锁冲突，如果有就需要等待）

关于具体SQL的加锁分析，可参考《非常好的加锁逻辑分析》