À luz de todas as coisas mencionadas, parece que o gargalo é a própria junção.

ASPECTO #1: Tamanho do Buffer de Junção

Com toda a probabilidade, seu join_buffer_size provavelmente é muito baixo.

De acordo com a documentação do MySQL sobre como o MySQL usa o cache de buffer de junção

Armazenamos apenas as colunas usadas no buffer de junção, não as linhas inteiras.

Sendo este o caso, faça com que as chaves do buffer de junção permaneçam na RAM.

Você tem 10 milhões de linhas vezes 4 bytes para cada chave. Isso é cerca de 40M.

Tente aumentar na sessão para 42M (um pouco maior que 40M)

SET join_buffer_size = 1024 * 1024 * 42;
UPDATE table1 a JOIN table2 b 
ON a.field1 = b.field1 
SET 
a.field2 = b.field2,
a.field3 = b.field3,
a.field4 = b.field4;

Se isso funcionar, continue a adicionar isso amy.cnf

[mysqld]
join_buffer_size = 42M

Reiniciar o mysqld não é necessário para novas conexões. Apenas corra

mysql> SET GLOBAL join_buffer_size = 1024 * 1024 * 42;

ASPECTO #2 : Operação de Junção

Você pode manipular o estilo da operação de junção ajustando o otimizador

De acordo com a documentação do MySQL no bloco aninhado-loop e junções de acesso de chave em lote

Quando o BKA é usado, o valor de join_buffer_size define o tamanho do lote de chaves em cada solicitação ao mecanismo de armazenamento. Quanto maior o buffer, mais acesso sequencial será à tabela à direita de uma operação de junção, o que pode melhorar significativamente o desempenho.

Para que o BKA seja usado, o sinalizador batched_key_access da variável de sistema otimizador_switch deve estar ativado. O BKA usa MRR, portanto, o sinalizador mrr também deve estar ativado. Atualmente, a estimativa de custo para MRR é muito pessimista. Portanto, também é necessário que mrr_cost_based esteja desativado para que o BKA seja usado.

Esta mesma página recomenda fazer isso:

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

ASPECTO #3: Gravando atualizações no disco (OPCIONAL)

A maioria esquece de aumentar o innodb_write_io_threads para gravar páginas sujas fora do buffer pool mais rapidamente.

[mysqld]
innodb_write_io_threads = 16

Você terá que reiniciar o MySQL para esta mudança

DE UMA CHANCE !!!

Com base na minha experiência, eu usaria LOAD DATA INFILE para importar seu arquivo CSV.

A instrução LOAD DATA INFILE lê linhas de um arquivo de texto em uma tabela em uma velocidade muito alta.

Exemplo que encontrei na internet Exemplo de Load Data . Eu testei este exemplo na minha caixa e funcionou bem

Tabela de exemplo

