罗罗编程学习记录
本页内容 展开查看

04.7.21.1 SQLAlchemy 与 Alembic

所属:全栈开发面试题库 / 04 后端语言与运行时 / 04.7 Python / 04.7.21 Python 后端常见搭配技术

覆盖范围

  • create_engine 与连接池参数:pool_size、max_overflow、pool_pre_ping、pool_recycle、pool_timeout
  • 连接池耗尽、连接泄漏的线上表现与排查链路
  • Session 生命周期、identity map、scoped_session 与线程/协程隔离
  • sessionmaker、expire_on_commit、autoflush 等会话参数取舍
  • SQLAlchemy 1.x 与 2.x 写法差异:select()、Session.execute、Mapped 注解
  • ORM 与 Core 的取舍边界
  • relationship 加载策略:selectinload、joinedload、subqueryload、lazy 与 N+1
  • 事务、begin_nested savepoint、隔离级别、死锁重试、乐观锁版本列
  • Alembic 迁移流程、autogenerate 局限、分支合并与零停机迁移
  • create_async_engine 与 asyncpg、批量写入与 insertmanyvalues、慢查询与执行计划

04.7.21.1-001 create_engine 最常用的参数有哪些?

一句话结论

create_engine 负责创建 Engine(连接工厂 + 连接池),核心参数包括连接串 url、连接池配置(pool_size/max_overflow/pool_recycle/pool_pre_ping/pool_timeout)、执行参数(echo/poolclass)以及驱动相关的 connect_args。Engine 是进程级单例,通常一个应用共享一个 Engine。

概念解释

  • Engine 内部持有一个 DBAPI 连接工厂和一个连接池,engine.connect() 从池里取连接,Session 内部通过 Engine 获取连接。
  • 连接串格式按驱动区分:mysql+pymysql://、postgresql+psycopg2://、sqlite:///;异步用 postgresql+asyncpg://。
  • connect_args 透传给底层 DBAPI 的 connect(),例如 MySQL 的 charset、init_command。

原理流程

  1. 调用 create_engine(url, **kwargs) 解析 URL 与驱动名。
  2. 根据 poolclass(默认 QueuePool)创建连接池,pool_size 决定常驻连接数。
  3. 首次取连接时才真正建立 TCP 与数据库会话,池不预热。
  4. 应用退出时由进程结束回收连接,一般不显式 engine.dispose()。

代码或图示

from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg2://app:***@db:5432/app",
    pool_size=10,
    max_overflow=5,
    pool_pre_ping=True,
    pool_recycle=1800,
    pool_timeout=30,
    echo=False,
)

常见误区

  • 把 Engine 当请求级对象反复创建:会重复建池、耗尽数据库连接,Engine 应在应用启动时创建一次并复用。
  • 连接串里的密码写进代码或日志:应从环境变量/配置中心注入;echo=True 会打印 SQL 与参数,生产必须关闭。
  • 误以为建池时就连上数据库:默认懒加载,第一次取连接才握手。

追问

  • Engine 是线程安全的吗?答:是,Engine 与连接池可被多线程共享;Session 才不是线程安全的。
  • 一个应用能建多个 Engine 吗?答:可以,对应不同数据库或不同连接池策略,但要注意总连接数叠加。
  • poolclass 什么时候换成 NullPool?答:短连接、测试环境或部署平台自己管理连接时,避免池持有失效连接。

04.7.21.1-002 连接池 pool_size/max_overflow 容量怎么换算?

一句话结论

QueuePool 默认 pool_size=5、max_overflow=10,即常驻 5 个连接,峰值可临时再借 10 个,总容量上限为 pool_size + max_overflow = 15。超出后新请求按 pool_timeout 等待,超时抛 TimeoutError(连接池获取超时)。

概念解释

  • pool_size:池中保持打开的常驻连接数,空闲也保留。
  • max_overflow:超出 pool_size 后允许临时创建的连接数,归还后若空闲会被关闭。
  • 总连接占用 = 常驻 + 溢出;数据库侧 max_connections 必须大于 (Engine 数 × 总容量) 再留余量。

原理流程

  1. 取连接时优先复用池中空闲连接。
  2. 空闲不足且当前连接数 < pool_size + max_overflow,新建连接。
  3. 达到上限后进入等待队列,等待 pool_timeout 秒。
  4. 等待超时抛出 sqlalchemy.exc.TimeoutError;连接归还后先入池,溢出连接在归还时关闭。

代码或图示

# 常驻 8,峰值再借 4,总上限 12
engine = create_engine(url, pool_size=8, max_overflow=4, pool_timeout=30)

常见误区

  • 把 max_overflow 理解成常驻扩容:它是临时溢出,归还即销毁,不提高常驻基数。
  • 只按单进程算连接:gunicorn/uvicorn 每个 worker 进程各持一个 Engine 与池,总连接 = worker 数 ×(pool_size + max_overflow),必须对照数据库 max_connections。
  • 设成 0 表示不限制:max_overflow=0 表示不允许溢出,不是无限。

追问

  • 连接池满了会立刻报错吗?答:不会,先按 pool_timeout 排队等待,超时才抛 TimeoutError。
  • 常驻连接一直不释放有问题吗?答:池会保留常驻连接,但要配合 pool_recycle 防止被中间件静默断开。
  • 多进程部署怎么估算数据库连接上限?答:worker 数 × 每进程池上限 × 实例数 ≤ 数据库 max_connections × 0.8。

04.7.21.1-003 pool_pre_ping 解决什么问题?原理是什么?

一句话结论

pool_pre_ping=True 在每次取出连接前先做一次轻量探活(默认执行 SELECT 1 之类的 ping),连接已失效则丢弃并重连,解决「连接被数据库或中间件静默断开后,应用拿到死连接才报错」的问题。默认值为 False。

概念解释

  • 长空闲连接可能被数据库 wait_timeout、防火墙、NAT 表项回收,TCP 半开状态下应用写数据才发现对端已关闭。
  • pre-ping 是 SQLAlchemy 2.x 推荐的生产开关,代价是每次借连接多一次往返;高并发下可结合 pool_recycle 缩短空闲周期。

原理流程

  1. 从池中取出连接后,先向数据库发送 ping 探测。
  2. 探测成功:连接可用,交给 Session 使用。
  3. 探测失败:该连接被判定失效,关闭并从池移除,重新创建一个可用连接再交付。
  4. 新连接本身的握手失败会向上抛出 DBAPI 连接异常。

代码或图示

engine = create_engine(url, pool_pre_ping=True, pool_recycle=1800)

常见误区

  • 以为开了 pre-ping 就不用 pool_recycle:两者互补,recycle 按时间主动回收,pre-ping 按使用前被动探活。
  • 认为 ping 零成本:每次借连接多一次 RTT,对延迟敏感场景要压测;可适当增大常驻池减少新建。
  • pre-ping 能防所有连接故障:只能发现取连接时已失效的连接,使用过程中断线仍需业务层重试。

追问

  • pre-ping 用什么语句探活?答:底层方言默认执行一条轻量 ping SQL(如 SELECT 1),具体语句由 DBAPI 方言决定(待确认:各方言 ping 语句差异)。
  • 不开 pre-ping 会报什么错?答:常见 MySQL server has gone away、Lost connection to MySQL server during query 或 PostgreSQL connection reset。
  • 探活失败的连接会进池吗?答:不会,判定失效后直接关闭并移除,再补建新连接。

04.7.21.1-004 pool_recycle 为什么要小于数据库空闲超时?

一句话结论

pool_recycle 设置连接最长存活时间(秒),超过后归还或下次取出时被回收重建,必须小于数据库侧 wait_timeout/idle_in_transaction_session_timeout 以及中间件的空闲断连时间,避免应用持有已被对端关闭的死连接。MySQL 常见配置为 1800 秒(30 分钟),需对照数据库实际超时调整。

概念解释

  • MySQL wait_timeout(默认 28800 秒)控制非交互连接空闲多久后断开;PostgreSQL 由 idle_in_transaction_session_timeout 等参数控制。
  • 连接在池里空闲期间数据库可能已回收,应用不主动 recycle 就会拿到死连接。
  • pool_recycle=-1 表示不回收(默认行为的一部分,待确认:各版本默认值差异),生产建议显式设置。

原理流程

  1. 连接创建时记录时间戳。
  2. 连接归还入池后,若存活时间超过 pool_recycle,下次取出时判定过期。
  3. 过期连接关闭重建;未过期则直接复用。
  4. 配合 pool_pre_ping 形成「时间回收 + 使用前探活」双保险。

代码或图示

# 数据库 wait_timeout=28800,这里设 1800 远小于它
engine = create_engine(url, pool_recycle=1800, pool_pre_ping=True)

常见误区

  • recycle 设得比数据库超时还大:等于没设,死连接照样流出。
  • 只看数据库超时,忽略云负载均衡/防火墙的空闲会话回收时间:中间件超时往往更短,要按最小值设置。
  • 认为 recycle 会主动断开正在使用的连接:只在归还或取出时判定,使用中的连接不受影响。

追问

  • 怎么查数据库的空闲超时?答:MySQL 查 SHOW VARIABLES LIKE 'wait_timeout';PostgreSQL 查 idle_in_transaction_session_timeout 与 tcp_keepalives_idle。
  • 连接被 recycle 重建会丢事务吗?答:只回收空闲连接;正在事务中的连接不会被强制回收,异常事务由业务回滚处理。
  • recycle 设太小有什么代价?答:连接重建更频繁,TCP 握手与认证开销上升,池常驻连接命中率下降。

04.7.21.1-005 pool_timeout 超时后报什么错?怎么处理?

一句话结论

pool_timeout(默认 30 秒)是请求等待空闲连接的最长时间;超时后抛出 sqlalchemy.exc.TimeoutError(包装底层等待超时),上层表现为 500 或任务失败。处理方向是排查连接占用时长、泄漏与池容量,而不是简单调大超时掩盖问题。

概念解释

  • 等待队列只在池满时进入:连接被长事务、慢查询、未关闭 Session 占住,新请求排队。
  • 超时错误本身不区分「池太小」和「连接被泄漏」,需要结合池统计与慢查询日志判断。

原理流程

  1. 请求取连接,池无空闲且已达 pool_size + max_overflow。
  2. 进入等待队列,等待已借出连接归还。
  3. 等待超过 pool_timeout 秒,抛出 TimeoutError。
  4. 上层捕获后返回错误或重试;若根因是慢 SQL/泄漏,重试只会加重积压。

代码或图示

from sqlalchemy import create_engine
from sqlalchemy.exc import TimeoutError

engine = create_engine(url, pool_size=8, max_overflow=4, pool_timeout=10)

try:
    with engine.connect() as conn:
        ...
except TimeoutError:
    # 记录池占用指标,触发告警,不盲目重试
    raise

常见误区

  • 超时就把 pool_timeout 调到 60 秒:排队更久只是把错误延后,请求堆积反而拖垮整体吞吐。
  • 把连接池超时和数据库 connect_timeout 混为一谈:前者是池内排队等待,后者是 TCP 握手超时,走 connect_args。
  • 忽略连接泄漏:Session 未 close()/未用上下文管理器,连接永不归还,池必然耗尽。

追问

  • 怎么看当前池里有多少连接在用?答:engine.pool.status() 输出池状态;也可挂指标暴露 checkedout 连接数。
  • 等待超时和获取连接被拒是一回事吗?答:不是,超时是排队未等到;拒绝是达到硬上限直接不排队(不同池实现行为不同)。
  • 临时缓解手段有哪些?答:缩短慢查询、修复泄漏、评估上调 pool_size/max_overflow,但必须先确认数据库连接余量。

04.7.21.1-006 线上连接池耗尽怎么排查?

一句话结论

连接池耗尽的典型表现是请求排队、TimeoutError、接口超时,排查链路为:先看池状态与指标(checkedout 连接数、等待队列)→ 定位占用连接的慢 SQL/长事务 → 检查 Session 是否泄漏未归还 → 核对池容量与数据库 max_connections → 区分容量不足、泄漏、慢查询、外部断连四类根因。

概念解释

  • 耗尽根因常见四类:容量规划不足(并发高、池小)、连接泄漏(Session 未关闭)、慢查询/长事务占住连接、下游故障导致连接长时间不释放。
  • 数据库侧也会先出现 Too many connections,与应用池耗尽互为因果。

原理流程

  1. 采集池指标:pool.size()、pool.checkedin()、pool.checkedout()、等待队列长度。
  2. 查数据库当前会话:MySQL SHOW PROCESSLIST/information_schema.processlist,PostgreSQL pg_stat_activity,看状态、事务时长、当前 SQL。
  3. 核对每个占用连接对应的应用请求/任务,定位慢查询或未提交事务。
  4. 检查代码路径:是否有 Session 脱离上下文管理器、异常分支未 close。
  5. 评估容量:worker 数 × 池上限是否逼近数据库连接上限。

代码或图示

-- MySQL:查看长事务与慢查询
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
ORDER BY time DESC;

常见误区

  • 只看应用报错就调大池:根因是慢 SQL 时,调大池只会让数据库被更多连接压垮。
  • 忽略多进程叠加:每个 worker 独立池,单进程池健康不代表整体连接余量充足。
  • 排查只看实时快照:长事务可能正在进行,需要结合会话时长与历史指标趋势判断。

追问

  • 怎么判断是泄漏而不是慢查询?答:泄漏连接长时间处于空闲/未归还状态,进程重启后恢复;慢查询连接有活跃 SQL 且随慢日志增长。
  • 数据库 Too many connections 怎么应急?答:先杀掉异常长会话释放连接,再限流应用入口,避免雪崩;长期要调大上限并修正池配置。
  • 池指标应该暴露到哪里?答:Prometheus 采集 checkedout/等待数,配合请求耗时与下游指标做关联告警。

04.7.21.1-007 连接泄漏怎么定位?

一句话结论

连接泄漏指连接被借出后未归还(Session 未关闭、引用长期持有),表现为 checkedout 连接单调上涨、重启后恢复、池最终耗尽。定位手段:池统计快照对比、Session 关闭路径审查、异常分支检查,必要时在 checkout/checkin 事件挂钩子记录栈。

概念解释

  • 泄漏通常来自:手动 Session() 后未 close()、把 Session 存进全局/缓存对象、异常跳过关闭、后台线程持有请求级 Session。
  • 正确姿势是 with Session(engine) as session: 上下文管理器,退出时自动 close() 归还连接。

