C++面试题（期）-数据库（二）

1.3 事务

1.3.1 说一说你对数据库事务的了解

1.3.2 事务有哪几种类型，它们之间有什么区别？

1.3.3 MySQL的ACID特性分别是怎么实现的？

1.3.4 谈谈MySQL的事务隔离级别

1.3.5 MySQL的事务隔离级别是怎么实现的？

1.3.6 事务可以嵌套吗？

1.3.7 如何实现可重复读？

1.3.8 如何解决幻读问题？

1.4.2 介绍一下间隙锁

1.4.3 InnoDB中行级锁是怎么实现的？

1.4.4 数据库在什么情况下会发生死锁？

1.4.5 说说数据库死锁的解决办法

1.5 优化

1.5.1 说一说你对数据库优化的理解

1.5.2 该如何优化MySQL的查询？

1.5.3 怎样插入数据才能更高效？

1.5.4 表中包含几千万条数据该怎么办？

1.5.5 MySQL的慢查询优化有了解吗？

1.5.6 说一说你对explain的了解

1.6 其他

1.6.1 介绍一下数据库设计的三大范式

1.6.2 说一说你对MySQL引擎的了解

1.6.3 说一说你对redo log、undo log、binlog的了解

1.6.4 谈谈你对MVCC的了解

1.6.5 MySQL主从同步是如何实现的？

1.3 事务

1.3.1 说一说你对数据库事务的了解

参考答案

事务可由一条非常简单的 SQL 语句组成，也可以由一组复杂的 SQL 语句组成。在事务中的操作，要么都执行修改，要么都不执行，这就是事务的目的，也是事务模型区别于文件系统的重要特征之一。事务需遵循ACID 四个特性：

A （ atomicity ）， 原子性 。原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的

数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个 SQL 语句执行失败，那么已经

执行成功的 SQL 语句也必须撤销，数据库状态应该退回到执行事务前的状态。

C （ consistency ），一致性。一致性指事务将数据库从一种状态转变为另一种一致的状态。在事务

开始之前和事务结束以后，数据库的完整性约束没有被破坏。

I （ isolation ），隔离性。事务的隔离性要求每个读写事务的对象与其他事务的操作对象能相互分

离，即该事务提交前对其他事务都不可见，这通常使用锁来实现。

D （ durability ），持久性。事务一旦提交，其结果就是永久性的，即使发生宕机等故障，数据库

也能将数据恢复。持久性保证的是事务系统的高可靠性，而不是高可用性。

事务可以分为以下几种类型：

扁平事务：是事务类型中最简单的一种，而在实际生产环境中，这可能是使用最为频繁的事务。在

扁平事务中，所有操作都处于同一层次，其由 BEGIN WORK 开始，由 COMMIT WORK 或

ROLLBACK WORK 结束。处于之间的操作是原子的，要么都执行，要么都回滚。

带有保存点的扁平事务：除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务

中较早的一个状态，这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效，

放弃整个事务不合乎要求，开销也太大。保存点（ savepoint ）用来通知系统应该记住事务当前的

状态，以便以后发生错误时，事务能回到该状态。

链事务：可视为保存点模式的一个变种。链事务的思想是：在提交一个事务时，释放不需要的数据

对象，将必要的处理上下文隐式地传给下一个要开始的事务。注意，提交事务操作和开始下一个事

务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就好像在一个事务

中进行的。嵌套事务：是一个层次结构框架。有一个顶层事务（top-level transaction ）控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务（subtransaction ），其控制每一个局部的变换。分布式事务：通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。对于分布式事务，同样需要满足ACID 特性，要么都发生，要么都失效。

对于 MySQL 的 InnoDB 存储引擎来说，它支持扁平事务、带有保存点的扁平事务、链事务、分布式事务。对于嵌套事务，MySQL 数据库并不是原生的，因此对于有并行事务需求的用户来说 MySQL 就无能为力了，但是用户可以通过带有保存点的事务来模拟串行的嵌套事务。

1.3.2 事务有哪几种类型，它们之间有什么区别？

参考答案

事务可以分为以下几种类型：

扁平事务：是事务类型中最简单的一种，而在实际生产环境中，这可能是使用最为频繁的事务。在

扁平事务中，所有操作都处于同一层次，其由 BEGIN WORK 开始，由 COMMIT WORK 或

ROLLBACK WORK 结束。处于之间的操作是原子的，要么都执行，要么都回滚。

带有保存点的扁平事务：除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务

中较早的一个状态，这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效，

放弃整个事务不合乎要求，开销也太大。保存点（ savepoint ）用来通知系统应该记住事务当前的

状态，以便以后发生错误时，事务能回到该状态。

链事务：可视为保存点模式的一个变种。链事务的思想是：在提交一个事务时，释放不需要的数据

