外观
29 - SQL不慢却拿不到连接:连接池、ORM查询与数据库迁移
摘要
SQL很快,接口却超时,原因可能在获取连接之前。本文用真实SQLAlchemy与Alembic实验,拆开连接持有、N+1查询和升级兼容性,适合已有事务基础、希望定位数据库问题的读者。
已实测:CPython 3.13.7、SQLAlchemy 2.1.6、Alembic 1.20.1、SQLite 3.50.4,Windows临时文件。更多执行结果见工程实践与验收手册。PostgreSQL、psycopg、网络断连和部署迁移为待验证延伸。本地成功不能证明跨库行为一致。
1. 场景:把池上限翻倍,等待反而更严重
这是可复现的教学场景。接口的 SQL 日志显示执行很快,但请求总耗时持续增长。第一次修改把连接池容量翻倍,短时间更多请求拿到连接,随后数据库出现更多同时执行的工作,接口依然慢。一个合理候选是连接泄漏,另一个是连接持有时间变长;仅凭总延迟无法判断二者。
connection pool(连接池)复用已经建立的连接,借用者取得连接后拥有一段使用权,使用完要归还。池容量限制同时持有数量;等待超时表示本轮在规定时间内没有借到连接,并不证明数据库拒绝登录,也不证明 SQL 执行超时。SQLAlchemy 的连接池文档分别描述 checkout、checkin 与池配置。
先画两条线:连接从checkout到checkin被谁持有,实际SQL执行了几次、每次做了多少工作。请求总耗时同时受两条线影响,SQL日志很短并不能证明连接早已归还。沿等待连接、执行、消费结果、退出事务、归还五段追状态,才知道该缩短持有还是减少查询。
下面故意把问题拆开验证:一个池槽位证明资源未归还时第二借用者进不来;两个父对象证明属性访问会增加真实SELECT次数。输入、目标结果不变,仅改变归还位置或加载策略。池容量只是第一条线的准入边界,不能消除第二条线的数据库成本,更不能替代迁移后的数据兼容检查。
本篇接着事务与连接生命周期往前走:不再只说应该关闭,而是观察池的真实借出数;接着性能测量往下拆,把查询次数与单条计划分开测量。
2. Engine、Connection与Session各管什么
Engine(引擎)组织驱动、方言与连接池,通常是应用生命周期内共享的基础设施。Connection(连接包装)对应一次实际借用;关闭它在池配置下通常是归还底层连接,而非每次关闭服务器会话。Session(ORM会话)管理对象状态和事务工作,不是 HTTP 会话,也不应按“一个全局对象够用”来设计并发访问。
ORM(Object-Relational Mapping,对象关系映射)把表记录映射为对象,减少手写常规数据访问代码。映射不会取消数据库约束,不会让属性读取都在内存完成,也不会消除事务设计。Session 的 identity map(身份映射)让同一会话内同一数据库身份对应已有对象;它可以减少重复装载,也可能掩盖实验里的真实查询次数。
因此每轮 N+1 对照创建新 Session。否则第一轮已经加载关系,第二轮访问命中会话状态,得出“没有额外查询”的错误结论。测试数据也要有至少两个父记录和不同子记录数,不能只使用一个对象,恰好把额外查询误当正常固定成本。
应用一般让请求建立自己的会话边界,明确成功提交与失败回滚,在 finally 或上下文协议里释放。不要让依赖注入提供的 Session 被后台任务继续保存,请求结束后资源所有权已经变化。后台任务应接收稳定业务标识,再建立自己的短会话,参见后台队列。
图表将在阅读到此处时加载…
图中的请求退出不自动保证最后两个步骤已经发生。需要检查代码是否把惰性结果带出了资源边界,是否在异常分支跳过关闭,以及后台工作是否借用了请求会话。
3. 池耗尽实验:只有一个槽位
独立脚本显式指定 QueuePool,pool_size=1、max_overflow=0、pool_timeout=0.05。这些是本实验配置,不能当成 SQLAlchemy 或任意驱动的统一默认。第一条连接被持有后借出数为一,第二次获取抛池等待异常;关闭第一条后借出数为零,再执行 SELECT 1 成功。
运行前可以预测:第一借用者尚未close,第二借用会失败;如果问题确实只是这个槽位被占用,归还后同一引擎的SELECT 1应恢复。主例按下表给出状态证据,而非只打印一个超时名字:
| 动作 | 借出数 | 本步结论 |
|---|---|---|
held=engine.connect() | 1 | 唯一池槽已被持有 |
| 再次engine.connect() | 仍由held占用 | 等待异常符合无overflow的显式配置 |
held.close() | 0 | 资源已归还,随后SELECT 1成功 |
恢复支持“本次失败发生在借用边界”,反证数据库完全不可用;它没有证明生产连接泄漏,因为教学代码本来就刻意持有held。要迁移到真实请求,还需关联持有者、持续时间与负载下降后的归还行为。
真实排障还应看持有时长分布与当前持有者。活跃借出数等于上限可能只是正常高负载;若负载下降后一直不归还,更支持泄漏或悬挂工作。若归还持续发生,但每次持有很长,则检查事务内外部调用、慢结果消费、锁等待。wait timeout 不是这些原因的同义词。
取消请求也要走完整关闭路径。协程等待结束并不必然停止驱动在线程中的工作;取消与归还的关系必须按驱动实现确认。不要在仍有 SQL 执行时强行把连接塞回池,后一个借用者可能继承未知状态。遇到连接失效时,还要检查池是否丢弃该物理连接,而非复用不可用对象。
4. N+1:属性读取触发了查询
先预测同一结果[(1,2),(2,1)]需要几条查询。N+1指一次加载N个父对象,再逐个访问关系时追加N次查询。新Session中执行父查询得到两个Parent;第一次读parent.children触发一条子查询,第二个父对象再触发一条,总数为三。SQL没有写在属性访问旁,不代表没有执行。
现在只给父查询增加selectinload,以父标识集合预取关联对象。父查询一条、子集合查询一条,后续len(parent.children)使用已经装载的关系,总数为二。两轮都新建Session、使用同样数据,真实before_cursor_execute计数与结果相等断言一起检验变化:少了一次往返,但没有用缓存热状态掩盖额外查询。
这不是宣称预取总比懒加载快。若只用极少父对象的关系,提前加载全部数据可能多读;若关联很大,批量 IN 查询也有参数数目和内存代价。joinedload(连接预取)可能减少往返,却放大结果行数和重复父字段;选择策略前要考虑基数、分页与实际访问路径。
把同一属性访问藏在序列化器里尤其容易忽略:路由代码没有显式 SQL,JSON 转换过程却访问关系。可把查询计数纳入接口验收,限制请求规模并明确加载策略。不能简单写“必须两条查询”,因为需求新增聚合后合理查询数可能变化;应该约束相同输入规模下没有按每条记录增长的意外查询。
Session 关闭后才访问未加载关系,可能出现 detached object(脱离会话对象)相关错误。修复不是重新打开一个全局会话,而是在资源边界内完成所需数据装载,把响应转换为不依赖活动会话的数据结构。否则响应生命周期会隐含数据库连接生命周期,慢客户端再次把池占满。
5. 查询计划不是查询次数的替代品
实验执行 SQLite 的 EXPLAIN QUERY PLAN,检查 children.parent_id 条件使用索引。计划说明数据库选择的访问路径,不是这条语句的实际耗时。这里没有 PostgreSQL 的 EXPLAIN ANALYZE,也没有模拟服务器缓冲命中、远程往返或锁等待,不得混写成跨库性能验证。
一条走索引的查询仍可能被执行上千次;一条全表扫描在很小的表上也可能合理。查询次数、每次扫描量、返回量和持有时间是不同维度。先确认问题是过多往返、过多扫描、过多返回还是锁等待,再选择合适证据。用一个索引截图解释全部慢请求,会遗漏 ORM 的关系加载行为。
生产计划还受到统计信息、参数分布、表大小与数据库版本影响。应脱敏保留相同结构和代表性参数,并记录观测环境。临时训练数据库里只有三条子记录,因此计划对照用于理解机制,不用于宣称上线加索引就能获得某个倍数提升。
6. 迁移:新代码已经部署,旧数据怎么办
Alembic 是数据库 schema(结构)版本迁移工具。脚本创建两个真实 revision(迁移版本):0001建立 jobs 表,写入旧记录;0002增加可空 source 字段。升级后旧记录仍存在且 source 为 None,版本表为0002。随后新代码填入 source,降级路径明确拒绝破坏性删除。
版本文件不是“修改模型后自动等价更新数据库”的保证。自动生成可以发现部分结构差异,仍需审核名称变更、数据回填、约束和方言能力。Alembic教程说明迁移环境与版本脚本的关系。本实验把环境和版本脚本写在临时目录,实际调用 upgrade/downgrade,不用手工 ALTER 冒充迁移工具执行。
expand/contract(先扩展后收缩)允许新旧代码短暂共存:先增加兼容字段,旧数据回填,新代码逐步使用,确认所有旧消费者停止后再收紧约束或删除字段。本例只实测增加可空字段和保留旧行,不声称已经验证全量滚动部署。对于大表,回填分批、锁持有、失败恢复和进度记录必须单独设计。
应用回滚与数据库回滚不同。旧二进制可能仍能读取新增字段后的表;删除新字段却会丢失新代码已经写入的数据。遇到失败优先判断向前修复是否可行,不能因为迁移工具提供 downgrade 就保证数据可恢复。示例选择拒绝破坏性降级,让验收明确暴露这项决策。
SQLite 与其他数据库的 DDL 事务能力不同,添加字段实验不能推导所有 schema 修改均可整体回滚。PostgreSQL 配置可改为经过测试的 psycopg URL,但迁移锁、隔离级别和网络提交状态必须在目标库实测,当前标注待验证。上线前还要确认迁移由谁执行、是否重复并发启动、权限是否最小,以及失败时旧应用是否仍能服务。
7. 实战与独立验收
安装依赖使用工程环境锁文件里的版本;如独立运行,可先创建虚拟环境再安装 SQLAlchemy、Alembic,记录实际版本,避免把滚动文档当成本机版本。完整独立代码在29_database_engineering.py,正文末尾附同一代码。
powershell
python -m pip install sqlalchemy==2.1.6 alembic==1.20.1
python docs/python/examples/29_database_engineering.py预期:池满时第二借用者超时,关闭后恢复;懒加载三条 SELECT、预取两条;结果一致;计划包含索引访问;旧行保留;破坏性降级被拒绝。时长不作为跨机器性能保证。若任一断言失败,先核对依赖版本、SQLite版本与完整异常,不要删除断言让演示通过。
独立能力任务:把父对象数量改为五,证明懒加载查询数随 N 增长,而预取在该固定规模下仍为两条;再删除索引,用计划解释访问路径变化,不捏造耗时结论。最后模拟旧代码只读 id,升级后仍可读取,证明兼容性的具体范围。
验收还包括所有 Session 和 Connection 退出后借出数回到零,临时目录可删除。Windows 上 SQLite 文件无法删除经常暴露连接未关闭;sqlite3 的事务上下文不等于关闭上下文,底层示例应使用明确 closing。资源回收能否观察到,与查询结果是否正确同样重要。
8. 踩坑与延伸
容易踩的坑是用全局 Session 把不同请求的对象、事务和错误状态混在一起,或者把连接池当成无限扩容层。一个错误请求应回滚自己的边界,不能让后续请求继承半完成事务。池 reset-on-return 是否恢复全部业务会话状态,取决于数据库与配置,不要假设所有临时表和用户变量自动消失。
另一坑是测 N+1 时复用已经加载关系的 Session,得到假阴性。第三个坑是模型增加字段后只验证创建新库,遗漏旧库升级。新库成功与现存数据兼容是不同验收,必须保留旧数据样本完成升级路径测试。
面试回答也应沿证据展开:池耗尽先查持有与归还;ORM慢先区分次数与单条工作;迁移失败先判断新旧应用和数据状态。涉及外部效果与重复提交,继续并发幂等;需要关联请求到数据库阶段,则看日志指标与追踪。 可进一步阅读数据库连接耗尽:池耗尽需要区分借用归还与锁等待。
迁移验收还要覆盖失败后的运行状态
将升级脚本跑通只是开始。迁移前应确认实际schema版本、旧代码读取字段与回填前提;迁移后应检查版本标记和数据不变量,而不是只看命令零退出。若命令在第二步失败,不能在没有检查库状态的情况下重复运行任意DDL。数据库可能支持事务DDL,也可能已经留下部分结构,需要目标库与驱动证据决定恢复路径。
部署计划还应将结构扩展与应用启用分开。先让新字段存在且允许旧数据,旧代码继续运行;新代码启用后观察写入来源与旧值比例,再决定回填和收紧约束。直接增加非空约束却没有旧数据默认值,会让本来健康的升级失败。默认值的业务语义也要核对,不能为了通过约束给所有历史数据随意填“api”。
真实连接池实验还可以补连接持有时间:记录checkout与checkin事件,以同一连接记录对齐,但日志不要输出凭据。把业务事务缩短前后,验证持有分布和等待变化;若SQL次数没变,等待仍改善,这支持“资源占有过长”而非“查询缺少索引”的解释。当前脚本验证借出数,不虚称已经采集完整生产分布。
把状态追踪迁移到版本变更,仍先保持一个不变量:jobs中id一的旧行不能消失。0001写入旧行,0002扩展可空source,读取结果应为(1,None);再填入api,拒绝破坏性降级。主例证明这一小段扩展路径,独立验收继续复用旧代码只读id的查询,检查新结构下仍能读取。
连接实验追谁还占有资源,迁移实验追当前结构与数据是谁能理解。两者都不能用命令成功取代状态检查;不同的是迁移有不可自动撤销的历史,新字段已有数据以后,退回版本号不等于恢复原状态。把失败后的真实schema、版本标记与业务行一起保留,才有恢复依据。
9. 结尾
数据库工程的稳定性来自明确的资源、查询和版本边界,并由状态证据逐项验证。
- 池等待、SQL执行、结果消费和归还分别测量。
- ORM查询次数用真实事件计数,身份映射与加载策略会改变观察结果。
- 升级需保留旧数据并验证兼容,应用回滚不等于安全删除新结构。
参考与完整代码
python
"""真实 SQLAlchemy QueuePool、N+1、SQLite计划与Alembic迁移。"""
from pathlib import Path
import sqlite3
import tempfile
from alembic import command
from alembic.config import Config
from sqlalchemy import ForeignKey, create_engine, event, select, text
from sqlalchemy.exc import TimeoutError as PoolTimeout
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship, selectinload
from sqlalchemy.pool import QueuePool
class Base(DeclarativeBase):
pass
class Parent(Base):
__tablename__ = "parents"
id: Mapped[int] = mapped_column(primary_key=True)
children: Mapped[list["Child"]] = relationship()
class Child(Base):
__tablename__ = "children"
id: Mapped[int] = mapped_column(primary_key=True)
parent_id: Mapped[int] = mapped_column(ForeignKey("parents.id"), index=True)
def migrate(url, directory):
scripts = directory / "migrations"
(scripts / "versions").mkdir(parents=True)
(scripts / "env.py").write_text('''from alembic import context
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
engine = create_engine(context.config.get_main_option("sqlalchemy.url"), poolclass=NullPool)
try:
with engine.connect() as connection:
context.configure(connection=connection)
with context.begin_transaction():
context.run_migrations()
finally:
engine.dispose()
''', encoding="utf-8")
(scripts / "versions" / "0001_jobs.py").write_text('''from alembic import op
import sqlalchemy as sa
revision = "0001"
down_revision = None
def upgrade():
op.create_table("jobs", sa.Column("id", sa.Integer, primary_key=True))
def downgrade():
op.drop_table("jobs")
''', encoding="utf-8")
(scripts / "versions" / "0002_source.py").write_text('''from alembic import op
import sqlalchemy as sa
revision = "0002"
down_revision = "0001"
def upgrade():
op.add_column("jobs", sa.Column("source", sa.String(32), nullable=True))
def downgrade():
raise RuntimeError("refuse destructive downgrade; preserve source data")
''', encoding="utf-8")
config = Config()
config.set_main_option("script_location", str(scripts))
config.set_main_option("sqlalchemy.url", url)
command.upgrade(config, "0001")
engine = create_engine(url)
try:
with engine.begin() as connection:
connection.execute(text("INSERT INTO jobs(id) VALUES(1)"))
command.upgrade(config, "head")
with engine.begin() as connection:
assert connection.execute(text("SELECT id,source FROM jobs")).all() == [(1, None)]
connection.execute(text("UPDATE jobs SET source='api' WHERE id=1"))
assert connection.execute(text("SELECT version_num FROM alembic_version")).scalar_one() == "0002"
try:
command.downgrade(config, "0001")
except RuntimeError as error:
assert "destructive" in str(error)
else:
raise AssertionError("destructive downgrade accepted")
print("migration: old row preserved; destructive downgrade rejected")
finally:
engine.dispose()
def main():
with tempfile.TemporaryDirectory() as directory:
root = Path(directory)
url = "sqlite:///" + (root / "example.sqlite").as_posix()
engine = create_engine(url, poolclass=QueuePool, pool_size=1,
max_overflow=0, pool_timeout=0.05)
try:
held = engine.connect()
try:
assert engine.pool.checkedout() == 1
try:
engine.connect()
except PoolTimeout:
print("pool: checkout=1 second borrower times out")
else:
raise AssertionError("pool limit ignored")
finally:
held.close()
assert engine.pool.checkedout() == 0
with engine.connect() as connection:
assert connection.execute(text("SELECT 1")).scalar_one() == 1
Base.metadata.create_all(engine)
with Session(engine) as session:
session.add_all([Parent(id=1, children=[Child(id=1), Child(id=2)]),
Parent(id=2, children=[Child(id=3)])])
session.commit()
observed = []
def count_select(connection, cursor, statement, parameters, context, executemany):
if statement.lstrip().upper().startswith("SELECT"):
observed.append(statement)
event.listen(engine, "before_cursor_execute", count_select)
results = []
for eager in [False, True]:
observed.clear()
with Session(engine) as session: # 新会话,避免身份映射掩盖查询。
query = select(Parent).order_by(Parent.id)
if eager:
query = query.options(selectinload(Parent.children))
result = [(parent.id, len(parent.children)) for parent in session.scalars(query)]
expected = 2 if eager else 3
assert len(observed) == expected, observed
print("eager:", eager, "SELECT count:", len(observed))
results.append(result)
assert results[0] == results[1] == [(1, 2), (2, 1)]
event.remove(engine, "before_cursor_execute", count_select)
with engine.connect() as connection:
plan = connection.execute(text("EXPLAIN QUERY PLAN SELECT * FROM children WHERE parent_id=1")).all()
assert any("INDEX" in row[-1] for row in plan)
print("SQLite plan:", plan)
assert engine.pool.checkedout() == 0
finally:
engine.dispose()
migrate(url, root)
print("SQLite:", sqlite3.sqlite_version)
if __name__ == "__main__":
main()