数据库进阶

这一课你将能做到

1. 建索引并用 EXPLAIN ANALYZE 亲眼验证「全表扫描 → 索引扫描」的提速效果
2. 用事务把多条语句打包成「要么全成、要么全不算」，并解释 ACID 与四种隔离级别
3. 说出连接池为什么必须存在，并在应用的连接串里正确配置它

为什么要学

上一课你会写 SQL 了，但「能跑」和「能扛」是两回事：数据到十万行，没索引的查询从 1ms 变成 200ms；并发下单两条 SQL 不打包，就可能把库存扣成负数；每次请求都新建数据库连接，几百并发就把连接数打爆。这四个问题——索引、事务、隔离、连接池——是后端面试数据库部分 100% 会问的内容，也是生产事故的高发区。

本课全程在 psql 里做实验，每个知识点都让你亲眼看到现象，而不是背结论。

先建立图像

索引的原理，一本书的类比就够了：

没有索引 = 一页一页翻书找关键词（全表扫描，逐行比对）
有索引   = 先查书末尾的关键词索引页，直接翻到第 137 页（B-tree，几次定位）

B-tree（平衡多路搜索树）示意：
                [50]
             /        \
        [20,35]      [70,85]
        /  |  \      /  |  \
     ... 叶子节点里存的是「索引列的值 → 行的物理位置」

10 万行数据，全表扫描要比对 10 万次；三层 B-tree 只需 3~4 次页访问。这就是百倍差距的来源。

事务则像「购物车」：从加入第一件到结算，中途任何人看到的都是同一批货，要么全部结算成功，要么整辆清空重来。

跟着做

接上一课的 notedb（没有就重建）。

步骤 1：造 10 万行数据，感受没有索引的痛

\c notedb

CREATE TABLE accounts (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name    varchar(50)  NOT NULL UNIQUE,
    balance numeric(12,2) NOT NULL DEFAULT 0 CHECK (balance >= 0)
);

-- generate_series 一次生成 10 万行，比循环 INSERT 快得多
INSERT INTO accounts (name, balance)
SELECT 'user_' || g, (random() * 10000)::numeric(12,2)
FROM generate_series(1, 100000) AS g;

现在执行一次精确查询，并计时：

EXPLAIN ANALYZE SELECT * FROM accounts WHERE name = 'user_99999';

关键输出一行：Execution Time: xx.xxx ms，以及 Seq Scan on accounts（顺序扫描 = 全表扫描，rows=100000）。记住这个数字，等下对比。

步骤 2：建索引，再看一次

CREATE INDEX idx_accounts_name ON accounts(name);

EXPLAIN ANALYZE SELECT * FROM accounts WHERE name = 'user_99999';

两处变化：扫描方式变成 Index Scan using idx_accounts_name；执行时间通常降一个数量级以上（本机测试大约从 15~30ms 降到 0.1ms 量级，机器不同绝对值不同，倍数才是重点）。

步骤 3：事务——转账必须有「后悔药」

建两张账户，模拟转账（A 转 B 100 元 = 两条 UPDATE）：

INSERT INTO accounts (name, balance) VALUES ('alice', 500), ('bob', 100);

BEGIN;                                                    -- 开启事务
UPDATE accounts SET balance = balance - 100 WHERE name = 'alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'bob';
COMMIT;                                                   -- 两条一起生效

-- 反面教材：转错了一半想反悔
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'alice';
-- 发现 bob 的账户被冻结了，这笔转账不该发生：
ROLLBACK;                                                 -- alice 的钱原路退回，如同没发生过
SELECT name, balance FROM accounts WHERE name IN ('alice','bob');

如果去掉 BEGIN/COMMIT，psql 默认每条语句独立提交——第一条成功第二条失败时，钱就凭空蒸发了。**原子性（Atomicity）**保护的就是这个场景。

步骤 4：开两个 psql 窗口，亲眼看看隔离级别

窗口 A 和窗口 B 同时连到 notedb。演示「不可重复读」：

-- 窗口 A
BEGIN;
SELECT balance FROM accounts WHERE name = 'alice';   -- 假设显示 500
-- 先停在这里！

-- 窗口 B（默认自动提交，执行完立即生效）
UPDATE accounts SET balance = 400 WHERE name = 'alice';

-- 窗口 A 继续
SELECT balance FROM accounts WHERE name = 'alice';   -- 读到 400！同一事务里两次读结果不同
COMMIT;

