¿Una forma más rápida de eliminar filas coincidentes?

Su tiempo de tres minutos parece muy lento. Mi conjetura es que la columna id no está siendo indexada correctamente. Si pudiera proporcionar la definición exacta de la tabla que está utilizando, sería útil.

Creé un script python simple para producir datos de prueba y ejecuté varias versiones diferentes de la consulta delete contra el mismo conjunto de datos. Aquí están mis definiciones de 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.5 k de los cuales también estaban en a). Aquí están los resultados de los varios comandos delete. Me caí 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 ejecutaron en un Intel Core2 quad-core 2.5 GHz, 2GB RAM con Ubuntu 8.10 y MySQL 5.0. Tenga en cuenta que la ejecución de una instrucción sql sigue siendo un subproceso único.

Actualización:

Actualizé 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 conjunto de caracteres (no funcionaba - no cavé en él).

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 reviso las mismas pruebas con 100k filas en a y 25k filas en b (y repoblando entre corridas).

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á ni cerca de la marca de tres minutos.

Otra cosa que puede que desee ver es mover el campo longtext al final del esquema. Me parece recordar que MySQL funciona mejor si todos los campos de tamaño restringido están primero y texto, blob, etc. están al final.

Prueba esto:

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

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

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 copia de seguridad. La cascada de consultas se realiza en mi máquina lenta en 5 segundos. Puede reemplazar la inserción en seleccionar por su propia consulta de filtro.

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

Estás haciendo tu subconsulta en 'b' para cada fila en 'a'.

Intenta:

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

Prueba esto:

DELETE QUICK A.* FROM A,B WHERE A.ID=B.ID

Es mucho más rápido que las consultas normales.

Refiérase a la sintaxis: http://dev.mysql.com/doc/refman/5.0/en/delete.html

Sé que esta pregunta se ha resuelto 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 en mi experiencia es mejor 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 dure cada la consulta del mutador se ejecuta, peor sería el retraso (la replicación es un subproceso único).

Así que, aquí está: haga una SELECCIÓN primero, como una consulta separada, recordando los ID devueltos en su script/aplicación, luego continúe eliminando en lotes (digamos, 50,000 filas a la vez). Esto logrará lo siguiente:

• cada una de las instrucciones delete no bloqueará la tabla durante demasiado tiempo, por lo que no dejará que la replicación se salga de control. Es especialmente importante si confía en su replicación para proporcionarle datos relativamente actualizados. El beneficio de usar lotes es que si encuentra que cada consulta de ELIMINACIÓN aún toma demasiado tiempo, puede ajustarla para que sea más pequeña sin tocar ninguna estructura de BD.
• otro beneficio de usar un SELECT separado es que el SELECT en sí puede tardar mucho tiempo en ejecutarse, especialmente si no puede usar los mejores índices de BD por cualquier razón. Si el SELECT es interno de un DELETE, cuando la instrucción completa migra a los esclavos, se tendrá que SELECCIONAR todo de nuevo, potencialmente, quedando los esclavos porque tiene que hacer el largo seleccionar todo de nuevo. Slave lag, de nuevo, sufre mucho. Si utiliza una consulta de SELECCIÓN separada, este problema desaparece, ya que todo lo que está pasando es una lista de ID.

Avísame si hay algún error en mi lógica.

Para más discusión sobre el retraso de replicación y formas de combatirlo, similar a este, ver MySQL Slave Lag (Delay) Explained Y 7 Maneras De Combatirlo

P.d. Una cosa a tener en cuenta es, por supuesto, las posibles ediciones de la tabla entre los tiempos en que la SELECCIÓN termina y elimina el inicio. Le dejaré manejar dichos detalles mediante el uso de transacciones y / o lógica pertinente a su aplicación.

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

Tal vez debería reconstruir los indicies antes de ejecutar una consulta hugh. Bueno, deberías reconstruirlos periódicamente.

REPAIR TABLE a QUICK;
REPAIR TABLE b QUICK;

Y luego ejecutar cualquiera de las consultas anteriores (es decir,)

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

La consulta en sí ya está en una forma óptima, la actualización de los índices hace que toda la operación tome ese tiempo. Podrías desactivar las teclas en esa tabla antes de la operación, eso debería acelerar las cosas. Puede volver a encenderlos en un momento posterior, si no los necesita de inmediato.

Otro enfoque sería agregar una columna de bandera 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, también, no es lo que desea, puede echar un vistazo a lo que los documentos de mysql tienen que decir sobre la velocidad de las sentencias delete.

Por cierto, después de publicar lo anterior en mi blog, Baron Schwartzde Percona me llamó la atención que su maatkit ya tiene una herramienta solo para este propósito: mk - archiver. http://www.maatkit.org/doc/mk-archiver.html .

Es probablemente la mejor herramienta para el trabajo.

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 clave externa o los índices son las razones de su consulta extremadamente lenta.

Intenta

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

Esto deshabilitaría las comprobaciones de la clave foránea. Desafortunadamente no se puede desactivar (al menos no se como) las actualizaciones de claves con una tabla InnoDB. Con una mesa MyISAM podrías hacer algo como

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

En realidad no probé si estos la configuración afectaría la duración de la consulta. Pero vale la pena intentarlo.

Conecte la base de datos usando terminal y ejecute el comando a continuación, mire el tiempo de resultado de cada uno de ellos, encontrará que los tiempos de eliminar 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 borrar 100 mil registros. Entonces, excepto para encontrar una manera de eliminar registros más rápido, hay algunos métodos indirectos.

1, Podemos cambiar el nombre de la table_name a table_name_bak, y luego seleccionar registros desde table_name_bak a table_name.

2, Para eliminar 10000 registros, podemos eliminar 1000 registros 10 veces. Hay un ejemplo de script ruby 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

La técnica básica para eliminar múltiples filas de MySQL en una sola tabla a través del campo 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