MySQL基础-面经

数据库 MySQL
DataBase MySQL
发布日期:   2025-05-08
更新日期:   2025-07-01
文章字数:   4.8k
阅读次数:  

MVCC存储在哪里，作用是什么

MVCC是什么

  • 传统的数据库并发控制通常使用锁机制，在并发量高的情况下容易导致阻塞和性能下降。
  • 读不阻塞写，写不阻塞读；为每个事务维护一个数据快照；创建新版本而不是覆盖。

MVCC的实现原理

好的，我们来详细解释一下 MVCC（多版本并发控制）的实现机制。

MVCC 的核心思想是维护数据的多个版本，使得读操作可以不阻塞写操作，写操作也不会阻塞读操作。它主要依赖于以下几个关键组成部分和机制：

  1. 事务 ID (Transaction ID)

    每个事务在开始时都会被分配一个唯一的、递增的事务 ID。这个 ID 在 MVCC 中用于标记数据是由哪个事务创建或修改的。

  2. 数据行的版本链 (Version Chain)

    MVCC 数据库中的每一行数据通常都会有几个隐藏的列，用于记录该行的版本信息。这些隐藏列通常包括：

    • 创建事务 ID (TrxId 或 DB_TRX_ID)： 记录创建该行数据的事务 ID。
    • 删除事务 ID (DelTrxId) 或 指向 Undo Log 的指针 (DB_ROLL_PTR)：
      • 有些实现会记录删除该行数据的事务 ID。
      • 更常见的实现（如 InnoDB）是使用一个回滚指针 (DB_ROLL_PTR) 指向 Undo Log 中记录了该行修改前数据的一条记录。通过这个指针，可以将同一逻辑行（同一条记录）的不同版本通过 Undo Log 连接起来，形成一个版本链。最新的版本位于链的头部，旧版本通过指针连接到 Undo Log 中。

  3. Undo Log (回滚日志)

    Undo Log 在 MVCC 中扮演着至关重要的角色：

    • 存储旧版本数据： 当一个事务修改（UPDATE 或 DELETE）一行数据时，修改前的旧版本数据会被写入 Undo Log。
    • 构建版本链： 数据行中的回滚指针 (DB_ROLL_PTR) 正是指向 Undo Log 中的对应记录，从而构建起数据行的版本链。这个链条记录了该行数据从最初创建到当前版本的所有修改历史。
    • 支持回滚： 当事务需要回滚时，可以通过 Undo Log 中的记录将数据恢复到修改前的状态。
    • 支持快照读： 这是 MVCC 实现非阻塞读的关键。当一个事务需要读取数据时，它会根据自身的事务 ID 和可见性规则，沿着版本链在 Undo Log 中找到符合条件的、对当前事务可见的数据版本进行读取。

  4. Read View (读视图)

    每个事务在启动时（或者根据隔离级别的不同，在执行特定语句时）会创建一个 Read View（或类似的机制，如快照时间戳）。Read View 记录了当前活跃（尚未提交）的事务 ID 列表。这个 Read View 是判断数据版本对当前事务是否可见的关键。

    Read View 通常包含以下信息：

    • m_ids: 当前活跃的事务 ID 列表。
    • min_trx_id: m_ids 列表中最小的事务 ID。
    • max_trx_id: 在创建 Read View 时，系统中应该分配给下一个事务的 ID，即当前最大的事务 ID + 1。
    • creator_trx_id: 创建这个 Read View 的事务的 ID。

  5. 可见性判断规则

    当一个事务尝试读取一行数据时，数据库会根据该事务的 Read View 和数据行版本（由创建事务 ID DB_TRX_ID 标记）来判断这个版本是否对当前事务可见。具体的判断规则会根据事务隔离级别（如 Read Committed 或 Repeatable Read）有所不同，但基本原则如下：

    • 如果数据行版本的创建事务 ID (DB_TRX_ID) 小于 Read View 中的 min_trx_id，表示这个版本是由一个在当前事务启动之前就已经提交的事务所创建，因此该版本对当前事务可见。
    • 如果数据行版本的创建事务 ID (DB_TRX_ID) 大于等于 Read View 中的 max_trx_id，表示这个版本是由一个在当前事务启动之后才开始的事务所创建，因此该版本对当前事务不可见。
    • 如果数据行版本的创建事务 ID (DB_TRX_ID) 在 Read View 的 min_trx_id 和 max_trx_id 之间：
      • 如果 DB_TRX_ID 等于 Read View 中的 creator_trx_id，表示这个版本是当前事务自己修改的，因此对当前事务可见。
      • 如果 DB_TRX_ID 在 Read View 的 m_ids 列表 中，表示这个版本是由一个在当前事务启动时仍然活跃（但尚未提交）的事务所创建，因此对当前事务不可见。
      • 如果 DB_TRX_ID 不在 Read View 的 m_ids 列表 中，表示这个版本是由一个在当前事务启动时已经提交的事务所创建（但其 ID 在 min_trx_id 和 max_trx_id 之间），因此对当前事务可见。

    如果当前数据行版本对当前事务不可见，数据库会沿着 Undo Log 中的版本链向前查找更旧的版本，并对找到的每个版本重复上述可见性判断，直到找到一个可见的版本或者版本链的末端。

