文章

面试题学习笔记 | MySQL 进阶知识

MySQL 事务的二阶段提交是什么?

要回答这个问题,首先要了解在 MySQL 中两个重要日志 binlogredolog 的区别:

redolog:

  • 重做日志(redolog) 是 InnoDB 引擎内部的 事务日志,用于记录数据页的物理修改

  • redolog 是固定大小的环形结构,它在事务进行中不断写入,写满后会从头开始覆盖,保存为刷入磁盘的脏页日志

  • redolog 主要用于事务的崩溃恢复。它帮助 InnoDB 在崩溃之后通过日志重做未写入数据页的数据修改,确保事务数据的持久性

binlog:

  • 二进制日志(binlog) 是 MySQL Server 层的日志,用于记录所有数据库的修改操作,包括增删改的 DML 语句和表结构修改的 DDL 语句

  • binlog 是追加写入的日志文件,事务提交完成之后才会写入。日志写满一个文件后,会创建新的文件继续写入,旧的日志文件不会被覆盖

  • binlog 主要用于数据恢复、主从复制、数据备份等场景,记录的是 SQL 语句的逻辑修改操作,而不是数据页的物理修改

假设没有二阶段提交的概念,对于这两个日志的写入方案,我们能够想到的大致是这两种:要么先写完 redolog,再写 binlog,要么先写 binlog,再写 redolog。这两种情况都会导致一些数据不一致的问题:

  1. 先写 redolog 再写 binlog:

    • 假设写完 redolog 之后,MySQL 异常宕机,此时 binlog 还未完成写入。重启之后,由于 redolog 中有记录,可以恢复事务的修改,但 binlog 中没有本次事务提交的数据,后续通过 binlog 恢复时,本次事务的修改会丢失,导致数据丢失

  2. 先写 binlog 再写 redolog:

    • 假设写完 binlog 之后,MySQL 异常宕机,此时 redolog 还没有写入。重启后,因 redolog 中没有记录,无法恢复这次事务的修改,但 binlog 里有数据,后续通过 binlog 恢复时,会复原本次事务的修改,但与原库数据不一致,造成数据不一致问题

因此,MySQL 事务设计了二阶段提交机制,以保证在崩溃恢复时,不会出现数据丢失或数据不一致的问题。

二阶段提交 分为两个阶段:

  1. 准备阶段:
    当事务提交时,MySQL 的 InnoDB 引擎会先写入 redolog,并给它标记为 prepare,表示事务已准备提交,但未完成提交。此时,redolog 为预提交状态,尚未标记为完成提交

  2. 提交阶段:
    redolog 状态变为 prepare 后,MySQL Server 会写入 binlog 记录用户操作。binlog 写入成功后,MySQL 会通知 InnoDB,将 redolog 状态改为 commit,完成事务提交过程

在 MySQL 异常宕机恢复时,会进行以下判断:

  1. 如果 redolog 处于 prepare 阶段,binlog 还未写入:
    此时 redolog 还未 commit,异常恢复后,redolog 中的记录将不被应用,binlog 中没有记录,因此数据一致

  2. redolog 处于 prepare 阶段,binlog 已写入但 redolog 未 commit:
    需要对比 redologbinlog 中的 XID 是否一致。若一致,提交事务;若不一致,则回滚事务

二阶段提交是一个经典的分布式解决方案,在协商场景下,所有节点都被询问确认后才提交,避免后续的回滚或补充数据问题

MySQL 中如何解决深度分页的问题?

首先要了解 什么是深度分页
深度分页 是指当数据量很大时,按照分页访问后面的数据时,必须先扫描前面的数据才能获取最终的数据。这种大批量的扫描会增加数据库负载,影响性能。本质上,深度分页的原因在于 LIMIT 操作在 server 层进行,而不是在存储引擎层,存储引擎会先查出所有数据,最终在返回给客户端时再进行 LIMIT 操作。

优化方案:

  1. 使用子查询:
    先通过二级索引查询到 id,然后回表查找实际数据。二级索引数据量较少,能够减少扫描的数据量

  2. 记录最大 id:
    每次分页都返回当前查询的最大 id,然后进行范围查询。此方法适合连续查询,但若跳页则无法生效

  3. 使用搜索引擎(如 Elasticsearch):
    虽然可以解决一部分分页性能问题,但 Elasticsearch 也存在深度分页的挑战,需要进一步优化

什么是 MySQL 的主从同步机制?它是如何实现的?

重点思路:

主从同步 是 MySQL 中将主库(Master)的数据同步到一个或多个从库(Slave)的技术,主要通过 binlog 实现数据复制。主库执行写操作时,将操作记录到 binlog 中,并推送给从库。从库重放 binlog 就可以实现数据同步。

复制类型:

MySQL 支持三种复制方式:

  • 同步复制: 主库等待所有从库确认接收到数据(性能差,但数据一致性高)

  • 异步复制: 主库不等待从库响应(性能高,但数据一致性差)

  • 半同步复制: 主库等待至少一个从库响应确认(性能折中,数据一致性较高)

主库同步流程:

  1. 接收到事务提交请求

  2. 更新数据

  3. 写入 binlog

  4. 向客户端响应

  5. 向从库推送 binlog 变更事件

从库同步流程:

  1. 由 I/O 线程将 binlog 写入 relay log

  2. 由 SQL 线程从 relay log 中重放事件,更新数据

  3. 向主库响应

如何处理 MySQL 的主从同步延迟?

首先,MySQL 的主从同步延迟是不可避免的,因为它本身是通过 binlog 从主库同步到从库。我们能做的只是尽量减少延迟时间,主要方法有:

  1. 使用缓存:
    主库写入后先同步到缓存中,查询时优先查找缓存,避免延迟问题。但这会引入缓存一致性问题

  2. 关键业务读主库:
    将关键业务的读写操作都转移到主库,从库则只用于非关键查询

  3. 二次查询:
    从库查询不到数据时,重新到主库查询,虽然能减轻延迟,但可能增加主库压力

  4. 强制写后立即读主库:
    尽量将写操作后的读操作转移到主库,但这种方式比较死板,且影响性能

总结:

今天的学习帮助我更加清晰地理解了 MySQL 中的事务和日志的相关机制、主从同步以及深度分页问题,特别是二阶段提交和主从同步延迟的处理。在实际应用中,我会根据业务场景灵活运用这些知识来优化系统,避免性能瓶颈和数据一致性问题。

License:  CC BY 4.0