ON DUPLICATE KEY UPDATE mais rápido que UPDATE

Eu sei que não é fácil obter um plano de execução para uma atualização do MySQL, pois ele fornece apenas as SELECTinstruções on. Mas a pista pode estar na ordem em que os registros são atualizados, na avaliação de um WHEREque contém um INcom grande quantidade de dados estáticos , bem como na quantidade de leituras e gravações conectadas, cache intermediário, associado a isso.

A declaração

UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN (1,...,10000); 

é um tipo de instrução que tentamos evitar ao atualizar bancos de dados maiores, pois o analisador parece enlouquecer com eles de tempos em tempos. IN ( a,b,c,...,ZZZZ )para mim, tornou-se um estilo de codificação adequado apenas para números de itens muito pequenos nos INdados. Estou trabalhando em um projeto de código aberto onde frequentemente me deparo com o que chamo de "junção de mente remota", a segunda metade geralmente se parece exatamente com o seu problema.

SELECT id FROM all_our_customers WHERE happytospam=1 AND LENGTH(email) > 6;
...
Storing result on client side as string like 
LOOP over results
$all_ids += ",$next_result";
END_LOOP
$all_ids = SUBSTRING($all_ids,1); 
ending up with a string like 
"1,2,3,4,5,8,10,100,1000,...,100000" in $all_ids
...
UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN ( $all_ids )

Enquanto a primeira parte geralmente é executada na velocidade da luz, a segunda parte leva uma eternidade, que é o que você descreve também. Essas consultas geralmente podem ser aceleradas EXTREMAMENTE reescrevendo-as como:

UPDATE humans_we_respect,all_our_customers 
SET humans_we_respect.expressed_interest=1 
WHERE all_our_customers.id = humans_we_respect.id 
AND all_our_customers.happytospam=1 
AND LENGTH(all_our_customers.email) > 6

Nós também usamos

UPDATE humans_we_respect 
SET expressed_interest=1 
WHERE id IN ( 
SELECT id 
FROM all_our_customers 
WHERE happytospam=1 
AND LENGTH(email) > 6 
)

que teve um desempenho melhor que o original, mas não tão bom quanto minha versão sugerida.

Isso tudo pressupõe que você use índices adequados com primário no ID e índices combinados de várias colunas, onde várias colunas são frequentemente usadas juntas ou têm um bom significado juntas e geralmente estão presentes em suas consultas.

A pista é que grandes quantidades de valores estáticos em INcláusulas aumentam o tempo de execução quase exponencialmente em consultas com muitos registros correspondentes, uma vez que basicamente NÃO USAM NENHUM ÍNDICE OU OTIMIZAÇÃO e geralmente terminam em varreduras completas de tabelas, nas quais IMHO a execução examinará cada registro/linha em sua tabela comparando-o com cada item em sua IN()lista UM por UM.

A declaração como

INSERT INTO humans_we_respect (id) VALUES (1),...,(10000) ON DUPLICATE KEY UPDATE expressed_interest=1;

porém está usando o índice para localizar o registro e depois atualiza-lo, mesmo assim não destinado a este uso, ele rodará muito melhor devido ao uso de um índice no ID se houver e só fará uma pesquisa de índice para o registro em vez de milhares de comparações! Trabalhar diretamente com duas tabelas pode, no entanto, ser mais rápido se a lista de IDs for derivada de outra tabela no mesmo servidor, há mais otimizações que podem ser usadas e você não precisa transferir dados de e para o processo do servidor mysql.

Assim como algumas informações extras:

UPDATE humans_we_respect SET expressed_interest=1 WHERE id='1' OR id='2' OR ...

é uma boa técnica para otimizar IN()consultas com contagens muito baixas de elementos, pois criará uma consulta de índice paralela para cada elemento, o que é ótimo para os primeiros elementos, mas diminuirá muito o desempenho com mais elementos e, em algum momento, atingirá o limite do analisador para otimização (IMHO, pode haver 255 elementos em uma consulta), ponto em que ele voltará a um ritmo lento ...

Apenas um palpite:

• Talvez o mysql verifique na primeira consulta o mysql verifica 17 milhões de linhas, se uma couber na INcláusula.
• na segunda consulta você insere apenas 10000 linhas, verifica a chave por índice e atualiza as linhas.

Se for esse o caso, você pode tentar reescrever a primeira consulta para UPDATE... WHERE id <= 10000, mas isso só funcionará se você realmente precisar atualizar 10.000 linhas com ids consecutivos. Talvez você também possa tentar UPDATE ... WHERE id = 1 OR id = 2 OR .... Mas isso só seria rápido, se o mysql pudesse otimizar isso internamente.