【八股战神篇】MySQL高频面试题

目录

专栏简介

一 什么是索引

延伸

1 索引的底层使用的是什么数据结构？

2 MySQL 索引分类有哪些？

3 什么字段适合创建索引？

4 索引失效的场景

5 什么是最左匹配原则？

二 为什么 InnoDB 存储引擎选用 B+ 树而不是 B 树呢？

延伸

1 B+树的分裂过程可以简单介绍一下吗？

2 B+树、B树和红黑树的特点及区别？

三 事务的隔离级别

延伸

1 事务的四大特性介绍一下？

2 什么是脏读、幻读、不可重复读

3 MVCC了解吗

四 慢查询优化

延伸

1 慢查询优化解决方案

五 Redo Log、Undo Log、Bin Log三种日志

延伸

1 redo log 和 bin log有什么区别？

2 有了 bin log，为啥还需要 redo log？

---

专栏简介

八股战神篇专栏是基于各平台共上千篇面经，上万道面试题，进行综合排序提炼出排序前百的高频面试题，并对这些高频八股进行关联分析，将每个高频面试题可能进行延伸的题目再次进行排序选出高频延伸八股题。面试官都是以点破面从一个面试题不断深入，目的是测试你的理解程度。本专栏将解决你的痛点，助你从容面对。本专栏已更新Java基础高频面试题、Java集合高频面试题、MySQL高频面试题，后续会更新JUC、JVM、Redis、操作系统、计算机网络、设计模式、场景题等，计划在七月前更新完毕（赶在大家高频面试前）点此链接订阅专栏“八股战神篇”。

一 什么是索引

索引（Index）是一种用于加速数据库查询操作的数据结构，它的核心作用是提升查询的速度。从本质上来说，索引的作用就是帮助快速定位有序双向链表中的元素，从而减少数据扫描的范围，提高查询效率。接下来，我会详细讲述索引的基本概念和原理。

第一个是索引的基本概念，

索引是数据库中一种独特的数据结构，它并不直接存储表中的数据，而是通过创建一个新的数据结构来指向数据表中的具体记录。可以把它类比为字典的目录：当我们查字典时，首先会根据单词的起始字母找到目录页，然后通过目录页中的页码快速定位到具体的单词位置。在这个过程中，目录页就相当于索引表，而目录项就是索引本身。

然而，索引比字典目录更加复杂，因为数据库需要处理动态的数据操作，比如插入、删除和更新等操作。这些操作会导致索引发生变化，因此数据库需要维护索引的一致性和高效性。

第二个是索引的原理，

当你在MySQL中创建一个索引时， 首先，MySQL会选择一种数据结构来存储索引，最常见的结构是B+树。B+树是一种自平衡的树形结构，叶子节点存储所有数据，而非叶子节点存储索引信息。它的每个节点包含多个键值对，每个键值对指向一个数据块。 然后，索引会根据数据列的值进行排序，将相应的数据行指向叶子节点。

当你执行一个查询时， 首先，MySQL会利用索引的树结构，根据查询条件快速定位到数据的范围，而无需扫描全表。通过从根节点开始，逐层向下遍历B+树，最终可以找到符合条件的数据。 其次，如果索引指向的列已经包含查询条件，MySQL可以直接从索引中获取数据，避免了全表扫描，提高了查询速度。 最后，如果查询条件涉及多个列，MySQL会使用复合索引来进一步提高查找效率，通过联合多个列的索引来加速多条件查询。

延伸

1 索引的底层使用的是什么数据结构？

数据库中索引的底层通常使用 B+树 或 哈希表 作为数据结构：

• B+树：大多数关系型数据库（如 MySQL 的 InnoDB 存储引擎）使用 B+树实现索引，适合范围查询、有序查询。

• 哈希表：一些特定场景下会使用哈希表索引（如 Memory 引擎中的哈希索引），适合等值查询。

B+树是索引的主要实现方式，因为它支持范围查询、排序以及多种复杂操作，而哈希索引更适合简单的键值匹配。

B+树和哈希表的对比

