top query optimize large improve check mysql performance sql-delete sql-execution-plan

mysql - optimize - sql server improve query performance on large table



¿Forma más rápida de eliminar filas coincidentes? (14)

Conecte la base de datos usando la terminal y ejecute el siguiente comando, observe el tiempo del resultado de cada uno de ellos, encontrará que los tiempos de borrar 10, 100, 1000, 10000, 100000 registros no se multiplican.

DELETE FROM #{$table_name} WHERE id < 10; DELETE FROM #{$table_name} WHERE id < 100; DELETE FROM #{$table_name} WHERE id < 1000; DELETE FROM #{$table_name} WHERE id < 10000; DELETE FROM #{$table_name} WHERE id < 100000;

El tiempo de borrar 10 mil registros no es 10 veces más que eliminar 100 mil registros. Luego, a excepción de encontrar una forma de eliminar registros más rápido, existen algunos métodos indirectos.

1, podemos renombrar table_name a table_name_bak, y luego seleccionar registros de table_name_bak a table_name.

2, para eliminar 10000 registros, podemos eliminar 1000 registros 10 veces. Hay un script de ruby ​​de ejemplo para hacerlo.

#!/usr/bin/env ruby require ''mysql2'' $client = Mysql2::Client.new( :as => :array, :host => ''10.0.0.250'', :username => ''mysql'', :password => ''123456'', :database => ''test'' ) $ids = (1..1000000).to_a $table_name = "test" until $ids.empty? ids = $ids.shift(1000).join(", ") puts "delete ==================" $client.query(" DELETE FROM #{$table_name} WHERE id IN ( #{ids} ) ") end

Soy un principiante relativo cuando se trata de bases de datos. Estamos utilizando MySQL y actualmente estoy tratando de acelerar una instrucción SQL que parece demorar un poco en ejecutarse. Miré a mi alrededor en SO por una pregunta similar, pero no encontré ninguna.

El objetivo es eliminar todas las filas de la tabla A que tengan una identificación coincidente en la tabla B.

Actualmente estoy haciendo lo siguiente:

DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);

Hay aproximadamente 100K filas en la tabla ay alrededor de 22K filas en la tabla b. La columna ''id'' es la PK para ambas tablas.

Esta afirmación toma aproximadamente 3 minutos para ejecutarse en mi caja de prueba: Pentium D, XP SP3, 2GB ram, MySQL 5.0.67. Esto me parece lento. Tal vez no lo es, pero esperaba acelerar las cosas. ¿Hay una manera mejor / más rápida de lograr esto?

EDITAR:

Alguna información adicional que podría ser útil. Las tablas A y B tienen la misma estructura que la siguiente para crear la tabla B:

CREATE TABLE b LIKE a;

La tabla a (y, por lo tanto, la tabla b) tiene algunos índices para ayudar a acelerar las consultas que se realizan en su contra. Una vez más, soy un novato relativo en el trabajo DB y sigo aprendiendo. No sé cuánto efecto tiene, si es que tiene, en las cosas. Supongo que tiene un efecto ya que los índices también deben limpiarse, ¿no? También me preguntaba si había otras configuraciones de base de datos que pudieran afectar la velocidad.

Además, estoy usando INNO DB.

Aquí hay información adicional que podría serle útil.

La Tabla A tiene una estructura similar a esta (he desinfectado esto un poco):

DROP TABLE IF EXISTS `frobozz`.`a`; CREATE TABLE `frobozz`.`a` ( `id` bigint(20) unsigned NOT NULL auto_increment, `fk_g` varchar(30) NOT NULL, `h` int(10) unsigned default NULL, `i` longtext, `j` bigint(20) NOT NULL, `k` bigint(20) default NULL, `l` varchar(45) NOT NULL, `m` int(10) unsigned default NULL, `n` varchar(20) default NULL, `o` bigint(20) NOT NULL, `p` tinyint(1) NOT NULL, PRIMARY KEY USING BTREE (`id`), KEY `idx_l` (`l`), KEY `idx_h` USING BTREE (`h`), KEY `idx_m` USING BTREE (`m`), KEY `idx_fk_g` USING BTREE (`fk_g`), KEY `fk_g_frobozz` (`id`,`fk_g`), CONSTRAINT `fk_g_frobozz` FOREIGN KEY (`fk_g`) REFERENCES `frotz` (`g`) ) ENGINE=InnoDB AUTO_INCREMENT=179369 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

