关于MySQL数据库的这些核心知识点，你都掌握了吗？
微信搜索公众号路人zhang，回复面试手册，领取本文档PDF版及更多面试资料。
推荐阅读：
数据库的事务
什么是数据库的事务？
百度百科的解释：数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。
事务的四大特性是什么？
- 原子性：原子性是指包含事务的操作要么全部执行成功，要么全部失败回滚。
- 一致性：一致性指事务在执行前后状态是一致的。
- 隔离性：一个事务所进行的修改在最终提交之前，对其他事务是不可见的。
- 持久性：数据一旦提交，其所作的修改将永久地保存到数据库中。
数据库的并发一致性问题
当多个事务并发执行时，可能会出现以下问题：
- 脏读：事务A更新了数据，但还没有提交，这时事务B读取到事务A更新后的数据，然后事务A回滚了，事务B读取到的数据就成为脏数据了。
- 不可重复读：事务A对数据进行多次读取，事务B在事务A多次读取的过程中执行了更新操作并提交了，导致事务A多次读取到的数据并不一致。
- 幻读：事务A在读取数据后，事务B向事务A读取的数据中插入了几条数据，事务A再次读取数据时发现多了几条数据，和之前读取的数据不一致。
- 丢失修改：事务A和事务B都对同一个数据进行修改，事务A先修改，事务B随后修改，事务B的修改覆盖了事务A的修改。
不可重复度和幻读看起来比较像，它们主要的区别是：在不可重复读中，发现数据不一致主要是数据被更新了。在幻读中，发现数据不一致主要是数据增多或者减少了。
数据库的隔离级别有哪些？
- 未提交读：一个事务在提交前，它的修改对其他事务也是可见的。
- 提交读：一个事务提交之后，它的修改才能被其他事务看到。
- 可重复读：在同一个事务中多次读取到的数据是一致的。
- 串行化：需要加锁实现，会强制事务串行执行。
数据库的隔离级别分别可以解决数据库的脏读、不可重复读、幻读等问题。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 未提交读 | 允许 | 允许 | 允许 |
| 提交读 | 不允许 | 允许 | 允许 |
| 可重复读 | 不允许 | 不允许 | 允许 |
| 串行化 | 不允许 | 不允许 | 不允许 |
MySQL的默认隔离级别是可重复读。
隔离级别是如何实现的？
事务的隔离机制主要是依靠锁机制和MVCC(多版本并发控制)实现的，提交读和可重复读可以通过MVCC实现，串行化可以通过锁机制实现。
什么是MVCC？
MVCC(multiple version concurrent control)是一种控制并发的方法，主要用来提高数据库的并发性能。
在了解MVCC时应该先了解当前读和快照读。
- 当前读：读取的是数据库的最新版本，并且在读取时要保证其他事务不会修该当前记录，所以会对读取的记录加锁。
- 快照读：不加锁读取操作即为快照读，使用MVCC来读取快照中的数据，避免加锁带来的性能损耗。
可以看到MVCC的作用就是在不加锁的情况下，解决数据库读写冲突问题，并且解决脏读、幻读、不可重复读等问题，但是不能解决丢失修改问题。
MVCC的实现原理：
-
版本号
系统版本号：是一个自增的ID，每开启一个事务，系统版本号都会递增。
事务版本号：事务版本号就是事务开始时的系统版本号，可以通过事务版本号的大小判断事务的时间顺序。
-
行记录隐藏的列
DB_ROW_ID：所需空间6byte，隐含的自增ID，用来生成聚簇索引，如果数据表没有指定聚簇索引，InnoDB会利用这个隐藏ID创建聚簇索引。
DB_TRX_ID：所需空间6byte，最近修改的事务ID，记录创建这条记录或最后一次修改这条记录的事务ID。
DB_ROLL_PTR：所需空间7byte，回滚指针，指向这条记录的上一个版本。
它们大致长这样，省略了具体字段的值。·
-
undo日志
MVCC做使用到的快照会存储在Undo日志中，该日志通过回滚指针将一个一个数据行的所有快照连接起来。它们大致长这样。
举一个简单的例子说明下，比如最开始的某条记录长这样
现在来了一个事务对他的年龄字段进行了修改，变成了这样
现在又来了一个事务2对它的性别进行了修改，它又变成了这样
从上面的分析可以看出，事务对同一记录的修改，记录的各个会在Undo日志中连接成一个线性表，在表头的就是最新的旧纪录。
在重复读的隔离级别下，InnoDB的工作流程：
-
SELECT
作为查询的结果要满足两个条件：
- 当前事务所要查询的数据行快照的创建版本号必须小于当前事务的版本号，这样做的目的是保证当前事务读取的数据行的快照要么是在当前事务开始前就已经存在的，要么就是当前事务自身插入或者修改过的。
- 当前事务所要读取的数据行快照的删除版本号必须是大于当前事务的版本号，如果是小于等于的话，表示该数据行快照已经被删除，不能读取。
-
INSERT
将当前系统版本号作为数据行快照的创建版本号。
-
DELETE
将当前系统版本号作为数据行快照的删除版本号。
-
UPDATE
保存当前系统版本号为更新前的数据行快照创建行版本号，并保存当前系统版本号为更新后的数据行快照的删除版本号，其实就是，先删除在插入即为更新。
总结一下，MVCC的作用就是在避免加锁的情况下最大限度解决读写并发冲突的问题，它可以实现提交读和可重复度两个隔离级。
数据库的锁
什么是数据库的锁？
当数据库有并发事务的时候，保证数据访问顺序的机制称为锁机制。
数据库的锁与隔离级别的关系？
| 隔离级别 | 实现方式 |
|---|---|
| 未提交读 | 总是读取最新的数据，无需加锁 |
| 提交读 | 读取数据时加共享锁，读取数据后释放共享锁 |
| 可重复读 | 读取数据时加共享锁，事务结束后释放共享锁 |
| 串行化 | 锁定整个范围的键，一直持有锁直到事务结束 |
数据库锁的类型有哪些？
按照锁的粒度可以将MySQL锁分为三种：
| MySQL锁类别 | 资源开销 | 加锁速度 | 是否会出现死锁 | 锁的粒度 | 并发度 |
|---|---|---|---|---|---|
| 表级锁 | 小 | 快 | 不会 | 大 | 低 |
| 行级锁 | 大 | 慢 | 会 | 小 | 高 |
| 页面锁 | 一般 | 一般 | 不会 | 一般 | 一般 |
MyISAM默认采用表级锁，InnoDB默认采用行级锁。
从锁的类别上区别可以分为共享锁和排他锁
- 共享锁：共享锁又称读锁，简写为S锁，一个事务对一个数据对象加了S锁，可以对这个数据对象进行读取操作，但不能进行更新操作。并且在加锁期间其他事务只能对这个数据对象加S锁，不能加X锁。
- 排他锁：排他锁又称为写锁，简写为X锁，一个事务对一个数据对象加了X锁，可以对这个对象进行读取和更新操作，加锁期间，其他事务不能对该数据对象进行加X锁或S锁。
它们的兼容情况如下（不太会用excel，图太丑了）：
MySQL中InnoDB引擎的行锁模式及其是如何实现的？
行锁模式
在存在行锁和表锁的情况下，一个事务想对某个表加X锁时，需要先检查是否有其他事务对这个表加了锁或对这个表的某一行加了锁，对表的每一行都进行检测一次这是非常低效率的，为了解决这种问题，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁，两种意向锁都是表锁。
- 意向共享锁：简称IS锁，一个事务打算给数据行加共享锁前必须先获得该表的IS锁。
- 意向排他锁：简称IX锁，一个事务打算给数据行加排他锁前必须先获得该表的IX锁。
有了意向锁，一个事务想对某个表加X锁，只需要检查是否有其他事务对这个表加了X/IX/S/IS锁即可。
锁的兼容性如下：
行锁实现方式：INnoDB的行锁是通过给索引上的索引项加锁实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录进行加锁。
InnoDB行锁主要分三种情况：
- Record lock：对索引项加锁
- Grap lock：对索引之间的“间隙”、第一条记录前的“间隙”或最后一条后的间隙加锁。
- Next-key lock：前两种放入组合，对记录及前面的间隙加锁。
InnoDB行锁的特性：如果不通过索引条件检索数据，那么InnoDB将对表中所有记录加锁，实际产生的效果和表锁是一样的。
MVCC不能解决幻读问题，在可重复读隔离级别下，使用MVCC+Next-Key Locks可以解决幻读问题。
什么是数据库的乐观锁和悲观锁，如何实现？
乐观锁：系统假设数据的更新在大多数时候是不会产生冲突的，所以数据库只在更新操作提交的时候对数据检测冲突，如果存在冲突，则数据更新失败。
乐观锁实现方式：一般通过版本号和CAS算法实现。
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。通俗讲就是每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁。
悲观锁的实现方式：通过数据库的锁机制实现，对查询语句添加for updata。
什么是死锁？如何避免？
死锁是指两个或者两个以上进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象。在MySQL中，MyISAM是一次获得所需的全部锁，要么全部满足，要么等待，所以不会出现死锁。在InnoDB存储引擎中，除了单个SQL组成的事务外，锁都是逐步获得的，所以存在死锁问题。
如何避免MySQL发生死锁或锁冲突：
-
如果不同的程序并发存取多个表，尽量以相同的顺序访问表。
-
在程序以批量方式处理数据的时候，如果已经对数据排序，尽量保证每个线程按照固定的顺序来处理记录。
-
在事务中，如果需要更新记录，应直接申请足够级别的排他锁，而不应该先申请共享锁，更新时在申请排他锁，因为在当前用户申请排他锁时，其他事务可能已经获得了相同记录的共享锁，从而造成锁冲突或者死锁。
-
尽量使用较低的隔离级别
-
尽量使用索引访问数据，使加锁更加准确，从而减少锁冲突的机会
-
合理选择事务的大小，小事务发生锁冲突的概率更低
-
尽量用相等的条件访问数据，可以避免Next-Key锁对并发插入的影响。
-
不要申请超过实际需要的锁级别，查询时尽量不要显示加锁
-
对于一些特定的事务，可以表锁来提高处理速度或减少死锁的概率。
扫一扫
7435
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
