MySQL连环炮，你扛得住嘛？

为了节省大家的时间，阿Q决定帮大家先将 MySQL 面试相关的知识进行汇总整理，一改往日知识点东一棒槌西一榔头的问题。那么，你准备好迎接 MySQL 连环炮了嘛？

• 1、三大范式

• 2、DML 语句和 DDL 语句区别

• 3、主键和外键的区别

• 4、drop、delete、truncate 区别

• 5、基础架构

• 6、MyISAM 和 InnoDB 有什么区别？

• 7、推荐自增id作为主键问题

• 8、为什么 MySQL 的自增主键不连续

• 9、redo log 是做什么的?

• 10、redo log 的刷盘时机

• 11、redo log 是怎么记录日志的

• 12、什么是 binlog

• 13、binlog 记录格式

• 14、binlog 写入机制

• 15、redolog 和 binlog 的区别是什么

• 16、两阶段提交

• 17、什么是 undo log.

• 18、什么是 relaylog

• 19、索引

• 20、Hash 索引

• 21、B树和B+ 树

• 22、主键索引

• 23、二级索引

• 24、聚簇索引与非聚簇索引

• 25、回表

• 26、覆盖索引和联合索引

• 27、最左前缀匹配原则

• 28、索引下推

• 29、隐式转换

• 30、普通索引和唯一索引该怎么选择?

• 31、避免索引失效

• 32、建立索引的规则

• 33、事务极其特性

• 34、并发事务带来的问题

• 35、事务的隔离级别

• 36、MVCC

• 37、Mysql 中的锁

• 38、查询语句执行过程

• 39、更新语句执行过程

• 40、sql 优化

• 41、主从同步数据

• 42、主从延迟要怎么解决

• 43、为什么不要使用长事务

1、三大范式

1NF(第一范式)：属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了。1NF 是所有关系型数据库的最基本要求 ，也就是说关系型数据库中创建的表一定满足第一范式。

2NF(第二范式)：2NF 要求数据库表中的每个实例或行必须可以被惟一地区分，2NF 在 1NF 的基础上增加了一个列，这个列称为主键，非主属性都依赖于主键。

3NF(第三范式)：3NF 在 2NF 的基础之上，要求每列都和主键列直接相关，而不是间接相关，即不存在其他表的非主键信息。

在开发过程中，并不一定要满足三大范式，有时候为了提高查询效率，可以在表中冗余其他表的字段。

2、DML 语句和 DDL 语句区别

• DML 是数据库操作语言（Data Manipulation Language）的缩写，是指对数据库中表记录的操作，主要包括表记录的插入、更新、删除和查询，是开发人员日常使用最频繁的操作。
• DDL （Data Definition Language）是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员（DBA）所使用，一般的开发人员很少使用。

3、主键和外键的区别

• 主键：用于唯一标识一行数据，不能有重复，不允许为空，且一个表只能有一个主键；
• 外键：用来和其他表建立联系，外键是另一表的主键，外键是可以有重复的，可以是空值。一个表可以有多个外键；

4、drop、delete、truncate 区别

（1）用法不同

• drop(丢弃数据): drop table 表名 ，直接将表结构都删除掉，在删除表的时候使用。
• truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。
• delete（删除数据） : delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和 truncate table 表名作用类似。

（2）属于不同的数据库语言

• truncate 和 drop 属于 DDL(数据定义语言)语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。
• delete 语句是 DML (数据库操作语言)语句，这个操作会放到 rollback segment 中，事务提交之后才生效。

（3）执行速度不同

• delete命令执行的时候会产生数据库的 binlog日志，而日志记录是需要消耗时间的，但是也有个好处方便数据回滚恢复。
• truncate命令执行的时候不会产生数据库日志，因此比 delete要快。除此之外，还会把表的自增值重置和索引恢复到初始大小等。
• drop命令会把表占用的空间全部释放掉。

一般来说：drop > truncate > delete

5、基础架构