Sospecho que parte del problema es que hay varios índices para esta tabla. La Tabla B es similar a la tabla B, aunque solo contiene las columnas id y h .

Además, los resultados de los perfiles son los siguientes:

starting 0.000018 checking query cache for query 0.000044 checking permissions 0.000005 Opening tables 0.000009 init 0.000019 optimizing 0.000004 executing 0.000043 end 0.000005 end 0.000002 query end 0.000003 freeing items 0.000007 logging slow query 0.000002 cleaning up 0.000002

SOLUCIONADO

Gracias a todas las respuestas y comentarios. Ciertamente me hicieron pensar sobre el problema. Felicitaciones a dotjoe por hacerme alejarme del problema haciendo la simple pregunta "¿Alguna otra tabla hace referencia a aid"?

El problema era que había un BORRADOR DELETE en la tabla A que llamaba a un procedimiento almacenado para actualizar otras dos tablas, C y D. La tabla C tenía un FK de vuelta a .id y después de hacer algunas cosas relacionadas con esa identificación en el procedimiento almacenado. , tenía la declaración,

DELETE FROM c WHERE c.id = theId;

Miré en la declaración EXPLAIN y reescribí esto como,

EXPLAIN SELECT * FROM c WHERE c.other_id = 12345;

Entonces, pude ver lo que estaba haciendo y me dio la siguiente información:

id 1 select_type SIMPLE table c type ALL possible_keys NULL key NULL key_len NULL ref NULL rows 2633 Extra using where

Esto me dijo que era una operación dolorosa y que se llamaría 22500 veces (para eliminar el conjunto de datos), ese era el problema. Una vez que creé un ÍNDICE en esa columna other_id y reran el EXPLAIN, obtuve:

id 1 select_type SIMPLE table c type ref possible_keys Index_1 key Index_1 key_len 8 ref const rows 1 Extra

Mucho mejor, de hecho realmente genial.

Agregué que Index_1 y mis tiempos de eliminación están en línea con los tiempos informados por mattkemp . Este fue un error muy sutil de mi parte debido a la habilidad de hornear algunas funciones adicionales en el último minuto. Resultó que la mayoría de las declaraciones alternativas sugeridas DELETE / SELECT, como dijo Daniel , terminaron tomando esencialmente la misma cantidad de tiempo y como mencionó soulmerge , la declaración fue bastante mejor de lo que yo iba a poder construir en base a lo que Necesitaba hacer Una vez que proporcioné un índice para esta otra tabla C, mis DELETE fueron rápidos.

Postmortem :
Dos lecciones aprendidas surgieron de este ejercicio. En primer lugar, está claro que no aproveché el poder de la sentencia EXPLAIN para tener una mejor idea del impacto de mis consultas SQL. Es un error de novato, así que no voy a pegarme por eso. Aprenderé de ese error. En segundo lugar, el código ofensivo era el resultado de una mentalidad de "hazlo rápido" y un diseño / prueba inadecuado llevó a que este problema no apareciera antes. Si hubiera generado varios conjuntos de datos de prueba considerables para utilizar como entrada de prueba para esta nueva funcionalidad, no habría desperdiciado mi tiempo ni el tuyo. Mis pruebas en el lado de DB carecían de la profundidad que mi aplicación tiene en su lugar. Ahora tengo la oportunidad de mejorar eso.

Referencia: EXPLAIN Statement

Eliminar datos de InnoDB es la operación más cara que puede solicitar. Como ya descubrió, la consulta en sí misma no es el problema, la mayoría de ellos se optimizarán para el mismo plan de ejecución de todos modos.