读写操作在 MVCC 中的处理：

  • 读操作： 当一个事务执行读操作（例如 SELECT）时，它会使用自己的 Read View，根据上述可见性判断规则，从数据行的版本链中找到对自身可见的最新版本进行读取。这个过程不涉及对数据的加锁，因此读操作不会阻塞写操作。
  • 写操作 (UPDATE/DELETE)： 当一个事务执行写操作时，它首先会像读操作一样找到对自身可见的最新版本数据。然后，它会将该版本的旧数据写入 Undo Log，并在数据行中创建或修改隐藏列，记录自己的事务 ID 和指向 Undo Log 的指针。新的数据版本只对创建它的事务或者后续启动并满足可见性规则的事务可见。写操作通常仍然需要对要修改的数据行加锁（写锁），以防止其他事务同时修改同一行，但这个锁通常只持续到事务提交或回滚，且不会阻塞读操作。
  • 写操作 (INSERT)： 当一个事务插入一行新数据时，它会在数据行中记录自己的事务 ID 作为创建事务 ID。新插入的数据行对创建它的事务可见，对于其他事务，其可见性也遵循上述可见性判断规则。

过期版本的清理 (Purge)

随着事务不断进行修改，数据行会产生越来越多的旧版本存储在 Undo Log 中。这些旧版本如果不再被任何活跃事务所需要，就需要被清理掉，以释放存储空间。这个清理过程通常由一个后台线程（Purge 线程）负责。Purge 线程会根据当前系统中所有活跃事务的 Read View，确定哪些 Undo Log 中的旧版本数据已经不再可能被任何事务读取，然后进行物理删除。长事务会持有旧版本的 Read View，从而阻止对旧版本数据的清理，可能导致 Undo Log 不断增长。

总结：

MVCC 通过为每个事务提供一个数据快照，并利用事务 ID、数据行版本链和 Undo Log 来存储和管理数据的多个版本。通过 Read View 和可见性判断规则，MVCC 使得读操作可以并发进行而不会被写操作阻塞，显著提高了数据库的并发性能和吞吐量，同时提供了良好的事务隔离性（通常是 Snapshot Isolation 或 Read Committed）。

MVCC存储在哪里

  • 数据行中的隐藏列： InnoDB 为每行数据添加了几个隐藏的系统列，其中与 MVCC 密切相关的主要有两个：

    • DB_TRX_ID：记录了最近一次修改（插入或更新）该行的事务 ID。删除在内部也被视为一种更新，会设置一个特殊的位标记。
    • DB_ROLL_PTR：这是一个回滚指针，指向 Undo Log 中的一条记录。如果该行是被更新的，Undo Log 记录包含了重建更新前行内容所需的信息。通过这个指针和 Undo Log，可以找到该行的上一个版本。

  • Undo Log（回滚日志）：

    • 作用： Undo Log 是存储旧版本数据的地方。每当事务修改数据时，旧版本的数据会被写入 Undo Log。
    • 存储位置： Undo Log 存储在称为回滚段（rollback segment）的数据结构中，这些回滚段位于 Undo 表空间。Undo 表空间可以是共享的（在 ibdataX 文件中）或者独立的文件。
    • MVCC 的依赖： 当一个事务需要读取某个数据行的旧版本时（根据事务自身的快照时间戳和数据行中的 DB_TRX_ID 判断），它会利用 DB_ROLL_PTR 沿着 Undo Log 的链条找到并重构出符合其可见性条件的旧版本数据。

MysQl的几种log

