MySQL常见面试题自测

文章目录

MySQL基础架构
- 一、说说 MySQL 的架构？
- 二、一条 SQL语句在MySQL中的执行过程
MySQL存储引擎
- 一、MySQL 提供了哪些存储引擎？
- 二、MySQL 存储引擎架构了解吗？
- 三、MyISAM 和 InnoDB 的区别？
MySQL 事务
- 一、何谓事务？
- 二、何谓数据库事务？
- 三、ACID 特性指的是什么？
- 四、并发事务带来了哪些问题?
- 五、不可重复读和幻读区别?
- 六、SQL 标准定义了哪些事务隔离级别?
- 七、MySQL 的默认隔离级别是什么?能解决幻读问题么?
- 八、什么是 MVCC？有什么用？原理了解么？
- 九、InnoDB 事务隔离级别实现原理?
MySQL 锁
- 一、表级锁和行级锁了解吗？有什么区别？
- 二、行级锁的使用有什么注意事项？
- 三、共享锁和排他锁呢？
- 四、意向锁有什么作用？
- 五、InnoDB 有哪几类行锁？
MySQL索引
- 一、何为索引？有什么作用？
- 二、索引的优缺点？
- 三、索引的底层数据结构？
- 四、MySQL 的索引结构为什么使用B+树？
- 五、主键索引和二级索引？
- 六、聚集索引与⾮聚集索引？
- 七、覆盖索引？
- 八、联合索引？
- 九、最左前缀匹配原则？
- 十、创建索引的注意事项有哪些？
MySQL日志
- 一、MySQL 中常见的日志有哪些？
- 二、慢查询日志有什么用？
- 三、binlog 主要记录了什么？有什么用？
- 四、redo log 如何保证事务的持久性？
- 五、页修改之后为什么不直接刷盘呢？
- 六、binlog 和 redolog 有什么区别？
- 七、undo log 如何保证事务的原子性？
性能优化
- 一、SQL 优化？
- 二、慢查询问题排查？

MySQL基础架构

一、说说 MySQL 的架构？

⾯试官⼀般不会直接问你 MySQL 基础架构，通常会由“⼀个 SQL 语句在 MySQL 中的执⾏流程”类似的问题引出。

在这里插入图片描述

二、一条 SQL语句在MySQL中的执行过程

提示：结合 MySQL 的基础架构来回答这个问题。

首先SQL语句进行执行的时候，先要进行数据库连接，连接完成后首先查询缓存，如果命中缓存，则直接返回。之后对SQL进行解析，解析的过程包括词法分析、语法分析后构建语法树。之后再执行SQL语句，有三个阶段分别是预处理器阶段，优化阶段和执行阶段。预处理阶段先检查字段是否存在，将select的*扩展成所有的列，在经过优化器选择合适的索引，最后执行器里执行操作，返回结果。

MySQL存储引擎

一、MySQL 提供了哪些存储引擎？

提示：可以通过 show engines; 命令查看 MySQL 提供的所有存储引擎。
MySQL支持多种存储引擎，MySQL默认使用InnoDB存储引擎。

二、MySQL 存储引擎架构了解吗？

提示：MySQL 存储引擎采⽤的是插件式架构，⽀持多种存储引擎，我们甚⾄可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，⽽不是数据库。

MySQL 存储引擎采用的是插件式架构，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

三、MyISAM 和 InnoDB 的区别？

提示：从是否⽀持⾏级锁、事务、外键、数据库异常崩溃后的安全恢复、MVCC 等⽅⾯回答。
首先在MyISAM数据库不支持行级锁，而InnoDB支持。MyISAM数据库不支持事务，而InnoDB支持。MyISAM数据库不支持外键，而InnoDB支持。MyISAM数据库不支持数据库异常崩溃后的安全恢复，而InnoDB支持。MyISAM数据库不支持MVCC，而InnoDB支持。

MySQL 事务

一、何谓事务？

提示：转账的案例。

事务就是逻辑上的一组操作，要么都执行，要么都不执行。

二、何谓数据库事务？

