Início / dba / Perguntas / 165495
Accepted
Collin Dauphinee
Asked: 2017-02-25 17:32:47 +0800 CST

Como posso copiar um subconjunto de colunas de uma tabela InnoDB para outra, sem travar?

  • 772

Eu tenho uma instância MySQL em execução no RDS da Amazon. Isso significa que não posso alterar nenhuma das configurações do log binário.

Eu tenho uma tabela source, criada assim, que contém cerca de 2 bilhões de linhas:

CREATE TABLE `source` (
   id INT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

Eu tenho outra tabela destination, com as mesmas colunas, mas idé uma BIGINT:

CREATE TABLE `destination` (
   id BIGINT PRIMARY KEY AUTO_INCREMENT,
   value1 VARCHAR(256),
   value2 VARCHAR(256)
);

A sourcetabela tem lacunas no ID que quero compactar. Eu gostaria de copiar todas as linhas sourcepara destination, sem a idcoluna , algo assim:

INSERT INTO `destination` (value1, value2)
SELECT value1, value2 FROM `source`;

Como posso fazer isso, sem travar a sourcetabela? A cópia em si vai demorar muito, devido ao tamanho da tabela, e não posso bloqueá-la por tanto tempo.

Basicamente, quero executar a instrução acima com READ UNCOMMITTEDisolamento, mas isso não é possível devido à minha incapacidade de alterar as configurações de log binário.

mysql innodb

3 respostas

  1. Best Answer

    SE você não estiver atualizando ou excluindo linhas antigas, a maneira de fazer isso com o mínimo de bloqueio é fazer parte por parte. Veja aqui para fazer chunking para deletar; ele pode ser adaptado para sua 'cópia'. Quando você chegar ao final da cópia, pare as gravações por tempo suficiente para copiar o pedaço final e renomear:

    Então, se seu objetivo real era mudar para BIGINT, termine com

    RENAME TABLE source TO old,
             destination TO source;
DROP TABLE old;
    • 2

  2. Maneira alternativa, que pode ser usada se você não se preocupar com atualizações de linhas antigas (mesma restrição que outra resposta)

    porque a tabela de bloqueio do MySQL para INSERT FROM SELECT e para a tabela SELECT INTO, mas ao mesmo tempo - não bloqueie para:

    • SELECT normal, como

      SELECT t1.col1, t1.col2, t1.col3 FROM table1 t2 LEFT JOIN table2 t2 ON t1.id = t2.id WHERE t2.id IS NULL

    Eu uso ETL Job (Talend) que abre o fluxo da tabela de origem e grava no destino.

    Do ponto de vista do MySQL - basta selecionar para cliente externo

    Exemplo de uso ao vivo - mensalmente, estamos preparando o conjunto de dados para o passado, o motivo - adicionando algumas colunas em tempo real, aumentamos a velocidade de cálculo mais de cem vezes. A tabela de origem sob alta carga regular INSERT/UPDATE, mas apenas para o período atual.

    alternativa "manual" - armazene o resultado da consulta no arquivo csv local (na máquina cliente ou S3), do que a inserção em massa do csv.

    O ETL apenas automatiza esse processo e exclui o arquivo csv intermediário. Muitas ferramentas de desenvolvedor como MySQL Workbench, Navicat, DBVisualizer (qualquer ferramenta séria) ajudam você a armazenar o resultado da consulta em um arquivo local com diferentes formatos.

    Os benefícios desta forma:

    • ele funciona mais rápido do que pedaços, porque lemos dados em uma única etapa, do que podemos usar a inserção em massa.
    • é mais fácil projetar quaisquer alterações nos dados em tempo real
    • 0

  3. Aqui está o problema real: instruções SELECT normais em uma tabela InnoDB não bloquearão nenhuma linha, mas INSERT INTO...SELECT sim. A maneira mais fácil de contornar isso é primeiro selecionar as linhas desejadas da tabela de origem em um arquivo e, em seguida, importar o arquivo para a tabela de destino.

    Uma maneira de fazer isso:

    SELECT... INTO OUTFILE, então LOAD DATA INFILE, por exemplo:

    Dada esta tabela:

    mysql> select * from source_table;
+----+-----------+---------+----------+
| id | columnA   | columnB | columnC  |
+----+-----------+---------+----------+
|  1 | monkey    | mammal  | jungle   |
|  2 | alligator | reptile | swamp    |
|  3 | elephant  | mammal  | savannah |
+----+-----------+---------+----------+
3 rows in set (0.00 sec)

    Extraia columnA e columnB onde columnC = 'swamp' e armazene os resultados em um arquivo no subdiretório schema:

    mysql> select NULL, columna, columnB into outfile 'swampanimals.txt' from source_table where columnC='swamp';

    O NULL existe um espaço reservado para a coluna id. Ele será substituído pelo valor auto_increment quando carregado posteriormente.

    Em seguida, carregue o arquivo resultante na tabela de destino:

    mysql> load data infile 'swampanimals.txt' into table target_table;

    Supondo que target_table estava vazio para começar, ficará assim:

    mysql> select * from target_table;
+----+-----------+---------+
| id | columnA   | columnB |
+----+-----------+---------+
|  1 | alligator | reptile |
+----+-----------+---------+
1 row in set (0.00 sec)

    Dado que você está usando o RDS, talvez não seja possível usar SELECT...INTO OUTFILE - não sei sobre isso. Se você não pode usá-lo, você pode fazer algo assim:

    mysql --host=<rdshost> -s -e "SELECT NULL, columnA, columnB FROM source_table WHERE columnC='swamp'" | sed -r "s/NULL/\\\N/" > swampanimals.txt

    Esse sed é a única maneira que conheço de obter o NULL na saída como um caractere NULL em vez da string literal "NULL". Então você usaria LOCAL ao fazer o arquivo de dados de carregamento:

    mysql> load data local infile "swampanimals.txt" into table target_table;

    Um problema que você pode ter, já que você tem dois bilhões de linhas de dados, é que o LOAD DATA INFILE pode criar uma transação muito grande e fazer com que seu arquivo ibdata, que é onde os logs de undo são armazenados, seja estendido. Você pode contornar isso carregando os dados do arquivo em partes. Aqui está um truque legal para isso, mas requer uma versão moderna da divisão. Dado um grande arquivo de entrada chamado "bigfile" e carregando-o na tabela "target_table" do esquema "target_schema":

    split -C100M --filter="mysql target_schema -e \"LOAD DATA LOCAL INFILE '/dev/stdin' INTO TABLE target_table\"" bigfile

    Isso dividirá o arquivo em pedaços de 100 MB, mas os pedaços não serão divididos entre as linhas. Dessa forma, você não teria nenhuma transação > 100 MB de tamanho e seus logs de desfazer não seriam esticados.

    Consulte: https://dev.mysql.com/doc/refman/5.6/en/select-into.html e https://dev.mysql.com/doc/refman/5.6/en/load-data.html para detalhes completos sobre SELECT...INTO OUTFILE e LOAD DATA INFILE.

    • 0

relate perguntas