面试题学习笔记 | MySQL 进阶知识
MySQL 事务的二阶段提交是什么?
要回答这个问题,首先要了解在 MySQL 中两个重要日志 binlog 和 redolog 的区别:
redolog:
重做日志(redolog) 是 InnoDB 引擎内部的 事务日志,用于记录数据页的物理修改
redolog 是固定大小的环形结构,它在事务进行中不断写入,写满后会从头开始覆盖,保存为刷入磁盘的脏页日志
redolog 主要用于事务的崩溃恢复。它帮助 InnoDB 在崩溃之后通过日志重做未写入数据页的数据修改,确保事务数据的持久性
binlog:
二进制日志(binlog) 是 MySQL Server 层的日志,用于记录所有数据库的修改操作,包括增删改的 DML 语句和表结构修改的 DDL 语句
binlog 是追加写入的日志文件,事务提交完成之后才会写入。日志写满一个文件后,会创建新的文件继续写入,旧的日志文件不会被覆盖
binlog 主要用于数据恢复、主从复制、数据备份等场景,记录的是 SQL 语句的逻辑修改操作,而不是数据页的物理修改
假设没有二阶段提交的概念,对于这两个日志的写入方案,我们能够想到的大致是这两种:要么先写完 redolog,再写 binlog,要么先写 binlog,再写 redolog。这两种情况都会导致一些数据不一致的问题:
先写 redolog 再写 binlog:
假设写完 redolog 之后,MySQL 异常宕机,此时 binlog 还未完成写入。重启之后,由于 redolog 中有记录,可以恢复事务的修改,但 binlog 中没有本次事务提交的数据,后续通过 binlog 恢复时,本次事务的修改会丢失,导致数据丢失
先写 binlog 再写 redolog:
假设写完 binlog 之后,MySQL 异常宕机,此时 redolog 还没有写入。重启后,因 redolog 中没有记录,无法恢复这次事务的修改,但 binlog 里有数据,后续通过 binlog 恢复时,会复原本次事务的修改,但与原库数据不一致,造成数据不一致问题
因此,MySQL 事务设计了二阶段提交机制,以保证在崩溃恢复时,不会出现数据丢失或数据不一致的问题。
二阶段提交 分为两个阶段:
准备阶段:
当事务提交时,MySQL 的 InnoDB 引擎会先写入 redolog,并给它标记为prepare
,表示事务已准备提交,但未完成提交。此时,redolog 为预提交状态,尚未标记为完成提交提交阶段:
当 redolog 状态变为prepare
后,MySQL Server 会写入 binlog 记录用户操作。binlog 写入成功后,MySQL 会通知 InnoDB,将 redolog 状态改为commit
,完成事务提交过程
在 MySQL 异常宕机恢复时,会进行以下判断:
如果 redolog 处于 prepare 阶段,binlog 还未写入:
此时 redolog 还未 commit,异常恢复后,redolog 中的记录将不被应用,binlog 中没有记录,因此数据一致redolog 处于 prepare 阶段,binlog 已写入但 redolog 未 commit:
需要对比 redolog 和 binlog 中的 XID 是否一致。若一致,提交事务;若不一致,则回滚事务
二阶段提交是一个经典的分布式解决方案,在协商场景下,所有节点都被询问确认后才提交,避免后续的回滚或补充数据问题
MySQL 中如何解决深度分页的问题?
首先要了解 什么是深度分页:
深度分页 是指当数据量很大时,按照分页访问后面的数据时,必须先扫描前面的数据才能获取最终的数据。这种大批量的扫描会增加数据库负载,影响性能。本质上,深度分页的原因在于 LIMIT 操作在 server 层进行,而不是在存储引擎层,存储引擎会先查出所有数据,最终在返回给客户端时再进行 LIMIT 操作。
优化方案:
使用子查询:
先通过二级索引查询到 id,然后回表查找实际数据。二级索引数据量较少,能够减少扫描的数据量记录最大 id:
每次分页都返回当前查询的最大 id,然后进行范围查询。此方法适合连续查询,但若跳页则无法生效使用搜索引擎(如 Elasticsearch):
虽然可以解决一部分分页性能问题,但 Elasticsearch 也存在深度分页的挑战,需要进一步优化
什么是 MySQL 的主从同步机制?它是如何实现的?
重点思路:
主从同步 是 MySQL 中将主库(Master)的数据同步到一个或多个从库(Slave)的技术,主要通过 binlog 实现数据复制。主库执行写操作时,将操作记录到 binlog 中,并推送给从库。从库重放 binlog 就可以实现数据同步。
复制类型:
MySQL 支持三种复制方式:
同步复制: 主库等待所有从库确认接收到数据(性能差,但数据一致性高)
异步复制: 主库不等待从库响应(性能高,但数据一致性差)
半同步复制: 主库等待至少一个从库响应确认(性能折中,数据一致性较高)
主库同步流程:
接收到事务提交请求
更新数据
写入 binlog
向客户端响应
向从库推送 binlog 变更事件
从库同步流程:
由 I/O 线程将 binlog 写入 relay log
由 SQL 线程从 relay log 中重放事件,更新数据
向主库响应
如何处理 MySQL 的主从同步延迟?
首先,MySQL 的主从同步延迟是不可避免的,因为它本身是通过 binlog 从主库同步到从库。我们能做的只是尽量减少延迟时间,主要方法有:
使用缓存:
主库写入后先同步到缓存中,查询时优先查找缓存,避免延迟问题。但这会引入缓存一致性问题关键业务读主库:
将关键业务的读写操作都转移到主库,从库则只用于非关键查询二次查询:
从库查询不到数据时,重新到主库查询,虽然能减轻延迟,但可能增加主库压力强制写后立即读主库:
尽量将写操作后的读操作转移到主库,但这种方式比较死板,且影响性能
总结:
今天的学习帮助我更加清晰地理解了 MySQL 中的事务和日志的相关机制、主从同步以及深度分页问题,特别是二阶段提交和主从同步延迟的处理。在实际应用中,我会根据业务场景灵活运用这些知识来优化系统,避免性能瓶颈和数据一致性问题。