对象，将必要的处理上下文隐式地传给下一个要开始的事务。注意，提交事务操作和开始下一个事

务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就好像在一个事务

中进行的。

嵌套事务：是一个层次结构框架。有一个顶层事务（ top-level transaction ）控制着各个层次的事

务。顶层事务之下嵌套的事务被称为子事务（ subtransaction ），其控制每一个局部的变换。

分布式事务：通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中

的不同节点。对于分布式事务，同样需要满足 ACID 特性，要么都发生，要么都失效。

1.3.3 MySQL的ACID特性分别是怎么实现的？

参考答案

原子性实现原理：

实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的 sql 语句。 InnoDB 实现回滚靠的是undo log，当事务对数据库进行修改时， InnoDB 会生成对应的 undo log 。如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用 undo log 中的信息将数据回滚到修改之前的样子。

undo log 属于逻辑日志，它记录的是 sql 执行相关的信息。当发生回滚时， InnoDB 会根据 undo log 的内容做与之前相反的工作。对于insert ，回滚时会执行 delete 。对于 delete ，回滚时会执行 insert 。对于update，回滚时则会执行相反的 update ，把数据改回去。

持久性实现原理：

InnoDB 作为 MySQL 的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘 IO ，效率会很低。为此，InnoDB 提供了缓存 (Buffer Pool) ， Buffer Pool 中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲。当从数据库读取数据时，会首先从Buffer Pool 中读取，如果 Buffer Pool 中没有，则从磁盘读取后放入Buffer Pool 。当向数据库写入数据时，会首先写入 Buffer Pool ， Buffer Pool 中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。 Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL 宕机，而此时 Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是， redo log 被引入来解决这个问题。当数据修改时，除了修改 Buffer Pool 中的数据，还会在 redo log记录这次操作。当事务提交时，会调用 fsync 接口对 redo log 进行刷盘。如果 MySQL 宕机，重启时可以读取redo log 中的数据，对数据库进行恢复。 redo log 采用的是 WAL （ Write-ahead logging ，预写式日志），所有修改先写入日志，再更新到Buffer Pool ，保证了数据不会因 MySQL 宕机而丢失，从而满足了持久性要求。既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将 Buffer Pool 中修改的数据写入磁盘( 即刷脏 ) 要快呢？主要有以下两方面的原因：

刷脏是随机 IO ，因为每次修改的数据位置随机，但写 redo log 是追加操作，属于顺序 IO 。

刷脏是以数据页（Page ）为单位的， MySQL 默认页大小是 16KB ，一个 Page 上一个小修改都要整页写入。而redo log 中只包含真正需要写入的部分，无效 IO 大大减少。

隔离性实现原理：

隔离性追求的是并发情形下事务之间互不干扰。简单起见，我们主要考虑最简单的读操作和写操作 ( 加锁读等特殊读操作会特殊说明) ，那么隔离性的探讨，主要可以分为两个方面。

第一方面， ( 一个事务 ) 写操作对 ( 另一个事务 ) 写操作的影响：锁机制保证隔离性。

隔离性要求同一时刻只能有一个事务对数据进行写操作， InnoDB 通过锁机制来保证这一点。锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁。获得锁之后，事务便可以修改数据。该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的锁。表锁在操作数据时会锁定整张表，并发性能较差。行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源，因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同的存储引擎支持的锁是不一样的，例如MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

第二方面， ( 一个事务 ) 写操作对 ( 另一个事务 ) 读操作的影响： MVCC 保证隔离性。

InnoDB 默认的隔离级别是 RR （ REPEATABLE READ ）， RR 解决脏读、不可重复读、幻读等问题，使用的是MVCC 。 MVCC 全称 Multi-Version Concurrency Control ，即多版本的并发控制协议。它最大的优点是读不加锁，因此读写不冲突，并发性能好。InnoDB 实现 MVCC ，多个版本的数据可以共存，主要基于以下技术及数据结构：

1. 隐藏列： InnoDB 中每行数据都有隐藏列，隐藏列中包含了本行数据的事务 id 、指向 undo log 的指

针等。

2. 基于 undo log 的版本链：每行数据的隐藏列中包含了指向 undo log 的指针，而每条 undo log 也会

指向更早版本的 undo log ，从而形成一条版本链。

3. ReadView ：通过隐藏列和版本链， MySQL 可以将数据恢复到指定版本。但是具体要恢复到哪个版本，则需要根据ReadView 来确定。所谓 ReadView ，是指事务（记做事务 A）在某一时刻给整个事务系统（ trx_sys ）打快照，之后再进行读操作时，会将读取到的数据中的事务 id 与 trx_sys 快照比

较，从而判断数据对该 ReadView 是否可见，即对事务 A 是否可见。

一致性实现原理：

可以说，一致性是事务追求的最终目标。前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障。实现一致性的措施包括：

保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证。

