Quais são as diferenças práticas entre `REPLACE` e` INSERT… ON DUPLICATE KEY UPDATE` no MySQL?

O que eu preciso é definir os valores de todos os campos de um registro com uma chave específica (a chave é composta de fato), inserindo o registro se ainda não houver registro com essa chave.

REPLACE parece ter o objective de fazer o trabalho, mas ao mesmo tempo sua página de manual sugere INSERT ... ON DUPLICATE KEY UPDATE .

Qual deles devo escolher melhor e por quê?

O único “efeito colateral” do REPLACE que me vem à mente é que ele aumentaria os valores de incremento automático (felizmente eu não uso nenhum) enquanto INSERT ... ON DUPLICATE KEY UPDATE provavelmente não. Quais são as outras diferenças práticas a ter em mente? Em que casos específicos o REPLACE pode ser preferido sobre INSERT ... ON DUPLICATE KEY UPDATE e vice-versa?

REPLACE realiza internamente uma exclusão e, em seguida, uma inserção. Isso pode causar problemas se você tiver uma restrição de chave estrangeira apontando para essa linha. Nessa situação, o REPLACE pode falhar ou pior: se sua chave estrangeira estiver configurada para exclusão em cascata, o REPLACE fará com que as linhas de outras tabelas sejam excluídas. Isso pode acontecer mesmo que a restrição tenha sido satisfeita antes e depois da operação REPLACE .

Usar INSERT ... ON DUPLICATE KEY UPDATE evita esse problema e, portanto, é preferível.

Para responder à pergunta em termos de desempenho, fiz um teste usando os dois methods

Substituir em envolve:
1. Tente inserir na mesa
2. Se 1 falhar, exclua linha e insira nova linha

Inserir na atualização de chave duplicada envolve:
1. Tente inserir na mesa
2. Se 1 falhar, atualize a linha

Se todas as etapas envolvidas forem inserções, não haverá diferença no desempenho. A velocidade depende do número de atualizações envolvidas. O pior caso é quando todas as declarações são atualizações

Eu tentei ambas as declarações na minha tabela InnoDB envolvendo 62.510 inputs (apenas atualizações). Nas velocidades de camparing:
Substituir por: 77.411 segundos
Inserir na atualização da chave duplicada: 2.446 segundos

 Insert on Duplicate Key update is almost 32 times faster. 

Tamanho da Tabela: 1.249.250 linhas com 12 colunas em um Amazon m3.medium

Ao usar REPLACE vez de INSERT ... ON DUPLICATE KEY UPDATE , às vezes observo problemas de bloqueio de teclas ou deadlock quando várias consultas chegam rapidamente para uma determinada chave. A atomicidade do último (além de não causar exclusões em cascata) é mais uma razão para usá-lo.

Em que casos específicos o REPLACE pode ser preferido sobre INSERT … ON DUPLICATE KEY UPDATE e vice-versa?

Acabei de descobrir da maneira mais difícil que no caso de tabelas com um mecanismo de armazenamento FEDERATED INSERT...ON DUPLICATE KEY UPDATE são aceitos, mas falham (com um erro 1022: não é possível escrever; chave duplicada na tabela. ..) se ocorrer uma violação de chave duplicada – veja o ponto correspondente nesta página do Manual de Referência do MySQL.

Felizmente, eu era capaz de usar REPLACE vez de INSERT...ON DUPLICATE KEY UPDATE dentro do meu gatilho após a inserção para obter o resultado desejado de replicação de alterações em uma tabela FEDERATED.

Substituir parece que faz duas operações no caso em que a chave já existe. Talvez isso implique que há uma diferença de velocidade entre os dois?

(INSERIR) uma atualização vs uma exclusão + uma inserção (REPLACE)

EDIT: Minha implicação de que a substituição pode ser mais lenta é realmente completamente errada. Bem, de acordo com este post de qualquer forma … http://www.tokutek.com/2010/07/why-insert-on-duplicate-key-update-may-be-slow-by-incurring-disk-seeks /

Se você não listar todas as colunas, acho que REPLACE irá redefinir quaisquer colunas não mencionadas com seus valores padrão nas linhas substituídas. ON DUPLICATE KEY UPDATE deixará as colunas não mencionadas inalteradas.

“É possível que, no caso de um erro de chave duplicada, um mecanismo de armazenamento possa executar o REPLACE como uma atualização em vez de uma exclusão mais inserção, mas a semântica é a mesma.”

http://dev.mysql.com/doc/refman/5.7/en/replace.html