MySQL常见面试题总结

MySQL常见面试题总结

MySQL基础

关系型数据库介绍

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

关系型数据库中，我们的数据都被存放在了各种表中，表中的每一行就存放着一条数据

大部分关系型数据库都使用SQL来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性（ACID）

有哪些常见的关系型数据库呢？

MySQL、PostgreSQL、Oracle、SQL Server、SQLite

MySQL介绍

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

由于MySQL是开源免费并且比较成熟的数据库，因此,MySQL被大量使用在各种系统中，其默认端口号是3306

MySQL基础架构

下图是MySQL的一个简要架构图，从下图你可以很清晰的看到客户端的一条SQL语句在MySQL内部是如何执行的

从上图可以看出，MySQL主要由下面及部分构成：

• 连接器：身份认证和权限相关（登陆MySQL的时候）
• 查询缓存：执行查询语句的时候，会先查询缓存（MySQL 8.0 版本之后移除了，因为这个功能不太实用）
• 分析器：如果命中缓存的话，SQL语句就会经过分析器，分析器说白了就是要先看你的SQL语句要干嘛，再检查你的SQL语句语法是否正确
• 优化器：按照MySQL认为最优的方案去执行
• 执行器：执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错
• 插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持InnoDB,MyISAM、Memory等多种存储引擎

MySQL存储引擎

MySQL核心在于存储引擎，想要深入学习MySQL，必定要深入研究MySQL存储引擎

MySQL支持哪些存储引擎？默认使用哪个？

MySQL支持多种存储引擎，你可以通过show engines命令来查看MySQL支持的所有存储引擎

从上图我们可以看出，MySQL当前默认的存储引擎是InnoDB。并且，所有的存储引擎只有InnoDB是事务性存储引擎，也就是说只有InnoDB支持事物

MySQL 5.5.5之前，MyISAM是默认引擎，之后InnoDB是默认引擎

MySQL存储引擎架构了解吗？

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

MyISAM和InnoDB的区别是什么？

MySQL 5.5 之前，MyISAM是默认引擎，虽然MyISAM的性能还行，各种特性也还不错（比如全文索引、压缩、空间函数等）。但是，MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全修复

1. 是否支持行级锁

   MyISAM只有表级锁，而InnoDB支持行级锁和表级锁，默认为行级锁。也就是说，MyISAM一锁就是锁住了整张表，这在并发写的情况下显得特别憨憨，这也是为什么InnoDB在并发写的时候，性能更好的原因

2. 是否支持事务

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

3. 是否支持外键

   MyISAM不支持，而InnoDB支持。外键对维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的

4. 是否支持数据库异常崩溃后的安全恢复

   MyISAM不支持，而InnoDB支持。使用InnoDB的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态，这个恢复过程依赖于redo log

5. 是否支持MVCC

   MyISAM不支持，而InnoDB支持。MVCC可以看作是行级锁的一个升级，可以有效减少加锁操作，提高性能

6. 索引实现不一样

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

   InnoDB中，其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按照B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录

MyISAM和InnoDB如何选择？

大多数都是使用InnoDB

MySQL事务

何为事务？

我们设想一个场景，这个场景中我们需要插入多条相关联的数据到数据库，不幸的是，这个过程可能会遇到下面这些问题

• 数据库中途突然因为某些原因挂掉了
• 客户端突然因为网络原因链接不上数据库了
• 并发访问数据库时，多个线程同时写入数据库，覆盖了彼此的更改

上面的任何一个问题都可能会导致数据的不一致性。为了保证数据的一致性，系统必须能够处理这些问题。事务就是我们抽象出来简化这些问题的首选机制。事务的概念起源于数据库，目前，已经成为一个比较广泛的概念，

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

何为数据库事务

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

数据库事务在我们日常开发中接触的最多了，那么数据库事务有什么作用呢？

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

另外，关系型数据库的事务都由ACID特性：

1. 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用
2. 一致性(Consistency)：执行事务前后，数据保持一致。例如在转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的
3. 隔离性(Isolation)：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
4. 持久性(Durability)：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响

这里额外补充一点：只有保证了事务的持久性、原子性和隔离性之后，一致性才能得到保证

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

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一个数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

• 脏读(Dirty read)：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所作的操作可能是不正确的
• 丢失修改(Lost to modify):指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结构就被丢失，因此成为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A =A-1,事务2也修改A =A-1，最终结果是A=19，事务1的修改被丢失、
• 不可重复读（Unrepeatable read）：指在一个事务内多次读同一数据。在这个事务还没结束时，另一个事务也访问该数据。那么在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了一个事务内两次读到的数据是不一样的情况，因此成为不可重复读
• 幻读(Phantom read)：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务(T2)插入了一些数据时，在随后的查询中，第一个事务(T1)就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读