特性	B+树索引	哈希索引
查询方式	支持范围查询、等值查询	仅支持等值查询
排序支持	支持排序	不支持
范围查询支持	支持	不支持
性能	查询性能稳定，O(log N)	查询性能极高，接近 O(1)
适用场景	复杂查询、范围查询、排序	简单键值匹配，高速查找
应用	MySQL InnoDB 主流索引	Redis、Memory 引擎等

示例：MySQL 中索引的使用

创建 B+树索引

CREATE INDEX idx_name ON users(name);

创建哈希索引（Memory 引擎）

CREATE TABLE cache_data (id INT,value VARCHAR(255),PRIMARY KEY (id)
) ENGINE = MEMORY;

使用全文索引

CREATE FULLTEXT INDEX idx_content ON articles(content);

2 MySQL 索引分类有哪些？

MySQL 中索引根据功能和用途可以分为以下几类：

• 主键索引（Primary Key Index）：唯一标识表中的每一行，字段值必须唯一且不能为空。

• 唯一索引（Unique Index）：保证字段值的唯一性，但允许为空值。

• 普通索引（Index）：没有唯一性要求的基础索引，用于加速查询。

• 复合索引（Composite Index）：由多个列组成的索引，用于优化多条件查询。

• 全文索引（Fulltext Index）：用于高效搜索文本中的关键词。

• 空间索引（Spatial Index）：用于存储和查询地理空间数据，适用于 Geometry 类型字段。

这些索引类型有不同的特性，适用于不同的查询场景。

索引类型对比

索引类型	特点	适用场景
主键索引	数据唯一且不能为空；InnoDB 中是聚簇索引	数据唯一标识的字段，如 ID 字段
唯一索引	数据唯一，但可为空	邮箱、用户名等需要唯一性的字段
普通索引	基本索引，无唯一性要求	加速查询，但无需唯一性约束的字段
复合索引	多列联合的索引，遵循最左前缀原则	多条件联合查询，如 (name, email)
全文索引	基于关键词匹配的文本搜索索引	快速匹配大文本字段，如博客文章、评论
空间索引	用于地理空间数据的索引	存储和查询地理位置信息

3 什么字段适合创建索引？

适合创建索引的字段通常具有以下特点：

• 查询频繁：经常在 WHERE、JOIN 或排序（ORDER BY）条件中使用的字段。

• 唯一性较高：值的区分度高，比如身份证号、邮箱等，不适合在重复率高的字段（如性别）上创建索引。

• 经常作为排序和分组依据：在 ORDER BY、GROUP BY 中出现的字段。

• 范围查询的字段：如 BETWEEN 或 > < 操作中的字段。

• 数据量较大的表：小表通常无需索引，因为全表扫描性能足够高。

4 索引失效的场景

索引失效的场景是指在数据库查询中，虽然我们为某些字段建立了索引，但由于某些原因，查询时索引未能被使用，导致查询性能下降。接下来我会详细讲述常见的七个索引失效场景及其原因。

第一个是，当查询条件中使用了函数或表达式操作时，索引会失效。例如，在查询中对索引列使用了 UPPER()、LOWER() 或其他函数操作，数据库无法直接利用索引，因为索引存储的是原始值，而不是经过函数处理后的值。类似的，如果在索引列上进行数学运算（如 age + 1），也会导致索引失效。

第二个是，当查询条件中使用了类型隐式转换时，索引会失效。例如，如果索引列是字符串类型，而查询条件中传入的是数字类型，数据库会尝试将字符串列转换为数字进行比较，这种隐式转换会导致索引失效。正确的做法是确保查询条件的数据类型与索引列的数据类型一致。

第三个是，当查询条件中使用了 LIKE 并以通配符 % 开头时，索引会失效。例如，LIKE '%abc' 这种查询方式会让数据库无法利用索引，因为通配符 % 在开头意味着需要扫描整个表来匹配数据。而如果通配符出现在末尾，如 LIKE 'abc%'，索引仍然可以生效。

第四个是，当查询条件中使用了 OR 且部分条件未命中索引时，索引可能失效。例如，如果查询条件是 WHERE indexed_column = 'value1' OR non_indexed_column = 'value2'，即使 indexed_column 上有索引，但由于 non_indexed_column 没有索引，数据库可能会选择全表扫描，从而导致索引失效。