Si bien puede ser difícil entender por qué los SUPRIMIENTOS de todos los casos son los más lentos, hay una explicación bastante simple. InnoDB es un motor de almacenamiento transaccional. Eso significa que si su consulta fue cancelada hasta la mitad, todos los registros seguirían en su lugar como si nada hubiera sucedido. Una vez que esté completo, todo desaparecerá en el mismo instante. Durante el BORRAR otros clientes que se conectan al servidor verán los registros hasta que se complete SU ELIMINACIÓN.

Para lograr esto, InnoDB utiliza una técnica llamada MVCC (control de concurrencia de varias versiones). Lo que básicamente hace es dar a cada conexión una vista de instantánea de toda la base de datos tal como estaba cuando se inició la primera declaración de la transacción. Para lograr esto, cada registro en InnoDB internamente puede tener múltiples valores, uno para cada instantánea. Esta es también la razón por la cual COUNTing en InnoDB toma algo de tiempo, depende del estado de la instantánea que vea en ese momento.

Para su transacción DELETE, cada registro que se identifica de acuerdo con las condiciones de su consulta, se marca para su eliminación. Como otros clientes pueden estar accediendo a los datos al mismo tiempo, no puede eliminarlos inmediatamente de la tabla, porque tienen que ver su respectiva instantánea para garantizar la atomicidad de la eliminación.

Una vez que todos los registros se han marcado para su eliminación, la transacción se confirma correctamente. Y aun así, no pueden eliminarse inmediatamente de las páginas de datos reales, antes de que todas las demás transacciones que trabajaron con un valor de instantánea antes de su transacción DELETE también hayan finalizado.

De hecho, sus 3 minutos no son realmente tan lentos, teniendo en cuenta el hecho de que todos los registros deben modificarse para prepararlos para su eliminación de una manera segura para las transacciones. Probablemente "escuchará" que su disco duro funcione mientras se ejecuta la instrucción. Esto es causado por acceder a todas las filas. Para mejorar el rendimiento, puede intentar aumentar el tamaño de la agrupación de almacenamiento intermedio InnoDB para su servidor e intentar limitar otro acceso a la base de datos mientras lo ELIMINA, reduciendo así el número de versiones históricas que InnoDB debe mantener por registro. Con la memoria adicional, InnoDB podría leer su tabla (principalmente) en la memoria y evitar algún tiempo de búsqueda de disco.

Esto es lo que siempre hago, cuando tengo que operar con datos súper grandes (aquí: una tabla de prueba de muestra con 150000 filas):

drop table if exists employees_bak; create table employees_bak like employees; insert into employees_bak select * from employees where emp_no > 100000; rename table employees to employees_todelete; rename table employees_bak to employees;

En este caso, el sql filtra 50000 filas en la tabla de respaldo. La cascada de consultas se realiza en mi máquina lenta en 5 segundos. Puede reemplazar el inserto en select por su propia consulta de filtro.

¡Ese es el truco para realizar la eliminación masiva en grandes bases de datos!; =)

La consulta en sí ya está en una forma óptima, actualizar los índices hace que toda la operación tarde tanto. Podría desactivar las teclas en esa tabla antes de la operación, eso debería acelerar las cosas. Puede volver a encenderlos más adelante, si no los necesita de inmediato.

