MySQL面试核心30题与深入解析：从索引、事务到调优与高可用

这是笔者在实习与秋招期间，历经数十场面试后总结出的MySQL高频考点，共30个核心问题，助你系统查漏补缺。

以下是本文涵盖的知识点总览：

01 三大范式？

• 第一范式：字段具有原子性，不可再分（字段单一职责）。
• 第二范式：满足第一范式，每行应该被唯一区分，需有一列作为主键（所有字段都要依赖主键）。
• 第三范式：满足一、二范式，且一个表不能包含其他表已存在的非主键信息（不间接依赖）。

范式的优点与缺点：

• 优点：数据冗余少，更新快，修改少，查询时需要的 DISTINCT 操作更少。
• 缺点：因为数据不冗余，查询可能需要多次表关联，效率可能降低，也可能使一些索引策略无效（本可属于同一索引的列被拆分到不同表中）。

02 InnoDB与MyISAM的区别？

2.1、MyISAM与InnoDB核心区别

• 索引结构：InnoDB是聚簇索引，MyISAM是非聚簇索引。
• 文件存储：InnoDB的数据和索引保存在 .ibd 文件中；MyISAM的表结构、索引、数据分别保存在 .frm、.MYI、.MYD 文件中。
• 功能支持：InnoDB支持事务、外键、行锁和表锁；MyISAM不支持事务和外键，只支持表锁。
• 计数查询：SELECT COUNT(*) 时，MyISAM更快（存储了总行数）。
• 性能倾向：MyISAM查询更优，InnoDB更新更优。
• 索引类型：两者都使用B+树索引。
• 全文索引：MyISAM支持，InnoDB在5.6版本后开始支持。

2.2、MyISAM详解

• 不支持事务，但每次查询操作是原子的。
• 支持表级锁，每次操作会对整个表加锁。
• 存储表的总行数。
• 一个表对应三个文件：.frm(表结构)、.MYI(索引)、.MYD(数据)。
• 采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅助索引与主索引结构类似，但无需保证唯一性。

2.3、InnoDB详解

• 支持ACID事务，提供四种隔离级别。
• 支持行级锁及外键约束，因此支持更高的写并发。
• 不存储总行数。
• 主键索引采用聚集索引（索引叶子节点存储数据行本身），辅助索引的数据域存储主键的值。因此通过辅助索引查找数据，需要先查到主键值，再回表查询主键索引。建议使用自增主键，防止插入数据时，为维持B+树结构而发生大量数据移动（页分裂）。

2.4、使用场景

大多数场景推荐使用 InnoDB 存储引擎。仅在某些读密集、且不介意 MyISAM 不支持事务、崩溃恢复等缺点的情况下，可考虑 MyISAM。

• MyISAM适合读多更新少的场景：因其索引与数据分离存放，在某些情况下读取更快。
• InnoDB适合插入更新频繁的场景：索引与数据存放一起，采用行锁，更新效率更高。
• 需要事务或高并发场景用InnoDB。
• 简单对比：MyISAM查询常更快，InnoDB更新常更快。

场景深入分析：MyISAM适合读多写少。因为其使用非聚簇索引，数据和索引分开存储。当数据量很大时，固定大小的内存页能读入的索引指针更多，理论上查询可能更快。而InnoDB需要将数据和索引一起读入，单页有效数据量可能更少，并且还需要维护额外的隐藏字段（如 row_id、trx_id）。

03 自增主键理解？

自增主键：InnoDB引擎的自增值保存在内存中。直到MySQL 8.0版本，才实现了“自增值持久化”能力，即重启后可以恢复为重启前的值。

• MySQL 5.7及之前：自增值保存在内存，未持久化。每次重启后，第一次打开表时，会找到当前最大的id值 max(id)，然后将 max(id)+1 作为该表的自增值。例如，表最大id为10，AUTO_INCREMENT=11。删除id=10的行后，AUTO_INCREMENT仍为11。但若重启实例，该表的 AUTO_INCREMENT 会变回10。
• MySQL 8.0及之后：自增值的变更记录在redo log中，重启时依靠redo log恢复。

