数据库基础

范式

在设计数据库表时，通常需要遵循一些规范，也就是范式。范式主要分为第⼀范式、第⼆范式、第三范式、巴斯-科德范式（BCNF）、第四范式和第五范式，范式级别越⾼对数据表的要求越严格。 通常在设计时，只需要满足第三范式就够了。

• 第⼀范式：要求最低，只要求表中字段不可⽤在拆分（比如用户兴趣标签，不应该拼接成一个字符串）。
• 第⼆范式：在第⼀范式的基础上，要求每条记录由主键唯⼀区分，记录中所有属性都依赖于主键。
• 第三范式：在第⼆范式的基础上，要求所有属性必须直接依赖主键，不允许间接依赖。

数据库事务

数据库事务四⼤特性

数据库事务有四个特性，也就是通常说的ACID。

• 原⼦性：指事务由原⼦的操作序列组成，所有操作要么全部成功，要么全部失败回滚。
• ⼀致性：指事务的执⾏不能破坏数据库数据的完整性和⼀致性，⼀个事务在执⾏之前和执⾏之后，数据库都必须处以⼀致性状态。⽐如在做多表操作时，多个表要么都是事务后新的值，要么都是事务前的旧值。
• 隔离性：指多个⽤户并发访问数据库时，数据库为每个⽤户执⾏的事务，不能被其他事务的操作所⼲扰，多个并发事务之间要相互隔离。
• 持久性：指⼀个事务⼀旦提交并执⾏成功，那么对数据库中数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务并发问题

当多个线程同时对数据库进行操作，就难免会遇到并发问题。事务并发问题有三类：脏读、不可重复读和幻读。

• 脏读：脏读是指在⼀个事务处理过程⾥读取了另⼀个未提交的事务中的数据。
• 不可重复读：不可重复读是指对于数据库中某个数据，⼀个事务范围内多次查询返回了不同的数据值，这是由于在多次查询之间，有其他事务修改了数据并进⾏了提交。
• 幻读：指⼀个事务中执⾏两次完全相同的查询时，第⼆次查询所返回的结果集跟第⼀个查询不相同。与不可重复读的区别在于，不可重复读是对同⼀条记录，两次读取的值不同。⽽幻读是记录的增加或删除，导致两次相同条件获取的结果记录数不同。

事务隔离级别

针对于事务可能出现的并发问题，Mysql提供了四种事务隔离级别。

• 读未提交：可以读取到其他事务未提交的内容，最低的隔离级别。这个隔离级别下，脏读、幻读、不可重复读都可能出现。
• 读已提交：只能读取到其他事务已经提交的数据。这个隔离级别可以解决脏读问题。
• 可重复读：整个事务过程中，对同数据的多次读取结果是相同的。这个级别可以解决脏读和不可重复读的问题。MySQL默认的隔离级别就是可重复读。
• 串⾏化：这是最⾼的隔离级别，所有事务操作都依次顺序执⾏。这个级别会导致并发度下降，性能最差。不过这个级别可以解决前⾯提到的所有并发问题。

Mysql锁

Mysql的默认存储引擎在5.5版本后改为了 InnoDB，特点是⽀持ACID事务、⽀持外键、⽀持⾏级锁提⾼了并发效率。通过锁对象来实现事务相关控制，锁对象可以是表、⻚、⾏等不同粒度的对象。在进⾏锁操作时，InnoDB会根据事务的要求，动态地将锁的粒度从低到⾼升级，在保证数据⼀致性的情况下尽可能地提⾼并发性。

按照锁粒度来分，Mysql锁可分为表级锁、页级锁和行级锁。

表级锁

表级锁锁住的是整个表，特点是开销⼩，加锁快，锁定⼒度⼤，发⽣锁冲突的概率最⾼，并发度最低，通常⽤于对表进⾏DDL操作或备份等需要操作整张表的情况。

页级锁

页级锁锁住的是某⼀⻚的数据（16kb左右），特点是开销和加锁时间介于表级和⾏级之间，会出现死锁，锁定⼒度介于表锁和⾏锁之间，并发度⼀般。

行级锁

行级锁锁住的⾏的数据，特点是锁的实现更加复杂，开销⼤，加锁速度慢。