Otro enfoque sería agregar una columna de indicador deleted a su tabla y ajustar otras consultas para que tengan en cuenta ese valor. El tipo booleano más rápido en mysql es CHAR(0) NULL (true = '''', false = NULL). Eso sería una operación rápida, puede eliminar los valores después.

Los mismos pensamientos expresados ​​en sentencias sql:

ALTER TABLE a ADD COLUMN deleted CHAR(0) NULL DEFAULT NULL; -- The following query should be faster than the delete statement: UPDATE a INNER JOIN b SET a.deleted = ''''; -- This is the catch, you need to alter the rest -- of your queries to take the new column into account: SELECT * FROM a WHERE deleted IS NULL; -- You can then issue the following queries in a cronjob -- to clean up the tables: DELETE FROM a WHERE deleted IS NOT NULL;

Si eso tampoco es lo que quieres, puedes echar un vistazo a lo que los documentos de mysql tienen para decir sobre la velocidad de las instrucciones de eliminación .

La técnica básica para eliminar múltiples filas de MySQL en una sola tabla a través del campo de id.

DELETE FROM tbl_name WHERE id <= 100 AND id >=200; Esta consulta es responsable de eliminar la condición coincidente entre 100 Y 200 de la tabla determinada

Obviamente, la consulta SELECT que construye la base de su operación DELETE es bastante rápida, por lo que creo que la restricción de la clave externa o los índices son los motivos de su consulta extremadamente lenta.

Tratar

SET foreign_key_checks = 0; /* ... your query ... */ SET foreign_key_checks = 1;

Esto inhabilitaría las verificaciones en la clave externa. Lamentablemente, no puede desactivar (al menos no sé cómo) las actualizaciones de claves con una tabla InnoDB. Con una tabla MyISAM, podrías hacer algo como

ALTER TABLE a DISABLE KEYS /* ... your query ... */ ALTER TABLE a ENABLE KEYS

De hecho, no probé si estas configuraciones afectarían la duración de la consulta. Pero vale la pena intentarlo.

Prueba esto:

DELETE a FROM a INNER JOIN b on a.id = b.id

El uso de subconsultas tiende a ser más lento y luego se une a medida que se ejecutan para cada registro en la consulta externa.

Sé que esta pregunta se ha solucionado bastante debido a las omisiones de indexación de OP, pero me gustaría ofrecer este consejo adicional, que es válido para un caso más genérico de este problema.

Personalmente he tenido que eliminar muchas filas de una tabla que existen en otra y, según mi experiencia, lo mejor es hacer lo siguiente, especialmente si espera que se eliminen muchas filas. Esta técnica, lo más importante, mejorará el retraso del esclavo de replicación, ya que cuanto más tiempo se ejecute cada consulta de un solo mutador, peor será el retraso (la replicación tiene un solo subproceso).

Entonces, aquí está: haga primero un SELECTO, como una consulta separada , recordando los IDs devueltos en su script / aplicación, luego continúe borrando en lotes (digamos, 50,000 filas a la vez). Esto logrará lo siguiente:

  • cada una de las instrucciones de eliminación no bloqueará la tabla durante demasiado tiempo, por lo tanto, no permitirá que el retraso de replicación se salga de control . Es especialmente importante si confía en su replicación para proporcionarle datos relativamente actualizados. La ventaja de usar lotes es que si encuentra que cada consulta DELETE aún tarda demasiado, puede ajustarla para que sea más pequeña sin tocar ninguna estructura de DB.
  • Otro beneficio de usar un SELECT por separado es que el SELECT en sí mismo puede tomar mucho tiempo para ejecutarse , especialmente si no puede utilizar los mejores índices de DB por cualquier razón. Si el SELECT es interno a un DELETE, cuando toda la instrucción migra a los esclavos, tendrá que hacer el SELECT de nuevo, potencialmente retrasando a los esclavos porque tiene que hacer la selección larga de nuevo. El retraso del esclavo, una vez más, sufre mucho. Si utiliza una consulta SELECT por separado, este problema desaparece, ya que todo lo que está pasando es una lista de ID.

Avíseme si hay algún error en mi lógica en alguna parte.

Para obtener más información sobre el retraso de replicación y las formas de combatirlo, similar a este, consulte Explicación del retardo de esclavo de MySQL (retraso) y 7 formas de combatirlo

PD: Una cosa de la que hay que tener cuidado es, por supuesto, posibles modificaciones en la tabla entre los momentos en que el SELECTO termina y los BORRADOS comienzan. Le permitiré manejar esos detalles mediante el uso de transacciones y / o lógica pertinente para su aplicación.

Su tiempo de tres minutos parece realmente lento. Mi suposición es que la columna de ID no se está indexando correctamente. Si pudiera proporcionar la definición de tabla exacta que está utilizando, sería útil.

Creé un script de python simple para producir datos de prueba y ejecuté varias versiones diferentes de la consulta de eliminación con el mismo conjunto de datos. Aquí están las definiciones de mi tabla:

drop table if exists a; create table a (id bigint unsigned not null primary key, data varchar(255) not null) engine=InnoDB; drop table if exists b; create table b like a;

Luego inserté 100k filas en a y 25k filas en b (22.5k de los cuales también estaban en a). Aquí están los resultados de los diversos comandos de eliminación. Dejé caer y repoblé la mesa entre carreras por cierto.

mysql> DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE a.id=b.id); Query OK, 22500 rows affected (1.14 sec) mysql> DELETE FROM a USING a LEFT JOIN b ON a.id=b.id WHERE b.id IS NOT NULL; Query OK, 22500 rows affected (0.81 sec) mysql> DELETE a FROM a INNER JOIN b on a.id=b.id; Query OK, 22500 rows affected (0.97 sec) mysql> DELETE QUICK a.* FROM a,b WHERE a.id=b.id; Query OK, 22500 rows affected (0.81 sec)