这就是 Read Committed（PG 默认级别）：事务内每次读都拿最新已提交数据。想让事务内读数一致，改用可重复读：

-- 窗口 A 重来一遍
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE name = 'alice';   -- 400
-- 窗口 B: UPDATE ... SET balance = 300 ...;
SELECT balance FROM accounts WHERE name = 'alice';   -- 还是 400（读到事务开始时的快照）
COMMIT;                                              -- 若期间数据被别人改过且要写回，会报序列化失败

四种隔离级别对应的关系（面试必背）：

隔离级别	脏读	不可重复读	幻读
Read Uncommitted	可能	可能	可能
Read Committed	不可能	可能	可能
Repeatable Read	不可能	不可能	PG 中不可能*
Serializable	不可能	不可能	不可能

* PG 的 RR 用 MVCC 快照实现了比 SQL 标准更强的保证；MySQL InnoDB 默认就是 RR。

步骤 5：连接池——为什么不能每次请求都开新连接

建一条 TCP 连接 + 认证 + 后端进程 fork，PG 一次连接的建立成本是毫秒级的，而且单个实例连接数有上限（默认 100）。高并发下「每请求一连接」的结果是：请求排队、数据库被打挂。

应用侧的解法是连接池：启动时建好 N 条连接反复复用，用完归还。Node 里 pg 驱动的池化示例：

// pool-demo.mjs —— npm install pg 后可运行
import pg from 'pg';
const pool = new pg.Pool({
  connectionString: 'postgresql://postgres:postgres@localhost:5432/notedb',
  max: 10,                      // 池中最大连接数，按数据库 max_connections 预算
  idleTimeoutMillis: 30000,     // 空闲连接 30 秒后回收
});

const res = await pool.query('SELECT name, balance FROM accounts WHERE name = $1', ['alice']);
console.log(res.rows);
await pool.end();

用 Prisma 时则是连接串参数：?connection_limit=10&pool_timeout=5。绝大多数 ORM/驱动都内置池，你要做的是显式配置大小而不是用默认值裸奔。

讲清楚要点

1. 索引不是越多越好：每个索引都是一棵要随写入维护的 B-tree——查询提速，INSERT/UPDATE 变慢，还占磁盘。只为「经常出现在 WHERE / JOIN / ORDER BY 里的列」建索引，联合索引注意列顺序（最左前缀原则）。
2. ACID 四个字各管一件事：原子性（全成或全不成）、一致性（约束永远不被破坏）、隔离性（并发事务互不干扰，按级别递减）、持久性（提交后断电不丢，靠 WAL 日志）。
3. MVCC 是 PG 并发的底座：每行数据带版本信息，读不阻塞写、写不阻塞读；代价是删除/更新只是「标记」，需要 VACUUM 清理死元组——这也是面试常问的「PG 为什么需要 VACUUM」。
4. 慢查询排查三板斧：① pg_stat_statements 扩展找最慢 SQL；② EXPLAIN ANALYZE 看执行计划找 Seq Scan、估算行数偏差；③ 对症下药——加索引 / 改写 SQL / 减少返回列。
5. 池大小不是越大越好：数据库总连接数 = 实例数 × 每实例池大小，必须小于数据库 max_connections。计算密集型系统，池大小接近 CPU 核数 × 2 往往比几百条连接吞吐更高。

你可能会懵的地方

• 为什么加了索引有时还是慢？ 常见四个原因：① 索引列上包了函数或类型不匹配（WHERE upper(name)=...、字符串列传了数字）；② 前置通配 LIKE '%x'；③ 区分度太低（性别列建 B-tree 没意义）；④ 优化器判断走索引反而慢（要回表的行太多，直接顺序扫）。用 EXPLAIN ANALYZE 看实际选择，不要猜。

• RR 级别下 PG 为什么没有幻读，标准里不是有吗？ PG 的 Repeatable Read 基于事务开始时的快照（MVCC），整个事务看到的是同一版本的数据，幻读自然消失；但代价是并发写冲突时提交会报 could not serialize access，需要应用重试。

• EXPLAIN 输出里 rows 估算和实际差很多说明什么？ 统计信息过期，优化器在「瞎猜」。执行 ANALYZE 表名; 收集统计信息；大批量导入数据后尤其要跑。

• 索引失效场景能背下来就够了吗？ 背结论不如会看执行计划。面试说「我一般用 EXPLAIN ANALYZE 验证，常见失效场景有函数包裹、隐式转换、前置通配、OR 条件混合」——先给方法再给清单，是加分回答。