提示：数据库事务可以保证多个对数据库的操作（也就是 SQL 语句）构成⼀个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循：要么全部执⾏成功,要么全部不执⾏。关系型数据库（例如： MySQL 、 SQL Server 、 Oracle 等）事务都有 ACID 特性。

多个数据库操作构成一个逻辑上的整体。这些数据库操作遵循要么全部执行成功，要么全部不执行。关系型数据库事务都有ACID特性。

三、ACID 特性指的是什么？

提示：ACID 特性⼤家都知道，但是，这⾥有⼀个绝⼤部分⼈可能会理解错误的地⽅：只有保证了事务的持久性、原⼦性、隔离性之后，⼀致性才能得到保障。也就是说 A、I、D 是⼿段，C 是⽬的！

ACID分别指代原子性、一致性、隔离性和持久性。只有保证了事务的持久性、原⼦性、隔离性之后，⼀致性才能得到保障。也就是说 A、I、D 是⼿段，C 是⽬的！

四、并发事务带来了哪些问题?

提示：脏读（Dirty read）、丢失修改（Lost to modify）、不可重复读（Unrepeatable read）、幻读（Phantom read）。
并发事务就是同一时间内发生多个事务，多个事务同时操作数据会导致脏读、丢失修改、不可重复度、幻读。

五、不可重复读和幻读区别?

不可重复读的重点是内容修改或者记录减少⽐如多次读取⼀条记录发现其中某些记录的值被修改；幻读的重点在于记录新增⽐如多次执⾏同⼀条查询语句（DQL）时，发现查到的记录增加了。

不可重复读的重点是内容修改或者记录减少⽐如多次读取⼀条记录发现其中某些记录的值被修改；

幻读的重点在于记录新增⽐如多次执⾏同⼀条查询语句（DQL）时，发现查到的记录增加了。

六、SQL 标准定义了哪些事务隔离级别?

提示：READ-UNCOMMITTED(读取未提交)、READ-COMMITTED(读取已提交)、REPEATABLE-READ(可重复读)、SERIALIZABLE(可串⾏化)。
定义了四种事务隔离级别，分别是 读取未提交、读取提交、可重复读、可串行化。

七、MySQL 的默认隔离级别是什么?能解决幻读问题么?

提示：默认隔离级别是 REPEATABLE-READ（可重读），可以解决幻读问题（两种情况，快照读和当前读）。
默认的隔离级别是可重复读，可以解决幻读问题。（快照读和当前读）。

快照读是普通的select语句是通过 MVCC 方式解决了幻读。因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。

当前读是select … for update 等语句。是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

八、什么是 MVCC？有什么用？原理了解么？

提示：RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）这两个隔离级别的实现都离不开 MVCC。
MVCC就是多版本并发控制，用于实现数据库并发控制的技术。它通过维护数据的多个版本来实现高效的并发访问，避免了传统锁机制带来的性能瓶颈。

作用：
提高并发性能：通过避免长时间锁定数据，MVCC 提高了数据库的并发性能。
实现一致性读：通过维护数据的多个版本，MVCC 能够实现一致性读操作，即在读操作期间，其他事务的修改不会影响当前事务的读取结果。
减少锁争用：MVCC 通过版本控制减少了事务之间的锁争用，从而提高了系统的整体性能。

MVCC 的核心思想是为每行数据维护多个版本，通过版本号来区分不同的事务视图。以下是 MVCC 的工作原理：

版本链：每行数据都有一个版本链，链中的每个版本代表数据在某个时间点的状态。版本链通过指针连接，最新版本在链头，旧版本在链尾。
隐藏列：InnoDB 在每行数据中维护了两个隐藏列：
- 事务ID（transaction ID）：表示创建或修改该版本的事务ID。
- 回滚指针（rollback pointer）：指向前一个版本的数据。
- 快照读：在快照读操作中，事务会根据其事务ID选择合适的版本进行读取。具体来说，事务会忽略那些在事务开始之后创建的版本，以及在事务开始之前删除的版本。
当前读：在当前读操作中，事务会读取最新的版本，并加锁以确保数据的一致性。

九、InnoDB 事务隔离级别实现原理?