自增值修改机制：

1. 插入时若id字段指定为0、NULL或未指定，则使用表当前的 AUTO_INCREMENT 值。
2. 插入时若id字段指定了具体值，则直接使用该值。

自增值新增机制：

1. 若准备插入的值 >= 当前自增值，新自增值 = “准备插入的值 + 1”。
2. 否则，自增值不变。

为什么自增主键不连续？

• MySQL 5.7及之前版本，自增值在内存中，未持久化，重启可能导致变化。
• 事务回滚：自增值一旦分配，不会因为事务回滚而收回，否则可能导致主键冲突。
• 唯一键冲突：事务因唯一键冲突插入失败，但自增值已被递增，导致后续插入的主键不连续。

示例：
假设表t已有记录 (1,1,1)（id，唯一键c，普通字段d）。执行：

insert into t values(null, 1, 1);

流程如下：

1. 执行器调用引擎接口写入一行 (0,1,1)。
2. InnoDB发现未指定自增id，获取当前自增值2。
3. 将行改为 (2,1,1)。
4. 将表自增值改为3。
5. 执行插入，发现c=1已存在（唯一键冲突），语句报错返回。

虽然id=2的行未插入成功，但自增值已改为3。后续插入新数据时，拿到的自增id就是3，出现不连续。

04 InnoDB为什么推荐用自增ID？

1. 顺序插入：主键页会近乎顺序地被填满，提高页的填充率，减少空间浪费。
2. 定位快速：新插入的行一定在最大数据行之后，定位寻址快，无需额外计算位置。
3. 减少碎片：减少了页分裂和内存碎片的产生。

对比UUID：UUID无序，插入位置随机，易导致频繁的页分裂、数据移动、内存碎片化以及大量的随机IO，影响性能。

总结：自增ID有序插入，效率高；UUID无序插入，易导致性能问题。

05 什么是索引？

• 索引是排好序的、帮助快速查找数据的数据结构。
• 优点：大幅提高查询速度，查询优化器可利用其提升性能。
• 缺点：占用额外物理空间，会降低增、删、改的速度（因为需要维护索引文件）。

06 索引类型（覆盖索引、回表、索引下推、联合索引）

• 普通索引：允许重复值。
• 唯一索引：列值必须唯一，允许NULL。一个表只能有一个主键索引，但可以有多个唯一索引。
• 主键索引
  • 唯一且非空。叶子节点存储完整的行记录数据，因此也称聚簇索引。
  • 非主键索引（二级索引）的叶子节点存储的是主键的值。使用二级索引查询需要回表操作（先查主键，再用主键查主键索引）。
  • 推荐使用自增主键，有助于保证空间利用率，减少页分裂。
• 全文索引：用于全文搜索。
• 覆盖索引：索引字段覆盖了查询语句需要的所有字段，查询可以直接从索引中取得数据，无需回表。
• 回表：通过二级索引找到主键ID，再根据ID去主键索引树查找完整记录的过程。
• 索引下推
  • 在根据索引查询的过程中，直接根据索引中的字段进行条件过滤，减少最后需要回表检查的记录数。
  • 例如：SELECT * FROM stu WHERE name=? AND age=?
    • 无索引下推：存储引擎根据name筛选数据返回给Server层，Server层再根据age过滤。
    • 有索引下推：存储引擎层直接根据name和age在索引中进行过滤，将最终结果返回。

07 索引底层数据结构？

主要有 B+树 和 Hash。

Hash索引：

• 底层是哈希表，适合等值查询，速度快。
• 缺点：大量哈希冲突时效率下降；不支持范围查询、排序、分组和模糊查询；无法利用多列索引的最左前缀匹配原则；通常需要回表。

