Checklist系列:MySQL自检五十五问，万字整理，推荐收藏

🚀最近也打算整理一波已经学过的知识，名字已经想好了就叫《CheckList》系列，以后需要用到的时候也可以拿出来看。问题来源于网上常见的面试题，问题的答案多以官网为主，每个问题我都贴了链接，觉得我写的不清楚的地方可以点击链接查看原文。

另外如果有什么问题这上面没有，可以评论，私信，提交issue等等的各种方式，只要我看到都会观看的，满一定程度会出续集。

点赞👍收藏🌟支持一波呗~~~~~~

PS：提交issue地址：https://github.com/Nortyr/monk_note/issues

---

基础

1. 什么是MySQL？

MySQL是开源的关系型数据库，以下是MySQL的一些关键特性

• 关系型数据库：基于关系模型的数据库系统，行列结构
• 支持SQL：SQL是结构化查询语言(Structured Query Language)
• 开源：开源地址:https://github.com/mysql
• 支持事务：支持事务处理，保证了事务的原子性、一致性、隔离性和持久性（ACID）
• 主从复制：支持主从复制
• 支持多种存储引擎：MySQL支持多种存储引擎，例如InnoDB、MyISAM等。

2. 数据库三大范式

• 第一范式（1NF）：表中的字段有原子性，不可以拆分。每个字段的值都只能是单一值。
• 第二范式（2NF）：表中的属性都依赖于主键。
• 第三范式（3NF）：任何非主属性不依赖于其他非主属性

范式的目的是减少冗余，会造成一些问题

• 查询效率下降：增加关联查询和子查询，影响sql执行效率
• 更新复杂度和性能降低：维护多张表，还要考虑一定的关联关系。
  所以，一般是反范式。

3. MySQL基础架构

精简版本：

官网版本：

官网链接：https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage-overview.html

4. SQL语句的执行过程

大致情况如上图

1. 与MySQL建立连接
2. 检查是否开启缓存，如果开启并且命中直接返回
3. 有分析器，进行词法分析，这一步分析是否合法
4. 由优化器生成执行计划。这一步查看是否可以根据索引优化
5. 由执行器执行SQL语句，交给存储引擎执行

5. CHAR 和 VARCHAR 的区别是什么？

• CHAR:
  • 固定长度不足由空格填充
  • 范围：0~255
  • 可以减少磁盘碎片
  • 存取快
• VHARCHAR:
  • 可变长度，会花1~2字节存储实际长度
  • 范围：0~65532
  • 存取慢

官网链接：https://dev.mysql.com/doc/refman/8.0/en/char.html

6. BLOB和TEXT有什么区别？

• BLOB:
  • 二进制存储
  • 没有字符集
• TEXT:
  • 字符存储
  • 有字符集

官网链接：https://dev.mysql.com/doc/refman/8.0/en/blob.html

7. DATETIME 和 TIMESTAMP 的区别是什么？

• DATETIME:
  • 范围：1000-01-01 00:00:00~9999-12-31 23:59:59
  • 时间格式：yyyy-MM-dd HH:mm:ss
  • 时区：存储当地时区
• TIMESTAMP:
  • 范围：1970-01-01 00:00:01UTC~2038-01-19 03:14:07UTC
  • 时间格式：yyyy-MM-dd HH:mm:ss
  • 时区：存储UTC（世界时间）

官网链接：https://dev.mysql.com/doc/refman/8.0/en/datetime.html

8. count(1)、count(*) 与 count(列名) 的区别？

• count(*):
  • 检索到null任然会计算在内
  • 8.0.13以后有单独优化
• count(expr):
  • 例如count(列名)在内，不会计算null
• count(1):
  • 等同于count(*)没有性能差异
  • 对于MyISAM有差异

官网链接：https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_count

9. 常见的存储引擎有哪些？MySQL默认使用的是什么

