10（上） MySQL-面试题

10.1 MySQL

10.1.1 SQL的执行

【必问】SQL语句在数据库中执行的详细全部步骤是什么？

1. 连接器（Connector）：建立连接、身份验证、权限校验

2. 查询缓存（Query Cache）【MySQL 8.0 已移除】

3. 解析器（Parser）：词法分析 + 语法分析

4. 优化器（Optimizer）：执行计划生成与优化

5. 执行器（Executor）：执行 SQL，与存储引擎交互

连接器是做什么的？

作用：

• 负责处理客户端连接请求

• 完成 用户身份认证（用户名/密码）

• 进行 权限校验（如是否有某个库表的查询/更新权限）

• 维护 连接状态、线程管理、资源控制

流程：

1. 客户端（如 MySQL CLI、JDBC、应用程序）向 MySQL Server 发起 TCP 连接请求

2. MySQL 的 连接器（通常是 mysqld 中的连接线程） 接收请求

3. 验证用户名 + 密码是否正确（可能涉及 SSL/TLS 加密）

4. 校验该用户对目标数据库、表是否有相应操作权限（SELECT / INSERT 等）

5. 连接成功后，分配一个 线程 来处理该客户端请求，并保持连接（除非使用连接池短连接）

解析器是做什么的？

作用：

• 对 SQL 语句进行 词法分析 和 语法分析

• 判断 SQL 写得是否合法（比如关键字是否正确、表名/字段是否存在、语法是否符合规则）

流程：

1. 词法分析（Lexical Analysis）：将 SQL 字符串拆解成一个个 Token（标记），比如关键字、表名、字段名、操作符等

2. 语法分析（Syntax Analysis）：根据 MySQL 的语法规则，判断这条 SQL 是否合法，生成一个 抽象语法树

优化器是做什么的？

作用：

• 在 SQL 语法正确的基础上，决定这条 SQL 如何执行才是最高效的

• 包括：

  • 选择使用哪个索引

  • 表的连接顺序（多表查询时）

  • 是否使用临时表、是否排序、是否使用聚合优化等

流程：

1. 生成多个可能的执行计划（Execution Plan）

2. 通过成本模型（Cost Model）估算每个计划的代价（如 I/O 次数、CPU 消耗、内存使用等）

3. 选择代价最低的执行方案，生成最终的执行计划

执行器是做什么的？

作用：

• 根据 优化器生成的执行计划，真正执行 SQL 语句

• 负责与 存储引擎（如 InnoDB、MyISAM）交互，获取或修改数据

• 控制事务、权限、行级访问控制等

流程：

1. 执行器先检查当前用户是否有执行该操作的权限（如 SELECT / UPDATE）

2. 按照执行计划，调用存储引擎的 API，开始逐条处理数据

   1. 比如执行 SELECT，则从存储引擎逐行读取数据并返回

   2. 执行 INSERT/UPDATE/DELETE，则修改数据，并写 Undo Log / Redo Log / Binlog（根据事务情况）

3. 对于 有 WHERE 条件的查询，执行器与存储引擎配合，逐行判断是否满足条件

4. 将结果集返回给客户端（逐行或批量返回）

MySQL的存储引擎有哪些？它们之间有什么区别？

MySQL 是一个支持多存储引擎（Storage Engine）架构的数据库管理系统，这也是它高度灵活和可定制的一个重要特性。

• InnoDB：默认引擎。支持事务、行级锁、外键、崩溃恢复，适合大多数业务场景。

• MyISAM：不默认引擎。不支持事务、表级锁，查询性能高，适合读多写少、不要求事务的场景。

• Memory：不默认引擎。数据存储在内存中，速度快，但重启数据丢失，适合临时表、缓存。

MySQL在设计表（建表）时需要注意什么？

1. 遵循数据库三范式（3NF），但也要适度权衡

2. 选择合适的数据类型

3. 主键设计要合理

4. 合理使用索引（加速查询，但不要滥用）

5. 避免 NULL 值滥用

6. 考虑表的分库分表与扩展性

MySQL中的数据排序是怎么实现的？

• 虽然数据存储在 InnoDB 等存储引擎中，但 ORDER BY 的排序逻辑是由 MySQL 的 Server 层负责执行的

• 存储引擎负责返回数据，Server 层负责按照 ORDER BY 的要求进行排序

MySQL 在执行排序时，会根据 待排序数据量的大小 选择不同的排序策略：

• 如果待排序的数据量较小，能全部放入内存的排序缓冲区（sort_buffer）中，MySQL 会直接在内存中使用 快速排序算法（QuickSort） 进行排序。

• 如果待排序的数据量很大，超过了 sort_buffer 的大小，MySQL 会把数据分成多份，一部分在内存排序，一部分写入磁盘的 临时文件。最后将这些临时文件通过 归并排序（merge sort） 合并成最终有序的结果。这个过程是排序性能瓶颈的常见来源。

如何优化MySQL的排序性能？

利用索引避免排序（最佳方案）：如果 ORDER BY 的字段正好是索引的一部分（特别是联合索引的最左前缀），MySQL 可能直接利用索引的有序性，避免额外的排序操作！

10.1.2 索引

MySQL索引是什么？

MySQL索引是一种特殊的数据结构，它类似于书籍的目录，能够帮助数据库系统快速定位到表中的特定数据行，而不必（一行一行地）扫描整个表。

索引本质上是对数据库表中一列或多列的值进行排序的一种结构，它存储了索引列的值和指向对应数据行的指针。

MySQL为什么需要索引？

1. 提高查询速度：索引能显著加快数据检索速度。没有索引时，MySQL必须执行全表扫描（逐行检查）来找到匹配的记录；有了索引，MySQL可以直接定位到相关数据。

2. 加速表连接：在多表关联查询时，索引可以加速JOIN操作，特别是当连接条件列上有索引时。