数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等。

应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据

库实现的多么完美，也无法保证状态的一致。

1.3.4 谈谈MySQL的事务隔离级别

参考答案

SQL 标准定义了四种隔离级别，这四种隔离级别分别是：

读未提交（ READ UNCOMMITTED ）；

读提交（ READ COMMITTED ）；

可重复读（ REPEATABLE READ ）；

串行化（ SERIALIZABLE ）。

事务隔离是为了解决脏读、不可重复读、幻读问题，下表展示了 4 种隔离级别对这三个问题的解决程度：

上述 4 种隔离级别 MySQL 都支持，并且 InnoDB 存储引擎默认的支持隔离级别是 REPEATABLE READ ，但是与标准SQL 不同的是， InnoDB 存储引擎在 REPEATABLE READ 事务隔离级别下，使用 Next-Key Lock 的锁算法，因此避免了幻读的产生。所以，InnoDB 存储引擎在默认的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL 标准的 SERIALIZABLE 隔离级别。

扩展阅读

并发情况下，读操作可能存在的三类问题：

1. 脏读：当前事务 (A) 中可以读到其他事务 (B) 未提交的数据（脏数据），这种现象是脏读。

2. 不可重复读：在事务 A 中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。

3. 幻读：在事务 A 中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

1.3.5 MySQL的事务隔离级别是怎么实现的？

参考答案

READ UNCOMMITTED ：

它是性能最好、也最野蛮的方式，因为它压根儿就不加锁，所以根本谈不上什么隔离效果，可以理解为没有隔离。

SERIALIZABLE ：

读的时候加共享锁，其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读。

REPEATABLE READ & READ COMMITTED ：

为了解决不可重复读， MySQL 采用了 MVVC ( 多版本并发控制 ) 的方式。我们在数据库表中看到的一行记录可能实际上有多个版本，每个版本的记录除了有数据本身外，还要有一个表示版本的字段，记为 row trx_id，而这个字段就是使其产生的事务的 id ，事务 ID 记为 transaction id，它在事务开始的时候向事务系统申请，按时间先后顺序递增。如下图，一行记录现在有 3 个版本，每一个版本都记录这使其产生的事务 ID ，比如事务 A 的 transaction id 是 100 ，那么版本 1 的 row trx_id 就是 100 ，同理版本 2 和版本 3。

可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读提交则是每次执行语句的时候都重新生成一次快照。对于一个快照来说，它能够读到那些版本数据，要遵循以下规则：

1. 当前事务内的更新，可以读到；

2. 版本未提交，不能读到；

3. 版本已提交，但是却在快照创建后提交的，不能读到；

4. 版本已提交，且是在快照创建前提交的，可以读到。

再强调一次，两者主要的区别就是在快照的创建上，可重复读仅在事务开始是创建一次，而读提交每次执行语句的时候都要重新创建一次。MySQL 已经在可重复读隔离级别下解决了幻读的问题，用的是间隙锁。 MySQL 把行锁和间隙锁合并在一起，解决了并发写和幻读的问题，这个锁叫做 Next-Key 锁。假设现在表中有两条记录，并且 age 字段已经添加了索引，两条记录 age 的值分别为 10 和 30 。此时，在数据库中会为索引维护一套B+ 树，用来快速定位行记录。 B+ 索引树是有序的，所以会把这张表的索引分割成几个区间。

此时，在数据库中会为索引维护一套 B+ 树，用来快速定位行记录。 B+ 索引树是有序的，所以会把这张表的索引分割成几个区间。如图所示，分成了3 个区间，在这 3 个区间是可以加间隙锁的。

之后，我用下面的两个事务演示一下加锁过程。

在事务 A 提交之前，事务 B 的插入操作只能等待，这就是间隙锁起得作用。当事务 A 执行 update user set name='风筝 2 号 ’ where age = 10 ; 的时候，由于条件 where age = 10 ，数据库不仅在 age =10 的行上添加了行锁，而且在这条记录的两边，也就是( 负无穷 ,10] 、 (10,30] 这两个区间加了间隙锁，从而导致事务B 插入操作无法完成，只能等待事务 A 提交。不仅插入 age = 10 的记录需要等待事务 A 提交，age<10、 10<age<30 的记录页无法完成，而大于等于 30 的记录则不受影响，这足以解决幻读问题了。这是有索引的情况，如果 age 不是索引列，那么数据库会为整个表加上间隙锁。所以，如果是没有索引的话，不管 age 是否大于等于 30 ，都要等待事务 A 提交才可以成功插入。

1.3.6 事务可以嵌套吗？

参考答案

可以，因为嵌套事务也是众多事务分类中的一种，它是一个层次结构框架。有一个顶层事务控制着各个层次的事务，顶层事务之下嵌套的事务被称为子事务，它控制每一个局部的变换。需要注意的是，MySQL 数据库不支持嵌套事务。

