MySQL常见面试题

1.一条SQL查询语句是如何执行的

我们要进行查询首先要连接到数据库上，输入连接命令且完成TCP握手之后，mysql客户端工具成功与服务端建立连接，此时要输入用户名和密码让连接器来认证身份。如果用户名和密码不对的话会收到错误且客户端程序结束执行。如果认证通过，连接器会在权限表中查询当前用户所拥有的权限，之后这个连接里面的权限判断逻辑都依赖于此时读取到的权限。连接又分为长连接和短连接，由于连接过程复杂因此常用长连接。

在连接建立完成之后，就可以开始执行select语句了，在查询开始之前，MySQL会先查询缓存（MySQL8.0之前），如果缓存命中再次检查权限，确保缓存的结果对当前用户可见，如果有就返回缓存结果。如果没有缓存命中就进入到分析器中。在解析开始之前开始Precheck，检查用户是否有select权限访问目标表，如果有权限，分析器先进行词法分析，识别SQL语句中的关键字，把字符串识别成表名，列名等。之后进行语法分析，检验词法分析后的SQL语句是否存在语法问题。如果没问题，在进入优化器之前要进行权限检查，防止执行期间权限被撤销。如果成功进入到优化器，优化器会在表中有多个索引决定用哪个索引或一个语句存在多表关联的时候决定各个表的连接顺序，经过优化后会得到SQL语句的执行方案。将执行方案给到执行器，执行器开始执行语句。执行器会调用引擎接口去读取表，然后按顺序遍历表的方式去将符合条件的行添加到结果集中，最后返回给客户端。

precheck读的是全局权限快照，第二次会实时查询最新的权限

2.一条SQL更新语句是如何执行的

RedoLog

RedoLog是InnoDB引擎特有的，是物理日志，记录的是“在某个数据页上做了什么修改”，是循环写的，空间固定会用完。MySQL中有一个WAL技术（Write-Ahead-Logging），它的关键点是先写日志，再写磁盘。当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。write pos和check point之间是“黑板”上空着的部分，write pos是当前记录的位置，check point是当前要擦除的位置，在擦除前要把记录更新到数据文件中。如果write pos追上了check point，那么说明“黑板”满了，要擦掉一些记录向前移动。有了redolog，InnoDB就保证了即便数据库发生异常重启，之前提交的记录都不会丢失，这称之为crash-safe。

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

redo log示意图

BinLog

BinLog是MySQL的Server层实现的，所有引擎都可用，是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。是追加写的，binlog文件写到一定大小后会切换到下一个，不会覆盖以前的日志。

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

Update SQL流程

首先通过连接器，让客户端与mysql实现连接，创建全局权限快照。写SQL更新语句，在分析器分析前Precheck，Precheck通过后分析器通过词法和语法分析知道这是一条更新语句。在进入优化器前进行二次权限检查，通过后进入到优化器后得到执行方案，然后进入执行器，一进来先清空相关表的查询缓存，执行器拿到方案后会先去引擎找目标行，引擎用B+树找到这一行，如果BufferPool中有目标行的所在页，就在页中找到行的信息返回，否则就将目标行所在页从磁盘读取到BufferPool中，再返回那一行的信息。执行器拿到了引擎给的行数据，对行数据进行修改，修改后再调用引擎的写入接口将这一行写回去。引擎将这行新数据更新到BufferPool中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。执行器生成这个操作的binlog并且将binlog写入磁盘中，然后调用引擎的提交事务接口，引擎修改刚刚写入的redo log的状态为commit，更新完成。

两阶段提交

将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是”两阶段提交”。

现在有一个场景，比如某天下午两点发现中午十二点有一次误删表，需要找回数据。那么我们首先要找到最近的一次全量备份，然后将这个全量备份恢复到临时库。然后从这个全量备份的时间点开始，将备份的binlog依次取出来，重放到中午误删表那个时刻。这样临时库就跟误删之前的线上库一样了，然后把表数据从临时库取出来，按需要恢复到线上库去。

