MySQL数据库面试题总结（2022最新版）

🐶 程序猿周周
⌨️ 短视频小厂BUG攻城狮
🤺 如果文章对你有帮助，记得关注、点赞、收藏，一键三连哦，你的支持将成为我最大的动力

本文是《后端面试小册子》系列的第 1️⃣ 篇文章，该系列将整理和梳理笔者作为 Java 后端程序猿在日常工作以及面试中遇到的实际问题，通过这些问题的系统学习，也帮助笔者顺利拿到阿里、字节、华为、快手等多个大厂 Offer，也祝愿大家能够早日斩获自己心仪的 Offer。

PS：《后端面试小册子》已整理成册，目前共十三章节，总计约二十万字，欢迎👏🏻关注公众号【程序猿周周】获取电子版和更多学习资料（最新系列文章也会在此陆续更新）。公众号后台可以回复关键词「电⼦书」可获得这份面试小册子。文中所有内容都会在 Github 开源，项目地址 csnotes，如文中存在错误，欢迎指出。如果觉得文章还对你有所帮助，赶紧点个免费的 star 支持一下吧！

标题地址MySQL数据库面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/122910606

Redis面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/122934938

计算机网络面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/122973684

操作系统面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/122994599

Linux面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/122994862

Spring面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/123016872

Java基础面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/123080189

Java集合面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/123171501

Java并发面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/123266624

Java虚拟机面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/123412605

Java异常面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/123462676

设计模式面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/123490442

Dubbo面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/123538243

Netty面试题总结（2022版）
https://blog.csdn.net/adminpd/article/details/123564362

文章目录

• 1 基础
  *
• 1.1 基本概念
  –
  • 1、MySQL有哪些数据库类型？
  • 2、CHAR 和 VARCHAR 区别？
  • 3、CHAR 和 VARCHAR 如何选择？
  • 4、CHAR，VARCHAR 和 Text 的区别？
• 1.2 数据库设计
  –
  • 1、什么是三大范式？
  • 2、什么是范式和反范式，以及各自优缺点？
• 2 索引
  *
  –
  • 1、索引的几种类型或分类？
  • 2、索引的优缺点？
  • 3、索引设计原则？
  • 4、索引的数据结构？
  • 5、Hash 和 B+ 树索引的区别？
  • 6、为何使用 B+ 树而非二叉查找树做索引？
  • 7、为何使用 B+ 树而非 B 树做索引？
  • 8、什么是最左匹配原则？
  • 9、什么是覆盖索引？
  • 10、什么是索引下推？
• 3 存储
  *
• 3.1 存储引擎
  –
  • 1、有哪些常见的存储引擎？
  • 2、MyISAM 和 InnoDB 的区别？
  • 3、InnoDB 的四大特性?
  • 4、InnoDB 为何推荐使用自增主键？
  • 5、如何选择存储引擎？
• 3.2 存储结构
  –
  • 1、什么是 InnoDB 的页、区、段？
  • 2、页由哪些数据组成？
  • 3、页中插入记录的过程？
  • 4、什么是 Buffer Pool？
  • 5、什么是 Change Buffer？
• 3.3 InnoDB
  –
  • 1、InnoDB 架构设计？
  • 2、InnoDB 有哪些线程？
  • 3、什么是 doublewrite？
  • 4、什么是自适应哈希？
• 4 事务
  *
  –
  • 1、什么是数据库的事务？
  • 2、什么是事务的四大特性（ACID）？
  • 4、事务的并发问题？
  • 5、什么是脏读、幻读和不可重复度？
  • 6、事务的隔离级别有哪些？
  • 7、ACID 特性是如何实现的？
• 5 锁
  *
  –
  • 1、数据库锁的作用以及有哪些锁？
  • 2、隔离级别和锁的关系？
  • 3、InnoDB 中的锁算法？
  • 4、什么是快照读和当前读？
  • 5、什么是 MVCC 以及实现？
• 6 进阶功能
  *
• 6.1 视图
• 6.2 存储过程
  –
  • 1、什么是存储过程？
  • 2、存储过程和函数的区别？
• 6.3 触发器
• 7、集群
  *
• 7.1 日志
  –
  • 1、MySQL 中有哪些常见日志？
• 7.2 主从复制
  –
  • 1、什么是主从复制？
  • 2、主从复制的作用？
  • 3、主从复制的架构？
  • 4、主从复制的实现原理？
  • 5、什么是异步复制和半同步？
  • 6、主从中常见问题以及解决？
• 8 SQL
  *
• 8.1 语法
  –
  • 1、常见的聚合查询？
  • 2、几种关联查询？
  • 3、Where 和 Having 的区别？
  • 4、SQL 关键字的执行顺序？
  • 5、In 和 Exists 的区别？
  • 6、Union 和 Union All 的区别？
  • 7、Drop、Delete 和 Truncate 的区别？
• 8.2 优化
  –
  • 1、一条 SQL 是如何执行的？
  • 2、如何判断 SQL 是否走了索引？
  • 3、索引失效的几种情况？
  • 4、Where 子句如何优化？
  • 5、超大分页或深度分页如何处理？
  • 6、大表查询如何优化？