1.3.7 如何实现可重复读？

参考答案

为了实现可重复读， MySQL 采用了 MVVC ( 多版本并发控制 ) 的方式。

我们在数据库表中看到的一行记录可能实际上有多个版本，每个版本的记录除了有数据本身外，还要有一个表示版本的字段，记为 row trx_id ，而这个字段就是使其产生的事务的 id ，事务 ID 记为

transaction id ，它在事务开始的时候向事务系统申请，按时间先后顺序递增。

如下图，一行记录现在有 3 个版本，每一个版本都记录这使其产生的事务 ID ，比如事务 A 的 transactionid 是 100 ，那么版本 1 的 row trx_id 就是 100 ，同理版本 2 和版本 3 。

可重复读是在事务开始的时候生成一个当前事务全局性的快照。对于一个快照来说，它能够读到那些版本数据，要遵循以下规则：

1. 当前事务内的更新，可以读到；

2. 版本未提交，不能读到；

3. 版本已提交，但是却在快照创建后提交的，不能读到；

4. 版本已提交，且是在快照创建前提交的，可以读到。

1.3.8 如何解决幻读问题？

参考答案

MySQL 已经在可重复读隔离级别下解决了幻读的问题，用的是间隙锁。 MySQL 把行锁和间隙锁合并在一起，解决了并发写和幻读的问题，这个锁叫做 Next-Key 锁。假设现在表中有两条记录，并且 age 字段已经添加了索引，两条记录 age 的值分别为 10 和 30 。此时，在数据库中会为索引维护一套B+ 树，用来快速定位行记录。 B+ 索引树是有序的，所以会把这张表的索引分割成几个区间。

之后，我用下面的两个事务演示一下加锁过程。

在事务 A 提交之前，事务 B 的插入操作只能等待，这就是间隙锁起得作用。当事务 A 执行 update user set name='风筝 2 号 ’ where age = 10 ; 的时候，由于条件 where age = 10 ，数据库不仅在 age=10 的行上添加了行锁，而且在这条记录的两边，也就是( 负无穷 ,10] 、 (10,30] 这两个区间加了间隙锁，从而导致事务B 插入操作无法完成，只能等待事务 A 提交。不仅插入 age = 10 的记录需要等待事务 A 提交，age<10、 10<age<30 的记录页无法完成，而大于等于 30 的记录则不受影响，这足以解决幻读问题了。这是有索引的情况，如果 age 不是索引列，那么数据库会为整个表加上间隙锁。所以，如果是没有索引的话，不管 age 是否大于等于 30 ，都要等待事务 A 提交才可以成功插入。

1.3.9 MySQL 事务如何回滚？

参考答案

在 MySQL 默认的配置下，事务都是自动提交和回滚的。当显示地开启一个事务时，可以使用 ROLLBACK 语句进行回滚。该语句有两种用法：

ROLLBACK ：要使用这个语句的最简形式，只需发出 ROLLBACK 。同样地，也可以写为 ROLLBACK WORK，但是二者几乎是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。 ROLLBACK TO [SAVEPOINT] identifier ：这个语句与 SAVEPOINT 命令一起使用。可以把事务回滚到标记点，而不回滚在此标记点之前的任何工作。

4.4 锁

4.4.1 了解数据库的锁吗？

参考答案

锁是数据库系统区别于文件系统的一个关键特性，锁机制用于管理对共享资源的并发访问。下面我们以MySQL数据库的 InnoDB 引擎为例，来说明锁的一些特点。

锁的类型： InnoDB存储引擎实现了如下两种标准的行级锁：

共享锁（ S Lock ），允许事务读一行数据。

排他锁（ X Lock ），允许事务删除或更新一行数据。如果一个事务T1 已经获得了行 r 的共享锁，那么另外的事务 T2 可以立即获得行 r 的共享锁，因为读取并没有改变行r 的数据，称这种情况为锁兼容。但若有其他的事务 T3 想获得行 r 的排他锁，则其必须等待事务T1、 T2 释放行 r 上的共享锁，这种情况称为锁不兼容。下图显示了共享锁和排他锁的兼容性，可以发现 X锁与任何的锁都不兼容，而S 锁仅和 S 锁兼容。需要特别注意的是， S 和 X 锁都是行锁，兼容是指对同一记录（row ）锁的兼容性情况。

锁的粒度：

InnoDB 存储引擎支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB 存储引擎支持一种额外的锁方式，称之为意向锁。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁：

意向共享锁（ IS Lock ），事务想要获得一张表中某几行的共享锁。

意向排他锁（ IX Lock ），事务想要获得一张表中某几行的排他锁。

由于InnoDB 存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下图所示。

锁的算法：

InnoDB 存储引擎有 3 种行锁的算法，其分别是：

