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 数据文件