SQLAlchemy执行MariaDB大数据量查询重复卡顿问题排查

可能的原因 #

• 结果集资源未完全释放：虽然代码遍历了结果集，但大数据量下SQLAlchemy底层游标可能未正确关闭，导致连接回到池后处于半失效状态，第二次复用该连接时无法正常通信，程序因等待响应卡住。
• 数据库端通信限制触发中断：
  • max_allowed_packet设置过小：20万条数据的查询结果数据包超过数据库允许的最大值，传输过程中连接被数据库终止。
  • 连接超时设置过短：wait_timeout或interactive_timeout值太小，第一次查询完成后，连接在池中空闲时被数据库主动关闭，第二次执行复用了已失效的连接。
• 连接池复用失效连接：SQLAlchemy默认连接池不会自动检测数据库端已关闭的连接，复用这类连接时会出现通信阻塞。

连接处理是否存在问题 #

现有代码存在潜在的连接资源回收隐患：

• 虽然用with engine.connect()确保连接自动归还池，但大数据量下，若结果集遍历过程中底层游标未彻底关闭，会导致连接回到池后状态异常。
• 列表推导式遍历结果集的方式，未显式关闭Result对象，部分场景下可能导致连接无法完全释放。
• engine.dispose()放在with块外，若这段代码被循环调用，可能存在连接池资源清理不彻底的情况。

可行解决办法 #

1. 确保结果集资源完全释放 #

显式用上下文管理器处理Result对象，确保游标关闭：

with engine.connect() as connection:
    print("test1")
    with connection.execute(sqlalchemy.text(query)) as result:
        dates = [row for row in result]
        print(len(dates))
engine.dispose()

或使用fetchall()一次性获取结果后立即释放游标：

with engine.connect() as connection:
    print("test1")
    result = connection.execute(sqlalchemy.text(query)).fetchall()
    print(len(result))
engine.dispose()

2. 调整数据库配置 #

修改MariaDB配置文件（如my.cnf），调整以下参数后重启服务：

max_allowed_packet = 64M  # 根据数据量调整，确保大于单查询结果数据包大小
wait_timeout = 3600
interactive_timeout = 3600  # 避免连接空闲时被过早关闭

3. 优化SQLAlchemy连接池设置 #

创建引擎时配置连接池自动检测与回收：

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+pymysql://user:pass@host/db",
    pool_recycle=300,  # 每5分钟回收一次连接，值需小于数据库wait_timeout
    pool_pre_ping=True  # 获取连接前先检测有效性，避免复用失效连接
)

4. 分批查询降低传输压力 #

用yield_per()分批获取结果，减少内存占用和单次传输压力：

with engine.connect() as connection:
    print("test1")
    result = connection.execute(sqlalchemy.text(query)).yield_per(1000)
    count = 0
    for row in result:
        count += 1
        # 处理单条数据逻辑
    print(count)
engine.dispose()

5. 排查网络稳定性 #

检查应用服务器与数据库服务器之间的网络是否存在丢包、延迟过高的情况，大数据量传输时网络不稳定易触发通信包读取错误。

内容的提问来源于stack exchange，提问作者Andika Eka