MySQL数据库 - 面试宝典

又到了金三银四、金九银十的时候了，是时候收藏一波面试题了，面试题可以不学，但不能没有！🥁🥁🥁

一个合格的计算机打工人，收藏夹里必须有一份MySQL 八股文 面试题，特别是即将找工作的计算机人，希望本篇博客对你有帮助！

祝各位计算机人都可以在就业季里，顺利通过面试，找到钱多事少离家近的满意工作😀😀😀

面试题系列博客导航🚥🚥🚥：

🥬JavaSE 八股文 面试宝典🥕MySQL 八股文 面试宝典⇦当前位置🪂🥪Spring、MyBatis、SpringMVC 八股文 面试宝典🍊SpringBoot框架 八股文 面试宝典🍒未完待续 ...🎨如果有一天你厌倦上班、厌倦996，想要考研备考的话：408 全套初复试笔记汇总 传送门 🏃‍🏃‍🏃‍

后续随着自己的学习，也会陆续补充新的面试题，如果对大家起到帮助的话，求赞👍 、求收藏 👏、求关注！👀

第1章 数据库基础

01. 简述数据库三大范式

​ 第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

​ 数据库第二范式：关系模式必须满足第一范式，并且所有非主属性都完全依赖于主码。注意，符合第二范式的关系模型可能还存在数据冗余、更新异常等问题。关系模型（学号，姓名，专业编号，专业名称）中，学号->姓名，而专业编号->专业名称，不满足数据库第二范式

​数据库第三范式：关系模型满足第二范式，所有非主属性对任何候选关键字都不存在传递依赖。

即每个属性都跟主键有直接关系而不是间接关系。

接着以学生表举例，对于关系模型（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）院校地址，院校电话和学号不存在直接关系，因此不满足第三范式。

02. 简述MySQL的架构

MySQL可以分为应用层、逻辑层、数据库引擎层、物理层。

应用层：负责和客户端，响应客户端请求，建立连接，返回数据。逻辑层：包括SQK接口，解析器，优化器，Cache与buffer。数据库引擎层：有常见的MyISAM,InnoDB等等。物理层：负责文件存储，日志等等。

03. 简述执行SQL语言的过程

客户端首先通过连接器进行身份认证和权限相关如果是执行查询语句的时候，会先查询缓存，但MySQL 8.0 版本后该步骤移除。没有命中缓存的话，SQL 语句就会经过解析器，分析语句，包括语法检查等等。通过优化器，将用户的SQL语句按照 MySQL 认为最优的方案去执行。执行语句，并从存储引擎返回数据。

第2章 存储引擎及数据类型

2.1 存储引擎的概念

存储引擎其实就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法 。

相关的sql语句

// 1. 查询支持的全部存储引擎SHOW ENGINES;// 2. 查询默认的存储引擎SHOW VARIABLES LIKE 'storage_engine%';

MySQL支持的存储引擎

InnoDB (默认)MyISAMMemory

2.2 面试题

01. 简述InnoDB存储引擎

① 定义

​ InnoDB 是 MySQL 的默认事务型引擎，对事务处理的能力优于其它存储引擎。

② 特点

支持外键约束；支持表级锁、行级锁；采用MVCC来支持高并发，并且实现了四个隔离级别(默认级别：可重复读)；间隙锁策略防止幻读，间隙锁使 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定防止幻行的插入。

③ 分析

​ 优点：事务管理、崩溃恢复能力、并发控制。

是唯一支持ACID事务的标准 MySQL 存储引擎；能自动从灾难中恢复；适合多重并发的更新请求；

​ 缺点：读写效率稍差，占用数据空间较大。

02. 简述Memory存储引擎

① 定义

​ Memory存储引擎将所有数据都保存在内存，不需要磁盘 IO。所以生命周期很短，一般为一次性。

② 特点

文件存储形式：一张 Memory 表对应一个磁盘文件 (文件名与表名相同)；索引类型：默认适用哈希索引，速度比B树索引快；锁：Memory 表使用表级锁，因此并发写入的性能较低；

03. 简述MyISAM存储引擎

① 定义

​ MySQL5.1及之前，MyISAM 是默认存储引擎。

​ 独立于操作系统，当建立一个MyISAM存储引擎的表时，就会在本地磁盘建立三个文件，例如建立tb_demo表：

