Excluindo grandes blocos de dados do mysql innodb

Question

Eu preciso excluir uma grande parte dos meus dados no meu banco de dados de produção, que tem cerca de 100 GB de tamanho. Se possível, gostaria de minimizar meu tempo de inatividade.

É provável que meus critérios de seleção para exclusão sejam

DELETE * FROM POSTING WHERE USER.ID=5 AND UPDATED_AT<100

Qual é a melhor maneira de excluí-lo?

• Construir um índice?
• Escreva um script sequencial que seja excluído por meio da paginação das linhas 1000 por vez?

Answer 1

Você pode minimizar quase todo o tempo de inatividade impedindo que a tabela seja bloqueada.

Use um SELECT INSERT para migrar os dados que você deseja para uma tabela temporária.

Renomeie as tabelas.

Você precisará considerar qualquer INSERTS durante o tempo em que o SELECT INSERT foi executado.

Por fim, descarte a tabela que contém os dados que você não deseja.

Lembre-se de que, se você não tiver innodb_file_per_table ativado em sua configuração, o espaço em disco alocado não será liberado.

Além disso, considerações especiais serão necessárias para sua aplicação e situação específica.

Aqui está um procedimento que eu escrevi usando este método ..

# Temp table, recreating forty_first_transaction_
#
CREATE TABLE working_table_temp_ LIKE working_table_;

# Increment if there's an auto_increment field.
#
ALTER TABLE working_table_temp_ AUTO_INCREMENT = 15000000;

# Testing inserts and select while this query was running resulted success.
# Verified no active lock.
#
INSERT INTO working_table_temp_ SELECT * FROM working_table_ WHERE id > $NUM;

RENAME TABLE working_table_ TO working_table_old_;
RENAME TABLE working_table_temp_ TO working_table_;

# Verify that all rows were caught
#
# The last row < 15000000 in both tables should be identical.
#
SELECT * FROM working_table_ where id < 15000000 order by id desc limit 5;
SELECT * FROM working_table_old_ where id < 15000000 order by id desc limit 5;

# If not, we need to move them !
#
# This query will need to be modified.
#
INSERT INTO working_table_ SELECT * FROM working_table_old_ WHERE id > 138376577;

# Verify application functionality, if necessary.
#
# LAST CHANCE FOR BACKOUT !!!
#
# Once verified, get rid of the old data to free up some space.
#
DROP TABLE working_table_old_;
^D

df -h




## BACKOUT ##

RENAME TABLE working_table_ TO working_table_new_;
RENAME TABLE working_table_old_ TO working_table_;