MySQL连环炮，你抗的住嘛？

最近后台好多小伙伴发私信咨询阿Q，问马上就是金九银十的面试黄金期了，该如何在三个月内突击一下，以便更好地通过面试呢？

有想赚点外块|技术交流的朋友，欢迎来撩

阿Q的想法就是需要对自己掌握的知识进行归纳整理，系统的、分类的去复习相关的知识点。这样也能更好的把自己陌生的内容框出来，在暴漏自己短板的同时，更针对性的做准备。

为了节省大家的时间，阿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”。

不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。它的主要作用就是数据备份、主从复制。

binlog会记录所有涉及更新数据的逻辑操作，属于逻辑日志，并且是顺序写。

13、binlog 记录格式

binlog 日志有三种格式，可以通过binlog_format参数指定。

• statement ：记录的内容是SQL语句原文，存在数据一致性问题；
• row：记录包含操作的具体数据，能保证同步数据的一致性；
• mixed：记录的内容是前两者的混合，MySQL会判断这条SQL语句是否可能引起数据不一致：如果是，就用row格式，否则就用statement格式。

14、binlog 写入机制

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

binlog 也提供了 sync_binlog 参数来控制写入 page cache 和磁盘的时机：

• 0：每次提交事务都只写入到文件系统的 page cache，由系统自行判断什么时候执行fsync，机器宕机，page cache里面的 binlog 会丢失。
• 1：每次提交事务都会执行fsync，就如同 redo log 日志刷盘流程 一样。
• N(N>1)：每次提交事务都写入到文件系统的 page cache，但累积N个事务后才fsync。如果机器宕机，会丢失最近N个事务的binlog日志。

15、redolog 和 binlog 的区别是什么

• redolog 是 Innodb 独有的日志，而 binlog 是 server 层的，所有的存储引擎都有使用到；
• redolog 记录了具体的数值，对某个页做了什么修改，binlog 记录的操作内容；
• binlog 大小达到上限或者 flush log 会生成一个新的文件，而 redolog 有固定大小只能循环利用；
• binlog 日志没有 crash-safe 的能力，只能用于归档，而 redo log 有 crash-safe 能力；
• redo log 在事务执行过程中可以不断写入（刷盘设置为1，后台线程1s执行一次或者 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候），而 binlog 只有在提交事务时才写入文件缓存系统；

16、两阶段提交

假设执行 sql 过程中写完 redo log 日志后，binlog 日志写期间发生了异常，会出现什么情况呢？

由于 binlog 没写完就异常，这时候 binlog 里面没有对应的修改记录。因此，之后用 binlog 日志恢复数据时，就会少这一次更新，最终数据不一致。

为了解决两份日志之间的逻辑一致问题，InnoDB 存储引擎使用两阶段提交方案。

将 redo log 的写入拆成了两个步骤 prepare 和 commit，这就是两阶段提交。使用两阶段提交后，写入 binlog 时发生异常也不会有影响，因为 MySQL 根据 redo log日志恢复数据时，发现 redo log 还处于 prepare 阶段，并且没有对应 binlog 日志，就会回滚该事务。

再看一个场景，redo log 设置 commit 阶段发生异常，那会不会回滚事务呢？

并不会回滚事务，虽然 redo log 是处于 prepare 阶段，但是能通过事务id找到对应的 binlog 日志，所以 MySQL 认为是完整的，就会提交事务恢复数据。

17、什么是 undo log

我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作（INSERT、DELETE、UPDATE）进行回滚，在 MySQL 中，恢复机制是通过回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。

每次对记录进行改动都会记录一条 undo log，每条 undo log 也都有一个DB_ROLL_PTR属性，可以将这些 undo log 都连起来，串成一个链表，形成版本链。

版本链的头节点就是当前记录最新的值。

18、什么是 relaylog

relaylog 是中继日志，在主从同步的时候使用到，它是一个中介临时的日志文件，用于存储从 master 节点同步过来的 binlog 日志内容。

master 主节点的 binlog 传到 slave 从节点后，被写入 relay log 里，从节点的 slave sql 线程从 relaylog 里读取日志然后应用到 slave 从节点本地。

从服务器 I/O 线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后 SQL 线程会读取 relay-log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。

19、索引

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据。

索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