• 存储引擎：MySQL定义了对于数据CRUD操作标准规范（接口）。存储引擎就是对应的实现，负责与文件系统进行交互
• 比较常见的存储引擎

	InnoDB	MyISAM
事务	Yes	NO
MVCC	Yes	NO
外键	Yes	No
聚簇索引	Yes	no
锁最小粒度	行锁	表锁
清空方式	逐行	重建

官网链接：https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

10. MySQL自增主键用完了会怎么样？

当AUTO_INCREMENT整数列用完值时，得到的值就是最大的值，后续INSERT操作将返回重复键错误。

官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

11 UNION与UNION ALL的区别？

• union：将结果集合并到同一个结果集中，删除重复数据，显示写法为UNION DISTINCT
• union all：不会删除重复数据

官网链接：https://dev.mysql.com/doc/refman/8.0/en/union.html

12. drop、delete与truncate的区别？

• drop
  • 属于DDL
  • 会删除表级表相关的索引，触发器
• delete
  • 属于DML
  • 可选子句中的条件WHERE标识要删除的行。如果没有WHERE 子句，则所有行都将被删除。
• truncate
  • 属于DDL
  • 会删除并重新创建表，比删除行快

官网链接：

• https://dev.mysql.com/doc/refman/8.0/en/drop-tablespace.html
• https://dev.mysql.com/doc/refman/8.0/en/delete.html
• https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html

事务

1. 什么是数据库事务

• 数据库事务：
  • 数据库一个或者多个操作组成的逻辑单位
• 目的（解决的问题）：
  • 失败了可以恢复到正常的状态，即使失败了仍可以恢复到一致性状态
  • 多个client在并发访问到数据库的时候，针对多个client访问提供隔离避免彼此之间互相干扰

官网链接：https://zh.wikipedia.org/wiki/%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BA%8B%E5%8A%A1

2. acid特性？

ACID 模型是一组数据库设计原则。上面提到了事务的需要解决2个问题。而ACID模型是是解决这个问题的基本条件。InnoDB遵循ACID模型，因此数据不会被损坏，结果也不会因软件崩溃和硬件故障等异常情况而失真。

• A(atomicity):事务中的所有操作要么全部成功完成，要么全部失败回滚，不会出现中间状态。
• C(consistency):事务执行后，系统应保持一致状态。如果事务违反了数据库的完整性约束，它将被回滚，以确保系统的一致性。
• I(isolation):隔离性确保多个并发事务之间互相不受影响，即一个事务的执行不应影响其他事务的执行。
• D(durability):一旦事务提交，其结果应该是永久性的，即使在系统故障或重启后也能够保持。

官网链接：https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html

3. 并发事务带来了哪些问题?

• 脏读：读到另一个事务未提交的数据
  • 官网链接：https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_dirty_read
• 不可重复读：两次查询内，返回了不同的数据。主要指修改和删除。快照的版本不一致导致的
  • 官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html
• 幻读：特指第二次读取到第一次没有返回的行
  • 官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-next-key-locking.html

4. 事务隔离级别

• 读未提交(READ UNCOMMITTED):事务可以看到其他事务“尚未提交”的修改。会有脏读的问题
• 读已提交(READ COMMITTED):每次读取会读取最新快照，会读取到其他事务已经提交的记录。可以避免脏读发生
• 可重复度(REPEATABLE READ)：第一次读取会生成快照，在不生成新的快照的前提下，多次读取是一致的
• 串行化(SERIALIZABLE)：Select隐式转化为SELECT … FOR SHARE
  相当于使用锁来解决事务建的问题

官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html#isolevel_serializable

5. 不可重复读和幻读有什么区别？

• 不可重复读：当在事务过程中，一行被检索两次并且该行中的值在两次读取之间不同时，就会发生不可重复读取。
• 幻读：当在事务过程中执行两个相同的查询并且第二个查询返回的行集合与第一个查询不同时，就会发生幻读。

例子：