Record Lock ：单个行记录上的锁。

Gap Lock ：间隙锁，锁定一个范围，但不包含记录本身。

Next-Key Lock ∶ Gap Lock+Record Lock ，锁定一个范围，并且锁定记录本身。

Record Lock 总是会去锁住索引记录，如果 InnoDB 存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB 存储引擎会使用隐式的主键来进行锁定。 Next-Key Lock 是结合了 Gap Lock 和 Record Lock的一种锁定算法，在 Next-Key Lock 算法下， InnoDB 对于行的查询都是采用这种锁定算法。采用Next-Key Lock的锁定技术称为 Next-Key Locking ，其设计的目的是为了解决 Phantom Problem （幻读）。而利用这种锁定技术，锁定的不是单个值，而是一个范围，是谓词锁（predict lock ）的一种改进。

关于死锁：

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。

锁的信息链表；

事务等待链表；

通过上述链表可以构造出一张图，而在这个图中若存在回路，就代表存在死锁，因此资源间相互发生等待。这是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB存储引擎选择回滚 undo 量最小的事务。

锁的升级：

锁升级（ Lock Escalation ）是指将当前锁的粒度降低。举例来说，数据库可以把一个表的 1000 个行锁升级为一个页锁，或者将页锁升级为表锁。

InnoDB 存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。

1.4.2 介绍一下间隙锁

参考答案

InnoDB 存储引擎有 3 种行锁的算法，间隙锁（ Gap Lock ）是其中之一。间隙锁用于锁定一个范围，但不包含记录本身。它的作用是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生。

1.4.3 InnoDB中行级锁是怎么实现的？

参考答案

InnoDB 行级锁是通过给索引上的索引项加锁来实现的。只有通过索引条件检索数据， InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

当表中锁定其中的某几行时，不同的事务可以使用不同的索引锁定不同的行。另外，不论使用主键索引、唯一索引还是普通索引，InnoDB 都会使用行锁来对数据加锁。

1.4.4 数据库在什么情况下会发生死锁？

参考答案

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。下图演示了死锁的一种经典的情况，即A 等待 B 、 B 等待 A ，这种死锁问题被称为AB-BA 死锁。

1.4.5 说说数据库死锁的解决办法

参考答案

解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。除了超时机制，当前数据库还都普遍采用wait-for graph （等待图）的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB 存储引擎也采用的这种方式。 wait-for graph 要求数据库保存以下两种信息：锁的信息链表；事务等待链表；通过上述链表可以构造出一张图，而在这个图中若存在回路，就代表存在死锁，因此资源间相互发生等待。这是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB 存储引擎选择回滚 undo 量最小的事务。

1.5 优化

1.5.1 说一说你对数据库优化的理解

参考答案

MySQL 数据库优化是多方面的，原则是 减少系统的瓶颈，减少资源的占用，增加系统的反应速度 。例如，通过优化文件系统，提高磁盘I\O 的读写速度；通过优化操作系统调度策略，提高 MySQL 在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。针对查询，我们可以通过使用索引、使用连接代替子查询的方式来提高查询速度。针对慢查询，我们可以通过分析慢查询日志，来发现引起慢查询的原因，从而有针对性的进行优化。针对插入，我们可以通过禁用索引、禁用检查等方式来提高插入速度，在插入之后再启用索引和检查。针对数据库结构，我们可以通过将字段很多的表拆分成多张表、增加中间表、增加冗余字段等方式进行优化。

1.5.2 该如何优化MySQL的查询？

参考答案

使用索引 ：

如果查询时没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询记录，从而减少查询的记录数，达到提高查询速度的目的。索引可以提高查询的速度，但并不是使用带有索引的字段查询时索引都会起作用。有几种特殊情况，在这些情况下有可能使用带有索引的字段查询时索引并没有起作用。

1. 使用 LIKE 关键字的查询语句

在使用 LIKE 关键字进行查询的查询语句中，如果匹配字符串的第一个字符为 “%” ，索引不会起作

用。只有 “%” 不在第一个位置，索引才会起作用。

2. 使用多列索引的查询语句

MySQL 可以为多个字段创建索引。一个索引可以包括 16 个字段。对于多列索引，只有查询条件中

使用了这些字段中的第 1 个字段时索引才会被使用。

3. 使用 OR 关键字的查询语句

查询语句的查询条件中只有 OR 关键字，且 OR 前后的两个条件中的列都是索引时，查询中才使用索

引。否则，查询将不使用索引。

优化子查询：

使用子查询可以进行 SELECT 语句的嵌套查询，即一个 SELECT 查询的结果作为另一个 SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL 操作。

子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时， MySQL 需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。在MySQL 中，可以使用连接（ JOIN ）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引，性能会更好。

1.5.3 怎样插入数据才能更高效？

参考答案

