后端-05-数据库操作与ORM
这篇笔记讲什么
应用代码写得好不好,一半取决于你怎么跟数据库说话。这篇笔记覆盖:连接池、SQL 进阶(JOIN/窗口函数/CTE)、事务边界与锁、索引与
EXPLAIN、N+1 查询、ORM 取舍、迁移管理、一致性实践、分页与批处理。前置知识:数据库原理 里的关系模型、范式、索引与隔离级别。本篇讲的是把这些原理用到生产代码里。
一、应用层与数据库的交互层次
从你的业务代码到数据库进程,中间隔着四层:
业务代码 service.create_order(...)
│
├── ORM(对象关系映射) Hibernate / EF Core / SQLAlchemy
│ 对象 → SQL,结果集 → 对象;负责身份映射、脏检查、懒加载
│
├── 查询构建器(Query Builder) Knex / Drizzle / jOOQ
│ 类型安全的 SQL 拼装,不做对象生命周期管理
│
├── 驱动(Driver) pg / mysql2 / JDBC / ADO.NET
│ 实现数据库线协议,序列化参数,解析结果集
│
└── 连接池(Connection Pool) HikariCP / Npgsql Pool / pg.Pool
复用 TCP 连接与数据库会话,控制并发上限
│
▼
数据库进程(PostgreSQL / MySQL / SQL Server)
| 层次 | 关注点 | 选错时的症状 |
|---|---|---|
| ORM | 开发效率、对象图维护 | 生成意料外的 SQL;N+1;批量更新逐行执行 |
| 查询构建器 | SQL 表达力 + 类型安全 | 复杂查询仍要退回原生 SQL |
| 驱动 | 协议兼容、参数绑定、类型映射 | 时间类型/JSON 类型转换错误 |
| 连接池 | 并发上限、连接复用 | 高并发排队超时;空闲连接被数据库杀掉 |
一条核心认知
ORM 不会替你思考 SQL。 它只是把「写 SQL」换成了「调用方法」,写得不好照样全表扫描、照样 N+1。
判断标准:你能不能说出
user.orders这一行代码访问数据库几次?说不出来,就是在赌运气。
二、连接池(Connection Pool)
2.1 为什么需要连接池
建立一次数据库连接的真实成本:
| 步骤 | 大致耗时 |
|---|---|
| TCP 三次握手 | 1 个 RTT(局域网 < 1ms,跨机房 10~30ms) |
| TLS 握手(若加密) | 1~2 个 RTT + 非对称加密运算 |
| 数据库认证(如 SCRAM) | 取决于密码哈希迭代次数,常是 CPU 密集 |
| 后端进程分配会话(PostgreSQL 是一进程一连接) | 分配内存、初始化 catalog 缓存 |
一条连接的开销在毫秒级,而一次简单查询可能只要 0.1ms。若每个请求都新建连接,99% 的时间花在握手上。连接池的作用:
- 复用已建立的连接,把握手成本摊薄到接近 0;
- 限制并发:池大小就是「最多几个请求同时打数据库」的闸门,保护数据库不被打穿;
- 健康检查:定期验证连接可用(
SELECT 1),自动剔除坏连接; - 统一超时:获取连接超时、语句超时、空闲回收时长都在一处配置。
2.2 池大小如何估算
PostgreSQL 官方 wiki 的经验公式(HikariCP 作者也广泛引用):
注意这是单个数据库实例的总连接数建议,不是单个应用的值。若数据库有 8 核、SSD:
看起来小得反直觉,原因是:连接数不等于并发能力。数据库能真正并行执行的查询数受 CPU 核心数限制;连接再多也只是在排队,还会因为上下文切换、内存占用(每连接约几 MB 工作内存)而变慢。
实践中的做法:
| 场景 | 单实例池大小建议 | 说明 |
|---|---|---|
| 小型应用(1 个服务) | 留一点余量应对突发 | |
| 微服务 N 个实例 | ,且每个池 | 必须保证 所有池 max_connections |
| 有慢查询/报表 | 拆出只读副本,报表走副本 | 慢查询不该占用写库连接 |
| Serverless / 短生命周期 | 用外部连接池器(PgBouncer) | 每个函数的池都会随实例销毁 |
连接数配大了反而更慢
用
pgbench做过的经典实验:连接数从 20 提到 200,TPS 可能下降。原因是锁竞争、WAL 写入串行化、每连接的work_mem累积吃光内存。「加连接数解决慢查询」是把排队时间换成切换开销,治标不治本。
2.3 max_connections 与池大小的关系
举例:PostgreSQL max_connections = 100,你有 3 个服务,分别部署 4、4、2 个实例:
正确做法是留 20%~30% 给运维、备份、迁移工具:
-- PostgreSQL:查看当前连接使用情况与上限
SELECT
(SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn,
count(*) AS total,
count(*) FILTER (WHERE state = 'active') AS active,
count(*) FILTER (WHERE state = 'idle') AS idle,
count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx
FROM pg_stat_activity;
-- 找出「开了事务却不提交」的连接——这是最危险的一类泄漏
SELECT pid, usename, application_name, state,
now() - state_change AS stuck_for,
left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY stuck_for DESC;
-- MySQL:Max_used_connections 接近 max_connections 就说明池配置或排查有问题
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'wait_timeout';
连接数超限时该做什么
优先级从高到低:
1. 加 PgBouncer / ProxySQL 做连接复用(transaction 模式下能把 1000 个客户端连接映射到 20 个数据库连接);
2. 排查慢查询:可能 20 个连接里有 18 个卡在同一条没索引的 SQL 上;
3. 检查连接泄漏(见下);
4. 最后才考虑提高
max_connections——每连接的成本你也一起提高了。
2.4 连接泄漏的症状与排查
典型症状(按出现顺序):
| 症状 | 说明 |
|---|---|
| 运行一段时间后报「获取连接超时」 | 池里连接被借光,且没人还 |
数据库侧 idle 连接数持续上升不下降 | 连接确实还在,只是应用不再使用 |
| 重启应用立刻恢复 | 泄漏是进程内的(池状态),不是数据库侧 |
idle in transaction 堆积 | 借了连接、开了事务、既没提交也没回滚 |
| 数据库报「too many clients already」 | 泄漏已超过 max_connections |
三种最常见的泄漏原因:
// ❌ 错误 1:异常路径没有释放连接
async function bad1() {
const client = await pool.connect();
const res = await client.query('SELECT * FROM orders'); // 若这里抛错…
client.release(); // …永远执行不到,连接泄漏
return res.rows;
}
// ✅ 正确:try/finally 保证释放
async function good1() {
const client = await pool.connect();
try {
const res = await client.query('SELECT * FROM orders');
return res.rows;
} finally {
client.release(); // 无论成功、抛错、提前 return,都会归还
}
}
// ✅ 更好:不需要事务/会话临时设置时,直接用 pool.query,它自动借还
async function good2() {
const { rows } = await pool.query('SELECT * FROM orders');
return rows;
}
// ❌ 错误 2:忘了 using / 手动 Open 后没关(C# + ADO.NET / EF Core)
var conn = new NpgsqlConnection(connStr);
conn.Open();
var cmd = new NpgsqlCommand("SELECT 1", conn);
cmd.ExecuteScalar(); // conn 从未 Dispose,底层物理连接一直占着
// ✅ 正确:using 声明保证 Dispose,Npgsql 内部会把连接归还池而不是真关闭
using var conn = new NpgsqlConnection(connStr);
await conn.OpenAsync();
using var cmd = new NpgsqlCommand("SELECT 1", conn);
await cmd.ExecuteScalarAsync();
// 说明:ADO.NET 的 Dispose 语义是「归还池」,所以 using 不会造成反复建连的开销
# ❌ 错误 3:全局持有一个 Session / Connection 长期不关(SQLAlchemy 常见)
session = Session(engine) # 模块级全局对象
def get_user(uid): return session.get(User, uid) # 连接一直被这个 Session 占着
# ✅ 正确:每次请求一个短生命周期 session,由框架的依赖注入管理
from contextlib import contextmanager
from sqlalchemy.orm import Session
@contextmanager
def session_scope():
s = Session(engine) # SQLAlchemy 2.x 推荐 sessionmaker
try:
yield s
s.commit() # 正常结束才提交
except Exception:
s.rollback()
raise
finally:
s.close() # 关键:归还连接到池
# FastAPI 里用依赖注入,请求结束自动 close
def get_db():
with session_scope() as s:
yield s
排查步骤:
- 打开池的监控指标(HikariCP 的
hikaricp_connections_active、pg.Pool的pool.totalCount/pool.idleCount); - 打日志记录「借出/归还」以及借出时的调用栈(仅调试期开启,开销大);
- 用上面的
pg_stat_activity查询看数据库侧的state; - 检查是否有
.connect()却没有对应的release()/Dispose()/close(); - 检查事务是否在所有分支上都被
commit或rollback。
// 给池加监控:active 数长期等于 max 且 idle 为 0 → 泄漏或池太小
setInterval(() => {
console.log(`pool total=${pool.totalCount} idle=${pool.idleCount} waiting=${pool.waitingCount}`);
}, 5000);
三、SQL 进阶
3.1 四种 JOIN 的语义差异
| 类型 | 语义 | 保留左表无匹配行 | 保留右表无匹配行 | 典型用途 |
|---|---|---|---|---|
INNER JOIN | 只保留两侧都匹配的组合 | ❌ | ❌ | 取「有订单的用户」 |
LEFT JOIN | 保留左表全部行,右表缺失补 NULL | ✅ | ❌ | 取「所有用户,含没下过单的」 |
RIGHT JOIN | 保留右表全部行 | ❌ | ✅ | 极少用,调换表顺序写成 LEFT JOIN 更清晰 |
FULL OUTER JOIN | 两侧都保留 | ✅ | ✅ | 对账:找出「只在 A」「只在 B」的记录 |
-- 建表与数据(PostgreSQL / SQLite / MySQL 8 均可执行;类型已取三者公共子集)
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
amount_cents INTEGER NOT NULL,
created_at TEXT NOT NULL -- ISO 8601 字符串,便于跨数据库演示
);
INSERT INTO users (id, name) VALUES (1,'Alice'), (2,'Bob'), (3,'Carol');
INSERT INTO orders (id, user_id, amount_cents, created_at) VALUES
(100, 1, 1999, '2026-01-05T10:00:00Z'),
(101, 1, 5000, '2026-02-11T12:30:00Z'),
(102, 2, 800, '2026-02-20T09:15:00Z');
-- 四种 JOIN 的对比:注意 Carol 没有订单,Bob 只有一单
SELECT u.name, o.id AS order_id, o.amount_cents
FROM users u
INNER JOIN orders o ON o.user_id = u.id; -- 只有 Alice×2, Bob×1(3 行)
SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id; -- 4 行,Carol 的 order_id 为 NULL
-- 实用技巧:LEFT JOIN + IS NULL = 反连接(找出没有订单的用户)
SELECT u.id, u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL; -- 只有 Carol
-- 等价的 NOT EXISTS 写法(通常优化器会把两者归一,但 EXISTS 更不易出错)
SELECT u.id, u.name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- 对账场景:FULL OUTER JOIN
SELECT a.id AS a_id, b.id AS b_id
FROM orders a
FULL OUTER JOIN orders b ON a.id = b.id AND a.amount_cents = b.amount_cents
WHERE a.id IS NULL OR b.id IS NULL;
两个高频 JOIN 错误
1. 把过滤条件写在
ON还是WHERE:对LEFT JOIN,WHERE o.status='paid'会把NULL行过滤掉,退化成INNER JOIN;写在ON里才能保留左表全部行。这是最经典的「为什么我的左连接丢数据」。2.
JOIN造成行数放大:一对多 JOIN 后SUM(amount)会重复累加。先聚合再 JOIN:```sql
-- ❌ 错误:Alice 有 2 单,若再 JOIN 商品表会重复计算
SELECT u.name, SUM(o.amount_cents) FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.name;
-- ✅ 若需同时统计多张一对多表,用子查询分别聚合后再 JOIN
SELECT u.name, COALESCE(agg.total, 0) AS total_cents
FROM users u
LEFT JOIN (
SELECT user_id, SUM(amount_cents) AS total FROM orders GROUP BY user_id
) agg ON agg.user_id = u.id;
```
3.2 子查询与 JOIN 的取舍
| 维度 | 子查询(IN / EXISTS / 标量子查询) | JOIN |
|---|---|---|
| 可读性 | 意图更聚焦(「哪些用户有订单」) | 需要读完整连接条件 |
| 结果集语义 | 不会放大行数 | 会放大行数,需 DISTINCT 或 GROUP BY 修正 |
| 性能 | 现代优化器常改写为半连接(semi-join),差异通常很小 | 大表连接时优化器选择更多(hash/merge join) |
NOT IN 的坑 | 右侧含 NULL 时整个结果为空 | LEFT JOIN ... IS NULL 不受影响 |
| 相关子查询 | 逐行执行,可能极慢(优化器可能物化为 hashed subplan) | 通常是集合式执行 |
-- ❌ NOT IN + NULL:orders.user_id 只要有一行是 NULL,整个查询返回 0 行
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);
-- ✅ 首选:NOT EXISTS,语义明确且对 NULL 安全
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- 标量子查询用于「取每人的最近一单」很直观,但大表上性能差(每行执行一次)
SELECT u.name,
(SELECT MAX(created_at) FROM orders o WHERE o.user_id = u.id) AS last_order_at
FROM users u;
3.3 GROUP BY 与 HAVING 的逻辑执行顺序
SQL 是声明式的,但理解逻辑执行顺序能解释 90% 的「为什么这里不能写别名」:
FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
① ② ③ ④ ⑤ ⑥ ⑦ ⑧
由此推出三条实用结论:
WHERE在GROUP BY之前,所以WHERE里不能用聚合函数(那时还没分组);HAVING在GROUP BY之后,用于过滤分组结果,可以包含聚合函数;SELECT里的别名在WHERE/GROUP BY中一般不可用(因为SELECT在它们之后求值)。ORDER BY是例外——它在SELECT之后,所以可以用别名。
-- 找出「订单数 ≥ 2 且总金额 > 3000 分」的用户
SELECT u.name,
COUNT(*) AS order_count,
SUM(o.amount_cents) AS total_cents
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2026-01-01T00:00:00Z' -- 分组前过滤行:能用索引
GROUP BY u.id, u.name
HAVING COUNT(*) >= 2 AND SUM(o.amount_cents) > 3000 -- 分组后过滤组
ORDER BY total_cents DESC; -- 可以用 SELECT 的别名
-- 结果:Alice, 2, 6999
性能要点:把条件尽量推早
WHERE能过滤掉的行就不要留到HAVING。WHERE o.created_at >= ...可以走索引;写成HAVING MIN(o.created_at) >= ...则必须先把全部行分组,无法用索引过滤。
3.4 窗口函数
窗口函数(Window Function)在不折叠行的前提下做分组计算——这是它与 GROUP BY 的本质区别:GROUP BY 把 N 行压成 1 行,窗口函数保留 N 行。
| 函数 | 作用 | 典型场景 |
|---|---|---|
ROW_NUMBER() | 组内连续编号,不并列 | 取每组最新一条(去重) |
RANK() | 并列同名次,跳号(1,1,3) | 排行榜 |
DENSE_RANK() | 并列同名次,不跳号(1,1,2) | 分档、等级 |
LAG(col, n) | 取前 n 行(默认 1) | 环比、上一次登录 |
LEAD(col, n) | 取后 n 行 | 下一次订单间隔 |
SUM() OVER(...) | 累计求和 | 累计销售额 |
AVG() OVER(...) | 移动平均 | 趋势平滑 |
NTILE(n) | 分成 n 桶 | 用户分位(RFM) |
FIRST_VALUE() / LAST_VALUE() | 窗口内首/末值 | 与组内极值对比 |
-- 建一张用于演示的销售表
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
region TEXT NOT NULL,
sold_on TEXT NOT NULL, -- 'YYYY-MM-DD'
amount INTEGER NOT NULL
);
INSERT INTO sales (id, region, sold_on, amount) VALUES
(1,'east','2026-01-01',100), (2,'east','2026-01-02',150), (3,'east','2026-01-03',150),
(4,'west','2026-01-01',200), (5,'west','2026-01-02', 50);
-- ① ROW_NUMBER / RANK / DENSE_RANK 的差别:注意 150 并列时的编号
SELECT region, sold_on, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn,
RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS drnk
FROM sales
ORDER BY region, rn;
-- east: 150→rn1, 150→rn2, 100→rn3(并列但 rn 不同)
-- rnk: 1,1,3 drnk: 1,1,2
-- ② 取每个 region 金额最大的一条(去重取最新/最优的标准解法)
SELECT region, sold_on, amount FROM (
SELECT region, sold_on, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC, id DESC) AS rn
FROM sales
) t
WHERE rn = 1; -- east: 150(01-03), west: 200(01-01)
-- 说明:ORDER BY 里加 id DESC 是为了在 amount 完全相同时结果确定(稳定排序)
-- ③ LAG / LEAD:计算环比增长与相邻记录间隔
SELECT region, sold_on, amount,
LAG(amount) OVER (PARTITION BY region ORDER BY sold_on) AS prev_amount,
amount - LAG(amount) OVER (PARTITION BY region ORDER BY sold_on) AS diff,
LEAD(sold_on) OVER (PARTITION BY region ORDER BY sold_on) AS next_day
FROM sales
ORDER BY region, sold_on;
-- ④ SUM ... OVER:累计值与占比(不折叠行的同时得到汇总信息)
SELECT region, sold_on, amount,
SUM(amount) OVER (PARTITION BY region ORDER BY sold_on) AS running_total,
SUM(amount) OVER (PARTITION BY region) AS region_total,
ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY region), 2) AS pct_of_region
FROM sales
ORDER BY region, sold_on;
窗口函数写法的三个坑
1.
WHERE里不能直接用窗口函数(它在SELECT阶段才求值)→ 必须套一层子查询或 CTE,如上面WHERE rn = 1;2. 不给窗口排序会得到不确定结果:
ROW_NUMBER() OVER (PARTITION BY region)没有ORDER BY时编号随便给,去重会丢错行;3.
LAST_VALUE默认窗口是「到当前行为止」,想取整组最后一个要显式写ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
3.5 CASE WHEN 条件聚合
把「行」按条件折叠成「列」,是行转列(pivot)的标准做法,一条 SQL 替代多次查询:
-- 按区域统计各月份销售额,一次扫描出宽表
SELECT
region,
SUM(CASE WHEN sold_on LIKE '2026-01%' THEN amount ELSE 0 END) AS jan,
SUM(CASE WHEN sold_on LIKE '2026-02%' THEN amount ELSE 0 END) AS feb,
COUNT(CASE WHEN amount >= 150 THEN 1 END) AS high_value_orders
FROM sales
GROUP BY region
ORDER BY region;
-- east: jan=400, feb=0, high_value_orders=2
-- west: jan=250, feb=0, high_value_orders=1
-- 条件聚合也是「一次查询拿多个指标」的利器:避免为每个指标发一次请求
SELECT
COUNT(*) AS total_orders,
COUNT(*) FILTER (WHERE status = 'paid') AS paid, -- PostgreSQL/SQLite 支持 FILTER
COUNT(*) FILTER (WHERE status = 'refunded') AS refunded,
COALESCE(SUM(amount_cents) FILTER (WHERE status='paid'), 0) AS paid_cents
FROM orders_demo;
-- MySQL 不支持 FILTER,写成 SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END)
3.6 CTE(WITH)与递归 CTE
CTE(Common Table Expression,公用表表达式) 用 WITH 定义一个临时命名结果集。价值在于把复杂查询拆成可读的步骤,而不是写出嵌套五层的子查询。
-- 分步骤计算:先算每人汇总,再筛出高价值用户
WITH user_totals AS (
SELECT user_id, COUNT(*) AS cnt, SUM(amount_cents) AS total
FROM orders
GROUP BY user_id
),
vip AS (
SELECT user_id FROM user_totals WHERE total >= 5000
)
SELECT u.name, t.cnt, t.total
FROM users u
JOIN user_totals t ON t.user_id = u.id
JOIN vip v ON v.user_id = u.id;
-- Alice, 2, 6999
-- 递归 CTE:查询组织架构中某人的所有下属(含多层)
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
manager_id INTEGER -- NULL 表示顶层
);
INSERT INTO employees (id, name, manager_id) VALUES
(1,'CEO',NULL), (2,'CTO',1), (3,'Eng',2), (4,'Intern',3), (5,'CFO',1);
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id, 1 AS depth
FROM employees
WHERE id = 1 -- 锚点(anchor):起点
UNION ALL
SELECT e.id, e.name, e.manager_id, s.depth + 1
FROM employees e
JOIN subordinates s ON e.manager_id = s.id -- 递归项:接上一轮结果
)
SELECT id, name, depth FROM subordinates ORDER BY depth, id;
-- 1 CEO 1 / 2 CTO 2 / 5 CFO 2 / 3 Eng 3 / 4 Intern 4
-- ⚠️ 递归 CTE 必须有终止条件,否则无限循环。
-- PostgreSQL 可加 UNION(去重)或用 depth 限制:
-- ... WHERE s.depth < 10
CTE 不一定更快
PostgreSQL 12 之前,
WITH是优化栅栏(optimization fence),会被物化,外层条件无法下推;12 之后非递归 CTE 默认可以被内联,行为更像子查询。若你确实想强制物化(例如防止重复计算昂贵的聚合),PostgreSQL 用
WITH x AS MATERIALIZED (...),MySQL 8 用WITH x AS (...) /+ MATERIALIZE /或NO_MERGE提示。具体语法以官方最新文档为准。
四、事务的正确使用
4.1 ACID 回顾与交叉引用
| 特性 | 含义 | 崩溃/并发时保证什么 |
|---|---|---|
| 原子性 Atomicity | 事务内操作要么全做要么全不做 | 中途崩溃不会留下半成品 |
| 一致性 Consistency | 事务前后满足约束(外键、唯一、检查) | 约束由数据库强制,不靠应用自觉 |
| 隔离性 Isolation | 并发事务互不干扰(按隔离级别) | 防脏读、不可重复读、幻读 |
| 持久性 Durability | 提交后即使断电也不丢 | 依赖 WAL / redo log 与 fsync |
前置的原理细节(WAL、MVCC、锁类型、各级别异常现象)见 数据库原理。本节只讲代码里怎么用对。
4.2 事务边界如何划定
原则:一个事务 = 一个业务不变量(invariant)的原子变更。
| 规则 | 做法 | 反例 |
|---|---|---|
| 尽量短 | 只在必须原子的写操作上开事务 | 把 HTTP 调用、发邮件、生成 PDF 放进事务 |
| 不跨用户交互 | 不要「等前端确认」再提交 | 「下单开事务 → 等用户付款 → 提交」(连接被霸占几十分钟) |
| 不含慢操作 | 大文件上传、外部 API、复杂计算移出事务 | 在事务里调第三方支付接口 |
| 读多写少可不开 | 单条 SELECT 隐式自动提交 | 为一次查询显式 BEGIN(MySQL 里还会开一致性快照) |
| 幂等边界对齐 | 事务边界与 Idempotency-Key 覆盖范围一致 | 部分提交 + 重试导致重复数据 |
# ❌ 反例:事务里做网络 IO,连接被占用数秒
def create_order_bad(uid, items):
with session_scope() as s:
order = Order(user_id=uid, items=items)
s.add(order)
s.flush() # 拿到 order.id
requests.post("https://pay.example.com/pre", json={...}) # 慢!事务一直开着
s.commit()
# ✅ 正确:把外部调用移到事务外,事务只包裹数据库写入
def create_order_good(uid, items):
# 1) 事务只做本地写入,毫秒级完成
with session_scope() as s:
order = Order(user_id=uid, items=items, status="pending")
s.add(order)
s.flush()
order_id = order.id
# 2) 事务外调用外部服务
try:
requests.post("https://pay.example.com/pre", json={"order_id": order_id}, timeout=3)
except requests.Timeout:
# 3) 补偿:另开一个短事务把状态改为失败(而不是回滚已提交的订单)
with session_scope() as s:
s.query(Order).filter_by(id=order_id).update({"status": "failed"})
raise
return order_id
4.3 长事务的危害
| 危害 | 机制 | 后果 |
|---|---|---|
| 阻塞 DDL | ALTER TABLE 需拿排他锁,要等所有长事务结束 | 发版卡住,连锁阻塞后续所有查询(PostgreSQL 尤其严重) |
| 撑大表膨胀 | MVCC 下旧版本行不能被清理(VACUUM 无法回收) | 表和索引持续膨胀,磁盘报警 |
| 锁等待雪崩 | 长事务持有的行锁让后续事务排队 | 连接池耗尽 → 应用整体不可用 |
| 主从延迟 | 长事务的 WAL 必须在从库整体重放完成才可见 | 读写分离场景读到旧数据 |
| 回滚代价高 | 一个大 UPDATE 中途失败要回滚 | 回滚耗时可能是执行的数倍 |
-- PostgreSQL:找出运行超过 5 分钟的活动事务与长期空闲事务
SELECT pid, now() - xact_start AS xact_age, state, left(query, 100) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND now() - xact_start > interval '5 minutes'
ORDER BY xact_age DESC;
-- 定位阻塞链:谁在等谁
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid;
-- 兜底:设置语句级超时,防止单条 SQL 无限期挂着
SET statement_timeout = '5s'; -- 当前会话
-- 或按角色/数据库设置默认值(DDL,需权限)
ALTER ROLE app_user SET statement_timeout = '5s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';
生产环境必须设置的两个超时
```sql
SET idle_in_transaction_session_timeout = '30s'; -- 杀掉「开着事务发呆」的连接
SET statement_timeout = '5s'; -- 单条语句最长执行时间
```
没有这两个超时,一个忘了
commit的连接就能把整个库拖住。MySQL 对应wait_timeout与max_execution_time。
4.4 悲观锁与乐观锁
悲观锁(Pessimistic Locking):假设冲突一定会发生,读的时候就把行锁住。
-- PostgreSQL / MySQL 8:行级排他锁,直到事务结束才释放
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- 其他事务的同一条会等待
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- 不等待,立刻失败(适合用户可立即看到「系统繁忙」的场景)
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- 只锁「稍后要更新的行」,跳过已被锁的行(队列消费常用)
SELECT id FROM jobs WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 10;
-- MySQL:注意 FOR UPDATE 必须走索引,否则会锁大量行甚至锁表
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- ✅ 主键,只锁 1 行
SELECT * FROM accounts WHERE name = 'Alice' FOR UPDATE; -- ⚠️ name 无索引 → 锁全表扫描过的行
乐观锁(Optimistic Locking):假设冲突很少,先读、后写,写入时检查版本未变。
-- 版本号方案
UPDATE articles
SET title = '新标题', version = version + 1, updated_at = now()
WHERE id = 42 AND version = 7; -- 若返回「影响 0 行」→ 有人先改了 → 应用层重试或报 409
-- 时间戳方案(精度可能不足,高并发下会漏检)
UPDATE articles
SET title = '新标题', updated_at = now()
WHERE id = 42 AND updated_at = '2026-10-11T08:30:00.123456Z';
| 对比项 | 悲观锁 FOR UPDATE | 乐观锁(版本号/时间戳) |
|---|---|---|
| 前提假设 | 冲突频繁 | 冲突罕见 |
| 加锁时机 | 读取时立即加锁 | 不枷锁,写入时校验 |
| 数据库压力 | 锁等待、可能死锁,占用连接 | 无锁,失败由应用重试 |
| 响应时延 | 高并发下排队明显 | 冲突时需重试,可能多次失败 |
| 事务长度要求 | 必须短(锁持有到提交) | 可以不那么严格(但读取到写入之间仍是窗口) |
| 实现复杂度 | 低(一条 FOR UPDATE) | 中(需要 version 列 + 重试逻辑 + 返回 409/412) |
| 典型场景 | 库存扣减、账户余额、秒杀 | 后台编辑文章、配置修改、表单提交 |
| 死锁风险 | 有(注意加锁顺序一致性) | 无 |
怎么选
- \> 冲突概率高、必须成功(钱、库存)→ 悲观锁,并严格控制事务长度;
- \> 冲突罕见、失败可重试(内容编辑)→ 乐观锁,配合 HTTP
412 Precondition Failed或409 Conflict(见 后端-01-HTTP与RESTfulAPI设计);- 不要混用:同一份数据一半代码用
FOR UPDATE、一半用版本号,锁语义会互相穿透,问题极难复现。
4.5 隔离级别的实际影响与幻读
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | PostgreSQL 实现 | MySQL InnoDB 默认 |
|---|---|---|---|---|---|
READ UNCOMMITTED | 可能 | 可能 | 可能 | 实际等同 READ COMMITTED | 允许(实际也基本不会脏读) |
READ COMMITTED | 不可能 | 可能 | 可能 | 默认,每条语句取新快照 | 支持 |
REPEATABLE READ | 不可能 | 不可能 | InnoDB 靠间隙锁大部分避免 | 支持(快照隔离,可能写冲突报错) | 默认,间隙锁防幻读 |
SERIALIZABLE | 不可能 | 不可能 | 不可能 | SSI(可串行化快照隔离),可能报序列化失败 | 加锁读,性能明显下降 |
幻读(Phantom Read):同一事务内两次执行同一范围查询,第二次多出了别的已提交事务插入的行。
-- 幻读演示(在 READ COMMITTED 下,会话 A 的第二次查询会多出一行)
-- 会话 A
BEGIN;
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- 假设得到 2
-- 会话 B(另一个连接)
INSERT INTO orders (id, user_id, amount_cents, created_at) VALUES (103, 1, 100, '2026-03-01T00:00:00Z');
COMMIT;
-- 会话 A 再次查询(READ COMMITTED 下每条语句取新快照)
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- 得到 3 → 幻读
COMMIT;
处理幻读的三种手段:
| 手段 | 做法 | 代价 |
|---|---|---|
| 提高隔离级别 | REPEATABLE READ / SERIALIZABLE | 并发下降,可能出现序列化失败需重试 |
| 加锁读 | SELECT ... FOR UPDATE(InnoDB 会用间隙锁锁住范围) | 锁范围可能比预期大,易死锁 |
| 唯一约束 + 幂等 | 直接 INSERT ... ON CONFLICT DO NOTHING,让数据库来裁判 | 最推荐:无锁等待,语义清晰 |
-- 首选的「防重复插入」写法:靠唯一约束而不是「先查再插」
ALTER TABLE orders ADD CONSTRAINT uq_orders_idem UNIQUE (idem_key);
INSERT INTO orders (id, user_id, amount_cents, created_at, idem_key)
VALUES (104, 1, 999, '2026-03-02T00:00:00Z', 'key-abc')
ON CONFLICT (idem_key) DO NOTHING;
-- MySQL 等价写法
-- INSERT IGNORE INTO orders (...) VALUES (...);
-- 或 INSERT ... ON DUPLICATE KEY UPDATE id = id;
「先查再插」在并发下必然失效
```python
if not db.query(Order).filter_by(idem_key=k).first(): # 两个请求同时到这里,都判为「不存在」
db.add(Order(idem_key=k)); db.commit() # 两个都插入 → 重复数据
```
检查与插入之间没有锁,这个窗口就是竞态。唯一约束是唯一可靠的裁判,应用层的查重只能作为「友好提示」。
五、索引与查询优化
5.1 EXPLAIN 关键字段解读
-- PostgreSQL:实际执行 + 缓冲区统计,最有用的一档
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, title FROM articles WHERE author_id = 7 AND status = 'published'
ORDER BY created_at DESC LIMIT 20;
输出示例(节选)与逐行解读:
Limit (cost=0.43..25.10 rows=20 width=40) (actual time=0.031..0.142 rows=20 loops=1)
Buffers: shared hit=26
-> Index Scan Backward using idx_articles_author_status_created on articles
(cost=0.43..1245.00 rows=20 width=40) (actual time=0.029..0.135 rows=20 loops=1)
Index Cond: ((author_id = 7) AND (status = 'published'::text))
Buffers: shared hit=26
Planning Time: 0.210 ms
Execution Time: 0.186 ms
| 字段 | 含义 | 怎么用 |
|---|---|---|
cost=启动..总 | 规划器估算的代价(任意单位,默认 1.0 ≈ 读一个磁盘页) | 只用于比较不同计划,不能当毫秒 |
rows | 估算输出行数 | 与 actual rows 差距 > 10 倍 → 统计信息过期,跑 ANALYZE |
width | 估算行宽(字节) | 宽行意味着更多 I/O |
actual time=启动..总 | 真实毫秒数 | 找最内层耗时大的节点 |
loops | 该节点被执行次数 | 总耗时 = actual time × loops;loops 很大是 N+1 的信号 |
Buffers: shared hit/read | 命中缓存/磁盘读的页数 | read 高说明缓存不够或首次访问 |
Rows Removed by Filter | 被过滤掉的行数 | 数值远大于输出行数 → 索引没覆盖过滤条件 |
Sort Method: quicksort Memory: 74kB | 排序方式与内存 | 出现 external merge Disk: → 排序落盘,考虑加索引或调 work_mem |
Hash Buckets/Batches | 哈希连接的内存/落盘 | Batches > 1 表示落盘 |
常见扫描/连接节点:
| 节点 | 含义 | 何时是好/坏 |
|---|---|---|
Seq Scan | 全表顺序扫描 | 小表或需读大部分行时是最优;大表返回少量行时是坏信号 |
Index Scan | 走索引并回表 | 返回行少时好 |
Index Only Scan | 只读索引(覆盖索引) | 最好;Heap Fetches 高说明可见性映射未更新,需 VACUUM |
Bitmap Index Scan + Bitmap Heap Scan | 先建位图再按物理序回表 | 中等选择率(几%~几十%)时优于 Index Scan |
Nested Loop | 外层每行扫一次内层 | 外层行数少 + 内层有索引时好;否则灾难 |
Hash Join | 建哈希表探测 | 大表等值连接,无索引时通常最好 |
Merge Join | 两侧已排序后归并 | 两侧都有序(如都有索引)时好 |
-- MySQL:EXPLAIN ANALYZE(8.0.18+)或传统 EXPLAIN
EXPLAIN ANALYZE SELECT id, title FROM articles
WHERE author_id = 7 AND status = 'published' ORDER BY created_at DESC LIMIT 20;
-- MySQL 传统 EXPLAIN 的 type 列(从好到差)
-- system > const > eq_ref > ref > range > index > ALL
-- key 列:实际使用的索引;rows:估算扫描行数;Extra 列要看:
-- Using index → 覆盖索引,好
-- Using filesort → 需要额外排序,考虑让索引顺序匹配 ORDER BY
-- Using temporary → 用了临时表,常见于 GROUP BY 没走索引
-- Using where → 在存储引擎层之后过滤(可能已扫描大量行)
5.2 回表与覆盖索引
回表:二级索引(secondary index)叶子节点只存索引列 + 主键值,要取其他列必须再按主键去聚簇索引/堆表查一次。
二级索引 idx(author_id, status):
(7,'published') → 主键 一千零一
(7,'draft') → 主键 一千零二
↓ 需要 title 时,回表
聚簇索引/堆表:主键 1001 → 整行(含 title、body...)
覆盖索引(Covering Index):查询所需列全部包含在索引里,无需回表。
-- 慢:需要回表取 title 和 created_at
CREATE INDEX idx_articles_author ON articles (author_id);
SELECT id, title, created_at FROM articles WHERE author_id = 7 ORDER BY created_at DESC;
-- 快:把 SELECT 与 ORDER BY 用到的列都放进索引 → Index Only Scan
CREATE INDEX idx_articles_covering ON articles (author_id, status, created_at DESC) INCLUDE (title);
-- 注意:INCLUDE 是 PostgreSQL 11+ 与 SQL Server 的语法;
-- MySQL 8 不支持 INCLUDE,需把列直接放进索引键,例如 (author_id, status, created_at, title)
覆盖索引的收益与代价
收益:消除回表 I/O,
Index Only Scan通常比Index Scan快数倍,尤其在随机 I/O 场景。代价:索引变宽 → 占用更多磁盘、写入更慢(每次
INSERT/UPDATE都要维护)、缓存命中率下降。只给高频且列数固定的查询做覆盖索引,不要给每个查询都做。
5.3 索引选择性计算
选择性(Selectivity) 衡量「这个索引能把数据筛到多细」:
- 值域 ,越接近 1 越好(唯一列 = 1.0);
- 越接近 0 越差:如
gender(0.0000002)、is_deleted(布尔,最多 0.5)。
-- PostgreSQL:用 pg_stats 查看列的选择性(n_distinct 为负表示「比例」,如 -0.5 = 50% 不同值)
SELECT tablename, attname, n_distinct, correlation,
most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'articles' AND attname IN ('author_id','status','created_at')
ORDER BY attname;
-- 直接精确计算(大表上代价高,仅用于分析)
SELECT
COUNT(DISTINCT author_id)::float / COUNT(*) AS sel_author,
COUNT(DISTINCT status)::float / COUNT(*) AS sel_status,
COUNT(DISTINCT created_at)::float/ COUNT(*) AS sel_created_at
FROM articles;
估算索引过滤后的行数:
例:,author_id 有 5000 个不同值(),status 有 2 个():
判断要不要建索引的经验阈值:
| 过滤后剩余比例 | 建议 |
|---|---|
| < 1% | 索引收益很大,必建 |
| 1% ~ 5% | 通常值得,看查询频率 |
| 5% ~ 20% | 视情况;Bitmap Index Scan 可能仍优于全表扫描 |
| > 20% ~ 30% | 全表扫描通常更快,索引反而增加 I/O |
-- 查看索引是否真的被用到、以及被扫描了多少次
SELECT indexrelname AS index_name, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'articles'
ORDER BY idx_scan DESC;
-- 长期 idx_scan = 0 的索引是「白付写入成本」的候选删除对象(但先确认不是给约束/唯一性用的)
-- 查看索引占用空间
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes WHERE relname = 'articles' ORDER BY pg_relation_size(indexrelid) DESC;
-- MySQL:查看索引基数(cardinality 越高选择性越好)
SHOW INDEX FROM articles;
5.4 联合索引与最左前缀
联合索引 (a, b, c) 的结构等价于「先按 a 排序,a 相同再按 b 排序,b 相同再按 c 排序」,因此只能从最左列开始连续匹配。
| 查询条件 | 能否用 (a, b, c) 索引 | 说明 |
|---|---|---|
WHERE a = 1 | ✅ 用 a | |
WHERE a = 1 AND b = 2 | ✅ 用 a, b | |
WHERE a = 1 AND b = 2 AND c = 3 | ✅ 全用 | 最理想 |
WHERE b = 2 | ❌ 完全不用 | 跳过最左列 |
WHERE b = 2 AND c = 3 | ❌ 完全不用 | |
WHERE a = 1 AND c = 3 | ⚠️ 只用 a | c 无法用于定位(但可能用于索引内过滤) |
WHERE a = 1 AND b > 2 AND c = 3 | ⚠️ a、b 用于定位,c 不能 | 范围条件右边的列失效 |
WHERE a = 1 ORDER BY b, c | ✅ 顺带完成排序 | 索引序即所需序,省掉 Sort |
设计联合索引的三条规则:
- 等值条件在前,范围条件在后——范围之后的列无法用于索引定位;
- 选择性高的列尽量靠前(但优先满足规则 1,等值列之间的顺序影响较小);
- 把
ORDER BY的列接在等值条件后面,让索引同时完成过滤与排序(避免Using filesort)。
-- 面向「某作者已发布文章按时间倒序」这一查询设计索引
CREATE INDEX idx_articles_author_status_created
ON articles (author_id, status, created_at DESC);
-- author_id、status 是等值 → 放前面;created_at 是排序键 → 放最后并指定 DESC
-- 验证:Extra / Index Cond 应该显示走了这个索引且没有 filesort
EXPLAIN ANALYZE
SELECT id, title FROM articles
WHERE author_id = 7 AND status = 'published'
ORDER BY created_at DESC LIMIT 20;
5.5 索引失效的典型写法清单
| 写法 | 为什么失效 | 改写 |
|---|---|---|
WHERE YEAR(created_at) = 2026 | 对列做函数运算,无法用索引有序性 | WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' |
WHERE created_at + 1 > '...' | 列参与运算 | 把运算移到右侧 |
WHERE title LIKE '%http%' | 前置通配符无法利用前缀有序性 | 用全文检索(PostgreSQL tsvector+GIN,MySQL FULLTEXT)或搜索引擎 |
WHERE title LIKE 'http%' | ✅ 可以(前缀匹配) | 注意 LIKE 'http%' 在部分排序规则下仍需 text_pattern_ops |
WHERE CAST(id AS TEXT) = '42' | 隐式/显式类型转换 | 参数类型与列类型一致 |
WHERE code = 42(code 是 varchar) | 隐式转换:MySQL 会把列转成数字 | 传字符串 '42' |
WHERE status != 'draft' | 负向条件选择性通常差 | 改为正向枚举,或建部分索引 |
WHERE a = 1 OR b = 2(只分别有单列索引) | 优化器可能放弃索引 | 建 (a,b) 复合索引,或改写成 UNION ALL |
WHERE is_deleted = 0 | 选择性极低(一半行) | 用部分索引:CREATE INDEX ... WHERE is_deleted = false |
ORDER BY col 但 col 无索引且 LIMIT 小 | 需排序全部匹配行 | 给排序键建索引,或接受小数据量 |
联合索引跳列:WHERE b = 2(索引 (a,b)) | 最左前缀 | 调整索引列顺序或另建索引 |
LIKE 参数用绑定变量但字符集不一致 | 排序规则冲突导致索引不可用 | 统一列的 COLLATE |
-- 部分索引:只索引「活跃」行,索引更小、写入更快、命中率更高
CREATE INDEX idx_articles_published_created
ON articles (created_at DESC)
WHERE status = 'published';
-- 表达式索引:把函数结果索引起来,让 WHERE lower(email) = ? 也能走索引
CREATE INDEX idx_users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'alice@example.com'; -- 走表达式索引
-- MySQL 8 函数索引语法(8.0.13+)
-- CREATE INDEX idx_users_email_lower ON users ((lower(email)));
5.6 慢查询日志定位流程
-- PostgreSQL:开启慢查询记录(超过 200ms 的语句)
ALTER SYSTEM SET log_min_duration_statement = 200; -- 单位毫秒,-1 关闭,0 记录全部
SELECT pg_reload_conf();
-- 日志里会出现:duration: 431.882 ms statement: SELECT ...
-- 更好的方式:用 pg_stat_statements 聚合统计(需在 shared_preload_libraries 中加载)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
round(total_exec_time::numeric, 2) AS total_ms,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
round(100 * total_exec_time / sum(total_exec_time) OVER (), 2) AS pct,
rows,
left(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
-- 关注点:total_ms 高 → 优化收益最大;mean_ms 高但 calls 少 → 可能是报表类,可放从库
# MySQL:my.cnf
# [mysqld]
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow.log
# long_query_time = 0.2
# log_queries_not_using_indexes = 1 # 记录未走索引的查询(日志量会变大,慎用)
# MySQL 慢日志分析:mysqldumpslow 自带,pt-query-digest 更强(Percona Toolkit)
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log # 按总耗时排序取前 10
完整的定位流程:
① 监控/告警发现接口变慢(P99 上升)
↓
② 找到对应 SQL:慢查询日志 / APM 链路追踪 / ORM 日志
↓
③ EXPLAIN (ANALYZE, BUFFERS) 看真实执行计划
↓
④ 判断根因:
· rows 估算严重偏差 → ANALYZE 更新统计信息
· Seq Scan + Rows Removed by Filter 巨大 → 缺索引或索引失效
· loops 很大 → N+1
· Sort Method 落盘 → 排序字段无索引 / work_mem 太小
· Buffers read 很高 → 缓存不足或数据量真的很大
↓
⑤ 改索引 或 改 SQL 或 改数据模型(归档、分区、汇总表)
↓
⑥ 回归验证:对比 EXPLAIN 前后 actual time 与 Buffers,再看线上 P99
改索引前先问三个问题
1. 这条查询的调用频率是多少?一年跑一次的分析 SQL 不值得为它加索引;
2. 加这个索引会让写入慢多少?高写入表的索引预算有限;
3. 能不能不改数据库解决?加缓存、改业务逻辑、预计算汇总表往往更划算。
六、N+1 查询问题
6.1 什么是 N+1
「查 1 次列表,再为列表里每一条各查 1 次关联数据」,总共 次查询。ORM 的懒加载(Lazy Loading)是主要来源。
# SQLAlchemy 2.x:典型 N+1 —— 1 + N 次查询
from sqlalchemy import create_engine, select
from sqlalchemy.orm import Session, relationship, Mapped, mapped_column
class Author(Base):
__tablename__ = "authors"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str]
# 默认 lazy="select":访问 .articles 时才发查询 → N+1 的根源
articles: Mapped[list["Article"]] = relationship(back_populates="author")
class Article(Base):
__tablename__ = "articles"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str]
author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
author: Mapped["Author"] = relationship(back_populates="articles")
def list_author_article_counts_bad(session: Session):
authors = session.scalars(select(Author)).all() # ① 1 次查询:取 50 个作者
result = []
for a in authors:
# ② 每次循环触发一条 SELECT ... WHERE author_id = ? → 共 50 次
result.append((a.name, len(a.articles)))
return result
# 总计:1 + 50 = 51 次往返
6.2 如何发现
| 手段 | 做法 | 优点 |
|---|---|---|
| SQL 日志计数 | 打开 ORM/驱动的语句日志,统计单次请求的 SELECT 条数 | 最直接,无需额外工具 |
| 断言式守卫 | 测试环境断言「单请求 SQL 条数 ≤ 阈值」,超了就失败 | 能进 CI,防止回归 |
| 连接池指标 | 请求期间 pool.totalCount 是否随数据量线性增长 | 无需改代码 |
pg_stat_statements | 看 calls 异常高的同构 SQL | 生产可用 |
| APM / 链路追踪 | 每个 ORM 调用是一个 span,父子关系一眼可见 | 直观,需接入 APM |
// 用事件钩子统计「每个请求发了多少条 SQL」——发现 N+1 的最简手段
let queryCount = 0;
pool.on('query', () => { queryCount++; });
app.use((req, res, next) => {
queryCount = 0;
res.on('finish', () => {
if (queryCount > 10) {
console.warn(`[N+1 嫌疑] ${req.method} ${req.originalUrl} 执行了 ${queryCount} 条 SQL`);
}
});
next();
});
# SQLAlchemy:给 Engine 挂事件监听,打印每条 SQL 并计数
from sqlalchemy import event
from sqlalchemy.engine import Engine
@event.listens_for(Engine, "before_cursor_execute")
def count_queries(conn, cursor, statement, params, context, executemany):
context._query_count = getattr(context, "_query_count", 0) + 1
print(f"[SQL #{context._query_count}] {statement[:120]}")
# 测试里断言:一个接口不应该超过 5 条 SQL——超额即视为性能回归
def test_list_authors_sql_count(client, db_session):
with count_sql(db_session) as counter:
client.get("/api/v1/authors")
assert counter.count <= 5, f"N+1 回归:执行了 {counter.count} 条 SQL"
6.3 三种修复方式
方式一:预加载(Eager Loading) —— 推荐,改动最小,语义清晰。
from sqlalchemy.orm import selectinload, joinedload
def list_author_article_counts_good(session: Session):
# selectinload:先查作者,再用一条 WHERE author_id IN (...) 查全部评论 → 固定 2 条 SQL
authors = session.scalars(
select(Author).options(selectinload(Author.articles))
).all()
return [(a.name, len(a.articles)) for a in authors]
def list_articles_with_author(session: Session):
# joinedload:一对一/多对一时用 JOIN 一次取回 → 1 条 SQL
return session.scalars(
select(Article).options(joinedload(Article.author)).limit(20)
).all()
# 选择依据:
# joinedload → 多对一、一对一(不会放大行数)
# selectinload→ 一对多、多对多(避免 JOIN 造成的行数爆炸)
# ⚠️ 对一对多使用 joinedload 会让主表行重复,ORM 还要去重,数据量大时很危险
// EF Core:Include 预加载;AsSplitQuery 避免笛卡尔积爆炸
var authors = await db.Authors
.Include(a => a.Articles) // 生成 JOIN 或拆分查询
.AsSplitQuery() // 多个集合导航时拆成多条 SQL,避免行数放大
.ToListAsync();
// 只读场景关闭变更跟踪,显著降低内存与 CPU
var list = await db.Articles
.AsNoTracking()
.Select(a => new ArticleDto(a.Id, a.Title, a.Author.Name)) // 投影,天然避免加载整实体
.ToListAsync();
方式二:用 JOIN + 聚合直接在数据库侧算完 —— 性能最好,返回最少数据。
-- 用一条 SQL 拿到「作者 + 文章数」,根本不给 N+1 机会
SELECT a.id, a.name, COUNT(ar.id) AS article_count
FROM authors a
LEFT JOIN articles ar ON ar.author_id = a.id
GROUP BY a.id, a.name
ORDER BY article_count DESC
LIMIT 50;
# 用 ORM 的查询构建器表达同一条 SQL(SQLAlchemy 2.x)
from sqlalchemy import func, select
stmt = (
select(Author.id, Author.name, func.count(Article.id).label("article_count"))
.outerjoin(Article, Article.author_id == Author.id)
.group_by(Author.id, Author.name)
.order_by(func.count(Article.id).desc())
.limit(50)
)
rows = session.execute(stmt).all()
方式三:批量查询 + 应用层组装(DataLoader 模式) —— 适合 GraphQL 或跨服务场景。
// DataLoader:把同一事件循环内的 N 个「按 id 查询」合并成 1 次 IN 查询
import DataLoader from 'dataloader';
const authorLoader = new DataLoader(async (authorIds) => {
const { rows } = await pool.query(
'SELECT id, name FROM authors WHERE id = ANY($1::int[])', // 1 次查询取全部
[authorIds]
);
const byId = new Map(rows.map(r => [r.id, r]));
// 必须按传入顺序返回,否则 DataLoader 会把结果错配给不同的 key
return authorIds.map(id => byId.get(id) ?? null);
});
// 循环里调用 loader.load() 不会立刻发查询,
// 同一 tick 内的所有 load 会被合并成一次 IN 查询
const posts = await Promise.all(articleIds.map(async id => {
const article = await getArticle(id);
return { ...article, author: await authorLoader.load(article.author_id) };
}));
| 修复方式 | SQL 条数 | 适用 | 代价 |
|---|---|---|---|
预加载(selectinload/Include) | 2(或 1) | 需要在代码里使用对象图 | 可能取回多余列/行 |
JOIN + 聚合 | 1 | 只需要聚合结果或少量字段 | 手写 SQL,复杂查询要维护 |
| 批量查询 / DataLoader | 1~2 | GraphQL、动态字段请求、跨服务 | 需要引入批处理层与请求级缓存 |
预加载不是无脑加
joinedload用在一对多上会放大行数:20 篇文章 × 平均 50 条评论 = 1000 行结果集,其中文章数据重复 50 次。此时应改用selectinload(两条 SQL),或AsSplitQuery。另外,预加载的层级越深,SQL 越复杂(
Include嵌套三层就会生成巨大的 JOIN)。超过两层关系时,考虑直接写 SQL 投影成 DTO。
七、ORM 的取舍
7.1 ORM 与手写 SQL 对比
| 维度 | ORM | 手写 SQL |
|---|---|---|
| 开发效率 | 高:CRUD 几乎零成本,实体变更后编译期发现 | 低:每条都要写、要维护、字段改名要全局搜 |
| 性能可控性 | 中低:生成的 SQL 不直观,容易 N+1 与过度取列 | 高:完全掌控执行计划与返回列 |
| 复杂查询表达力 | 低:多表聚合、窗口函数、递归 CTE 需退回 raw/literal SQL | 高:数据库吃什么就写什么 |
| 学习成本 | 中:要同时学 ORM API 与它生成的 SQL | 低(前提是已会 SQL),但要求真的会 SQL |
| 重构安全性 | 高:改字段名 IDE 全局重命名,编译报错 | 低:字符串里改漏了运行时才炸 |
| 类型安全 | 高(EF Core / Prisma / Drizzle 都能推导类型) | 取决于工具(jOOQ、sqlc 可做到) |
| 调试难度 | 中:要先知道它生成了什么 SQL | 低:SQL 就是代码 |
| 迁移管理 | 通常内置(EF Migrations / Prisma Migrate / Alembic) | 需自己维护 SQL 脚本与版本表 |
| 数据库可移植性 | 较高(换库改连接串,但要小心方言差异) | 低:SQL 方言差异要自己处理 |
7.2 何时该放弃 ORM 写原生 SQL
| 场景 | 建议 |
|---|---|
| 简单 CRUD(占 80%) | 用 ORM,享受类型与效率 |
| 报表、多表聚合、窗口函数 | 写原生 SQL,结果映射到 DTO(不要映射到实体) |
| 批量操作(万行级) | 用驱动层的 COPY / 批量 INSERT,绕过 ORM 的逐行处理 |
| 热点路径(每秒数千次) | 手写 SQL + 覆盖索引,逐条 EXPLAIN 验证 |
复杂的 UPSERT / ON CONFLICT | 多数 ORM 支持不完整,直接写 SQL 更可靠 |
混合策略(推荐)
默认 ORM,遇到这三种情况立即切 SQL:
1.
EXPLAIN显示计划不对,而 ORM 不给你控制权;2. 需要在数据库里算聚合/窗口函数;
3. 单次操作超过 1000 行。
关键是切换要低成本:让 ORM 的 Session/连接与原生 SQL 共用同一个事务,不要各开各的连接。
7.3 Active Record 与 Data Mapper
| 对比项 | Active Record | Data Mapper |
|---|---|---|
| 核心思想 | 对象自己负责持久化:user.save() | 对象是纯数据,映射器负责持久化:session.save(user) |
| 实体是否知道数据库 | 知道(继承基类、带 save/delete 方法) | 不知道(POCO/POJO,可脱离数据库测试) |
| 代码量 | 少,上手快 | 多,需要 Repository/Mapper 层 |
| 单元测试 | 需 mock 数据库或跑内存库 | 实体可直接 new 出来测 |
| 领域模型复杂度 | 适合简单 CRUD | 适合复杂领域模型(领域驱动设计) |
| 代表实现 | Ruby on Rails AR、Laravel Eloquent、Django ORM、Sequelize | Hibernate、EF Core、SQLAlchemy(Session)、Doctrine |
# Active Record 风格(Django ORM):实体自带持久化方法
class Article(models.Model):
title = models.CharField(max_length=100)
status = models.CharField(max_length=16, default="draft")
Article.objects.filter(status="published").count()
article = Article(title="新文章") # 实体自己知道怎么存
article.save() # ← 实体与数据库耦合
// Data Mapper 风格(EF Core):实体是纯类,DbContext 负责映射
public class Article // 纯 POCO,无 DbContext 依赖,可直接 new 出来测
{
public int Id { get; set; }
public string Title { get; set; } = "";
public string Status { get; set; } = "draft";
}
public class BlogContext : DbContext
{
public DbSet<Article> Articles => Set<Article>();
}
// 持久化由 context 负责
var article = new Article { Title = "新文章" }; // 完全不知道数据库存在
context.Articles.Add(article);
await context.SaveChangesAsync();
两者不是对立的
SQLAlchemy 同时支持两种风格:
Session(Data Mapper,推荐)与declarative_base+ 显式session.add;Django 是典型 Active Record。选型依据是领域复杂度:表单型 CRUD 用 Active Record 更快;有复杂不变量与业务规则时 Data Mapper 更易测试与重构。
7.4 主流 ORM 速览
| ORM | 语言 / 生态 | 模式 | 定位与特点 | 官方文档 |
|---|---|---|---|---|
| EF Core | C# / .NET | Data Mapper | LINQ 强类型查询、变更跟踪、内置 Migrations、与 ASP.NET Core 集成最好 | <https://learn.microsoft.com/zh-cn/ef/core/> |
| Prisma | TypeScript / Node.js | Data Mapper | schema 文件定义模型 + 代码生成 Client,类型体验极佳;查询引擎是独立二进制 | <https://www.prisma.io/docs> |
| Drizzle | TypeScript / Node.js | 查询构建器 | 「像 SQL 的 TypeScript」,无运行时魔法,体积小,贴近 SQL | <https://orm.drizzle.team/docs/overview> |
| SQLAlchemy | Python | Data Mapper | 功能最全之一,核心层是查询构建器,ORM 层可选;生态成熟 | <https://docs.sqlalchemy.org/en/20/> |
| Hibernate | Java | Data Mapper | JPA 的事实标准实现,缓存与脏检查强大,但也最容易写出 N+1 | <https://hibernate.org/orm/documentation/> |
| HikariCP | Java | 连接池 | 不是 ORM,但常与 Hibernate 搭配;性能与稳定性标杆 | <https://github.com/brettwooldridge/HikariCP> |
版本敏感提示
以上 ORM 的 API、默认行为(如是否默认懒加载、
Include语义、生成的 SQL 形态)在大版本间会变。例如 EF Core 3.0 起
Include相关行为、Drizzle 的 API 在 0.x 期间多次调整。以官方最新文档为准,不要照抄旧教程的写法。
八、迁移(Migration)管理
8.1 为什么不能手改生产库结构
手工 ALTER TABLE 的问题 | 后果 |
|---|---|
| 环境不一致 | 开发库有 phone 列、生产库没有,代码在预发通过、生产 500 |
| 无法回溯 | 新同事拿到代码,不知道数据库该长什么样;重建环境靠猜 |
| 无法回滚 | 改错了只能凭记忆手动改回来,容易漏 |
| 无审计 | 谁在什么时候改了哪个表,查不到 |
| 破坏性变更无审查 | 直接 DROP COLUMN 丢数据,没有 Code Review 拦 |
迁移文件是数据库结构(Schema)的唯一真相来源:代码仓库里的迁移链能重建出任意版本的数据库。
8.2 正反向操作
# Alembic(SQLAlchemy 官方迁移工具):每个迁移都有 upgrade 与 downgrade
# 生成:alembic revision --autogenerate -m "add phone to users"
# 应用:alembic upgrade head
# 回退:alembic downgrade -1
def upgrade() -> None:
op.add_column("users", sa.Column("phone", sa.String(32), nullable=True))
op.create_index("idx_users_phone", "users", ["phone"])
def downgrade() -> None:
# 反向操作必须真的能执行,否则出事时无法快速回退
op.drop_index("idx_users_phone", table_name="users")
op.drop_column("users", "phone")
// EF Core:生成 SQL 脚本先审阅,再执行(生产环境的推荐流程)
// dotnet ef migrations add AddUserPhone
// dotnet ef migrations script --idempotent -o migrate.sql
public partial class AddUserPhone : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.AddColumn<string>(
name: "phone", table: "users", type: "varchar(32)", nullable: true);
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropColumn(name: "phone", table: "users");
}
}
downgrade很容易写成假的
op.add_column的反向是drop_column,看起来对称,但数据已经丢了:回退后那一列的数据永久消失。所以破坏性迁移的正确做法不是「写好 downgrade」,而是先做向后兼容的迁移(见 8.4),让任何时刻回滚代码都不需要回滚结构。
8.3 团队协作中的迁移冲突
场景:A 和 B 从同一个版本各自建了一个迁移,合并后迁移链出现两个「头」(heads)。
main: base ── 001_add_status ── 002_A_add_phone
└ 002_B_add_email ← 两个 head,冲突
处理原则:
| 问题 | 处理方式 |
|---|---|
| 多个 head | 生成一个「合并迁移」(Alembic alembic merge -m "merge" head1 head2;EF Core 通常靠时间戳顺序自动串成一条链) |
| 两个迁移改同一个表 | 不要靠自动合并,人工确认顺序与依赖,必要时手工写一个合并脚本 |
| 迁移已上生产后要改 | 绝不修改已发布的迁移文件,只能追加新迁移修正 |
| 迁移文件与模型不一致 | CI 里跑「检查是否有未生成的迁移」(alembic check / dotnet ef migrations has-pending-model-changes) |
| 迁移顺序与部署顺序 | 迁移必须向后兼容上一个版本的代码,否则滚动发布期间新旧代码同时读写会炸 |
# Alembic 常用命令
alembic current # 当前数据库处于哪个版本
alembic history --verbose # 迁移历史与分支情况
alembic heads # 有几个头(应该只有 1 个)
alembic merge -m "merge heads" <rev1> <rev2> # 合并分支
alembic upgrade head # 升到最新
alembic downgrade -1 # 回退一步
alembic check # 检测模型与迁移是否漂移(新版本支持)
# EF Core
dotnet ef migrations list
dotnet ef migrations script --idempotent -o migrate.sql # 生成可重复执行的脚本
dotnet ef database update
CI 里加一道闸
在 PR 流水线里执行:
alembic upgrade head && alembic check(或 EF 的has-pending-model-changes)。这样「改了模型忘了写迁移」「迁移跑不通」在合并前就会暴露,而不是等发版时才发现。
8.4 生产环境迁移的安全流程
核心原则:任何一次迁移都必须能被「旧代码 + 新结构」和「新代码 + 旧结构」同时容忍,这样才能实现滚动发布与随时回滚。
以「把 users.name 拆成 first_name + last_name」为例,拆成 5 次发布:
| 阶段 | 迁移动作 | 代码动作 | 此时的状态 |
|---|---|---|---|
| ① 加列 | ADD COLUMN first_name, last_name(可空,无默认值或默认 NULL) | 不读新列,只写新旧两处 | 旧代码正常;新列开始有数据 |
| ② 回填 | 分批 UPDATE ... SET first_name = split_part(name,' ',1) WHERE first_name IS NULL LIMIT 1000(循环直到 0 行) | 仍双写 | 历史数据补齐 |
| ③ 切换读 | 无结构变更 | 改读新列(带回退到旧列的逻辑) | 读路径已切换,出问题可秒回滚 |
| ④ 停止写旧列 | 把旧列改为可空(若原本 NOT NULL) | 只写新列 | 旧列停止更新 |
| ⑤ 删列 | DROP COLUMN name(等一个发布周期后) | 无 | 清理完成 |
-- 阶段 ①:加可空列 + 索引(加列本身在 PostgreSQL 11+ 是元数据操作,不重写表)
ALTER TABLE users ADD COLUMN IF NOT EXISTS first_name TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_name TEXT;
-- PostgreSQL:给已有表加索引不要锁死写入
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_first_name ON users (first_name);
-- ⚠️ CREATE INDEX CONCURRENTLY 不能在事务块里执行(Alembic 需 autocommit_block)
-- 阶段 ②:分批回填,每批都提交,避免长事务与锁表
DO $$
DECLARE affected INT;
BEGIN
LOOP
UPDATE users
SET first_name = split_part(name, ' ', 1),
last_name = NULLIF(substring(name from position(' ' in name) + 1), '')
WHERE first_name IS NULL AND id IN (
SELECT id FROM users WHERE first_name IS NULL ORDER BY id LIMIT 1000
);
GET DIAGNOSTICS affected = ROW_COUNT;
EXIT WHEN affected = 0;
COMMIT; -- 每批提交,缩短锁持有时间
PERFORM pg_sleep(0.05); -- 主动让路,降低对线上查询的冲击
END LOOP;
END $$;
-- 阶段 ③ 的读逻辑(应用代码里做回退,便于快速切回)
-- SELECT COALESCE(first_name, split_part(name,' ',1)) AS first_name FROM users;
-- 阶段 ⑤:确认无引用后再删列(先确认没有视图/索引/触发器依赖)
ALTER TABLE users DROP COLUMN IF EXISTS name;
加 NOT NULL 列的正确顺序(PostgreSQL 11 之前加带默认值的列会重写全表,务必注意):
-- ❌ 老版本上危险:ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';
-- MySQL 8 支持 INSTANT 算法,PostgreSQL 11+ 对常量默认值也是元数据操作,但跨版本不可假设
-- ✅ 稳妥顺序:加可空列 → 分批回填 → 加约束(用 NOT VALID 避免长时间锁表)→ 校验
ALTER TABLE users ADD COLUMN status TEXT; -- ①
UPDATE users SET status = 'active' WHERE status IS NULL; -- ② 分批做
ALTER TABLE users ADD CONSTRAINT users_status_not_null
CHECK (status IS NOT NULL) NOT VALID; -- ③ 不扫表,秒级完成
ALTER TABLE users VALIDATE CONSTRAINT users_status_not_null; -- ④ 只取 SHARE UPDATE EXCLUSIVE 锁
迁移禁令清单
1. 禁止在大表上直接
ALTER COLUMN TYPE(多数情况会重写整表并持有排他锁);2. 禁止在业务高峰期执行无
CONCURRENTLY的CREATE INDEX;3. 禁止在一次迁移里同时改结构和改数据(出问题无法区分原因);
4. 禁止在生产直接
DROP列/表——先改名(ALTER TABLE ... RENAME COLUMN x TO x_deprecated_20261011),观察一周再删;5. 必须在上线前用生产数据量的副本演练一遍迁移并计时。
九、数据一致性实践
9.1 唯一约束兜底,而不是只靠应用层查重
-- ✅ 让数据库做裁判:并发下唯一约束是唯一可靠的保证
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);
-- 大小写不敏感的唯一性:用函数索引(PostgreSQL)
CREATE UNIQUE INDEX uq_users_email_lower ON users (lower(email));
-- 「软删除 + 唯一约束」的经典冲突:删掉后想重新注册同一邮箱
-- 方案 A:部分唯一索引,只约束未删除的行(PostgreSQL / SQLite)
CREATE UNIQUE INDEX uq_users_email_active ON users (lower(email)) WHERE deleted_at IS NULL;
-- 方案 B:唯一键包含删除时间,删除时把它写成删除时刻
-- UNIQUE (email, deleted_at) —— NULL 的重复行为各数据库不同,需实测确认
# 应用层:捕获唯一约束冲突并翻译成业务错误,而不是先 count 再 insert
from sqlalchemy.exc import IntegrityError
def register(email: str, password: str):
try:
user = User(email=email, password_hash=hash_password(password))
session.add(user)
session.commit()
return user
except IntegrityError as e:
session.rollback()
# 关键:靠数据库报的约束名来区分是哪个唯一键冲突,而不是猜
if "uq_users_email" in str(e.orig):
raise ApiError(409, "EMAIL_ALREADY_REGISTERED", "该邮箱已被注册")
raise
9.2 软删除的取舍
软删除(Soft Delete):不真的 DELETE,而是标记 deleted_at。
| 维度 | 软删除 | 硬删除 |
|---|---|---|
| 数据恢复 | ✅ 简单(改回 NULL) | ❌ 需依赖备份,耗时且可能丢窗口期数据 |
| 审计/合规 | ✅ 保留历史 | ❌ 无痕迹 |
| 外键完整性 | ✅ 历史订单仍能关联到用户 | ❌ 需 ON DELETE SET NULL 或级联删除(危险) |
| 查询复杂度 | ❌ 每个查询都要带 deleted_at IS NULL,漏一个就出 bug | ✅ 无需额外条件 |
| 唯一约束 | ❌ 与唯一索引冲突(见上) | ✅ 无冲突 |
| 性能 | ❌ 表持续膨胀,索引变大,统计信息失真 | ✅ 表保持精简 |
| 隐私合规 | ❌ GDPR 等要求「真删除」时需额外硬删流程 | ✅ 天然满足 |
-- 软删除的三种实现层次(按可靠性排序)
-- ① 数据库层:视图 + 规则(最难绕过,但改造成本高)
CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;
-- ② ORM 层:全局查询条件(推荐,Django 用自定义 Manager,SQLAlchemy 用 with_loader_criteria)
-- SQLAlchemy 2.x:对某个实体全局加条件,避免漏写
from sqlalchemy import event, select
from sqlalchemy.orm import with_loader_criteria
@event.listens_for(Session, "do_orm_execute")
def _add_soft_delete_filter(execute_state):
if execute_state.is_select and not execute_state.execution_options.get("include_deleted", False):
execute_state.statement = execute_state.statement.options(
with_loader_criteria(User, lambda cls: cls.deleted_at.is_(None), include_aliases=True)
)
# 需要查全部(含已删除)时显式打标
session.execute(select(User).execution_options(include_deleted=True))
-- ③ 应用层:每个查询手写条件(最容易漏,不推荐作为唯一手段)
SELECT * FROM users WHERE deleted_at IS NULL;
软删除的三条铁律
1. 必须有部分唯一索引,否则删掉的邮箱永远不能再注册;
2. 必须有清理策略:定期把
deleted_at < now() - interval '90 days'的记录真删或归档到历史表,否则表会无限膨胀;3. 所有查询默认带条件(用 ORM 全局钩子,而不是靠自觉),并提供显式的「包含已删除」开关。
9.3 审计字段
CREATE TABLE articles (
id TEXT PRIMARY KEY,
title TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(), -- 创建时间,一旦写入不再改
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), -- 最后修改时间
created_by TEXT,
updated_by TEXT,
deleted_at TIMESTAMPTZ -- 软删除标记
);
-- updated_at 自动维护:触发器方式(数据库保证,任何写入途径都生效)
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_articles_updated_at
BEFORE UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- MySQL 8 更简单,建表时声明即可
-- updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-- 注意:触发器会带来写入开销;若应用层能保证统一走 ORM 的 onupdate,也可以不用触发器。
-- 取舍点:数据库触发器更可靠(任何途径都生效),应用层更可控(无隐式行为)。
9.4 时区统一存 UTC
-- PostgreSQL:永远用 timestamptz,不要用 timestamp(无时区)
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
happened_at TIMESTAMPTZ NOT NULL, -- ✅ 带时区,内部按 UTC 存储
-- happened_at TIMESTAMP NOT NULL, -- ❌ 无时区,跨时区/夏令时必然出错
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 会话时区只影响「显示」,不影响存储
SET TIME ZONE 'Asia/Shanghai';
SELECT happened_at FROM events; -- 显示为 +08:00
SET TIME ZONE 'UTC';
SELECT happened_at FROM events; -- 显示为 +00:00,同一时刻
-- 应用写入时传 UTC,读取后由前端按用户时区展示
INSERT INTO events (happened_at) VALUES ('2026-10-11T08:30:00Z');
-- MySQL:DATETIME 不带时区,TIMESTAMP 带时区但范围只到 2038 年
-- 推荐:用 DATETIME,并约定「应用层始终写 UTC」,连接串显式声明时区
-- jdbc:mysql://host/db?connectionTimeZone=UTC&forceConnectionTimeZoneToSession=true
-- 或启动参数:--default-time-zone='+00:00'
SELECT @@global.time_zone, @@session.time_zone, NOW(), UTC_TIMESTAMP();
时区的四个真实事故
1. 服务器时区是
Asia/Shanghai,代码却当成 UTC 处理 → 时间整体偏移 8 小时;2. 夏令时切换日(有 DST 的地区)某小时重复出现,用本地时间做主键/去重会撞车;
3.
TIMESTAMP的 2038 问题(32 位秒数溢出)→ 长期项目用DATETIME或timestamptz;4. 数据库与应用时区不一致,
now()与应用new Date()相差数小时,只有跨天边界才暴露。
十、分页与批处理
10.1 LIMIT/OFFSET 深分页问题
-- ❌ 深分页:需要读取并丢弃前 400000 行
SELECT id, title, created_at FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 400000;
扫描行数为 ( 为偏移量, 为页大小),相对有效行数的读放大:
返回 20 行却要扫描 2 万倍的数据量。若有 (created_at) 索引,是 40 万次索引项遍历;若没有,还要叠加一次 排序。
-- ✅ 游标(keyset)分页:利用索引直接定位到断点,读放大恒为 1
-- 第一页
SELECT id, title, created_at FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 上一页最后一行是 (created_at='2026-10-01T12:00:00Z', id='a-900')
-- 下一页:行值比较(row value comparison),比手写 OR 条件更准确
SELECT id, title, created_at FROM articles
WHERE (created_at, id) < ('2026-10-01T12:00:00Z', 'a-900')
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- ⚠️ 为什么必须用 (created_at, id) 复合游标而不是只用 created_at?
-- 若多行 created_at 相同(批量导入很常见),只用 created_at 会:
-- · 用 `<` 跳过同一时间戳的剩余行(漏数据)
-- · 用 `<=` 反复返回同一行(死循环)
-- 加上唯一的 id 作为决胜键(tie-breaker),游标才有全序。
-- 对应的索引必须与排序键完全一致
CREATE INDEX idx_articles_created_id ON articles (created_at DESC, id DESC);
| 对比项 | LIMIT/OFFSET | 游标分页 |
|---|---|---|
| 时间复杂度 | ||
| 深分页表现 | 线性恶化 | 恒定 |
| 跳页 / 总数 | ✅ | ❌ |
| 数据变动 | 重复/漏项 | 稳定 |
| 实现复杂度 | 低 | 中(游标编码、复合键) |
| 缓存友好 | 差(每页独立键) | 好 |
折中方案
若产品必须支持跳页(后台表格)又要控制性能:
1. 限制可达页数(如最多前 100 页),超出提示「请使用筛选条件缩小范围」;
2. 总数用估算:PostgreSQL 可查
pg_class.reltuples,或对EXPLAIN的rows估算值取整;3. 用「延迟关联」减少回表:
```sql
-- 先在索引上翻页拿到主键,再回表取数据(仍随 OFFSET 线性增长,但 I/O 小得多)
SELECT a.id, a.title, a.created_at
FROM articles a
JOIN (
SELECT id FROM articles ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 400000
) t ON t.id = a.id;
```
10.2 批量插入
# ❌ 逐行插入:10000 行 = 10000 次往返 + 10000 次事务提交
for row in rows:
session.add(Article(**row))
session.commit()
# ✅ 方式一:ORM 批量(SQLAlchemy 2.x)
session.execute(insert(Article), rows) # executemany,一次往返一批
session.commit()
# ✅ 方式二:驱动层批量(psycopg 的 execute_values / copy)
from psycopg2.extras import execute_values
with conn.cursor() as cur:
execute_values(
cur,
"INSERT INTO articles (id, title, created_at) VALUES %s",
[(r["id"], r["title"], r["created_at"]) for r in rows],
page_size=1000, # 每 1000 行一条 INSERT,避免单条 SQL 过大
)
conn.commit()
# ✅ 方式三:COPY(PostgreSQL 最快的批量导入,比 INSERT 快数倍)
# 用 copy_expert 从内存/文件流导入,适合百万行级初始化
with conn.cursor() as cur:
cur.copy_expert(
"COPY articles (id, title, created_at) FROM STDIN WITH (FORMAT csv)",
io.StringIO(csv_text),
)
conn.commit()
-- 单条 INSERT 多行的上限:受 max_allowed_packet(MySQL)/ 参数个数上限(PostgreSQL 65535)约束
-- 实践值:每批 500~2000 行,或每批总参数数 < 10000
INSERT INTO articles (id, title, created_at) VALUES
('a-1','标题 1','2026-10-11T00:00:00Z'),
('a-2','标题 2','2026-10-11T00:00:01Z');
-- ON CONFLICT 让批量导入可重复执行(幂等)
-- ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title;
10.3 事务大小控制
| 批量规模 | 事务策略 | 原因 |
|---|---|---|
| < 1000 行 | 一个大事务 | 简单,失败整体回滚,语义清晰 |
| 1000 ~ 10 万行 | 按 500~2000 行分批提交 | 避免长事务(锁、WAL、膨胀、从库延迟) |
| > 10 万行 | 分批 + 断点续传 + 幂等写入 | 任何中断都能从上次位置继续 |
| 全量重建 | 新表构建 + RENAME 原子切换 | 避免长时间锁原表 |
def batch_insert(rows, batch_size=1000):
"""分批插入:每批一个独立事务,失败时能知道进度并安全重跑(依赖唯一键幂等)"""
total = len(rows)
for start in range(0, total, batch_size):
batch = rows[start:start + batch_size]
try:
with session_scope() as s: # 每个批次独立提交
s.execute(insert(Article).on_conflict_do_nothing(index_elements=["id"]), batch)
except Exception as e:
# 记录失败批次的位置,便于修复后从断点续跑,而不是从头再来
raise RuntimeError(f"批次 {start}~{start+len(batch)-1} 插入失败") from e
print(f"进度 {min(start + batch_size, total)}/{total}")
长事务在批量写入中的具体危害
1. WAL 与锁:一个 100 万行的
UPDATE会产生巨量 WAL,持有大量行锁,期间任何触及这些行的事务全部排队;2. 从库延迟:主库提交瞬间,从库要重放整个事务,延迟可能达到数十秒;
3. 回滚极慢:中途失败时,PostgreSQL 回滚同样要遍历所有改动;
4. 膨胀:MVCC 保留所有旧版本,直到事务结束才能回收。
结论:批量操作的默认策略是「小事务 + 幂等 + 断点续传」,而不是「一个大事务保证原子性」。业务上通常也不需要百万行级别的原子性。
十一、备份与恢复要点
| 方式 | 命令 / 工具 | 特点 | 适用 |
|---|---|---|---|
| 逻辑备份 | pg_dump / mysqldump | 输出 SQL 或自定义格式,可跨版本、可单表恢复 | 中小库、版本升级、迁移 |
| 物理备份 | pg_basebackup / XtraBackup | 复制数据文件,快、可做 PITR | 大库、需要快速恢复 |
| 连续归档(PITR) | WAL 归档 + restore_command | 能恢复到任意时间点(如误删前 1 秒) | 生产必备 |
| 快照 | 云盘快照 / LVM | 秒级、便宜 | 变更前的一次性保护 |
| 逻辑复制 | 发布/订阅 | 跨版本、可选择性同步表 | 灰度迁移、读写分离 |
# 逻辑备份与恢复(PostgreSQL)
pg_dump -h localhost -U app -Fc -d blog -f blog_20261011.dump # -Fc 自定义压缩格式
pg_restore -h localhost -U app -d blog_restore -j 4 blog_20261011.dump # 4 个并行任务
# 只备份某张表
pg_dump -h localhost -U app -t articles -Fc -d blog -f articles.dump
# MySQL
mysqldump --single-transaction --routines --triggers blog > blog.sql # InnoDB 一致性快照
mysql blog_restore < blog.sql
# 验证备份可用性(关键且最常被跳过的一步)
createdb blog_verify && pg_restore -d blog_verify blog_20261011.dump
psql -d blog_verify -c "SELECT count(*) FROM articles;"
备份的三条铁律
1. 没验证过恢复的备份等于没有备份——定期(每月)在独立环境完整恢复一次并记录耗时;
2. 备份要与数据库分开存储:同一块盘、同一个云账号下的备份,在事故中会一起消失;
3. 明确 RPO 与 RTO:RPO(能容忍丢多少数据,决定备份/WAL 频率)与 RTO(多久必须恢复,决定用逻辑还是物理备份)。没有这两个指标就无法设计备份策略。
部署环境下的备份调度、监控与告警落地细节见 后端-08-部署与运维基础。
十二、常见坑
十二个高频错误
1. 连接泄漏:
connect()后没有finally释放 → 池耗尽,重启才恢复。2. 连接池大小盲目设成几百 → 数据库更慢,因为连接数不等于并发能力。
3. N+1 查询:循环里访问懒加载属性,50 条数据发 51 次查询。
4. 长事务:把外部 HTTP 调用写进事务,或忘了
commit的连接。5. 「先查再插」防重复 → 并发下必然重复;应该用唯一约束。
6.
NOT IN遇到NULL→ 结果为空,且极难排查。7. 对索引列用函数:
WHERE date(created_at) = '2026-10-11'导致索引失效。8. 联合索引跳列:建了
(a,b)却按b查询,索引完全用不上。9.
OFFSET深分页:读放大数千倍,还可能触发数据库超时。10. 金额用浮点:对账永远差一点,必须用整数分或定点数。
11. 迁移在业务高峰跑、且没有
CONCURRENTLY→ 锁表导致全站不可用。12. **
SELECT ***:取回不需要的大字段(TEXT/BLOB),浪费网络与内存,还破坏覆盖索引。
上线前自检
- [ ] 每个列表接口都有
limit上限,且深分页有方案(游标或页数限制)?- [ ] 所有唯一性约束都在数据库里声明了吗?
- [ ] 事务里有网络调用/文件 IO 吗?
- [ ]
statement_timeout与idle_in_transaction_session_timeout设了吗?- [ ] 时间字段统一 UTC,类型是
timestamptz(或DATETIME存 UTC)吗?- [ ] 高频查询都有
EXPLAIN验证,且没有Rows Removed by Filter爆炸?- [ ] 备份恢复演练过吗,RPO/RTO 有明确数字吗?
十三、练习题
- 基础题:给定表
users(id, name)与orders(id, user_id, amount_cents, created_at),写出三条 SQL:(a) 查询所有没有下过订单的用户;(b) 查询订单数 ≥ 2 的用户及其总金额;(c) 查询每个用户最近一单的时间(分别用标量子查询与窗口函数各写一遍)。 - 基础题:为查询
SELECT id, title FROM articles WHERE author_id = ? AND status = 'published' ORDER BY created_at DESC LIMIT 20设计索引。写出CREATE INDEX语句,说明列顺序的依据,并指出哪些列可以放进INCLUDE以变成覆盖索引。 - 进阶题:以下代码在生产环境会出什么问题?给出至少两种修复方案并比较。
```python
posts = session.scalars(select(Post).limit(100)).all()
for p in posts:
print(p.author.name, len(p.comments)) # 懒加载
```
要求:说明总 SQL 条数,给出 selectinload 版本与「JOIN + 聚合」版本,并分析各自在「文章 100 篇、每篇平均 30 条评论」场景下的结果集大小。
- 进阶题:某接口需要「扣减库存」:
UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0。
(a) 这条 SQL 是悲观锁还是乐观锁?为什么它天然避免了超卖?
(b) 若业务需要「先读库存,判断是否充足,再扣减」,请分别写出悲观锁与乐观锁实现,并说明各自在高并发下的表现。
(c) 如果扣减服务被调用两次(网络重试),如何保证不重复扣减?
- 思考题:团队要把
users表从一个 2000 万行的大表按created_at拆成「热表 + 归档表」。请设计一个不停机的迁移方案:包含迁移顺序、如何回填、如何双写与切换读、如何回滚,以及每一步的风险点。
十四、FAQ
Q1:连接池大小到底设多少?有没有一个「标准答案」?
A:没有标准答案,但有推导方法。起点用 (数据库侧总连接预算,不是单实例池大小),然后:
- 数清所有连这个库的应用实例数 (含定时任务、后台 worker、运维工具);
- 保证 ;
- 用压测找拐点:逐步提高并发,观察 TPS 与 P99,TPS 不再上升而 P99 上升的那个点就是上限;
- 如果算出来每个实例只能分到 2~3 个连接,说明该上 PgBouncer 了,而不是砍池大小。
Q2:ORM 生成的 SQL 我完全不了解,怎么建立信心?
A:三步建立可观测性:
- 打开 SQL 日志(开发环境始终打开):SQLAlchemy 用
echo=True或logging,EF Core 用LogTo(Console.WriteLine),Prisma 用log: ['query']; - 给接口加 SQL 条数断言:测试里断言「这个接口不超过 N 条 SQL」,把 N+1 变成测试失败而不是线上事故;
- 关键接口自己跑
EXPLAIN:ORM 写完后把生成的 SQL 贴到EXPLAIN (ANALYZE, BUFFERS)里看一遍,确认走的是预期索引。
Q3:什么时候该加缓存而不是优化 SQL?
A:判断依据是「同一条查询是否被反复以相同参数执行」以及「数据能容忍多旧」:
| 情况 | 选择 |
|---|---|
| 相同参数的查询 QPS 很高,且数据不常变 | 加缓存(Redis)性价比最高 |
| 查询本身慢但每个参数只查一次(如用户详情页长尾) | 优化 SQL / 加索引 |
| 复杂聚合报表,数据可容忍 1 小时延迟 | 预计算汇总表 + 定时刷新,优于缓存 |
| 数据要求强一致(余额) | 不做缓存,或缓存只用于展示层 |
| 写多读少 | 缓存命中率低,先优化写入路径 |
注意:加缓存的复杂度不低(一致性、穿透、雪崩、热 key)。如果一条 SQL 加个索引就能从 500ms 降到 2ms,就别引入缓存。
Q4:迁移在生产环境失败了怎么办?
A:取决于失败发生在哪一步:
- 迁移脚本本身报错、事务已回滚(PostgreSQL 的 DDL 在事务里):最幸运的情况。数据库结构没变,修脚本重跑即可。
- 迁移部分成功(如 MySQL 的 DDL 隐式提交,或分批回填做到一半):不要慌着回滚。先确认当前结构,因为回填通常是幂等的(
WHERE first_name IS NULL),直接重跑剩余部分比回滚更安全。 - 迁移成功但代码有问题:这正是「先加列后删列、双写过渡」的价值——此时只回滚应用版本即可,数据库结构对新旧代码都兼容。
- 必须回滚结构:优先执行准备好的
downgrade,但要清楚它会丢数据。若涉及删列,从备份恢复到独立实例、把数据补回来是唯一可靠路径。
事前准备:迁移前做一次快照/备份;在生产数据量副本上演练并计时;把迁移与代码发布分成两次部署,而不是一次全上。
相关笔记
- 数据库原理——关系模型、SQL 基础、范式、索引结构与事务隔离级别的理论根基
- 后端-01-HTTP与RESTfulAPI设计——分页设计、
409/412状态码、Idempotency-Key 与本文的接口层配合 - 后端-03-ASPNET-Core——EF Core 在 ASP.NET Core 中的注册、作用域与迁移实践
- 后端-08-部署与运维基础——数据库备份调度、监控告警与生产迁移流程
- 后端-09-后端安全与性能——SQL 注入防护、缓存策略与慢查询的运维视角
参考来源
- PostgreSQL 官方文档:Using EXPLAIN、Window Functions、WITH Queries (CTE)、SELECT、Transaction Isolation、Conditional Expressions (CASE)、CREATE INDEX、Backup and Restore
- SQLite 官方文档:SELECT(含窗口函数与 CTE 说明)、Documentation Index
- Microsoft Learn(中文):EF Core 高效查询、EF Core 迁移
- node-postgres 文档:Pooling
- Redis 官方文档(缓存与分布式锁实现参考):<https://redis.io/docs/latest/>
- 说明:
dev.mysql.com从中国大陆的自动请求可能返回 403(链接本身有效,但无法自动核验),MySQL 相关细节请以本地SHOW VARIABLES/EXPLAIN实测与官方最新文档为准;PostgreSQL 文档中的版本号与语法(如INCLUDE、MATERIALIZED、NOT VALID)在不同大版本间存在差异,以官方最新文档为准。