面试题学习笔记 | 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 中的事务和日志的相关机制、主从同步以及深度分页问题，特别是二阶段提交和主从同步延迟的处理。在实际应用中，我会根据业务场景灵活运用这些知识来优化系统，避免性能瓶颈和数据一致性问题。