如何优化SQLAlchemy查询性能？多关联表数据获取慢求助

我用SQLAlchemy从关联多个表的Delivery表提取数据生成配送JSON文件，现在处理速度极慢——100条左右配送数据（多数包含多个任务）要花15秒。之前试过在查询里关联所有表，但没返回数据，求具体或通用的性能优化方案，相关代码如下：

# Get the deliveries for the specified date
delivery_rs = session.query(Delivery).join(Order) \
    .filter(and_(Delivery.DespatchDateTime.between(start_date, end_date), Order.ProductionSite == site_map.get(site))).all()
# Setup our rowcount for the metadata later
rowcount = 0

# Go through each delivery in the resultset, formulate the full job/delivery/client/customer data and add it to the data array
for delivery in delivery_rs:
    # Add to our rowcount
    rowcount = rowcount + 1

    # Add the jobs to our job array
    job_deliveries = delivery.JobDeliveries
    jobs = []
    quantity = 0
    for job_delivery in job_deliveries:
        job = job_delivery.Job
        web_ref = job.ClientJobReference
        if web_ref and not re.match(r'^CCW_', web_ref):
            web_ref = "" 
        elif web_ref:
            web_ref = re.sub(r'^CCW_', '', web_ref)
        jobs.append({
            "web_ref":      "CCW_{}".format(web_ref) if web_ref else "",
            "name":         job.JobName,
            # The artwork is stored in S3, so provide a link
            "thumbnail":    "https://example.com/{}.png".format(web_ref) if web_ref else ""
        })

        quantity = quantity + job_delivery.Quantity

    # Format our delivery data
    if delivery.AddressContact:
        address_contact = delivery.AddressContact
        contact_data =  {
                    "title":    title_map.get(address_contact.Title),
                    "name":     address_contact.ContactName,
                    "email":    address_contact.ContactEmail,
                    "phone":    address_contact.ContactNumber
                }
    else:
        contact_data = {}
    
    order = delivery.Order
    client = order.Client
    delivery_method = delivery.DeliveryMethod
    address = delivery.Address
    
    result["data"].append(
        {
            "order_number": order.OrderSequenceId,
            "quantity":     quantity,
            "method":       delivery_method.Name,
            "client":       client.Name,
            "end_client":   client.EndCustomer,
            "jobs":         jobs,
            "contact":      contact_data,
            "address": {
                "business": address.BusinessName,
                "postcode": address.PostCode,
                "town":     address.Town,
                "county":   address.County,
                "country":  address.Country.Name,
                "lines": [
                    address.AddressLine1,
                    address.AddressLine2
                ]
            }
        }
    )

优化建议 #

• 解决N+1查询问题：用预加载关联数据
  当前循环中访问delivery.JobDeliveries、delivery.Order等关联属性时，SQLAlchemy会为每条Delivery触发单独的查询，这是性能瓶颈的核心。改用joinedload/subqueryload一次性加载所有需要的关联表：

from sqlalchemy.orm import joinedload, subqueryload

delivery_rs = session.query(Delivery).join(Order) \
    .options(
        # 预加载一对一/多对一关联
        joinedload(Delivery.Order).joinedload(Order.Client),
        joinedload(Delivery.Address).joinedload(Address.Country),
        joinedload(Delivery.DeliveryMethod),
        joinedload(Delivery.AddressContact),
        # 一对多关联用subqueryload避免结果集重复
        subqueryload(Delivery.JobDeliveries).joinedload(JobDelivery.Job)
    ) \
    .filter(and_(Delivery.DespatchDateTime.between(start_date, end_date), Order.ProductionSite == site_map.get(site))) \
    .all()

• 确保过滤字段有数据库索引
  检查Delivery.DespatchDateTime和Order.ProductionSite是否建立了索引，没有索引会导致全表扫描，直接拖慢查询速度。

• 简化字符串处理逻辑
  把正则替换改成更高效的字符串方法，减少CPU开销：

web_ref = job.ClientJobReference
if web_ref:
    if web_ref.startswith('CCW_'):
        web_ref = web_ref[4:]  # 直接截断比re.sub快
    else:
        web_ref = ""

• 分批加载数据，降低内存压力
  如果后续数据量增长，不要一次性用all()取出所有数据，改用yield_per()分批加载：

delivery_rs = session.query(Delivery).join(Order) \
    .options(...)  # 保留预加载选项
    .filter(...) \
    .yield_per(50)  # 每次加载50条

• 排查关联查询无数据的问题
  之前关联所有表没返回数据，大概率是用了内连接（join）过滤掉了关联表为空的记录。如果允许关联表为空，改用outerjoin：

delivery_rs = session.query(Delivery).outerjoin(Order) \
    .filter(...) \
    .all()

或者检查关联条件是否正确，确保业务逻辑上的关联关系和查询条件匹配。

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