• 8.3 实践
  –
  • 几种常见名次问题

; 1 基础

1.1 基本概念

1、MySQL有哪些数据库类型？

• *数值类型

有包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示 1 字节、2 字节、3 字节、4 字节、8 字节的 整数类型。

1）任何整数类型都可以加上 UNSIGNED 属性，表示无符号整数。

2）任何整数类型都可以指定长度，但它不会限制数据的合法长度，仅仅限制了显示长度。

还有包括 FLOAT、DOUBLE、DECIMAL 在内的 小数类型。

• *字符串类型

包括 VARCHAR、CHAR、TEXT、BLOB。

注意：VARCHAR(n) 和 CHAR(n) 中的 n 并不代表字节个数，而是代表字符的个数。

• *日期和时间类型

常用于表示日期和时间类型为 DATETIME、DATE 和 TIMESTAMP。

尽量使用 TIMESTAMP，空间效率高于 DATETIME。

ref MySQL 数据类型

2、CHAR 和 VARCHAR 区别？

1）首先可以明确的是 CHAR 是定长的，而 VARCHAR 是可以变长。

CHAR 会根据声明的字符串长度分配空间，并会使用空格对字符串右边进行尾部填充。所以在检索 CHAR 类型数据时尾部空格会被删除，如保存的是字符串 'char '，但最后查询到的是 'char'。又因为长度固定，所以存储效率高于 VARCHAR 类型。

VARCHAR 在 MySQL 5.0 之后长度支持到 65535 字节， 但会在数据开头使用额外 1~2 个字节存储字符串长度（列长度小于 255 字节时使用 1 字节表示，否则 2 字节），在结尾使用 1 字节表示字符串结束。

2）再者，在存储方式上，CHAR 对英文字符（ASCII）占用 1 字节，对一个汉字使用用 2 字节。而 VARCHAR 对每个字符均使用 2 字节。

虽然 VARCHAR 是根据字符串长度分配存储空间的，但在内存中依旧使用声明长度进行排序等作业，故在使用时仍需综合考量字段长度。

3、CHAR 和 VARCHAR 如何选择？

1）对于经常变更的数据来说，CHAR 比 VARCHAR更好，因为 CHAR 不容易产生碎片。

2）对于非常短的列或固定长度的数据（如 MD5），CHAR 比 VARCHAR 在存储空间上更有效率。

4）使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。

4）尽量避免使用 TEXT/BLOB 类型，查询时会使用临时表，导致严重的性能开销。

4、CHAR，VARCHAR 和 Text 的区别？

1） 长度区别

• Char 范围是 0～255。
• Varchar 最长是 64k（注意这里的 64k 是整个 row 的长度，要考虑到其它的 column，还有如果存在 not null 的时候也会占用一位，对不同的字符集，有效长度还不一样，比如 utf-8 的，最多 21845，还要除去别的column），但 Varchar 在一般情况下存储都够用了。
• 如果遇到了大文本，考虑使用 Text，最大能到 4G（其中 TEXT 长度 65,535 bytes，约 64kb；MEDIUMTEXT 长度 16,777,215 bytes，约 16 Mb；而 LONGTEXT 长度 4,294,967,295 bytes，约 4Gb）。

2） 效率区别

效率来说基本是 Char > Varchar > Text，但是如果使用的是 Innodb 引擎的话，推荐使用 Varchar 代替 Char。

3） 默认值区别

Char 和 Varchar 支持设置默认值，而 Text 不能指定默认值。

1.2 数据库设计

1、什么是三大范式？

• 第一范式（1NF）：字段（或属性）是不可分割的最小单元，即不会有重复的列，体现原子性
• 第二范式（2NF）：满足 1NF 前提下，存在一个候选码，非主属性全部依赖该候选码，即存在主键，体现唯一性，专业术语则是 消除部分函数依赖
• 第三范式（3NF）：满足 2NF 前提下，非主属性必须互不依赖， *消除传递依赖

ref：如何理解关系型数据库的常见设计范式？

除了三大范式外，还有 BC范式和 第四范式，但其规范过于严苛，在生产中往往使用不到。

2、什么是范式和反范式，以及各自优缺点？

范式是符合某一种级别的关系模式的集合。构造数据库必须遵循一定的规则。在关系数据库中，这种规则就是范式。

名称优点缺点范式范式化的表减少了数据冗余，数据表更新操作快、占用存储空间少。查询时通常需要多表关联查询，更难进行索引优化反范式反范式的过程就是通过冗余数据来提高查询性能，可以减少表关联和更好进行索引优化存在大量冗余数据，并且数据的维护成本更高

所以在平时工作中，我们通常是将范式和反范式相互结合使用。

2 索引

首先了解一下什么是索引， 索引是对数据库表中一列或多列的值进行排序的数据结构，用于快速访问数据库表中的特定信息。

1、索引的几种类型或分类？