3. 保证数据唯一性：通过创建唯一索引(UNIQUE INDEX)，可以确保表中某列（或列组合）的值不重复。

4. 加速排序和分组：索引已按特定顺序存储数据，对于ORDER BY和GROUP BY操作，如果排序字段有索引，可以避免临时排序操作。

索引很好，但有何代价？

1. 占用额外存储空间：索引需要存储空间

2. 降低写入速度：INSERT、UPDATE、DELETE操作需要同时更新索引，会变慢

3. 维护成本：索引越多，数据库维护成本越高

【必问】MySQL索引的数据结构是什么？

B+树（B+ Tree） 是 B树（B-Tree）的一种变种，它在数据库管理系统（如 MySQL 的 InnoDB）、文件系统等需要高效磁盘存储与检索的场景中被广泛使用，尤其适合做索引结构。

它具有如下性质：

1. 非叶子节点（索引节点）

   1. 每个非叶子节点最多有 m 个子节点，即最多有 m - 1 个键。

   2. 每个键起 索引作用，用于指引去哪个子节点查找。

   3. 非叶子节点不存储实际数据，只存储键和指向子节点的指针。

   4. 子节点的键范围是：左子树所有键 ≤ 当前键 < 右子树所有键。

2. 叶子节点

   1. 所有的数据记录（或数据指针）都存储在叶子节点中。

   2. 叶子节点也是有序排列的（通常是按主键升序）。

   3. 叶子节点之间通过指针连接，形成一个双向链表（或单向链表），方便范围扫描。

   4. 每个叶子节点可以存储多个键值对（具体数量取决于节点大小和阶数 m）。

3. 所有叶子节点都在同一层，保证平衡。

B+树 vs 哈希表做数据库索引时有什么优势？

哈希表：

• 不支持范围查询

• 不支持排序

• 哈希冲突会影响性能

B+树 vs 二叉树做数据库索引时有什么优势？

二叉树：

• 每个节点最多有两个子节点，树太高

• 在最坏情况下（数据有序插入）会退化为链表，查询效率变为O(n)，而B+树保持平衡

B+树 vs 平衡二叉树做数据库索引时有什么优势？

平衡二叉树：

• 每个节点最多有两个子节点，树太高

• 为了平衡，不断旋转，影响性能

B+树 vs 红黑树做数据库索引时有什么优势？

红黑树特点：

• 虽然平衡了，且不算特别平衡、不会一直旋转，但由于是二叉，树还是会太高

• 叶子节点无联系，不便于范围查询

B+树 vs B树做数据库索引时有什么优势？

B树：

• 数据既存储在内部节点也存储在叶子节点，存储量太大

• 叶子节点无联系，不便于范围查询

B+树 vs 跳表做数据库索引时有什么优势？

跳表：

• 跳表的节点通常是随机分布的链表节点，难以有效组织以匹配磁盘块大小

• 树高度相对较高

MySQL三层B+树能存多少数据？

在 MySQL InnoDB 中，一个三层的 B+ 树索引（通常对应 1 个索引，比如聚簇索引），在默认 16KB 页大小、合理行大小的情况下，可以存储大约 几千万 到 1 亿 ~ 1.5 亿 条左右的数据记录。

【必问】MySQL聚集索引和非聚集索引是什么？

聚集索引（Clustered Index）：

• 聚集索引决定了表中数据的物理存储顺序。在MySQL的InnoDB存储引擎中，表数据本身就是按照聚集索引的顺序来存储的，即聚集索引的叶子节点直接包含完整的行数据。

• 表中只能有一个聚集索引。

• 数据行实际上存储在索引的叶子页中。

非聚集索引（Non-clustered Index / Secondary Index）：

• 非聚集索引是独立于数据存储结构的索引，它不决定数据的物理存储顺序，其叶子节点包含的是指向数据行的引用（在InnoDB中通常是聚集索引的键值）。

• 表中可以有多个非聚集索引。

• 叶子节点不直接包含完整的数据行，而是包含指向数据行的信息。在InnoDB中，非聚集索引的叶子节点存储的是对应行的主键值（而不是直接的数据地址）。

• 当通过非聚集索引查询数据时，先找到主键值，然后再通过主键值到聚集索引中查找完整数据（这个过程称为回表）。

MySQL非聚集索引如何避免回表？

非聚集索引其实不一定会回表的。如果查询的所有列都包含在索引中，MySQL就可以直接从索引中获取所需数据而无需回表，这称为覆盖索引。

例如你查select *，那就避免不了回表了，但如果你查的列刚好就是索引（即使是非聚集索引），那也不会回表。

MySQL联合索引是什么？

联合索引（Composite Index / 复合索引 / 组合索引）是指在多个列上创建的单一索引，即一个索引包含多个字段。到时候你查的时候查这个联合索引，就不会回表。

【必问】最左前缀原则是什么？

联合索引遵循最左前缀原则（Leftmost Prefix Principle），这意味着索引按照索引定义中列的顺序，从左到右依次使用。

索引中的数据是按照索引列的顺序进行排序的，首先是第一列排序，然后是第一列相同的按照第二列排序，以此类推。

以索引(A, B, C)为例，数据实际上是按照以下顺序排序的：

1. 先按A列排序

2. A列相同的值，再按B列排序

3. A列和B列都相同的值，再按C列排序

最左前缀原则：

1. 查询条件必须包含联合索引的最左边列，才能有效利用该联合索引。

2. 联合索引的列顺序非常重要，应该将最常用作查询条件或范围查询的列放在前面，或者按照查询频率和选择性排序。

3. 等值查询列在前，范围查询列在后。

【必问】MySQL索引失效的情况？

1. 违反最左前缀原则（针对联合索引）

2. 在索引列上使用函数或运算

3. 使用不等于(!= 或 <>)操作符

4. 使用LIKE以通配符（%）开头，以通配符结尾索引不会失效

