python - python如何加快查询海量数据的速度?
问题描述
我的任务是:从两个大表中选择数据并对其进行一些操作。
在测试过程中,我发现查询时间确实很长。
耗时:800secs,TableA 行数 = 1.8M,TableB 行数 = 3.8M
我需要程序可以在 300 秒内完成。
我做了什么:
- 使用命名游标(使用更少的内存,但没有时间差)
- 在选择表格部分时使用线程(无时差)
- 使用 fetchmany 而不是简单地循环游标(无时差)
- 将查询分开
created
,同时运行多个python(我将它分成2部分后加速了20%,即使我将部分编号增加到3,4,5....也没有更大的变化......)
E.g.
python main.py --created_start=1635343080000 --created_end=1635343140000 --process_id=0 &
python main.py --created_start=1635343140000 --created_end=1635343200000 --process_id=1 &
...
我的部分代码:
...
cur_a = connection.cursor(name=f'fetch_a_{args.process_id}')
cur_a.execute(
f"""
SELECT * FROM tableA WHERE created BETWEEN {args.created_start} AND {args.created_end}
"""
)
cur_b = connection.cursor(name=f'fetch_b_{args.process_id}')
cur_b.execute(
f"""
SELECT * FROM tableB WHERE created BETWEEN {args.created_start} AND {args.created_end}
"""
)
def query_to_set(cur, table_name, table_set, table_pk):
step_size = 20000
_fetched_size = 0
while True:
rows = cur.fetchmany(size=step_size)
if not rows:
break
_fetched_size += len(rows)
print(f"selecting {table_name} data...{_fetched_size} fetched")
#query form DB is a IO-bound task, use multithreading
x = threading.Thread(
target=query_to_set,
args=(cur_a, "tableA")
)
x.start()
y = threading.Thread(
target=query_to_set,
args=(cur_b, "tableB"),
)
y.start()
x.join()
y.join()
编辑 1:我更改了以下内容,它大大减少了处理时间
- 不要
select *
,只选择您需要的字段 - 将您的程序放在数据库附近。即如果您的数据库在 AWS 上,请将您的程序放在 AWS 机器中
编辑2:
EXPLAIN select * from tableA where created between 1635343080000 and 1635343200000;
Index Scan using tableA_created_idx on tableA(cost=0.58..229971.15 rows=5000000 width=114)
Index Cond: ((created >= '1635343080000'::bigint) AND (created <= '1635343200000'::bigint))
解决方案
您是否已经在您的 postgres 表上创建了索引?根据您的查询创建正确的索引应该会显着提高性能。在您的情况下,您可以在表 tableA 和 tableB 中BTREE
的列上创建索引。created
DROP INDEX IF EXISTS tableA_created;
CREATE INDEX tableA_created
ON tableA
USING btree (created) ;
DROP INDEX IF EXISTS tableB_created;
CREATE INDEX tableB_created
ON tableB
USING btree (created) ;
然后,您可以尝试通过postgres之间和中的JOIN
查询来替换 python 中的光标。tableA
tableB
推荐阅读
- javascript - AngularJS + Select2(多个 - 标签) - 有时会显示其他标签
- c# - LazyLoadingEnabled false 不起作用
- asp.net-web-api - 为什么这个 RestSharp 文件上传不起作用?
- javascript - 在 android 设备上运行 expo 应用程序时网络请求失败
- assembly - RISC-V 使用 LUI 和 ADDI 构建 32 位常量
- c - 使用 strtok() 输入字符串或声明字符串的区别
- postgresql - 授予所有未来表的权限,独立于其所有者
- javascript - 反应中的缩略图组件问题
- c# - 检查列表是否不相交
- charts - toBase64Image() 上的 ChartJs 尺寸