Performance SQLAlchemy和或

Question

我使用以下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中的两个表，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的所有数据读取到内存中并与pandas合并功能合并的速度要快得多。然而，这似乎是次优的。有谁有关于如何改进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, ...))

Answer 1

我建议您在字段object_id、data_type、data_provider、...上以相同的顺序创建复合索引，并确保它们在WHERE条件中遵循相同的顺序。它可能会通过磁盘空间的成本来提高您的请求速度。

此外，您还可以使用几个后续的小SQL请求，而不是具有复杂OR条件的大型查询。在应用程序端累积提取的数据，或者，如果数据量足够大，则在快速临时存储中(临时表、noSQL等)。

此外，您可以检查MySQL配置并增加与每个线程的内存量、请求等相关的值。一个好主意是检查您的复合索引是否适合可用内存，否则它是无用的。

我想DB调优可能会对提高生产率有很大帮助。否则，您可能会分析应用程序的体系结构以获得更重要的结果。