原理流程

  1. 周期性记录 pool.checkedout() 数值,观察是否只涨不跌。
  2. 审查代码:搜索直接 Session(、sessionmaker() 调用,确认是否都在 with 块内。
  3. 检查异常分支:try/except 中是否有路径跳过 close()。
  4. 必要时监听 events.checkout/events.checkin,在 checkout 时记录调用栈,泄漏时对照未 checkin 的栈。

代码或图示

from sqlalchemy import event

@event.listens_for(engine, "checkout")
def _checkout(dbapi_conn, record, conn):
    import traceback
    record["stack"] = "".join(traceback.format_stack(limit=8))

常见误区

  • 用 session.close() 代替 session.rollback() 就认为安全:close 会归还连接,但未提交事务仍会被回滚;泄漏风险在于根本没走到 close。
  • 把 Session 放到模块级全局变量复用:跨请求复用 Session 会串状态,且异常时难以保证关闭。
  • 认为进程重启能根治泄漏:重启只是清空现场,代码路径不修,高负载后必然复发。

追问

  • Session close() 后连接去哪了?答:连接归还连接池,Session 本身可再用,但状态被清空;泄漏是指连接从未归还。
  • 后台任务里怎么管理 Session?答:任务函数内新建 Session 并用 with 包裹,任务结束即关闭,不要复用请求上下文里的 Session。
  • 怎么验证修复有效?答:压测后观察 checkedout 连接数随请求量回落,而不是单调增长。

04.7.21.1-008 几种连接池实现怎么选?

一句话结论

SQLAlchemy 提供 QueuePool(默认,多线程 Web 服务)、NullPool(不池化,每次新建/关闭)、StaticPool(固定单连接复用)、SingletonThreadPool(每线程一个连接)。常规后端服务用 QueuePool;测试或平台已管连接时用 NullPool;SQLite 等特殊场景才考虑 StaticPool。

概念解释

  • QueuePool:线程安全队列,按 pool_size/max_overflow 管理,是生产默认。
  • NullPool:每次取连接新建,归还即关闭,无复用。
  • StaticPool:所有请求共享同一个底层连接,适合 SQLite 内存库。
  • SingletonThreadPool:每个线程持有自己的连接,跨线程不复用。

原理流程

  1. create_engine(..., poolclass=...) 指定池类。
  2. 池类实现 connect/get/put 与计数逻辑。
  3. QueuePool 在多线程间安全排队;NullPool 直接调用 DBAPI 新建。
  4. 池行为随池类不同,pool_size 等参数只对支持的池类生效。

代码或图示

from sqlalchemy.pool import NullPool, StaticPool

engine = create_engine(url, poolclass=NullPool)
sqlite_engine = create_engine("sqlite:///:memory:", poolclass=StaticPool)

常见误区

  • 在多线程 Web 服务里用 SingletonThreadPool:线程间连接不共享,连接数随线程数膨胀。
  • 给 NullPool 配 pool_size:参数无效,NullPool 不维护常驻连接。
  • 把 StaticPool 用于多连接数据库:它强制单连接,并发写会串行甚至锁等待。

追问

  • 什么时候用 NullPool?答:短任务脚本、测试环境,或部署平台(如某些 Serverless/连接代理)已做连接管理时。
  • 池类可以在运行时换吗?答:Engine 创建时确定,换池类需重建 Engine。
  • SQLite 默认池行为有何不同?答:SQLite 文件/内存库的连接与事务隔离较特殊,内存库常配 StaticPool 保证同一连接。

04.7.21.1-009 Session 的生命周期分几个阶段?

一句话结论

Session 生命周期为:创建(sessionmaker 或 Session(engine))→ 绑定/查询时取连接 → 跟踪对象状态(脏检查、identity map)→ 提交或回滚 → close() 归还连接并释放对象引用。Web 场景下 Session 应绑定请求,请求结束即关闭,不跨请求复用。

概念解释

  • Session 是「工作单元(unit of work)」:聚合一组对象变更,commit 时统一 flush 为 SQL。
  • Session 内部维护 identity map、事务状态、脏集合;close 后对象默认变为 detached(可配置过期)。

原理流程

  1. 创建 Session,尚未取数据库连接。
  2. 首次查询/写入时从 Engine 借连接,开启事务(2.x 显式或自动开始)。
  3. 增删改查登记到 Session,变更在 flush 时落库。
  4. commit() 提交事务并按配置过期对象;rollback() 回滚并清理脏状态。
  5. close() 归还连接,释放 identity map 中的引用。

代码或图示

from sqlalchemy.orm import Session, sessionmaker

SessionLocal = sessionmaker(bind=engine, expire_on_commit=False)

with SessionLocal() as session:
    user = session.get(User, 1)
    session.commit()
# 退出 with 块自动 close,连接归还

常见误区

  • Session 跨请求/跨任务复用:identity map 残留旧对象,脏检查误判,事务状态串味。
  • 忘记 close:连接长期占用,逐步演变为泄漏。
  • 把 Session 当全局单例:Session 不是线程安全的,多线程共享会出现状态错乱。

追问

  • Session 是线程安全的吗?答:不是,一个 Session 同时只应由一个线程使用;scoped_session 做线程隔离但每线程仍是独立 Session。
  • close 后还能用 session 里的对象吗?答:对象变为 detached,访问未加载属性可能触发 detached 异常(视过期配置而定)。
  • 一个 Session 能跨多个事务吗?答:可以,commit/rollback 后事务结束,新操作会开启下一个事务,直到 close。

04.7.21.1-010 identity map 是什么?解决什么问题?

一句话结论

identity map 是 Session 内部按主键缓存实例的映射表:同一主键在 Session 内只对应一个 Python 对象,重复查询返回同一实例。它解决重复对象构造、对象身份不一致(同一行两个实例状态打架)的问题,并支撑脏检查与工作单元提交。

概念解释

  • 键由表身份 + 主键值组成;未提交的新对象不在 identity map 中(标记为 pending)。
  • 脏检查遍历 Session 内对象,与数据库快照比对,生成 update SQL。

原理流程

  1. 查询返回行后,先按主键查 identity map。
  2. 命中:直接返回已有实例;未命中:构造新实例放入 map。
  3. 对象属性修改由 Session 监听,加入脏集合。
  4. flush 时对脏集合逐对象生成 update,commit 后按配置过期或保留。

代码或图示

u1 = session.get(User, 1)
u2 = session.execute(select(User).where(User.id == 1)).scalar_one()
assert u1 is u2  # 同一 identity map 实例

常见误区

  • 以为 identity map 是二级缓存:它只在单个 Session 内有效,Session close 即清空,不跨请求。
  • 依赖同一实例判断业务相等:跨 Session 的同主键对象不是同一个 Python 实例,is 不成立,要用主键比较。
  • 长生命周期 Session 导致 map 堆积:对象越攒越多,内存上涨且脏检查成本增加。

追问

  • identity map 能减少 SQL 吗?答:不能阻止查询执行,只复用已有实例;避免 N+1 要靠加载策略,不是 map。
  • 未提交的新对象在哪里?答:处于 pending 状态,放入 Session 新对象集合,flush 后才进 map。
  • 改了实例属性会立刻写库吗?答:不会,先记为脏,flush/commit 时才生成 SQL。

04.7.21.1-011 scoped_session 的线程隔离原理是什么?

一句话结论

scoped_session 用一个线程局部存储(thread-local,基于 threading.local)把 Session 实例按线程隔离:同一线程多次调用返回同一 Session,不同线程各拿各的;remove() 关闭当前线程绑定的 Session 并清空局部引用。它是老式 Flask/SQLAlchemy 集成的常见写法。

概念解释

  • 内部维护一个 registry(默认线程局部注册表),工厂创建 Session 后绑定到当前线程。
  • Web 框架在请求结束时调用 scoped_session.remove(),确保 Session 归还、不残留到下一个请求。

原理流程

  1. session_factory = sessionmaker(engine) 后包一层 ScopedSession(session_factory)。
  2. 首次在某线程访问代理对象,工厂创建 Session 存入线程局部槽。
  3. 同线程后续访问取出同一 Session;新线程走工厂新建。
  4. 请求结束 remove():关闭当前线程 Session,删除注册表条目。

代码或图示

from sqlalchemy.orm import scoped_session, sessionmaker

db_session = scoped_session(sessionmaker(bind=engine, expire_on_commit=False))

# 请求结束钩子
db_session.remove()

常见误区

  • 在异步协程里直接用 scoped_session:threading.local 不隔离协程,多个协程可能共享同一 Session,导致串状态。
  • 忘记 remove():线程复用(线程池)时上一请求的 Session 残留到下一请求。
  • 把 scoped_session 当线程安全保证:它只隔离 Session 归属,Session 本身仍不能被多线程并发操作。

追问

  • 异步应用该怎么替代 scoped_session?答:用 async_sessionmaker,按请求/协程显式创建 AsyncSession,配合上下文变量管理。
  • remove() 和 close() 区别?答:close 关闭 Session 归还连接;remove 再清空线程局部绑定,避免下次复用旧 Session。
  • scoped_session 代理透明转发哪些方法?答:query/execute/commit/rollback/close 等都转发给底层 Session。

04.7.21.1-012 为什么异步协程里不能直接用 scoped_session?

一句话结论

scoped_session 默认基于 threading.local,隔离单位是线程;而异步协程跑在同一线程的事件循环上,多个协程共享线程局部槽,会互相拿到同一个 Session,造成事务与对象状态串扰。异步栈必须用 async_sessionmaker 显式管理 AsyncSession 生命周期。

概念解释

  • 线程局部存储按线程区分,协程切换不换线程,因此无法按协程隔离。
  • 即便换成 contextvars 注册表,Session 的同步阻塞 API 也不适合事件循环;异步必须走 AsyncSession 与异步驱动。

原理流程

  1. 协程 A 创建/获取 Session,写入线程局部槽。
  2. 事件循环切到协程 B,B 访问 scoped_session 拿到同一个 Session。
  3. B 的 commit/rollback 作用于 A 未完成的事务,identity map 互相污染。
  4. 正确做法:每协程内 async with AsyncSession(engine) 或经 async_sessionmaker 工厂创建。

代码或图示

from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine

async_engine = create_async_engine("postgresql+asyncpg://app:***@db:5432/app")
AsyncSessionLocal = async_sessionmaker(async_engine, expire_on_commit=False)

async with AsyncSessionLocal() as session:
    ...

常见误区

  • 给 scoped_session 传 contextvars 注册表就以为支持异步:隔离问题缓解了,但同步 Session 的阻塞调用仍会卡住事件循环。
  • 异步路由里混用同步 Session:阻塞驱动调用会把事件循环拖住,并发能力退化。
  • 一个异步请求内共享 Session 跨任务:子任务应独立 Session 或明确传递,避免生命周期不可控。

追问

  • AsyncSession 和 Session API 差别大吗?答:核心方法名对应加 await:session.execute → await session.execute,commit/rollback/close 都要 await。
  • 异步栈里有同步遗留库怎么办?答:用线程池(run_in_executor/anyio.to_thread)隔离阻塞调用,不要在事件循环里跑同步 DBAPI。
  • 能不能一个 AsyncSession 被多个协程并发用?答:不能,AsyncSession 同样要求串行使用,并发操作要各建 Session。

04.7.21.1-013 sessionmaker 有哪些关键参数?

一句话结论

sessionmaker 是 Session 工厂,关键参数包括 bind(绑定 Engine/连接)、autoflush(查询前是否自动 flush,默认 True)、expire_on_commit(commit 后对象是否过期,默认 True)、autocommit(1.x 概念,2.x 已移除)、class_(自定义 Session 子类)。工厂在应用启动时创建一次。

概念解释

  • bind:Session 使用的 Engine;也可后面 session.bind。
  • autoflush=True:查询前自动把脏变更 flush,避免读到未落库的旧状态。
  • expire_on_commit=True:commit 后实例属性过期,下次访问重新查库。

原理流程

  1. 调用 sessionmaker(bind=engine, ...) 生成工厂类。
  2. 每次调用工厂按参数实例化新 Session。
  3. 参数随 Session 实例固化,运行中可改单个 Session 属性。
  4. 工厂复用避免重复传参,保证全站 Session 配置一致。

代码或图示

SessionLocal = sessionmaker(
    bind=engine,
    autoflush=True,
    expire_on_commit=False,
)

常见误区

  • 在请求循环里反复 sessionmaker(engine):工厂可复用,重复创建本身开销不大,但散落配置容易不一致。
  • 混淆 autoflush 与 commit:autoflush 只 flush 到事务内,不提交;commit 才落事务边界。
  • 以为 expire_on_commit 影响数据库数据:它只影响 Python 实例属性的过期标记,不改变已提交数据。

追问

  • autocommit 在 2.x 里去哪了?答:2.x 移除了该参数,事务由 Session 自动开始与显式 commit()/rollback() 控制(待确认:2.x 迁移说明中旧参数映射)。
  • 工厂参数和实例参数冲突吗?答:实例化时可覆盖工厂默认值,Session 属性优先级更高。
  • 自定义 Session 子类怎么挂?答:sessionmaker(class_=MySession, bind=engine),子类可封装公共方法。

04.7.21.1-014 expire_on_commit=False 什么时候该开?

一句话结论

expire_on_commit=False 表示 commit 后不把实例属性标记过期,代码可继续读已有属性而不触发二次查询;默认 True,commit 后属性过期,访问任一属性会重新 SELECT。高并发读多写少、commit 后还要返回对象给序列化层的场景常用 False 减少往返。

概念解释

  • 默认 True:commit 后对象进入 expired 状态,属性访问触发懒加载查询;好处是保证读到最新提交状态。
  • False:保留内存中的属性值;风险是后续若有并发修改,内存值可能不是数据库最新值。

原理流程

  1. commit 提交事务,Session 内对象按配置处理。
  2. True:所有属性置为 expired,下次访问查询数据库。
  3. False:属性保留,直接读内存快照。
  4. 对象被其他事务修改时,False 的 Session 不会自动刷新,需显式 refresh()。

代码或图示

SessionLocal = sessionmaker(bind=engine, expire_on_commit=False)

with SessionLocal() as s:
    user = User(name="ann")
    s.add(user)
    s.commit()
    # expire_on_commit=False 时直接可读 user.name,不触发查询
    return user.name

常见误区

  • 一律开 False 规避过期查询:读到陈旧快照的风险被忽略,长 Session 下更明显。
  • 开了 False 就以为数据一定最新:并发更新后内存值可能过期,需要 session.refresh(obj) 显式刷新。
  • 把 expire_on_commit 误解为是否提交数据:它只控制实例属性过期,与事务提交无关。

追问

  • 过期对象访问时 Session 已关闭会怎样?答:detached 状态下访问未加载属性会抛 DetachedInstanceError。
  • 想强制刷新单个对象怎么做?答:session.refresh(obj) 重新 SELECT;session.expire_all() 批量过期。
  • Web 服务返回模型该用哪种?答:常用 False 配合 commit 后直接序列化;若事务内后续还有读一致性要求,保持默认 True 更稳妥。

04.7.21.1-015 autoflush 与手动 flush 怎么配合?

一句话结论

autoflush=True(默认)在执行查询前自动 flush 待写入变更,保证查询能看到本 Session 内未提交的修改;关闭后需要手动 session.flush()。flush 只把变更推到当前事务,不提交事务,连接与事务保持到 commit/rollback。

概念解释

  • flush:对象状态 → SQL(INSERT/UPDATE/DELETE),仍在事务内,未提交。
  • commit:提交事务,变更对外可见。
  • 关闭 autoflush 的场景:批量导入前先统计、避免查询触发意外写入,或精细控制 SQL 顺序。

原理流程

  1. 对象 add/修改,进入 pending/dirty 集合。
  2. 执行查询时若 autoflush=True,先 flush 脏集合再发 SELECT。
  3. autoflush=False 时查询直接发 SQL,脏变更留在 Session。
  4. 显式 flush() 生成 SQL;commit() 内含一次 flush 再提交。

代码或图示

s = SessionLocal(autoflush=False)
s.add(User(name="a"))
s.flush()          # 手动 flush,拿到自增主键
count = s.query(User).count()  # 不会自动 flush
s.commit()

常见误区

  • 以为 flush 就是 commit:flush 不结束事务,连接仍占用,中途崩溃会整体回滚。
  • 关闭 autoflush 后忘记 flush:查询读不到刚修改的数据,调试时误以为代码没生效。
  • 大对象批量处理中反复 flush:每次 flush 都产生 SQL 与脏检查开销,批量场景应分批 flush/commit。

追问

  • flush 后能拿到自增主键吗?答:能,flush 后 obj.id 已回填。
  • autoflush 会影响性能吗?答:每次查询前做脏检查,长 Session 对象多时开销上升;一般保持默认即可。
  • flush 失败会怎样?答:抛出 SQLAlchemy 错误,事务进入异常状态,需要 rollback 后再操作。

04.7.21.1-016 2.x select() 与 1.x Query 怎么对应?

一句话结论

2.x 推荐用 select(Model) 构造语句,再交给 session.execute(stmt) 执行;1.x 的 session.query(Model) 链式 API 在 2.x 仍保留为兼容层,但新代码应迁移到 select()。2.x 的语句是可组合、可独立于 Session 构造的对象,Core 与 ORM 查询写法统一。

概念解释

  • 1.x:session.query(User).filter(User.age > 18).order_by(User.id),查询对象绑定 Session。
  • 2.x:stmt = select(User).where(User.age > 18).order_by(User.id),result = session.execute(stmt),语句与执行分离。
  • 结果取值:result.scalar_one()、result.scalars().all()、result.mappings().all()。

原理流程

  1. 用 select(...) 构造语句对象,可叠加 where/order_by/limit/options。
  2. session.execute(stmt) 执行并返回 Result。
  3. 按标量、元组或映射方式取值。
  4. 兼容层 session.query() 内部最终也走 2.x 执行路径。

代码或图示

from sqlalchemy import select

# 2.x 写法
stmt = select(User).where(User.age >= 18).order_by(User.id).limit(20)
users = session.execute(stmt).scalars().all()

# 1.x 兼容写法(新代码不推荐)
users = session.query(User).filter(User.age >= 18).order_by(User.id).limit(20).all()

常见误区

  • 2.x 里继续链式 query().filter() 当新代码:能用但偏离推荐路径,迁移成本后续更高。
  • execute(select(User)) 后直接遍历拿元组:默认返回 ORM 实体需 scalars();取列元组用 result.all(),映射字典用 mappings()。
  • 把 1.x get() 与 2.x 混淆:2.x 用 session.get(User, pk),按主键直接查。

追问

  • selectinload 等加载选项挂在哪里?答:select(User).options(selectinload(User.addresses))。
  • 2.x 还支持 query 装饰关系懒加载吗?答:兼容层保留,但关系加载建议显式写 selectinload/joinedload。
  • 语句对象能复用吗?答:可以,select() 构造后可多次 execute;带绑定参数时用 bindparams。

04.7.21.1-017 2.x Session.execute 相比旧查询有何变化?

一句话结论

2.x 统一执行入口为 session.execute(stmt),返回 Result 对象,取值方法显式化(scalar_one/scalars/mappings);1.x query() 的链式方法(filter/order_by/all/first)在兼容层可用。2.x 还要求显式处理结果元组解包,避免隐式展开。

概念解释

  • 1.x query(User, Address) 返回元组列表;2.x execute(select(User, Address)) 同样返回元组,但需通过 result.all() 或 mappings() 取。
  • 2.x 对 ORM 实体查询推荐 scalars() 解包实体,避免拿到 (User,) 单元素元组。

原理流程

  1. 构造 select 语句。
  2. session.execute 绑定当前 Session 与事务执行。
  3. Result 提供多种取值视图:标量、标量列表、映射、行元组。
  4. 异常场景:scalar_one() 要求恰好一行,多行/零行分别抛 MultipleResultsFound/NoResultFound。

代码或图示

result = session.execute(select(User, Address).join(Address))
for user, address in result.all():
    ...

# 只取实体
users = session.execute(select(User)).scalars().all()

# 字典式取值
rows = session.execute(select(User.name, User.age)).mappings().all()

常见误区

  • 忘记 scalars(),把 (User,) 元组当实体用:属性访问报错或类型不符。
  • 用 first() 替代 scalar_one() 做唯一性校验:first() 多行只取第一行,不校验唯一性。
  • 2.x 中混用 query 与 execute 结果风格:同一代码库两种取值方式增加维护成本。

追问

  • one() 和 scalar_one() 区别?答:one() 返回行元组,scalar_one() 解包单个标量/实体;二者都要求恰好一行。
  • 查不到用哪个方法?答:scalar_one_or_none() 允许零行,多行仍报错。
  • execute 能跑原生 SQL 吗?答:能,session.execute(text("SELECT ...")),参数用 :name 占位并传参字典。

04.7.21.1-018 2.x 的 Mapped 注解模型怎么写?

一句话结论

2.x 推荐用 DeclarativeBase 作为声明式基类,列类型用 Mapped[int]/Mapped[str] 注解声明,mapped_column() 补充约束;关系用 Mapped[list["Address"]] = relationship(...)。类型注解同时承担类型检查与 ORM 映射双重职责,配合 mypy 可静态校验。

概念解释

  • 1.x 传统写法:Column(Integer, primary_key=True) 直接赋值给类属性。
  • 2.x 注解写法:id: Mapped[int] = mapped_column(primary_key=True),Mapped 包裹 Python 类型。
  • 可选字段:Mapped[Optional[str]];自引用/前向引用用字符串注解。

原理流程

  1. 定义 Base = DeclarativeBase。
  2. 模型类继承 Base,类属性写 Mapped[...] 注解。
  3. mapped_column() 指定主键、默认值、唯一、索引、外键等。
  4. 关系属性用 relationship(),类型标注 Mapped[list[...]] 或 Mapped[Optional[...]]。

代码或图示

from sqlalchemy import ForeignKey, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    age: Mapped[Optional[int]] = mapped_column(nullable=True)
    addresses: Mapped[list["Address"]] = relationship(back_populates="user")

class Address(Base):
    __tablename__ = "addresses"
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    user: Mapped[User] = relationship(back_populates="addresses")

常见误区

  • 2.x 模型还继承 1.x 的 declarative_base():能用但旧风格,丢失类型注解收益。
  • Mapped 内部类型与 mapped_column 类型不一致:类型检查与数据库列类型冲突,需对齐。
  • 忘记给关系集合写 Mapped[list[...]]:集合类型无法被静态检查推断,懒加载属性可能类型丢失。

追问

  • 1.x 的 Column 写法在 2.x 还能用吗?答:能,兼容保留;新项目建议注解写法。
  • mapped_column 和 Column 能力一样吗?答:职责对应,mapped_column 配合注解声明,约束参数一致。
  • 枚举字段怎么标注?答:Python Enum 类型配 Mapped[Status],列用 Enum(Status)。

04.7.21.1-019 1.x 与 2.x 在事务开启上有什么差异?

一句话结论

1.x 存在隐式事务开始/提交的历史包袱(如 autocommit 语义、连接首次使用时开启事务);2.x 明确 Session 事务模型:连接首次被使用时开启事务,commit()/rollback() 结束事务,之后新操作再开启下一个事务。2.x 移除了 autocommit 参数,事务边界更可预期。

概念解释

  • 2.x 中 Session 事务由 SessionTransaction 管理,session.in_transactive() 可查询当前是否在事务中。
  • 连接在事务需要时才从池取出,纯读配置场景不会借连接。
  • 1.x 迁移时重点检查依赖旧 autocommit/隐式提交的代码路径。

原理流程

  1. Session 创建时不借连接、不开事务。
  2. 首次查询/写入触发连接获取并开启事务。
  3. 变更在事务内累积,flush 生成 SQL。
  4. commit() 提交并结束事务;rollback() 回滚结束;下一次操作重新开事务。

代码或图示

with SessionLocal() as s:
    print(s.in_transaction())  # False
    s.add(User(name="x"))
    print(s.in_transaction())  # True
    s.commit()
    print(s.in_transaction())  # False

常见误区

  • 以为 Session 创建就持有数据库连接:2.x 懒获取,连接只在需要时借出。
  • 依赖 1.x 旧行为:某些操作后数据自动可见,2.x 必须显式 commit()。
  • 长事务期间一直持有连接:事务不结束连接不归还,是池占用的主要来源之一。

追问

  • 怎么查当前是否在事务中?答:session.in_transaction() 返回布尔值。
  • 嵌套事务怎么开?答:session.begin_nested() 创建 savepoint,外层事务仍存在。
  • 2.x 迁移要注意哪些旧 API?答:Query 链式、autocommit、旧 relationship 懒加载默认行为等需对照迁移指南(待确认:具体弃用清单以官方 2.0 迁移文档为准)。

04.7.21.1-020 业务该用 ORM 还是 Core?

一句话结论

ORM 适合业务实体多、关系复杂、需要对象建模与工作单元管理的 CRUD 主流程;Core(SQL 表达式层)适合大批量统计、报表、复杂聚合、性能敏感的读写,直接控制 SQL 形态。两者可在同一应用混用:主业务走 ORM,重查询走 Core/原生 SQL。

概念解释

  • ORM 价值:面向对象建模、关系映射、脏检查、迁移模型对齐、减少样板 SQL。
  • Core 价值:贴近 SQL、无对象构造开销、批量聚合表达力强、结果直接是行列。
  • 性能差异主要来自 ORM 的对象实例化与关系加载,而非 SQL 本身。

原理流程

  1. 评估场景:实体关系多、事务内对象变更多 → ORM。
  2. 批量聚合、大结果集、报表 → Core 表达式或 text()。
  3. 混合时以 Core 语句 execute 取值,ORM 负责写路径。
  4. 重查询必须看执行计划,避免 ORM 生成的低效 SQL 被放过。

代码或图示

from sqlalchemy import func

# Core 聚合:按部门统计人数
stmt = select(Employee.dept_id, func.count(Employee.id)).group_by(Employee.dept_id)
rows = session.execute(stmt).all()

常见误区

  • 全表统计也走 ORM 实体列表:先把行构造成对象再 Python 层聚合,内存与 CPU 浪费严重。
  • 因 ORM 性能争议就全面弃用:多数 Web CRUD 瓶颈不在 ORM 映射,而在 N+1 与缺索引。
  • Core 语句不做参数化:原生 text() 仍要绑定参数,避免拼接注入。

追问

  • 大结果集怎么避免一次性载入?答:服务端游标/分批 limit offset,或流式 yield_per(待确认:2.x 流式取值方法与驱动支持)。
  • ORM 生成的 SQL 在哪看?答:echo=True 或日志级别 sqlalchemy.engine 设为 INFO。
  • 原生 SQL 适合什么场景?答:复杂分析、窗口函数、方言特有优化,ORM 表达不便时使用。

04.7.21.1-021 relationship 加载策略有哪几种?

一句话结论

加载策略控制关联对象何时、以何种 SQL 取出:lazy="select"(默认,访问时单查)、joined(JOIN 一次取出)、selectin(按主键集合 IN 查)、subquery(子查询批量取)、dynamic(查询器延迟过滤)、noload/raise。策略可在关系定义时写死,也可在查询时用 options() 覆盖。

概念解释

  • 加载分两类:立即加载(joined/selectin/subquery)在主查询阶段一并取;懒加载(select/dynamic)访问时再查。
  • 查询时覆盖优先级高于模型定义:select(User).options(joinedload(User.addresses))。

原理流程

  1. 主查询取出主对象列表。
  2. 按策略决定关联对象的取数时机与 SQL 形态。
  3. 立即加载策略在主查询前后追加 JOIN/IN/子查询。
  4. 懒加载在访问属性时触发额外查询。

代码或图示

from sqlalchemy.orm import selectinload, joinedload

# 查询时覆盖:selectin 批量取地址
users = session.execute(
    select(User).options(selectinload(User.addresses))
).scalars().all()

常见误区

  • 模型上写死加载策略无法应对不同场景:列表页需要批量、详情页需要单查,应在查询层用 options 按需覆盖。
  • 认为 joined 一定最快:一对多 JOIN 会产生主对象行膨胀,需配合 contains_eager 去重。
  • 忽略懒加载在 Session 关闭后触发:detached 后访问关系属性抛 DetachedInstanceError。

追问

  • 默认 lazy 值是多少?答:2.x 关系默认仍为 select(待确认:2.0 默认懒加载行为有无调整)。
  • 加载策略能在反向关系上分别设吗?答:能,双向关系两侧策略独立配置。
  • 嵌套关系怎么批量加载?答:selectinload(User.addresses).selectinload(Address.orders) 链式叠加。

04.7.21.1-022 lazy="select" 为什么会导致 N+1?

一句话结论

默认 lazy="select" 在每个主对象访问关系属性时单独发一条 SELECT:主列表 1 条 SQL,N 个对象各 1 条关联 SQL,共 N+1 条。N 越大数据库往返越多,是 ORM 应用最常见的性能问题。修复方向是显式改为 selectinload/joinedload 批量加载。

概念解释

  • N+1 触发点:序列化层遍历主对象逐个访问关系属性;模板/序列化器常隐式触发。
  • 根因是懒加载按对象粒度取数,而非按集合批量取。

原理流程

  1. 查询主对象列表:SQL 1。
  2. 遍历每个对象,访问 user.addresses。
  3. 每个对象触发一条按 user_id 查地址的 SQL:SQL 2…N+1。
  4. 总往返数与列表长度线性相关,慢查询日志中出现大量相同形态 SQL。

代码或图示

# 触发 N+1
users = session.execute(select(User)).scalars().all()
for u in users:
    print(u.addresses)  # 每个 u 一条 SELECT

# 修复:批量预取
users = session.execute(
    select(User).options(selectinload(User.addresses))
).scalars().all()

常见误区

  • 只在模型上改默认策略:不同页面访问的关系不同,全局改 joined 可能把不需要的关系也 JOIN 出来。
  • 以为加了缓存就解决 N+1:缓存只挡重复请求,单次请求内 N 条 SQL 仍在。
  • 排查时只看接口总耗时:需打开 SQL 日志按条数与形态聚类,才能发现重复小查询。

追问

  • N+1 在什么数据量下明显?答:列表几十条以上就会放大往返;小列表影响有限,但接口增长后复发。
  • 怎么静态发现 N+1?答:代码审查遍历访问关系的位置;运行期开 SQL 统计,按 SQL 模板分组看次数。
  • 懒加载在异步 Session 里有什么限制?答:AsyncSession 中懒加载默认不能隐式触发,必须显式 selectinload 等异步加载路径。

04.7.21.1-023 joinedload 的原理与适用场景是什么?

一句话结论

joinedload 通过 LEFT JOIN(默认;可配 INNER JOIN)在主查询里一并取出关联对象,主对象与关联对象同一条 SQL 返回。适合一对一场景(不膨胀主行);一对多时 JOIN 会让主对象按关联行数膨胀,需正确去重或改用 selectinload。

概念解释

  • joinedload 默认 LEFT OUTER JOIN,关联为空时主对象仍在。
  • 一对多使用 joinedload 时,同一主对象会出现多行,SQLAlchemy 在结果层按 identity map 合并去重。
  • 多集合同时 joinedload 会形成笛卡尔积,应避免。

原理流程

  1. 主表与关联表 JOIN,SELECT 两侧列。
  2. 结果行在 Result 中按实体拆分。
  3. identity map 合并重复主对象,回填关系集合。
  4. 一对多膨胀行越多,网络传输与内存构造成本越高。

代码或图示

# 一对一:joinedload 最直接
user = session.execute(
    select(User).options(joinedload(User.profile))
).scalar_one()

# 一对多 joinedload 会产生行膨胀
users = session.execute(
    select(User).options(joinedload(User.addresses))
).scalars().unique().all()

常见误区

  • 一对多大量数据还强行 joinedload:行膨胀严重,SQL 与传输开销反超 selectin。
  • 同时 joinedload 两个一对多集合:JOIN 相乘形成笛卡尔积,结果行数爆炸。
  • 忽略 JOIN 类型:需要内连接语义时用 joinedload(..., innerjoin=True),默认 LEFT 可能返回空关联。

追问

  • joinedload 和手写 JOIN 有什么区别?答:joinedload 不影响 WHERE 过滤关联表;需要按关联表条件过滤应手写 join(),再用 contains_eager 回填。
  • 一对多推荐哪种加载?答:集合规模中等用 selectinload;一对一用 joinedload。
  • joinedload 结果去重要调 unique() 吗?答:2.x 一对多 joinedload 结果建议 .unique() 避免重复行解包问题。

04.7.21.1-024 selectinload 怎么避免 N+1?

一句话结论

selectinload 先查主对象,收集主键集合,再用一条 IN (主键列表) 批量查出关联对象,按外键回填到各主对象。主对象 N 条只追加 1 条关联 SQL,把 N+1 压成 2 条;是一对多集合批量加载的首选策略。

概念解释

  • 第二条 SQL 的 IN 列表为当前主对象主键,数量随列表长度增长。
  • 主键很多时 IN 列表过长,需注意数据库 IN 上限与 SQL 长度(待确认:各数据库 IN 元素上限)。
  • selectinload 不产生 JOIN 行膨胀,主表与关联表分别取数。

原理流程

  1. 执行主查询,得到主对象主键列表。
  2. 构造 SELECT ... WHERE user_id IN (...) 批量查关联。
  3. 按外键分组,回填到对应主对象的关系集合。
  4. 嵌套关系可链式 selectinload 继续批量取下一级。

代码或图示

from sqlalchemy.orm import selectinload

users = session.execute(
    select(User).options(
        selectinload(User.addresses).selectinload(Address.orders)
    )
).scalars().all()

常见误区

  • 主键上万时仍用 selectinload:IN 列表过长,应分页/分批或改其他策略。
  • 以为 selectinload 一条 SQL 解决全部:它至少两条 SQL(主表 + 关联表),但与 N 无关。
  • 嵌套层级过深导致 SQL 条数叠加:每层各一条批量 SQL,要控制预取层级。

追问

  • selectinload 与 subqueryload 区别?答:selectin 用 IN 主键集合;subquery 把子查询作为派生表 JOIN,适合需要关联条件过滤的批量场景。
  • IN 列表太长怎么优化?答:分批查询、限制主列表分页大小,或改为按外键范围批量取。
  • selectinload 能用于多对多吗?答:能,中间表由 ORM 处理,加载行为同样是批量 IN。

04.7.21.1-025 subqueryload 适用什么场景?

一句话结论

subqueryload 把主查询作为子查询,关联表与该子查询 JOIN 批量取出关联对象。适合关联对象需要附加过滤条件、或主查询本身较复杂的批量加载;常规一对多批量优先 selectinload,subqueryload 是补充手段。

概念解释

  • 子查询复用主查询的 FROM/WHERE,避免重复过滤条件。
  • 结果形态类似 joinedload 的派生表 JOIN,但不直接 JOIN 主表原始行。
  • 复杂主查询下 subqueryload 的 SQL 可能较长,需看执行计划。

原理流程

  1. 主查询生成子查询语句。
  2. 关联表 JOIN 该子查询,批量取关联行。
  3. 结果按外键回填主对象关系。
  4. 主查询条件变更时子查询自动跟随,无需重复维护过滤逻辑。

代码或图示

from sqlalchemy.orm import subqueryload

users = session.execute(
    select(User).where(User.active.is_(True))
    .options(subqueryload(User.addresses))
).scalars().all()

常见误区

  • 简单一对多也用 subqueryload:SQL 比 selectin 复杂,收益不明显。
  • 子查询引用主表别名出错:多表 JOIN 时别名与子查询列要对齐。
  • 忽略执行计划:子查询可能被优化器展开或物化,实际开销需用 EXPLAIN 验证。

追问

  • subqueryload 会膨胀主行吗?答:它 JOIN 的是子查询派生表,回填在结果层完成;一对多仍需注意重复行合并。
  • 三种批量加载怎么选?答:一对一 joinedload;一对多常规 selectinload;主查询复杂或需子查询过滤用 subqueryload。
  • 子查询加载能分页吗?答:预取与主列表分页配合,主列表先分页再批量加载当前页关联。

04.7.21.1-026 双向关系两侧的加载策略要注意什么?

一句话结论

双向关系(relationship + back_populates/backref)两侧策略独立:一侧预取不会自动回填另一侧,配置不当会出现重复加载或反向访问触发懒查询。设计时明确列表页从哪一侧进入,预取主路径需要的关系,反向侧保持懒加载即可。

概念解释

  • back_populates 显式双向绑定;backref 自动创建反向属性。
  • 预取只填充当前加载方向的对象图;从已加载对象访问未预取的反向属性仍会懒加载。
  • 集合与标量两侧类型标注要对应:Mapped[list[...]] 与 Mapped[Optional[...]]。

原理流程

  1. 定义双向关系,两侧用 back_populates 对齐属性名。
  2. 查询一侧时按该侧策略加载;关联对象的反向引用由 ORM 自动设置。
  3. 反向属性未在本次预取范围内,访问时触发懒加载。
  4. 序列化路径遍历到哪一侧,就要保证该侧关系已预取。

代码或图示

class User(Base):
    addresses: Mapped[list["Address"]] = relationship(back_populates="user")

class Address(Base):
    user: Mapped[User] = relationship(back_populates="addresses")

常见误区

  • 两侧都配 joinedload:同一关系被重复 JOIN,SQL 冗余。
  • 用 backref 又在另一侧手写 relationship:重复定义导致关系双向绑定冲突。
  • 认为回填后反向属性已加载:回填是对象引用赋值,不等于数据库已查出该反向集合。

追问

  • backref 和 back_populates 选哪个?答:新代码推荐 back_populates,显式可检查;backref 适合简单模型。
  • 循环加载会不会导致递归?答:对象图双向引用,序列化时要避免循环引用展开,需要截断层级。
  • 反向关系懒加载在 Session 外报错怎么办?答:在查询阶段显式预取,或序列化前在 Session 内完成取值。

04.7.21.1-027 lazy="dynamic" 与普通懒加载有何不同?

一句话结论

lazy="dynamic" 不把关系集合加载成列表,而是返回一个可继续过滤/排序的查询器(1.x Query 或 2.x 兼容对象),访问时才执行查询。适合集合很大、需要分页或条件筛选的关系;代价是不能直接当列表用,且每次操作都重新查询。

概念解释

  • 普通 select:访问即把整个集合载入内存列表。
  • dynamic:返回查询对象,可叠加 filter/order_by/limit,all()/count() 触发 SQL。
  • 2.x 中动态关系的 API 仍在兼容层,新代码也可在主查询里按需构造 Core 语句。

原理流程

  1. 访问关系属性拿到动态查询器,不含数据。
  2. 叠加过滤/排序/分页条件。
  3. 执行方法触发 SQL,返回子集。
  4. 新增对象加入关系时不会立即进入已有动态查询结果,需重新查询。

代码或图示

# 模型定义
posts: Mapped[list["Post"]] = relationship(lazy="dynamic")

# 使用:分页取最近 10 条
recent = user.posts.order_by(Post.created_at.desc()).limit(10).all()

常见误区

  • 把 dynamic 集合当列表 len()/迭代直接用:拿到的是查询器,需先执行。
  • 大集合用普通 select 全量载入:内存膨胀,应改用 dynamic 或分页预取。
  • 混用两种风格:同一关系有时当列表有时当查询器,调用方容易踩错。

追问

  • dynamic 能配合 selectinload 预取吗?答:不能,动态关系本身延迟查询,批量预取策略不适用。
  • 2.x 推荐替代方案是什么?答:在查询层用独立 Core 语句按外键条件查子集,模型关系保持普通加载。
  • dynamic 关系怎么做计数?答:查询器 count() 生成 COUNT(*) SQL,不载入明细。

04.7.21.1-028 lazy="noload" 和 lazy="raise" 分别防什么?

一句话结论

lazy="noload" 访问关系时不发 SQL,返回空集合/None;lazy="raise" 在触发懒加载时直接抛异常,防止应用在预取不充分时隐式打数据库。详情页/后台任务可按场景使用 raise 暴露遗漏的预取。

概念解释

  • noload:关系保持空值,适合只取主对象、明确不需要关联数据的场景。
  • raise:用于测试/严格模式,任何隐式懒加载都报错,迫使查询显式预取。
  • 两者都避免「不经意间多发 SQL」,但语义一个静默跳过、一个报错拦截。

原理流程

  1. 模型或查询选项指定 noload/raise。
  2. 访问关系属性时按策略处理:noload 返回空,raise 抛 LazyLoadError。
  3. 必要时在查询层用 options() 单独覆盖某一关系的加载方式。

代码或图示

from sqlalchemy.orm import noload, raiseload

users = session.execute(
    select(User).options(noload(User.addresses), raiseload(User.posts))
).scalars().all()

常见误区

  • 生产全局开 raise 后遗漏预取:接口直接 500,需要先在测试环境暴露问题再收紧。
  • noload 返回空被业务当成「确实无关联数据」:空集合可能是未加载,需区分语义。
  • 以为 raise 阻止所有关系访问:只阻止懒加载触发的 SQL,已预取的属性可正常读。

追问

  • raise 抛什么异常?答:sqlalchemy.orm.exc.LazyLoadError(待确认:2.x 异常类名与模块路径)。
  • 怎么在测试中发现 N+1?答:开 raise 或记录 SQL 条数,访问关系即报错/计数超标。
  • noload 能减少内存吗?答:能,不构造关联对象;但序列化层读到空值要确认业务允许。

04.7.21.1-029 contains_eager 与手写 JOIN 怎么配合?

一句话结论

当需要按关联表条件过滤(WHERE/JOIN 条件在关联表上),手写 join() 后用 contains_eager() 告诉 ORM 把已 JOIN 出来的列回填为关系对象;joinedload 只做加载、不参与过滤。两者用途不同:过滤靠手写 JOIN,回填靠 contains_eager。

概念解释

  • joinedload(User.addresses):额外 JOIN,不改变主查询的 WHERE 语义。
  • join(Address).where(Address.city == "x"):过滤条件落在关联表,需要 contains_eager(User.addresses) 回填。
  • 手写 JOIN 可能出现重复主行,配合 unique()/去重处理。

原理流程

  1. select(User).join(Address).where(Address.city == "昆明") 构造过滤查询。
  2. .options(contains_eager(User.addresses)) 标记关联列回填路径。
  3. 执行后结果按实体拆分,回填关系集合。
  4. 未匹配过滤条件的主对象不出现在结果中。

代码或图示

from sqlalchemy.orm import contains_eager

stmt = (
    select(User)
    .join(User.addresses)
    .where(Address.city == "昆明")
    .options(contains_eager(User.addresses))
)
users = session.execute(stmt).unique().scalars().all()

常见误区

  • 用 joinedload 加 WHERE 过滤关联表:joinedload 的 JOIN 不影响主过滤,条件不生效。
  • 手写 JOIN 后忘记 contains_eager:关联列返回为行元组,关系属性仍懒加载,再次触发 N+1。
  • 多表 JOIN 别名混乱:contains_eager 路径要与实际 JOIN 链一致。

追问

  • contains_eager 是哪种加载策略的回填?答:对应 joinedload 的 JOIN 回填方式,用于手写 JOIN 场景。
  • 过滤后的关联集合是全部还是子集?答:回填的是本次 JOIN 命中的子集,未命中的关联行不在集合中。
  • 一对多过滤后主对象去重怎么做?答:Result.unique() 或在 SQL 层 DISTINCT,按数据库方言选择。

04.7.21.1-030 怎么定位一次接口里的 N+1 与慢查询?

一句话结论

排查链路:打开 SQL 日志(echo=True 或 sqlalchemy.engine 日志级别)→ 按 SQL 模板聚合条数与耗时 → 找出重复按主键/外键单查的形态定位懒加载关系 → 用 EXPLAIN 看执行计划判断缺索引/全表扫描 → 修复后对比 SQL 条数与接口耗时。

概念解释

  • N+1 特征:同一形态 SQL 随列表长度重复出现,参数为不同主键/外键。
  • 慢查询特征:单条 SQL 耗时长,常伴随全表扫描、缺索引、回表多、大结果集。
  • 生产环境不能长期开 echo,应接入慢查询日志与 ORM 语句计数指标。

原理流程

  1. 开发/测试环境开启引擎日志,复现接口请求。
  2. 汇总 SQL:统计模板出现次数、总耗时、单次最大耗时。
  3. 重复单查 → 定位对应关系属性,改为 selectinload/joinedload。
  4. 长 SQL → 数据库侧 EXPLAIN 分析 type/key/rows,补索引或改写。
  5. 修复后压测对比 SQL 条数、P95 耗时与数据库负载。

代码或图示

import logging
logging.basicConfig()
logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)
logging.getLogger("sqlalchemy.pool").setLevel(logging.INFO)

