我需要在一对一的关系中为每个俱乐部插入一个finance_entity。我决定在MySQL Server中运行此操作,因为单个插入物甚至较慢。如何优化它以更快地运行?有什么方法可以黑客插入选择来做到这一点吗?
我不能将club_id放在finance_entity上,因为更多的关系指向它。
alter table clubs add column finance_entity_id int unsigned null after id;
DELIMITER !
drop procedure if exists create_entities!
create procedure create_entities()
begin
create_entities_for_club: loop
set @club_key = (select id from clubs where finance_entity_id is null limit 1);
insert into finance_entity (id) value (null);
update clubs set finance_entity_id = last_insert_id() where id = @club_key;
if @club_key is null then
leave create_entities_for_club;
end if;
end loop create_entities_for_club;
end!
call create_entities_for_club()!
DELIMITER ;
alter table clubs change column finance_entity_id finance_entity_id int unsigned not null;
alter table clubs add unique (finance_entity_id);
如果有现有表的1:1,则只需具有相同的 PRIMARY KEY
,但是称其为 AUTO_INCREMENT
。
设置临时密钥似乎是最快的方法。
除此之外,必须在缓冲区中有足够的尺寸,结果我的测试机只有16m的缓冲尺寸。
将它们添加到您的my.ini或my.cnf
innodb_buffer_pool_size=1024M
innodb_additional_mem_pool_size = 8M
innodb_log_file_size = 256M
innodb_log_buffer_size = 512M
使用它将其插入表格,将索引添加到club_id,可能使其更快。
alter table finance_entity add column club_id int unsigned null;
insert into finance_entity (club_id) select id from clubs;
update clubs join finance_entity on clubs.id = finance_entity.club_id
set finance_entity_id = finance_entity.id;
alter table finance_entity drop column club_id;
这在20,000个条目中在1.6秒内运行。