08 B树与B+树区别？为何用B+树？

B+树特点：

• 非叶子节点只存储键值（key），不存储数据（data），因此单个节点能存储更多键，树的高度更低，查询更稳定。
• 叶子节点间有双向链表指针，支持高效的范围查询。
• 数据只存储在叶子节点，非叶子节点可视为索引的冗余。

为何选用B+树而非红黑树等？

1. 更少磁盘I/O：B+树节点度（出度）更大，树高更低，查询所需磁盘I/O次数更少。
2. 利用磁盘预读：磁盘往往按页预读。B+树叶子节点存储相邻数据，顺序读取代价低。
3. 存储更多索引：由于非叶子节点不存数据，同样大小的磁盘页能容纳更多索引项，进一步降低了树高。

09 索引设计原则（查询快，占用空间少）

• 出现在 WHERE 或 JOIN 子句中的列。
• 基数（不同值数量）小的表没必要建索引。
• 使用短索引，对长字符串可指定前缀长度。
• 定义有外键的数据列一定要建索引。
• 不要过度索引。
• 更新非常频繁的列不适合建索引。
• 区分度不高的列（如性别）不适合建索引。
• 尽量扩展已有索引，而非新建，例如从 (a) 扩展到 (a,b)。

字符串字段建立索引的方法：

1. 直接创建完整索引，占用空间可能较大。
2. 创建前缀索引，节省空间，但会增加查询扫描次数，且无法使用覆盖索引。
3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀区分度不够的问题。
4. 额外增加一个哈希字段并建立索引，有额外的存储和计算开销。

总结：索引设计要兼顾查询效率与空间占用。通常建在 WHERE 条件涉及、区分度高、基数大的列上。避免过长索引，优先使用联合索引。更新频繁、区分度低的列不适宜建索引。

10 索引失效场景？

• 以“%”开头的 LIKE 语句（后缀“%”不影响）。
• OR 语句前后没有同时使用索引。
• 列类型是字符串，条件中未加引号（导致隐式类型转换）。
• 当MySQL优化器估算使用全表扫描比使用索引更快时（如数据重复度高）。
• 违反最左前缀原则使用组合索引。
• 在索引字段上使用 NOT、<>、!=。
• 对索引字段进行运算操作或使用函数。
• 对索引字段进行 IS NULL 判断（并非绝对失效，取决于数据分布）。

11 如何创建索引？

ALTER TABLE table_name ADD INDEX index_name (column_list);
CREATE INDEX index_name ON table_name (column_list);

也可以在 CREATE TABLE 时直接创建。

12 非聚簇索引一定会回表查询吗？

不一定。如果查询的字段全部包含在索引中（即覆盖索引），则无需回表，直接从索引获取结果。

13 联合索引的建立规则？

• 将查询最频繁或选择性（区分度）最高的列放在前面。
• 考虑索引的复用性，例如有联合查询 (a,b)，则 (a) 上的查询也可复用该索引，无需单独为 (a) 建索引。
• 如果既有联合查询 (a,b)，又有大量基于 a、b 各自的单列查询，则需要权衡。通常原则是空间，将查询量小的列单独建索引。

14 最左匹配原则

联合索引 (a,b,c) 的匹配规则是从左往右依次匹配，直到遇到范围查询（>、<、BETWEEN、LIKE）停止。

索引是先根据 a 排序，a 相同时 b 有序，a 不同则 b 全局无序。同理，(a,b) 确定时 c 有序。

示例：

