MySQL连环炮，你扛得住嘛？

作者：微信小助手

发布时间：2023-10-13T16:01:20

为了节省大家的时间，阿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 内部是如何执行的。

img
  • 连接器： 身份认证和权限相关(登录 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 因为读写不能并发，它的处理能力跟核数没关系。

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”。

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