接着我们来说两阶段提交的重要性，用反证法来解释。假如不用两阶段提交，再假设当前 ID=2 的行，字段 c 的值是 0，执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash的情况。

假如先redolog后binlog，redolog写完的时候，binlog还没写完，MySQL就异常重启了。MySQL重启之后redolog将数据恢复回来，但是binlog还没写完就crash了，因此binlog里面没有这条语句。当我们需要用binlog来恢复临时库的时候，临时库就会少了这次更新，恢复出来的值是0，与原库的值不同。

假如先binlog后redolog，binlog写完，redolog还没写完就crash，MySQL重启之后这个事务无效，值还是原来的值，但binlog记录了这个值更新。当我们用binlog恢复的时候就会多出一个事务，恢复出来的值是1，和原库值不同。

所以如果不用两阶段提交，那么数据库的状态可能和用它日志恢复出来的库的状态不一致。redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

除了恢复临时库，在我们要搭建备库增加系统读能力的时候，现在常见的做法也是全量备份加上binlog来实现的，如果出现这种不一致就会导致主从不一致。

发生crash重启后，MySQL会扫描redolog，如果有处于prepare状态的事务，那么就会通过事务的XID去找binlog，如果binlog存在这个XID那么就完成数据页更新，否则就进行事务回滚

3.事务隔离（数据库并发一致性错误）

当数据库中有多个事务同时执行的时候，可能会出现脏读、不可重复读、幻读的问题。为了解决这些问题就有了隔离级别这个概念。SQL标准的事务隔离级别有读未提交、读提交、可重复读和串行化。读未提交是指一个事务还未提交时，它做的变更就能被别的事务看到，读的时候直接返回记录上的最新值。读提交指事务提交后，它做的变更才会被别的事务看到，在每次读的时候都会创建一个读视图。可重复读指的是在一个事务执行过程中看到的数据总跟这个事务启动时看到的数据一致，在事务启动的时候创建视图。串行化，对同一行记录，“写”会加“写锁”，“读”会加“读锁”，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成才能继续执行，通过加锁的方式避免并行访问。

• 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
• 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
• 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

可重复读的实现

在MySQL中，在每条记录更新的时候都会同时记录一条回滚操作，记录上的最新值通过回滚操作可以得到前一个状态的值。假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

在查询这一条记录的时候，不同时刻启动的事务会有不同的read-view，就如图中的ABC，对于同一条记录在系统中可以存在多版本，这就是数据库的MVCC。如果要得到A，就得由C执行回滚操作得到。

INSERT操作的undolog在事务提交后立即删除，因为undolog只需要记录新插入数据的主键信息，用于事务回滚的时候能够快速定位并删除该行数据。UPDATE/DELETE操作的undolog在事务提交后延迟删除，由Purge线程异步清理。当系统中所有可能访问当前undolog的review均已提交，当前活跃事务中的最小ID如果大于undolog中的事务id，那么就说明当前的redolog不被需要，可以被清理。

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。长事务还占用锁资源，也可能拖垮整个库，因此我们要避免长事务。可以通过每次都用显示事务的方式或set autocommit=0并且每次都主动commit。

MVCC原理

InnoDB每行数据都有三个隐藏字段

• DB_TRX_ID：最近修改该行的事务ID。
• DB_ROLL_PTR：回滚指针，指向Undo Log中的旧版本数据。
• DB_ROW_ID：隐含的行ID（无主键时使用）

然后事务在启动的时候会产生一个视图，这个视图包含

• trx_ids：生成Read View时活跃的事务ID列表。
• min_trx_id：活跃事务中的最小ID。
• max_trx_id：下一个将分配的事务ID。
• creator_trx_id：创建该Read View的事务ID。

