tabla - optimizar consultas lentas mysql

Eliminando millones de filas en MySQL

Question

Recientemente encontré y solucioné un error en un sitio en el que estaba trabajando que dio como resultado millones de filas duplicadas de datos en una tabla que será bastante grande incluso sin ellos (aún en millones). Puedo encontrar fácilmente estas filas duplicadas y puedo ejecutar una sola consulta de eliminación para eliminarlas todas. El problema es que tratar de eliminar tantas filas de una vez bloquea la tabla durante mucho tiempo, lo que me gustaría evitar si es posible. Las únicas formas en que puedo ver para deshacerme de estas filas, sin quitar el sitio (bloqueando la tabla) son:

1. Escriba un script que ejecutará miles de consultas de eliminación más pequeñas en un bucle. En teoría, se solucionará el problema de la tabla bloqueada porque otras consultas podrán pasar a la cola y ejecutarse entre las eliminaciones. Pero aún aumentará un poco la carga de la base de datos y tardará mucho tiempo en ejecutarse.
2. Cambie el nombre de la tabla y vuelva a crear la tabla existente (ahora estará vacía). Luego haga mi limpieza en la tabla renombrada. Cambie el nombre de la nueva tabla, nombre la anterior y combine las nuevas filas en la tabla renombrada. De esta forma se requieren muchos más pasos, pero se debe hacer el trabajo con una interrupción mínima. La única parte difícil aquí es que la tabla en cuestión es una tabla de informes, por lo que una vez que se renombra y se coloca la vacía en su lugar, todos los informes históricos desaparecen hasta que lo vuelvo a colocar en su lugar. Además, el proceso de fusión podría ser un poco doloroso debido al tipo de datos que se almacenan. En general, esta es mi elección probable en este momento.

Me preguntaba si alguien más había tenido este problema antes y, en caso afirmativo, cómo lo resolvió sin desmantelar el sitio y, con suerte, con una interrupción mínima o nula para los usuarios. Si selecciono el número 2, o un enfoque diferente, similar, puedo programar que las cosas funcionen tarde en la noche y hacer la fusión temprano a la mañana siguiente y solo informar a los usuarios con anticipación, por lo que no es un gran problema. Solo estoy buscando si alguien tiene alguna idea para una mejor o más fácil manera de hacer la limpieza.

Answer 1

De acuerdo con la documentación de mysql , TRUNCATE TABLE es una alternativa rápida a DELETE FROM . Prueba esto:

TRUNCATE TABLE table_name

Intenté esto en filas de 50M y lo hice en dos minutos.

Nota: las operaciones truncadas no son seguras para transacciones; se produce un error al intentar uno en el curso de una transacción activa o un bloqueo de tabla activo

Answer 2

Hazlo en lotes de, digamos, 2000 filas a la vez. Comprométase en el medio. Un millón de filas no es mucho y esto será rápido, a menos que tenga muchos índices sobre la mesa.

Answer 3

Para nosotros, DELETE WHERE %s ORDER BY %s LIMIT %d respuesta no era una opción, porque el criterio DONDE era lento (una columna no indexada), y llegaría al maestro.

SELECCIONE de una réplica de lectura una lista de claves primarias que desea eliminar. Exportar con este tipo de formato:

00669163-4514-4B50-B6E9-50BA232CA5EB
00679DE5-7659-4CD4-A919-6426A2831F35

Utilice la siguiente secuencia de comandos bash para captar esta entrada y dividirla en instrucciones DELETE [requiere bash ≥ 4 a causa de mapfile incorporado ]:

sql-chunker.sh (recuerde chmod +x me, y cambie el shebang para apuntar a su ejecutable bash 4) :

#!/usr/local/Cellar/bash/4.4.12/bin/bash

# Expected input format:
: <<!
00669163-4514-4B50-B6E9-50BA232CA5EB
00669DE5-7659-4CD4-A919-6426A2831F35
!

if [ -z "$1" ]
  then
    echo "No chunk size supplied. Invoke: ./sql-chunker.sh 1000 ids.txt"
fi

if [ -z "$2" ]
  then
    echo "No file supplied. Invoke: ./sql-chunker.sh 1000 ids.txt"
fi