行级锁可细分为共享锁、排他锁、意向锁。

共享锁

共享锁⼜称S锁、读锁，可以允许读，但不能写。共享锁可以与共享锁⼀起使⽤。

⽤法：select ... from table_name lock in share mode;

排他锁

排他锁⼜称X锁、写锁，不能允许读，也不能允许写，排他锁不能与其他锁⼀起使⽤。在mysql中，update，delete，insert，alter 这些写的操作默认都会加上排他锁。select 默认不会加任何锁类型。⼀旦写数据的任务没有完成，数据是不能被其他任务读取的，这对并发操作有较⼤的影响。共享/排他锁的释放⽅式为 commit 或 rollback 。

⽤法：select ... from table_name for update。

意向锁

innoDB 为了⽀持多粒度的锁，即允许⾏级锁和表级锁共存，⽽引⼊意向锁。意向锁是指未来的某个时刻，事务可能要加共享/排他锁，先提前声明⼀个意向。这样如果有⼈尝试对全表进⾏修改，就不需要判断表中的数据是否被加锁了，只需要通过等待意向互斥锁被释放就⾏了。 意向锁又分为意向共享锁和意向互斥锁。

• 意向共享锁（IS）：表示事务准备给数据⾏加⼊共享锁，也就是说⼀个数据⾏加共享锁前必须先取得该表的IS锁；
• 意向互斥锁（IX） ：表示事务准备给数据⾏加⼊排他锁，说明事务在⼀个数据⾏加排他锁前必须先取得该表的IX锁。

IX，IS是表级锁，不会和⾏级的X、S锁发⽣冲突，只会和表级的X、S发⽣冲突。意向锁之间是互相兼容的。

记录锁/行锁（record lock）

记录锁/行锁是指单个⾏记录上的锁。记录锁总是会锁住索引记录，如果 innoDB 存储引擎表在建⽴的时候没有设置任何⼀个索引，那么innoDB存储引擎会使⽤隐式的主键来进⾏锁定。

间隙锁（gap lock)）

间隙锁锁住记录中的间隔，即范围查询的记录。间隙锁的主要⽬的就是为了防⽌其他事务在间隔中插⼊数据，以导致不可重复读。如果把事务的隔离级别降级为读已提交(Read Committed)，那么间隙锁会⾃动失效。

临键锁（next-key锁)）

innoDB默认的隔离级别是可重复读，并且会以临键锁的⽅式对数据⾏进⾏加锁。临键锁是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会⾸先对索引记录加上⾏锁，再对索引记录两边的间隙加上间隙锁。加上间隙锁之后，其他事务就不能在这个间隙修改或者插⼊记录。当查询的索引含有唯⼀属性（唯⼀索引或主键索引）时，Innodb 存储引擎会对临键锁进⾏优化，将其降为行锁，即仅锁住索引本身，⽽不是范围。

插⼊意向锁（insert intention lock）

插⼊意向锁是⼀种间隙锁形式的意向锁，在真正执⾏ INSERT 操作之前设置。

当执⾏插⼊操作时，总会检查当前插⼊操作的下⼀条记录（已存在的主索引节点）上是否存在锁对象，判断是否锁住了 gap，如果锁住了，则判定和插⼊意向锁冲突，当前插⼊操作就需要等待，也就是配合上⾯的间隙锁或者临键锁⼀起防⽌了幻读操作。

申请插入意向锁的动作是数据库完成的，就是说，事务A申请⼀⾏的⾏锁的时候，数据库会⾃动先开始申请表的插入意向锁。

锁冲突

有了锁，就肯定会出现锁竞争，也就会有锁冲突情况。Mysql中的锁冲突情况主要有下面2种情况：

• 共享锁和排他锁的冲突：共享锁和排他锁之间存在冲突。当某个事务持有共享锁时，其他事务可以同时申请共享锁，但不能申请排他锁。如果某个事务持有排他锁，则其他事务⽆法同时申请共享锁和排他锁。
• ⾏级锁和表级锁的冲突：⾏级锁和表级锁之间也存在冲突。当某个事务申请⾏级锁时，其他事务可以同时申请锁定其他⾏，但不能申请锁定整张表。如果某个事务持有表级锁，则其他事务⽆法申请⾏级锁和表级锁。