tb_demo.frm：存储表结构tb_demo.MYD：存储数据tb_demo.MYI：存储索引

② 特点

不支持事务、表不支持外键；Myisam支持表级锁，不支持行级锁；

③ 分析

​ 优点：占用空间小，处理速度快；

​ 缺点：不支持事务的完整性和并发性 (适合查询频繁，不适合对于增删改要求高的情况)；

04. 简述Myisam和Innodb的区别

第3章 索引

3.1 索引的概念

3.2 面试题

01. 索引的基本原理

​ 索引的定义：用于快速找到特定记录的一种数据结构。如果没有索引，执行查询时需要遍历整张表。

​ 索引的原理：把无序的数据变成有序的查询。

​ 索引检索的实现过程：

把创建了索引的列的内容进行排序对排序结果生成倒排表在倒排表内容上拼上数据地址链在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

02. 索引的设计原则

​ 在进行索引设计的时候，应该保证 ==索引字段占用的空间越小越好，==这只是一个大的方向，还有一些：

适合情况：

① 定义有外键的数据列一定要创建索引；② 适合索引的列是出现在where字句中的列，或者连接子句中指定的列；③ 在选择索引列的时候，越短越好，可以指定某些列的一部分，没必要用全部字段的值；

不适合情况：

① 更新频繁的字段不要有索引；② 大文本、大对象不要创建索引；③ 基数较小的表，索引效果差，没必要创建索引；③ 不要给表中的每一个字段都创建索引，并不是索引越多越好；④ 创建索引的列不要过多，可以创建 ==组合索引，==但是组合索引的列的个数不建议太多

03. 索引的类型

04. 索引的数据结构

总：

​ 索引的数据结构和具体存储引擎的实现有关，mysql中使用较多的索引有hash索引，B+树索引，

​innodb存储引擎的索引实现为B+树索引，memory存储引擎的索引实现为hash索引。

分：

​ B+树是一个绝对平衡的多叉排序树，

在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。因此，B+树索引被广泛应用于数据库、文件系统等场景。

​ 哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只

需一次哈希算法即可立刻定位到相应的位置，速度非常快。

​如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值，前提是键值都是唯一的。如果

键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，知道找到对应的数据

​如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，

就没办法再利用索引完成范围查询检索

​ 哈希索引也没办法利用索引完成排序，以及like这样的部分模糊查询

​ 哈希索引也不支持多列联合索引的最左匹配规则

​ B+树索引的关键字检索效率比较平均，不像B树那样波动大，在有大量重复键值情况下，哈希索引的效率也是极低的，因此存在

哈希碰撞问题。

05. Hash索引

​ 哈希索引对于每一行数据计算一个哈希码，并将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

​ 只有 Memory 引擎显式支持哈希索引。

​ Hash索引不支持范围查询，无法用于排序，也不支持部分索引列匹配查找。

06. 自适应Hash索引

​ InnoDB对于频繁使用的某些索引值，会在内存中基于 B-Tree 索引之上再创键一个哈希索引，这也被称为自适应Hash索引。

10. 聚簇、非聚簇索引

​ mysql的索引类型跟存储引擎是相关的，

innodb存储引擎数据文件跟索引文件全部放在ibd文件中，

myisam的数据文件放在myd文件中，索引放在myi文件中，

其实区分聚簇索引和非聚簇索引非常简单，只要判断数据跟索引是否存储在一起就可以了。

​ innodb存储引擎在进行数据插入的时候，数据必须要跟索引放在一起，如果有主键就使用主键，没有主键就使用唯一键，没有唯

一键就使用6字节的rowid，因此跟数据绑定在一起的就是聚簇索引，而为了避免数据冗余存储，其他的索引的叶子节点中存储的都

是聚簇索引的key值，因此innodb中既有聚簇索引也有非聚簇索引，而myisam中只有非聚簇索引。

11. 聚集索引、稀疏索引

​ 聚集索引按每张表的主键构建一棵B+树，数据库中的每个搜索键值都有一个索引记录，每个数据页通过双向链表连接。表数据访问更快，但表更新代价高。

​ 稀疏索引不会为每个搜索关键字创建索引记录。搜索过程需要，我们首先按索引记录进行操作，并按顺序搜索，直到找到所需的数据为止。

12. 辅助索引、回表查询