-- 全值匹配，与顺序无关，优化器会调整
SELECT * FROM table_name WHERE a = '1' AND b = '2' AND c = '3';
-- 匹配左边列，用到索引
SELECT * FROM table_name WHERE a = '1';
SELECT * FROM table_name WHERE a = '1' AND b = '2';
-- 未包含最左列a，无法使用索引
SELECT * FROM table_name WHERE b = '2';
SELECT * FROM table_name WHERE b = '1' AND c = '3';
-- 只用到了a列的索引，b、c未用到
SELECT * FROM table_name WHERE a = '1' AND c = '3';
-- 范围查询a，只有a用到索引，b在a的范围内无序
SELECT * FROM table_name WHERE a > 1 AND a < 3 AND b > 1;
-- a等值，b范围，a和b都能用到索引（a相同时b有序）
SELECT * FROM table_name WHERE a = 1 AND b > 3;

15 前缀索引

对长字符串字段创建索引时，为了节省空间，可以只对字段值的前 N 个字符创建索引：CREATE INDEX idx_name ON table_name (column_name(N));。

16 百万级数据如何删除？

删除数据的速度与索引数量成正比。高效的做法是：先删除索引 -> 删除无用数据 -> 再重新创建索引。

17 普通索引和唯一索引怎样选？

• 查询比较：两者性能几乎一致。因为数据以页为单位加载进内存，唯一索引找到即返回，普通索引多一次指针遍历，但这个成本在内存中微乎其微。
• 更新比较：
  • 唯一索引需要保证唯一性，必须将数据页读入内存检查，无法利用 Change Buffer。
  • 普通索引可以利用 Change Buffer：当要更新的数据页不在内存中时，将更新操作缓存在 Change Buffer，等下次查询该数据页时，再合并（merge）更新。这显著减少了写操作的磁盘I/O。
• 适用场景：写多读少的场景，普通索引更有优势，能利用 Change Buffer 提升性能。若写入后立刻会读，则 Change Buffer 反而可能成为负担。

事务、隔离机制、日志、MVCC与锁

18 一条SQL查询语句执行过程（MySQL架构）

MySQL分为 Server层 和 存储引擎层。Server层包含连接器、分析器、优化器、执行器等。

一条查询SQL的执行流程：

1. 连接器：管理连接，进行身份验证和权限校验。
2. 查询缓存（如果开启）：检查缓存中是否有该SQL的结果集（Key为SQL语句）。若有，直接返回。
3. 分析器：进行词法分析和语法分析。
4. 优化器：生成执行计划，选择索引，决定最优执行方案。
5. 执行器：调用存储引擎接口，逐行判断是否满足条件，将满足条件的行组成结果集返回。若使用索引，则按索引筛选。

19 两阶段提交（一条更新语句怎么执行？）

以更新语句为例，过程涉及 redo log 和 binlog 的两阶段提交：

1. 执行器让存储引擎查找满足条件的行。行在内存则直接返回，否则从磁盘读入后返回。
2. 执行器执行更新操作，然后调用引擎接口写入新数据。
3. 引擎将新数据更新到内存中，同时将更新操作写入 redo log，此时 redo log 处于 prepare 状态。告知执行器执行完成，随时可提交事务。
4. 执行器生成该操作的 binlog，并将 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口。引擎把刚刚写入的 redo log 状态改为 commit。更新完成。

20 MySQL的事务原理

事务：一组要么全部成功、要么全部失败的操作集合。

ACID特性：

1. 原子性 (Atomicity)：操作要么全做，要么全不做。
2. 隔离性 (Isolation)：事务提交前，其结果对其他事务不可见。
3. 一致性 (Consistency)：事务总是使数据库从一个一致性状态转变到另一个一致性状态（如转账前后总额不变）。
4. 持久性 (Durability)：事务提交后，其对数据的修改是永久性的。

事务并发问题：

1. 脏读：读到了其他事务未提交的数据。
2. 不可重复读：同一事务内，两次读取同一数据，结果不一致（侧重数据被修改）。
3. 幻读：同一事务内，两次按相同条件查询，第二次看到了其他事务新插入的行（侧重数据行数变化）。

隔离级别及原理：

