python - Pandas read_sql 异常缓慢

Question

我正在尝试将三个不同 MySQL 表中的几列读取到三个不同的数据帧中。

从数据库中读取并不需要很长时间，但实际上将它们放入数据帧中却相当慢。

start_time = time.time()
print('Reading data from database...')

from sqlalchemy import create_engine
q_crash = 'SELECT <query string> FROM table1'
q_vehicle = 'SELECT <query string> table2'
q_person = 'SELECT <query string> FROM table3'
engine = create_engine('mysql+pymysql://user:password@host:port/dbasename')

print('Database time: {:.1f}'.format(time.time() - start_time))

crash = pd.read_sql_query(q_crash, engine)
print('Read_sql time for table 1: {:.1f}'.format(time.time() - start_time))
vehicle = pd.read_sql_query(q_vehicle, engine)
print('Read_sql time for table 2: {:.1f}'.format(time.time() - start_time))
person = pd.read_sql_query(q_person, engine)
print('Read_sql time for table 3: {:.1f}'.format(time.time() - start_time))

输出:

Reading data from database...
Database time: 0.0
Read_sql time for table 1: 13.4
Read_sql time for table 2: 30.9
Read_sql time for table 3: 49.4

这正常吗？这些表非常大——表 3 超过 601,000 行。但每当我使用 read_csv 时，pandas 都能毫不费力地处理更大的数据集。

Answer 1

pandas.read_sql 在加载大型结果集时可能会很慢。在这种情况下，您可以尝试使用我们的工具 ConnectorX (pip install -U connectorx)。我们提供了 read_sql 功能，旨在提高速度和内存使用方面的性能。

在您的示例中，您可以像这样切换到它:

import connectorx as cx

start_time = time.time()

q_crash = 'SELECT <query string> FROM table1'
q_vehicle = 'SELECT <query string> table2'
q_person = 'SELECT <query string> FROM table3'
db_url = "mysql://user:password@host:port/dbasename"

crash = cx.read_sql(q_crash, db_url)
print('Read_sql time for table 1: {:.1f}'.format(time.time() - start_time))
vehicle = cx.read_sql(q_vehicle, db_url)
print('Read_sql time for table 2: {:.1f}'.format(time.time() - start_time))
person = cx.read_sql(q_person, db_url)
print('Read_sql time for table 3: {:.1f}'.format(time.time() - start_time))

此外，您可以通过指定分区列 (partition_on) 和分区号 (partition_num) 在客户端机器上利用多核，其中 ConnectorX 将拆分原始查询并并行获取每个拆分的结果。您可以找到一些如何操作的示例 here .

这是我们在 MySQL 上使用 4 个线程获取 60M 行 x 16 列数据的基准测试结果: