Início / dba / Perguntas / 22057
Accepted
Ben
Asked: 2012-08-07 07:51:56 +0800 CST

incremento rápido em massa no MySQL

  • 772

Eu tenho uma grande tabela foobardescrevendo uma relação muitos-para-muitos e contendo milhões de foo's, milhões de bar's e cada baruma com várias centenas de foo's -> bilhões de linhas.

CREATE TABLE `foobar` (
`foo_id` INT(10) UNSIGNED NOT NULL,
`bar_id` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`foo_id`, `bar_id`),
INDEX `bar_id_idx` (`bar_id`))

Eu tenho outra tabela contando os foo_id's em foobar:

CREATE TABLE `foo_amount` (
`foo_id` INT(10) UNSIGNED NOT NULL,
`amount` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`foo_id`),
INDEX `amount_idx` (`amount`))

A contagem poderia ser feita assim:

INSERT INTO foo_amount (SELECT foo_id, COUNT(*) AS amount FROM foobar GROUP BY foo_id);

Mas eu teria que recalcular a tabela com cada linha inserida/excluída em foobar.

Uma inserção geralmente adiciona um novo barobjeto com várias centenas foode 's. Por exemplo, inserir a barcom bar_id42 tendo foo's com foo_id's 3, 8, 26, 44, .... ficaria assim:

INSERT INTO foobar VALUES (3,42), (8,42), (26,42), (44,42), ...;

Minha segunda tentativa foi atualizar a foo_counttabela após cada barobjeto inserido:

INSERT INTO foo_amount (SELECT foo_id, 1 FROM foobar WHERE bar_id = 42)
ON DUPLICATE KEY UPDATE amount = amount + 1;

Mas isso é muito lento. Você tem alguma ideia de como otimizar isso? Uma opção pode ser acumular novos bar's temporariamente foo_count_tmpe mesclá-los de foo_countvez em quando. A foo_counttabela não estaria atualizada o tempo todo, mas tudo bem. Mas como eu acionaria a atualização então?

mysql ndbcluster

2 respostas

  1. Best Answer

    Que GROUP BYtal contar com foobar do zero ???

    Primeiro, insira quaisquer novos dados no foobar

    Em seguida, faça uma nova GROUP BYcontagem de foobar na tabela temporária:

    CREATE TABLE foo_amount_new LIKE foo_amount;
INSERT INTO foo_amount_new
SELECT foo_id,COUNT(1)
FROM foobar WHERE bar_id = ... 
GROUP BY foo_id;

    Por fim, troque a tabela temporária e solte o antigo foo_amount

    ALTER TABLE foo_amount RENAME foo_amount_zap;
ALTER TABLE foo_amount_new RENAME foo_amount;
DROP TABLE foo_amount_zap;

    No entanto, com uma tabela na casa dos bilhões, esta é uma batalha difícil porque você tem um índice para reconstruir. Uma vez que o seguinte acontece em cada INSERT ... ON DUPLICATE KEY:

    • o valor teria que ser incrementado
    • o valor teria que mudar no amountíndice

    Tente remover o amountíndice para acelerar INSERTs e UPDATEs.

    SUGESTÃO ALTERNATIVA

    Experimente sua solução de tabela temporária usando outro método

    PASSO 01)CREATE TABLE foobar_new LIKE foobar;

    PASSO 02) Faça seus INSERTs bulk emfoobar_new

    PASSO 03)CREATE TABLE foo_amount_new LIKE foo_amount;

    PASSO 04) Realize GROUP BYa contagem do último lote de INSERT em massa

    INSERT INTO foo_amount_new
SELECT foo_id,COUNT(1) FROM foobar_new WHERE bar_id = ... 
GROUP BY foo_id;

    PASSO 05) Realize um INSERT em massa foobardefoobar_new

    INSERT INTO foobar SELECT * FROM foobar_new;

    PASSO 06) Realize um ATUALIZAÇÃO em massa foo_amountdefoo_amount_new

    UPDATE foo_amount A INNER JOIN foo_amount_new B
USING (foo_id) SET A.amount = A.amount + B.amount;

    PASSO 07) Solte as tabelas temporárias

    DROP TABLE foobar_new;
DROP TABLE foo_amount_new;
    • 4

  2. A consulta a seguir é típica?

    INSERT INTO foobar VALUES (3,42), (8,42), (26,42), (44,42), ...;

    Em caso afirmativo, e estou assumindo que isso é gerado por código (não manualmente), sugiro que você crie a próxima consulta:

    UPDATE foo_amount SET amount=amount+1 WHERE foo_in IN (3, 8, 26, 44, ...);

    Mas algumas coisas não estão muito claras para mim:

    • Os INSERTs têm garantia de funcionamento? Quero dizer, pode INSERT INTO foobar VALUES (3,42), (8,42), (26,42), (44,42)conter uma duplicata, falhando assim a operação?

    • E se você estiver usando algum soert de IGNORE, isso complica sua compreensão sobre se você deve incrementar o amountin foo_amount(também se aplica às suas soluções)

    • Por último, o que você está fazendo é essencialmente gerenciar tabelas de resumo. Eu não quero dizer que você não deveria - mas você tem certeza de que precisa deles? você pode talvez apenas buscar os dados quando necessário? Ainda pode ser mais eficiente do que gerenciar todas as gravações. "eficiente" é uma espécie de borrão aqui, é claro, já que você precisa decidir quem obtém a prioridade mais alta para otimização: lê ou escreve.

    • 0

relate perguntas