CREATE TABLE example (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Column2` varchar(14) NOT NULL,
  `Column3` varchar(14) NOT NULL,
  `Column4` varchar(14) NOT NULL,
  `Column5` DATE NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB

Exemplo de arquivo CSV

# more /tmp/example.csv
Column1,Column2,Column3,Column4,Column5
1,A,Foo,sdsdsd,4/13/2013
2,B,Bar,sdsa,4/12/2013
3,C,Foo,wewqe,3/12/2013
4,D,Bar,asdsad,2/1/2013
5,E,FOObar,wewqe,5/1/2013

Instrução de importação a ser executada no console MySQL

LOAD DATA LOCAL INFILE '/tmp/example.csv'
    -> INTO TABLE example
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (id, Column3,Column4, @Column5)
    -> set
    -> Column5 = str_to_date(@Column5, '%m/%d/%Y');

Resultado

MySQL [testcsv]> select * from example;
+----+---------+---------+---------+------------+
| Id | Column2 | Column3 | Column4 | Column5    |
+----+---------+---------+---------+------------+
|  1 |         | Column2 | Column3 | 0000-00-00 |
|  2 |         | B       | Bar     | 0000-00-00 |
|  3 |         | C       | Foo     | 0000-00-00 |
|  4 |         | D       | Bar     | 0000-00-00 |
|  5 |         | E       | FOObar  | 0000-00-00 |
+----+---------+---------+---------+------------+

IGNORE simplesmente ignora a primeira linha que são os cabeçalhos das colunas.

Após IGNORE, estamos especificando as colunas (pulando coluna2), a serem importadas, que correspondem a um dos critérios da sua pergunta.

Aqui está outro exemplo diretamente do Oracle: Exemplo LOAD DATA INFILE

Isso deve ser suficiente para você começar.

1. CREATE TABLEque corresponde ao CSV
2. LOAD DATAnessa mesa
3. UPDATE real_table JOIN csv_table ON ... SET ..., ..., ...;
4. DROP TABLE csv_table;

A etapa 3 será muito mais rápida do que linha por linha, mas ainda bloqueará todas as linhas da tabela por um período de tempo não trivial. Se esse tempo de bloqueio for mais importante do que o tempo que todo o processo leva, ...

Se nada mais estiver escrevendo na mesa, então...

1. CREATE TABLEque corresponde ao CSV; nenhum índice, exceto o que é necessário no JOINarquivo UPDATE. Se for único, faça-o PRIMARY KEY.
2. LOAD DATAnessa mesa
3. copie o real_tablepara new_table( CREATE ... SELECT)
4. UPDATE new_table JOIN csv_table ON ... SET ..., ..., ...;
5. RENAME TABLE real_table TO old, new_table TO real_table;
6. DROP TABLE csv_table, old;

A etapa 3 é mais rápida que a atualização, especialmente se os índices desnecessários forem deixados de fora.
O passo 5 é "instantâneo".

Você disse:

• As atualizações afetam 6-25% da sua mesa
• Você quer fazer isso o mais rápido possível (<1hr)
• sem trava
• não precisa ser em uma única transação
• ainda (no comentário sobre a resposta de Rick James), você expressa preocupação com as condições da corrida

Muitas dessas afirmações podem ser contraditórias. Por exemplo, grandes atualizações sem travar a tabela. Ou evitando condições de corrida sem usar uma transação gigante.

Além disso, como sua tabela é fortemente indexada, tanto as inserções quanto as atualizações podem ser lentas.

Evitando condições de corrida

Se você conseguir adicionar um carimbo de data/hora atualizado à sua tabela, poderá resolver as condições de corrida e evitar registrar meio milhão de atualizações em uma única transação.

Isso libera você para realizar atualizações linha por linha (como você faz atualmente), mas com confirmação automática ou lotes de transações mais razoáveis.

Você evita condições de corrida (ao atualizar linha por linha) realizando uma verificação de que uma atualização posterior ainda não ocorreu ( UPDATE ... WHERE pk = [pk] AND updated < [batchfile date])

E, mais importante, isso permite que você execute atualizações paralelas .

Correndo o mais rápido possível — Paralelizando

Com esta verificação de carimbo de hora agora em vigor:

1. Divida seu arquivo de lote em alguns pedaços de tamanho razoável (digamos 50.000 linhas/arquivo)
2. Em paralelo, faça com que um script leia cada arquivo e produza um arquivo com 50.000 instruções UPDATE.
3. Em paralelo, assim que (2) terminar, mysqlexecute cada arquivo sql.

(por exemplo bash, veja splite veja xargs -Pmaneiras de executar facilmente um comando de várias maneiras paralelas. O grau de paralelismo depende de quantos threads você deseja dedicar à atualização )

Para o UPDATEcorrer rápido, você precisa

INDEX(uploaders, downloaders, verified)

Pode estar em qualquer mesa. Os três campos podem estar em qualquer ordem.

Isso facilitará a UPDATEpossibilidade de combinar rapidamente as linhas entre as duas tabelas.

And make the datatypes the same in the two tables (both INT SIGNED or both INT UNSIGNED).

Large updates are I/O bound. I would suggest:

1. Create a distinct table that will store your 3 frequently updated fields. Let's call one table assets_static where you keep, well, static data, and the other assets_dynamic that will store uploaders, downloaders and verified.
2. If you can, use the MEMORY engine for the assets_dynamic table. (backup to disk after each update).
3. Update your lightweight and nimble assets_dynamic as per your update 4 (i.e. LOAD INFILE ... INTO temp; UPDATE assets_dynamic a JOIN temp b on a.id=b.id SET [what has to be updated]. This should take less than a minute. (On our system, assets_dynamic has 95M rows and updates impact ~ 6M rows, in a little more than 40s.)
4. When you run Sphinx's indexer, JOIN assets_static and assets_dynamic (assuming that you want to use one of these fields as an attribute).