필사 모드: DB 完全指南 — 内部结构、索引、查询规划器、分区、Vector DB (Season 2 Ep 13, 2025)

前言 — 为什么必须深入了解 DB

“会写 SQL 不就够了吗?”的反例:

• P99 延迟尖刺 → 不理解索引与规划器就无法排查
• 事务隔离问题 → 不懂隔离级别就无法复现、无法修复
• 存储成本暴涨 → 需要分区与压缩策略
• 分布式 DB 选型 → 不懂内部结构就会选错
• 向量检索 → 需要设计嵌入存储

2025 年，资深工程师深入理解 DB 是必备素养。

第 1 部 — 存储引擎的两种哲学

1.1 B-Tree (Balanced Tree)

读取优化。传统 RDBMS(PostgreSQL, MySQL InnoDB)的默认选择。

     [50]
    /    \
  [20]  [80]
 /  \   /  \
[10][30][70][90]

特点:

• 平衡二叉树 (实际 fanout 有数百)
• O(log N) 查找
• In-place update → 写放大低
• 作为索引结构被广泛使用

缺点: 随机写多时会分裂并产生 I/O。

1.2 LSM-Tree (Log-Structured Merge)

写入优化。RocksDB, Cassandra, ScyllaDB, LevelDB, ClickHouse。

Memtable (RAM)
   ↓ flush
L0 SSTables (磁盘)
   ↓ compact
L1 SSTables
   ↓ compact
L2 SSTables ...

特点:

• 所有写入都是 append-only → 顺序 I/O
• Compaction 事后整理
• 写入速度非常快
• 把空间与 CPU 花在 compaction 上

缺点: 读取需要遍历多层 → 用 Bloom Filter 等手段缓解。

1.3 选择标准

1.4 2024–2025 趋势: B-Tree 与 LSM 的融合

• Aurora, Neon: 存储层分离 (类似 S3 的对象存储 + 本地缓存)
• TigerBeetle: 金融专用，LSM + 确定性
• FoundationDB: 分层，ACID + Key-Value
• Postgres Incremental Materialized View: 读取最优 + 实时

第 2 部 — 索引深入

2.1 索引的 8 种类型

2.2 索引设计的 5 条原则

1. 查询优先: 看真实查询再设计
2. 选择性: 选择性高的列优先
3. 复合索引的顺序: 从最常用的过滤条件开始
4. 用 INCLUDE 做 Covering: 避免回表
5. 不用的索引要删除: 会拖慢写入性能

2.3 索引用不上的十大原因

1. 对列使用了函数: WHERE LOWER(email) = ... → 需要 Expression Index
2. 隐式类型转换: WHERE id = '123' (id 是 int)
3. 前置通配符: LIKE '%foo' → Full scan
4. OR 条件: 优化器可能放弃 → 改用 UNION
5. Not Equals (!=): 大多不会走索引
6. NULL 比较: Postgres 可以对 IS NULL 建索引，设计时要留意
7. 数据量太小: 规划器更偏好 Sequential Scan
8. 复合索引顺序错了: 对 (a, b) 索引写 WHERE b = ?
9. 统计信息过期: 需要 ANALYZE
10. 索引 Bloat: 需要 REINDEX

第 3 部 — 查询规划器与 EXPLAIN

3.1 规划器的职责

把 SQL (声明式) 转换成执行计划 (过程式)。基于代价的优化 (CBO)。

3.2 读懂 PostgreSQL EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'KR'
GROUP BY u.name;

输出示例:

HashAggregate  (cost=1234..5678 rows=100)
  Group Key: u.name
  ->  Hash Join  (cost=500..1200 rows=10000)
        Hash Cond: o.user_id = u.id
        ->  Seq Scan on orders o  (cost=0..800 rows=100000)
        ->  Hash  (cost=300..300 rows=500)
              ->  Index Scan on users_country_idx  (cost=0..300 rows=500)

读法:

• 由内向外
• cost=startup..total
• rows: 预估行数 (与 ANALYZE 的实测值对比)
• actual time: 实际耗时
• Buffers: 内存与磁盘访问

3.3 重要的节点类型

3.4 Postgres 没有 Plan Hint

MySQL、Oracle 有提示语法。Postgres 靠调整 ANALYZE + 统计目标 + random_page_cost 来引导。

第 4 部 — 事务隔离

4.1 重新定义 ACID

• Atomicity: 全有或全无
• Consistency: 不违反约束
• Isolation: 并发执行看起来像串行执行
• Durability: 一旦提交就保留

4.2 隔离级别的 4 个层次 (SQL-92)

4.3 各 DB 的默认隔离级别

4.4 Snapshot Isolation vs Serializable

• Snapshot Isolation (SI): 每个事务看到自己开始时刻的快照
  • 缺点: 可能出现 Write Skew
• Serializable (MVCC SSI): SI + 冲突检测 → abort
  • Postgres 通过 SERIALIZABLE 提供 SSI

4.5 Write Skew 示例

