mysql 相关知识点整理

数据库的三个范式

1. 列的原子性 （即列不能够再分成其他几列）
2. 在第一个范式的基础上，表必须有主键，没有包含主键的列必须完全依赖于主键，不能只依赖主键的一部分
3. 在第二个范式的基础之上，非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

详细讲解见链接

数据库ACID的特性

1. 原子性（Atomicity）:一个事务中的所有操作要么全部完成，要么全部不完成，不会结束在中间某个环节。
2. 一致性（consistency）：在一个事务执行之前和执行之后的数据都必须处于一致性的状态，即事务处理成功，系统中所有变化将正确应用，事务失败，所有变化都自动回滚。
3. 隔离性 （Isolation）: 事务之间的操作是相互独立的，互不影响，即事务内部操作及使用的数据对于并发的事务是封闭隔离的
4. 持久性 （Durability):只要事务成功结束，它对数据库所有的变化都必须永久的保存下来

其中原子性，隔离性以及持久性都是为了保证数据的一致性

数据库事务的隔离级别

1. 未提交读（Read Uncommitted）:允许脏读，即可能读取到其他事务中未提交事务修改的数据。
2. 提交读 （Read Committed）： 只能读取已提交的的数据。（大部分数据库比如Oracle默认该级别）
3. 可重复读 （Repeated Read）：在同一个事务内的查询都是事务开始时刻一致的，（InnoDB默认级别）。
4. 串行读（Serializable）：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

mysql锁分类

按读写

1. 独占锁（排他锁，X锁，写锁）：只要有事务对数据上加任务锁，其他事务就不能对这些数据在放置X锁，同样，某个事务放置了X锁，其他事务就不能再加其他任何锁，只有获取排他锁的事务是可以对数据进行读取和修改。
2. 共享锁（读锁，S锁）：S锁与S锁可以兼容，可以同时放置。
3. 更新锁 （U锁）：它允许再加S锁，但不允许其他事务再施加U锁或X锁，当被读取的数据要被更新时，则升级S锁为X锁。U锁的优点是允许事务A读取数据的同时不阻塞其它事务，并同时确保事务A自从上次读取数据后数据没有被更改，因此可以减少X锁和S锁的冲突，同时避免使用S锁后再升级为X锁造成的死锁现象。

注意，MySQL并不支持U锁，SQLServer才支持U锁。

按粒度

1. 行级锁
2. 页级锁
3. 表级锁

不同存储引擎支持不同锁粒度

MyISAM和MEMORY存储引擎采用的是表级锁，页级锁仅被BDB存储引擎支持，InnoDB存储引擎支持行级锁和表级锁，默认情况下是采用行级锁。

行锁

按行对数据进行加锁。InnoDB行锁是通过给索引上的索引项加锁来实现的，Innodb一定存在聚簇索引，行锁最终都会落到聚簇索引 ¹上，通过非聚簇索引 ²查询的时候，先锁非聚簇索引，然后再锁聚簇索引。如果一个where语句里面既有聚簇索引，又有二级索引，则会先锁聚簇索引，再锁二级索引。由于是分步加锁的，因此可能会有死锁发生。³

行锁兼容矩阵

1. 间隙锁（gap Lock）：只锁间隙，前开后开区间(a,b)，对索引的间隙加锁，防止其他事务插入数据。
2. 记录锁(Record Lock)：只锁记录，特定几行记录。
3. 临键锁(Next-Key Lock)：同时锁住记录和间隙，前开后闭区间(a,b]。
4. 插入意图锁(Insert Intention Lock)：插入时使用的锁。在代码中，插入意图锁，实际上是GAP锁上加了一个LOCK_INSERT_INTENTION的标记。

行锁之间的兼容关系

1. 插入意图锁操作之间不会冲突
2. 间隙锁，临键锁会阻止Insert（插入的数据刚好在区间内，不允许插入）
3. 间隙锁和记录锁，临键锁不会冲突
4. 记录锁和记录锁，临键锁不会冲突
5. 已有插入锁不阻止任何准备加的锁
6. 间隙锁只会阻塞插入锁

ps: 对于记录锁，列必须是唯一索引列或者主键列，查询语句必须为精确匹配，如“=”，否则记录锁会退化为临键锁。间隙锁和临键锁基于非唯一索引，在唯一索引列上不存在间隙锁和临键锁。

乐观锁和悲观锁

悲观锁

总是假设最坏情况，每次在拿数据时候，都认为会被别人修改，所以每次拿数据都会上锁，这样别人拿数据的时候就会阻塞。

乐观锁

总是假设最好的情况，每次拿数据都认为不会被别人修改，所以都不上锁，但在更新的时候会判断下在此期间有没有别人更新这个数据。

1. 数据版本（version）记录机制实现（最常用）：当读取数据时将version字段的值一同读出，数据没更新一次，对此的version值加一，当我们提交更新时候，判断记录的版本信息与第一次取出来的版本值是否一致，一致就更新，不一致则认为是过期数据。
2. 时间戳（timestamp）记录机制：原理同数据版本一致。

大多数数据库，出于性能考虑，都是使用以乐观锁为理论基础的MVCC（多版本并发控制）来避免不可重复读和幻读。

MVCC在InnoDB中的实现

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

1. SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。
2. INSERT时，保存当前事务版本号为行的创建版本号
3. DELETE时，保存当前事务版本号为行的删除版本号
4. UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

未完待续。。。。。。。。。。。。。。。。。。

---

1. 聚簇索引： 表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种 ↩︎

2. 非聚集索引：表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。 ↩︎

3. 聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。而对于非聚集索引，则是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。详见链接 ↩︎