5. 类型转换导致索引失效（隐式触发转换函数）

MySQL的索引下推是什么？

索引下推（ICP） 是将 WHERE 条件中 “与索引相关部分的过滤操作” 下推到存储引擎层进行过滤，而不是在服务器层（Server 层）才过滤，从而减少不必要的数据读取和回表操作。

1. 存储引擎 在利用索引查找数据的同时，还会进一步根据索引中的字段，判断是否满足 WHERE 条件（即：把 WHERE 条件中 “与索引相关” 的部分下推到存储引擎层进行过滤）

2. 只有那些满足条件的索引记录，才会被返回给 Server 层

3. Server 层收到的数据已经是经过索引层筛选后的更少、更精确的数据，减少了回表和无效数据的处理

索引下推的优点：存储引擎利用索引中的字段提前过滤，减少了不必要的数据读取和回表，提升了性能。

MySQL如何科学地加索引？

1. 索引不是越多越好

2. 索引应该针对查询模式设计，关注高频查询

3. 使用日志识别慢查询、使用EXPLAIN分析查询执行计划，找到需要加索引的列

4. 选择记录重复概率低的列

5. 对较长的字符串增加前缀索引

LSM-tree索引如何解决数据库写多读少的问题？

LSM-Tree（Log-Structured Merge Tree）是一种为优化写性能而设计的索引结构，核心理念是：

“先写日志（顺序写），再异步合并排序（批量写磁盘）”，从而将随机写转化为顺序写，大幅提升写入吞吐量。

随机写：每次都先找位置，找到了就地写。

顺序写：先一直追加，再合并。

10.1.3 事务/MVCC

事务是什么？为什么要有事务？

MySQL事务(Transaction)是一组不可分割的SQL操作序列，这些操作要么全部成功执行，要么全部不执行。事务是数据库管理系统执行过程中的一个逻辑单位。

在MySQL中，只有使用InnoDB存储引擎的表才支持事务。

事务在数据库操作中非常重要，主要原因包括：

1. 保证数据完整性：确保一组相关操作要么全部成功，要么全部失败，避免数据处于不一致状态。

2. 处理并发问题：在多用户同时访问数据库时，事务隔离级别可以控制并发事务之间的相互影响程度。

3. 提供故障恢复机制：事务的持久性保证了即使系统崩溃，已提交的事务结果也不会丢失。

4. 简化错误处理：通过事务的回滚机制，可以方便地处理操作过程中出现的错误，将数据库恢复到操作前的状态。

【必问】事务的四个基本特性是什么？分别是怎么实现的？

1. 原子性(Atomicity)

   1. 定义：原子性指事务是一个不可分割的工作单位，事务中的操作要么全部成功执行，要么全部不执行。

   2. 实现原理：InnoDB通过undo log(回滚日志)实现原子性。当一个事务对数据库进行修改时，InnoDB会先记录修改前的数据到undo log中。如果事务执行失败或调用了ROLLBACK，InnoDB会根据undo log中的信息将数据回滚到修改前的状态。

2. 一致性(Consistency)

   1. 定义：一致性指事务必须使数据库从一个一致性状态变换到另一个一致性状态。即事务执行前后，数据都保持有效状态。

   2. 实现原理：一致性是事务的最终目标，它实际上是由其他三个特性(原子性、隔离性、持久性)共同保证的结果。

3. 隔离性(Isolation)

   1. 定义：隔离性指多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

   2. 实现原理：InnoDB通过锁机制和MVCC(多版本并发控制)实现隔离性。

4. 持久性(Durability)

   1. 定义：持久性指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，即使数据库发生故障也不应该对其有任何影响。

   2. 实现原理：InnoDB通过redo log(重做日志)实现持久性。先写日志，再写磁盘。两阶段提交。数据库重启时，会检查redo log中的事务，确保数据不丢失。

【必问】并发条件下脏读、幻读、不可重复读分别是什么？

1. 脏读(Dirty Read)：脏读是指一个事务读取了另一个未提交事务修改过的数据。

2. 不可重复读(Non-repeatable Read)：不可重复读是指在同一事务内，多次读取同一数据返回的结果不同，这是因为在事务执行期间，其他事务修改了该数据并提交了。（同一数据变化）

3. 幻读(Phantom Read)：幻读是指在同一事务内，连续执行两次相同的查询，但第二次查询返回了第一次查询没有返回的"幻影行"，这是因为其他事务插入或删除了符合查询条件的数据并提交了。（行数）

【必问】数据库隔离级别有哪几种？每种级别解决哪种问题？

1. 读未提交(Read Uncommitted)

   1. 隔离级别最低，并发性能最高，但数据一致性最差。

   2. 允许一个事务读取另一个未提交事务修改的数据。

   3. 可能发生脏读(Dirty Read)、不可重复读(Non-repeatable Read)和幻读(Phantom Read)。

2. 读已提交(Read Committed)

   1. 中等隔离级别，平衡了并发性能和数据一致性。

   2. 一个事务只能读取另一个已提交事务修改的数据。

   3. 可能发生不可重复读(Non-repeatable Read)和幻读(Phantom Read)。

3. 可重复读(Repeatable Read)

   1. MySQL默认隔离级别，提供较高数据一致性。

   2. 确保在同一事务内多次读取同一数据时，结果始终一致，即使其他事务在此期间修改了该数据。

   3. 可能发生幻读(Phantom Read)(但在MySQL的InnoDB存储引擎中实际上也防止了)。

4. 串行化(Serializable)

   1. 隔离级别最高，并发性能最低，但数据一致性最好。

   2. 最高的隔离级别，通过强制事务串行执行来避免并发问题。

   3. 防止脏读(Dirty Read)、不可重复读(Non-repeatable Read)和幻读(Phantom Read)。通过完全锁定相关数据或强制事务串行执行来实现。

MySQL MVCC是什么？做什么用的？

