Contxeto: O cliente relatou lentidão no carregamento da página, que não atualizava por muito tempo. Após investigação, descobriu-se que uma tabela continha cerca de 50 milhões de registros. A solução foi excluir os dados entigos, mantendo apenas os dos últimos seis meses.

Método de exclusão: exclusão em lotes, conforme descrito abaixo.

1. Verificar o total de registros na tabela su_num

mysql> SELECT COUNT(*) FROM su_num;
+----------+
| COUNT(*) |
+----------+
| 58296808 |
+----------+

2. Verificar a quantidade de dados dos últimos seis meses (2021-11-07 10:06:08 até 2022-05-30 23:00:00)

mysql> SELECT COUNT(*) FROM su_num 
    WHERE st_time >= '2021-11-07 10:06:08' 
      AND st_time <= '2022-05-30 23:00:00' 
      AND num_id='1274';
+----------+
| COUNT(*) |
+----------+
|  9187486 |
+----------+

3. Estratégia de exclusão icnremental

Com o comando DELETE comum não é possível remover todo o volume de uma vez. O ideal é deletar no máximo 10.000 linhas por execução:

DELETE FROM su_num 
WHERE st_time >= '2021-11-07 10:06:08' 
  AND st_time <= '2022-05-30 23:00:00' 
  AND num_id='1274' 
LIMIT 10000;

4. Automatização com scripts

Se a exclusão manual for muito lenta, scripts ajudam a automatizar o processo.

(a) Script em Python

import pymysql
import datetime

"""
Este script requer a instalação do pymysql:
pip3 install pymysql==0.10.1 --trusted-host mirrors.aliyun.com

Descrição: Exclusão otimizada em lotes.
1. Deleta em blocos de 500.000 registros por vez.
2. Ajusta a variável key_buffer_size de 8 MB para 512 MB (recomendado < 70% da memória livre).

Execução: /usr/bin/python3 <script>
Agendamento (cron): 10 18 * * * /usr/bin/python3 script.py >> ./test.logs 2>&1
"""

def deletar_dados(data_inicio, data_fim, at_id):
    inicio_processo = datetime.datetime.now()
    conexao = pymysql.connect(host='x.x.x.x', user='xxxx', password='xxxxx', 
                              port=xxxx, database='db_stu_num')
    
    sql_select = "SELECT * FROM su_num WHERE st_time > '{0}' AND st_time < '{1}' AND act_id='{2}' LIMIT 1".format(data_inicio, data_fim, at_id)
    sql_delete = "DELETE FROM su_num WHERE st_time > '{0}' AND st_time < '{1}' AND act_id='{2}' LIMIT 500000".format(data_inicio, data_fim, at_id)
    
    contador = 0
    try:
        with conexao.cursor() as cursor:
            cursor.execute("SET GLOBAL key_buffer_size = 536870912")  # 512 MB
            while True:
                resultado = cursor.execute(sql_select)
                print("Registros encontrados: {0}".format(resultado))
                if resultado is None or resultado == 0:
                    break
                else:
                    deletados = cursor.execute(sql_delete)
                    conexao.commit()
                    contador += 1
                    print("Lote {0}: {1} registros excluídos".format(contador, deletados))
    except Exception as e:
        conexao.rollback()
        print("Erro: {0}".format(e))
    finally:
        conexao.close()
    
    fim_processo = datetime.datetime.now()
    print("Início do processo: {0}".format(inicio_processo))
    print("Fim do processo: {0}".format(fim_processo))
    print("Duração total: {0} segundos".format((fim_processo - inicio_processo).total_seconds()))

if __name__ == "__main__":
    deletar_dados("2021-11-07 10:06:08", "2022-05-30 23:00:00", 1274)

(b) Script em Shell

#!/bin/bash

usuario='xxxx'
senha='xxxxxx'
host='x.x.x.x'
porta='xxxx'
total_registros=9187486
banco='db_stu_num'
tabela='su_num'
comando_mysql="/usr/bin/mysql -u ${usuario} -p${senha} -h ${host} -P ${porta}"

for ((i=10000; i<=${total_registros}; i+=10000))
do
    ${comando_mysql} -e "USE ${banco}; DELETE FROM ${tabela} WHERE st_time >= '2021-11-07 10:06:08' AND st_time <= '2022-05-30 23:00:00' AND act_id='1274' LIMIT 10000;"
done

5. Observações sobre desempenho

Testes mostraram que a exclusão de 10.000 registros levou entre 30,65 segundos e 1 minuto e 59,07 segundos. Para evitar impacto excessivo no desempenho do MySQL, recomenda-se excluir no máximo 5.000 registros por lote.