第五个是，当查询中使用了 NOT 或 != 操作符时，索引可能会失效。例如，WHERE column != 'value' 或 WHERE NOT column = 'value' 这样的查询条件通常会导致数据库放弃使用索引，因为这类操作需要扫描大量数据来排除不符合条件的记录。

第六个是，当查询中使用了复合索引但未遵循最左前缀原则时，索引会失效。例如，假设有一个复合索引 (A, B, C)，如果查询条件只包含 B 或 C，而没有包含 A，那么这个复合索引将无法被使用。只有从最左边的列开始，并按顺序使用索引列，才能有效利用复合索引。

第七个是，当查询中使用了 IS NOT NULL 时，索引可能会失效。虽然 MySQL 支持对 IS NULL 使用索引，但在很多情况下，特别是 IS NOT NULL 的查询，数据库可能会选择全表扫描，从而导致索引失效。

5 什么是最左匹配原则？

最左匹配原则是数据库中使用复合索引（联合索引）时的一个重要规则。它指的是：在查询条件中，必须按照复合索引中字段的顺序，从最左边的字段开始使用，才能充分利用索引。如果跳过了某个字段，则后续字段无法使用索引。

最左前缀原则主要适用于联合索引。联合索引由多个字段组成，例如 (A, B, C)。 索引可以支持以下查询： – WHERE A = ? – WHERE A = ? AND B = ? – WHERE A = ? AND B = ? AND C = ?

但以下查询无法完全利用索引： – WHERE B = ? AND C = ? （跳过了 A） – WHERE C = ? （跳过了 A 和 B）

二 为什么 InnoDB 存储引擎选用 B+ 树而不是 B 树呢？

InnoDB 存储引擎选用 B+树 而非 B树，主要是因为 B+树在数据库场景中具有以下优势：

1. 叶子节点链表结构便于范围查询：B+树的叶子节点通过双向链表连接，可以方便地进行范围查询（如 BETWEEN 或 > 操作）。

2. 更高的磁盘读写效率：B+树的非叶子节点只存储键值，而不存储实际数据，因此单个节点可以容纳更多的键值，树的高度更低，减少磁盘 I/O。

3. 便于扫描和排序：B+树的所有数据都存储在叶子节点中，天然有序，支持高效的全表扫描和排序操作。

相比之下，B树的非叶子节点也存储数据，这会导致节点存储能力变小，树的高度增加，从而增加磁盘 I/O，降低性能。

B树与 B+树的结构对比

• B树：

  • 每个节点存储键值和实际数据。

  • 数据既存在叶子节点，也存在非叶子节点。

  • 各节点之间无链表连接。

• B+树：

  • 非叶子节点只存储键值，用于索引定位，不存储实际数据。

  • 数据仅存储在叶子节点中。

  • 所有叶子节点通过链表连接，便于顺序访问。

示意图：

• B树：

    [10 | data]      [30 | data]/      \         /      \
  [5 | data] [15 | data]  [25 | data] [35 | data]

• B+树：

     [10]          [30]/    \        /    \
  [5]--->[15]--->[25]--->[35]

总结：B+树对比 B树的优势

特性	B树	B+树
非叶子节点	存储键值和数据	仅存储键值
叶子节点	存储部分键值和数据	存储所有键值和数据
数据存储位置	分散在叶子和非叶子节点中	全部存储在叶子节点
磁盘 I/O	节点存储能力较低，树高较大，I/O 次数更多	节点存储能力更高，树高较低，I/O 次数更少
范围查询和排序	不支持顺序访问	支持链表顺序访问，便于范围查询和排序
全表扫描	需要逐节点访问	只需遍历叶子节点，性能更高

延伸

1 B+树的分裂过程可以简单介绍一下吗？

在 B+树 中，分裂是为了保持树的平衡性。当节点因插入新数据而超出其容量时，会触发分裂操作。分裂的过程如下：

• 找到要插入的节点：如果插入数据导致当前节点的键值数量超出最大容量，则该节点需要分裂。

• 分裂节点

  ：将节点的键值分为两部分：

  • 左半部分留在原节点；

  • 中间的键值上移到父节点，作为分裂的新分界点；

  • 右半部分被移动到一个新节点中。

• 调整父节点：将中间键值插入到父节点。如果父节点也超出容量，则递归进行分裂。