MVCC 的核心思想是：不对数据行直接加锁来进行读写控制，而是为数据的每个版本维护多个“快照”（版本），让读操作可以不阻塞写操作，写操作也可以不阻塞读操作。

简单来说，当多个事务并发访问同一数据时，MVCC 为每个事务提供某个时间点的数据快照，使得事务读取到的数据要么是它开始时的状态，要么是它可见的状态，而不会受到其他事务当前正在进行的修改的影响。

MySQL MVCC是怎么实现的？undo log、版本链、ReadView分别是什么？

MVCC 的实现依赖于以下几个关键技术：

1. Undo Log

   1. Undo Log 是数据库为了实现事务回滚、MVCC 等功能而保留的旧数据记录。

   2. 当某一行数据被修改（UPDATE 或 DELETE）时，数据库不会直接覆盖原有数据，而是先把旧值记录到 Undo Log 中，然后再更新当前数据行。

2. 版本链（Version Chain）

   1. 数据库为每行数据维护多个版本，每个版本包含数据内容以及一些元信息，比如：

      • 创建该版本的事务 ID（creator_trx_id）

      • 删除该版本的事务 ID（deleted_trx_id，可选）

      • 指向下一个版本的指针

   2. 这些版本通过链表形式组织在一起，形成“版本链”。

3. 事务 ID（Transaction ID）

   1. 每个事务在开始时会被分配一个唯一的、递增的事务 ID，用于标识事务的先后顺序。

4. ReadView（读视图）

   1. 当一个事务执行读操作时，会生成一个“读视图”，用来判断哪些版本的数据是当前事务可见的。

   2. ReadView 包括：

      • 当前活跃的事务 ID 列表（即尚未提交的事务）

      • 当前事务自己的 ID

      • 最小事务 ID（min_trx_id）

      • 最大事务 ID（max_trx_id）

   3. 通过比较数据版本的事务 ID 与 ReadView 中的信息，MVCC 决定哪个版本的数据是当前事务可以读取的。

当前读、快照读分别是什么？

1. 快照读（Snapshot Read）：快照读是指读取的是事务开始时（或某个时间点）的数据库数据快照，即历史版本的数据，而不是当前最新的数据。简单来说：你读到的数据，是事务开启那一刻的“那一刻”的数据状态，不受其他事务后续修改的影响。

2. 当前读（Current Read）：当前读是指读取的是数据的最新版本，而且是“当前时刻”数据库中最新的数据状态，也就是其他事务已经提交了的最新修改。通常需要加锁，以保证数据的一致性。

【必问】MySQL四种事务隔离级别分别如何使用MVCC实现？

1. 读未提交(Read Uncommitted)

   1. 几乎不使用 MVCC 来隔离数据。

2. 读已提交(Read Committed)

   1. 每次执行 快照读（普通 SELECT） 时，InnoDB 都会生成一个新的 ReadView。

   2. 这个 ReadView 反映的是当前时刻所有已提交事务的状态。

   3. 因此，同一事务内多次读取，可能得到不同的结果（因为每次读的视图不同，看到的提交状态也不同）。

3. 可重复读(Repeatable Read)

   1. 事务第一次执行快照读（如普通 SELECT）时，生成一个 ReadView，并在整个事务期间复用这个视图。

   2. 后续所有的快照读，都基于这个同一个 ReadView 去版本链中查找数据，因此多次读取结果一致。

   3. 即使其他事务提交了新的修改，当前事务也看不到这些新提交的内容。

4. 串行化(Serializable)

   1. 不使用 MVCC 来隔离数据，直接加锁。

MySQL如何使用MVCC在可重复读的级别下避免幻读？

select：使用快照读+ReadView给你最初版本的数据。

update、insert等操作：使用当前读+临键锁，阻塞住其他的事务阻止其对数据进行操作。

临键锁：锁定的是索引记录本身（行锁）、以及该记录前面的间隙（间隙锁）。

间隙锁：不锁定具体的行，而是锁定某个范围（gap），比如 id 在 (2, 5) 之间的所有“潜在位置”。

（追问）什么时候幻读依然可能发生？

• 如果你没有使用索引，或者查询没有走索引，可能导致锁的范围变大甚至表锁，影响性能。

• 如果你只做普通 SELECT（不加 FOR UPDATE / LOCK IN SHARE MODE），且没有触发当前读，那么也不会加 Gap 锁，此时幻读是有可能发生的（但极少场景）。

10.1.4 日志

MySQL undo log是什么？

1. 事务回滚：如果事务最终回滚（rollback），InnoDB 就可以利用 Undo Log 中记录的旧数据，将数据恢复到事务开始之前的状态。

2. 实现 MVCC（多版本并发控制）：当一个事务读取某行数据时，如果该行数据正在被其他事务修改，InnoDB 可能不会直接读取最新数据，而是根据事务的隔离级别，从 Undo Log 中找到一个适合该事务看到的“历史版本”数据，从而实现一致性非锁定读。

3. 支持事务的崩溃恢复：在数据库异常崩溃并重启后，Undo Log 也有助于将未提交事务的修改回滚掉，确保数据库的一致性。

（追问）undo log有哪些类型？

• INSERT Undo Log：仅在事务回滚时需要，因为 INSERT 插入的数据在事务回滚时需要删除。这类 Undo Log 在事务提交后就可以很快被清理。

• UPDATE/DELETE Undo Log：不仅用于回滚，还用于 MVCC 提供一致性读的旧版本数据，因此其生命周期可能更长

MySQL Buffer Pool是什么？

MySQL 的 Buffer Pool（缓冲池） 是 InnoDB 存储引擎 的核心内存结构之一，它是数据库性能优化的关键所在。简单来说，Buffer Pool 是一块位于内存中的区域，用来缓存磁盘上的数据页（data pages）和索引页（index pages），以减少对磁盘的频繁 I/O 操作，提高数据库的访问速度。