常见误区

  • 只看接口耗时不看 SQL 条数:慢在网络/序列化时,SQL 层面看不出问题;反之亦然。
  • 补索引前不看执行计划:重复索引、索引失效(函数包裹列、隐式类型转换)未被发现。
  • 把 N+1 全归罪 ORM:序列化器/模板隐式访问关系,代码路径审查同样关键。

追问

  • 生产怎么统计 SQL 条数?答:在 Session/引擎层挂钩子计数,按接口/任务暴露指标,或采集慢日志聚合。
  • EXPLAIN 重点看哪些列?答:type(访问类型,避免 ALL)、key(实际用索引)、rows(扫描行数)、Extra(Using filesort/Using temporary)。
  • 慢 SQL 改不了表结构怎么办?答:先加缓存、限制分页范围、拆分查询,长期再推动索引/表结构改造。

04.7.21.1-031 事务隔离级别怎么设置?各级别解决什么问题?

一句话结论

隔离级别由数据库会话决定,SQLAlchemy 可在 create_engine 的 isolation_level 参数或连接级 execution_options(isolation_level=...) 设置。ANSI 四级:读未提交、读已提交(RC,多数数据库默认)、可重复读(RR,MySQL InnoDB 默认)、串行化;级别越高一致性越强,并发与锁代价越大。