function join_by {
    local d=$1
    shift
    echo -n "$1"
    shift
    printf "%s" "${@/#/$d}"
}

while mapfile -t -n "$1" ary && ((${#ary[@]})); do
    printf "DELETE FROM my_cool_table WHERE id IN (''%s'');/n" `join_by "'',''" "${ary[@]}"`
done < "$2"

Invocar así:

./sql-chunker.sh 1000 ids.txt > batch_1000.sql

Esto le dará un archivo con la salida formateada como tal (he usado un tamaño de lote de 2):

DELETE FROM my_cool_table WHERE id IN (''006CC671-655A-432E-9164-D3C64191EDCE'',''006CD163-794A-4C3E-8206-D05D1A5EE01E'');
DELETE FROM my_cool_table WHERE id IN (''006CD837-F1AD-4CCA-82A4-74356580CEBC'',''006CDA35-F132-4F2C-8054-0F1D6709388A'');

Luego ejecute las declaraciones de esta manera:

mysql --login-path=master billing < batch_1000.sql

Para aquellos que no están familiarizados con login-path , es solo un acceso directo para iniciar sesión sin escribir una contraseña en la línea de comando.

Answer 4

También recomendaría agregar algunas restricciones a su mesa para asegurarse de que esto no vuelva a suceder. Un millón de filas, a 1000 por disparo, tomará 1000 repeticiones de una secuencia de comandos para completar. Si el script se ejecuta una vez cada 3,6 segundos, finalizará en una hora. Sin preocupaciones. Es poco probable que sus clientes lo noten.

Answer 5

Tuve un caso de uso de eliminar filas de 1M + en la tabla de filas de 25M + en MySQL. Probé diferentes enfoques, como la eliminación de lotes (descrito anteriormente).
Descubrí la forma más rápida (copia de los registros necesarios en la nueva tabla):

1. Crear una tabla temporal que contenga solo identificadores.

CREATE TABLE id_temp_table (temp_id int);

2. Insertar identificadores que deben eliminarse:

inserta en id_temp_table (temp_id) selecciona .....

3. Crear nueva tabla table_new

4. Inserte todos los registros de la tabla en table_new sin filas innecesarias que están en id_temp_table

inserte en table_new .... where table_id NOT IN (seleccione distinct (temp_id) de id_temp_table);

5. Renombrar tablas

Todo el proceso tomó ~ 1hr. En mi caso de uso, la eliminación simple del lote en 100 registros duró 10 minutos.

Answer 6

Usaría mk-archiver del excelente paquete de utilidades Maatkit (un conjunto de scripts Perl para administración de MySQL). Maatkit es de Baron Schwartz, el autor del libro O''Reilly "High Performance MySQL".

El objetivo es un trabajo directo de bajo impacto para picar datos antiguos fuera de la tabla sin afectar mucho las consultas OLTP. Puede insertar los datos en otra tabla, que no necesita estar en el mismo servidor. También puede escribirlo en un archivo en un formato adecuado para LOAD DATA INFILE. O no puede hacer ninguna de las dos cosas, en cuyo caso solo se trata de un DELETE incremental.

Ya está diseñado para archivar las filas no deseadas en pequeños lotes y, como beneficio adicional, puede guardar las filas eliminadas en un archivo en caso de que se arruine la consulta que selecciona las filas para eliminar.

No requiere instalación, solo tome http://www.maatkit.org/get/mk-archiver y ejecute perldoc en él (o lea el sitio web) para obtener documentación.

Answer 7

lo siguiente borra 1,000,000 de registros, uno a la vez.

 for i in `seq 1 1000`; do 
     mysql  -e "select id from table_name where (condition) order by id desc limit 1000 " | sed ''s;/|;;g'' | awk ''{if(NR>1)print "delete from table_name where id = ",$1,";" }'' | mysql; 
 done

puedes agruparlos y eliminar delete table_name donde IN (id1, id2, ... idN) estoy seguro también sin mucha dificultad

Answer 8

DELETE FROM `table`
WHERE (whatever criteria)
ORDER BY `id`
LIMIT 1000

Lave, enjuague, repita hasta que afecten cero filas. Tal vez en un script que duerme por un segundo o tres entre iteraciones.