• 调整树高度：当根节点也需要分裂时，会生成一个新的根节点，树的高度增加。

通过分裂操作，B+树始终保持平衡，每次插入的时间复杂度为 (O(\log N))。

2 B+树、B树和红黑树的特点及区别？

B+树、B树和红黑树是常见的平衡树数据结构，尤其在数据库、文件系统和内存中应用的十分广泛。接下来，我将从六个方面详细讲解这三者的特点和区别。

第一个方面是数据存储位置。B+树将所有数据存储在叶子节点，非叶子节点只存储索引键；B树则将数据存储在叶子节点和非叶子节点中，所有节点都存储数据；而红黑树则在每个节点中都存储数据。

第二个方面是索引查找效率。B+树的查找操作最终都在叶子节点完成，查找路径统一，效率较高；B树的查找操作可能在非叶子节点完成，查找路径不统一，效率稍低；红黑树的查找路径统一，时间复杂度为O(log n)，每次操作通过旋转和重新染色来保持平衡。

第三个方面是树的高度。B+树由于扇出较高（每个节点存储多个键），树的高度通常较低，查询效率较高；B树由于非叶子节点也存储数据，扇出较小，树的高度较高；红黑树作为二叉查找树，树的高度较高，每个节点最多有两个子节点，树的深度较大。

第四个方面是磁盘I/O效率。B+树由于非叶子节点只存储索引，扇出高，可以减少磁盘访问次数，因此磁盘I/O效率非常优秀；B树稍逊于B+树，因为非叶子节点也存储数据，导致扇出较小，磁盘访问次数略多；红黑树的磁盘I/O效率较差，因为树的高度较高，每次查找可能需要频繁访问磁盘。

第五个方面是适用场景。B+树常用于数据库索引、文件系统索引，适合大规模数据的存储和检索，尤其在需要高效范围查询时；B树适用于数据库索引和文件系统索引，但相比B+树，查找效率稍低；红黑树适用于内存中的数据结构，如Java中的TreeMap和TreeSet，适合存储符号表、集合、关联数组等内存数据。

第六个方面是空间利用率。B+树的空间利用率较高，非叶子节点只存储索引，存储效率较好；B树的空间利用率较低，非叶子节点存储数据和索引，存储效率较低；红黑树的空间利用率较高，所有节点都存储数据，且由于树的平衡性，内存利用效率较好。

三 事务的隔离级别

事务的隔离级别是数据库管理系统（DBMS）中用于控制事务并发执行时的数据一致性和并发控制的一种机制。不同的隔离级别决定了事务在执行过程中对其他事务的可见性，从而影响了数据的完整性和查询的准确性。接下来我将详细讲述四种隔离级别。

第一种是读取未提交（READ UNCOMMITTED），在这个隔离级别下，事务可以读取其他事务尚未提交的数据，可能会发生脏读、不可重复读、幻读。当你在执行一个查询时，如果一个事务正在修改数据，但尚未提交，其他事务仍然可以看到这个未提交的数据。这虽然提供了最高的并发性，但也带来了数据一致性的风险，比如读取到不一致的数据。

第二种是读取已提交（READ COMMITTED），在这个隔离级别下，事务只能读取其他事务已提交的数据，可能会发生不可重复读、幻读。当你在执行一个查询时，只有那些已经提交的事务对当前事务可见。然而，不可重复读问题依然存在：如果在同一个事务中多次查询相同的数据，可能会得到不同的结果，因为其他事务可能在查询间修改了数据。

第三种是可重复读（REPEATABLE READ），在这个隔离级别下，事务在执行期间会锁定查询的数据行，确保该数据在事务完成前不会被其他事务修改。当你在执行一个查询时，同一事务中的查询结果不会变化，即使其他事务修改了数据，当前事务也看不到变化的数据。然而，这个级别仍然存在幻读问题，即在查询过程中，其他事务可能会插入新的数据行，导致当前事务查询的数据集发生变化。

第四种是可串行化（SERIALIZABLE），在这个隔离级别下，事务的执行会像是串行执行的，即一个事务执行完成后，另一个事务才能开始。当你在执行一个查询时，不仅当前查询的数据不会被修改，其他事务也不能插入新的数据行。这个级别提供了最高的数据一致性，但代价是性能的显著下降，因为它限制了并发操作。

