python - 如何使用多处理有效地将数据从一个数据库表插入到另一个数据库表？

Question

我的 mysql 数据库表中有多达 500k 行数据。我必须使用一些查询来处理这些数据，并将结果查询数据插入到 5 个不同的表中。

我的代码片段如下:

def jobsFunction(values):
    unique_values = []
    ref_value = {}
    for value in values:
        if value not in unique_values:
            unique_values.append(value[0])
            # some select queries with other tables
            # from the result insert into table1
            for query_vals in select_query:
                ref_val[id] = some_val
                # Insert into table2 with query_vals
                # Update table3 with query_vals
        # insert into table4 for each iteration with some process
        # insert into table5 based on ref_val[id]

if __name__ == '__main__':
    query = "SELECT roll_no, user_id, tenant_item_id FROM table_name"
    cursor.execute(query)
    vals = cursor.fetchall()
    values = list(vals)
    jobFunction(values)

问题是完成整个过程需要超过 12 个小时。所以我决定使用 multiprocessing.Pool 完成该过程，代码如下:

import multiprocessing as mp

def jobsFunction(values):
    # jobs function code

if __name__ == '__main__':
    # values fetching
    lock = mp.Lock()
    p = mp.Pool()
    p.map(jobsFunction, values)
    p.close()
    p.join()

但在这种情况下，从 main 函数到 jobsFunction 的数据流不按顺序排列。

我的问题是:我使用的方法是否满足我的要求？如何使用多处理或多线程有效地实现我的要求？

Answer 1

从数据库中取出数据然后写入回来的速度很慢。尽量避免它。一些数字:如果每个查询只需要 100 毫秒，那么执行它们就需要超过 13 个小时。

考虑使用这种设计:不要将所有数据传输到 Python 进行处理，而是使用一系列或 SQL 查询来完成数据库内的所有操作。因此，不要将数据读入 Python 列表，而是使用 SQL 查询，例如

insert into table1 (...)
select ... from table_name

或

update table1 out
set out.col1 = source.col2,
    out.col2 = source.col3 ...
from table_name source
where out.pk = source.pk
  and ...

数据库经过优化以复制数据。这些查询将运行得非常快，特别是当您正确设置索引时。

考虑使用辅助表来使您的查询更加简单或高效，因为您可以创建它们、截断它们、用数据填充它们，然后为您的案例创建完美的索引。

仅在 Python 中执行真正复杂的操作，并确保它只处理几行。