Início / dba / Perguntas / 249718
Accepted
Joe
Asked: 2019-09-27 11:15:11 +0800 CST

Como inserir valores em um banco de dados altamente normalizado sem verificação excessiva de duplicatas?

  • 772

A situação:

O líder do nosso projeto decidiu usar um banco de dados altamente normalizado como nosso design de banco de dados. O que significa que literalmente todos os campos de uma tabela grande são um ID em vez do valor real. Sua intenção é não ter duplicatas de qualquer tipo, mesmo em lugares onde duplicatas não machucam (primeiros nomes de pessoas, esse tipo de coisa).

No entanto, isso leva a um problema: ao inserir novos dados, precisamos verificar cada subtabela para ver se o valor existe (primeira consulta) e inseri-lo se não existir (segunda consulta) ou então recuperar o ID, fazer isso para literalmente todas as colunas da tabela principal (então 30 vezes ou algo assim), e então podemos criar o objeto que realmente queríamos obter. (São ~60 acessos ao banco de dados para criar um objeto!).

Trabalhamos na primavera, então usamos jdbcTemplate para construir uma conexão de banco de dados, e cada consulta é cara. Quando estamos inserindo milhares de novos registros ou atualizando-os, isso na verdade torna o banco de dados muito lento.

Todo esse processo parece completamente sujo e errado para mim e, portanto, queria perguntar: existe uma maneira melhor? É possível fazer uma subconsulta inserir um valor se não existir, ou não, se existir, e retornar a chave real em ambos os casos, que é usada imediatamente para definir o ID na tabela principal? Existe uma solução elegante para reduzir o número de consultas sem introduzir muito SQL complicado (para o bem dos membros da equipe)?

mysql mariadb

2 respostas

  1. Best Answer

    Um pequeno pensamento...

    Formalmente você tem algo parecido com esta estrutura simplificada:

    CREATE TABLE slave1 (id PK, value UNIQUE);
CREATE TABLE slave2 (id PK, value UNIQUE);
CREATE TABLE main (id PK, id_slave1 FK, id_slave2 FK);

    Quando você precisa inserir 2 registros (id_1, val_1_1, val_1_2) e (id_2, val_2_1, val_2_2), você executa:

    CREATE TEMPORARY TABLE temp (val_slave1, val_slave2) [ENGINE=Memory];

INSERT INTO temp (val_slave1, val_slave2)
VALUES (val_1_1, val_1_2),
       (val_2_1, val_2_2);

INSERT IGNORE INTO slave1 (value)
SELECT DISTINCT val_slave1
FROM temp;

INSERT IGNORE INTO slave2 (value)
SELECT DISTINCT val_slave2
FROM temp;

INSERT INTO main (id_slave1, id_slave2)
SELECT slave1.id, slave2.id
FROM temp
JOIN slave1 ON temp.val_slave1 = slave1.value
JOIN slave2 ON temp.val_slave2 = slave2.value;

    O mecanismo de temppode ser Memory quando a quantidade de valores inseridos for baixa, e InnoDB ou qualquer outra coisa se o array de dados inserido for enorme.

    INSERT IGNORE funciona rápido o suficiente no campo indexado UNIQUE. Garante que não haja duplicatas nas tabelas escravas, e que os valores que devem ser inseridos existirão nas escravas durante a inserção na tabela principal.

    E a consulta final também deve ser rápida - especialmente quando os campos de tabela temporários também são indexados.

    Se você precisar inserir apenas um registro, então você pode, claro, não usar tabela temp... mas acho que a uniformidade é mais segura do que uma pequena simplificação.

    Claro, isso pode ser otimizado. Por exemplo, todas as inserções podem ser unidas em um procedimento armazenado, e você não precisa em "60 acessos ao banco de dados", uma CHAMADA é suficiente. Finalmente você deve executar apenas 3 consultas independentes pela contagem de registros para inserir. E apenas um deles (inserindo em temptable) pode ser enorme (ou mesmo pode ser dividido em vários inserts).

    • 3

  2. Se você puder agrupar os dados, posso fornecer uma técnica que envolve 2 consultas por lote - uma para inserir novos nomes para o lote, outra para encontrar todos os ids. E não desperdiça ids, como REPLACE, INSERT IGNORE, IODKU, etc.

    1. Crie uma tabela temporária com todos os dados desnormalizados. Tem colunas vagas para os ids.
    2. Execute as 2 consultas para cada coluna que precisa ser normalizada. (2*30 consultas no seu caso).
    3. INSERT .. SELECT ..para mover os dados para a(s) tabela(s) real(is).
    4. Trunque ou elimine a tabela temporária.

    Ver Normalização em Massa

    Isso foi projetado (e aperfeiçoado anos atrás) quando eu precisava colocar grandes quantidades de dados (possivelmente provenientes de muitos clientes) em tabelas e alguma normalização (não total) era necessária.

    No começo eu tinha INSERT IGNORE, mas rapidamente percebi que era provável que eu ficasse sem ids de auto_increment. Eu não estava disposto a usar 8 bytes BIGINTs. Afinal, um dos propósitos de normalizá-lo é economizar espaço.

    • 0

relate perguntas