影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。针对这些情况，可以分别进行优化。对于MyISAM 引擎的表，常见的优化方法如下：

1. 禁用索引

对于非空表，插入记录时， MySQL 会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引。对于空表批量导入数据，则不需要进行此操作，因为MyISAM 引擎的表是在导入数据之后才建立索引的。

2. 禁用唯一性检查

插入数据时， MySQL 会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启。

3. 使用批量插入

插入多条记录时，可以使用一条 INSERT 语句插入一条记录，也可以使用一条 INSERT 语句插入多条记录。使用一条INSERT 语句插入多条记录的情形如下，而这种方式的插入速度更快。

INSERT INTO fruits VALUES

( 'x1' , '101' , 'mongo2' , '5.7' ) ,

( 'x2' , '101' , 'mongo3' , '5.7' ) ,

( 'x3' , '101' , 'mongo4' , '5.7' ) ;

4. 使用 LOAD DATA INFILE 批量导入

当需要批量导入数据时，如果能用 LOAD DATA INFILE 语句，就尽量使用。因为 LOAD DATA INFILE 语句导入数据的速度比INSERT 语句快。

对于 InnoDB 引擎的表，常见的优化方法如下：

1. 禁用唯一性检查

插入数据之前执行 set unique_checks=0 来禁止对唯一索引的检查，数据导入完成之后再运行

set unique_checks=1 。这个和 MyISAM 引擎的使用方法一样。

2. 禁用外键检查

插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。

3. 禁用自动提交

插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。

1.5.4 表中包含几千万条数据该怎么办？

参考答案

建议按照如下顺序进行优化：

1. 优化 SQL 和索引 ；

2. 增加缓存 ，如 memcached 、 redis ；

3. 读写分离 ，可以采用主从复制，也可以采用主主复制；

4. 使用 MySQL 自带的分区表，这对应用是透明的，无需改代码，但 SQL 语句是要针对分区表做优化的；

5. 做垂直拆分，即根据模块的耦合度，将一个大的系统分为多个小的系统；

6. 做水平拆分，要选择一个合理的 sharding key ，为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql 中尽量带 sharding key ，将数据定位到限定的表上去查，而不是扫描全部的表。

1.5.5 MySQL的慢查询优化有了解吗？

参考答案

优化 MySQL 的慢查询，可以按照如下步骤进行：

开启慢查询日志：

MySQL 中慢查询日志默认是关闭的，可以通过配置文件 my.ini 或者 my.cnf 中的 log-slow-queries 选项打开，也可以在MySQL 服务启动的时候使用 -- log - slow - queries[=file_name] 启动慢查询日志。

启动慢查询日志时，需要在 my.ini 或者 my.cnf 文件中配置 long_query_time 选项指定记录阈值，如果某条查询语句的查询时间超过了这个值，这个查询过程将被记录到慢查询日志文件中。

分析慢查询日志：

直接分析 mysql 慢查询日志，利用 explain 关键字可以模拟优化器执行 SQL 查询语句，来分析 sql 慢查询语句。

常见慢查询优化：

1. 索引没起作用的情况

在使用 LIKE 关键字进行查询的查询语句中，如果匹配字符串的第一个字符为 “%” ，索引不会起

作用。只有 “%” 不在第一个位置，索引才会起作用。

MySQL 可以为多个字段创建索引。一个索引可以包括 16 个字段。对于多列索引，只有查询条

件中使用了这些字段中的第 1 个字段时索引才会被使用。

查询语句的查询条件中只有 OR 关键字，且 OR 前后的两个条件中的列都是索引时，查询中才使

用索引。否则，查询将不使用索引。

2. 优化数据库结构

对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因

为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常

联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查

询效率。

3. 分解关联查询

很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询

结果在应用程序中进行关联，很多场景下这样会更高效。

4. 优化 LIMIT 分页

当偏移量非常大的时候，例如可能是 limit 10000,20 这样的查询，这是 mysql 需要查询 10020 条然后

只返回最后 20 条，前面的 10000 条记录都将被舍弃，这样的代价很高。优化此类查询的一个最简单

的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回

所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

1.5.6 说一说你对explain的了解

参考答案

MySQL 中提供了 EXPLAIN 语句和 DESCRIBE 语句，用来分析查询语句， EXPLAIN 语句的基本语法如下：

EXPLAIN [ EXTENDED ] SELECT select_options

使用 EXTENED 关键字， EXPLAIN 语句将产生附加信息。执行该语句，可以分析 EXPLAIN 后面 SELECT 语句

的执行情况，并且能够分析出所查询表的一些特征。下面对查询结果进行解释：

id ： SELECT 识别符。这是 SELECT 的查询序列号。

select_type ：表示 SELECT 语句的类型。

table ：表示查询的表。

type ：表示表的连接类型。

possible_keys ：给出了 MySQL 在搜索数据记录时可选用的各个索引。