两名医生中至少要有一人值班:

-- T1: 读取 on_call，看到 2 人 → OK
-- T2: 同时读取 on_call，看到 2 人 → OK
-- 两者都把自己的状态改成 off-call
-- 提交 → 值班 0 人。约束被破坏

只有在 Serializable 下才会被检测出来。

4.6 Deadlock

两个以上的事务互相等待对方的锁。DB 检测到后 → 牺牲其中一个 (abort)。

预防:

• 保持加锁顺序一致 (按 ID 升序)
• SELECT FOR UPDATE NOWAIT
• 重试逻辑 (幂等性 + 退避)

第 5 部 — MVCC (Multi-Version Concurrency Control)

5.1 MVCC 基础

“写不阻塞读，读不阻塞写。”

为每一行保留多个版本，事务读取属于自己快照的那个版本。

5.2 Postgres 的 MVCC

• Tuple 头: xmin (创建事务)、xmax (删除事务)
• Vacuum: 清理死元组。必须做。
• autovacuum: 自动，但仍需调优

5.3 Vacuum Bloat 问题

• Long-running transaction 会阻塞 autovacuum
• Table/Index Bloat → 性能下降
• 解决: pg_repack、VACUUM FULL、周期性监控

5.4 MySQL InnoDB 的 MVCC

• 旧版本放在 Undo Log 里
• 由 Purge Thread 清理
• Clustered Index (按 Primary Key 做物理排序)

第 6 部 — 分区与分片

6.1 垂直 vs 水平

• 垂直: 按列切分 (几乎不用)
• 水平 (Horizontal): 按行切分。Partitioning 与 Sharding。

6.2 Partitioning (单个 DB 内)

Postgres 原生支持 (10+):

CREATE TABLE events (
  id bigserial,
  event_time timestamptz,
  data jsonb
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

优点: 分区裁剪、易于管理 (DROP old partition)。 局限: 仍在单个 DB 内。受单台服务器容量限制。

6.3 Sharding (多个 DB)

把数据分散到多台服务器。

分片键的选择:

• 基数要高
• 考虑访问模式
• 便于再平衡
• 避免 Hot Shard

分片方式:

• Hash: 均匀，范围查询 ❌
• Range: 范围查询 OK，有 Hot Shard 风险
• Directory-based: 灵活，需要管理元数据

6.4 跨分片查询

默认就很贵。 JOIN、ORDER BY 跨多个分片时，就要在应用层做 merge。

解决:

• 把相关数据放在同一分片 (按 tenant_id)
• Read Replica + Materialized View
• OLAP 单独处理 (数据仓库)

6.5 分布式 SQL DB (自动分片)

• CockroachDB
• YugabyteDB
• Spanner
• TiDB
• Vitess (构建在 MySQL 之上)
• Citus (Postgres 扩展)

第 7 部 — Replication 与 HA

7.1 Replication 的方式

• Physical (WAL/Binlog): 字节级，精确
• Logical: SQL/Row 级，灵活 (异构、部分复制)

7.2 Postgres Replication 2025

• Streaming Replication: 传输 WAL
• Logical Replication (10+): 按表粒度
• Synchronous 选项: 确定但慢
• Patroni: HA 管理 (事实标准)

7.3 Failover 模式

检测到 Primary 故障
  ↓ (consensus: etcd、Patroni)
在 Standby 中选出最新的
  ↓
提升为新 Primary + 应用切换 DSN
  ↓
原 Primary 进行 Rebuild

核心: 防止 Split-brain (Fencing)。

7.4 用好 Read Replica

• 分散 Read-heavy 负载
• 接受 Replication lag
• “Read-your-writes”要走 Primary

第 8 部 — PostgreSQL 在 2025 年的一枝独秀

8.1 Postgres 为什么赢了

2024–2025 年的 DB 选型中，Postgres 成为事实上的默认值的理由:

1. 可扩展性: 数量庞大的 Extension
2. JSONB: 内置 NoSQL 能力
3. pgvector: Vector DB 能力
4. PostGIS: GIS 最强
5. Timescale: 时序
6. Citus: 分布式
7. Logical Replication: CDC 与迁移
8. 许可证: PostgreSQL License (宽松)
9. 社区: 稳定，不依附于某家大厂
10. 云托管: Aurora, RDS, Cloud SQL, Neon, Supabase

8.2 Postgres Extension Top 15 (2025)

8.3 2024–2025 的 Postgres 生态

• Neon: Serverless Postgres，分支能力
• Supabase: Postgres + Auth + Realtime
• Xata: DevEx 优先
• Aurora Postgres: AWS 托管
• ParadeDB: 检索扩展

第 9 部 — NoSQL: 什么时候用

9.1 NoSQL 的 5 个类别

9.2 2025 年的现实: “Postgres 就够了”

大多数 NoSQL 的使用场景 Postgres 都能覆盖:

• Document → JSONB
• Key-Value → UNLOGGED 表，或者搭配 Redis
• Time-Series → TimescaleDB
• Graph → Apache AGE extension

仍然要选 NoSQL 的情况:

• 全球低延迟 (DynamoDB Global Table)
• 超大规模写入 (Cassandra)
• 图算法专用 (Neo4j)

9.3 Redis 的位置

内存缓存、队列、排行榜、Rate Limit。属于“大家多少都会有一个”的工具。

2024 年的许可证变更 → Valkey 分叉 (AWS、Google 主导)。选择变多了。

第 10 部 — Vector DB

10.1 为什么需要 Vector DB

为 LLM 嵌入检索、推荐等场景服务的近似最近邻 (ANN) 搜索。

10.2 ANN 算法

• HNSW (Hierarchical Navigable Small World): 快且准，内存 ↑
• IVF (Inverted File): 基于聚类
• PQ (Product Quantization): 节省内存
• HNSW + PQ: 混合

10.3 2025 年 Vector DB 对比

10.4 2025 年的推荐

• 简单 RAG: pgvector (在现有 Postgres 上加装)
• 大规模与高级过滤: Qdrant 或 Weaviate
• 完全托管: Pinecone
• 本地/边缘: LanceDB

10.5 pgvector 示例

CREATE EXTENSION vector;

CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  content text,
  embedding vector(1536)
);

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- 检索
SELECT content
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