MySQL 中存在多种类型的日志文件，每种日志都有其特定的用途，对于数据库的监控、故障排查、数据恢复和复制至关重要。以下是 MySQL 中主要的几种日志：

  1. 错误日志 (Error Log)

    • 用途： 记录 MySQL 服务器运行过程中的各种错误信息、警告信息以及一些重要的事件，比如服务器的启动、关闭、崩溃等。
    • 作用： 这是排查 MySQL 服务器问题时首先应该查看的日志。它可以帮助你了解服务器为什么无法启动、为什么会意外关闭，或者在运行过程中出现了哪些异常情况。
    • 内容： 包含时间戳、事件类型（错误、警告、注意）、错误代码和描述等信息。

  2. 通用查询日志 (General Query Log)

    • 用途： 记录 MySQL 服务器接收到的每一个客户端连接以及客户端发送的所有 SQL 语句。
    • 作用： 这个日志可以非常详细地记录数据库的所有活动。它对于审计、追踪特定客户端的行为或者调试应用程序非常有用，可以准确地看到客户端向服务器发送了什么命令。
    • 注意： 由于会记录所有的操作，通用查询日志会非常庞大，并且对性能有一定影响，因此在生产环境中通常不建议长时间开启，除非是出于调试目的。

  3. 慢查询日志 (Slow Query Log)

    • 用途： 记录执行时间超过预设阈值的 SQL 语句。
    • 作用： 这是数据库性能优化的重要工具。通过分析慢查询日志，可以找出那些执行效率低下的查询语句，从而有针对性地进行优化，比如添加索引、重写查询语句等。
    • 内容： 包含查询语句本身、执行时间、锁定时间、发送给客户端的行数、扫描的行数等信息。可以通过配置 long_query_time 参数来设定慢查询的时间阈值。

  4. 二进制日志 (Binary Log 或 Binlog)

    • 用途： 记录所有更改了数据库数据或结构的语句（数据修改事件）。它以二进制格式存储，不记录 SELECT 等只读操作。
    • 作用： Binary Log 是 MySQL 复制（Replication）和数据恢复（Point-in-Time Recovery）的基础。
      • 复制： 在主从复制架构中，主服务器将 Binlog 发送给从服务器，从服务器通过回放 Binlog 中的事件来保持与主服务器的数据同步。
      • 数据恢复： 在进行基于时间点的数据恢复时，可以使用全量备份加上 Binlog 来将数据库恢复到备份之后的任意时间点。
    • 内容： 记录了导致数据改变的事件，可以是语句格式（Statement-Based Logging）、行格式（Row-Based Logging）或混合格式（Mixed-Based Logging）。

  5. 重做日志 (Redo Log)

    • 用途： 这是 InnoDB 存储引擎特有的日志，用于保证事务的持久性（Durability）。它记录了数据页的物理修改。
    • 作用： 当事务提交时，即使数据还没有来得及写入磁盘的数据文件中，事务的修改也已经被记录在 Redo Log 中了。如果数据库发生崩溃，在重启时 InnoDB 会检查 Redo Log，将已提交事务的修改从 Redo Log 中重放到数据文件中，确保数据不会丢失（Crash Recovery）。
    • 内容： 记录的是对数据页的物理修改操作，例如“在某某数据页的某某偏移量处写入某某值”。它是循环写入的，有一组固定大小的文件。

  6. 撤销日志 (Undo Log)

    • 用途： 这也是 InnoDB 存储引擎特有的日志，用于保证事务的原子性（Atomicity）和实现 MVCC（多版本并发控制）。它记录了数据修改前的旧版本数据。
    • 作用：
      • 事务回滚： 当事务需要回滚时，InnoDB 利用 Undo Log 中的信息将数据恢复到修改之前的状态，撤销事务所做的所有修改。
      • MVCC： 在 MVCC 中，Undo Log 存储了数据行的多个历史版本。当一个事务读取数据时，如果当前的数据版本不可见，就会沿着 Undo Log 的版本链找到符合其可见性条件的旧版本数据进行读取。
    • 内容： 记录的是逻辑操作，例如插入操作对应的 Undo Log 是删除操作，更新操作对应的 Undo Log 是将数据恢复到更新前的状态

分库分表

💡 一、为什么需要分库分表（动因）

核心目标：解决单库单表性能瓶颈。

  • 数据量大 → 单表数据超千万行，查询慢、索引失效

  • 并发高 → 单库连接数/事务冲突瓶颈

  • 运维难 → 备份、迁移、恢复时间长

  • 可用性差 → 单点故障风险高，难以扩展

✅ 示例句式：

随着业务发展，单表数据量过大或请求并发过高，数据库响应延迟明显，单库承压，因此需要通过分库分表来提升系统的吞吐能力与可用性。

🧩 二、分库分表的方式与策略

1. 拆分方式

  • 垂直拆分（按业务模块拆库）：如用户、订单、商品拆成不同库

  • 水平拆分（按数据范围拆表）：如 user_id % N 拆成 user_0, user_1…

2. 分片策略（Sharding Key）

  • 范围分片（如：按时间、ID区间）

  • 哈希分片（如：user_id % N）

  • 标签分片（如：地区、商户ID）

✅ 示例句式：

