MySQL常见面试题总结

1.什么是关系型数据库

关系型数据库（RDB，Relational Database）就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。

关系型数据库中，我们的数据都被存放在了各种表中（比如用户表），表中的每一行就存放着一条数据（比如一个用户的信息）。

2.事务的四大特性(ACID)

事务的四大特性，即ACID特性，是数据库管理系统中确保数据完整性和一致性的核心概念。以下是ACID特性的详细介绍：

• 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

  一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

  隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

  持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

3.什么是SQL，什么是MySQL?

3.1SQL

SQL 是一种结构化查询语言(Structured Query Language)，专门用来与数据库打交道，目的是提供一种从数据库中读写数据的简单有效的方法。几乎所有的主流关系数据库都支持 SQL ，适用性非常强。并且，一些非关系型数据库也兼容 SQL 或者使用的是类似于 SQL 的查询语言。

3.2 MySQL

MySQL 是一种关系型数据库，主要用于持久化存储我们的系统中的一些数据比如用户信息。

由于 MySQL 是开源免费并且比较成熟的数据库，因此，MySQL 被大量使用在各种系统中。任何人都可以在 GPL(General Public License) 的许可下下载并根据个性化的需要对其进行修改。MySQL 的默认端口号是3306。

MySQL字段类型：

4. MySQL基础架构

MySQL 主要由下面几部分构成：

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。

• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。

• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

• 优化器： 按照 MySQL 认为最优的方案去执行。

• 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。

• 插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

4.1 存储引擎

MySQL的核心在于存储引擎

MySQL 当前默认的存储引擎是InnoDB(MySQL5.5之后默认InnoDB，之前是MyISAM)。并且，所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

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

4.1.1 InnoDB相对MyISAM的优势

1、是否支持行级锁

MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

也就说，MyISAM 一锁就是锁住了整张表，这在并发写的情况下是多么滴憨憨啊！这也是为什么 InnoDB 在并发写的时候，性能更牛皮了！

2、是否支持事务

MyISAM 不提供事务支持。

InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）。

3、是否支持外键

MyISAM 不支持，而 InnoDB 支持。

外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！

总结：

• InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。

• MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。

• MyISAM 不支持外键，而 InnoDB 支持。

• MyISAM 不支持 MVCC，而 InnoDB 支持。

• 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

• MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。

• InnoDB 的性能比 MyISAM 更强大。

5. MySQL日志

MySQL 日志常见的面试题有：

5.1 MySQL 中常见的日志有哪些？

• 慢查询日志

• binlog（二进制日志）

• redo log（重做日志）

• undo log(回滚日志 )

5.2 慢查询日志有什么用？

用于记录执行时间较长的SQL查询。以下是慢查询日志的主要用途：

• 通过分析慢查询日志，可以识别出执行时间较长的SQL语句，进而优化这些语句；

• 慢查询日志可以帮助管理员监控数据库的性能，及时发现并解决潜在的性能问题。

5.3 binlog (二进制日志)主要记录了什么？

主要记录了对数据库执行更改的所有操作，但不包括数据查询语句（SELECT语句）。这些操作包括数据的插入（INSERT）、更新（UPDATE）、删除（DELETE）以及表结构的变更（如CREATE TABLE、ALTER TABLE等）。

任何对表结构的更改，每个事务的开始、提交或回滚，都会被记录到binlog中。

5.4 redo log（重做日志） 如何保证事务的持久性？

是MySQL InnoDB存储引擎中用于保证事务持久性的关键机制之一。

• 当一个事务开始时，InnoDB会先将事务中的修改操作记录到内存中的redo log buffer中。

• redo log采用Write-Ahead Logging（WAL）技术，即在修改数据之前先记录日志。这样，即使在系统崩溃或断电的情况下，已经写入redo log的修改操作也不会丢失。

• 当一个事务提交时，InnoDB会确保对应的redo log已经被写入磁盘。这意味着，即使在事务提交后系统崩溃，也可以通过应用redo log中的修改操作来恢复数据。

• 如果MySQL服务器在事务提交后崩溃，InnoDB会在重启时执行崩溃恢复过程。在这个过程中，InnoDB会读取redo log文件，并根据其中的记录重新执行那些已经提交但尚未写入磁盘的修改操作。因此，InnoDB可以确保即使在系统崩溃的情况下，已经提交的事务的修改操作也不会丢失。

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

