第5章：数据库——从会写SQL到能保证正确性

---

5.1 索引是一种有代价的数据结构

很多初级开发者容易走入极端：“给每一列都加上索引”。
认知基准：索引能够显著加速特定模式的读查询，但其本质是额外维护在磁盘与内存中的有序树形数据结构（B+ Tree）。每一次 INSERT、UPDATE 和 DELETE，底层都需要同步修改这些索引树，引发随机 I/O、页分裂（Page Split）并消耗巨量物理存储空间。

联合索引设计典型场景：

假设你的系统有一个异步分析任务模块，最核心的高频查询是：“查询某个用户最近提交的 20 个任务状态”：

SELECT id, status, created_at
FROM analysis_jobs
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

• 反模式设计：仅在 user_id 和 created_at 上分别单独建两个单列索引。数据库优化器必须在两者之间二选一，如果走 user_id 索引，依然需要将该用户的所有历史数据全部读取出来，并在内存中进行昂贵的 Filesort 排序；
• 高效复合索引：构建 (user_id, created_at DESC) 联合索引。首先基于 user_id 准确定位，且在索引树内部数据已经天然按照 created_at 逆序物理排列，直接读取前 20 条即可返回，彻底消除文件排序开销！

---

5.2 会看执行计划（EXPLAIN），才能谈真正优化

绝不能仅凭“代码没报错”或者“使用了 Index Scan”就盲目宣布优化成功。在 PostgreSQL 中使用深度剖析命令：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at
FROM analysis_jobs
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

核心指标观察维度：

• Execution Time：真实端到端物理执行耗时；
• Scan Type：是全表扫描（Seq Scan）、位图索引扫描（Bitmap Heap Scan）还是最优的索引扫描（Index Scan / Index Only Scan）；
• Buffers：shared hit（命中内存缓冲区页数）与 read（产生物理磁盘读取的冷数据页数）；
• Rows Filtered：扫描行数与最终过滤保留行数的比例（扫描 100 万行最终只留下 10 行说明索引极差）。

WARNING

避坑防线：务必注意 EXPLAIN ANALYZE 会真实在数据库中物理执行目标 SQL！如果在生产环境针对修改性语句（UPDATE / DELETE）运行该命令，会造成真实业务数据被修改或写入，必须在沙箱隔离环境中测试或在测试事务中主动 ROLLBACK！

---

5.3 事务与隔离级别：事务不等于自动解决所有并发问题

ACID 特性中的“隔离性（Isolation）”并非铁板一块，不同隔离级别允许不同的并发副作用：

隔离级别	脏读 (Dirty Read)	不可重复读 (Non-Repeatable Read)	幻读 (Phantom Read)	写偏斜 (Write Skew)
Read Committed (读已提交)	彻底杜绝	允许发生	允许发生	允许发生
Repeatable Read (可重复读)	彻底杜绝	彻底杜绝	基本杜绝	依然可能发生
Serializable (串行化)	彻底杜绝	彻底杜绝	彻底杜绝	彻底杜绝

关键认知纠偏：

• 开启了 @Transactional 事务，丝毫不代表不会发生超卖或重复扣费！
• 典型的转账或余额扣减逻辑，如果采用简单的读取余额判断，在多事务并发交织下依然会把余额扣成负数。
• 真实生产资金场景必须引入严格的双边复式记账流水模型、账务幂等键、以及死锁重试退避机制。

---

5.4 数据库是“事实来源”，缓存不是

很多系统因盲目引入 Redis 导致线上数据灾难：

1. 缓存更新失败时如何保持一致？
2. 缓存失效瞬间，高并发流量会不会把底层数据库瞬间击穿打垮？
3. 用户刚在前端修改完个人资料，刷新后由于读到未同步的旧缓存，发现资料没变，该业务能否容忍？

IMPORTANT

核心原则：PostgreSQL / MySQL 永远是系统持久化的“单一事实来源（Single Source of Truth）”，Redis 仅是提升读取吞吐的易失加速层。在独立开发初期，如果没有达到每秒数千次高频重复读，过早引入 Redis 只会徒增运维成本与数据不一致风险。

---

5.5 实战任务与验收自测

实战任务：

设计并实现四张核心业务表：users（用户表）、jobs（任务表）、job_results（任务明细结果表）、orders（订单交易表）：

• 给出清晰的外键关联、唯一索引约束（Unique Constraint）、复合检索索引以及自动化数据库版本迁移脚本（Alembic / Flyway）；
• 使用脚本灌入 10 万条仿真任务记录，构造复杂场景慢查询并记录优化前后的 EXPLAIN (ANALYZE, BUFFERS) 对比报告。

验收思考题：

• 为什么说数据库底层的“唯一约束（Unique Constraint）”是防范业务并发漏洞最不可突破的最后一道生命防线？
• 为什么即使熟练使用 ORM（SQLAlchemy / Prisma / Hibernate），也绝对不能取代对原生 SQL 与存储引擎原理的深度理解？