1. 读未提交：直接读取最新数据，未解决任何并发问题。
2. 读已提交：通过 MVCC 实现，每次语句执行前都生成一个新的 Read View。解决了脏读。
3. 可重复读（默认）：通过 MVCC 实现，仅在事务开始时生成一个 Read View，后续全用这个视图。解决了脏读、不可重复读。快照读（普通SELECT）使用MVCC防幻读；当前读（SELECT ... FOR UPDATE, UPDATE, DELETE, INSERT）通过间隙锁防幻读。
4. 可串行化：通过加锁实现，读加共享锁，写加排他锁，强制事务串行执行。

总结：读已提交和可重复读的核心区别在于 Read View 的生成时机。可重复读在事务开始时生成一个，读已提交每次查询前都生成新的。

21 ACID实现原理

• 原子性：通过 undo log 实现。记录事务开始前的旧数据版本，用于回滚。
• 隔离性：主要通过 MVCC 实现。
• 持久性：通过 redo log 实现。记录事务对数据的修改，用于故障恢复（Crash-safe）。

22 幻读问题详解

实验表：

CREATE TABLE `tx` (
  `age` int(5) DEFAULT NULL,
  `name` varchar(5) DEFAULT NULL,
  `id` int(5) NOT NULL PRIMARY KEY
) ENGINE=InnoDB;
INSERT INTO `tx` VALUES ('20', '张三', '1');
INSERT INTO `tx` VALUES ('20', '李四', '2');

实验现象（基于可重复读隔离级别）：

结论：

1. 在RR级别下，只使用快照读或只使用当前读，不会出现幻读。
   • 快照读依靠MVCC读取历史版本。
   • 当前读依靠间隙锁阻塞其他事务的插入。
2. 先快照读，后当前读，期间有其他事务插入符合条件的数据并提交，则当前读会发生幻读（更新到了新插入的数据）。

白话解释：事务1先查age=20有2条数据（快照读）。事务2插入一条age=20的数据并提交。事务1再更新age=20的数据（当前读），会发现更新了3行，把事务2新插的那行也更新了。

总结：RR级别并未完全解决幻读。仅当“先快照读，后当前读”时可能发生。

23 MVCC原理

多版本并发控制。核心原理：版本链 + Read View。

版本链：
InnoDB每行数据有隐藏字段：row_id、trx_id（最近修改它的事务ID）、roll_pointer（回滚指针）。

• 每次事务更新数据时，都会生成一个新的版本，trx_id设为当前事务ID，roll_pointer指向旧版本，形成一条链表。
• undo log 存储这些旧版本数据，用于回滚（原子性）和构建MVCC版本链。

Read View（一致性视图）：

• 读已提交：每次查询前生成一个新的Read View。
• 可重复读：事务开始时生成一个Read View，后续沿用。
• Read View 包含：m_ids（生成视图时活跃事务ID数组）、min_trx_id（低水位）、max_trx_id（高水位，下一个将分配的事务ID）。

数据可见性规则：
根据数据行版本的 trx_id 与当前事务的 Read View 对比：

1. 若 trx_id < min_trx_id（在绿色区），说明该版本在视图生成前已提交，可见。
2. 若 trx_id >= max_trx_id（在红色区），说明该版本由将来启动的事务生成，不可见。
3. 若 min_trx_id <= trx_id < max_trx_id（在黄色区）：
   • 若 trx_id 在 m_ids 数组中，说明该版本由未提交事务生成，不可见。
   • 若 trx_id 不在 m_ids 数组中，说明该版本由已提交事务生成，可见。

若不可见，则通过 roll_pointer 取上一个历史版本，重新进行上述判断，直到找到可见的版本。

24 日志机制分析

为保证事务原子性和一致性，MySQL采用 WAL（Write-Ahead Logging） 机制：先写日志，适当时机再写磁盘。

undo log：

• 作用：记录事务开始前的数据旧版本，用于回滚（保证原子性）和实现MVCC。

redo log：

