搁置很久，终于又开始写博客了，今天记一次线上故障的原因及原理
PS：本文为手写大纲+AI扩展并润色，DDL是第一生产力，AI是第二生产力，哈哈哈~
一、数据库查询执行原理
1. ORDER BY 的排序机制
SELECT * FROM table_a
ORDER BY period DESC
LIMIT 1
MySQL排序过程：
阶段1: 数据扫描
├─ 全表扫描 或 索引扫描
└─ 读取所有符合条件的记录到内存
阶段2: 排序比较
├─ 使用 period 字段进行比较
├─ 当 period 值相同时 → 比较结果为 "相等"
└─ "相等" 的记录之间的相对顺序是 **未定义的**
阶段3: LIMIT 截取
└─ 取排序后的第1条记录（但相等记录的顺序不稳定）
2. 为什么相同 period 的记录顺序不确定？
原因 A：物理存储顺序影响
磁盘上的数据页（Page）：
┌─────────────────────────────────┐
│ Page 1: 记录1-100 │
│ - 记录53: period=202401, id=53 │ ← 先写入
│ - 记录87: period=202401, id=87 │ ← 后写入
└─────────────────────────────────┘
如果没有索引，扫描顺序 = 物理存储顺序
但物理存储顺序受以下因素影响：
1. INSERT 顺序
2. UPDATE 导致的行迁移
3. DELETE 后的空间复用
4. 页分裂和合并
原因 B：索引扫描的不确定性
假设有索引 idx_period：
B+树索引结构：
[202401]
/ \
[202401,id=53] [202401,id=87]
↓ ↓
数据页A 数据页B
当 period 相同时：
- 索引中的排序只保证 period 字段有序
- 同一个 period 值下的多条记录，在B+树的叶子节点中：
* 可能按主键ID排序（InnoDB特性）
* 可能按插入顺序
* 但SQL标准不保证这个顺序是稳定的
二、具体场景分析
场景1：数据插入顺序导致的不一致
// 时刻 T1: 数据库中的数据
id | period | updateTime
----|---------|--------------------
100 | 202401 | 2024-01-10 10:00:00 ← 先插入
101 | 202401 | 2024-01-15 14:30:00 ← 后插入
99 | 202312 | 2024-01-05 09:00:00
// 执行查询 Query1
ORDER BY period DESC LIMIT 1
→ 可能返回 id=100 或 id=101（不确定）
为什么？
排序算法的稳定性：
期间值 记录ID 排序后的可能顺序
202401 → [100] → 情况A: [100, 101]
202401 → [101] → 情况B: [101, 100]
202312 → [99] → [99]
MySQL的 filesort（文件排序）：
- 使用 quicksort（快速排序）
- 快速排序是 **不稳定排序算法**
- 相等元素的相对位置可能改变
场景2：并发修改导致的不一致
// 线程A执行查询
Time: 10:00:00.000
SELECT * FROM table ORDER BY period DESC LIMIT 1
// 线程B同时插入新数据
Time: 10:00:00.001
INSERT INTO table (period, updateTime)
VALUES (202402, NOW())
// 结果：
查询A在10:00:00.000 → 返回 period=202401
查询A在10:00:00.002 → 返回 period=202402 (新数据)
隔离级别的影响：
READ COMMITTED（读已提交）：
事务A ──────────[查询]──────────→
事务B ──[开始]─[插入]─[提交]───→
↑
可能读到新数据
REPEATABLE READ（可重复读，MySQL默认）：
事务A ──[开始]──[查询1]──[查询2]──→
事务B ──────[插入+提交]──────────→
↑
查询1和查询2看到的数据可能不同
（取决于事务开始时间）
场景3：索引选择导致的不一致
-- 有两个索引
CREATE INDEX idx_period ON table(period);
CREATE INDEX idx_update_time ON table(update_time);
-- 查询时，MySQL可能选择不同的索引：
执行计划A: 使用 idx_period
├─ 按 B+树顺序扫描
└─ 叶子节点内部顺序 = 主键顺序
执行计划B: 全表扫描
├─ 按聚簇索引（主键）顺序扫描
└─ 然后 filesort 排序
不同执行计划 → 不同扫描顺序 → 相同period的记录顺序不同
查看执行计划：
EXPLAIN SELECT * FROM table_a
ORDER BY period DESC LIMIT 1;
+----+-------+-------+------+----------+
| id | type | key | rows | Extra |
+----+-------+-------+------+----------+
| 1 | index | idx_p | 1000 | Using... | ← 索引扫描
+----+-------+-------+------+----------+
-- 统计信息更新后可能变化：
ANALYZE TABLE table_a;
-- 再次执行可能选择不同的执行计划
三、period 是月份的特殊性
// period 字段特点
period = 202401 // 2024年1月
period = 202401 // 同一个月可能有多条记录
业务含义：
- 同一个月份（period）内，可能有：
├─ 不同日期的数据更新（updateTime不同）
├─ 同一天的多次更新
└─ 补录的历史数据
示例数据：
id | period | updateTime | 说明
----|---------|---------------------|------------------
201 | 202401 | 2024-01-31 23:59:00 | 月末汇总
202 | 202401 | 2024-02-05 10:00:00 | 次月初补录修正
203 | 202401 | 2024-02-10 14:00:00 | 再次修正
问题本质：
查询意图：获取最新期间的数据
代码实现：ORDER BY period DESC LIMIT 1
当最新期间有多条记录时：
- period 相同 → 排序无法区分
- LIMIT 1 随机取一条
- 可能不是业务上真正需要的"最新"记录
四、底层机制验证实验
-- 实验：创建测试数据
CREATE TABLE test_order (
id INT PRIMARY KEY AUTO_INCREMENT,
period VARCHAR(6),
update_time DATETIME
);
-- 插入相同period的数据
INSERT INTO test_order (period, update_time) VALUES
('202401', '2024-01-10 10:00:00'),
('202401', '2024-01-15 14:00:00'),
('202401', '2024-01-20 16:00:00');
-- 多次执行相同查询
SELECT * FROM test_order ORDER BY period DESC LIMIT 1;
-- 查看内部排序详情
SELECT * FROM test_order ORDER BY period DESC;
-- 结果可能是：
-- id=1, period=202401 ← 这三条记录的顺序
-- id=2, period=202401 ← 在不同时刻
-- id=3, period=202401 ← 可能不同
MySQL内部排序日志：
# 开启排序日志
SET optimizer_trace="enabled=on";
# 执行查询
SELECT * FROM test_order ORDER BY period DESC LIMIT 1;
# 查看排序过程
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
排序算法选择：
├─ 数据量小 → 内存排序（quicksort）
├─ 数据量大 → 外部排序（归并排序 + 临时文件）
└─ 相同值的排序顺序 → 依赖算法实现细节（不保证）
五、解决方案的底层原理
方案：添加第二排序字段
orderByDesc(TableA::getPeriod)
.orderByDesc(TableA::getUpdateTime)
.orderByDesc(TableA::getId)
原理：
排序比较逻辑：
比较两条记录 A 和 B：
1. 比较 period
if (A.period != B.period)
return A.period > B.period ? -1 : 1;
2. period相同，比较 updateTime
if (A.updateTime != B.updateTime)
return A.updateTime > B.updateTime ? -1 : 1;
3. updateTime也相同，比较 id（主键唯一）
return A.id > B.id ? -1 : 1;
结果：每条记录都有唯一的排序位置
复合索引优化：
CREATE INDEX idx_period_time_id
ON table_a(period DESC, update_time DESC, id DESC);
查询性能：
- 索引已排序 → 无需 filesort
- 直接读取第一条 → O(1) 复杂度
- 结果绝对稳定
总结
不一致的根本原因：
| 层面 | 原因 | 影响 |
|---|---|---|
| SQL语义 | ORDER BY 单一字段，相同值排序未定义 | 标准不保证顺序 |
| 排序算法 | 快速排序是不稳定排序 | 相等元素顺序可能变化 |
| 存储层 | 物理存储顺序、页分裂、碎片 | 扫描顺序不确定 |
| 索引层 | B+树同值节点顺序未定义 | 索引扫描顺序不稳定 |
| 并发层 | 事务隔离级别、MVCC机制 | 不同时刻看到不同数据 |
| 业务层 | period月份可能有多条记录 | 无法区分真正的"最新" |
数据库只保证你明确指定的排序规则，没有指定的部分都是不确定的。