MySQL 的数据是存储在磁盘上的（比如 .ibd 文件或系统表空间文件），但磁盘的访问速度远远慢于内存。为了提高数据访问效率，InnoDB 不会每次都直接从磁盘读取数据，而是先检查数据是否已经在内存中的 Buffer Pool 里，如果在，就直接读取；如果不在，才去磁盘加载进来，并放入 Buffer Pool 中缓存起来。

（追问）Buffer Pool 的基本工作原理是什么？

1. 按页管理：数据是以 页（Page） 为单位加载进 Buffer Pool 的，默认每页大小是 16KB。

2. LRU 算法管理缓存页：Buffer Pool 使用 改进的 LRU（Least Recently Used，最近最少使用）算法 来管理内存中的数据页。

3. 脏页（Dirty Page）与刷盘：InnoDB 后台有专门的线程（如 Flush List 线程 和 Page Cleaner 线程）定期或在合适的时机将这些脏页 刷写（flush）回磁盘，以保证数据持久化。脏页的刷新是异步进行的，不会阻塞事务的执行。

【必问】MySQL redo log是什么？

Redo Log 是 InnoDB 引擎在内存缓冲区（Buffer Pool）之外维护的一块固定大小的、顺序写入的磁盘日志文件，它记录了 对数据页所做的物理级别的修改（即“我修改了这个数据页的某个偏移处的值”），而不是记录 SQL 语句本身或者逻辑变化。

（追问）redo log如何解决Buffer Pool崩溃刷盘失败的问题？

在事务提交时，先将本次修改对应的 Redo Log 记录写入磁盘（确保落盘），然后再返回成功给客户端；而数据页的真正写入磁盘可以稍后异步进行。

这样，即使数据库在脏页尚未刷盘时崩溃，重启后 InnoDB 也可以通过 Redo Log 找到那些已经提交但尚未应用到磁盘的数据修改，然后 重做（redo）这些操作，恢复数据。

（追问）那如果连redo log都没来得及刷盘，MySQL就崩溃了，怎么办？

如果事务已经提交，但 Redo Log 还没刷盘，而此时 MySQL 崩溃了，那么在默认配置下，这种情况是不会发生的 —— 因为 InnoDB 一定会保证事务提交前，Redo Log 已经刷盘（落盘）。如果真的没刷盘，那说明这个事务实际上并未“真正提交成功”，也就不存在“丢失已提交数据”的问题。关键点：只有在 Redo Log 成功落盘后，事务才算提交成功。

（追问）redo log为什么不适合做备份恢复或者主从复制？

1. Redo Log 是物理日志，且是循环使用的（非持久保存）。日志文件写满后，会从第一个文件开始覆盖旧的日志内容。

2. Redo Log 是物理日志，不是逻辑日志。它 不关心具体是哪张表、哪行数据、执行了什么 SQL，只关心内存页的物理变化。备份恢复 和 主从复制通常需要逻辑可读、可理解的操作信息。

【必问】MySQL bin log是什么？为什么bin log可以用来备份恢复？

Binlog（Binary Log）是 MySQL Server 层提供的一种逻辑日志，记录了所有修改数据的 SQL 操作（或者数据变更的“事件”）。

特点：

• 属于 Server 层日志。不是 InnoDB 特有的，而是 MySQL Server 自己记录的，所有存储引擎（如 InnoDB、MyISAM）的变更都会记录到 Binlog。

• 记录的是 逻辑层面的数据变更，比如：INSERT INTO users VALUES(...)；UPDATE users SET name='Tom' WHERE id=1；或者更底层的行变更事件（Row 格式下）。

• 是追加写（append-only）的文件。不会覆盖，除非手动删除或配置过期策略。

• 可用于数据恢复与主从同步。是 MySQL 实现 增量备份恢复 和 主从复制 的基础。

MySQL万一出现问题导致redo log和bin log不一致怎么办？什么是redo log的两阶段提交？

为了保证 InnoDB 的 Redo Log 和 Server 层的 Binlog 数据一致，MySQL 引入了两阶段提交机制：将事务的提交过程拆分为两个阶段，确保 Redo Log 和 Binlog 要么都成功，要么都失败，避免出现不一致。

1. 阶段 1：Prepare（准备阶段）

   1. InnoDB 先将事务的 Redo Log 写入到 Redo Log Buffer

   2. 然后调用 fsync() 将 Redo Log 刷盘到磁盘（落盘），但此时 Redo Log 的状态是 "Prepare"（预备提交），不是最终提交

   3. 此时事务尚未完成提交，但修改已持久化到 Redo Log，具备崩溃恢复能力

2. 阶段 2：Commit（提交阶段）

   1. 接着，MySQL 会将事务对应的 Binlog 写入 Binlog 文件，并调用 fsync() 刷盘

   2. 最后，InnoDB 再将 Redo Log 的状态从 "Prepare" 更新为 "Commit"（提交完成）

      1. 这一步是在 Redo Log 中标记该事务已正式提交

   3. 至此，事务才算 真正提交成功，InnoDB 和 Server 层都完成了各自日志的写入

如果崩溃发生在不同阶段，MySQL 如何恢复？

MySQL 在 崩溃重启后，会进入 崩溃恢复（Crash Recovery） 阶段，此时会检查 Redo Log 中那些处于 Prepare 状态但未标记为 Commit 的事务，并根据 Binlog 的存在与否 来判断如何处理：

• 情况1：Redo Log 是 Prepare 状态，但 Binlog 不存在。说明事务的 Redo Log 写入了，但 Binlog 没有成功写入（可能在写 Binlog 前崩溃了）

  • 处理方式：回滚该事务！

• 情况2：Redo Log 是 Prepare 状态，且 Binlog 已存在。说明Binlog 已经成功写入（事务变更已同步到 Binlog），只是 Redo Log 还没标记为 Commit

  • 处理方式：提交该事务！