• 物理日志。记录事务对数据页的物理修改。
• 作用：提供 Crash-Safe 能力。即使数据库异常重启，也能通过redo log恢复已提交的事务。
• 特性：空间固定，循环写入。有 write pos（当前写入位置）和 checkpoint（待擦除位置）两个指针。
• 写入流程：redo log buffer -> (write) page cache -> (fsync) 磁盘。
• 刷盘策略（innodb_flush_log_at_trx_commit）：
  • 0：事务提交时只写到 redo log buffer。
  • 1：事务提交时直接 fsync 持久化到磁盘（推荐）。
  • 2：事务提交时只 write 到 page cache。

binlog：

• Server层日志，用于主从复制和数据恢复。
• 三种格式：
  • STATEMENT：记录SQL语句。可能导致主从不一致（如使用函数 NOW()）。
  • ROW：记录每行数据的变化（前镜像/后镜像）。数据安全，但日志量大。
  • MIXED：混合模式，由MySQL判断选择STATEMENT或ROW。
• 写入机制：事务执行中日志写入 binlog cache，提交时一次性写入 binlog 文件。
  • write：写到page cache。
  • fsync：持久化到磁盘。
• 刷盘策略（sync_binlog）：
  • 0：只 write，不 fsync。
  • 1：每次提交都 fsync（推荐，与 innodb_flush_log_at_trx_commit=1 构成“双1配置”）。
  • N：累积N个事务后再 fsync。

两阶段提交：
为了保证 redo log 和 binlog 的逻辑一致性，事务提交采用两阶段提交（2PC）。

1. Prepare阶段：InnoDB写redo log，状态标记为 prepare。
2. Commit阶段：写binlog，然后InnoDB将redo log状态标记为 commit。

崩溃恢复逻辑：

• 如果 redo log 里事务有 commit 标记，直接提交。
• 如果 redo log 里事务只有 prepare 标记，则检查 binlog：
  • 若对应 binlog 完整，则提交事务。
  • 否则，回滚事务。

25 Explain分析

• type（访问类型，性能从好到差）：
  • system / const：常量级。
  • eq_ref：唯一索引扫描。
  • ref：非唯一索引扫描。
  • range：索引范围扫描。
  • index：全索引扫描。
  • ALL：全表扫描。
• possible_keys：可能使用的索引。
• key：实际使用的索引。
• key_len：使用的索引长度（字节数）。
• rows：预估需要扫描的行数。
• Extra：
  • Using index：使用覆盖索引，无需回表。
  • Using where：在存储引擎检索行后，Server层再进行过滤（可能回表）。
  • Using filesort：需要额外的排序操作（可能未用索引排序）。
  • Using temporary：使用了临时表。

26 脏页？怎样刷新脏页？

脏页：内存中的数据页已被修改，但与磁盘上的数据页不一致。

刷脏页的触发时机：

1. redo log 写满：停止所有更新，将 checkpoint 向前推进，推进部分的脏页刷盘。
2. 系统内存不足：需要淘汰数据页时，如果是脏页，必须先刷盘才能淘汰。
3. MySQL 空闲时。
4. MySQL 正常关闭前。

27 MySQL调优篇

27.1、一条SQL执行很慢的原因？

分两种情况讨论：

一、偶尔很慢

1. 数据库正在刷新脏页（如redo log满）。
2. 执行时遇到锁（表锁、行锁）。
3. SQL本身写得不好，如未用索引、索引失效。

二、一直很慢

1. 没用到索引/索引失效（见第10点）。
2. 有索引但走了全表扫描：可能因为索引统计信息（基数）不准确，导致优化器误判。可用 ANALYZE TABLE 重新统计。

27.2、SQL优化（定位与处理慢查询）

（1）开启慢查询日志

• 修改配置文件 my.cnf：

  [mysqld]
  slow_query_log = ON
  slow_query_log_file = /path/to/slow.log
  long_query_time = 2
  log_queries_not_using_indexes = ON