​ 辅助索引是非聚集索引，叶子节点不包含记录的全部数据，包含了一个书签用来告诉InnoDB哪里可以找到与索引相对应的行数

据。

​ 通过辅助索引查询，先通过书签查到聚集索引，再根据聚集索引查对应的值，需要两次，也称为回表查询。

13. 联合索引、最左匹配原则

​ 联合索引是指对表上的多个列的关键词进行索引。

​ 对于联合索引的查询，如果精确匹配联合索引的左边连续一列或者多列，则mysql会一直向右匹配直到遇到范围查询（>,

<,between,like）就停止匹配。Mysql会对第一个索引字段数据进行排序，在第一个字段基础上，再对第二个字段排序。

14. 覆盖索引

​ 覆盖索引指一个索引包含或覆盖了所有需要查询的字段的值，不需要回表查询，即索引本身存了对应的值。

16. 主键索引、非主键索引查询

​ 对于select * from 主键=XX，基于主键的普通查询仅查找主键这棵树，

​ 对于select * from 非主键=XX，基于非主键的查询有可能存在回表过程（回到主键索引树搜索的过程称为回表），因为非主键索引叶子节点仅存主键值，无整行全部信息。

17. 问：非主键索引的查询一定会回表吗？

​ 不一定，当查询语句的要求字段全部命中索引，不用回表查询。

​ 如select 主键 from 非主键=XX，此时非主键索引叶子节点即可拿到主键信息，不用回表。

第4章 事务

01. 事务四大特征

事务四大特征：原子性，一致性，隔离性和持久性。

① 原子性（Atomicity）

​一个原子事务的所有操作要么都完成，要么都不完成。

​ 这意味着，工作单元中的每项任务都必须正确执行。如果有任一任务执行失败，则整个工作单元或事务就会被终止。

​ 即此前对数据所作的任何修改都将被撤销。

​ 如果所有任务都被成功执行，事务就会被提交，即对数据所作的修改将会是永久性的。

② 一致性（Consistency）

​ ==事务执行前后数据库状态保持一致。==它必须由事务系统和应用开发人员共同来保证：

事务系统通过保证事务的原子性，隔离性和持久性来满足这一要求；

应用开发人员则需要保证数据库有适当的约束(主键，引用完整性等)，并且保证所实现的业务逻辑不会导致数据的不一致；

③ 隔离性（Isolation）

​多个并发事务对数据块进行操作，事务间互不干扰。

​ 换言之，在事务或工作单元执行完毕之前，其所访问的数据不能受系统其他部分的影响。

④ 持久性（Durability）

​事务执行完毕，对数据的修改是永久的，即使系统故障也不会丢失。

​ 持久性表示在某个事务的执行过程中，对数据所作的所有改动都必须在事务成功结束前保存至某种物理存储设备。

​ 这样可以保证，所作的修改在任何系统瘫痪时不至于丢失。

02. ACID靠什么保证？

​ 原子性由undolog日志来保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql；

​ 一致性是由其他三大特性保证，程序代码要保证业务上的一致性；

​ 隔离性是由MVCC来保证；

​ 持久性由redolog日志来保证，mysql修改数据的时候会在redolog中记录一份日志数据，就算数据没有保存成功，只要日志保存成功了，数据仍然不会丢失；

03. SQL的事务隔离级别

​ MySQL定义了四种隔离级别，包括一些具体规则，用于限定事务内外哪些改变是可见的，哪些改变是不可见的。

​ 低级别的隔离一般支持更高的并发处理，并且拥有更低的系统开销。

① REPEATABLE READ 可重复读

​ MySQL数据库默认的隔离级别。该级别解决了READ UNCOMMITTED隔离级别导致的问题。

​ 一个事务执行过程中看到的数据总是和事务启动时看到的数据是一致的。

​ 不过，这会导致另外一个棘手问题“幻读”。InnoDB存储引擎通过多版本并发控制机制解决了幻读问题。

② READ COMMITTED 读取提交内容

​ 大多数数据库系统的默认隔离级别（但是不是MySQL的默认隔离级别）

一个事务开始时，只能“看见”已经提交事务所做的改变，

一个事务从开始到提交前，所做的任何数据改变都是不可见的，除非已经提交。

​ 这种隔离级别也支持所谓的“不可重复读”。这意味着用户运行同一个语句两次，看到的结果是不同的。