不可重复度和幻读有什么区别呢？

• 不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改
• 幻读的终于在于记录新增，比如多次执行同一条查询语句时，发现查到的记录增加了

幻读其实可以看作是不可重复读的一种特殊情况，单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样。

举个例子：执行 delete 和 update 操作的时候，可以直接对记录加锁，保证事务安全。而执行 insert 操作的时候，由于记录锁（Record Lock）只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁（Gap Lock）。也就是说执行 insert 操作的时候需要依赖 Next-Key Lock（Record Lock+Gap Lock） 进行加锁来保证不出现幻读。

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

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

• READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读和不可重复读
• READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读和不可重复读仍有可能发生
• REPEATABLE-READ(可重复读)：对同一字段的多次读取结果是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
• SERIALIZABLE(可串行化)：最高的隔离级别，完全服务ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可防止脏读、不可重复读和幻读

MySQL的隔离级别是基于锁实现的吗？

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

SERIALIZABLE隔离级别，是通过锁实现的。其他的隔离级别都是基于MVCC实现的。

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

MySQL的默认隔离级别是什么？

MySQL InnoDB存储引擎的默认支持的隔离级别是REPEATABLE-READ(可重复读)。

MySQL锁

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

MyISAM仅仅支持表级锁，一锁就是整张表，这在并发写的情况下性能非常差

InnoDB不仅仅支持表级锁，还支持行级锁，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁，所以对并发写入操作来说，InnoDB性能更高

表级锁和行级锁对比

• 表级锁：MySQL中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和InnoDB都支持表级锁
• 行级锁：MySQL中粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。并发度高，但加锁的开销也最大，加锁慢，会出现死锁

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

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

不过，很多时候即使使用了索引也有可能会走全表扫描，这是因为MySQL优化器的原因

共享锁和排他锁呢？

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

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

排他锁与任何锁都不兼容，共享锁仅和共享锁兼容

由于MVCC的存在，对于一般的SELECT语句，InnoDB不会加任何锁，不过，你可以通过以下爱语句显示加共享锁或排他锁

# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁有什么作用？

如果需要用到表锁的话，如何判断表中的记录没有行锁呢？一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东西来快速判断是否可以对某个表使用表锁。

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

• 意向共享锁（Intention Shared Lock,IS锁）：事务有意向对表中的某些加共享锁(S锁),加共享锁前必须先取得该表的IS锁
• 意向排他锁(Intention Exclusive Lock,IX锁)：事务有意向对表中的某些记录加排他锁(X锁)，加排他锁之前必须先取得该表的IX锁

意向锁是数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InnoDB会先获取该数据行所在数据表的对应意向锁。意向锁之间是互相兼容的。意向锁和共享锁、排他锁互斥

InnoDB有哪几类行锁？

• 记录锁(Record Lock):也被称为记录锁，属于单个行记录上的锁
• 间隙锁(Gap Lock):锁定一个范围，不包括记录本身
• 临键锁(Next-Key Lock)：记录锁+间隙锁，锁定一个范围，包含记录本身。记录锁只能锁住已经存在的记录，为了避免插入新纪录，需要依赖间隙锁

InnoDB的默认隔离级别RR（可重读）是可以解决幻读问题的发生的，主要有下面两种情况：

• 快照读（一致性非锁定读）：由MVCC机制来保证不出现幻读
• 当前读（一致性锁定读）：使用Next-Key Lock进行加锁来保证不出现幻读

当前读和快照读有什么区别?

快照读就是单纯的SELECT语句，但不包括下面这两个加锁的SELECT语句

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

快照即记录的历史版本，每行记录可能存在多个历史版本

快照读的情况下，如果读取的记录正在执行UPDATE/DELETE操作，读取操作不会因此去等待记录上X锁的释放，而是会去读取行的一个快照

只有在事务隔离级别RC（读取已提交）和RR（可重读）下，InnoDB才会使用快照读

• 在RC级别下，对于快照数据，快照读总是读取被锁定行的最新一份快照数据
• 在RR级别下，对于快照数据，快照都总是读取本事务开始时的行数据版本

因此，快照读比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景

当前读就是给行记录加X锁或S锁

当前读的一些常见SQL语句类型如下：

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

MySQL性能优化

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

可以是可以，直接存储文件对应的二进制数据即可。不过，还是建议不要在数据库中存储文件，会严重影响数据库性能，消耗过多存储空间。可以选择使用云服务厂商提供的开箱即用的文件存储服务，成熟稳定，价格也比较低

MySQL如何存储IP地址？

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

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

• INET_ATON():把IP转为无符号整型
• INET_NTOA:把整型的IP转为地址

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