5.6 undo log 如何保证事务的原子性？

记录初始状态：

• 当一个事务开始执行时，数据库会将即将被修改的数据的初始状态记录到 Undo Log 中。在事务过程中，每当数据库中的数据发生变化，原始数据（即修改之前的数据）都会被记录到 Undo Log 中。

执行事务操作：

• 事务会进行对数据库的各种操作（如插入、更新、删除等）。这些操作在内存中生效，但还没有被持久化到磁盘。

提交或回滚：

• 提交：当事务正常结束并成功提交时，事务的所有操作会被永久性地写入数据库，Undo Log 可能会被清理。

• 回滚：如果事务在执行过程中遇到错误或者显式调用回滚操作，数据库会利用 Undo Log 中记录的原始数据，逐步将已经进行的操作回退到事务开始前的状态，确保事务的所有操作对数据库没有任何影响。

Undo Log 通过记录事务中每一步修改前的数据，并在事务失败或回滚时使用这些日志进行恢复，确保了事务的原子性。这样，事务在完成之前对数据库的任何更改都可以被撤销，确保数据库要么完全反映事务的所有变化，要么完全不受事务影响。

6. MySQL事务

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

大多数情况下，我们在谈论事务的时候，如果没有特指分布式事务，往往指的就是数据库事务。

那数据库事务有什么作用呢？

简单来说，数据库事务可以保证多个对数据库的操作（也就是 SQL 语句）构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循：要么全部执行成功,要么全部不执行 。

6.1 并发事务带来哪些问题？

6.1.1 脏读

一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据，这也就是脏读的由来。

6.1.2 丢失修改

在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

6.1.3 不可重复读

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

6.1.4 幻读

幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

6.2 并发事务的控制方式有哪些

MySQL 中并发事务的控制方式无非就两种：锁 和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

6.2.1 锁

锁 控制方式下会通过锁来显式控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

• 共享锁（S 锁）（Share Lock）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

• 排他锁（X 锁）（Exclusive Lock）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

读写锁可以做到读读并行，但是无法做到写读、写写并行。另外，根据根据锁粒度的不同，又被分为 表级锁(table-level locking) 和 行级锁(row-level locking) 。InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类。

6.2.2 MVCC

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。

• undo log : undo log 用于记录某行数据的多个版本的数据。

• read view 和 隐藏字段 : 用来判断当前版本数据的可见性。

7. SQL定义了哪些事务隔离级别

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

• READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

• REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

• SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为`SELECT @@transaction_isolation;

8. MySQL锁

8.1 行级锁和表级锁的区别

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。

行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。

表级锁和行级锁对比：

• 表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。

• 行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。这个在我们日常工作开发中经常会遇到，一定要多多注意！！

8.2 InnoDB有哪几类行锁

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

• 记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。

• 间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。

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

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

8.3 共享锁和排他锁

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

共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

8.4 意向锁

用于快速判断是否可以对某个表使用表锁

意向锁是表级锁，共有两种：

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。

• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的。意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

意向锁之间是互相兼容的。

	IS 锁	IX 锁
IS 锁	兼容	兼容
IX 锁	兼容	兼容

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

	IS 锁	IX 锁
S 锁	兼容	互斥
X 锁	互斥	互斥

9. MySQL性能优化

9.1 能用MySQL直接存储文件（比如图片）吗？

可以是可以，直接存储文件对应的二进制数据即可。不过，还是建议不要在数据库中存储文件，会严重影响数据库性能，消耗过多存储空间。

数据库只存储文件地址信息，文件由文件存储服务负责存储。

9.2 MySQL如何存储IP地址

可以将 IP 地址转换成整形数据存储，性能更好，占用空间也更小。

MySQL 提供了两个方法来处理 ip 地址

• INET_ATON()：把 ip 转为无符号整型 (4-8 位)

• INET_NTOA() :把整型的 ip 转为地址

插入数据前，先用 INET_ATON() 把 ip 地址转为整型，显示数据时，使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可

---

著作权归JavaGuide(javaguide.cn)所有 基于MIT协议 原文链接：MySQL常见面试题总结 | JavaGuide