Início / dba / Perguntas / 331074
Accepted
moe_
Asked: 2023-09-11 00:36:20 +0800 CST

otimizar a operação de inserção no MySQL (InnoDB)

  • 772

Eu tenho esta tabela:

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));

Estou inserindo cerca de 400 mil linhas nesta tabela com Python. esta é a instrução de inserção:

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));

Eu tenho um índice nas tabelas tab1.col1 e tab2.col2. mas a inserção está demorando em torno de 5 minutos/1000 linhas

Eu tentei muitas técnicas diferentes dos documentos oficiais do MySQL, como:

  • usando cursor.execute(stmt,param)
  • usando cursor.executemany(stmt, param s )
  • vários processos (bilhar https://pypi.org/project/billiard/ )
  • bloqueando o commit até que todos os dados sejam inseridos e então confirme as alterações
  • encapsulando o insert stmt dentro de uma transação (com START TRANSACTION )

Mas nenhuma das opções acima deu uma boa melhoria.

mysql

1 respostas

  1. Best Answer

    Usando o MariaDB, há essa melhoria embutida no MariaDB-10.7+ para inserção em massa.

    Isto é para tabelas vazias quando foreign_key_checks=0e unique_checks=0.

    Em geral, independentemente da versão, considere aumentar innodb-buffer-pool-sizepara cobrir as tab3informações criadas e os tab1/ tab2dados que estão sendo lidos para que a maior parte fique na memória.

    Versões anteriores podem se beneficiar de um aumento de innodb-log-file-size.

    Veja também o seu phase. Se contiver pequenas quantidades de texto, a varchar(max num of characters)é um tipo de armazenamento melhor, pois evita algum código de manipulação de tamanho em massa do innodb que faz algumas compensações de tamanho/velocidade.

    • 2

relate perguntas