水平拆分时我们通常选取业务查询最频繁的字段作为分片键，比如 user_id，使用 hash 或 range 分片以保证数据均衡与高可用。

⚙️ 三、常见技术实现方式

1. 自研方案
  • 在 DAO 层封装路由逻辑，结合 ThreadLocal 或 AOP 实现数据源动态切换
  • 灵活但维护成本高，适合中小型项目
2. 中间件方案（主流）

  • ShardingSphere-JDBC：支持分库分表、读写分离、柔性事务

  • MyCat：基于代理的分布式数据库中间件

  • TDDL：阿里内部方案

  • Vitess、PolarDB-X、OceanBase：云原生一体化解决方案

✅ 示例句式：

我们团队使用 ShardingSphere-JDBC 实现分库分表，通过配置 sharding rule 动态路由数据源，配合读写分离有效降低主库压力。

🔄 四、分布式事务与一致性问题

  • 分片后事务可能涉及多个库，难以使用本地事务

  • 解决方案：

    • 强一致性：使用 XA 两阶段提交（开销大）

    • 最终一致性：TCC、SAGA、消息补偿机制

    • 避开事务：通过业务逻辑控制，将操作限制在单分片内

✅ 示例句式：

我们尽量通过业务分片控制事务边界落在同一个库上，如果无法避免跨库，则使用 TCC 模式实现柔性事务控制。

📈 五、扩容与维护

  • 扩容：如原先 4 分片扩到 8 分片 → 面临数据迁移、路由规则变更

  • 迁移策略：双写 + 验证 + 切换（或使用工具如 DataX、Canal）

  • 监控点：热点分布、分片倾斜、主从同步延迟、慢 SQL

✅ 示例句式：

在分片扩容时，我们采用“新旧双写+数据比对+流量切换”策略，确保数据一致性和用户无感知迁移。

🧠 六、项目实战加分项（如有）

如果你曾实际在项目中用过分库分表，建议：

  • 简要描述场景、数据量、采用的中间件和策略

  • 强调你解决的关键问题，如：分片键选择、事务方案、查询优化

✅ 示例句式：

在智慧教育平台项目中，因题库表数据超千万，查询频繁，我们使用 ShardingSphere 做水平分表，按 question_id 取模分为 8 表，并使用缓存+预查询优化了分页性能。

如何分库分表

“MySQL 分库分表是为了应对数据量和并发量达到单机瓶颈时的扩展性问题。它将数据分散存储到多个数据库实例或表中。

主要方式有两种：

  1. 垂直拆分：
    • 分库： 按业务模块将不同表放到不同数据库（例如用户库、订单库）。
    • 分表： 按字段热度将一张表的大字段或不常用字段拆分到另一张表。
    • 目的： 缓解不同业务/字段的IO压力。
  2. 水平拆分：
    • 分库分表： 按数据行将一张表的数据分散到多个库或多个表中。
    • 目的： 彻底解决单表数据量过大的问题，提升整体吞吐量。
    • 关键： 选择合适的分片键（如ID取模、哈希、范围等），并依赖中间件（如ShardingSphere、MyCAT）或应用层逻辑来实现路由。

索引失效的场景

MySQL 索引失效，意味着查询无法使用索引而进行全表扫描，严重影响性能。主要原因在于查询条件破坏了索引的有序性或无法有效利用B+树结构。

常见的失效情况和避免方法有：

  1. 对索引列进行函数或表达式操作： WHERE YEAR(time) = 2023。
    • 避免： 把函数/表达式作用于查询值，如 WHERE time >= '2023-01-01' ...。
  2. LIKE 以 % 开头进行模糊匹配： WHERE name LIKE '%张三%'。
    • 避免： 尽量使用前缀匹配 LIKE '张三%' 或考虑全文索引。
  3. 隐式类型转换： WHERE phone = 123 (phone是VARCHAR)。
    • 避免： 确保查询条件类型与索引列类型一致。
  4. OR 连接非同一索引列： WHERE col1 = 'A' OR col2 = 'B'。
    • 避免： 考虑 UNION ALL 或为相关列创建联合索引。
  5. 联合索引未遵循“最左前缀原则”： (a,b,c) 索引，但查询 WHERE b = 1。
    • 避免： 查询条件从最左列开始且连续使用。
  6. != 或 NOT IN： 这类非等值查询通常效率低，优化器可能选择全表扫描。

判断索引是否生效，主要使用 EXPLAIN 命令，查看 type、key 和 Extra 字段，特别是 ALL、Using filesort、Using temporary 等提示。”

文章作者: foo1s
文章链接: http://foo1s.github.io/2025/05/08/mian-jing-chang-jian-wen-ti/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 foo1s !
赏
评论