mysql的 order by是怎么工作的？redo-log和binlog为什么采用双确认机制？

前言

一、order by 是怎么工作的？
二、redo-log和binlog为什么采用双确认机制
一、完整正常事务提交流程（主库）
各个节点宕机会怎么样？

1. redo log prepare 之前宕机
2. redo log 刷盘后宕机 没写binlog
3. binlog刷盘成功，但是redo log 未commit就宕机了
4. commit之后宕机
   加入主从复制后的完整流程（非常重要）
   主库已提交，但binlog未同步到从库
   从库执行 relay log 中途宕机
   把所有角色放在一张总览图（终极版）

---

mysql的 order by是怎么工作的？redo-log和binlog为什么采用双确认机制？

• 前言
• • 一、order by 是怎么工作的？
  • 二、redo-log和binlog为什么采用双确认机制
  • • 一、完整正常事务提交流程（主库）
    • 各个节点宕机会怎么样？
    • • 1. redo log prepare 之前宕机
      • 2. redo log 刷盘后宕机 没写binlog
      • 3. binlog刷盘成功，但是redo log 未commit就宕机了
      • 4. commit之后宕机
    • 加入主从复制后的完整流程（非常重要）
    • • 主库已提交，但binlog未同步到从库
      • 从库执行 relay log 中途宕机
    • 把所有角色放在一张总览图（终极版）

一、order by 是怎么工作的？

order by排序：通过索引获取所有符合条件的数据，然后放到sort_buff中进行排序。

如果需要查询的字段太长，可以通过配置项配置大小边界。

那么就会先获取排序字段和主键字段 然后sort_buff中排序，然后再去查表 获取完整结果。优化的方式可以通过在排序字段上建立索引（索引是有序的）来减少排序，通过建立覆盖索引，来减少回表

二、redo-log和binlog为什么采用双确认机制

为了保证事务的原子性+崩溃后恢复后的一致性，避免数据和日志的不一致性。

双确认机制，只有当两个日志都写入成功之后，事物才会真正的提交。

一、完整正常事务提交流程（主库）

┌────────────────────┐ │ 客户端发送 SQL │ └─────────┬──────────┘ │ ▼ ┌──────────────────────────┐ │InnoDB执行 SQL（改内存） │ │BufferPool│ └─────────┬────────────────┘ │ ▼ ┌──────────────────────────────────┐ │ 生成 redo log（PREPARE） │ │ redo log buffer │ └─────────┬────────────────────────┘ │ ▼ ┌──────────────────────────────────┐ │ redo log 刷盘（fsync） ✅ │ │ 【阶段一：Prepare】 │ └─────────┬────────────────────────┘ │ ▼ ┌──────────────────────────────────┐ │ 生成 binlog（事务事件） │ │ binlog cache │ └─────────┬────────────────────────┘ │ ▼ ┌──────────────────────────────────┐ │ binlog 刷盘（fsync） ✅ │ │ 【binlog 持久化】 │ └─────────┬────────────────────────┘ │ ▼ ┌──────────────────────────────────┐ │ redo log 写 COMMIT+刷盘 ✅ │ │ 【阶段二：Commit】 │ └─────────┬────────────────────────┘ │ ▼ ┌────────────────────┐ │ 事务提交成功返回 OK │ └────────────────────┘

刷盘：是指的从内存写入磁盘的操作，图中第一次 redo log 刷盘（fsync）是把redo log文件写到磁盘。刷盘的主要目的是防止崩溃后数据丢失。

当阶段二 Commit后，返回事物提交成功的同时，会异步把数据写入到.ibd文件中。

各个节点宕机会怎么样？

1. redo log prepare 之前宕机

事物没有开始 直接丢弃

2. redo log 刷盘后宕机 没写binlog

恢复流程：

扫描 redo log >发现PREPARE > 检查 binlog 不存在 >回滚事物

3. binlog刷盘成功，但是redo log 未commit就宕机了

扫描 redo log >发现PREPARE > 检查 binlog 存在 >补写redo log commit > 事物成功

4. commit之后宕机

扫描redo log > 发现commit 直接恢复

加入主从复制后的完整流程（非常重要）

Slave SQL Thread ↓ 读取 relay log ↓ 执行 SQL / 行事件 ↓ 生成 redo log ↓ redo log 刷盘 ↓ 数据落盘

主库已提交，但binlog未同步到从库

这是主从延迟，不是数据错误

从库执行 relay log 中途宕机

relay log 在
redo log 未 commit

恢复：

• 从库根据 redo log 回滚/重做
• 再继续执行 relay log

把所有角色放在一张总览图（终极版）

Client │ ▼ Master InnoDB │ redo log (prepare → commit) │ ▼ Master binlog ───────► Slave IO Thread │ ▼ relay log │ ▼ Slave SQL Thread │ ▼ Slave redo log │ ▼ Slave 数据文件