• 用户 A 运行相同的查询两次。
• 在这期间，用户 B 运行事务并提交。
• 不可重复读：用户A第二次查询到的A行的值不同。
• 幻读：查询中的所有行前后都有相同的值，但正在选择不同的行（因为 B 删除或插入了一些行）。示例：select sum(x) from table;如果已添加或删除行，即使受影响的行本身没有被更新，也会返回不同的结果。

参考链接：https://stackoverflow.com/questions/11043712/non-repeatable-read-vs-phantom-read

6. 不同隔离级别下可能会发生的问题？

隔离级别	脏读	不可重复读	幻读
读未提交(READ UNCOMMITTED)	✅	✅	✅
读已提交(READ COMMITTED)	❎	✅	✅
可重复度(REPEATABLE READ)	❎	❎	✅
串行化(SERIALIZABLE)	❎	❎	❎

7. MySql如何解决RR级别下的幻读

RR级别下解决了大部分的幻读的问题。但是还是会有幻读问题，下面是一点例子

锁解决幻读

事务A	事务B

通过加锁阻塞B事务，这样就肯定不会有幻读了

MVCC解决幻读

事务A	事务B

• 事务提交后查询：
  

MVCC中的幻读场景

事务A	事务B

PS:update test set name='kak' where id >120替换成锁也有一样的效果

• 原因
  官网解释如下：
  
  译文

快照适用于事务内的select语句，不一定适用于DML语句。

其他事务修改或删除并提交，其他事务的修改或删除可能影响到那些行，这些行对于该事务可见

上面
update test set name='kak' where id >120
和
update test set name='kak' where id <120 查询到的结果不同证明了这一点

锁和cud操作会生成最新的ReadView快照

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE 
UPDATE
DELETE
INSERT

官网链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

8. 各个事务隔离级别都是如何实现读取的？

• 读未提交(READ UNCOMMITTED):直接读取最新数据
• 读已提交(READ COMMITTED):每次读取生成一个ReadView
• 可重复度(REPEATABLE READ)：第一次读取会生成ReadView
• 串行化(SERIALIZABLE)：读写锁的方式

9.MVCC是如何实现的？

MVCC(Multi Version Concurrency Control),多版本并发控制。由多个模块共同实现。

1. UNDOLOG：InnoDB使用UNDOLOG进行回滚，也使用这些日志构成版本链。

• UNDOLOG(新增):仅在回滚时用，提交后可以立即丢弃。
• UNDOLOG(修改):还会用于版本连，不存在事务使用到的时候才会丢弃

2. 每一行记录中有DB_TRX_ID(事务id)，DB_ROLL_PTR(回滚指针)，DB_ROW_ID(记录id)
3. ReadView，下面贴了部分源码。主要是由几个字段来的，以下解释来自于注释

• m_low_limit_id:读取不应看到任何 trx id >= m_low_limit_id 的值。换句话说，这就是“高水位线”。
• m_up_limit_id: 读取应该看到 trx id <= m_up_limit_id 的值。换句话说，这就是低水位线”。
• m_creator_trx_id：创建事务的事务id
• m_ids：当前快照还活跃的读写事务
• m_low_limit_no：小于这个值的undolog 可以不用再看了，他们已经可以被回收了

大致逻辑：
先简单的概述下这四个

• m_low_limit_id：高水位，高于这个id的是看不见的
• m_up_limit_id：低水位，低于这个id的当前事务是可以看见的
• m_creator_trx_id：当前事务id
• m_ids：活跃的读写事务id

接下来就是比较事务id了

1. DB_TRX_ID < m_up_limit_id 的可以直接看
2. DB_TRX_ID > m_low_limit_id 看不到，要按版本链找
3. m_up_limit_id < DB_TRX_ID < m_low_limit_id 要在 m_ids 逐一比较
   3.1 如果不在，直接返回
   3.2 如果在，按版本连向上找

下面画图举个栗子

这个是ReadView源码

private