如何加速SQLAlchemy查询？

Question

我有一个超过1000万行的表。大约有50多列。该表存储传感器数据/参数。假设我需要查询一整天或86400秒的数据。完成这个查询大约需要20秒或更长时间。在

我在一些列上添加了单独的索引，比如recordTimestamp（在捕获数据时存储）、deviceId（传感器的标识）、positionValid（GPS地理定位是否有效）。然后我添加了一个包含所有三列的复合索引。在

以下是我的疑问：

t1 = time.time()
conn = engine.connect()
select_statement = select([Datatable]).where(and_(
    Datatable.recordTimestamp >= start_date,
    Datatable.recordTimestamp <= end_date,
    Datatable.deviceId == device_id,
    Datatable.positionValid != None,
    Datatable.recordTimestamp % query_interval == 0))
lol_data = conn.execute(select_statement).fetchall()    
conn.close() 
t2 = time.time()
time_taken = t2 - t1
print('Select: ' + time_taken)

以下是我的解释分析声明：

^{pr2}$

以下是选择的解释分析结果：

Index Scan using "ix_dataTable_recordTimestamp" on dataTable (cost=0.44..599.35 rows=5 width=301) (actual time=0.070..10.487 rows=661 loops=1)
Index Cond: (("recordTimestamp" >= 1519744521) AND ("recordTimestamp" <= 1519745181))
Filter: (("positionValid" IS NOT NULL) AND (("deviceId")::text = '864495033990901'::text) AND (("recordTimestamp" % 1) = 0))
Rows Removed by Filter: 6970
Planning time: 0.347 ms
Execution time: 10.658 ms

下面是Python计算的时间结果：

Select:  47.98712515830994 
JSON:  0.19731807708740234

下面是我的代码分析：

10302 function calls (10235 primitive calls) in 12.612 seconds

Ordered by: cumulative time

ncalls  tottime  percall  cumtime  percall filename:lineno(function)
    1    0.000    0.000   12.595   12.595 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py:882(execute)
    1    0.000    0.000   12.595   12.595 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/sql/elements.py:267(_execute_on_connection)
    1    0.000    0.000   12.595   12.595 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py:1016(_execute_clauseelement)
    1    0.000    0.000   12.592   12.592 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py:1111(_execute_context)
    1    0.000    0.000   12.590   12.590 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/engine/default.py:506(do_execute)
    1   12.590   12.590   12.590   12.590 {method 'execute' of 'psycopg2.extensions.cursor' objects}
    1    0.000    0.000    0.017    0.017 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/engine/result.py:1113(fetchall)
    1    0.000    0.000    0.017    0.017 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/engine/result.py:1080(_fetchall_impl)
    1    0.008    0.008    0.017    0.017 {method 'fetchall' of 'psycopg2.extensions.cursor' objects}

Answer 1

• 根据您的查询和索引，我认为您已经尽力做到最好了，因为您的查询涉及"recordTimestamp"、"deviceId"和{}，因此，只需确保您已经从3列创建了索引。在
• 我想问题出在“select([Datatable])”，我猜你选择了所有的列，因此，作为你的描述，有50多个列，解析数据并将数据发送到客户端需要时间。更清楚地说，添加索引只会帮助您的“执行时间”（找到结果的时间），但不会帮助您的“获取时间”（当您运行“lol_data=连接执行（select_statement）.fetchall（））。在
• 解决方案：如果不想更改表的结构，只需选择所需的列。但是把表分成两个表更好。1表包含参数，其他表包含"deviceId"，"recordTimestamp"，值。您可以通过使用索引来更改"deviceId"（比较和发送字符串比使用整数花费更多的时间）。在

Answer 2

SQLAlchemy is just the connector to the database, the whole query runs at the end of the database.

在过程和SQLAlchemy的帮助下优化查询，您可以将其归档。 这里有一个很好的阅读，可以优化你的使用方式。 SQLAlchemy collection docs

如果您使用的是MySQL数据库，那么还应该尝试MySQLdb API，它比SQLAlchemy快一点，因为MySQLdb是专门面向对象的MySQL操作和迭代的。在

Answer 3

尝试使用内置于COPY的Postgres，或者如果您确实需要在Python中检索结果（例如，您不能通过COPY直接写入磁盘），您可以使用COPY via psycopgscopy_expert函数：

cur = conn.cursor()

outputquery = "COPY ({0}) TO STDOUT WITH CSV HEADER".format(query)

with open('resultsfile', 'w') as f:
    cur.copy_expert(outputquery, f)

conn.close()

这应该避免全部序列化。在