下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到客户端的一条 SQL 语句在 MySQL 内部是如何执行的。

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行。
• 执行器： 执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
• 插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

6、MyISAM 和 InnoDB 有什么区别？

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎，MySQL 5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

（1）是否支持行级锁

MyISAM 只有表级锁，而 InnoDB 支持行级锁和表级锁，默认为行级锁。

具体的锁详情请参考阿Q的此篇文章：面试必备常见存储引擎与锁的分类，请查收

（2）是否支持事务

MyISAM 不提供事务支持，InnoDB 提供事务支持，实现了 SQL 标准定义的四个隔离级别，具有提交和回滚事务的能力。

InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）。

关于 MySQL 事务以及解决幻读的详细介绍，可以看看阿Q写的这篇文章：InnoDB 解决幻读的方案--LBCC&MVCC

（3）是否支持外键

MyISAM 不支持，而 InnoDB 支持。

（4）是否支持数据库异常崩溃后的安全恢复

MyISAM 不支持，而 InnoDB 支持。使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

（5）是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。

（6）索引实现

虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

• InnoDB 引擎中，其数据文件本身就是索引文件。其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶子节点 data 域保存了完整的数据记录。
• MyISAM 索引文件和数据文件是分离的，索引保存的是数据文件的指针。

（7）性能差别

InnoDB 的性能比 MyISAM 更强大，不管是在读写混合模式下还是只读模式下，随着 CPU 核数的增加，InnoDB 的读写能力呈线性增长。MyISAM 因为读写不能并发，它的处理能力跟核数没关系。

7、推荐自增id作为主键问题

• 普通索引的 B+ 树上存放的是主键索引的值，如果该值较大，会 「导致普通索引的存储空间较大」
• 使用自增 id 做主键索引新插入数据只要放在该页的最尾端就可以，直接 「按照顺序插入」，不用刻意维护
• 页分裂容易维护，当插入数据的当前页快满时，会发生页分裂的现象，如果主键索引不为自增 id，那么数据就可能从页的中间插入，页的数据会频繁的变动， 「导致页分裂维护成本较高」

8、为什么 MySQL 的自增主键不连续

• 在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化；
• 唯一键冲突：插入数据时先将自增主键+1，然后插入数据时唯一键冲突，插入数据失败，但是未将自增主键改回；
• 事务回滚：和唯一键冲突类似，回滚操作时自增值也不回退，事实上，这么做的主要原因是为了提高性能。

9、redo log 是做什么的?

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。

比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

更新表数据的时候，如果发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

10、redo log 的刷盘时机

• 红色部分为 redo log buffer 属于内存
• 黄色部分为 page cache ，此时已经写入磁盘了，但是未进行持久化
• 绿色部分是硬盘，已经完成持久化

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略

• 设置为0的时候，表示每次事务提交时 不进行刷盘操作，只是保留在 redo log buffer中，mysql 崩溃会丢失1s的数据；
• 设置为1的时候，表示每次事务提交时 都将进行刷盘操作（默认值），持久化到磁盘；
• 设置为2的时候，表示每次事务提交时都只把redo log buffer内容写入 page cache，OS宕机会丢失1s的数据，因为未进行持久化；

innodb_flush_log_at_trx_commit 参数默认为 1 ，也就是说当事务提交时会调用 fsync(同步操作) 对 redo log 进行刷盘。

另外 InnoDB 存储引擎有一个后台线程，每隔1秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。

redo log buffer占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。

11、redo log 是怎么记录日志的

硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。

比如可以配置为一组4个文件，每个文件的大小是 1GB，整个 redo log 日志文件组可以记录4G的内容。

它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写，如下图所示。

所以，如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中，那么就会发生「内存抖动」现象，从肉眼的角度来观察会发现 mysql 会宕机一会儿，此时就是正在刷盘了。

12、什么是 binlog

binlog 是归档日志，属于 Server 层的日志，是一个二进制格式的文件，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”。

不管用什么存储引擎，只要发生了表数据更新，都会产生