性能对比：

隔离级别	并发性能	数据一致性
READ UNCOMMITTED	最高	最低（可能脏读）
READ COMMITTED	较高	防止脏读
REPEATABLE READ	较低	防止脏读和不可重复读
SERIALIZABLE	最低	完全一致

延伸

1 事务的四大特性介绍一下？

事务的四大特性（ACID）分别是（有的面试官可能会问英文是什么建议记一下）：

• 原子性（Atomicity）：事务是一个不可分割的整体，要么全部执行成功，要么全部失败回滚。

• 一致性（Consistency）：事务执行前后，数据库必须保持一致状态，不会破坏数据的完整性。

• 隔离性（Isolation）：多个事务并发执行时，事务之间互不干扰，保证中间状态对其他事务不可见。

• 持久性（Durability）：事务一旦提交，其对数据库的修改是永久的，即使系统崩溃也能恢复。

详解

1. 原子性（Atomicity）

• 原子性意味着事务是一个不可分割的整体。事务中的所有操作要么全部完成，要么全部回滚撤销，不能只执行一部分。

• 实现原理：

  • 通过事务日志实现，如果事务未成功完成，则通过日志回滚到事务开始前的状态。

• 例子：

  在银行转账中，如果从账户 A 扣款成功，但账户 B 加款失败，则整个转账操作需要回滚，保证事务的原子性。

  START TRANSACTION;
  UPDATE Accounts SET balance = balance - 100 WHERE id = 1;  -- 从账户 A 扣款
  UPDATE Accounts SET balance = balance + 100 WHERE id = 2;  -- 向账户 B 加款
  COMMIT;  -- 如果两步操作都成功，提交事务

1. 一致性（Consistency）

• 一致性保证事务执行前后，数据库状态必须保持一致，符合数据库的完整性约束（如主键约束、外键约束等）。

• 实现原理：

  • 数据库会通过约束条件和事务回滚机制，确保在事务执行失败时数据不会被破坏。

• 例子：

  转账操作前后，账户 A 和账户 B 的总金额应保持不变：

  • 转账前：A 的余额 1000 元，B 的余额 500 元，总和 1500 元。

  • 转账后：A 的余额 900 元，B 的余额 600 元，总和仍为 1500 元。

1. 隔离性（Isolation）

• 隔离性保证多个事务并发执行时，事务之间的操作互不干扰，一个事务的中间状态对其他事务不可见。

• 实现原理：

  • 通过锁机制（行锁、表锁等）和隔离级别（如 REPEATABLE READ）来实现事务隔离。

• 隔离性问题：

  • 脏读：读取到其他未提交事务的修改。

  • 不可重复读：同一事务中多次读取结果不一致。

  • 幻读：同一事务中，前后查询的结果集记录数不一致。

• 例子： 一个事务正在修改账户余额，在事务提交前，其他事务不能读取到未提交的数据。

1. 持久性（Durability）

• 持久性保证事务一旦提交，其对数据库的修改是永久性的，即使系统崩溃也不会丢失。

• 实现原理：

  • 数据库通过事务日志（如 redo log）记录所有修改操作，并在崩溃后通过日志进行恢复。

• 例子： 转账事务完成后，即使系统重启，账户 A 和账户 B 的余额修改仍然有效，不会丢失。

---

事务特性总结表

特性	描述	实现机制	示例
原子性	事务中的操作不可分割，要么全部完成，要么全部回滚。	事务日志（undo log）	转账中 A 扣款成功但 B 加款失败时回滚。
一致性	事务前后，数据库必须从一个一致状态转到另一个一致状态。	约束检查 + 回滚	转账前后账户余额总和保持一致。
隔离性	并发事务之间互不干扰，中间状态对其他事务不可见。	锁机制 + 隔离级别	一个事务修改数据时，另一个事务无法读取。
持久性	事务提交后，修改永久生效，系统崩溃后也能恢复。	事务日志（redo log）	转账完成后，数据写入磁盘即永久有效。

2 什么是脏读、幻读、不可重复读

