Eu executo um aplicativo (site de comparação de preços) onde preciso atualizar uma tabela de preços com dezenas de milhões de linhas todos os dias.
Basicamente, cada feed de produto que eu baixo de um comerciante resulta em vários milhões de linhas para excluir da tabela de preços ( WHERE merchantId = x) e vários milhões de novas linhas para inserir no lugar das antigas.
Até agora, eu costumava baixar todos os feeds sequencialmente, inserir todos eles em uma tabela temporária e depois trocar a tabela antiga pela nova em uma única RENAME TABLEconsulta.
Isso me forçou a atualizar todos os comerciantes de uma vez, portanto, gostaria de abandonar esse método e ter a capacidade de processar um comerciante por vez, excluindo todos os registros WHERE merchantId = xe inserindo todos os novos registros em uma consulta de subsegundo.
Meu primeiro pensamento foi usar o particionamento por lista , uma partição por comerciante. Isso resolveria metade do meu problema: posso usar TRUNCATE PARTITIONpara remover rapidamente todos os preços existentes para um determinado comerciante, mas a outra metade do problema permanece: como inserir rapidamente os milhões de novas linhas, sem tempo de inatividade e bloqueios desnecessários e sem executar com a mesa parcialmente carregada por vários minutos?
Então, pensei: é possível criar uma tabela temporária com a mesma estrutura da tabela particionada, preenchê-la com as novas linhas e, depois de concluída, trocar uma partição da tabela de preços por esta tabela temporária?
Qualquer sugestão para resolver este problema seria apreciada, incluindo sugestões que não envolvam particionamento. Também pensei no MRG_MYISAMmotor, mas não tenho certeza se ele atende às minhas necessidades. Em uma observação final, qualquer mecanismo de armazenamento é adequado para esta tabela de preços, incluindo mecanismos não transacionais.
Depois de pesquisar um pouco mais no site do MySQL, descobri o Exchange Partitions and Subpartitions with Tables , que parece ser um novo recurso do MySQL 5.6:
Parece ser exatamente o que eu estava procurando.
Depois de executar alguns benchmarks no meu servidor, descobri que trocar uma partição por uma tabela não é instantâneo, mas ainda muito rápido : cerca de 1s por 1.700.000 registros na tabela sendo integrada na tabela particionada (o número de registros na partição sendo retirado não parece ter nenhuma influência na velocidade), o que é muito próximo da troca de subsegundos que eu esperava obter para uma tabela com alguns milhões de registros.
Isso deve ser comparado com a inserção de linhas diretamente na tabela particionada, onde obtenho cerca de 27.000 inserções/segundo.
Portanto, trocar uma tabela já preenchida por uma partição é aproximadamente 60x mais rápido (para mim, YMMV) do que inserir diretamente na tabela particionada.
Doce!