SQLAlchemy构建含10万条IN条件的查询耗时过长，求优化

算力阿峰

2026-8-20

SQLAlchemy长列表IN查询构建性能优化问题

问题描述

我编写了如下SQLAlchemy查询语句，其中input_video_ids是包含10万条值的长列表：

query = session.query(table.video_id).\
        join(VideoCatalog, VideoCatalog.id == table.video_id).\
        filter(table.event_code.in_(event_codes)).\
        filter(rec.video_id.in_(input_video_ids))
result = query.all()

发现仅构建上述query对象就耗时超过2秒，比该查询在数据库中的执行时间还要长。单独执行以下代码段同样耗时2秒：

query = session.query(table.video_id).\
        join(VideoCatalog, VideoCatalog.id == table.video_id).\
        filter(table.event_code.in_(event_codes)).\
        filter(rec.video_id.in_(input_video_ids))

性能分析显示大部分耗时集中在SQLAlchemy内部方法调用中（分析结果如下）：

4668894 function calls (4668876 primitive calls) in 2.736 seconds

   Ordered by: cumulative time

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
        3    0.000    0.000    2.735    0.912 /usr/local/lib/python3.7/dist-packages/sqlalchemy/orm/attributes.py:218(operate)
        3    0.000    0.000    2.735    0.912 /usr/local/lib/python3.7/dist-packages/sqlalchemy/orm/properties.py:366(operate)
      8/2    0.000    0.000    2.735    1.367 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/operators.py:515(in_)
        4    0.000    0.000    2.735    0.684 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:758(operate)
      6/2    0.000    0.000    2.735    1.367 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/operators.py:1261(in_op)
        4    0.000    0.000    2.735    0.684 <string>:1(<lambda>)
        4    0.001    0.000    2.735    0.684 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/type_api.py:64(operate)
        2    0.222    0.111    2.734    1.367 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/default_comparator.py:158(_in_impl)
   179533    0.256    0.000    2.089    0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4218(_bind_param)
   179533    0.764    0.000    1.833    0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:961(__init__)
   179534    0.353    0.000    0.555    0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4402(__new__)
   179533    0.081    0.000    0.291    0.000 /usr/lib/python3.7/re.py:185(sub)
        2    0.000    0.000    0.286    0.143 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:1991(__init__)
        2    0.102    0.051    0.286    0.143 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:2001(<listcomp>)
   179534    0.118    0.000    0.182    0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4354(__new__)
   179533    0.082    0.000    0.159    0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/type_api.py:559(coerce_compared_value)
   897732    0.126    0.000    0.126    0.000 {built-in method builtins.isinstance}
   179533    0.072    0.000    0.121    0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4744(_is_literal)
   179537    0.033    0.000    0.119    0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4666(_expression_literal_as_text)
   179533    0.082    0.000    0.108    0.000 /usr/lib/python3.7/re.py:271(_compile)
   179533    0.101    0.000    0.101    0.000 {method 'sub' of 're.Pattern' objects}
   179537    0.036    0.000    0.086    0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4686(_literal_as_text)
   179533    0.058    0.000    0.077    0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/sqltypes.py:3011(_resolve_value_to_type)
   179537    0.065    0.000    0.065    0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:706(self_group)
   179537    0.029    0.000    0.050    0.000 /usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py:4670(_literal_as)
   179541    0.042    0.000    0.042    0.000 {built-in method __new__ of type object at 0xa21aa0}
   179555    0.021    0.000    0.021    0.000 {built-in method builtins.getattr}
   179544    0.020    0.000    0.020    0.000 {built-in method builtins.hasattr}
   179549    0.019    0.000    0.019    0.000 {method 'get' of 'dict' objects}
   179533    0.019    0.000    0.019    0.000 {built-in method builtins.id}
   179533    0.018    0.000    0.018    0.000 {method 'strip' of 'str' objects}
   179538    0.016    0.000    0.016    0.000 {method 'append' of 'list' objects}

尝试用原生SQL通过session.execute(Raw Query)执行时，没有这额外的2秒耗时，推测问题出在SQLAlchemy的预处理环节。当前使用SQLAlchemy版本为1.3.17，求优化方案。

优化方案

使用bindparam的expanding参数
SQLAlchemy默认会为in_()中的每个元素创建单独的绑定参数，10万条数据会生成大量对象，导致构建查询耗时剧增。1.3版本支持expanding参数，可将整个列表作为单个参数传入，由数据库端自动展开：

from sqlalchemy import bindparam

query = session.query(table.video_id).\
        join(VideoCatalog, VideoCatalog.id == table.video_id).\
        filter(table.event_code.in_(event_codes)).\
        filter(rec.video_id == bindparam('vid', expanding=True)).\
        params(vid=input_video_ids)

升级SQLAlchemy版本
1.3版本属于旧版本，后续的1.4+版本对长列表IN查询的参数绑定逻辑做了性能优化，能大幅降低查询构建的耗时，建议升级到稳定的新版本。

保留原生SQL的参数化查询
既然原生SQL没有性能问题，可继续使用session.execute但采用参数化方式，避免SQL注入风险：

sql = """
    SELECT t.video_id 
    FROM your_table t
    JOIN video_catalog vc ON vc.id = t.video_id
    WHERE t.event_code IN :event_codes AND rec.video_id IN :input_video_ids
"""
result = session.execute(sql, {"event_codes": event_codes, "input_video_ids": input_video_ids}).fetchall()

分块处理长列表
如果无法升级版本或使用expanding，可将10万条数据分成多个小批次（比如每1万条一批）分别查询，再合并结果。虽然会增加数据库查询次数，但能降低单次查询构建的耗时：

from itertools import islice

def chunked(iterable, size):
    it = iter(iterable)
    while True:
        chunk = list(islice(it, size))
        if not chunk:
            break
        yield chunk

results = []
for chunk in chunked(input_video_ids, 10000):
    batch_query = session.query(table.video_id).\
                  join(VideoCatalog, VideoCatalog.id == table.video_id).\
                  filter(table.event_code.in_(event_codes)).\
                  filter(rec.video_id.in_(chunk))
    results.extend(batch_query.all())
# 若结果有重复可去重
results = list(set(results))

内容的提问来源于stack exchange，提问作者satinder singh

SQLAlchemy构建含10万条IN条件的查询耗时过长，求优化

问题描述 #

优化方案 #

问题描述

优化方案