SQLAlchemy批量查询性能疑问：为何JSON聚合方式快10倍？

我在使用SQLAlchemy开发应用时遇到了性能问题，哪怕是最简单的查询，速度都很慢：

data = [x[0] for x in db.query(MyTable.id).all()]
# 预热后耗时约30ms，返回4500条结果

用原生查询性能也没改善：

raw = [x[0] for x in db.execute("SELECT id FROM mytable").all()]
# 预热后耗时约30ms，返回相同结果

但我发现先在数据库端把查询结果聚合为JSON，再在客户端解析的方式速度极快：

raw_json = json.loads(db.execute("SELECT JSON_ARRAYAGG(id) FROM mytable").scalar_one())
# 耗时约3ms，返回相同结果

这种性能差异在其他客户端执行原SQL语句时也一致，但这种写法太繁琐。我有以下疑问：

1. 为何第二种方式比第一种快3倍？它们生成的SQL相同，3倍的性能开销似乎随数据量增加而扩大。 编辑： 这是我的测试设置有误导致的。
2. 为何第三种方式比第二种快10倍？除了db.execute(...).all()，是否有更高效的查询结果获取方法？（scalars().all()似乎没区别。）

当前使用的技术栈：SQLAlchemy 1.4、pymysql 1.0.2、Python 3.10，也可以考虑切换版本。

问题解答 #

1. 第三种方式更快的核心原因

第三种方式快10倍的核心在于数据传输和结果解析的开销差异：

• 执行SELECT id FROM mytable时，数据库需要返回4500条独立行记录，pymysql驱动要逐行解析这些结果，将每一行转换为Python元组/对象，再在Python中遍历提取每个元素，这中间涉及大量IO交互和对象创建开销。
• 用JSON_ARRAYAGG(id)时，数据库会把所有id聚合为单个JSON字符串返回，仅需传输一条记录。Python端只需解析一次JSON字符串就能得到完整列表，省去了逐行处理的大量开销，数据量越大，这种差异越明显。

2. 更高效的查询结果获取方法

除了JSON聚合的方式，还可以尝试以下优化方向：

• 使用流式查询：SQLAlchemy 1.4+支持流式结果集，避免一次性加载所有数据到内存，适合大数据量场景。示例：

  result = db.execute("SELECT id FROM mytable").yield_per(1000)
  data = [x[0] for x in result]
  

• 调整数据库驱动游标类型：将pymysql的cursorclass设置为pymysql.cursors.SSCursor（服务器端游标），减少客户端内存占用，同时优化结果传输效率：

  from sqlalchemy import create_engine
  engine = create_engine("mysql+pymysql://user:pass@host/db?cursorclass=pymysql.cursors.SSCursor")
  

• 直接调用底层游标方法：跳过SQLAlchemy的部分封装，直接使用游标fetchall()，在小数据量场景下可能有小幅性能提升：

  cursor = db.execute("SELECT id FROM mytable").cursor
  raw = [x[0] for x in cursor.fetchall()]
  

• 升级依赖版本：SQLAlchemy 2.x对结果集处理做了不少性能优化，pymysql的新版本也修复了驱动层面的性能问题，可以尝试升级到最新稳定版。

内容的提问来源于stack exchange，提问作者Georg Wicke-Arndt