如何使用python和mysql尽快将数据插入多个表中

Question

我创建了多个表，我想尽快将数据插入其中。

代码现在正在运行，但速度非常慢。

我只能在大约 10 秒内将 15k 数据插入 10k 个不同的表中。 我不能使用executemany()因为据我了解，只有在想将数据插入同一个表中才能使用它，但就我而言，我几乎每次都想将新数据插入不同的表中。

我尝试使用池连接进行连接，但它似乎对代码速度没有太大影响。

如何使此代码更快？

import random
import sys
from timeit import default_timer as timer
#from numba import jit, cuda
import string
from mysql.connector import connect, Error, pooling
import os
import multiprocessing

def create_data(data_list, data_loop_ii, step_number):
current_data = ([data_loop_ii, step_number])
data_list.append(current_data)
step_number += 1
return step_number, data_list

def cpu_func():
max_space_number = 2 ** 256
number_of_data_per_side = 10000
number_of_spaces = 1000000
step_number = 0
try:
connection = connect(
host="localhost",
user="admin",
password="password",
database="db",
)
print("connected to db")
cursor = connection.cursor()
except Error as e:
print(e)
sys.exit()
number_of_data_per_side_halved = int(number_of_data_per_side/2)
for space_ii in range(number_of_spaces):
random_space = random.randrange(0, max_space_number)
# populate data list
data_list = []
for data_loop_ii in range(number_of_data_per_side):
step_number, data_list = create_data(data_list, data_loop_ii, step_number)
# populate query list
insert_query = []
for current_data in data_list:
insert_query.append(f"INSERT INTO {current_data[0]} (r_short,r,seed) VALUES ({int(current_data[1])},{str(current_data[2])},{str(current_data[3])})")
#insert_query += f"INSERT INTO {current_data[0]} (r_short,r,seed) VALUES ({int(current_data[1])},{str(current_data[2])},{str(current_data[3])});"
# insert query loop
for current_query in insert_query:
#print(f"current_query {current_query}")
try:
cursor.execute(current_query)
except:
print("insert query error")
sys.exit()
# commit after query loop is finished
try:
connection.commit()
#connection.close()
except:
print("Failure..")
sys.exit()

cpu_func()

(来自评论)

CREATE TABLE t_name (
id INT AUTO_INCREMENT PRIMARY KEY,
int_short INT,
int_long VARCHAR(80))
INSERT INTO t_name (int_short, int_long)
VALUES
( 10982344, 1098234407980983709870987123770928137409128347) 

(进一步修订)

CREATE TABLE r (
id int NOT NULL AUTO_INCREMENT, 
r_short int DEFAULT NULL, 
r varchar(80) DEFAULT NULL, 
trx_id varchar(80) DEFAULT NULL, 
PRIMARY KEY (id), 
KEY r_short (r_short), 
KEY r_short_index (r_short) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1651988598
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Answer 1

数据库技术喜欢按行缩放。

• 一个连接用于整个任务。
• 对于每个表，构造一个包含最多 1000 个表的单个INSERT。 重复此操作，直到在该表中插入所有所需的行。
• Use autocommit=ON，如果您通常可以执行 1000 个批次。
• 如果强制每个表只执行一行，则在每 1000 行(左右)行(跨多个表插入)周围使用BEGIN和COMMIT。
• 通常最好在单个表中有十亿行。 有时分区更好。 具有相同架构的 10K 个不同表几乎总是最糟糕的方法。 请提供你支持这样的论点，这样我就可以在其中戳洞。
• 请提供将命中此大数据集的选择(或其他内容)。 这将帮助我提供有关分区和/或分片的建议。
• 是的，一次COMMIT100M 行将是"压倒性的"，尽管它会起作用。 我上面建议的"1000"是许多因素之间的合理折衷。 在所有情况下，它都非常接近最佳状态。
• 如果你有INDEX(values)，select all the lines that values=something会很快.
• 不要尝试在单个调用中执行多个查询。

(根据评论)

如果20734707098720918237747770989023747098709987213447存储在声明为VARCHAR(99)的列中，并且您有INDEX(big_number)并且您说... WHERE big_number LIKE '2073470709%';，那么即使没有索引，查询也应该非常有效short_int。

但是，一旦你有了这个索引，就没有必要使用short_int;简单地说... WHERE big_number = "20734707098720918237747770989023747098709987213447"。

如果big_number太大而无法INDEX，请说出来;我们可以讨论一种解决方法。

65536表可能会遇到操作系统问题(因为每个TABLE磁盘上至少需要 1 个文件)。 即使操作系统不抱怨，数据库和操作系统的许多方面也使得使用这么多表可能会很慢。

如果big_number是十六进制，那么它的大小可以通过使用VARBINARY(..)或BLOB而不是VARCHAR(..)或TEXT来减少一半。 这将需要对 SQL 进行一些更改;我们可以讨论细节。

实际上，从许多桌子中选择一个，然后从该表中SELECT(带有INDEX)比拥有一张带有合适INDEX的大桌子要慢。 (同上PARTITIONing。

表大小限制为 32TB。 如果你威胁要打它，我们可以解决它。

INSERT ... 1098234407980983709870987123770928137409128347- 由于该"数字"被放入VARCHAR中，因此应该引用。 (它似乎可以在没有引号的情况下工作，但我担心。

运行此内容。 (如果桌子很大，则需要很长时间。

ALTER TABLE t_name
ADD INDEX(int_short),
ADD INDEX(int_long);

int_short上的索引将帮助您查询;另一个索引将有助于我的建议(无需int_short)。

(我那个样本看起来不像十六进制。 这样，最多 80 个，就不需要讨论我对TEXT和BLOB的评论。

更多

似乎没有足够的磁盘空间将所需的索引添加到表中。

计划 A：更大的磁盘 - 至少 800GB。

计划B：DECIMAL压缩。 您的1098234407980983709870987123770928137409128347是 47 位，但VARCHAR要求最多 80 位。 如果列确实是所有数字，并且永远不会超过 64 位，则使其DECIMAL(64,0)(如果安全，则更少)。 这将占用 29 个字节(如果您可以小于"64"，则更少)。 相比之下，VARCHAR为 47(或更多？

方案C：如果不是全位数或不限于64，请指定数据的类型和最大大小。 我还有其他一些想法可能适用于压缩。

无论如何，摆脱r_short及其上的索引。 只需与r. (这样可以节省该列及其索引占用的空间，并简化代码。

当我们在做的时候，你愿意讨论一下trx_id是什么样子的吗？ 它可能受益于类似的收缩。

和。。。 该表目前有大约 16 亿行？PRIMARY KEY是(INT SIGNED)将达到约20亿。 您可能应该同时修复它。INT UNSIGNED最高为 40 亿，占用相同的 4 字节空间。 如果从长远来看这不安全，让我们进一步讨论。 您是否使用id加入其他表？ 是否有一些列(或列的组合)是唯一的 - 因此可以用来代替PK的id？