概念解释

  • RC:避免脏读;不可重复读仍可能。
  • RR:避免不可重复读;InnoDB 靠 MVCC 快照,幻读在一定条件下仍可能。
  • 串行化:加锁串行执行,并发最低。
  • 隔离级别是数据库方言概念,SQLAlchemy 只透传,不自己实现。

原理流程

  1. Engine 创建时指定默认 isolation_level。
  2. 借连接时把级别传给 DBAPI/数据库会话。
  3. 单条语句/连接可用 execution_options 覆盖。
  4. 业务按一致性要求选级别,避免全局拔高到串行化。

代码或图示

engine = create_engine(url, isolation_level="REPEATABLE READ")

# 连接级覆盖
with engine.connect().execution_options(isolation_level="SERIALIZABLE") as conn:
    ...

常见误区

  • 以为 ORM 能跨数据库保证隔离语义:实际行为由数据库实现决定,方言差异需分别验证。
  • 全局用串行化追求强一致:并发吞吐骤降,锁等待与死锁上升。
  • 忽略长事务在 RR 下的快照代价:MVCC 版本链堆积,清理压力增大。

追问

  • PostgreSQL 默认级别是什么?答:读已提交(READ COMMITTED)。
  • MySQL InnoDB 默认级别?答:可重复读(REPEATABLE READ)。
  • 怎么在事务中改隔离级别?答:级别在连接/会话建立时确定,事务中途切换需新连接或数据库支持的会话命令(待确认:各方言事务内切换限制)。