数据会通过DB_ROLL_PTR指向undolog中的旧版本，然后形成一条版本链，undolog中记录着trx_id，即历史版本的事务ID。事务会通过ReadView对比当前事务和版本链中的trx_id，来决定是否允许访问该版本的数据。

4.索引

哈希表结构索引适用于等值查询的情景，有序数组适用于静态存储引擎

InnoDB索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。

这个表的建表语句是：

mysql> create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

根据上面的索引结构说明，我们来讨论一个问题：基于主键索引和普通索引的查询有什么区别？

• 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
• 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。即要查询的字段在索引上就是覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

最左前缀原则

B+树的索引结构满足最左前缀原则，用下图（name，age）联合索引来分析

当我们要查的是所有名字第一个字是“张”的人， SQL 语句的条件是”where name like ‘张 %’”。这时，能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。可以看得出来只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

索引失效场景

1. 违背最左前缀原则
2. 隐式类型转换,比如phone为varchar，写成whre phone=121231
3. 索引列参与运算，比如where age+1>30会失效，要改成where age>29
4. Like左模糊匹配，比如where name like '%A%'不能利用索引
5. or连接非索引条件，比如age=10 or address='BeiJing',其中address无索引
6. 对索引列使用函数

5.锁

InnoDB的锁分为全局锁、表级锁、行级锁。

当全库进行逻辑备份的时候会用到全局锁，锁定整个数据库实例，使数据库处于只读状态。表级锁有两种，一种是表锁，一种是元数据锁MDL，表锁的语法是 lock tables … read/write，可以用unlock tables主动释放锁。lock table不仅会限制别的线程的读写，也限制了本线程的读写。MDL不需要显式使用，它会在访问一个表的时候给表加上，保证了在对当前表进行读写的时候表的结构不会发生变化。

行级锁有两种，一种是记录锁，一种是间隙锁。记录锁锁定单行索引记录，防止其他事务修改，比如当前读的时候select for update就会给记录加上这个锁，RC和RR都生效。间隙锁锁定了一个一个范围的记录，防止幻读的发生，RR下生效。

6.BufferPool

虽然说 MySQL 的数据是存储在磁盘里的，但是也不能每次都从磁盘里面读取数据，这样性能是极差的。要想提升查询性能，加个缓存就行了嘛。所以，当数据从磁盘中取出后，缓存内存中，下次查询同样的数据的时候，直接从内存中读取。因此InnoDB引擎设计了一个BufferPool。读出来的数据分为数据页和索引页，都使用 InnoDB 的标准页结构（16KB），包含页头、行记录、页尾等信息。数据页存储完整行数据，索引页存储导航结构。

LRU策略

InnoDB的LRU链表分为Young和Old两个区域。Young占5/8，用于存放最活跃的热数据。Old占 3/8，用于存放新加载的数据和较不活跃的数据。

当新数据页首次加载时，会被插到Old区的头部，而不是直接放在整个LRU的头部，避免一次性操作污染热数据。只有当一个页在Old区存活超过innodb_old_blocks_time（默认1秒）后再次被访问就会被移动到Young区的头部，这个时间窗口防止短期访问的页进入到热区中。Young区的页被访问会被移到Young区头部，保证真正的热数据在最容易被访问的位置。

当BufferPool需要空间加载新页的时候，LRU链表从尾部开始扫描，优先淘汰old区域的页，如果是脏页就刷盘再淘汰，普通页就直接淘汰。只有当old区不能腾出足够空间的时候，会尝试Young区淘汰。当Young区的某些页不再被频繁访问会逐渐向LRU链表的尾部移动。

ChangBuffer

changeBuffer是InnoDB对非唯一二级索引（非唯一指索引列允许重复值）写操作的优化工具，它的核心是"延迟写入+批量合并"，是BufferPool的一部分，默认占用25%的BufferPool内存。可以把changbuffer看作是BufferPool的写缓存助手