Todas las pruebas se realizaron en un Intel Core2 quad-core de 2.5GHz, 2GB de RAM con Ubuntu 8.10 y MySQL 5.0. Tenga en cuenta que la ejecución de una instrucción SQL sigue siendo de un solo subproceso.

Actualizar:

Actualicé mis pruebas para usar el esquema de itsmatt. Lo modifiqué ligeramente al eliminar el incremento automático (estoy generando datos sintéticos) y la codificación del juego de caracteres (no funcionaba, no profundicé en ello).

Aquí están mis nuevas definiciones de tabla:

drop table if exists a; drop table if exists b; drop table if exists c; create table c (id varchar(30) not null primary key) engine=InnoDB; create table a ( id bigint(20) unsigned not null primary key, c_id varchar(30) not null, h int(10) unsigned default null, i longtext, j bigint(20) not null, k bigint(20) default null, l varchar(45) not null, m int(10) unsigned default null, n varchar(20) default null, o bigint(20) not null, p tinyint(1) not null, key l_idx (l), key h_idx (h), key m_idx (m), key c_id_idx (id, c_id), key c_id_fk (c_id), constraint c_id_fk foreign key (c_id) references c(id) ) engine=InnoDB row_format=dynamic; create table b like a;

Luego volví a hacer las mismas pruebas con 100k filas en ay 25k filas en b (y repoblando entre ejecuciones).

mysql> DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE a.id=b.id); Query OK, 22500 rows affected (11.90 sec) mysql> DELETE FROM a USING a LEFT JOIN b ON a.id=b.id WHERE b.id IS NOT NULL; Query OK, 22500 rows affected (11.48 sec) mysql> DELETE a FROM a INNER JOIN b on a.id=b.id; Query OK, 22500 rows affected (12.21 sec) mysql> DELETE QUICK a.* FROM a,b WHERE a.id=b.id; Query OK, 22500 rows affected (12.33 sec)

Como puede ver, esto es bastante más lento que antes, probablemente debido a los múltiples índices. Sin embargo, no está cerca de los tres minutos.

Otra cosa que quizás quiera ver es mover el campo de texto largo al final del esquema. Me parece recordar que mySQL funciona mejor si todos los campos de tamaño restringido son los primeros y el texto, blob, etc. están al final.

Tal vez deberías reconstruir las indices antes de ejecutar una consulta tan ruidosa. Bueno, deberías reconstruirlos periódicamente.

REPAIR TABLE a QUICK; REPAIR TABLE b QUICK;

y luego ejecuta cualquiera de las consultas anteriores (es decir)

DELETE FROM a WHERE id IN (SELECT id FROM b)

Usted está haciendo su subconsulta en ''b'' para cada fila en ''a''.

Tratar:

DELETE FROM a USING a LEFT JOIN b ON a.id = b.id WHERE b.id IS NOT NULL;

DELETE FROM a WHERE id IN (SELECT id FROM b)