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_nestedsavepoint、隔离级别、死锁重试、乐观锁版本列 - 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。
原理流程
- 调用
create_engine(url, **kwargs)解析 URL 与驱动名。 - 根据
poolclass(默认 QueuePool)创建连接池,pool_size决定常驻连接数。 - 首次取连接时才真正建立 TCP 与数据库会话,池不预热。
- 应用退出时由进程结束回收连接,一般不显式
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 数 × 总容量)再留余量。
原理流程
- 取连接时优先复用池中空闲连接。
- 空闲不足且当前连接数 <
pool_size + max_overflow,新建连接。 - 达到上限后进入等待队列,等待
pool_timeout秒。 - 等待超时抛出
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缩短空闲周期。
原理流程
- 从池中取出连接后,先向数据库发送 ping 探测。
- 探测成功:连接可用,交给 Session 使用。
- 探测失败:该连接被判定失效,关闭并从池移除,重新创建一个可用连接再交付。
- 新连接本身的握手失败会向上抛出 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或 PostgreSQLconnection 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表示不回收(默认行为的一部分,待确认:各版本默认值差异),生产建议显式设置。
原理流程
- 连接创建时记录时间戳。
- 连接归还入池后,若存活时间超过
pool_recycle,下次取出时判定过期。 - 过期连接关闭重建;未过期则直接复用。
- 配合
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 占住,新请求排队。
- 超时错误本身不区分「池太小」和「连接被泄漏」,需要结合池统计与慢查询日志判断。
原理流程
- 请求取连接,池无空闲且已达
pool_size + max_overflow。 - 进入等待队列,等待已借出连接归还。
- 等待超过
pool_timeout秒,抛出TimeoutError。 - 上层捕获后返回错误或重试;若根因是慢 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,与应用池耗尽互为因果。
原理流程
- 采集池指标:
pool.size()、pool.checkedin()、pool.checkedout()、等待队列长度。 - 查数据库当前会话:MySQL
SHOW PROCESSLIST/information_schema.processlist,PostgreSQLpg_stat_activity,看状态、事务时长、当前 SQL。 - 核对每个占用连接对应的应用请求/任务,定位慢查询或未提交事务。
- 检查代码路径:是否有 Session 脱离上下文管理器、异常分支未 close。
- 评估容量: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()归还连接。
原理流程
- 周期性记录
pool.checkedout()数值,观察是否只涨不跌。 - 审查代码:搜索直接
Session(、sessionmaker()调用,确认是否都在with块内。 - 检查异常分支:
try/except中是否有路径跳过close()。 - 必要时监听
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:每个线程持有自己的连接,跨线程不复用。
原理流程
create_engine(..., poolclass=...)指定池类。- 池类实现
connect/get/put与计数逻辑。 - QueuePool 在多线程间安全排队;NullPool 直接调用 DBAPI 新建。
- 池行为随池类不同,
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(可配置过期)。
原理流程
- 创建 Session,尚未取数据库连接。
- 首次查询/写入时从 Engine 借连接,开启事务(2.x 显式或自动开始)。
- 增删改查登记到 Session,变更在 flush 时落库。
commit()提交事务并按配置过期对象;rollback()回滚并清理脏状态。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。
原理流程
- 查询返回行后,先按主键查 identity map。
- 命中:直接返回已有实例;未命中:构造新实例放入 map。
- 对象属性修改由 Session 监听,加入脏集合。
- 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 归还、不残留到下一个请求。
原理流程
session_factory = sessionmaker(engine)后包一层ScopedSession(session_factory)。- 首次在某线程访问代理对象,工厂创建 Session 存入线程局部槽。
- 同线程后续访问取出同一 Session;新线程走工厂新建。
- 请求结束
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与异步驱动。
原理流程
- 协程 A 创建/获取 Session,写入线程局部槽。
- 事件循环切到协程 B,B 访问 scoped_session 拿到同一个 Session。
- B 的 commit/rollback 作用于 A 未完成的事务,identity map 互相污染。
- 正确做法:每协程内
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 后实例属性过期,下次访问重新查库。
原理流程
- 调用
sessionmaker(bind=engine, ...)生成工厂类。 - 每次调用工厂按参数实例化新 Session。
- 参数随 Session 实例固化,运行中可改单个 Session 属性。
- 工厂复用避免重复传参,保证全站 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:保留内存中的属性值;风险是后续若有并发修改,内存值可能不是数据库最新值。
原理流程
- commit 提交事务,Session 内对象按配置处理。
True:所有属性置为 expired,下次访问查询数据库。False:属性保留,直接读内存快照。- 对象被其他事务修改时,
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 顺序。
原理流程
- 对象 add/修改,进入 pending/dirty 集合。
- 执行查询时若
autoflush=True,先 flush 脏集合再发 SELECT。 autoflush=False时查询直接发 SQL,脏变更留在 Session。- 显式
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()。
原理流程
- 用
select(...)构造语句对象,可叠加where/order_by/limit/options。 session.execute(stmt)执行并返回Result。- 按标量、元组或映射方式取值。
- 兼容层
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.xexecute(select(User, Address))同样返回元组,但需通过result.all()或mappings()取。 - 2.x 对 ORM 实体查询推荐
scalars()解包实体,避免拿到(User,)单元素元组。
原理流程
- 构造
select语句。 session.execute绑定当前 Session 与事务执行。Result提供多种取值视图:标量、标量列表、映射、行元组。- 异常场景:
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]];自引用/前向引用用字符串注解。
原理流程
- 定义
Base = DeclarativeBase。 - 模型类继承 Base,类属性写
Mapped[...]注解。 mapped_column()指定主键、默认值、唯一、索引、外键等。- 关系属性用
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/隐式提交的代码路径。
原理流程
- Session 创建时不借连接、不开事务。
- 首次查询/写入触发连接获取并开启事务。
- 变更在事务内累积,flush 生成 SQL。
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 本身。
原理流程
- 评估场景:实体关系多、事务内对象变更多 → ORM。
- 批量聚合、大结果集、报表 → Core 表达式或
text()。 - 混合时以 Core 语句
execute取值,ORM 负责写路径。 - 重查询必须看执行计划,避免 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))。
原理流程
- 主查询取出主对象列表。
- 按策略决定关联对象的取数时机与 SQL 形态。
- 立即加载策略在主查询前后追加 JOIN/IN/子查询。
- 懒加载在访问属性时触发额外查询。
代码或图示
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 触发点:序列化层遍历主对象逐个访问关系属性;模板/序列化器常隐式触发。
- 根因是懒加载按对象粒度取数,而非按集合批量取。
原理流程
- 查询主对象列表:SQL 1。
- 遍历每个对象,访问
user.addresses。 - 每个对象触发一条按
user_id查地址的 SQL:SQL 2…N+1。 - 总往返数与列表长度线性相关,慢查询日志中出现大量相同形态 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 会形成笛卡尔积,应避免。
原理流程
- 主表与关联表 JOIN,SELECT 两侧列。
- 结果行在
Result中按实体拆分。 - identity map 合并重复主对象,回填关系集合。
- 一对多膨胀行越多,网络传输与内存构造成本越高。
代码或图示
# 一对一: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 行膨胀,主表与关联表分别取数。
原理流程
- 执行主查询,得到主对象主键列表。
- 构造
SELECT ... WHERE user_id IN (...)批量查关联。 - 按外键分组,回填到对应主对象的关系集合。
- 嵌套关系可链式
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 可能较长,需看执行计划。
原理流程
- 主查询生成子查询语句。
- 关联表 JOIN 该子查询,批量取关联行。
- 结果按外键回填主对象关系。
- 主查询条件变更时子查询自动跟随,无需重复维护过滤逻辑。
代码或图示
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[...]]。
原理流程
- 定义双向关系,两侧用
back_populates对齐属性名。 - 查询一侧时按该侧策略加载;关联对象的反向引用由 ORM 自动设置。
- 反向属性未在本次预取范围内,访问时触发懒加载。
- 序列化路径遍历到哪一侧,就要保证该侧关系已预取。
代码或图示
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 语句。
原理流程
- 访问关系属性拿到动态查询器,不含数据。
- 叠加过滤/排序/分页条件。
- 执行方法触发 SQL,返回子集。
- 新增对象加入关系时不会立即进入已有动态查询结果,需重新查询。
代码或图示
# 模型定义
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」,但语义一个静默跳过、一个报错拦截。
原理流程
- 模型或查询选项指定
noload/raise。 - 访问关系属性时按策略处理:noload 返回空,raise 抛
LazyLoadError。 - 必要时在查询层用
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()/去重处理。
原理流程
select(User).join(Address).where(Address.city == "昆明")构造过滤查询。.options(contains_eager(User.addresses))标记关联列回填路径。- 执行后结果按实体拆分,回填关系集合。
- 未匹配过滤条件的主对象不出现在结果中。
代码或图示
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 语句计数指标。
原理流程
- 开发/测试环境开启引擎日志,复现接口请求。
- 汇总 SQL:统计模板出现次数、总耗时、单次最大耗时。
- 重复单查 → 定位对应关系属性,改为 selectinload/joinedload。
- 长 SQL → 数据库侧
EXPLAIN分析 type/key/rows,补索引或改写。 - 修复后压测对比 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 只透传,不自己实现。
原理流程
- Engine 创建时指定默认
isolation_level。 - 借连接时把级别传给 DBAPI/数据库会话。
- 单条语句/连接可用
execution_options覆盖。 - 业务按一致性要求选级别,避免全局拔高到串行化。
代码或图示
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 与加锁顺序,再统一事务内的加锁/更新顺序并缩短事务。
概念解释
- 四个必要条件:互斥、持有并等待、不可剥夺、循环等待;破坏循环等待(统一顺序)最常见。
- 典型场景:多事务以不同顺序更新同一批行;外键检查、唯一索引插入间隙也可能加锁。
- 死锁与锁等待不同:锁等待是排队,超时才报错;死锁是循环,检测后立即回滚一方。
原理流程
- 事务 A 锁行 1,事务 B 锁行 2。
- A 等待行 2,B 等待行 1,形成循环。
- 数据库死锁检测器发现循环,选择代价较小的事务回滚。
- 被回滚方收到死锁错误(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)、连接级瞬时故障。
- 不可盲目重试:业务校验失败、唯一约束冲突、参数错误不应重试。
- 幂等手段:唯一业务单号、乐观锁版本、去重表、先查后写的条件更新。
原理流程
- 捕获数据库异常,判断是否属于可重试类别。
- 事务已被回滚(死锁)或需先
rollback()清理 Session 状态。 - 按退避间隔重试,次数上限内重新执行业务。
- 超过次数上报告警;每次重试基于同一幂等键,结果可重复。
代码或图示
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 内。
原理流程
- 外层事务开启。
begin_nested()执行SAVEPOINT sp_xxx。- 子块内异常:回滚到该 savepoint,外层事务状态保留。
- 子块正常结束: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 与
SAVEPOINTSQL 对应吗?答:对应,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 上重试业务。
原理流程
- 操作抛异常,事务进入失败状态。
- 调用
rollback():向数据库发回滚,Session 清理脏集合与快照。 - 连接仍健康:Session 可开启新事务继续。
- 连接已断:关闭 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,表膨胀。
- 连接被长事务占住不归还,池容量被快速耗尽。
原理流程
- 事务开启后执行远程 HTTP、发 MQ、等待用户输入等慢操作。
- 期间行锁与快照一直持有,其他事务排队。
- 数据库 undo/版本链无法回收,表与索引膨胀。
- 事务结束后锁释放,但积压请求与清理压力滞后释放。
代码或图示
# 反例:事务内远程调用
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 时加锁。
- 版本列可以是整数递增,也可用时间戳/更新哈希。
原理流程
- 读取对象,得到当前版本号
v。 - 业务修改后提交,ORM 生成
UPDATE ... SET version=v+1 WHERE id=? AND version=v。 - 影响行数 1:提交成功;影响行数 0:并发修改已发生。
- 抛
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选项控制冲突行为(方言支持不同)。 - 锁在事务结束时释放,必须在同一事务内完成后续更新。
原理流程
- 事务内
select(...).with_for_update()读取并锁定目标行。 - 其他事务尝试锁同一行时阻塞或按选项跳过/立即报错。
- 当前事务完成校验与更新。
- 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/重新查询)→ 判断业务可否自动合并(纯增量字段可合并,覆盖型字段需人工或提示)→ 在新快照上重放业务 → 再次提交。冲突次数应埋点监控;持续冲突说明并发模型或锁策略需要调整。
概念解释
- 可合并场景:计数追加、状态机单向流转、独立字段修改。
- 不可自动合并:金额覆盖、用户选择被后写覆盖,需提示冲突或人工处理。
- 重试必须短链路,避免长时间占用资源。
原理流程
- 捕获
StaleDataError,当前事务回滚。 - 重新读取最新版本与字段。
- 按业务规则合并:保留双方合法变更,或放弃本次修改。
- 在新版本上重放操作并提交;记录冲突次数与字段。
代码或图示
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 与数据库,执行命令。
原理流程
alembic init生成骨架文件。- 配置
sqlalchemy.url或在env.py从应用配置注入。 - 设置
target_metadata = Base.metadata,供 autogenerate 比对。 - 执行
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 能检测的差异,存在漏检与误检。
- 数据迁移、复杂约束、方言特定变更通常需手写。
原理流程
- 运行 autogenerate,env.py 连接数据库并加载 metadata。
- Alembic 生成差异清单,渲染为
versions/下脚本。 - 人工审查:补漏检项、修正类型/默认值、编写数据回填。
- 提交脚本后在测试库 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重命名并保留数据。 - 枚举/类型细节:不同数据库枚举实现差异,自动生成可能不准。
- 数据回填永远不会自动生成。
原理流程
- Alembic 遍历 metadata 表/列/约束,与数据库 reflection 结果比对。
- 可结构化对比的项生成操作;语义级变更(改名、内容修改)无法判断。
- 人工对照模型与上线意图,补写重命名、数据迁移、约束调整。
- 审查清单化:每次生成后核对漏检项。
代码或图示
# 漏检/易错:重命名列需手写
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。- 数据迁移分:回填新列、清洗脏数据、历史数据转换;大批量回填需分批,避免长事务锁表。
- 降级脚本是回滚兜底,不是所有变更都能完美逆向。
原理流程
- 脚本顶部记录
revision、down_revision、branch_labels。 upgrade():先结构后数据,分批回填。downgrade():逆序还原,数据回填按需反向处理。- 测试库往返执行 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:允许并发读写的算法与锁模式,但不是所有变更都支持。
- 影子表方案:建新表,双写过渡,回填历史,校验后切流,旧表延迟下线。
原理流程
- 变更前查表行数、引擎、现有索引与数据库版本能力。
- 选择在线 DDL 参数或影子表方案。
- 低峰执行,监控锁等待、主从延迟、磁盘空间。
- 迁移后校验数据一致性,观察一段再清理旧结构。
代码或图示
-- 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,形成汇合节点。
原理流程
- 两分支分别提交迁移脚本,产生两个 head。
alembic merge -m "merge branch" head1 head2生成合并 revision。- 审查合并脚本依赖与顺序,提交版本库。
- 目标库 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:等所有旧版本实例下线、对账完成,再删旧列/旧表。
- 典型场景:列改名、表拆分、类型变更、枚举迁移。
原理流程
- expand 阶段发布迁移:加新列/新表,应用代码兼容读写。
- 双写阶段:新写入同步两侧;离线任务回填历史数据并对账。
- 观察期:确认新结构数据完整、旧结构无新写入依赖。
- contract 阶段:下一版本删除旧结构,完成迁移。
代码或图示
v1 代码:读旧列,写旧列
v2 代码:读新列(回退旧列兜底),新旧双写
v3 代码:只读新列,停止双写
v4 迁移:删除旧列
常见误区
- 一步到位改列名:滚动部署期间旧实例读不到新列,直接报错。
- 双写无对账:两侧不一致,切换后数据缺失。
- contract 太早:还有旧版本实例或报表/任务读旧列,删除后故障。
追问
- 双写不一致怎么补偿?答:定时对账任务扫描差异,按规则补齐并告警。
- 表拆分怎么过渡?答:影子表 + 双写 + 回填,读流量按比例切到新表,最后下线旧表。
- expand 阶段数据库冗余会不会浪费?答:短期冗余可接受,迁移完成后 contract 回收。
04.7.21.1-047 迁移回滚策略有哪些?哪些变更不可回滚?
一句话结论
可回滚变更:加表/加列可 drop,索引可删除,部分类型变更可逆;不可完美回滚:数据行删除或覆盖、列类型收窄后再放大、枚举值删除、数据清洗后无法复原。策略上优先向前修复(追加新迁移),回滚仅用于结构未落地或可还原场景;发布前明确每个迁移的回滚预案。
概念解释
alembic downgrade是结构回滚工具,不自动恢复被销毁的数据。- 生产回滚常受限于:已上线数据无法逆向、新代码已依赖新结构。
- 回滚演练要在测试库做,确认 downgrade 脚本能跑通。
原理流程
- 变更前评估可逆性,标注 downgrade 是否安全。
- 结构问题:回退迁移版本或追加修复脚本。
- 数据破坏:靠备份/快照恢复,而非 downgrade。
- 代码与结构回滚顺序:先回代码兼容旧结构,再回结构。
代码或图示
alembic current
alembic downgrade -1 # 仅结构可逆时使用
alembic upgrade head # 向前修复
常见误区
- 假设所有迁移都能 downgrade:数据销毁类变更回滚即丢数据。
- 生产直接 downgrade 未验证脚本:降级脚本可能有缺陷,二次故障。
- 新代码已发布才发现结构问题:此时回滚结构会让新代码报错,需先回代码。
追问
- 备份什么时候做?答:结构性大变更与数据迁移前做快照/备份,保留恢复窗口。
- 向前修复和回滚怎么选?答:影响面小、代码可兼容时优先追加修复迁移;刚发布且可逆时回滚更快。
- 回滚后版本链怎么维护?答:回退后再升级要保证脚本链连续,避免分叉。
04.7.21.1-048 迁移与应用版本如何兼容共存?
一句话结论
滚动部署期间新旧版本实例同时运行,数据库结构必须同时兼容两代代码:新增结构先于新代码上线,删除结构晚于旧代码下线。发布顺序为「加结构 → 部署兼容代码 → 双写迁移 → 旧实例退出 → 删结构」,代码变更分小步,避免一次性改表又改代码。
概念解释
- 兼容性方向:新代码能读旧结构,旧代码能忽略新结构。
- 字段删除前先确认:接口、报表、定时任务、其他服务都不再引用。
- 跨服务共享表时,要等所有调用方升级完成再 contract。
原理流程
- 先发布只加不改不删的迁移。
- 部署兼容层代码:新逻辑读新结构,兜底旧结构。
- 滚动替换实例,旧版本逐步退出。
- 确认无旧依赖后发布删除旧结构的迁移。
代码或图示
迁移 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 异步驱动支持有限,异步场景一般用于网络数据库。
原理流程
- URL 指定方言 + 异步驱动:
postgresql+asyncpg://。 create_async_engine(url, pool_size=..., ...)建异步连接池。async_sessionmaker(engine)工厂创建AsyncSession。- 所有查询/提交/回滚/关闭方法都
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对应。
原理流程
- 请求进入,经工厂创建 AsyncSession。
await session.execute(select(...))查询,显式scalars()取值。- 变更后
await session.commit(),异常await session.rollback()。 - 退出上下文自动
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)。
- 第三方库是否异步友好,要查其驱动/客户端实现。
原理流程
- 识别同步阻塞点:驱动调用、网络请求、文件处理、重计算。
- IO 阻塞:提交线程池,await 结果。
- CPU 密集:提交进程池,注意序列化开销。
- 池大小与下游连接数匹配,避免无限并发。
代码或图示
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 事件/关系级联,需要自行处理关联对象。
原理流程
- 数据整理为映射列表(字典或模型实例列表)。
- 选择批量方法:
bulk_insert_mappings、Coreinsert().values(list)。 - 按批次大小分批 execute/commit。
- 回填自增主键与关联关系按驱动/方言能力处理。
代码或图示
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 等支持较好,部分老驱动回退。
原理流程
- 批量
insert().values(rows)或 ORM 批量方法传入多行。 - 方言判断驱动是否支持 insertmanyvalues 优化。
- 支持:构造多行 VALUES,按参数上限分批执行。
- 不支持:回退 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 登记(部分模式可保留)。 - 适合导入/同步/报表回填等纯数据搬运场景。
原理流程
- 整理映射列表,插入映射任意字段,更新映射含主键。
- 调用对应 bulk 方法生成批量 INSERT/UPDATE。
- flush/commit 落库。
- 关联对象、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:一条语句表达多行不同值,构造复杂。
- 批量更新锁范围:一次锁多行,需控制批次避免长锁。
原理流程
- 按更新模式选语句形态:同值批量用
WHERE id IN;异值逐行或 CASE。 - 驱动执行,记录语句次数与耗时。
- 分批 commit,观察锁等待与主从延迟。
- 对比执行计划:是否命中主键/索引,扫描行数多少。
代码或图示
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说明排序或分组需额外操作。
原理流程
- 慢日志/指标找到耗时 SQL,提取完整语句与参数。
EXPLAIN查看计划:访问类型、索引、行数、Extra。- 判断问题:缺索引、索引失效、回表过多、排序分组代价。
- 改写 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 前缀通配、不符合最左前缀。
- 复合索引顺序:等值在前、范围在后,按查询条件排列。
- 索引使用率:长期零命中的索引增加写入与存储负担。
原理流程
- 对照查询条件设计索引:等值列、排序列、范围列。
EXPLAIN验证 key/type/rows。- 数据库侧统计索引扫描次数与表访问次数。
- 冗余/重复索引合并删除,保留有效索引。
代码或图示
-- 复合索引:(active, city, created_at) 按最左前缀设计
-- 反例:WHERE YEAR(created_at) = 2026 使索引失效
常见误区
- 每列单独建索引:优化器可能选其一,复合索引更符合组合查询。
- 忽略最左前缀:跳过前导列,后续列索引无法使用。
- 只看开发库计划:数据分布不同,生产计划可能改变。
追问
- 怎么查索引使用率?答:MySQL
performance_schema.table_io_waits/sys 视图;PostgreSQLpg_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 会话。
原理流程
- 列出部署项:实例数、进程/worker 数、每进程池参数。
- 相乘得应用连接峰值估算。
- 加其他客户端(定时任务、脚本、报表)连接。
- 对照数据库上限,调整池大小或实例数,保留余量。
代码或图示
示例: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';PostgreSQLSHOW 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 级别。- 参数可能含敏感信息(手机号、证件号),全量打印有合规风险。
原理流程
- 开发调试:
echo=True或 engine logger 设 INFO 查看 SQL。 - 测试/预发:按需开 pool 日志排查连接问题。
- 生产:关闭 echo,采集慢查询与执行计数指标。
- 敏感参数在日志层脱敏,不依赖 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,任务结束关闭,避免驻留。
原理流程
- 请求进入,工厂创建 Session 并存入请求上下文/依赖。
- 业务代码通过注入获取 Session 执行读写。
- 响应返回前:正常 commit;异常 rollback。
- 退出请求统一
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)