提示：这个问题研究的⽐较深⼊，⼀般的⾯试不会问，难度有点⼤，需要结合 MySQL 锁和 MVCC 来回答。学有余⼒的朋友，可以通过下⾯这两篇⽂章来准备这个问题：
结合 MySQL 锁和 MVCC 来回答

MySQL 锁

一、表级锁和行级锁了解吗？有什么区别？

提示：表级锁(table-level locking)⼀锁就锁整张表。⾏级锁的粒度更⼩，仅对相关的记录上锁即可（对⼀⾏或者多⾏记录加锁）

表级锁，一锁就锁住整张表。而行级锁粒度更小是针对相关记录上锁即可。

二、行级锁的使用有什么注意事项？

提示：InnoDB 的⾏锁是针对索引字段加的锁，表级锁是针对⾮索引字段加的锁。当我们执⾏ UPDATE 、 DELETE 语句时，如果 WHERE 条件中字段没有命中索引或者索引失效的话，就会导致扫描全表对表中的所有记录进⾏加锁。

InnoDB 的⾏锁是针对索引字段加的锁，表级锁是针对⾮索引字段加的锁。当我们执⾏ UPDATE 、 DELETE 语句时，如果 WHERE 条件中字段没有命中索引或者索引失效的话，就会导致扫描全表对表中的所有记录进⾏加锁。

三、共享锁和排他锁呢？

提示：不论是表级锁还是⾏级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类

共享锁（S锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取。
排他锁（X锁）：又称写锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。当一个记录已经被加了排他锁后，那其他事物就不能对这条事物加任何的锁了。

四、意向锁有什么作用？

提示：快速判断是否可以对某个表使⽤表锁。意向锁是表级锁，共有两种。并且，意向锁之间是互相兼容的。
快速判断是否可以对某个表使⽤表锁。
意向锁共有两种：

意向共享锁：事务有意向对表中的某些记录加共享锁，加共享锁前必须先取得该表的意向共享锁。
意向排他锁：事务有意向向表中的某些记录加排他锁，加排他锁之前必须先取得该表的意向排他锁。

五、InnoDB 有哪几类行锁？

提示：表级锁(table-level locking)⼀锁就锁整张表。⾏级锁的粒度更⼩，仅对相关的记录上锁即可（对⼀⾏或者多⾏记录加锁）

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。
间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

MySQL索引

一、何为索引？有什么作用？

提示：索引的作⽤就相当于书的⽬录。

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

二、索引的优缺点？

提示：索引并不都是好的，创建索引和维护索引也需要耗费资源和时间
优点：

使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 减少 IO 次数，这也是创建索引的最主要的原因。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
缺点：
创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
索引需要使用物理文件存储，也会耗费一定空间。

三、索引的底层数据结构？

提示：表级锁(table-level locking)⼀锁就锁整张表。⾏级锁的粒度更⼩，仅对相关的记录上锁即可（对⼀⾏或者多⾏记录加锁）

B+树

四、MySQL 的索引结构为什么使用B+树？

提示：结合⼆叉查找树、平衡⼆叉树、红⿊树、B 树存在的缺陷来回答这个问题。

B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

五、主键索引和二级索引？

提示：数据表的主键列使⽤的就是主键索引。⼆级索引⼜称为辅助索引，是因为⼆级索引的叶⼦节点存储的数据是主键。也就是说，通过⼆级索引，可以定位主键的位置。唯⼀索引，普通索引，前缀索引等索引属于⼆级索引。

数据表的主键列使⽤的就是主键索引。⼆级索引⼜称为辅助索引，是因为⼆级索引的叶⼦节点存储的数据是主键。也就是说，通过⼆级索引，可以定位主键的位置。唯⼀索引，普通索引，前缀索引等索引属于⼆级索引。

六、聚集索引与⾮聚集索引？

提示：聚集索引即索引结构和数据⼀起存放的索引。主键索引属于聚集索引。⾮聚集索引即索引结构和数据分开存放的索引

聚集索引即索引结构和数据⼀起存放的索引。主键索引属于聚集索引。⾮聚集索引即索引结构和数据分开存放的索引