key ：是 MySQL 实际选用的索引。

key_len ：给出索引按字节计算的长度， key_len 数值越小，表示越快。

ref ：给出了关联关系中另一个数据表里的数据列名。

rows ：是 MySQL 在执行这个查询时预计会从这个数据表里读出的数据行的个数。

Extra ：提供了与关联操作有关的信息。

扩展阅读

DESCRIBE 语句的使用方法与 EXPLAIN 语句是一样的，分析结果也是一样的，并且可以缩写成 DESC 。

DESCRIBE 语句的语法形式如下：

DESCRIBE SELECT select_options

4.5.7 explain 关注什么？

参考答案

重点要关注如下几列：

其中，type包含以下几种结果，从上之下依次是最差到最好：

另外， Extra 列需要注意以下的几种情况：

1.6 其他

1.6.1 介绍一下数据库设计的三大范式

参考答案

目前关系数据库有六种范式，一般来说，数据库只需满足第三范式 (3NF ）就行了。

第一范式（ 1NF ）：

是指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。

即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。

第二范式（ 2NF ）：

在 1NF 的基础上，非码属性必须完全依赖于候选码（在 1NF 基础上消除非主属性对主码的部分函数依赖）。第二范式是在第一范式的基础上建立起来的，即满足第二范式必须先满足第一范式。第二范式要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。

例如在员工表中的身份证号码即可实现每个一员工的区分，该身份证号码即为候选键，任何一个候选键都可以被选作主键。在找不到候选键时，可额外增加属性以实现区分，如果在员工关系中，没有对其身份证号进行存储，而姓名可能会在数据库运行的某个时间重复，无法区分出实体时，设计辟如ID 等不重复的编号以实现区分，被添加的编号或ID 选作主键。

第三范式（ 3NF ）：

在 2NF 基础上，任何非主属性不依赖于其它非主属性（在 2NF 基础上消除传递依赖）。

第三范式是第二范式的一个子集，即满足第三范式必须满足第二范式。简而言之，第三范式要求一个关系中不包含已在其它关系已包含的非主关键字信息。

例如，存在一个部门信息表，其中每个部门有部门编号（ dept_id ）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF ）也应该构建它，否则就会有大量的数据冗余。

1.6.2 说一说你对MySQL引擎的了解

参考答案

MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。MySQL 8.0 支持的存储引擎有 InnoDB 、 MyISAM 、 Memory 、 Merge 、 Archive 、 Federated 、 CSV、 BLACKHOLE 等。其中，最常用的引擎是 InnoDB 和 MyISAM 。 InnoDB存储引擎：

InnoDB 是事务型数据库的首选引擎，支持事务安全表（ ACID ），支持行锁定和外键。 MySQL 5.5.5 之后，InnoDB 作为默认存储引擎，主要特性如下：

1. InnoDB 给 MySQL 提供了具有提交、回滚和崩溃恢复能力的事务安全（ ACID 兼容）存储引擎。

InnoDB 锁定在行级并且也在 SELECT 语句中提供一个类似 Oracle 的非锁定读。这些功能增加了多用户部署和性能。在SQL 查询中，可以自由地将 InnoDB 类型的表与其他 MySQL 表的类型混合起来，甚至在同一个查询中也可以混合。

2. InnoDB 是为处理巨大数据量的最大性能设计。它的 CPU 效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。

3. InnoDB 存储引擎完全与 MySQL 服务器整合，为在主内存中缓存数据和索引而维持它自己的缓冲

池。 InnoDB 将它的表和索引存在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘分

区）。这与 MyISAM 表不同，比如在 MyISAM 表中每个表被存在分离的文件中。 InnoDB 表可以是任

何尺寸，即使在文件尺寸被限制为 2GB 的操作系统上。

4. InnoDB 支持外键完整性约束（ FOREIGN KEY ）。存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB 会为每一行生成一个 6B 的 ROWID ，并以此作为主键。

5. InnoDB 被用在众多需要高性能的大型数据库站点上。 InnoDB 不创建目录，使用 InnoDB 时，

MySQL 将在数据目录下创建一个名为 ibdata1 的 10MB 大小的自动扩展数据文件，以及两个名为

ib_logfile0 和 ib_logfile1 的 5MB 大小的日志文件。

MyISAM 存储引擎：

MyISAM 基于 ISAM 存储引擎，并对其进行扩展。它是在 Web 、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。 MyISAM 的主要特性如下：

1. 在支持大文件（达 63 位文件长度）的文件系统和操作系统上被支持。

2. 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块以及若下一个块被删除则扩展到下一块来自动完成。

3. 每个 MyISAM 表最大的索引数是 64 ，这可以通过重新编译来改变。每个索引最大的列数是 16 个。

4. 最大的键长度是 1000B ，这也可以通过编译来改变。对于键长度超过 250B 的情况，一个超过 1024B

的键将被用上。

5. BLOB 和 TEXT 列可以被索引。

6. NULL 值被允许在索引的列中，这个值占每个键的 0~1 个字节。

7. 所有数字键值以高字节优先被存储，以允许一个更高的索引压缩。

8. 每个表一个 AUTO_INCREMENT 列的内部处理。 MyISAM 为 INSERT 和 UPDATE 操作自动更新这一列，这使得AUTO_INCREMENT 列更快（至少 10% ）。在序列顶的值被删除之后就不能再利用。

9. 可以把数据文件和索引文件放在不同目录。

10. 每个字符列可以有不同的字符集。

11. 有 VARCHAR 的表可以固定或动态记录长度。

12. VARCHAR 和 CHAR 列可以多达 64KB

1.6.3 说一说你对redo log、undo log、binlog的了解

参考答案

binlog （ Binary Log ）：

二进制日志文件就是常说的 binlog 。二进制日志记录了 MySQL 所有修改数据库的操作，然后以二进制的形式记录在日志文件中，其中还包括每条语句所执行的时间和所消耗的资源，以及相关的事务信息。默认情况下，二进制日志功能是开启的，启动时可以重新配置 --log - bin[=file_name] 选项，修改二进制日志存放的目录和文件名称。

redo log ：

重做日志用来实现事务的持久性，即事务 ACID 中的 D 。它由两部分组成：一是内存中的重做日志缓冲（redo log buffer ），其是易失的；二是重做日志文件（ redo log file ），它是持久的。

InnoDB 是事务的存储引擎，它通过 Force Log at Commit 机制实现事务的持久性，即当事务提交

（ COMMIT ）时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的 COMMIT 操作完成才算完成。这里的日志是指重做日志，在InnoDB 存储引擎中，由两部分组成，即 redo log 和 undo log。 redo log用来保证事务的持久性， undo log 用来帮助事务回滚及 MVCC 的功能。 redo log 基本上都是顺序写的，在数据库运行时不需要对redo log 的文件进行读取操作。而 undo log 是需要进行随机读写的。

undo log ：

重做日志记录了事务的行为，可以很好地通过其对页进行 “ 重做 ” 操作。但是事务有时还需要进行回滚操作，这时就需要undo 。因此在对数据库进行修改时， InnoDB 存储引擎不但会产生 redo ，还会产生一定量的undo 。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条 ROLLBACK 语句请求回滚，就可以利用这些undo 信息将数据回滚到修改之前的样子。

redo 存放在重做日志文件中，与 redo 不同， undo 存放在数据库内部的一个特殊段（ segment ）中，这个段称为undo 段（ undo segment ）， undo 段位于共享表空间内。

1.6.4 谈谈你对MVCC的了解

参考答案

InnoDB 默认的隔离级别是 RR （ REPEATABLE READ ）， RR 解决脏读、不可重复读、幻读等问题，使用

的是 MVCC 。 MVCC 全称 Multi-Version Concurrency Control ，即多版本的并发控制协议。它最大的优

点是读不加锁，因此读写不冲突，并发性能好。 InnoDB 实现 MVCC ，多个版本的数据可以共存，主要基

于以下技术及数据结构：

1. 隐藏列： InnoDB 中每行数据都有隐藏列，隐藏列中包含了本行数据的事务 id 、指向 undo log 的指

针等。

2. 基于 undo log 的版本链：每行数据的隐藏列中包含了指向 undo log 的指针，而每条 undo log 也会

指向更早版本的 undo log ，从而形成一条版本链。

3. ReadView ：通过隐藏列和版本链， MySQL 可以将数据恢复到指定版本。但是具体要恢复到哪个版

本，则需要根据 ReadView 来确定。所谓 ReadView ，是指事务（记做事务 A ）在某一时刻给整个事

务系统（ trx_sys ）打快照，之后再进行读操作时，会将读取到的数据中的事务 id 与 trx_sys 快照比

较，从而判断数据对该 ReadView 是否可见，即对事务 A 是否可见。

1.6.5 MySQL主从同步是如何实现的？

参考答案

复制（ replication ）是 MySQL 数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。总体来说，replication 的工作原理分为以下 3 个步骤：

1. 主服务器（ master ）把数据更改记录到二进制日志（ binlog ）中。

2. 从服务器（ slave ）把主服务器的二进制日志复制到自己的中继日志（ relay log ）中。

3. 从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

复制的工作原理并不复杂，其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是，复制不是完全实时地进行同步，而是异步实时。这中间存在主从服务器之间的执行延时，如果主服务器的压力很大，则可能导致主从服务器延时较大。复制的工作原理如下图所示，其中从服务器有2 个线程，一个是 I/O 线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL 线程，复制执行中继日志。