04.7.21.1-032 数据库死锁怎么产生?怎么排查?

一句话结论

死锁是两个以上事务互相持有对方等待的锁,形成循环等待;InnoDB 会主动检测并回滚其中一个事务。排查先看数据库死锁日志(MySQL SHOW ENGINE INNODB STATUS、PostgreSQL 日志),定位互相等待的 SQL 与加锁顺序,再统一事务内的加锁/更新顺序并缩短事务。

概念解释

  • 四个必要条件:互斥、持有并等待、不可剥夺、循环等待;破坏循环等待(统一顺序)最常见。
  • 典型场景:多事务以不同顺序更新同一批行;外键检查、唯一索引插入间隙也可能加锁。
  • 死锁与锁等待不同:锁等待是排队,超时才报错;死锁是循环,检测后立即回滚一方。

原理流程

  1. 事务 A 锁行 1,事务 B 锁行 2。
  2. A 等待行 2,B 等待行 1,形成循环。
  3. 数据库死锁检测器发现循环,选择代价较小的事务回滚。
  4. 被回滚方收到死锁错误(MySQL 1213 Deadlock found),上层按策略重试。

代码或图示

-- MySQL 查看最近死锁信息
SHOW ENGINE INNODB STATUS;
-- 关注 LATEST DETECTED DEADLOCK 段:等待事务、SQL、持锁类型

常见误区

  • 把锁等待超时当死锁:lock wait timeout 是排队超时,死锁是循环检测,二者日志与处理不同。
  • 只靠重试不调整顺序:重试能缓解偶发死锁,但根因是加锁顺序不一致,高并发下复发。
  • 大事务多表更新:持锁时间长、锁范围大,死锁概率显著上升。

追问

  • 死锁被回滚的一方报什么错?答:SQLAlchemy 包装为 OperationalError,底层 MySQL 错误码 1213。
  • 怎么统一加锁顺序?答:事务内按固定主键/索引顺序更新,例如先按 id 排序再批量处理。
  • 外键会加剧死锁吗?答:会,外键检查会对子表/父表加共享锁,更新主表时需关注关联行。

04.7.21.1-033 死锁重试的边界与幂等怎么设计?

一句话结论

死锁/锁等待错误可在有限次数内重试(指数退避,如 3 次),前提是操作幂等:重试不会重复扣款、重复下单。非幂等写操作必须先引入去重键或事务回滚干净,再谈重试;重试次数与退避要写入配置,避免重试风暴。

概念解释

  • 可重试错误:死锁(1213)、锁等待超时(1205)、连接级瞬时故障。
  • 不可盲目重试:业务校验失败、唯一约束冲突、参数错误不应重试。
  • 幂等手段:唯一业务单号、乐观锁版本、去重表、先查后写的条件更新。

原理流程

  1. 捕获数据库异常,判断是否属于可重试类别。
  2. 事务已被回滚(死锁)或需先 rollback() 清理 Session 状态。
  3. 按退避间隔重试,次数上限内重新执行业务。
  4. 超过次数上报告警;每次重试基于同一幂等键,结果可重复。

代码或图示

import time
from sqlalchemy.exc import OperationalError

def run_with_retry(session_factory, fn, retries=3):
    for attempt in range(retries):
        session = session_factory()
        try:
            result = fn(session)
            session.commit()
            return result
        except OperationalError as e:
            session.rollback()
            if attempt == retries - 1 or not _is_retryable(e):
                raise
            time.sleep(0.05 * (2 ** attempt))
        finally:
            session.close()

常见误区

  • 事务未回滚就重试:Session 处于异常状态,继续操作会报事务已回滚错误。
  • 重试非幂等写:重复扣款/重复发货,比死锁本身更严重。
  • 无限重试或固定短间隔:下游故障时放大流量,形成重试风暴。

追问

  • 怎么判断异常可重试?答:按底层错误码/异常类型白名单匹配,不把所有 OperationalError 都当可重试。
  • 重试期间请求超时怎么办?答:同步接口设置总超时预算,超限转异步任务;重试在预算内进行。
  • 幂等键放哪里?答:业务唯一单号入库加唯一索引,重复请求被数据库拦截。

04.7.21.1-034 begin_nested savepoint 解决什么问题?

一句话结论

session.begin_nested() 在当前事务内创建一个 savepoint,内部子事务可独立回滚而不影响外层事务;用于「部分失败可回退、整体仍需提交」的场景,如批量处理中单条失败跳过、嵌套业务步骤局部撤销。它不是真正的分布式事务,外层提交才整体落库。

概念解释

  • savepoint 是数据库事务内的回滚点,回滚到 savepoint 后外层事务继续。
  • 2.x 推荐用 with session.begin_nested(): 上下文管理器,退出时自动 release/rollback。
  • 与嵌套 Session 不同:savepoint 仍在同一数据库事务与同一 Session 内。

原理流程

  1. 外层事务开启。
  2. begin_nested() 执行 SAVEPOINT sp_xxx。
  3. 子块内异常:回滚到该 savepoint,外层事务状态保留。
  4. 子块正常结束:release savepoint;外层 commit() 整体提交。

代码或图示

for item in items:
    try:
        with session.begin_nested():
            process(item)
    except ProcessingError:
        # 单条回滚到 savepoint,继续下一条
        log.warning("skip %s", item.id)

常见误区

  • 把 savepoint 当独立事务:内层回滚不提交任何数据,外层回滚则全部撤销。
  • savepoint 内抛异常后外层 Session 状态混乱:上下文管理器会正确回滚 savepoint,手动管理需显式 rollback 到点。
  • 跨数据库使用 savepoint:能力依赖方言,部分数据库/驱动支持有限(待确认:各方言 savepoint 支持差异)。

追问

  • savepoint 与 SAVEPOINT SQL 对应吗?答:对应,ORM 生成 SAVEPOINT/ROLLBACK TO SAVEPOINT/RELEASE SAVEPOINT。
  • 嵌套层级有限制吗?答:受数据库 savepoint 数量与深度限制,业务上不应过深。
  • 批量导入部分失败怎么处理?答:逐条 savepoint 隔离失败项,最后外层统一提交,失败记录落错误表。

04.7.21.1-035 事务回滚后 Session 还能继续用吗?

一句话结论

rollback() 回滚当前事务并清理 Session 内脏状态、过期对象;之后 Session 可以继续使用,开启下一个事务。但如果异常来自连接级故障(连接已断开),应关闭 Session 重新创建;detached 对象在回滚后访问未加载属性可能报错。

概念解释

  • 回滚后:未提交变更撤销,identity map 中对象按配置过期,连接归还或保留由 Session 配置决定。
  • 连接损坏场景:rollback() 无法恢复失效连接,继续使用会再次抛连接异常。
  • 正确模式:请求级 Session 在异常后 rollback()/close(),不要在脏 Session 上重试业务。

原理流程

  1. 操作抛异常,事务进入失败状态。
  2. 调用 rollback():向数据库发回滚,Session 清理脏集合与快照。
  3. 连接仍健康:Session 可开启新事务继续。
  4. 连接已断:关闭 Session,由工厂新建。

代码或图示

try:
    session.add(obj)
    session.commit()
except DBAPIError:
    session.rollback()
    # 连接健康时可继续;连接故障则 close 重建
    raise

常见误区

  • 异常后不 rollback 直接 commit:事务已标记失败,commit 行为不可预期。
  • 复用回滚前的对象引用:属性已过期,继续修改可能基于脏快照。
  • 把 Session 当无状态工具反复复用:长生命周期 Session 累积状态,异常清理成本高。

追问

  • rollback 会归还连接吗?答:事务结束后连接仍由 Session 持有,close() 才归还池;具体行为受池与 Session 配置影响。
  • 批量任务中途失败要保留进度吗?答:进度落独立进度表/日志,不依赖未提交事务内的对象状态。
  • 怎么判断连接是否损坏?答:异常类型与消息(断连、重置)结合 pre-ping 探活,必要时 dispose 重建 Engine 连接。

04.7.21.1-036 长事务有哪些危害?

一句话结论

长事务长时间持有锁与连接,导致锁等待堆积、MVCC 版本链堆积(回滚段/undo 膨胀)、连接池被占满、备份与清理任务受影响。工程上应缩短事务边界:事务内只做必要读写,远程调用、消息投递、文件处理移出事务。

概念解释

  • 锁持有时间 = 事务时长;行锁/间隙锁范围随事务扩大。
  • MVCC 下旧版本不能被清理,长事务阻塞 purge,表膨胀。
  • 连接被长事务占住不归还,池容量被快速耗尽。

原理流程

  1. 事务开启后执行远程 HTTP、发 MQ、等待用户输入等慢操作。
  2. 期间行锁与快照一直持有,其他事务排队。
  3. 数据库 undo/版本链无法回收,表与索引膨胀。
  4. 事务结束后锁释放,但积压请求与清理压力滞后释放。

代码或图示

# 反例:事务内远程调用
with session.begin():
    order = create_order()
    notify_http(order)   # 慢,持锁等待
    session.add(order)

# 正例:事务内只落库,事务外发通知
with session.begin():
    order = create_order()
    session.add(order)
notify_http(order)

常见误区

  • 把「请求处理时长」当事务时长:事务从第一次 SQL 到 commit,中间任何慢操作都在拉长事务。
  • 大循环包在一个事务里:批量数据应分批 commit,避免单事务过大。
  • 异步任务里长事务无监控:后台任务慢不易被发现,需监控事务时长指标。

追问

  • 怎么监控长事务?答:数据库侧查 pg_stat_activity/processlist 的事务时长;应用侧记录事务开始与 commit 耗时。
  • 远程调用为什么要移出事务?答:网络耗时不可控,会把数据库锁暴露给外部延迟。
  • 批量任务怎么控制事务大小?答:按批次 commit,每批几百到几千行,平衡锁时长与提交次数。

04.7.21.1-037 乐观锁版本列怎么实现并发控制?

一句话结论

乐观锁在表上加版本列(如 version),更新时带 WHERE version = :old_version:命中则版本 +1,影响行数为 0 说明已被他人修改,抛版本冲突错误。ORM 用 version_id_col 配置,适合冲突少、读多写少的场景;冲突多时悲观锁或串行化更合适。

概念解释

  • 乐观假设冲突不常发生,提交时检查;不加读锁,依赖版本号校验。
  • 冲突检测在 UPDATE 语句影响行数,而非 SELECT 时加锁。
  • 版本列可以是整数递增,也可用时间戳/更新哈希。

