Qual é a maneira mais eficiente de adicionar muitos dados a um banco de dados mysql?

Question

Eu tenho um lote de dados que precisa ser adicionado a um banco de dados mysql todos os dias, consistindo de aproximadamente 100 mil consultas.

START TRANSACTION;
INSERT IGNORE INTO info (Created, Modified, MType, Pform, Name, Version, Sig) VALUES ('2013-07-31','2013-07-31','mtype','pform','name','version','signature');
(repeat 49,000 times)

edit: SELECT id INTO @r FROM info WHERE sig = 'signature';

INSERT IGNORE INTO fullname (ID, Name) values (@r,'fullname');
(repeat 49,000 times)
UPDATE info SET dbversion = 2098 WHERE dbversion = 0;
UPDATE version SET ver = 2098;
COMMIT;

Eu sinto que essa não é a maneira mais eficiente de fazer isso. O processo não usa muito CPU ou memória, mas eu gostaria que fosse concluído mais rapidamente, se possível. Leva ~ 20 minutos agora. Algum conselho?

    

Answer 1

Leia sobre LOAD DATA e veja se ele atende às suas necessidades e restrições. Dos quais, o único que você nos disse é "mais rápido", mas talvez você tenha outras preocupações também: -)

link

Como alternativa, parece que você pode fazer um INSERT INTO VALUES (1a, 1b),..(50000a, 50000b) , mas pode haver um tamanho máximo de consulta.

link

    

Answer 2

Olhando para a consulta que você postou na pergunta, eu assumirei que suas tabelas usam o InnoDB.

Embora a resposta que você aceitou seja ótima, eu queria adicionar outra coisa.

Arquitetura InnoDB

Porfavor,tomenotadobufferdegravaçãodupla.AsalteraçõessãogeralmentegravadasnoBufferdeGravaçãoDuplaemibdata1enosArquivosdeLog(ib_logfile0,ib_logfile1)pormeiodoBufferdeLog.

Vocêpodedesativarobufferdegravaçãodupla,masissorequerqueomysqlsejareiniciadoduasvezes.

PASSO01:SETGLOBALinnodb_fast_shutdown=0;

PASSO02:servicemysqlrestart--innodb_doublewrite='OFF'--innodb_fast_shutdown=0

PASSO03:Executeoseucódigo

STARTTRANSACTION;INSERTIGNOREINTOinfo(Created,Modified,MType,Pform,Name,Version,Sig)VALUES('2013-07-31','2013-07-31','mtype','pform','name','version','signature');(repeat49,000times)edit:SELECTidINTO@rFROMinfoWHEREsig='signature';INSERTIGNOREINTOfullname(ID,Name)values(@r,'fullname');(repeat49,000times)UPDATEinfoSETdbversion=2098WHEREdbversion=0;UPDATEversionSETver=2098;COMMIT;

PASSO04:servicemysqlrestart

SuaconsultadevetermenosE/Sdediscoduranteaatualizaçãoemmassa.

Experimente!

CAVEAT:definindo innodb_fast_shutdown para 0 força todas as alterações transacionais em ibdata1, ib_logfile0 e ib_logfile1 a serem totalmente liberadas para o disco. Isto faz uma inicialização mais curta do mysql.