脏读是指一个事务读取到了另一个事务尚未提交的数据。如果后续该事务回滚了未提交的修改，则第一个事务读取到的数据就是无效的，从而导致数据的不一致性。脏读是事务隔离性问题之一。它发生在两个事务并发执行时，一个事务读取到了另一个事务未提交的数据，而这些未提交的数据可能被回滚，因此读取的内容可能是错误或无效的。

幻读是指在一个事务中执行两次相同的查询时，结果集的行数不同。通常是因为另一个事务在两次查询之间插入或删除了满足查询条件的数据行，从而导致查询结果的不一致。幻读是事务隔离性问题之一。幻读主要发生在 多行操作 中，例如范围查询一个事务第一次查询时获取了满足条件的记录。另一个事务在其间插入或删除了符合条件的记录。 当第一个事务再次查询时，结果集行数发生了变化，产生“幻影”记录，这就是幻读。

不可重复读是指在一个事务中多次读取同一条记录时，结果不一致，通常是因为在两次读取之间，另一事务修改了该记录的数据（包括更新或删除操作）。不可重复读是事务隔离性问题之一。不可重复读的关键在于，事务中读取到的数据在操作期间被其他事务修改了，导致数据前后不一致。主要表现第一次读取获得的结果和第二次读取的结果不同。修改可以是更新现有数据或删除数据。

3 MVCC了解吗

MVCC（多版本并发控制）是一种数据库管理系统通过维护数据的历史版本来实现高并发读写场景下数据一致性的关键技术。其核心思想在于允许读取操作不阻塞写入操作，同时写入操作也不会阻塞读取操作，以此在保证事务隔离性的前提下最大限度地提升并发性能。

在MySQL的InnoDB引擎中，MVCC通过为每一行数据隐式添加两个系统版本号字段来实现分别是创建版本号（记录数据插入或修改时的事务ID）和删除/过期版本号（记录数据删除或更新时的事务ID）。每个事务启动时会生成一个唯一的系统版本号作为事务ID，并通过“快照视图”（Read View）决定当前事务能观察到哪些版本的数据。

MVCC的实现依赖于版本链和Undo日志。当数据被修改时，旧版本数据会存入Undo日志，并形成以版本号为顺序的链表结构。读操作通过遍历版本链选择对当前事务可见的版本。例如，更新操作（UPDATE）会将原始行标记为删除（记录当前事务ID为删除版本号），并插入新行（记录当前事务ID为创建版本号），从而形成可追溯的版本历史。

四 慢查询优化

慢查询优化总体上通过三个步骤来进行，先捕获低效SQL，然后使用工具进行分析，最后采用一些方法进行优化，

首先，是捕获低效SQL，我们需要确保慢查询日志已启用。通过设置全局变量slow_query_log为'ON'来开启此功能，并设定long_query_time参数以确定什么程度的查询延迟被视为“慢”。

接下来，我们要分析慢查询日志，这是找出问题根源的关键步骤。利用工具如mysqldumpslow可以帮助我们总结日志信息，比如按出现频率或总执行时间排序，从而聚焦于最需要关注的查询语句。

对于每一个慢查询，我们可以使用EXPLAIN命令查看MySQL如何执行这些查询。EXPLAIN提供的输出能够揭示查询执行计划中的细节，例如是否进行了全表扫描（ALL），以及哪些索引被使用等。根据这些信息，我们可以做出更明智的决策来优化查询性能。

后是，采用一些方法进行优化，常用的优化策略如下：

1. 创建合理的索引：对频繁查询的字段（如 WHERE、JOIN、ORDER BY、GROUP BY 字段）创建索引。

2. 优化 SQL 语句：简化查询逻辑，减少嵌套查询或子查询。

3. 分区或分表：对于数据量较大的表，考虑分区或分表。

4. 缓存机制：使用数据库缓存或应用层缓存减少重复查询。

延伸

1 慢查询优化解决方案

1.慢查询的解决方案

解决MySQL慢查询问题的方案可以按照资源消耗从少到多的顺序排列，像金字塔一样逐步提升。以下是从资源消耗少到多的常见优化方式：

(1)SQL优化

合理使用索引：确保查询字段使用了索引。对于WHERE、JOIN、ORDER BY、GROUP BY等操作的字段，应该创建相应的索引。

(2)索引优化

创建复合索引：对于多个字段联合查询，创建复合索引（注意索引顺序）。