10.1.5 集群/分库分表

MySQL为什么要做分库分表？

1. 解决单库单表的数据量过大问题

   1. 存储瓶颈

   2. 性能下降，查询、写入变慢

2. 提高系统的并发处理能力

   1. 连接数限制

   2. I/O 和 CPU 瓶颈

如何在MySQL中实现读写分离？

注意：读写分离并不能替代数据库的水平拆分（如分库分表），它主要是针对读负载的优化。

工作流程：

1. 写请求（INSERT/UPDATE/DELETE）：都路由到 主库（Master）

2. 主库将数据变更通过 Binlog 同步到从库（Slave）（通过 MySQL 原生 主从复制机制）

3. 读请求（SELECT）：路由到 一个或多个从库（Slave），实现读负载均衡

4. 主从同步通常是异步的（MySQL 默认），也就是说从库的数据可能会稍微落后于主库（有一定延迟）。

MySQL的分库分表策略有哪几种？水平分库分表，垂直分库分表是什么？

1. 垂直拆分（分库）

   1. 定义：按照业务模块或功能，将不同的表拆分到不同的数据库中。

   2. 例子：将用户表、订单表、商品表分别放在不同的数据库中，比如 user_db、order_db、product_db。

2. 水平拆分（分表）

   1. 定义：将同一张表的数据按照某种规则（如用户ID、时间、哈希值等）拆分到多个结构相同的表中，甚至分散在不同的数据库实例上。

   2. 例子：将用户表按 user_id % 10 拆分成 10 张表：user_0 到 user_9，每个表只存一部分用户的数据。

MySQL分库分表的业务日志（如操作记录、行为日志、审计日志）如何与数据表一起拆分？

1. 日志与主表保持相同的分片规则

   1. 如果你的业务日志和某张业务表（如 order 表）强相关（比如订单操作日志），那么可以让日志表使用和主表相同的分片键和分片规则，存放在同一个分库分表中。

   2. 优点：

      • 日志和数据在同一个库表中，查询关联方便（比如通过事务保证一致性，或在同一分片查询）。

   3. 缺点：

      • 如果日志量非常大，可能会拖慢主业务表所在分库的性能。

      • 跨分片查询多个日志仍然复杂。

2. 日志独立存储，不分库分表 或 单独分库

   1. 将日志类数据（尤其是行为日志、流水日志）单独存放在一个独立数据库或大数据平台中，不与主业务库放在一起。

   2. 优点：

      • 避免日志数据影响主库性能。

      • 更适合海量日志的存储与分析。

   3. 缺点：

      • 与主表数据的关联查询可能较为复杂，需要通过唯一 ID（如 order_id、user_id）做关联。

      • 事务一致性较难保证（日志与主业务数据可能不在同一个事务中）。

MySQL主从复制的流程？

1. 主库开启 binlog 日志功能。

2. 配置从库连接主库。

3. 从库的I/O Thread线程，负责连接到主库，请求并拉取主库的 binlog 内容，并将其保存到本地的 relay log（中继日志）。

4. 从库上的SQL Thread线程，负责读取 relay log 中的事件，并在从库上重新执行这些 SQL 操作，从而使从库数据和主库保持一致。

MySQL分库分表后的事务一致性如何保证？

1. 使用分布式事务

   1. 这个详情见Seata专栏。

2. 最终一致性方案

   1. 如果业务可以接受 短时间内数据不一致，但最终达到一致状态，那么可以采用 最终一致性模型，通过异步消息队列等手段实现。

   2. 常见实现方式：消息队列（MQ）：如 RocketMQ、Kafka，支持事务消息（如 RocketMQ 的事务消息机制）。

MySQL跨库/跨表查询如何优化？

1. 冗余数据 / 数据同步（空间换时间）

   1. 将部分关键数据冗余存储到多个库/表中，或者通过异步同步方式，将查询需要的数据集中到同一个库中，避免跨库查询。

2. 使用分库分表中间件提供的跨库查询能力

   1. 目前主流的分库分表中间件，它们在一定程度上支持跨库查询，但通常有功能限制和性能代价。但通常不支持跨库 JOIN、复杂子查询、多表关联排序分页。

3. 使用专门的数据聚合层 / 数据仓库

   1. 将分库分表中的数据，通过 ETL 工具、Binlog 同步、消息队列 等方式，抽取到：MySQL 汇总库、Elasticsearch（适合检索）、Hive / HBase（大数据场景）等数仓，然后在这些专门的查询系统中执行复杂 SQL、聚合、排序、分页等操作。

如何使用分布式ID生成方案代替自增ID？

1. UUID（Universally Unique Identifier）：使用标准 UUID 算法（如 UUID.randomUUID()），生成一个 128 位的全球唯一字符串。

   1. 适用场景：非主键唯一标识，如 session ID、token、日志唯一标识等

   2. 不适用场景：MySQL 主键 / 高频写入表

2. 数据库自增ID + 多实例步长（号段模式 / 自增步长）：每个数据库实例设置不同的 自增起始值 和 步长（step）。或者采用 号段模式（Segment）：中心数据库维护一个 ID 池，应用每次获取一个号段（如 1~1000），用完再取下一个号段。

   1. 适用场景：中小规模系统，不想引入第三方组件

   2. 适用性有限，不推荐用于大规模分布式系统

3. Snowflake（雪花算法）：Snowflake 是一个 64位 long 型 ID，结构如下：| 1 bit 符号位 | 41 bit 时间戳 | 10 bit 工作机器ID | 12 bit 序列号 |，最终生成的 ID 是一个 有序的、趋势递增的 64 位 long 整数，非常适合做 MySQL 主键。

   1. 适用场景：绝大多数分布式系统，尤其是分库分表、微服务架构

   2. 注意点：需要解决时钟回拨问题（可用 NTP 同步 + 检测逻辑）

