w wyl123ly 的博客
MD

后端-05-数据库操作与ORM

90 KB 2026.10.11 原文件

后端-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% 的时间花在握手上。连接池的作用:

  1. 复用已建立的连接,把握手成本摊薄到接近 0;
  2. 限制并发:池大小就是「最多几个请求同时打数据库」的闸门,保护数据库不被打穿;
  3. 健康检查:定期验证连接可用(SELECT 1),自动剔除坏连接;
  4. 统一超时:获取连接超时、语句超时、空闲回收时长都在一处配置。

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

排查步骤:

  1. 打开池的监控指标(HikariCP 的 hikaricp_connections_active、pg.Pool 的 pool.totalCount / pool.idleCount);
  2. 打日志记录「借出/归还」以及借出时的调用栈(仅调试期开启,开销大);
  3. 用上面的 pg_stat_activity 查询看数据库侧的 state;
  4. 检查是否有 .connect() 却没有对应的 release() / Dispose() / close();
  5. 检查事务是否在所有分支上都被 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
    ①            ②          ③            ④          ⑤          ⑥           ⑦           ⑧

由此推出三条实用结论:

  1. WHERE 在 GROUP BY 之前,所以 WHERE 里不能用聚合函数(那时还没分组);
  2. HAVING 在 GROUP BY 之后,用于过滤分组结果,可以包含聚合函数;
  3. 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 长事务的危害

危害机制后果
阻塞 DDLALTER 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⚠️ 只用 ac 无法用于定位(但可能用于索引内过滤)
WHERE a = 1 AND b > 2 AND c = 3⚠️ a、b 用于定位,c 不能范围条件右边的列失效
WHERE a = 1 ORDER BY b, c✅ 顺带完成排序索引序即所需序,省掉 Sort

设计联合索引的三条规则:

  1. 等值条件在前,范围条件在后——范围之后的列无法用于索引定位;
  2. 选择性高的列尽量靠前(但优先满足规则 1,等值列之间的顺序影响较小);
  3. 把 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,复杂查询要维护
批量查询 / DataLoader1~2GraphQL、动态字段请求、跨服务需要引入批处理层与请求级缓存

预加载不是无脑加

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 RecordData Mapper
核心思想对象自己负责持久化:user.save()对象是纯数据,映射器负责持久化:session.save(user)
实体是否知道数据库知道(继承基类、带 save/delete 方法)不知道(POCO/POJO,可脱离数据库测试)
代码量少,上手快多,需要 Repository/Mapper 层
单元测试需 mock 数据库或跑内存库实体可直接 new 出来测
领域模型复杂度适合简单 CRUD适合复杂领域模型(领域驱动设计)
代表实现Ruby on Rails AR、Laravel Eloquent、Django ORM、SequelizeHibernate、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 CoreC# / .NETData MapperLINQ 强类型查询、变更跟踪、内置 Migrations、与 ASP.NET Core 集成最好<https://learn.microsoft.com/zh-cn/ef/core/>
PrismaTypeScript / Node.jsData Mapperschema 文件定义模型 + 代码生成 Client,类型体验极佳;查询引擎是独立二进制<https://www.prisma.io/docs>
DrizzleTypeScript / Node.js查询构建器「像 SQL 的 TypeScript」,无运行时魔法,体积小,贴近 SQL<https://orm.drizzle.team/docs/overview>
SQLAlchemyPythonData Mapper功能最全之一,核心层是查询构建器,ORM 层可选;生态成熟<https://docs.sqlalchemy.org/en/20/>
HibernateJavaData MapperJPA 的事实标准实现,缓存与脏检查强大,但也最容易写出 N+1<https://hibernate.org/orm/documentation/>
HikariCPJava连接池不是 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 有明确数字吗?


十三、练习题

  1. 基础题:给定表 users(id, name) 与 orders(id, user_id, amount_cents, created_at),写出三条 SQL:(a) 查询所有没有下过订单的用户;(b) 查询订单数 ≥ 2 的用户及其总金额;(c) 查询每个用户最近一单的时间(分别用标量子查询与窗口函数各写一遍)。
  2. 基础题:为查询 SELECT id, title FROM articles WHERE author_id = ? AND status = 'published' ORDER BY created_at DESC LIMIT 20 设计索引。写出 CREATE INDEX 语句,说明列顺序的依据,并指出哪些列可以放进 INCLUDE 以变成覆盖索引。
  3. 进阶题:以下代码在生产环境会出什么问题?给出至少两种修复方案并比较。