1）从 物理结构上可以分为聚集索引和非聚集索引两类：

• 聚簇索引指索引的键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引，也就是我们常说的 主键索引；
• 非聚簇索引的逻辑顺序则与数据行的物理顺序不一致。

2）从 应用上可以划分为一下几类：

• 普通索引：MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了提高查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建；
• 唯一索引：索引列中的值必须是唯一的，但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建；
• 主键索引：特殊的唯一索引，也成聚簇索引，不允许有空值，并由数据库帮我们自动创建；
• 组合索引：组合表中多个字段创建的索引，遵守最左前缀匹配规则；
• 全文索引：只有在 MyISAM 引擎上才能使用，同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。

2、索引的优缺点？

先来说说 优点：创建索引可以大大提高系统的性能。

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

既然增加索引有如此多的优点，为什么不对表中的每一个列都创建一个索引呢？这是因为索引也是有 缺点的：

• 创建和维护索引需要耗费时间，这种时间随着数据量的增加而增加，这样就降低了数据的维护速度。
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

3、索引设计原则？

• 选择唯一性索引；

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

• 为常作为查询条件的字段建立索引；

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

• 为经常需要排序、分组和联合操作的字段建立索引；

经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

• 限制索引的数目；

每个索引都需要占⽤用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦。

• 小表不建议索引（如数量级在百万以内）；

由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

• 尽量使用数据量少的索引；

如果索引的值很长，那么查询的速度会受到影响。此时 尽量使用前缀索引。

• 删除不再使用或者很少使用的索引。

4、索引的数据结构？

索引的数据结构和具体存储引擎的实现有关，MySQL 中常用的是 Hash 和 B+ 树索引。

• Hash 索引底层就是 Hash 表，进行查询时调用 Hash 函数获取到相应的键值（对应地址），然后回表查询获得实际数据.

• B+ 树索引底层实现原理是多路平衡查找树，对于每一次的查询都是从根节点出发，查询到叶子节点方可以获得所查键值，最后查询判断是否需要回表查询.

5、Hash 和 B+ 树索引的区别？

Hash
1）Hash 进行等值查询更快，但无法进行范围查询。因为经过 Hash 函数建立索引之后，索引的顺序与原顺序无法保持一致，故不能支持范围查询。同理，也不支持使用索引进行排序。

2）Hash 不支持模糊查询以及多列索引的最左前缀匹配,因为 Hash 函数的值不可预测，如 AA 和 AB 的算出的值没有相关性。

3）Hash 任何时候都避免不了回表查询数据.

4）虽然在等值上查询效率高，但性能不稳定，因为当某个键值存在大量重复时，产生 Hash 碰撞，此时查询效率反而可能降低。

B+ Tree

1）B+ 树本质是一棵查找树，自然支持范围查询和排序。

2）在符合某些条件（聚簇索引、覆盖索引等）时候可以只通过索引完成查询，不需要回表。

3）查询效率比较稳定，因为每次查询都是从根节点到叶子节点，且为树的高度。

6、为何使用 B+ 树而非二叉查找树做索引？

我们知道二叉树的查找效率为 O(logn)，当树过高时，查找效率会下降。另外由于我们的索引文件并不小，所以是存储在磁盘上的。

文件系统需要从磁盘读取数据时，一般以页为单位进行读取，假设一个页内的数据过少，那么操作系统就需要读取更多的页，涉及磁盘随机 I/O 访问的次数就更多。将数据从磁盘读入内存涉及随机 I/O 的访问，是数据库里面成本最高的操作之一。

因而这种树高会随数据量增多急剧增加，每次更新数据又需要通过左旋和右旋维护平衡的二叉树，不太适合用于存储在磁盘上的索引文件。

7、为何使用 B+ 树而非 B 树做索引？

在此之前，先来了解一下 B+ 树和 B 树的区别：

• B 树非叶子结点和叶子结点都存储数据，因此查询数据时，时间复杂度最好为 O(1)，最坏为 O(log n)。而 B+ 树只在叶子结点存储数据，非叶子结点存储关键字，且不同非叶子结点的关键字可能重复，因此查询数据时，时间复杂度固定为 O(log n)。
• B+ 树叶子结点之间用链表相互连接，因而只需扫描叶子结点的链表就可以完成一次遍历操作，B 树只能通过中序遍历。

为什么 B+ 树比 B 树更适合应用于数据库索引？

• B+ 树减少了 IO 次数。

由于索引文件很大因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。

• *B+ 树查询效率更稳定

由于数据只存在在叶子结点上，所以查找效率固定为 O(log n)，所以 B+ 树的查询效率相比B树更加稳定。

• *B+ 树更加适合范围查找

B+ 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+ 树的效率更高。

ref 为什么 B+ 树比 B 树更适合应用于数据库索引？

8、什么是最左匹配原则？

顾名思义，最左优先，以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询（>、

Original: https://blog.csdn.net/adminpd/article/details/122910606
Author: 程序猿周周
Title: MySQL数据库面试题总结（2022最新版）