删除冗余索引：定期清理无用索引，减少索引维护的负担。

避免索引覆盖不必要的字段：有时候一个大字段（如TEXT或BLOB）放入索引会增加存储开销，应该避免。

更新统计信息：定期更新表的统计信息，帮助优化器选择更合适的执行计划。

(3)数据库配置优化

调整缓存设置：增加innodb_buffer_pool_size，确保更多的数据能够缓存到内存中。调整query_cache_size，如果适用，启用查询缓存（对于更新频繁的应用不推荐）。

调整连接设置：如增加max_connections，但要注意数据库承载能力。

调整临时表大小：如果临时表经常写入磁盘，可以通过调整tmp_table_size和max_heap_table_size来避免此问题。

增加排序缓存：增加sort_buffer_size来提高ORDER BY和GROUP BY操作的效率。

(4)架构优化

分库分表：对于单表数据量过大的情况，使用分库分表策略，将数据分散到不同的数据库或表中，减少每个查询的负载。

读写分离：通过主从复制，减少主库的查询压力，读请求分发到从库。

数据库集群：使用分布式数据库系统，解决单机性能瓶颈。

(5)硬件升级

增加内存：通过增加内存，提高缓存命中率，减少磁盘IO。

更换更快的磁盘：使用SSD代替传统的硬盘，提升磁盘IO性能。

增加CPU处理能力：提升CPU性能，减少数据库查询的CPU瓶颈。

五 Redo Log、Undo Log、Bin Log三种日志

Redo Log 是 MySQL 中 InnoDB 存储引擎使用的一种持久化日志，用于记录事务对数据的修改，确保数据库的崩溃恢复能力。即使数据库在写入过程中崩溃，Redo Log 也能帮助恢复到事务提交时的状态。

Undo Log 是 MySQL 中 InnoDB 存储引擎使用的一种日志，用于记录数据的撤销操作。其主要作用是支持事务回滚和多版本并发控制（MVCC）。当事务操作失败或主动回滚时，Undo Log 可以将数据恢复到事务开始前的状态。

Bin Log（Binary Log） 是 MySQL 的二进制日志，主要用于记录对数据库执行的所有修改操作（如 INSERT、UPDATE、DELETE）以及可能引起数据变化的语句（如 CREATE TABLE、ALTER TABLE）。它的主要用途包括：

• 数据恢复：通过重放 Bin Log 恢复数据到某个时间点。

• 主从复制：Bin Log 是 MySQL 主从同步的基础，用于将主库的变更同步到从库。

• 增量备份：通过记录变更操作实现增量备份，减少全量备份的频率。

详解

1，Redo Log 的工作原理

预写日志（Write Ahead Logging） – 当事务修改数据时，先将修改记录写入 Redo Log，并将日志标记为“准备提交”。 – 等日志写入成功后，事务才会真正提交。

示例过程：

• 修改数据：事务 T1 修改数据，记录写入内存和 Redo Log。

• 提交事务：事务提交时，先确保 Redo Log 持久化到磁盘。

• 异常恢复：如果数据库崩溃，InnoDB 使用 Redo Log 恢复已提交的事务。

2，Redo Log 的组成结构

Redo Log 由两部分组成：

• Redo Log Buffer（内存中的日志缓冲区） – Redo Log 首先写入内存中的缓冲区。 – 当缓冲区满、事务提交或某些时间点触发时，日志会被刷新到磁盘。

• Redo Log File（磁盘上的日志文件）

  • 持久化到磁盘的日志文件，默认命名为 ib_logfile0 和 ib_logfile1。

  • 日志文件采用循环写的方式（有限大小，写满后覆盖旧数据）。

1，Undo Log 的工作原理

数据修改与日志记录 – 当事务修改数据时，InnoDB 不直接覆盖原始数据，而是先将修改前的快照写入 Undo Log。 – Undo Log 保存在回滚段（Rollback Segment）中，与数据表的操作分开存储。

回滚过程 – 如果事务执行失败或被主动回滚，InnoDB 根据 Undo Log 逐条撤销已执行的操作，将数据恢复到事务开始前的状态。

2，Undo Log 的内容结构

