数据库进阶
ACID 与事务。
30 分钟
数据库进阶
这一课你将能做到
- 建索引并用
EXPLAIN ANALYZE亲眼验证「全表扫描 → 索引扫描」的提速效果 - 用事务把多条语句打包成「要么全成、要么全不算」，并解释 ACID 与四种隔离级别
- 说出连接池为什么必须存在，并在应用的连接串里正确配置它
为什么要学
上一课你会写 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');