如何加速SQLAlchemy查询？

Question

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

我在一些列上添加了单独的索引，例如记录时间戳（存储数据捕获的时间）、设备ID（传感器的标识）和位置有效性（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)

以下是我的EXPLAIN ANALYZE语句：

EXPLAIN ANALYZE SELECT datatable.id, datatable."createdAt", datatable."analogInput01", datatable."analogInput02", datatable."analogInput03", datatable."analogInput04", datatable."analogInput05", datatable."analogInput06", datatable."analogInput07", datatable."canEngineRpm", datatable."canEngineTemperature", datatable."canFuelConsumedLiters", datatable."canFuelLevel", datatable."canVehicleMileage", datatable."deviceId", datatable."deviceTemperature", datatable."deviceInternalVoltage", datatable."deviceExternalVoltage", datatable."deviceAntennaCut", datatable."deviceEnum", datatable."deviceVehicleMileage", datatable."deviceSimSignal", datatable."deviceSimStatus", datatable."iButton01", datatable."iButton02", datatable."recordSequence", datatable."recordTimestamp", datatable."accelerationAbsolute", datatable."accelerationBrake", datatable."accelerationBump", datatable."accelerationTurn", datatable."accelerationX", datatable."accelerationY", datatable."accelerationZ", datatable."positionAltitude", datatable."positionDirection", datatable."positionSatellites", datatable."positionSpeed", datatable."positionLatitude", datatable."positionLongitude", datatable."positionHdop", datatable."positionMovement", datatable."positionValid", datatable."positionEngine" FROM datatable WHERE datatable."recordTimestamp" >= 1519744521 AND datatable."recordTimestamp" <= 1519745181 AND datatable."deviceId" = '864495033990901' AND datatable."positionValid" IS NOT NULL AND datatable."recordTimestamp" % 1 = 0;

以下是使用EXPLAIN ANALYZE进行SELECT的结果：

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计算得出的时间结果：

Translated text:

Below is the time result calculated by 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}

- Afeez Aziz

---

你能提供一下慢查询的EXPLAIN ANALYZE吗？查询在10毫秒内完成。 - leovp

我不确定为什么直接使用会出现这种情况...尽管我在连接的开始和结束之前添加了print(time.time())，结果如下：Select: 10.030901908874512，因此根据打印语句，大约需要10秒钟。 - Afeez Aziz

是的，花费太多时间的是将其转换为Python对象。接收到的数据集大约为1-3MB。我的选择是什么？ - Afeez Aziz

除非您真正需要所有原始数据，否则您可能已经在数据库本身上执行了一些分析，并仅返回聚合结果。或者，如果您需要逐行处理数据，则可以在运行时完成而无需获取所有行。基本上，需要更多的上下文才能提供更多的想法。 - van

需要生成完整的原始数据，因为它将作为CSV返回，并且是应用程序业务逻辑的一部分。您需要什么样的上下文或进一步解释？ - Afeez Aziz

Answer 1

尝试使用Postgres内置的COPY，如果您确实需要通过Python检索结果（例如，您无法直接通过COPY写入磁盘），则可以使用psycopgs copy_expert函数进行COPY。

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()

这应该完全避免序列化。

- fny

Answer 2

• 根据您的查询和索引，我认为您已经尽力了，因为您的查询涉及到"recordTimestamp"、"deviceId"和"positionValid"，所以请确保您已经从这三列创建了索引。
• 我认为问题在于"select([Datatable])"，我猜您选择了所有列，所以根据您的描述，有50多个列，解析数据并将数据发送到客户端需要时间。更明确地说，添加索引只有助于您的"执行时间"（查找结果的时间），但不会帮助您的"获取时间"（当您运行"lol_data = conn.execute(select_statement).fetchall()"时）。
• 解决方案：如果您不想改变表的结构，您可以只选择您需要的列。但是将表拆分成两个表会更好。一个表包含参数，另一个表包含"deviceId"、"recordTimestamp"和值。您可以使用索引来更改"deviceId"（与使用整数相比，比较和发送字符串需要更长的时间）。

- Kien.N

---

我已经尝试使用select([Datatable.recordTimestamp, Datatable.deviceId])，查询时间大致相同。我已经尝试过将deviceId以整数形式进行实验，不幸的是查询时间也相同。 - Afeez Aziz

是的，因为您的数据库执行相同的操作：获取每行数据并解析它，但只发送2列，因此在接收数据时可以节省一些时间，但解析数据时不会节省时间。如果有50多个列，那么非常大，如果您维护此表单，则使用查询已经是最好的选择（从3个列创建BTREE索引，而不是HASH索引，因为HASH索引涉及地址而不是值）。我正在处理一个拥有2亿条记录和仅4列（计数、设备ID、日期时间和值）的表格。检索一个月的数据（约30,000行）只需要8秒钟。 - Kien.N

Answer 3

SQLAlchemy只是数据库连接器，整个查询在数据库的末尾运行。

通过存储过程和SQLAlchemy优化查询，您可以实现它。这里有一篇好文章可以优化您的使用方式。 SQLAlchemy collection docs 如果您正在使用MySQL数据库，您还应该尝试MySQLdb API，因为MySQLdb专门面向MySQL操作和迭代，所以比SQLAlchemy稍快一些。

- LAMRIN TAWSRAS