七、覆盖索引？

提示：覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了

八、联合索引？

提示：MySQL 中的索引可以以⼀定顺序引⽤多列。

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

九、最左前缀匹配原则？

提示：在 MySQL 建⽴联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。

十、创建索引的注意事项有哪些？

提示：选择合适的字段创建索引、尽可能的考虑建⽴联合索引⽽不是单列索引......。

不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

MySQL日志

一、MySQL 中常见的日志有哪些？

提示：错误⽇志（error log）、⼆进制⽇志（binary log）、⼀般查询⽇志（general query log）、慢查询⽇志（slow query log）、事务⽇志(redo log 和undo log) ......。
undolog 、 redolog 、binlog。

二、慢查询日志有什么用？

提示：慢查询⽇志记录了执⾏时间超过 long_query_time （默认是 10s）的所有查询，在我们解决 SQL 慢查询（SQL 执⾏时间过⻓）问题的时候经常会⽤到。

三、binlog 主要记录了什么？有什么用？

提示：MySQL binlog(binary log 即⼆进制⽇志⽂件) 主要记录了 MySQL 数据库中数据的所有变化(数据库执⾏的所有 DDL 和 DML 语句)。

MySQL binlog(binary log 即⼆进制⽇志⽂件) 主要记录了 MySQL 数据库中数据的所有变化(数据库执⾏的所有 DDL 和DML 语句)。

四、redo log 如何保证事务的持久性？

提示：redo log 主要做的事情就是记录⻚的修改，⽐如某个⻚⾯某个偏移量处修改了⼏个字节的值以及具体被修改的内容是什么。在事务提交时，我们会将redo log 按照刷盘策略刷到磁盘上去，这样即使 MySQL 宕机了，重启之后也能恢复未能写⼊磁盘的数据，从⽽保证事务的持久性。

redo log 主要做的事情就是记录⻚的修改，⽐如某个⻚⾯某个偏移量处修改了⼏个字节的值以及具体被修改的内容是什么。在事务提交时，我们会将redo log 按照刷盘策略刷到磁盘上去，这样即使 MySQL 宕机了，重启之后也能恢复未能写⼊磁盘的数据，从⽽保证事务的持久性。

五、页修改之后为什么不直接刷盘呢？

提示：性能⾮常差！InnoDB ⻚的⼤⼩⼀般为 16KB，⽽⻚⼜是磁盘和内存交互的基本单位。这就导致即使我们只修改了⻚中的⼏个字节数据，⼀次刷盘操作也需要将 16KB ⼤⼩的⻚整个都刷新到磁盘中。⽽且，这些修改的⻚可能并不相邻，也就是说这还是随机 IO。

性能⾮常差！InnoDB ⻚的⼤⼩⼀般为 16KB，⽽⻚⼜是磁盘和内存交互的基本单位。这就导致即使我们只修改了⻚中的⼏个字节数据，⼀次刷盘操作也需要将 16KB ⼤⼩的⻚整个都刷新到磁盘中。⽽且，这些修改的⻚可能并不相邻，也就是说这还是随机 IO。

六、binlog 和 redolog 有什么区别？

提示：可以从⽤途、写⼊⽅式、是否是 InnoDB 引擎特有的这⼏个⽅⾯来回答。

binlog 主要⽤于数据库还原，属于数据级别的数据恢复，主从复制是binlog 最常⻅的⼀个应⽤场景。redolog 主要⽤于保证事务的持久性，属于事务级别的数据恢复。
redolog 属于 InnoDB 引擎特有的，binlog 属于所有存储引擎共有的，因为 binlog 是 MySQL 的 Server 层实现的。
redolog 属于物理⽇志，主要记录的是某个⻚的修改。binlog 属于逻辑⽇志，主要记录的是数据库执⾏的所有DDL 和 DML 语句。
binlog 通过追加的⽅式进⾏写⼊，⼤⼩没有限制。
redo log 采⽤循环写的⽅式进⾏写⼊，⼤⼩固定，当写到结尾时，会回到开头循环写⽇志。