原理流程

  1. 读取对象,得到当前版本号 v。
  2. 业务修改后提交,ORM 生成 UPDATE ... SET version=v+1 WHERE id=? AND version=v。
  3. 影响行数 1:提交成功;影响行数 0:并发修改已发生。
  4. 抛 StaleDataError,上层重试或提示刷新。

代码或图示

from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy import Integer

class Product(Base):
    __tablename__ = "products"
    id: Mapped[int] = mapped_column(primary_key=True)
    stock: Mapped[int]
    version: Mapped[int] = mapped_column(Integer, default=1)

# ORM 配置版本列(模型声明或 mapper 参数)
# __mapper_args__ = {"version_id_col": version}

常见误区

  • 高冲突场景硬上乐观锁:大量冲突重试,吞吐反而下降。
  • 更新时绕过 ORM 直接写 SQL:版本列不递增/不校验,乐观锁失效。
  • 版本冲突后不刷新就重试:基于旧快照再改,持续冲突。

追问

  • 版本冲突抛什么异常?答:sqlalchemy.orm.exc.StaleDataError(待确认:2.x 异常类模块路径)。
  • 乐观锁能防超卖吗?答:库存扣减高并发下乐观锁冲突率高,通常用条件更新 WHERE stock > 0 或悲观锁/原子扣减更直接。
  • 版本列必须整数吗?答:不必须,时间戳或内容哈希也可,但整数递增实现最简单。

04.7.21.1-038 悲观锁 with_for_update 怎么用?

一句话结论

with_for_update() 在 SELECT 上加行锁(SELECT ... FOR UPDATE),事务内锁住读取的行,其他事务阻塞直到释放。适合必须串行化的短临界区(如扣款前锁定库存行);锁持有时间要短,否则阻塞堆积。FOR UPDATE 由数据库方言支持。

概念解释

  • 悲观锁假定冲突会发生,读时即加锁。
  • 可配合 nowait/skip_locked 选项控制冲突行为(方言支持不同)。
  • 锁在事务结束时释放,必须在同一事务内完成后续更新。

原理流程

  1. 事务内 select(...).with_for_update() 读取并锁定目标行。
  2. 其他事务尝试锁同一行时阻塞或按选项跳过/立即报错。
  3. 当前事务完成校验与更新。
  4. commit/rollback 释放行锁。

代码或图示

stmt = select(Account).where(Account.id == aid).with_for_update()
account = session.execute(stmt).scalar_one()
account.balance -= amount
session.commit()

常见误区

  • 事务外执行 FOR UPDATE:语句结束锁即释放,失去保护意义。
  • 大范围 FOR UPDATE:锁住多行甚至扫描间隙,阻塞面过大。
  • 与乐观锁混用不区分场景:同一份代码两种并发策略,行为难以预测。

追问

  • nowait 和 skip_locked 区别?答:nowait 拿不到锁立即报错;skip_locked 跳过被锁行返回其他行(待确认:各方言支持范围)。
  • 悲观锁会阻塞读吗?答:取决于数据库隔离级别与锁类型,InnoDB 行锁阻塞写与加锁读,普通快照读不阻塞。
  • 锁等待超时怎么设?答:数据库 innodb_lock_wait_timeout,应用层配超时与错误处理。

04.7.21.1-039 乐观锁冲突后怎么重试与合并?

一句话结论

版本冲突后流程:先刷新最新数据(refresh/重新查询)→ 判断业务可否自动合并(纯增量字段可合并,覆盖型字段需人工或提示)→ 在新快照上重放业务 → 再次提交。冲突次数应埋点监控;持续冲突说明并发模型或锁策略需要调整。

概念解释

  • 可合并场景:计数追加、状态机单向流转、独立字段修改。
  • 不可自动合并:金额覆盖、用户选择被后写覆盖,需提示冲突或人工处理。
  • 重试必须短链路,避免长时间占用资源。

原理流程

  1. 捕获 StaleDataError,当前事务回滚。
  2. 重新读取最新版本与字段。
  3. 按业务规则合并:保留双方合法变更,或放弃本次修改。
  4. 在新版本上重放操作并提交;记录冲突次数与字段。

代码或图示

for _ in range(3):
    session = SessionLocal()
    try:
        product = session.get(Product, pid)
        product.stock -= qty
        session.commit()
        break
    except StaleDataError:
        session.rollback()
        # 重新读最新快照,重放业务
    finally:
        session.close()

常见误区

  • 不刷新直接重试:旧快照再提交,连续冲突。
  • 盲目覆盖最新值:后写覆盖先写,丢失他人更新。
  • 冲突无监控:高频冲突被当成普通报错,掩盖并发设计问题。

追问

  • 合并规则放哪一层?答:业务服务层定义字段级合并策略,ORM 只负责检测冲突。
  • 冲突率多高要换策略?答:冲突导致重试占比持续升高时,评估悲观锁、队列串行化或拆分热点行。
  • 前端怎么感知冲突?答:接口返回版本冲突错误码,前端提示刷新后重试,避免静默覆盖。

04.7.21.1-040 Alembic 的目录结构与初始化流程是什么?

一句话结论

alembic init <dir> 生成 env.py(迁移环境,连接 Engine 与元数据)、versions/(版本脚本目录)、alembic.ini(配置文件)、script.py.mako(脚本模板)。env.py 是核心:配置数据库 URL、加载模型 target_metadata、控制在线/离线迁移模式。

概念解释

  • alembic.ini:脚本路径、日志、数据库 URL;生产常用环境变量覆盖 URL。
  • versions/:每个迁移一个脚本,含 upgrade()/downgrade() 与修订链 down_revision。
  • env.py:连接 SQLAlchemy Engine,对比 metadata 与数据库,执行命令。

原理流程

  1. alembic init 生成骨架文件。
  2. 配置 sqlalchemy.url 或在 env.py 从应用配置注入。
  3. 设置 target_metadata = Base.metadata,供 autogenerate 比对。
  4. 执行 alembic revision/upgrade/downgrade,由 env.py 驱动。

代码或图示

alembic/
├── alembic.ini
├── env.py
├── script.py.mako
└── versions/
    └── 0001_add_users.py

常见误区

  • target_metadata 未指向应用模型:autogenerate 永远生成空迁移。
  • 把模型定义复制进 env.py:应导入应用 Base,避免两处定义漂移。
  • 生产在 alembic.ini 写明文密码:应从环境变量/配置中心注入。

追问

  • 在线与离线模式区别?答:在线模式连接数据库执行 SQL;离线模式生成 SQL 脚本文件交由 DBA 执行。
  • 多个 Base 怎么配置?答:target_metadata 可传合并后的 MetaData 对象,或在 env.py 列出全部模型。
  • 迁移脚本模板能改吗?答:改 script.py.mako,自定义注释与导入。

04.7.21.1-041 revision 与 --autogenerate 流程差异?

一句话结论

alembic revision 生成空白脚本,由人手写 upgrade/downgrade;alembic revision --autogenerate 对比数据库现状与模型 metadata,自动生成表/列/索引/约束变更脚本。autogenerate 是草稿,必须人工审查修正,不能直接上线。

概念解释

  • 对比基准:metadata(模型定义)vs 数据库当前版本。
  • 自动生成基于 Alembic 能检测的差异,存在漏检与误检。
  • 数据迁移、复杂约束、方言特定变更通常需手写。

原理流程

  1. 运行 autogenerate,env.py 连接数据库并加载 metadata。
  2. Alembic 生成差异清单,渲染为 versions/ 下脚本。
  3. 人工审查:补漏检项、修正类型/默认值、编写数据回填。
  4. 提交脚本后在测试库 upgrade 验证,再上生产。

代码或图示

alembic revision --autogenerate -m "add product table"
alembic upgrade head
alembic downgrade -1

常见误区

  • autogenerate 脚本不审查直接执行:类型映射、默认值、索引命名可能不符合预期。
  • 改模型后忘记生成迁移:模型与表结构漂移,环境不一致。
  • 多人同时改模型产生冲突迁移:需合并修订链(见分支合并题)。

追问

  • autogenerate 基于哪个方向比对?答:metadata 为目标,数据库为现状,生成把现状改到目标的 DDL。
  • 空白 revision 什么时候用?答:纯数据迁移、重命名、复杂 DDL、autogenerate 难以表达的变更。
  • 怎么验证脚本?答:先在测试库 upgrade 与 downgrade 往返,检查表结构与数据。

04.7.21.1-042 autogenerate 能检测什么、漏检什么?

一句话结论

可检测:新增/删除表、新增/删除列、列类型与长度变化、索引/唯一约束/外键增删。漏检或不可靠:表/列重命名(可能被识别为删旧加新)、约束/检查约束内容变更、枚举值变化、数据行迁移、部分方言特定索引、默认值函数表达式。

概念解释

  • 重命名风险:autogenerate 看不到「改名」,只能看到旧列消失、新列出现,需手动改为 alter_column 重命名并保留数据。
  • 枚举/类型细节:不同数据库枚举实现差异,自动生成可能不准。
  • 数据回填永远不会自动生成。

原理流程

  1. Alembic 遍历 metadata 表/列/约束,与数据库 reflection 结果比对。
  2. 可结构化对比的项生成操作;语义级变更(改名、内容修改)无法判断。
  3. 人工对照模型与上线意图,补写重命名、数据迁移、约束调整。
  4. 审查清单化:每次生成后核对漏检项。

代码或图示

# 漏检/易错:重命名列需手写
def upgrade():
    op.alter_column("users", "fullname", new_column_name="nickname")

常见误区

  • 依赖自动 diff 判断重命名:删列加新列会导致历史数据丢失。
  • 忽略枚举值新增:枚举类型在不同库实现不同,需显式迁移。
  • 默认值变更被漏掉:server_default 表达式对比可能不完整,要核对脚本。

追问

  • 怎么让 autogenerate 识别重命名?答:脚本里手动写成 rename 操作;自动检测需要额外配置或人工标注(待确认:Alembic 重命名检测选项)。
  • 检查约束变更能自动发现吗?答:多数情况下不可靠,需手写 create_check_constraint 等。
  • 数据迁移放在哪个脚本?答:紧随结构变更的 revision 脚本中,先改结构再回填数据。

04.7.21.1-043 迁移脚本怎么写升级、降级与数据迁移?

一句话结论

每个 revision 脚本含 upgrade() 与 downgrade():结构变更用 op.create_table/op.add_column/op.alter_column 等操作;数据迁移用 op.execute() 或批量 update。降级应可逆还原结构与数据;不可逆的数据销毁要在脚本注释与发布说明中标注。

概念解释

  • op 命名空间提供跨方言操作,底层生成对应 DDL。
  • 数据迁移分:回填新列、清洗脏数据、历史数据转换;大批量回填需分批,避免长事务锁表。
  • 降级脚本是回滚兜底,不是所有变更都能完美逆向。

原理流程

  1. 脚本顶部记录 revision、down_revision、branch_labels。
  2. upgrade():先结构后数据,分批回填。
  3. downgrade():逆序还原,数据回填按需反向处理。
  4. 测试库往返执行 upgrade/downgrade 验证可逆性。

代码或图示

def upgrade():
    op.add_column("orders", op.Column("channel", op.String(20)))
    op.execute("UPDATE orders SET channel = 'web' WHERE channel IS NULL")

def downgrade():
    op.drop_column("orders", "channel")

常见误区

  • 数据迁移一次性更新全表:大事务锁表、主从延迟,应分批提交。
  • 降级脚本直接 DROP 不考虑数据:回滚后历史数据无法恢复。
  • 脚本里写死业务逻辑:迁移应面向结构与数据,复杂转换可在应用兼容期完成。

追问

  • 大批量回填怎么分批?答:按主键区间循环 update,每批提交,控制单事务大小。
  • 迁移期间应用要兼容新旧结构吗?答:需要,滚动部署期间新旧代码共存,字段/表不能一步到位删除。
  • op.execute 写 SQL 要注意方言吗?答:跨库场景优先用 op 操作;原生 SQL 需考虑方言差异。

04.7.21.1-044 线上 DDL 锁表与大表改造怎么避免?

一句话结论

直接 ALTER TABLE 大表可能长时间锁表或大量重建,阻塞读写。生产做法:评估表大小与锁行为,利用数据库在线 DDL 能力(MySQL ALGORITHM=INPLACE/LOCK=NONE、PostgreSQL 在线加列/索引),大表拆分迁移步骤,低峰执行并监控主从延迟;必要时用影子表 + 双写 + 数据回填再切换。

概念解释

  • 锁表风险:重建表类操作(改类型、加大量索引)在老版本 MySQL 上可能元数据锁阻塞。
  • 在线 DDL:允许并发读写的算法与锁模式,但不是所有变更都支持。
  • 影子表方案:建新表,双写过渡,回填历史,校验后切流,旧表延迟下线。

原理流程

  1. 变更前查表行数、引擎、现有索引与数据库版本能力。
  2. 选择在线 DDL 参数或影子表方案。
  3. 低峰执行,监控锁等待、主从延迟、磁盘空间。
  4. 迁移后校验数据一致性,观察一段再清理旧结构。

代码或图示

-- MySQL 在线加索引(评估支持情况)
ALTER TABLE orders ADD INDEX idx_user (user_id), ALGORITHM=INPLACE, LOCK=NONE;

常见误区

  • 大表加列默认值直接全表刷写:老版本 MySQL 可能重建表,需评估版本行为。
  • 迁移不监控主从延迟:大 DDL 复制延迟导致从库落后,切换有风险。
  • 影子表双写不一致:需要对账校验与补偿,不能直接切换。

追问

  • 怎么判断 DDL 是否锁表?答:查数据库文档对该变更的算法/锁模式说明,测试库观察阻塞行为。
  • 加索引为什么要低峰?答:构建索引消耗 IO/CPU,且可能产生锁与延迟。
  • 列删除为什么延后?答:滚动部署期间旧版本代码仍读旧列,先停止写入再在下一版本删除。

04.7.21.1-045 多分支迁移怎么合并修订链?

一句话结论

并行分支各自生成 revision,down_revision 指向同一基分会产生分叉;用 alembic merge 生成合并脚本,把两条分支链接到同一 head。合并后 alembic heads 只应剩一个 head;脚本按依赖顺序在目标库 upgrade,避免分叉导致「找不到 head」。

概念解释

  • 修订链是有向图:每个 revision 有一个或多个 down_revision。
  • 分叉时存在多个 head,upgrade head 无法确定顺序。
  • merge 脚本的 down_revision 列出两个分支 tip,形成汇合节点。

