>我使用以下 sqlalchemy 代码从数据库中检索一些数据
q = session.query(hd_tbl).
join(dt_tbl, hd_tbl.c['data_type'] == dt_tbl.c['ID']).
filter(or_(and_(hd_tbl.c['object_id'] == get_id(row['object']),
hd_tbl.c['data_type'] == get_id(row['type']),
hd_tbl.c['data_provider'] == get_id(row['provider']),
hd_tbl.c['data_account'] == get_id(row['account']))
for index, row in data.iterrows())).
with_entities(hd_tbl.c['ID'], hd_tbl.c['object_id'],
hd_tbl.c['data_type'], hd_tbl.c['data_provider'],
hd_tbl.c['data_account'], dt_tbl.c['value_type'])
其中hd_tbl
和dt_tbl
是 SQL DB 中的两个表,data
是通常包含大约 1k-9k 条目的 pandas 数据帧。hd_tbl
目前包含大约 90k 行。
执行时间似乎随着data
的长度呈指数级增长。相应的 sql 语句(通过 sqlalchemy)如下所示:
SELECT data_header.`ID`, data_header.object_id, data_header.data_type, data_header.data_provider, data_header.data_account, basedata_data_type.value_type
FROM data_header INNER JOIN basedata_data_type ON data_header.data_type = basedata_data_type.`ID`
WHERE data_header.object_id = %s AND data_header.data_type = %s AND data_header.data_provider = %s AND data_header.data_account = %s OR
data_header.object_id = %s AND data_header.data_type = %s AND data_header.data_provider = %s AND data_header.data_account = %s OR
...
data_header.object_id = %s AND data_header.data_type = %s AND data_header.data_provider = %s AND data_header.data_account = %s OR
表和列已完全索引,性能不令人满意。目前,将hd_tbl
和dt_tbl
的所有数据读取到内存中并与熊猫合并功能合并要快得多。但是,这似乎是次优的。有人知道如何改进 sqlalchemy 调用吗?
编辑: 我能够通过以下方式使用 sqlalchemy tuple_ 显着提高性能:
header_tuples = [tuple([int(y) for y in tuple(x)]) for x in
data_as_int.values]
q = session.query(hd_tbl).
join(dt_tbl, hd_tbl.c['data_type'] == dt_tbl.c['ID']).
filter(tuple_(hd_tbl.c['object_id'], hd_tbl.c['data_type'],
hd_tbl.c['data_provider'],
hd_tbl.c['data_account']).in_(header_tuples)).
with_entities(hd_tbl.c['ID'], hd_tbl.c['object_id'],
hd_tbl.c['data_type'], hd_tbl.c['data_provider'],
hd_tbl.c['data_account'], dt_tbl.c['value_type'])
带有相应的查询...
SELECT data_header.`ID`, data_header.object_id, data_header.data_type, data_header.data_provider, data_header.data_account, basedata_data_type.value_type
FROM data_header INNER JOIN basedata_data_type ON data_header.data_type = basedata_data_type.`ID`
WHERE (data_header.object_id, data_header.data_type, data_header.data_provider, data_header.data_account) IN ((%(param_1)s, %(param_2)s, %(param_3)s, %(param_4)s), (%(param_5)s, ...))
我建议您在字段object_id
、data_type
、data_provider
、...
上创建一个复合索引,它们与它们放在表中的顺序相同,并确保它们在您的 WHERE 条件下遵循相同的顺序。它可能会通过磁盘空间的成本来加快您的请求速度。
此外,您可以使用几个后续的小 SQL 请求,而不是具有复杂OR
条件的大型查询。在应用程序端积累提取的数据,或者,如果数量足够大,则在快速临时存储(临时表,noSQL等)中累积
此外,您可以检查MySQL配置并增加与每个线程,请求等的内存量相关的值。一个好主意是检查您的复合索引是否适合可用内存,或者它是否无用。
我想数据库调整可能对提高生产力有很大帮助。否则,您可以分析应用程序的体系结构以获得更重要的结果。