逻辑日志 – 记录的是每次修改的逻辑操作，适用于 INSERT、UPDATE、DELETE 操作。 – 例子： 对某行执行 UPDATE，Undo Log 记录原始行的数据值。 对某行执行 DELETE，Undo Log 记录被删除的整行。

存储方式 Undo Log 存储在专用的 Undo 表空间中（从 MySQL 5.6 开始支持独立 Undo 表空间）。 数据写入表时对应生成 Undo Log，事务提交后，Undo Log 可被清理。

1，Binlog 的类型

MySQL 支持以下三种 Binlog 格式，每种格式的记录方式不同：

• STATEMENT（语句模式） – 记录每一条修改数据的 SQL 语句。 – 优点：Binlog 文件体积小，写入效率高。 – 缺点：某些场景（如基于函数或用户变量的操作）可能导致不一致。

• ROW（行模式） – 记录每一行数据的具体变更内容（包括变更前后的值）。 – 优点：更高的一致性，不受函数等操作影响。 – 缺点：Binlog 文件体积较大。

• MIXED（混合模式） – 根据具体场景在 STATEMENT 和 ROW 模式之间切换。 – 优点：结合了两者的优势，避免了一些不一致问题。

切换格式：

SET GLOBAL binlog_format = 'ROW';  -- 设置 Binlog 格式为 ROW

2，Bin Log 的结构

1. 事件类型 Binlog 是以事件为单位存储的，每个事件描述一次操作： – Query Event：记录 DDL 和 DML 语句。 – Table Map Event：记录表的元数据（在 ROW 格式中使用）。 – Write Rows Event：记录行的插入操作。 – Update Rows Event：记录行的更新操作。 – Delete Rows Event：记录行的删除操作。

1. 文件结构 Binlog 文件以二进制格式存储，文件名通常为 binlog.000001、binlog.000002，并通过 binlog.index 文件管理。

3，Bin Log 的使用示例

• 查看 Binlog 文件列表：

SHOW BINARY LOGS;

• 查看当前正在写入的 Binlog 文件：

SHOW MASTER STATUS;

• 读取 Binlog 文件内容：

mysqlbinlog binlog.000001

• 恢复数据：

mysqlbinlog --start-datetime="2025-01-14 10:00:00" 
--stop-datetime="2025-01-14 12:00:00" binlog.000001 | mysql -u root -p

三种日志的对比

日志类型	功能	记录内容	持久化时机
Redo Log	保证事务持久性，支持崩溃恢复	已提交事务的修改操作	事务提交时写入磁盘
Undo Log	支持事务回滚和 MVCC 的快照隔离	未提交事务前的数据快照	修改数据时写入磁盘
Binlog	支持主从同步和数据恢复	所有更改数据库结构或数据的操作	事务提交后写入磁盘

延伸

1 redo log 和 bin log有什么区别？

Redo Log 和 Binlog 是 MySQL 中两种不同的日志类型，它们的功能和作用各有侧重。主要区别如下：

特性	Redo Log	Binlog
作用	保证事务持久性（崩溃恢复）	数据恢复（增量恢复）、主从复制
记录内容	数据的物理变化（页级别修改）	数据的逻辑变化（SQL 或行级别操作）
写入时机	在事务执行过程中实时写入	在事务提交时写入
持久性	用于 InnoDB 引擎内部，写入磁盘后立即可用	用于 MySQL 服务器，提交后生成日志
适用范围	仅适用于 InnoDB 存储引擎	适用于整个 MySQL 实例
日志格式	物理日志（记录页的修改）	逻辑日志（SQL 语句或行记录）
循环使用	有固定大小，循环覆盖使用	文件递增存储，不会覆盖
主要用途	崩溃恢复	主从复制、增量备份、数据恢复

2 有了 bin log，为啥还需要 redo log？

虽然 Binlog 和 Redo Log 都记录了数据的修改，但两者的作用和设计目的不同，不能互相替代。因此，MySQL 同时需要 Redo Log 和 Binlog 来确保性能、事务的持久性和数据一致性。以下是具体原因：

• – Redo Log：解决事务持久性（事务已提交但未刷新到磁盘时，系统崩溃后能恢复）。

• – Binlog：解决增量备份和主从复制问题（确保逻辑操作可重复执行）。