原理流程

  1. 两分支分别提交迁移脚本,产生两个 head。
  2. alembic merge -m "merge branch" head1 head2 生成合并 revision。
  3. 审查合并脚本依赖与顺序,提交版本库。
  4. 目标库 upgrade 到合并 head,链恢复线性。

代码或图示

alembic heads
alembic merge -m "merge feature branches" <rev1> <rev2>
alembic upgrade head

常见误区

  • 直接改两个脚本的 down_revision 手动拼接:容易形成环或错序,应由 merge 生成。
  • 合并后仍留多 head:CI 检查 alembic heads 数量,确保单一 head。
  • 本地链与远程库链不一致:已上线环境的历史脚本不能改写,新变更只能追加。

追问

  • 迁移脚本能改历史已执行的吗?答:已在生产执行的脚本不应修改,避免环境链断裂;修正靠新增脚本。
  • 两个分支改同一列怎么办?答:合并时人工协调变更顺序,避免冲突 DDL。
  • CI 怎么校验迁移健康?答:自动 upgrade/downgrade 往返,检查 head 唯一、脚本可执行。

04.7.21.1-046 什么是零停机迁移的 expand-contract 模式?

一句话结论

expand-contract 把一次破坏性结构变更拆成三段:expand(加新列/新表,新旧结构并存)→ 双写与数据回填(新旧结构同步)→ contract(确认无依赖后删除旧结构)。配合滚动部署,新旧版本应用都能读写,避免改表期间服务中断或字段不兼容。

概念解释

  • expand:新增兼容结构,旧代码不感知,新代码可写新结构。
  • 双写:写入同时更新新旧两侧,异步任务回填历史数据。
  • contract:等所有旧版本实例下线、对账完成,再删旧列/旧表。
  • 典型场景:列改名、表拆分、类型变更、枚举迁移。

原理流程

  1. expand 阶段发布迁移:加新列/新表,应用代码兼容读写。
  2. 双写阶段:新写入同步两侧;离线任务回填历史数据并对账。
  3. 观察期:确认新结构数据完整、旧结构无新写入依赖。
  4. contract 阶段:下一版本删除旧结构,完成迁移。

代码或图示

v1 代码:读旧列,写旧列
v2 代码:读新列(回退旧列兜底),新旧双写
v3 代码:只读新列,停止双写
v4 迁移:删除旧列

常见误区

  • 一步到位改列名:滚动部署期间旧实例读不到新列,直接报错。
  • 双写无对账:两侧不一致,切换后数据缺失。
  • contract 太早:还有旧版本实例或报表/任务读旧列,删除后故障。

追问

  • 双写不一致怎么补偿?答:定时对账任务扫描差异,按规则补齐并告警。
  • 表拆分怎么过渡?答:影子表 + 双写 + 回填,读流量按比例切到新表,最后下线旧表。
  • expand 阶段数据库冗余会不会浪费?答:短期冗余可接受,迁移完成后 contract 回收。

04.7.21.1-047 迁移回滚策略有哪些?哪些变更不可回滚?

一句话结论

可回滚变更:加表/加列可 drop,索引可删除,部分类型变更可逆;不可完美回滚:数据行删除或覆盖、列类型收窄后再放大、枚举值删除、数据清洗后无法复原。策略上优先向前修复(追加新迁移),回滚仅用于结构未落地或可还原场景;发布前明确每个迁移的回滚预案。

概念解释

  • alembic downgrade 是结构回滚工具,不自动恢复被销毁的数据。
  • 生产回滚常受限于:已上线数据无法逆向、新代码已依赖新结构。
  • 回滚演练要在测试库做,确认 downgrade 脚本能跑通。

原理流程

  1. 变更前评估可逆性,标注 downgrade 是否安全。
  2. 结构问题:回退迁移版本或追加修复脚本。
  3. 数据破坏:靠备份/快照恢复,而非 downgrade。
  4. 代码与结构回滚顺序:先回代码兼容旧结构,再回结构。

代码或图示

alembic current
alembic downgrade -1   # 仅结构可逆时使用
alembic upgrade head   # 向前修复

常见误区

  • 假设所有迁移都能 downgrade:数据销毁类变更回滚即丢数据。
  • 生产直接 downgrade 未验证脚本:降级脚本可能有缺陷,二次故障。
  • 新代码已发布才发现结构问题:此时回滚结构会让新代码报错,需先回代码。

追问

  • 备份什么时候做?答:结构性大变更与数据迁移前做快照/备份,保留恢复窗口。
  • 向前修复和回滚怎么选?答:影响面小、代码可兼容时优先追加修复迁移;刚发布且可逆时回滚更快。
  • 回滚后版本链怎么维护?答:回退后再升级要保证脚本链连续,避免分叉。

04.7.21.1-048 迁移与应用版本如何兼容共存?

一句话结论

滚动部署期间新旧版本实例同时运行,数据库结构必须同时兼容两代代码:新增结构先于新代码上线,删除结构晚于旧代码下线。发布顺序为「加结构 → 部署兼容代码 → 双写迁移 → 旧实例退出 → 删结构」,代码变更分小步,避免一次性改表又改代码。

概念解释

  • 兼容性方向:新代码能读旧结构,旧代码能忽略新结构。
  • 字段删除前先确认:接口、报表、定时任务、其他服务都不再引用。
  • 跨服务共享表时,要等所有调用方升级完成再 contract。

原理流程

  1. 先发布只加不改不删的迁移。
  2. 部署兼容层代码:新逻辑读新结构,兜底旧结构。
  3. 滚动替换实例,旧版本逐步退出。
  4. 确认无旧依赖后发布删除旧结构的迁移。

代码或图示

迁移 A:加新列(兼容旧代码)
代码 B:读写新列,旧列兜底
迁移 C:回填与对账
代码 D:停止写旧列
迁移 E:删除旧列

常见误区

  • 结构与代码同一个版本一次发布:灰度期间新旧实例结构假设不一致。
  • 忽略离线任务/数据脚本:它们往往比在线服务更晚升级,仍读旧列。
  • 跨服务表结构直接改:未通知调用方,下游读到新结构报错。

追问

  • 怎么确认旧结构无引用?答:代码搜索 + 日志/慢查询审计 + 下游接口确认,观察一个发布周期。
  • 兼容兜底写在哪?答:数据访问层/序列化层,新字段缺失时回退旧字段映射。
  • 数据库变更能在代码回滚后保留吗?答:能,加结构类变更向前兼容,代码回滚不影响。

04.7.21.1-049 create_async_engine 与 asyncpg 怎么选?

一句话结论