第 11 部 — 六个月 DB 路线图

Month 1: SQL 深入

• Window Function、CTE、Recursive
• 事务隔离与 Deadlock 实操
• 索引设计

Month 2: Postgres 内部

• 彻底读懂 EXPLAIN
• MVCC 与 Vacuum
• WAL 与 Replication

Month 3: Storage Engine

• DDIA 第 3 章 (Storage)
• 动手实现 B-Tree / LSM
• 学习 RocksDB 与 Cassandra

Month 4: 分布式 DB

• CockroachDB、Spanner 的结构
• Sharding 设计
• Replication 运维

Month 5: Vector DB

• pgvector 实战
• 理解 HNSW
• 搭建 RAG 数据层

Month 6: 优化与运维

• Slow Query 分析
• 索引调优
• Connection Pooling (PgBouncer)
• Schema Migration 不停机 (Postgres 进阶)

第 12 部 — DB 检查清单 12 条

1. 知道 B-Tree vs LSM-Tree 的选择标准
2. 按用途知道 Postgres 的 5 种索引类型
3. 能说出 索引用不上的 5 个原因
4. 能读懂 EXPLAIN ANALYZE 的输出
5. 知道 事务隔离的 4 个级别 以及各自防止的现象
6. 能解释 Write Skew
7. 知道 MVCC 与 Vacuum 的关系
8. 知道 Partitioning vs Sharding 的区别
9. 知道 Hash vs Range 分片 的取舍
10. 知道 Postgres Extension Top 5
11. 知道 pgvector 与 Qdrant 的选择标准
12. 知道 Connection Pool (PgBouncer) 的必要性

第 13 部 — DB 反模式 10 条

1. 只信 ORM，从不检查查询: N+1、加载过多列很常见
2. 一个接一个无节制地加索引: 写入性能下降。没用到的要删掉
3. Long transaction: MVCC bloat。事务要短
4. 迷信默认隔离级别: 不了解 Read Committed 的边界，就会写出 Write Skew 的 bug
5. 一个一个地管理 Connection: 没有 Pool 却开几千个 connection → Postgres 崩掉
6. 什么都塞进 JSONB: 放弃了模式的优势。结构化数据应该用列
7. 分片键选错: Hot Shard → 重新分片的地狱
8. 迁移毫无计划导致 downtime: 不停机迁移策略是必须的
9. 忽视 Vacuum 调优: 不知不觉表就 bloat 了 10 倍
10. 把 DB 当消息队列: 能做但不推荐。请用 Redis/Kafka

结语 — DB 是“生产系统的引力”

应用可以换，DB 模式却很难改。DB 是生产系统中惯性最大的那部分。

所以一开始就要设计好:

• 用索引设计撑住五年的结构
• 用分区与分片为 10 倍增长做准备
• 用隔离级别保证数据完整性
• 用可观测性与备份为灾难做准备

2025 年的 DB 工程是这样的:

• Postgres First: 大多数问题用它就能解决
• Vector DB: LLM 时代的必需品
• 分布式 DB: 只给超大规模服务
• NoSQL: 只有理由明确时才用

下一篇预告 — “网络完全指南: HTTP/3、QUIC、TLS 1.3、gRPC、WebSocket、CDN”

Season 2 Ep 14 讲互联网的管道，也就是网络。下一篇包括:

• TCP vs UDP vs QUIC
• HTTP/1.1 → 2 → 3 的演进
• TLS 1.3 Handshake
• gRPC vs REST vs GraphQL vs tRPC
• WebSocket vs SSE vs Long Polling
• CDN 架构 (Cloudflare, Fastly)

那些看不见的线路，下一篇见。