使用SQLAlchemy批量删除数据耗时久，求高效优化方案

我想把这条SQL语句转换成SQLAlchemy代码：

DELETE FROM  traceability.autodiscovery WHERE (sapsystemname) in (SELECT DISTINCT sapsystemname FROM traceability.lastrun_workorders)

我写的SQLAlchemy代码是：

autodiscovery.delete().where(autodiscovery.c.sapsystemname in df['sapsystemname'].unique().tolist())

但打印生成的SQL时，输出是：

DELETE FROM autodiscovery WHERE false

后来改成循环生成删除语句：

for i in df['sapsystemname'].unique().tolist():
    print(autodiscovery.delete().where(autodiscovery.c.sapsystemname == i))

输出的SQL是：

DELETE FROM  traceability.autodiscovery WHERE sapsystemname is :sapsystemname_1

其中:sapsystemname_1对应变量i。但这种循环方式处理200k-600k行的数据集时速度极慢，目标表有1-1.5百万条记录，要删除其中200k-600k条，原方法执行耗时约45-50分钟，求高效替代方案？

1. 还原原SQL的子查询逻辑 #

直接用SQLAlchemy构造原SQL的子查询，让数据库引擎优化执行，避免把数据拉到Python内存处理：

from sqlalchemy import select, distinct

# 构造子查询
subquery = select(distinct(lastrun_workorders.c.sapsystemname)).select_from(lastrun_workorders)
# 构造删除语句
delete_stmt = autodiscovery.delete().where(autodiscovery.c.sapsystemname.in_(subquery))
# 执行删除
with engine.begin() as conn:
    conn.execute(delete_stmt)

这种方式和原SQL逻辑完全一致，不需要把lastrun_workorders的数据加载到DataFrame，减少数据传输开销，数据库可利用索引优化查询和删除操作。

2. 批量绑定参数的IN查询 #

如果必须用DataFrame中的数据集，不要循环执行单条删除，而是构造带批量参数的IN查询。注意数据库对IN子句的参数数量有限制（如MySQL默认1000），可分批次处理：

import math

sapsystem_list = df['sapsystemname'].unique().tolist()
batch_size = 1000  # 根据数据库调整该值
total_batches = math.ceil(len(sapsystem_list) / batch_size)

with engine.begin() as conn:
    for i in range(total_batches):
        batch = sapsystem_list[i*batch_size : (i+1)*batch_size]
        delete_stmt = autodiscovery.delete().where(autodiscovery.c.sapsystemname.in_(batch))
        conn.execute(delete_stmt)

这种方式把多次单条查询合并成少量批量查询，大幅减少数据库连接和交互的开销。

3. 利用临时表优化删除 #

针对超大量数据，可先把要删除的sapsystemname导入临时表，再关联删除：

from sqlalchemy import text

# 1. 创建临时表并导入DataFrame数据
with engine.begin() as conn:
    conn.execute(text("CREATE TEMPORARY TABLE temp_sapsystems (sapsystemname VARCHAR(255) PRIMARY KEY)"))
    df[['sapsystemname']].drop_duplicates().to_sql('temp_sapsystems', conn, if_exists='append', index=False)

# 2. 关联临时表执行删除
delete_stmt = autodiscovery.delete().where(
    autodiscovery.c.sapsystemname == select(text('temp_sapsystems.sapsystemname')).select_from(text('temp_sapsystems'))
)
with engine.begin() as conn:
    conn.execute(delete_stmt)

临时表可建立索引，关联删除时数据库查询效率更高，适合百万级删除场景。

4. 索引优化 #

无论用哪种方法，确保autodiscovery.sapsystemname和lastrun_workorders.sapsystemname字段都建立索引，这能让数据库快速定位要删除的记录，大幅缩短执行时间：

CREATE INDEX idx_autodiscovery_sapsystemname ON traceability.autodiscovery(sapsystemname);
CREATE INDEX idx_lastrun_sapsystemname ON traceability.lastrun_workorders(sapsystemname);

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