4. Redis 自增：利用 Redis 的 INCR 或 INCRBY 命令生成唯一递增 ID；可以做分片键前缀组合，如：业务前缀 + Redis自增ID。

   1. 适合对有序性要求高、并发强、不想用 Snowflake 的场景

   2. 强依赖 Redis，存在单点问题（可用 Redis 集群解决）

MySQL如何实现数据库的不停服迁移？

利用 MySQL 原生的 主从复制（Master-Slave Replication）机制，先让目标库作为源库的 从库（Slave），实时同步数据；在数据追上之后，切换应用连接至目标库（提升为 Master 或新主），从而实现无缝迁移。

迁移步骤（以同版本或兼容版本为例）：

1. 准备阶段

   1. 源库（Old Master）：当前正在提供服务的生产数据库

   2. 目标库（New Slave → New Master）：新的 MySQL 实例，可以是：

2. 将目标库配置为源库的从库（开启主从复制）

   1. 在源库上创建用于主从同步的账号（REPLICATION SLAVE 权限）

   2. 查看源库当前的 binlog 位置（执行 SHOW MASTER STATUS）

   3. 将目标库的数据与源库保持一致（初始数据同步）

      1. 方法一：使用 mysqldump 导出源库的完整数据（加 --master-data 参数记录 binlog 位置）

      2. 方法二：直接使用物理备份工具，如 Percona XtraBackup（推荐，支持热备、支持 InnoDB，几乎无阻塞）

   4. 将备份数据导入到目标库

   5. 在目标库配置主从同步

   6. 检查主从同步状态

3. 业务切换（停写切换 or 双写后切换）

   1. 方式一：停写切换（推荐在维护窗口进行）

      • 暂停源库的写入（或让应用停止写入）

      • 确认从库数据已完全同步（Seconds_Behind_Master = 0）

      • 将应用的数据库连接配置指向 目标库（原从库）

      • 恢复应用写入

   2. 方式二：双写 + 切换（更平滑，适合重要业务）

      • 应用同时写入源库和目标库（需业务层支持双写逻辑）

      • 确保目标库数据与源库一致

      • 逐步将读请求切到目标库

      • 最终停写源库，只读写目标库

4. 目标库提升为主库，源库可降级/关闭

10.1.6 锁

MySQL中的读锁 vs 写锁有什么区别？

读锁（Shared Lock / S 锁）：

• 读锁也称为共享锁，是允许多个事务同时持有的锁。当一个事务给某行数据或表加上读锁后，其他事务也可以加读锁，但不能加写锁。

• 多个事务可同时持有读锁：即多个事务可以同时读取同一份数据。

• 不允许其他事务加写锁：即其他事务不能修改被加了读锁的数据，直到所有的读锁都被释放。

• 持有读锁的事务自身也不能加写锁（除非显式解锁或事务结束）。

写锁（Exclusive Lock / X 锁）：

• 写锁也称为排他锁，是独占锁，当一个事务给数据加上写锁后，其他事务既不能加读锁，也不能加写锁，直到该写锁被释放。

• 排他性：只有一个事务可以持有某一资源的写锁。

• 阻止其他事务的读锁和写锁：其他事务无法读取（如果加了读锁）或修改该数据。

• 写锁是修改数据的前提：当你需要更新、删除或插入数据时，通常会自动加上写锁。

MySQL中的公平锁 vs 非公平锁有什么区别？

公平锁（Fair Lock）：

• 定义：按照线程（或事务）请求锁的顺序来分配锁资源，即先到先得（FIFO, First In First Out）。

非公平锁（Nonfair Lock）：

• 不保证先到先得，新来的请求可能会插队直接尝试获取锁，而不一定排队。

• InnoDB 的锁调度机制默认是 “非公平” 的，或者说 并没有严格保证先到先得的公平性。

但以下几点可以帮助你理解或间接控制锁的公平性：

• 防止饿死：如果某个事务长时间拿不到锁，可以通过设置 锁等待超时参数：innodb_lock_wait_timeout（默认 50 秒）。

• 干脆不锁：通过 MVCC（多版本并发控制） 实现读不加锁（快照读），写时再检查冲突，从而减少锁的使用和等待。

MySQL中的行锁 vs 表锁有什么区别？

表锁（Table Lock）：

• 锁定整张表，其他事务不能对该表进行冲突操作。

行锁（Row Lock）：

• 只锁定表中的某一行（或多行）数据，其他事务还可以操作表中未被锁定的行。

• InnoDB 是 MySQL 的默认存储引擎（5.5 版本之后），支持事务、行级锁、MVCC 等高级特性。

• 默认情况下，InnoDB 在执行 DML（如 UPDATE / DELETE / SELECT ... FOR UPDATE）时，会对操作的行自动加行锁。

MySQL中的记录锁 vs 间隙锁 vs 临键锁 vs 插入意向锁有什么区别？

在 MySQL 的 InnoDB 存储引擎 中，间隙锁、临键锁等锁是用于实现事务隔离级别（特别是可重复读 RR）和解决幻读问题的重要锁机制。它们都属于 行锁的扩展，但锁定的范围不仅仅是“行”，还可能包括行之间的“间隙”。

这些锁通常是在 有索引 的情况下起作用，如果查询没有使用索引，InnoDB 可能会锁表（或锁全表范围）！

• 记录锁（Record Lock）：就是行锁。

• 间隙锁（Gap Lock）：锁定索引记录之间的间隙（gap），但不包括记录本身，防止其他事务在间隙中插入新数据。

• 临键锁（Next-Key Lock）：记录锁 + 间隙锁的组合，锁定索引记录及其前面的间隙，InnoDB 默认的加锁方式（在 RR 级别下）。

• 插入意向锁（Insert Intention Lock）：插入数据时使用的特殊间隙锁，表示“想要插入到某个间隙中”。

【必问】MySQL中的乐观锁 vs 悲观锁有什么区别？