• 什么时候该考虑分库分表？ 入门阶段：几乎永远不需要。先索引、先缓存、先读写分离；单表几千万行之前，绝大多数「慢」都是 SQL 和索引问题，不是容量问题。

错法对照

错法	问题	正解
给每个查询列都建独立索引	写入性能雪崩、优化器选择困难	按查询模式建少量联合索引
长事务开着不提交（挂着 BEGIN 去吃午饭）	锁被持有、VACUUM 无法清理、快照膨胀	事务内只放必要语句，用完立即提交
SELECT * 扫大表	拖垮 IO、无法走覆盖索引	明确列出需要的列
连接串不配池大小，连接数随实例数线性涨	打爆数据库 max_connections	统一规划池上限，压测验证
在事务里做 RPC/发消息等慢操作	长事务 + 不确定的回滚语义	数据库事务只包数据库操作
靠 try/catch 包住多条 SQL 就以为安全了	没开事务，中间态照样落库	BEGIN...COMMIT 包住，异常走 ROLLBACK

卡点急救

1. EXPLAIN ANALYZE 输出太长看不懂：只看三样——扫描类型（Seq Scan/Index Scan）、每步 rows（估算 vs actual 偏差大就有统计问题）、最后的 Execution Time。
2. 两个窗口演示时窗口 B 的 UPDATE 一直转圈：窗口 A 的事务锁住了行没提交。去窗口 A 执行 COMMIT 或 ROLLBACK；彻底卡死时查 SELECT pid, state, query FROM pg_stat_activity WHERE wait_event IS NOT NULL; 后 SELECT pg_terminate_backend(pid);。
3. 10 万行 INSERT 执行很慢：检查是不是用了程序循环逐条 INSERT；数据库端用 generate_series 一次成批（如步骤 1）。
4. 创建索引锁表把服务卡住：生产环境用 CREATE INDEX CONCURRENTLY（不阻塞读写，但不能在事务内执行）。
5. 连接池报 timeout exceeded：池太小或连接泄漏（借了不还）。检查代码是否每次查询都新建 Pool/Client；必要时调大 max 并加 pool_timeout 快速失败。

面试常问

• 讲讲 B+ 树索引的原理，为什么不用哈希或二叉树？ B+ 树矮胖（千万级数据三四层）、叶子节点成链表利于范围扫描；哈希不支持范围与排序；二叉树在磁盘上层数太深、IO 次数多。索引存磁盘，减少 IO 次数是设计核心。

• 四种隔离级别分别解决什么问题？MySQL 和 PG 默认级别？ 从低到高依次消除脏读 → 不可重复读 → 幻读。MySQL InnoDB 默认 Repeatable Read，PG 默认 Read Committed；PG 的 RR 靠 MVCC 快照天然避免幻读。

• 什么是 MVCC？ 多版本并发控制：读操作按快照读取历史版本，写操作创建新版本，读写互不阻塞。PG 在行内存版本链靠 VACUUM 回收，MySQL InnoDB 用 undo log 实现回滚段。

• 线上一条 SQL 突然变慢，你怎么排查？ 给出方法论：确认变化点（数据量/执行计划/锁等待）→ pg_stat_statements 或慢日志定位 → EXPLAIN (ANALYZE, BUFFERS) 看计划 → 检查统计信息与索引 → 必要时改写 SQL 或加缓存。

• 转账扣款的并发安全怎么保证？ 事务 + 行级锁：UPDATE accounts SET balance=balance-100 WHERE id=1 AND balance>=100，靠数据库行锁串行化对同一行的修改，CHECK 约束兜底负数。高并发热点账户再谈队列/账户拆分。

自测三问

1. 复现步骤 1-2：你的查询从多少 ms 降到多少 ms？执行计划从什么变成了什么？
2. 用两个 psql 窗口复现「不可重复读」，并把它改成 REPEATABLE READ 让两次读一致。
3. 「给注册时间 created_at 建索引后，注册接口 INSERT 变慢了」——解释原因并说出你的取舍。

小结一句话

索引用 B-tree 换查询速度、事务用 ACID 换数据正确性、隔离级别在一致性与并发之间做取舍、连接池用复用换吞吐——四个开关都在你手里，前提是会用 EXPLAIN 验证而不是凭感觉。

延伸

• PostgreSQL 索引官方文档
• 使用 EXPLAIN
• 下一课：Redis 与缓存——把热数据搬到内存里，查询从毫秒级干到微秒级。