③ READ UNCOMMITTED 读取未提交内容

​ 在这个隔离级别，所有事务都可以“看到”未提交事务的执行结果。

​ 读取未提交数据，也被称为“脏读” （读的可能不是最终数据）。

​ 本隔离级别很少用于实际应用，因为它的性能也不必其他性能好多少，而别的级别还有其他更多的优点。

④ SERIALIZABLE 可串行化

​ 该级别是最高级别的隔离级。

​ 它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。

​ 简而言之，SERIALIZABLE是在每个读的数据行上加锁。

​ 在这个级别，可能导致大量的超时Timeout和锁竞争Lock Contention现象，实际应用中很少使用到这个级别，但如果用户的应用为了数据的稳定性，需要强制减少并发的话，也可以选择这种隔离级。

06. 事务并发出现的问题

丢失修改脏读：当前事务可以查看到别的事务未提交的数据。不可重读：在同一事务中，使用相同的查询语句，同一数据资源莫名改变了。幻读：在同一事务中，使用相同的查询语句，莫名多出了一些之前不存在的数据，或莫名少了一些原先存在的数据。

第5章 锁

01. 解决数据库死锁

​ ① 预先检测到死锁的循环依赖，并立即返回一个错误

​ ② 当查询的时间达到锁等待超时的设定后放弃锁请求

02. mysql锁的类型

​基于锁的属性分：共享锁、排他锁。

​基于锁的粒度分类：行级锁（innodb ）、表级锁（ innodb 、myisam）、页级锁（ innodb引擎）、记录锁、间隙锁、临键锁。

​基于锁的状态分类：意向共享锁、意向排它锁。

​共享锁（share lock）： 共享锁又称读锁，简称 S 锁；当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能

对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数

据的时候不支持修改，避免出现重复读的问题。

​排他锁（exclusive lock）：排他锁又称写锁，简称 X 锁；当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，

直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读

取，避免了出现脏数据和脏读的问题。

​表锁（table lock）：表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进

行对表进行访问；特点：粒度大，加锁简单，容易冲突；

​行锁：行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记

录可正常访问，特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高

​记录锁（Record lock）:记录锁也属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁

住的只是表的某一条记录，加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前

被其他事务读取的脏读问题

​页锁：页级锁是 MysQL 中锁定粒度介于行级锁和表级锁中间的一种锁．表级锁速度快，但冲突多，行级冲突少，但速度慢。所

以取了折衷的页级，一次锁定相邻的一组记录。特点：开销和加锁时间界于表锁和行锁之间，会出现死锁；锁定粒度界于表锁和行

锁之间，并发度一般。

​间隙锁：是属于行锁的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个

区间，遵循左开右闭原则。范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ（重

复读）的事务级别中。

​临键锁（Next-Key lock)：也属于行锁的一种，并且它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临

键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。

03. 共享锁、排它锁

​ 共享锁也称为读锁，相互不阻塞，多个客户在同一时刻可以同时读取同一个资源而不相互干扰。

​ 排他锁也称为写锁，会阻塞其他的写锁和读锁，确保在给定时间内只有一个用户能执行写入并防止其他用户读取正在写入的同一

资源。

04. 按粒度的锁分类

​ 表级锁: 对当前操作的整张表加锁,实现简单，加锁快，但并发能力低。

​ 行锁: 锁住某一行，如果表存在索引，那么记录锁是锁在索引上的，如果表没有索引，那么 InnoDB 会创建一个隐藏的聚簇索引

加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

​ Gap 锁：也称为间隙锁: 锁定一个范围但不包括记录本身。其目的是为了防止同一事物的两次当前读出现幻读的情况。

​ Next-key Lock： 行锁+gap锁。

05. 乐观锁和悲观锁

​ 乐观锁：对于数据冲突保持一种乐观态度，操作数据时不会对操作的数据进行加锁，只有到数据提交的时候才通过一种机制来验

证数据是否存在冲突。

​ 悲观锁：对于数据冲突保持一种悲观态度，在修改数据之前把数据锁住，然后再对数据进行读写，在它释放锁之前任何人都不能

对其数据进行操作，直到前面一个人把锁释放后下一个人数据加锁才可对数据进行加锁，然后才可以对数据进行操作，一般数据库