死锁

当出现锁冲突时，事务就需要等待锁资源。如果出现互相等待的情况，那么就会出现死锁问题。下面具体分析下死锁出现的情况。

加锁顺序不⼀致导致的死锁

想象这种情况：事务1持有资源1的锁，事务2持有资源2的锁，事务1申请资源2的锁发⽣等待，这时如果事务2申请资源1的锁，就会出现死锁。这种情况下就需要对某一个事务进行回滚，比如事务2释放资源2的锁，取消对资源1锁的竞争。

插⼊意向锁和间隙锁发⽣死锁

假设现在有表 t，只存在 id 为 1和2的记录，当两个事务同时插入不同记录时，场景如下：

插入意向锁和间隙锁死锁场景模拟

注意当对未存在的⾏进⾏加锁的时候，mysql是会锁住⼀段范围，即间隙锁。

⾏锁与间隙锁发⽣死锁

假设现在有表 t，主键id最大值是5，模拟场景如下：

行锁与间隙锁死锁场景模拟

死锁避免方法

要避免死锁，最直接的⽅法就是破坏产⽣死锁的条件，如互斥条件、循环等待等。⽐如：

• 不同事务中的加锁顺序尽量保持统⼀；
• 尽量避免⼤事务，占有的资源锁越多，越容易出现死锁；
• 尽量避免间隙锁，可以将间隙所转化为⾏锁

死锁解决

当出现死锁后，Mysql提供了以下方案来解决：

• 设置超时，即当两个事务互相等待时，当⼀个等待时间超过设置的阈值时，其中⼀个事务进⾏回滚，另⼀个等待的事务就能继续执⾏。在 innodb 存储引擎中，可以用参数 innodb_lock_wait_timeout 来设置超时时间。
• 采⽤wait-for graph的⽅式来进⾏死锁检测，如果检测到死锁，会选择回滚undo量最⼩的事务。

索引

索引分类

Mysql中的索引可以分为以下几种：

• 唯⼀索引：就是索引列中的值必须是唯⼀的，但是允许出现空值。这种索引⼀般⽤来保证数据的唯⼀性，⽐如保存账户信息的表，每个账户的id必须保证唯⼀，如果重复插⼊相同的账户id时会MySQL返回异常。
• 主键索引 ：是⼀种特殊的唯⼀索引，但是它不允许出现空值。
• 普通索引 ：与唯⼀索引不同，它允许索引列中存在相同的值。例如学⽣的成绩表，各个学科的分数是允许重复的，就可以使⽤普通索引。
• 联合索引：就是由多个列共同组成的索引。联合索引需要注意最左原则，就是Where查询条件中的字段必须与索引字段从左到右进⾏匹配。⽐如，⼀个⽤户信息表，⽤姓名和年龄组成了联合索引，如果查询条件是姓名等于张三，那么满⾜最左原则；如果查询条件是年龄⼤于20，由于索引中最左的字段是姓名不是年龄，所以不能使⽤这个索引。
• 全⽂索引：在5.6版本后InnoDB引擎也⽀持了全⽂索引，并且在5.7.6版本后⽀持了中⽂索引。全⽂索引只能在 CHAR、VARCHAR 和 TEXT 类型字段上使⽤，底层使⽤倒排索引实现。要注意对于⼤数据量的表，⽣成全⽂索引会⾮常消耗时间也⾮常消耗磁盘空间。

索引实现

索引主要有以下四种实现方式：

• B-Tree：b+树⽐较适合⽤作'>'或'<'这样的范围查询，是MySQL中最常使⽤的⼀种索引实现。
• R-Tree：是⼀种⽤于处理多维数据的数据结构，可以对地理数据进⾏空间索引。不过实际业务场景中使⽤的⽐较少。
• Hash：是使⽤散列表来对数据进⾏索引，Hash⽅式不像B-tree那样需要多次查询才能定位到记录，因此Hash索引的效率⾼于B-tree，但是不⽀持范围查找和排序等功能。实际使⽤的也⽐较少。
• FullText：就是全⽂索引，是⼀种记录关键字与对应⽂档关系的倒排索引。