Mysql 基础

引子

Mysql 用的还是特别多的，面试频次也很高，基本上是每面必问。而且比较集中于 索引、事务、优化 等方向。

Mysql 引擎

MyISAM 和 InnoDB 的区别？

虽然我们一般使用的是 InnoDB 引擎，但是这个面试题也经常有人问。

我们可以从以下几个方面回答：

1. 是否支持事务
   MyISAM 不提供事务支持。
   InnoDB 提供事务支持，具有 提交(commit) 和 回滚(rollback) 事务的能力。
2. 是否支持行级锁
   MyISAM 只有表级锁(table-level locking)。
   InnoDB 支持行级锁(row-level locking)和表级锁，默认为行级锁。
3. 是否支持数据库异常崩溃后的安全恢复
   MyISAM 不支持。
   InnoDB 支持，依赖 redo log 实现。
4. 是否支持外键
   MyISAM 不支持。
   InnoDB 支持。

Mysql 锁机制

Mysql 中有哪些锁？

MyISAM 只有表级锁(table-level locking)。
InnoDB 支持行级锁(row-level locking)和表级锁，默认为行级锁。

• 表级锁：MySQL 中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
• 行级锁：MySQL 中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现 死锁。

InnoDB引擎下还有：

• Record lock：记录锁，单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 临键锁，锁定一个范围，包含记录本身

什么时候会使用到 Gap lock 间隙锁？

可重复读级别下才会有间隙锁。

间隙锁定是锁定索引记录之间的间隙，或者锁定第一个和最后一个记录之间的间隙。组织其他事务将值插入到这个间隙中来，间隙可能跨越单个索引值，多个索引值，也有可能为空。

• 间隙锁锁定的是某个索引记录之前和之后的一个间隙范围
• 间隙锁只影响一般索引
• 间隙锁是在RR隔离级别下特有的

Mysql 会发生死锁吗？

答案是 会的，InnoDB 下行级锁发生竞争，就有可能死锁。

也分为几种情况：

• 一张表两行记录交叉申请互斥锁

事务A	事务B
1、DELETE FROM t1 WHERE id = 1;
	1、DELETE FROM t1 WHERE id = 2;
2、DELETE FROM t1 WHERE id = 2;
	2、DELETE FROM t1 WHERE id = 1;

事务A执行到第二步，等待事务B释放第一步的锁；
事务B执行到第二步，等待事务A释放第一步的锁；
互相申请对方持有的锁，造成死锁

• 两张表两行记录交叉申请互斥锁
  情况与上述情况类似

Mysql 怎么应对死锁情况的？

MySQL有两种死锁处理方式：

• 等待，直到超时（innodb_lock_wait_timeout=50s）。
• 发起死锁检测，主动回滚一条事务，让其他事务继续执行（innodb_deadlock_detect=on）。

由于性能原因，一般都是使用 死锁检测 来进行处理死锁。

Innodb 提供了 wait-for graph 算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph 算法都会被触发，检测是否出现等待环路。当检测到死锁时，InnoDB 会选择代价比较小的事务进行回滚。

怎么避免死锁？

• 使用事务，不 lock tables
• 避免长事务，事务执行完毕及时提交
• 修改多个表或者多个行的时候，将修改的顺序保持一致
• 创建索引，可以使创建的锁更少

Mysql 事务

要么全部执行成功,要么全部不执行。

ACID

1. 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用
2. 一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的
3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响

Mysql 怎么保证事务的？

• redo log 保证事务的 持久性
• undo log 保证事务的 原子性
• 锁机制 + MVCC 保证事务的 隔离性
• 一致性由 持久性、原子性、隔离性 保证

并发事务带来的问题

• 脏读（Dirty read）：事务A 访问到 事务B 事务未提交的数据
• 丢失修改（Lost to modify）：事务A 修改数据-1，事务B 修改数据-1，并发下可能数据只减了1，而不是减2
• 不可重复读（Unrepeatable read）：事务A 在事务内多次读取一条数据，中间事务B 修改了这条数据的值，事务A 发现事务B 修改数据前后 获取的数据值不一样
• 幻读（Phantom read）：与 不可重复读 类似，事务A 在事务内多次读取范围数据，中间事务B 插入了几条数据，事务A 发现事务B 插入数据前后 获取的数据值范围不一样，多了几条数据

不可重复读和幻读区别

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

事务隔离级别

事务隔离级别：

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致 脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是 幻读或不可重复读 仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以 阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

Mysql 默认事务隔离级别

REPEATABLE-READ(可重复读)

MySQL InnoDB 的 REPEATABLE-READ（可重复读），可通过加锁来避免幻读，即 Next-Key Locks（record+gap 临键锁，锁定一个范围，包含记录本身）