本身锁的机制都是基于悲观锁的机制实现的。

06. MVCC

1、MVCC 多版本并发控制

​ MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

​ 其存在目的是在保证数据一致性的前提下提供一种高并发的访问性能。

​ 对数据读写在不加读写锁的情况下实现互不干扰,从而实现数据库的隔离性，在事务隔离级别为读提交和可重复读中使用到。

2、当前读

​ 像select lock in share mode(共享锁)、select for update、update、insert、delete(排他锁)这些操作都是一种当前读，为什么叫当前读？

​ 就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

3、快照读（提高数据库的并发查询能力）

​ 像不加锁的select操作就是快照读，即不加锁的非阻塞读；

​ 快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；

​ 之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC；

​ 可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；

​ 既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

4、当前读、快照读、MVCC关系

​ MVCC多版本并发控制指的是维持一个数据的多个版本，使得读写操作没有冲突，快照读是MySQL为实现MVCC的一个非阻塞读功能。MVCC模块在MySQL中的具体实现是由三个隐式字段，undo日志、read view三个组件来实现的。

07. MVCC解决的问题

数据库并发场景有三种，分别为：

​ 1、读读：不存在任何问题，也不需要并发控制

​ 2、读写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、幻读、不可重复读

​ 3、写写：有线程安全问题，可能存在更新丢失问题

​ MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务

时间戳关联，读操作只读该事务开始前的数据库的快照，所以MVCC可以为数据库解决一下问题：

​ 1、在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能

​ 2、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题

第6章 慢查询

01. 慢查询的处理办法

1、开启慢查询日志，准确定位到哪个sql语句出现了问题

2、分析sql语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写

3、分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引

4、如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

02. MySQL优化流程

通过慢日志定位执行较慢的SQL语句利用explain对这些关键字段进行分析根据分析结果进行优化

03. EXPLAIN的关键字段

explain关键字用于分析sql语句的执行情况，可以通过他进行sql语句的性能分析。

type：表示连接类型，从好到差的类型排序为

system：系统表，数据已经加载到内存里。const：常量连接，通过索引一次就找到。eq_ref：唯一性索引扫描，返回所有匹配某个单独值的行。ref：非主键非唯一索引等值扫描，const或eq_ref改为普通非唯一索引。range：范围扫描，在索引上扫码特定范围内的值。index：索引树扫描，扫描索引上的全部数据。all：全表扫描。

key：显示MySQL实际决定使用的键。

key_len：显示MySQL决定使用的键长度，长度越短越好

Extra：额外信息

Using filesort：MySQL使用外部的索引排序，很慢需要优化。Using temporary：使用了临时表保存中间结果，很慢需要优化。Using index：使用了覆盖索引。Using where：使用了where。

第7章 日志

01. MySQL的日志log

​ redo log: 存储引擎级别的log（InnoDB有，MyISAM没有），该log关注于事务的恢复.在重启mysql服务的时候，根据redo log进

行重做，从而使事务有持久性。

​ undo log：是存储引擎级别的log（InnoDB有，MyISAM没有）保证数据的原子性，该log保存了事务发生之前的数据的一个版

本，可以用于回滚，是MVCC的重要实现方法之一。

​ bin log：数据库级别的log，关注恢复数据库的数据。

02. redo log、binlog的区别

​ ① redo log 是InnoDB引擎特有的，只记录该引擎中表的修改记录。

​ binlog是MySQL的Server层实现的，会记录所有引擎对数据库的修改。

​ ② redo log是物理日志，记录的是在具体某个数据页上做了什么修改；

​ binlog是逻辑日志，记录的是这个语句的原始逻辑。

​ ③ redo log是循环写的，空间固定会用完；

​ binlog是可以追加写入的，binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

05. crash-safe

​ InnoDB通过redo log保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crashsafe。

06. WAL技术

​ WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

​ 事务在提交写入磁盘前，会先写到redo log里面去。

​ 如果直接写入磁盘涉及磁盘的随机I/O访问，涉及磁盘随机I/O访问是非常消耗时间的一个过程，相比之下先写入redo log，后面再找合适的时机批量刷盘能提升性能。

07. 两阶段提交

为了保证binlog和redo log两份日志的逻辑一致，最终保证恢复到主备数据库的数据是一致的，采用两阶段提交的机制。

