我有这张表:
create table tab3(
id int not null auto_increment,
phrase text,
link_1 int,
link_2 int,
primary key (id),
foreign key (link_1) references tab1 (id),
foreign key (link_2) references tab2 (id));
我使用 Python 将大约 400k 行插入到该表中。这是插入语句:
INSERT INTO tab3(phrase, link_1, link_2)
VALUES(
%s,
(select id from tab1 where tab1.col1 = %s),
(select id from tab2 where tab2.col2 = %s));
我在表 tab1.col1 和 tab2.col2 上都有索引。但插入需要很长时间,大约 5 分钟/1000 行
我尝试了MySQL 官方文档中的许多不同技术,例如:
- 使用cursor.execute(stmt,param)
- 使用cursor.executemany(stmt, param s )
- 多个进程(台球https://pypi.org/project/billiard/)
- 阻塞提交,直到插入所有数据块,然后提交更改
- 将插入 stmt 封装在一个事务中(使用START TRANSACTION)
但以上都没有给出很好的改进。
使用 MariaDB ,MariaDB-10.7+对批量插入进行了内置改进。
这适用于
foreign_key_checks=0
和时的空表unique_checks=0
。一般来说,无论版本如何,请考虑增加
innodb-buffer-pool-size
以覆盖tab3
创建的信息和正在读取的tab1
/tab2
数据,以便大部分数据都在内存中。早期版本可能会受益于增加的
innodb-log-file-size
.还要看看你的
phase
. 如果它包含少量文本,则 avarchar(max num of characters)
是更好的存储类型,因为它避免了一些 innodb 批量大小处理代码,这些代码会进行一些大小/速度权衡。