悲观锁（Pessimistic Locking）：

• 在读取或修改数据之前，先对数据加锁（通常是行锁或表锁），其他事务在此期间不能修改这些数据。

• 锁会在事务提交或回滚时释放。

• 适合并发冲突概率高、写多读少、数据一致性要求非常严格的场景。

乐观锁（Optimistic Locking）：

• 不主动加锁，允许多个事务并发读取和操作数据。

• 在更新数据时，通过一个版本号或时间戳等机制检查数据是否被修改过，如果发现数据已经变了，就认为发生了冲突。

• 常见的实现方式：版本号机制（version 字段）、时间戳（timestamp）、CAS（Compare And Set）思想。

• 适合读多写少、冲突概率较低的场景，比如商品浏览量、配置信息更新等。

10.1.7 数据库连接池

为什么需要数据库连接池？

在传统的数据库访问方式中，每当应用程序需要与数据库交互时，都会创建一个新的数据库连接，使用完毕后再关闭连接。但数据库连接的创建和销毁是比较耗时且消耗资源的操作。

数据库连接池的核心思想是“复用”：在应用程序启动时，预先创建一定数量的数据库连接，并将这些连接保存在一个“池”（pool）中。当应用程序需要访问数据库时，从池中获取一个已经建立好的连接，使用完毕后再将连接归还到池中，而不是真正关闭它。

数据库连接池的工作流程？

1. 搞一台机子专门做数据库连接服务器。

2. 初始化阶段：应用启动时，预先创建一定数量（如 10 个）的数据库连接，放入池中。

3. 获取连接：当应用需要访问数据库时连这个专门服务器，让这个服务器从池中获取一个空闲的连接。

4. 使用连接：应用程序使用该连接执行 SQL 操作。

5. 归还连接：操作完成后，应用将连接归还给连接池，而不是关闭它。归还的连接可被其他请求再次使用。数据库服务器把操作结果返回客户端。

6. 连接维护：连接池还会负责检测空闲连接、失效连接，并根据配置自动补充或移除连接。

（追问）数据库连接池的线程安全问题？

所有对连接队列的访问（如 push、pop）都必须加锁保护，使用 std::mutex；等待连接可用时使用 std::condition_variable。

（追问）连接的心跳检测？

在 getConnection() 时，对取出的连接做有效性检测（如执行 mysql_ping(conn)）。若连接失效，则销毁并创建新连接替代。

（追问）有人未归还连接？

有人忘记调用 releaseConnection() ，未归还连接：推荐使用 RAII（资源获取即初始化）包装类，例如一个 ScopedConnection 类，在构造函数中获取连接，析构函数中自动归还。

（追问）连接超时、死锁怎么办？

设置获取连接的最大等待时间，超时后抛异常或返回错误，而不是无限等待。

数据库异步连接池是什么？如何实现？

• libmysqlclient-dev 库：提供同步阻塞的接口。

• 异步连接接口：QueryCallback AsyncQuery(PreparedStatement<T>* stmt);

• 把 stmt push到消息队列，连接池里的多个线程负载均衡地挑出一个线程，执行语句。

• 使用 promise + future 机制获得执行的结果。如果是C语言的话，只能再设置一个消息队列装载返回的结果。

数据库异步连接池有哪些模式？

1. chain式

   1. 把多个异步操作按顺序组织起来，形成一种“异步流水线”

   2. 每一步都要有返回的结果

   3. 若任何一个环节出错，不继续执行，直接返回

2. holder式

   1. 异步地执行操作，且只返回一个最终需要的结果，不需要关心执行顺序

3. transaction式：在同一个数据库连接上执行多个 SQL 操作，并保证这些操作要么全部成功（commit）、要么全部失败（rollback），即事务的 ACID 特性。通常需要：

   1. 显式调用 BEGIN TRANSACTION、COMMIT、ROLLBACK

   2. 确保整个事务期间使用的是同一个连接，不能被其它操作抢占或归还

（追问）异步连接池如何提升数据库的吞吐量？从MySQL和Redis两方面考虑下？

• MySQL一个连接对应一个线程，这样MySQL服务器端也会用多线程来处理，提升吞吐量。

• Redis虽然是单线程，但是底层使用epoll，一次epoll wait会收获多个连接请求，提升吞吐量。

（追问）那连接数是越大越好吗？为什么要有连接池上限？

数据库访问负载过大，MySQL、Redis不堪重负。

你设计的数据库连接池如何防止SQL注入？

1. 参数化查询强制机制

• 实现方式：只暴露参数化查询方法，不提供直接拼接SQL的接口。

1. SQL语句预验证

• 检测内容：检查SQL语句是否包含明显的字符串拼接特征（如未参数化的变量插入）。

1. 与上层框架的协同防护

• ORM/查询构建器集成

ORM是什么？你的代码里是如何做ORM的？

ORM（Object-Relational Mapping，对象关系映射） 是一种编程技术，用于在面向对象编程语言中的对象与关系型数据库之间建立映射关系，从而让开发者能够以操作对象的方式操作数据库，而不必编写复杂的SQL语句。

1. 定义数据模型（C++类）

2. 生成ORM代码（使用ODB编译器），这将生成几个文件：

   1. user-odb.hxx 和 user-odb.cxx：ODB生成的持久化代码

   2. 可能还有SQL schema定义文件

3. 使用ORM进行数据库操作（安全，防注入）

除了ODB之外，C++还有一些其他的ORM或数据库抽象方案： 1. QxOrm（基于Qt框架） 专为Qt设计，与Qt的对象模型紧密集成 提供直观的API和Qt信号槽支持 2. SOCI（Simple Open C++ Interface） 虽然不是完整ORM，但是一个优秀的数据库访问抽象层 支持多种数据库后端，提供简洁的接口 3. LiteSQL 轻量级C++ ORM库 支持SQLite、PostgreSQL等数据库