执行器调用存储引擎接口，存储引擎将修改更新到内存中后，将修改操作记录redo log中，此时redo log处于prepare状态。存储引擎告知执行器执行完毕，执行器生成这个操作对应的binlog，并把binlog写入磁盘。执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交commit状态，更新完成。

08. 实现主备一致

​ MySQL通过binlog（二进制日志）实现主备一致。

​ binlog记录了所有修改了数据库或可能修改数据库的语句，而不会记录select、show这种不会修改数据库的语句。

​ 在备份的过程中，主库A会有一个专门的线程将主库A的binlog发送给备库B进行备份。其中binlog有三种记录格式：

​ ①statement：记录对数据库进行修改的语句本身，有可能会记录一些额外的相关信息。

优点：binlog日志量少，IO压力小，性能较高。

缺点：由于记录的信息相对较少，在不同库执行时由于上下文的环境不同可能导致主备不一致。

​ ②row：记录对数据库做出修改的语句所影响到的数据行以及对这些行的修改。

​ 比如当修改涉及多行数据，会把涉及的每行数据都记录到binlog。

优点：能够完全的还原或者复制日志被记录时的操作。缺点：日志量占用空间较大，IO压力大，性能消耗较大。

​ ③mixed：混合使用上述两种模式，一般的语句使用statment方式进行保存，

​ 如果遇到一些特殊的函数，则使用row模式进行记录。

​ MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式。

​ 但是在生产环境中，一般会使用row模式。

09. 问：只靠binlog可以数据库崩溃时恢复吗？

不可以。

历史原因：

InnoDB在作为MySQL的插件加入MySQL引擎家族之前，就已经是一个提供了崩溃恢复和事务支持

的引擎了。InnoDB接入了MySQL后，发现既然binlog没有崩溃恢复的能力，那引入InnoDB原有的

redo log来保证崩溃恢复能力。

实现原因：binlog没有记录数据页修改的详细信息，不具备恢复数据页的能力。binlog记录着数据行的增删改，

但是不记录事务对数据页的改动，这样细致的改动只记录在redo log中。当一个事务做增删改时，

其实涉及到的数据页改动非常细致和复杂，包括行的字段改动以及行头部以及数据页头部的改动，

甚至b+tree会因为插入一行而发生若干次页面分裂，那么事务也会把所有这些改动记录下来到redo

log中。因为数据库系统进程crash时刻，磁盘上面页面镜像可以非常混乱，其中有些页面含有一些

正在运行着的事务的改动，而一些已提交的事务的改动并没有刷上磁盘。事务恢复过程可以理解为

是要把没有提交的事务的页面改动都去掉，并把已经提交的事务的页面改动都加上去这样一个过

程。这些信息，都是binlog中没有记录的，只记录在了存储引擎的redo log中。操作写入binlog可细分为write和fsync两个过程，write指的就是指把日志写入到文件系统的page

cache，并没有把数据持久化到磁盘,fsync才是将数据持久化到磁盘的操作。通过参数设置

sync_binlog为0的时候，表示每次提交事务都只write，不fsync。此时数据库崩溃可能导致部分提交

的事务以及binlog日志由于没有持久化而丢失。

第8章 MySQL复制

01. 主从复制

​ MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。

​ MySQL默认采用异步复制方式，可以方便的实现数据的多处自动备份，不仅能增加数据库的安全性，还能进行读写分离，提升数据库负载性能。

主从复制流程：

​ ① 在事务完成之前，主库在binlog上记录这些改变，完成binlog写入过程后，主库通知存储引擎提交事物

​ ② 从库将主库的binlog复制到对应的中继日志，即开辟一个I/O工作线程，

​ I/O线程在主库上打开一个普通的连接，然后开始binlog dump process，将这些事件写入中继日志。

​ 从主库的binlog中读取事件，如果已经读到最新了，线程进入睡眠并等待ma主库产生新的事件。

​ 读写分离：即只在MySQL主库上写，只在MySQL从库上读，以减少数据库压力，提高性能。

02. 主从同步的应用场景

​ 1、在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业

务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

​ 2、做数据的热备

​ 3、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个

机器的I/O性能。

第9章 数据库优化

暂无

金三银四 金九银十 面试宝典 MySQL面试题 超级无敌全的面试题汇总（超万字的面试题 让你的MySQL无可挑剔）