```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 条评论」场景下的结果集大小。

  1. 进阶题:某接口需要「扣减库存」:UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0。

(a) 这条 SQL 是悲观锁还是乐观锁?为什么它天然避免了超卖?

(b) 若业务需要「先读库存,判断是否充足,再扣减」,请分别写出悲观锁与乐观锁实现,并说明各自在高并发下的表现。

(c) 如果扣减服务被调用两次(网络重试),如何保证不重复扣减?

  1. 思考题:团队要把 users 表从一个 2000 万行的大表按 created_at 拆成「热表 + 归档表」。请设计一个不停机的迁移方案:包含迁移顺序、如何回填、如何双写与切换读、如何回滚,以及每一步的风险点。

十四、FAQ

Q1:连接池大小到底设多少?有没有一个「标准答案」?

A:没有标准答案,但有推导方法。起点用 (数据库侧总连接预算,不是单实例池大小),然后:

  1. 数清所有连这个库的应用实例数 (含定时任务、后台 worker、运维工具);
  2. 保证 ;
  3. 用压测找拐点:逐步提高并发,观察 TPS 与 P99,TPS 不再上升而 P99 上升的那个点就是上限;
  4. 如果算出来每个实例只能分到 2~3 个连接,说明该上 PgBouncer 了,而不是砍池大小。

Q2:ORM 生成的 SQL 我完全不了解,怎么建立信心?

A:三步建立可观测性:

  1. 打开 SQL 日志(开发环境始终打开):SQLAlchemy 用 echo=True 或 logging,EF Core 用 LogTo(Console.WriteLine),Prisma 用 log: ['query'];
  2. 给接口加 SQL 条数断言:测试里断言「这个接口不超过 N 条 SQL」,把 N+1 变成测试失败而不是线上事故;
  3. 关键接口自己跑 EXPLAIN:ORM 写完后把生成的 SQL 贴到 EXPLAIN (ANALYZE, BUFFERS) 里看一遍,确认走的是预期索引。

Q3:什么时候该加缓存而不是优化 SQL?

A:判断依据是「同一条查询是否被反复以相同参数执行」以及「数据能容忍多旧」:

情况选择
相同参数的查询 QPS 很高,且数据不常变加缓存(Redis)性价比最高
查询本身慢但每个参数只查一次(如用户详情页长尾)优化 SQL / 加索引
复杂聚合报表,数据可容忍 1 小时延迟预计算汇总表 + 定时刷新,优于缓存
数据要求强一致(余额)不做缓存,或缓存只用于展示层
写多读少缓存命中率低,先优化写入路径

注意:加缓存的复杂度不低(一致性、穿透、雪崩、热 key)。如果一条 SQL 加个索引就能从 500ms 降到 2ms,就别引入缓存。

Q4:迁移在生产环境失败了怎么办?

A:取决于失败发生在哪一步:

  1. 迁移脚本本身报错、事务已回滚(PostgreSQL 的 DDL 在事务里):最幸运的情况。数据库结构没变,修脚本重跑即可。
  2. 迁移部分成功(如 MySQL 的 DDL 隐式提交,或分批回填做到一半):不要慌着回滚。先确认当前结构,因为回填通常是幂等的(WHERE first_name IS NULL),直接重跑剩余部分比回滚更安全。
  3. 迁移成功但代码有问题:这正是「先加列后删列、双写过渡」的价值——此时只回滚应用版本即可,数据库结构对新旧代码都兼容。
  4. 必须回滚结构:优先执行准备好的 downgrade,但要清楚它会丢数据。若涉及删列,从备份恢复到独立实例、把数据补回来是唯一可靠路径。

事前准备:迁移前做一次快照/备份;在生产数据量副本上演练并计时;把迁移与代码发布分成两次部署,而不是一次全上。


相关笔记

  • 数据库原理——关系模型、SQL 基础、范式、索引结构与事务隔离级别的理论根基
  • 后端-01-HTTP与RESTfulAPI设计——分页设计、409/412 状态码、Idempotency-Key 与本文的接口层配合
  • 后端-03-ASPNET-Core——EF Core 在 ASP.NET Core 中的注册、作用域与迁移实践
  • 后端-08-部署与运维基础——数据库备份调度、监控告警与生产迁移流程
  • 后端-09-后端安全与性能——SQL 注入防护、缓存策略与慢查询的运维视角

参考来源