20、Hash 索引

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。

但是！哈希算法有个 Hash 冲突问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。

链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后 HashMap 为了减少链表过长的时候搜索时间过长引入了红黑树。

为了减少 Hash 冲突的发生，一个好的哈希函数应该“均匀地”将数据分布在整个可能的哈希值集合中。

既然哈希表这么快，为什么 MySQL 没有使用其作为索引的数据结构呢？  主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了，并且每次 IO 只能取一个。

21、B树和B+ 树

• B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的；B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

22、主键索引

数据表的主键列使用的就是主键索引，一种特殊的唯一索引。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

23、二级索引

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于二级索引。

• 唯一索引(Unique Key) ：唯一索引也是一种约束。索引列的值必须唯一，但允许有空值；如果是组合索引，则列值的组合必须唯一。一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
• 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
• 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
• 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合（后文介绍）；
• 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。

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

聚簇索引即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 的主键索引的叶子节点中存放的就是数据行，所以它属于聚簇索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd 文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

非聚簇索引即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

辅助索引是我们人为创建的索引，它的叶子节点中存放的是主键，当我们通过辅助索引查找到主键之后，再通过查找的主键去回表查找主键索引。

25、回表

回表就是先通过数据库索引扫描出该索引树中数据所在的行，取到主键 id，再通过主键 id 取出主键索引数中的数据，即基于非主键索引的查询需要多扫描一棵索引树。

26、覆盖索引和联合索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。指的是通过索引就能查询到我们所需要的数据，而不需要根据索引再去查询数据表中的数据（ 回表），这样就减少了数据库的 io 操作，提高查询效率。

使用表中的多个字段创建索引，就是联合索引，也叫组合索引或复合索引。

27、最左前缀匹配原则

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如 >、查询缓存—>分析器—>优化器—>权限校验—>执行器—>引擎

39、更新语句执行过程

update tb_student A set A.age='19' where A.name=' 张三 ';

1

这条语句基本上也会沿着上一个查询的流程走，只不过执行更新的时候要记录日志，这就会引入日志模块了，MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），我们就以 InnoDB 模式下来探讨这个语句的执行流程。

• 先查询到张三这一条数据，如果有缓存，也是会用到缓存。
• 然后拿到查询的语句把 age 改为 19，然后调用引擎 API 接口写入这一行数据，InnoDB 引擎把数据保存在内存中同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器执行完成了随时可以提交。
• 执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
• 更新完成。

更新语句执行流程如下：分析器---->权限校验---->执行器—>引擎—redo log(prepare 状态)—> binlog —>redo log(commit状态)

40、sql 优化

41、主从同步数据

• master 主库将此次更新的事件类型写入到主库的 binlog 文件中
• master 创建 log dump 线程通知 slave 需要更新数据
• slave 向 master 节点发送请求，将该 binlog 文件内容存到本地的 relaylog 中
• slave 开启 sql 线程读取 relaylog 中的内容，将其中的内容在本地重新执行一遍，完成主从数据同步

同步策略：

• 全同步复制：主库强制同步日志到从库，等全部从库执行完才返回客户端，性能差；
• 半同步复制：主库收到至少一个从库确认就认为操作成功，从库写入日志成功返回 ack 确认；

42、主从延迟要怎么解决

• MySQL 5.6 版本以后，提供了一种并行复制的方式，通过将 SQL 线程转换为多个 work 线程来进行重放
• 提高机器配置(王道)
• 在业务初期就选择合适的分库、分表策略，避免单表单库过大带来额外的复制压力
• 避免长事务
• 避免让数据库进行各种大量运算
• 对于一些对延迟很敏感的业务直接使用主库读

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

• 并发情况下，数据库连接池容易被撑爆
• 容易造成大量的阻塞和锁超时，长事务还占用锁资源，也可能拖垮整个库
• 执行时间长，容易造成主从延迟
• 回滚所需要的时间比较长，事务越长整个时间段内的事务也就越多
• undolog 日志越来越大，长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

今天的内容到这里就结束了，跪求一键三连，期望靓仔在评论区打出老铁666，鼓励一下阿Q。

好看的皮囊千篇一律，有趣的灵魂万里挑一，让我们在冷漠的城市里相互温暖，我是阿Q，我们下期再见！