• 或动态设置：

  SET GLOBAL slow_query_log = 'ON';
  SET GLOBAL long_query_time = 2;

（2）分析慢查询日志
使用 mysqldumpslow 等工具分析慢日志文件。或使用 SHOW PROCESSLIST 命令实时查看正在执行的慢SQL。

（3）使用Explain分析执行计划
对定位到的慢SQL，使用 EXPLAIN 分析其执行计划（见第25点）。

（4）针对性优化

• 索引优化
  1. 尽量使用覆盖索引。
  2. 遵循最左前缀原则建立和使用联合索引。
  3. 避免索引失效的写法。
  4. 在写多读少的场景，可考虑使用普通索引代替唯一索引以利用 Change Buffer。
  5. 遵循索引设计原则（见第9点）。
• SQL语句优化
  1. 分页优化：对于自增主键表，将 LIMIT m,n 改为 WHERE id > xxx LIMIT n。
  2. INSERT优化：批量插入、使用事务、按主键顺序插入。
• 数据库结构优化
  1. 字段过多的大表可考虑垂直拆分。
  2. 对于常关联查询的表，可考虑建立中间表。
• 优化器优化
  • 启用 MRR (Multi-Range Read)：SET optimizer_switch='mrr=on';。它将随机磁盘读转化为顺序读，减少IO。
• 架构优化
  • 采用读写分离（主库写，从库读），这是应对高并发读的常用方案。关于高并发场景下的数据库架构设计，涉及更多系统性的思考。

总结优化流程：设慢查询 -> 分析日志 -> 定位SQL -> Explain分析 -> 从索引、SQL、结构、优化器、架构等多层面优化。

28 主从同步

核心：主库的 binlog + 从库的 relay log + 三条线程。

原理：

1. 主库的 binlog dump 线程将 binlog 事件发送给从库。
2. 从库的 I/O线程 接收并写入 relay log（中继日志）。
3. 从库的 SQL线程 读取 relay log 并重放其中的事件，从而保持与主库数据一致。

同步策略：

• 全同步复制：主库需等待所有从库执行完事务才返回。性能差，可用性低。
• 半同步复制（常用）：主库只要收到至少一个从库的ACK确认，即可认为操作成功。在数据一致性和性能间取得平衡。

29 高可用架构

经典M-S（主-备）结构：

客户端操作主库A，备库B同步A的更新。建议将备库设为只读模式。

主备延迟：

• 定义：同一事务，在备库执行完成的时间戳与主库执行完成的时间戳之差。
• 主要原因：备库消费 relay log 的速度慢于主库生产 binlog 的速度。
• 来源：
  1. 备库机器性能差。
  2. 备库承担了大量读请求，消耗资源。
  3. 主库执行大事务，传导到备库执行时间也长。

主备切换策略：

• 可靠性优先策略（常用）：
  1. 判断备库延迟 seconds_behind_master 小于设定值（如5秒）。
  2. 将主库A设为只读（readonly=true），此时系统短暂不可写。
  3. 等待备库B延迟变为0。
  4. 将备库B设为可写（readonly=false），业务请求切至B。

备库并行复制：
为解决备库单线程回放慢的问题，采用多worker线程并行回放relay log。

• coordinator 负责读取和分发事务。
• 多个 worker 线程并发执行事务。
• 分发规则：保证更新同一行的事务和同一事务内的语句必须分到同一个worker，以维持数据一致性。

30 总结

本文系统梳理了MySQL面试中从基础概念到高级原理的30个核心问题，涵盖存储引擎、索引、事务隔离、MVCC、日志体系、性能调优及高可用架构等关键领域。理解这些问题，不仅能帮助你在面试求职中游刃有余，更能深化对MySQL内部运作机制的认识，为实际工作中的数据库设计、优化和故障排查打下坚实基础。希望这份凝结了实战经验的笔记能对你有所助益。欢迎到云栈社区交流探讨更多技术细节。