当修改（INSERT/UPDATE/DELETE）非唯一二级索引且目标页不在内存时，不立即读磁盘，而是先把操作记录到 Change Buffer。比如要更新100条数据的name字段（name有二级索引），如果这些索引页不在内存，如果不用ChangBuffer就要100次磁盘读（加载索引页）+100次磁盘写。用 ChangeBuffer只需记录100次操作到内存，后续合并到磁盘时可能只需1次顺序写。后续当索引页被加载到BufferPool的时候，再合并ChangBuffer中的操作，避免了随机写IO过多（数据以非连续的方式写入磁盘不同物理位置的操作）的毛病。

BufferPool工作流程

有了BufferPool之后，当读取数据时，InnoDB会先通过哈希表快速定位所需的数据页是否在BufferPool中，如果命中在BufferPool中直接读取，并且更新LRU链表，将该页移动到Young的头部（若在old区存活超过时间窗口）。如果没有命中，就先从BufferPool的free链表（由空闲描述数据块组成）中获取空闲页，如果Free链表是空的就触发LRU淘汰，被淘汰的页会加回Free链表 。从Free链表中取出空闲描述数据块，从磁盘中载入数据，插入到old区的头部。

如果是聚簇索引修改，直接修改BufferPool的页，标记为脏页并加入Flush链表（Flush与LRU的脏页会共存），对非唯一二级索引修改，如果目标页在BufferPool中也是直接修改并标记脏页，如果不在的话就要将修改记录记录到ChangBuffer中生成redolog保证持久化，当该页被读取到BufferPool中再合并ChangBuffer中的操作。后台线程定期从Flush链表尾部刷脏页到磁盘，并取消LRU的脏页标记。

7.InnoDB和MyISAM

InnoDB和MyISAM是MySQL最核心的两种存储引擎，主要区别如下：InnoDB支持事务、行级锁和外键，适合高并发写入场景（如电商、支付系统），采用聚簇索引结构（数据与主键绑定），崩溃恢复能力强但占用更多存储空间；MyISAM不支持事务和行级锁，仅支持表锁，读写性能高但并发能力弱，采用非聚簇索引（索引与数据分离），适合读密集型应用（如日志分析），且表压缩效率更高但崩溃后修复困难。MySQL 5.5+默认使用InnoDB，因其在事务安全性和并发性能上的综合优势，而MyISAM适用于无需事务且以查询为主的场景

8.事务是什么，ACID是什么

事务是数据库系统中的一个操作单元，包含一组操作，这些操作要不全部成功，要不全部失败回滚。ACID指的是原子性、一致性、隔离性和持久性。原子性指的是事务内的所有操作不可分割，要么都成功要么都失败回滚。一致性指的是事务执行完毕后状态合法，逻辑正常。比如转账场景转账后两用户总金额不变。隔离性指的是并发事务之间互不干扰。持久性指的是事务提交后修改永远生效。

9.如何定位和优化慢SQL

可以开启MySQL的慢查询日志，查看慢查询，这样子就可以定位到慢SQL。得到慢SQL之后分析SQL语句的执行计划，关注执行计划中的全表扫描，索引使用情况等指标，决定是否要避免全表扫描，重建索引，重写SQL之类的操作。

10.数据库三大范式

数据库三大范式是关系型数据库设计的核心规范，第一范式要求字段不可再分，每行有唯一主键。第二范式是在第一范式基础上要求非主键字段必须完全依赖整个主键，消除部分依赖。第三范式是第二范式基础上要求非主键字段间不能存在传递依赖。这样能够有效减少数据冗余，避免更新异常。

11.MySQL事务是怎么实现的

MySQL的InnoDB引擎支持事务，通过日志系统和MVCC共同实现事务的ACID特性。

原子性：事务开始时，记录数据修改之前的状态到undolog，如果事务失败就通过undolog回滚到原始状态。

隔离性：通过MVCC和锁机制实现

持久性：事务提交时先写redolog到磁盘，后台线程异步将脏页刷盘

一致性：通过上面三个特性共同保证。