create_async_engine 创建异步 Engine,配合异步驱动使用:PostgreSQL 常用 asyncpg(URL postgresql+asyncpg://),MySQL 常用 aiomysql/asyncmy(待确认:当前推荐异步 MySQL 驱动与成熟度)。异步栈必须全程异步:异步 Engine、AsyncSession、异步驱动,不能混用同步 DBAPI。

概念解释

  • 异步驱动实现非阻塞网络 IO,事件循环不被数据库等待卡住。
  • asyncpg 是 PostgreSQL 高性能异步驱动,协议层优化明显;同步 psycopg2 不能在异步 Engine 下使用。
  • SQLite 异步驱动支持有限,异步场景一般用于网络数据库。

原理流程

  1. URL 指定方言 + 异步驱动:postgresql+asyncpg://。
  2. create_async_engine(url, pool_size=..., ...) 建异步连接池。
  3. async_sessionmaker(engine) 工厂创建 AsyncSession。
  4. 所有查询/提交/回滚/关闭方法都 await。

代码或图示

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker

async_engine = create_async_engine(
    "postgresql+asyncpg://app:***@db:5432/app",
    pool_size=10,
    pool_pre_ping=True,
)
AsyncSessionLocal = async_sessionmaker(async_engine, expire_on_commit=False)

常见误区

  • 异步 Engine 配同步驱动:握手与调用阻塞事件循环,异步收益消失。
  • 池参数照搬同步:异步池同样受 worker 数与数据库连接上限约束。
  • 在异步路由里直接调用同步 ORM 方法:阻塞事件循环,需线程池隔离。

追问

  • asyncpg 比 psycopg2 快在哪?答:非阻塞协议实现 + prepared statement/类型编解码优化(基准需按场景压测)。
  • 异步连接池也有 pre_ping 吗?答:有对应参数,行为与同步池一致(待确认:异步池参数名与默认值)。
  • 一个异步进程开多少连接?答:按事件循环数量与数据库上限估算,通常比同步多进程池总和更集中。

04.7.21.1-050 异步 Session 的使用规范是什么?

一句话结论

AsyncSession 按请求/任务显式创建,用 async with AsyncSessionLocal() as session: 管理生命周期;查询、commit、rollback、close 全部 await;一个 Session 同一时刻只被一个协程使用。懒加载关系在异步模式下不能隐式触发,必须显式预取。

概念解释

  • 异步 Session 不绑定线程局部,隔离靠协程内显式实例化。
  • 关系懒加载需要异步 IO,隐式访问无法 await,因此 selectinload/joinedload 成为必需。
  • 会话工厂 async_sessionmaker 参数与同步 sessionmaker 对应。

原理流程

  1. 请求进入,经工厂创建 AsyncSession。
  2. await session.execute(select(...)) 查询,显式 scalars() 取值。
  3. 变更后 await session.commit(),异常 await session.rollback()。
  4. 退出上下文自动 await close() 归还连接。

代码或图示

async with AsyncSessionLocal() as session:
    stmt = select(User).options(selectinload(User.addresses))
    users = (await session.execute(stmt)).scalars().all()
    await session.commit()

常见误区

  • 把 AsyncSession 存全局/类变量跨请求复用:协程间串状态,生命周期失控。
  • 忘记 await: coroutine 未执行,方法返回协程对象而非结果。
  • 访问未预取关系:异步懒加载抛错或需显式 await session.get(...) 路径处理。

追问

  • 异步 Session 能并发操作吗?答:不能,同一 Session 串行使用;并发查不同数据各建 Session。
  • 异步事务怎么开?答:async with session.begin(): 上下文,退出自动 commit/rollback。
  • 异步栈里怎么调同步遗留服务?答:anyio.to_thread.run_sync 或线程池执行,避免阻塞循环。

04.7.21.1-051 异步应用中遇到阻塞调用怎么兜底?

一句话结论

事件循环里禁止阻塞调用(同步 DBAPI、CPU 密集计算、阻塞 IO),兜底手段是放入线程池/进程池执行(anyio.to_thread、loop.run_in_executor、FastAPI 的 def 路由自动走线程池)。阻塞库无法改造时,在边界隔离并限制池大小,避免事件循环被拖慢。

概念解释

  • 阻塞调用卡住事件循环,所有协程等待,并发能力崩塌。
  • 线程池适合 IO 阻塞,进程池适合 CPU 密集(绕过 GIL)。
  • 第三方库是否异步友好,要查其驱动/客户端实现。

原理流程

  1. 识别同步阻塞点:驱动调用、网络请求、文件处理、重计算。
  2. IO 阻塞:提交线程池,await 结果。
  3. CPU 密集:提交进程池,注意序列化开销。
  4. 池大小与下游连接数匹配,避免无限并发。

代码或图示

import anyio

# 同步阻塞函数放线程池
result = await anyio.to_thread.run_sync(sync_blocking_call, arg)

# FastAPI:def 路由自动进入线程池,async def 运行在事件循环

常见误区

  • 给阻塞函数包一层 async def 但内部仍同步调用:只是语法异步,阻塞照旧。
  • 线程池无上限:阻塞任务堆积,内存与下游连接被打满。
  • CPU 密集放线程池:GIL 限制下提速有限,应考虑进程池或独立服务。

追问

  • 怎么检测事件循环被阻塞?答:监控事件循环延迟(loop lag)、接口 P99 抖动、协程排队指标。
  • 同步 SDK 很多怎么办?答:边界隔离 + 线程池;长期推动换异步客户端或拆成同步服务。
  • 线程池大小怎么定?答:按下游连接数与阻塞类型估算,IO 型可适中放大,CPU 型接近核数。

04.7.21.1-052 批量写入的性能路径有哪些?

一句话结论

批量写入优先走批量 INSERT/UPDATE:ORM 批量映射方法、Core insert() 批量执行、驱动 executemany;分批提交控制事务大小,避免单事务过大。相比逐对象 add + 逐条 INSERT,批量路径减少往返、对象构造与脏检查开销。

概念解释

  • 逐对象路径:每个对象进入 Session 脏检查,flush 时逐条 SQL。
  • 批量路径:数据以字典/映射列表传入,绕过完整 ORM 状态管理,直接生成批量语句。
  • 取舍:批量方法不触发 ORM 事件/关系级联,需要自行处理关联对象。

原理流程

  1. 数据整理为映射列表(字典或模型实例列表)。
  2. 选择批量方法:bulk_insert_mappings、Core insert().values(list)。
  3. 按批次大小分批 execute/commit。
  4. 回填自增主键与关联关系按驱动/方言能力处理。

代码或图示

from sqlalchemy import insert

# Core 批量插入
session.execute(insert(User), [
    {"name": "a", "age": 1},
    {"name": "b", "age": 2},
])
session.commit()

常见误区

  • 几万行一个事务:undo 膨胀、锁持有久、回滚代价大。
  • 批量写入忽略唯一性/外键约束:冲突在批量语句中整体失败,需分批定位。
  • 依赖 ORM 级联:批量方法绕过关系处理,子表需单独批量写入。

追问

  • 批量大小多少合适?答:常见几百到几千行一批,按单事务耗时与锁占用压测确定。
  • 批量插入能拿自增 ID 吗?答:部分方言/驱动支持批量回填,能力有限(待确认:insertmanyvalues 回填行为)。
  • 批量写入要关 autoflush 吗?答:批量场景可关闭,避免每批查询前额外脏检查。

04.7.21.1-053 insertmanyvalues 机制解决什么问题?

一句话结论

insertmanyvalues 是 SQLAlchemy 2.x 针对批量 INSERT 的优化机制:当驱动/方言支持时,把多行 VALUES 合并为一条多行 INSERT(或按驱动能力分批),减少网络往返;在不支持的场景回退到 executemany。它对 Core 批量 insert 与 ORM 批量插入自动生效,无需业务代码切换。

概念解释

  • 旧路径:executemany 可能逐行发语句,往返次数 = 行数。
  • 新路径:多行合并,一次往返携带多行;方言按参数上限拆分批次。
  • 驱动支持程度不同:asyncpg、psycopg 等支持较好,部分老驱动回退。

原理流程

  1. 批量 insert().values(rows) 或 ORM 批量方法传入多行。
  2. 方言判断驱动是否支持 insertmanyvalues 优化。
  3. 支持:构造多行 VALUES,按参数上限分批执行。
  4. 不支持:回退 executemany;执行后按能力回填主键。

代码或图示

# 2.x 批量插入,底层按方言选择 insertmanyvalues
session.execute(
    insert(User),
    [{"name": f"u{i}"} for i in range(1000)],
)

常见误区

  • 以为所有驱动都合并多行:老驱动/方言回退 executemany,性能差异仍在。
  • 单行数据也套批量接口:批量路径固定开销不划算。
  • 忽略参数上限:超长多行语句会被方言分批,业务无需手动拆,但要了解批次行为。

追问

  • 怎么确认走了哪条路径?答:开启引擎日志看 SQL 形态(多行 VALUES 还是逐行),或查方言执行统计(待确认:版本与驱动矩阵)。
  • insertmanyvalues 影响事务大小吗?答:合并往返不等于合并事务,提交批次仍由业务控制。
  • 主键回填在优化路径下可用吗?答:取决于方言与驱动,批量回填能力需实测验证。

04.7.21.1-054 批量插入/更新映射方法怎么取舍?

一句话结论

二者是 Session 级批量方法:bulk_insert_mappings(model, list_of_dicts) 按字典批量插入;bulk_update_mappings(model, list_of_dicts) 按主键批量更新。优点是绕过完整 ORM 状态管理、速度快;代价是不触发对象事件、不做关系级联、不回填完整实体状态。

概念解释

  • 字典键对应模型列名,更新映射必须包含主键。
  • 与普通 add 路径对比:无脏检查、无 identity map 登记(部分模式可保留)。
  • 适合导入/同步/报表回填等纯数据搬运场景。

原理流程

  1. 整理映射列表,插入映射任意字段,更新映射含主键。
  2. 调用对应 bulk 方法生成批量 INSERT/UPDATE。
  3. flush/commit 落库。
  4. 关联对象、ORM 事件钩子需另行处理。

代码或图示

session.bulk_insert_mappings(User, [
    {"name": "a", "age": 1},
    {"name": "b", "age": 2},
])
session.bulk_update_mappings(User, [
    {"id": 1, "age": 20},
])
session.commit()

常见误区

  • 期望触发 after_insert 等钩子:bulk 方法绕过事件,审计/校验逻辑不会执行。
  • 更新映射漏主键:无法定位行,语句报错或误更新。
  • 批量后继续用 Session 内实体:bulk 路径未登记完整对象状态,需重新查询。

追问

  • bulk 方法和 Core insert 选哪个?答:已在 Session 内且模型映射清楚用 bulk;纯数据层批量用 Core insert,更贴近 SQL。
  • 批量插入后能查刚写入的行吗?答:commit 后重新查询;bulk 不保证实体在 identity map 中。
  • 事件钩子缺失怎么补?答:在批量业务层统一写审计/校验,或拆回普通 ORM 路径处理少量关键对象。

04.7.21.1-055 批量更新与 executemany 的性能差异在哪?

一句话结论

批量更新可走 UPDATE ... WHERE id IN (...) 或按行 executemany;前者一条语句更新多行,往返少,后者逐行执行,往返多。数据库侧还需评估索引命中与锁范围;不同驱动对 executemany 的内部优化(多语句/预处理复用)差异较大,需按实际驱动压测。

概念解释

  • executemany:驱动重复执行同一模板,参数替换多行;开销在往返与解析。
  • 多行 UPDATE/CASE:一条语句表达多行不同值,构造复杂。
  • 批量更新锁范围:一次锁多行,需控制批次避免长锁。

原理流程

  1. 按更新模式选语句形态:同值批量用 WHERE id IN;异值逐行或 CASE。
  2. 驱动执行,记录语句次数与耗时。
  3. 分批 commit,观察锁等待与主从延迟。
  4. 对比执行计划:是否命中主键/索引,扫描行数多少。

代码或图示

from sqlalchemy import update

# 同值批量更新
session.execute(
    update(User).where(User.id.in_(ids)),
    {"status": "disabled"},
)

常见误区

  • 大表批量更新无分批:锁与 undo 膨胀,阻塞在线读写。
  • 用 IN 但 id 无索引:全表扫描,更新代价极高。
  • 忽视驱动 executemany 实现:同一接口不同驱动性能差距明显,不能凭经验下结论。

追问

  • 批量更新前要不要锁查询?答:按业务一致性要求;条件更新带状态校验可减少显式锁。
  • 异值批量怎么写更高效?答:CASE WHEN 构造单条 UPDATE,或分批 executemany,按方言与数据量选择。
  • 怎么衡量批量写入收益?答:对比语句条数、往返次数、单批耗时、锁等待与错误率。

04.7.21.1-056 慢查询定位先看执行计划的哪些部分?

一句话结论

EXPLAIN 先看访问类型(type)、实际命中索引(key)、扫描行数(rows)、额外信息(Extra:Using filesort/Using temporary/Using index condition)。全表扫描(type=ALL)、未命中索引、扫描行数巨大、排序/临时表是主要优化信号;再结合慢查询日志的耗时与行数定位问题 SQL。

概念解释

  • type 从优到劣常见:const/ref/range/index/ALL,ALL 最差。
  • key 为 NULL 表示未用索引;rows 是预估扫描行数,越大代价越高。
  • Extra 中 Using filesort/Using temporary 说明排序或分组需额外操作。

原理流程

  1. 慢日志/指标找到耗时 SQL,提取完整语句与参数。
  2. EXPLAIN 查看计划:访问类型、索引、行数、Extra。
  3. 判断问题:缺索引、索引失效、回表过多、排序分组代价。
  4. 改写 SQL 或补索引后再次 EXPLAIN 对比,压测验证。

代码或图示

EXPLAIN
SELECT u.id, u.name, a.city
FROM users u JOIN addresses a ON a.user_id = u.id
WHERE u.active = 1 AND a.city = '昆明'
ORDER BY u.created_at DESC;

常见误区

  • 只看语句执行快慢不看计划:数据量小时代价不明显,上线后恶化。
  • 索引建了但未命中:函数包裹列、隐式类型转换、前导通配符 LIKE 都会使索引失效。
  • 一次加多个索引不评估写入代价:索引越多写入越慢,需权衡。

追问

  • 回表是什么?答:二级索引查到主键后再回主键索引取完整行;覆盖索引可避免回表。
  • Using index 是好还是坏?答:覆盖索引扫描,通常避免回表,是正向信号。
  • 计划是预估的吗?答:是基于统计信息的估算,统计过时可能失真,可更新统计信息后再看。

04.7.21.1-057 怎么判断索引是否被有效使用?

一句话结论

核对 EXPLAIN 的 key 是否为目标索引、type 是否达到 ref/range 以上、rows 是否显著下降;再用慢日志与 performance_schema/pg_stat_user_indexes 观察索引实际命中与使用率。区分「建了索引」和「查询真的用到」,长期未命中的冗余索引应评估删除。

概念解释

  • 索引失效常见原因:对列做函数/运算、隐式类型转换、LIKE 前缀通配、不符合最左前缀。
  • 复合索引顺序:等值在前、范围在后,按查询条件排列。
  • 索引使用率:长期零命中的索引增加写入与存储负担。

原理流程

  1. 对照查询条件设计索引:等值列、排序列、范围列。
  2. EXPLAIN 验证 key/type/rows。
  3. 数据库侧统计索引扫描次数与表访问次数。
  4. 冗余/重复索引合并删除,保留有效索引。

代码或图示

-- 复合索引:(active, city, created_at) 按最左前缀设计
-- 反例:WHERE YEAR(created_at) = 2026 使索引失效

常见误区

  • 每列单独建索引:优化器可能选其一,复合索引更符合组合查询。
  • 忽略最左前缀:跳过前导列,后续列索引无法使用。
  • 只看开发库计划:数据分布不同,生产计划可能改变。

追问

  • 怎么查索引使用率?答:MySQL performance_schema.table_io_waits/sys 视图;PostgreSQL pg_stat_user_indexes 的 idx_scan。
  • 冗余索引怎么识别?答:前缀被另一索引覆盖、列顺序重复的索引组合。
  • 排序字段需要进索引吗?答:避免 filesort 时把排序列纳入复合索引末尾,视查询模式而定。

04.7.21.1-058 应用连接池与数据库 max_connections 怎么换算?

一句话结论

总数据库连接 = 应用实例数 × 每实例 worker/进程数 × 每进程池上限(pool_size + max_overflow),必须小于数据库 max_connections 并预留运维/备份/其他服务余量(建议留 20% 以上)。异步单进程池与多进程同步池算法不同,要按实际部署拓扑逐项相乘,不能只看单进程配置。

概念解释

  • 同步 gunicorn:每个 worker 进程独立 Engine 与池。
  • 异步 uvicorn:单进程内一个异步池,workers 数相乘。
  • 数据库侧 max_connections 还要留给迁移脚本、监控、DBA 会话。

原理流程

  1. 列出部署项:实例数、进程/worker 数、每进程池参数。
  2. 相乘得应用连接峰值估算。
  3. 加其他客户端(定时任务、脚本、报表)连接。
  4. 对照数据库上限,调整池大小或实例数,保留余量。

代码或图示

示例:2 实例 × 4 worker × (pool_size 8 + max_overflow 4) = 96 连接
数据库 max_connections = 120,预留 24 给运维与其他客户端

常见误区

  • 只按单进程池大小算:漏掉 worker 数与实例数,生产连接被打爆。
  • 池开满才考虑数据库上限:峰值叠加时先触发数据库 Too many connections。
  • 忽略异步任务/脚本独立连接:它们常连同一数据库,连接预算需单独计。

追问

  • 连接数超了会怎样?答:数据库拒绝新连接(1040 Too many connections),应用取连接失败。
  • 怎么调池更稳妥?答:先压测单进程实际占用,再按部署规模反推 pool_size,溢出设小。
  • 数据库连接上限怎么查?答:MySQL SHOW VARIABLES LIKE 'max_connections';PostgreSQL SHOW max_connections。

04.7.21.1-059 echo/echo_pool 日志怎么分级?生产为什么必须关?

一句话结论

echo=True 打印 SQL 语句与参数,echo_pool=True 打印连接池借还事件;可分别设为 False 或用日志级别控制。生产开启会泄露 SQL/参数到日志、增加 IO 与序列化开销,高并发下明显拖慢服务;生产用慢查询日志与指标替代全量 SQL 打印。

概念解释

  • SQLAlchemy 日志走 sqlalchemy.engine、sqlalchemy.pool 等 logger。
  • echo 等价于引擎级日志开关,结构化日志场景建议直接配 logging 级别。
  • 参数可能含敏感信息(手机号、证件号),全量打印有合规风险。

原理流程

  1. 开发调试:echo=True 或 engine logger 设 INFO 查看 SQL。
  2. 测试/预发:按需开 pool 日志排查连接问题。
  3. 生产:关闭 echo,采集慢查询与执行计数指标。
  4. 敏感参数在日志层脱敏,不依赖 ORM 打印。

代码或图示

engine = create_engine(url, echo=False, echo_pool=False)

import logging
logging.getLogger("sqlalchemy.engine").setLevel(logging.WARNING)
logging.getLogger("sqlalchemy.pool").setLevel(logging.WARNING)

常见误区

  • 生产临时 echo=True 后忘记关:日志暴涨、敏感信息外泄。
  • 用全量 SQL 日志做性能监控:量大成本高,应靠慢日志与聚合指标。
  • 把 pool 日志当业务日志:连接借还事件频繁,容易淹没关键信息。

追问

  • 怎么只看慢 SQL?答:数据库慢查询日志按阈值捕获;应用层记录执行时长超阈值的语句。
  • 日志里能看到绑定参数吗?答:echo 开启时参数随 SQL 输出,生产需脱敏。
  • 排查连接问题临时开什么?答:echo_pool 查看 checkout/checkin 与回收事件,事后关闭。

04.7.21.1-060 生产环境 Session 怎么绑定请求并回收?

一句话结论

Web 场景每个请求创建独立 Session,请求结束(无论正常返回还是异常)必须 close() 归还连接;用中间件/依赖注入管理生命周期,Session 不存全局、不跨请求复用。后台任务各自建 Session;回收路径要覆盖异常分支,并监控 Session 数、连接占用与事务时长。

概念解释

  • 请求级 Session 绑定:进入请求创建,退出请求关闭;异常时 rollback 再 close。
  • 框架集成:Flask/SQLAlchemy 扩展、FastAPI 依赖、Django ORM 各有约定,核心都是请求边界释放。
  • 长任务/定时任务:函数内建 Session,任务结束关闭,避免驻留。

原理流程

  1. 请求进入,工厂创建 Session 并存入请求上下文/依赖。
  2. 业务代码通过注入获取 Session 执行读写。
  3. 响应返回前:正常 commit;异常 rollback。
  4. 退出请求统一 close(),连接归还池;任务/线程同理。

代码或图示

# FastAPI 依赖:请求级 Session
from fastapi import Depends

def get_session():
    session = SessionLocal()
    try:
        yield session
        session.commit()
    except Exception:
        session.rollback()
        raise
    finally:
        session.close()

常见误区

  • 异常分支跳过 close:连接泄漏,池逐步耗尽。
  • 复用请求 Session 给后台线程:线程生命周期长于请求,连接被长期占用。
  • Session 数无监控:泄漏后只能靠重启恢复,应加实例化/关闭计数与连接占用告警。

追问

  • 多步跨服务调用时 Session 保持多久?答:只覆盖本地数据库事务边界,远程调用移出事务。
  • 怎么验证 Session 都被回收?答:压测后看 checkedout 连接回落,对比 Session 创建/关闭计数。
  • 框架自带 Session 管理还需要手写 close 吗?答:扩展负责绑定与移除,但要确认异常路径也触发 remove/close。

本节高频题

  • Engine 与连接池参数(001、002、003、004、005)
  • 连接池耗尽与泄漏排查(006、007、058)
  • Session 生命周期与 identity map(009、010)
  • scoped_session 线程/协程隔离(011、012)
  • expire_on_commit 与 autoflush(014、015)
  • 1.x/2.x 写法迁移(016、017、018、019)
  • 加载策略与 N+1(021、022、023、024、029、030)
  • 隔离级别、死锁与重试(031、032、033)
  • savepoint 与长事务(034、035、036)
  • 乐观锁与悲观锁(037、038、039)
  • Alembic 迁移与 autogenerate 局限(040、041、042、043、045)
  • 零停机与版本兼容(046、047、048)
  • 异步引擎与 AsyncSession(049、050、051)
  • 批量写入与执行计划(052、053、055、056、057)