mysql zusammenfassen Schneller Weg um passende Zeilen zu löschen?




sql update mehrere zeilen gleichzeitig (12)

Die grundlegende Technik zum Löschen mehrerer Zeilenform MySQL in einer Tabelle durch das ID-Feld

DELETE FROM tbl_name WHERE id <= 100 AND id >=200; Diese Abfrage ist verantwortlich für das Löschen der übereinstimmenden Bedingung zwischen 100 UND 200 aus der bestimmten Tabelle

Ich bin ein relativer Neuling, wenn es um Datenbanken geht. Wir verwenden MySQL und ich versuche gerade, eine SQL-Anweisung zu beschleunigen, die eine Weile zu laufen scheint. Ich habe mich bei SO nach einer ähnlichen Frage umgesehen, aber keine gefunden.

Das Ziel besteht darin, alle Zeilen in Tabelle A zu entfernen, die eine übereinstimmende ID in Tabelle B haben.

Ich mache derzeit folgendes:

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

Es gibt ungefähr 100.000 Zeilen in Tabelle a und ungefähr 22.000 Zeilen in Tabelle b. Die Spalte 'id' ist die PK für beide Tabellen.

Diese Anweisung dauert ungefähr 3 Minuten, um auf meiner Testbox zu laufen - Pentium D, XP SP3, 2GB RAM, MySQL 5.0.67. Das scheint mir langsam. Vielleicht nicht, aber ich hatte gehofft, die Dinge zu beschleunigen. Gibt es einen besseren / schnelleren Weg, dies zu erreichen?

BEARBEITEN:

Einige zusätzliche Informationen, die hilfreich sein könnten. Die Tabellen A und B haben die gleiche Struktur wie ich die Tabelle B erstellt habe:

CREATE TABLE b LIKE a;

Tabelle a (und somit Tabelle b) enthält einige Indizes, um Abfragen zu beschleunigen, die dagegen ausgeführt werden. Wieder bin ich ein relativer Neuling bei der DB Arbeit und lerne immer noch. Ich weiß nicht, wie viel Wirkung das auf Dinge hat. Ich nehme an, dass es einen Effekt hat, da die Indizes auch aufgeräumt werden müssen, oder? Ich habe mich auch gefragt, ob es andere DB-Einstellungen gibt, die die Geschwindigkeit beeinflussen könnten.

Außerdem benutze ich INNO DB.

Hier sind einige zusätzliche Informationen, die für Sie hilfreich sein könnten.

Tabelle A hat eine ähnliche Struktur (ich habe das ein wenig bereinigt):

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;

Ich vermute, dass ein Teil des Problems darin besteht, dass es eine Reihe von Indizes für diese Tabelle gibt. Tabelle B sieht ähnlich wie Tabelle B aus, enthält jedoch nur die Spalten id und h .

Außerdem sind die Profilergebnisse wie folgt:

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

Gelöst

Danke an alle Antworten und Kommentare. Sie haben mich bestimmt dazu gebracht, über das Problem nachzudenken. Kudos an dotjoe, dass sie mich von dem Problem abbringen , indem sie die einfache Frage stellen: "Sprechen irgendwelche anderen Tabellen auf a.id?"

Das Problem bestand darin, dass es in Tabelle A einen DELETE-TRIGGER gab, der eine gespeicherte Prozedur zum Aktualisieren von zwei anderen Tabellen, C und D, anwendete. Tabelle C hatte einen FK zurück zu a.id und nachdem einige mit dieser ID in Zusammenhang stehende Sachen in der gespeicherten Prozedur ausgeführt wurden Es hatte die Aussage,

DELETE FROM c WHERE c.id = theId;

Ich schaute in die Erklärung von EXPLAIN und schrieb das als

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

Also, ich konnte sehen, was das tat und es gab mir die folgenden Informationen:

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

Dies sagte mir, dass es eine schmerzhafte Operation zu machen sei und da es 22500 mal aufgerufen werden würde (weil die gegebene Datenmenge gelöscht wurde), war das das Problem. Sobald ich einen INDEX in dieser Spalte "other_id" erstellt und die EXPLAIN wiederholt habe, bekam ich:

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

Viel besser, wirklich großartig.

Ich habe hinzugefügt, dass Index_1 und meine Löschzeiten den von mattkemp angegebenen Zeiten entsprechen . Das war ein wirklich subtiler Fehler meinerseits, weil ich in letzter Minute einige zusätzliche Funktionen hatte. Es stellte sich heraus, dass die meisten der vorgeschlagenen alternativen DELETE / SELECT-Anweisungen, wie Daniel sagte, im Wesentlichen die gleiche Menge an Zeit einnahmen und wie soeben erwähnt, war die Aussage so ziemlich die beste, die ich basierend auf was konstruieren konnte Ich musste es tun. Sobald ich einen Index für diese andere Tabelle C bereitgestellt hatte, waren meine DELETEs schnell.

Postmortem :
Aus dieser Übung sind zwei Lehren gezogen worden. Erstens ist es klar, dass ich die Macht der EXPLAIN-Anweisung nicht genutzt habe, um eine bessere Vorstellung von den Auswirkungen meiner SQL-Abfragen zu bekommen. Das ist ein Anfängerfehler, also werde ich mich nicht verprügeln. Ich werde von diesem Fehler lernen. Zweitens war der beleidigende Code das Ergebnis einer "Get it done quick" -Mentalität und unzureichendes Design / Testen führte dazu, dass dieses Problem nicht früher auftrat. Hätte ich mehrere umfangreiche Testdatensätze erstellt, die als Testeingabe für diese neue Funktionalität verwendet werden könnten, hätte ich weder meine Zeit noch Ihre Zeit verschwendet. Meine Tests auf der DB-Seite fehlten die Tiefe, die meine Anwendungsseite hat. Jetzt habe ich die Möglichkeit, das zu verbessern.

Referenz: EXPLAIN-Anweisung


Verbinde database mit terminal und führe den folgenden Befehl aus, schau dir jeweils die Ergebniszeit an, du wirst feststellen, dass Zeiten für das Löschen von 10, 100, 1000, 10000, 100000 Datensätzen nicht multipliziert werden.

  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;

Der Zeitpunkt des Löschens von 10.000 Datensätzen ist nicht 10-mal so lang wie das Löschen von 100.000 Datensätzen. Dann gibt es einige indirekte Methoden, außer einen Weg zu finden, Datensätze schneller zu löschen.

1, Wir können den Tabellenname in Tabellenname_Bak umbenennen und dann Datensätze aus Tabellenname_Bak in Tabellenname auswählen.

2, um 10000 Aufzeichnungen zu löschen, können wir 1000 Aufzeichnungen 10mal löschen. Es gibt ein Beispiel Ruby-Skript, um es zu tun.

#!/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

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


Ich weiß, dass diese Frage aufgrund der Indexierungsunterlassungen von OP ziemlich gelöst wurde, aber ich möchte diesen zusätzlichen Hinweis anbieten, der für einen allgemeineren Fall dieses Problems gültig ist.

Ich habe persönlich damit beschäftigt, viele Zeilen aus einer Tabelle zu löschen, die in einer anderen existieren, und meiner Erfahrung nach ist es das Beste, Folgendes zu tun, besonders wenn Sie erwarten, dass viele Zeilen gelöscht werden. Diese Technik verbessert vor allem die Replikationsverzögerung, da je länger die einzelnen Mutatorabfragen ausgeführt werden, desto schlechter die Verzögerung (Replikation ist single threaded).

Also, hier ist es: Machen Sie zuerst eine SELECT als separate Abfrage , erinnern Sie sich an die IDs, die in Ihrem Skript / Ihrer Anwendung zurückgegeben werden, und fahren Sie dann mit dem Löschen in Stapeln fort (z. B. 50.000 Zeilen gleichzeitig). Dies wird Folgendes erreichen:

  • Jede der Löschanweisungen blockiert die Tabelle nicht zu lange, sodass die Replikationsverzögerung nicht außer Kontrolle geraten kann . Dies ist besonders wichtig, wenn Sie sich auf Ihre Replikation verlassen, um Ihnen relativ aktuelle Daten zur Verfügung zu stellen. Der Vorteil der Verwendung von Stapeln besteht darin, dass Sie, wenn Sie feststellen, dass jede DELETE-Abfrage immer noch zu lange dauert, diese kleiner einstellen können, ohne DB-Strukturen zu berühren.
  • Ein weiterer Vorteil der Verwendung einer separaten SELECT-Anweisung ist, dass die Ausführung der SELECT-Anweisung selbst sehr lange dauert , insbesondere dann, wenn sie aus irgendeinem Grund nicht die besten DB-Indizes verwenden kann. Wenn SELECT in einem DELETE enthalten ist, wenn die gesamte Anweisung zu den Slaves migriert, muss sie SELECT erneut ausführen, was möglicherweise den Slaves hinterherhinkt, da sie die lange Auswahl erneut ausführen muss. Die Slave-Verzögerung leidet erneut stark. Wenn Sie eine separate SELECT-Abfrage verwenden, verschwindet dieses Problem, da Sie nur eine ID-Liste übergeben.

Lass es mich wissen, wenn irgendwo ein Fehler in meiner Logik ist.

Weitere Informationen zu Replikationsverzögerungen und Möglichkeiten, diese zu bekämpfen, finden Sie unter MySQL-Slave-Verzögerung (Verzögerung) und 7 Möglichkeiten, um gegen sie anzukämpfen

PS Eine Sache, über die man vorsichtig sein sollte, sind natürlich mögliche Änderungen an der Tabelle zwischen den Zeiten, an denen SELECT beendet und DELETEs begonnen hat. Ich werde Sie solche Details behandeln lassen, indem Sie Transaktionen und / oder Logik verwenden, die für Ihre Anwendung relevant sind.


Versuche dies:

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

Unterabfragen neigen dazu, langsamer als Joins zu sein, da sie für jeden Datensatz in der äußeren Abfrage ausgeführt werden.



Die Abfrage selbst ist bereits in einer optimalen Form, das Aktualisieren der Indizes bewirkt, dass die gesamte Operation so lange dauert. Sie könnten die Schlüssel in dieser Tabelle vor der Operation deaktivieren , was die Dinge beschleunigen sollte. Sie können sie zu einem späteren Zeitpunkt wieder einschalten, wenn Sie sie nicht sofort benötigen.

Ein anderer Ansatz wäre das Hinzufügen einer deleted Flag-Spalte zu Ihrer Tabelle und das Anpassen anderer Abfragen, sodass sie diesen Wert berücksichtigen. Der schnellste boolesche Typ in mysql ist CHAR(0) NULL (true = '', false = NULL). Das wäre eine schnelle Operation, Sie können die Werte danach löschen.

Die gleichen Gedanken in SQL-Statements ausgedrückt:

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;

Wenn das auch nicht das ist, was Sie wollen, können Sie sich ansehen, was die mysql-Dokumente über die Geschwindigkeit von delete-Anweisungen sagen müssen.


Deine Zeit von drei Minuten scheint wirklich langsam zu sein. Meine Vermutung ist, dass die ID-Spalte nicht richtig indiziert ist. Wenn Sie die genaue Tabellendefinition angeben könnten, die Sie verwenden, wäre das hilfreich.

Ich habe ein einfaches Python-Skript erstellt, um Testdaten zu erstellen, und mehrere verschiedene Versionen der Löschabfrage für denselben Datensatz ausgeführt. Hier sind meine Tabellendefinitionen:

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;

Ich habe dann 100k Zeilen in a und 25k Zeilen in b eingefügt (22,5k davon waren auch in a). Hier sind die Ergebnisse der verschiedenen Löschbefehle. Ich habe den Tisch zwischen den Läufen fallen lassen und neu bevölkert.

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)

Alle Tests wurden auf einem Intel Core2 Quad-Core 2,5 GHz, 2 GB RAM mit Ubuntu 8.10 und MySQL 5.0 durchgeführt. Beachten Sie, dass die Ausführung einer SQL-Anweisung immer noch single-threaded ist.

Aktualisieren:

Ich habe meine Tests aktualisiert, um itsmatts Schema zu verwenden. Ich habe es leicht modifiziert, indem ich die automatische Inkrementierung (ich erstelle synthetische Daten) und die Zeichensatzcodierung (funktionierte nicht - habe mich nicht damit beschäftigt) geändert habe.

Hier sind meine neuen Tabellendefinitionen:

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;

Ich wiederhole dann dieselben Tests mit 100k Zeilen in a und 25k Zeilen in b (und repopuliere zwischen Läufen).

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)

Wie Sie sehen können, ist dies ein wenig langsamer als vorher, wahrscheinlich aufgrund der mehrfachen Indizes. Es ist jedoch nicht annähernd die Drei-Minuten-Marke.

Etwas anderes, das Sie vielleicht betrachten möchten, ist, das Longtext-Feld an das Ende des Schemas zu verschieben. Ich glaube mich zu erinnern, dass mySQL besser abschneidet, wenn alle Felder mit beschränkter Größe zuerst sind und Text, Blob usw. am Ende stehen.


Vielleicht sollten Sie die Indizes neu erstellen, bevor Sie eine solche große Abfrage ausführen. Nun, Sie sollten sie regelmäßig neu aufbauen.

REPAIR TABLE a QUICK;
REPAIR TABLE b QUICK;

und dann eine der obigen Abfragen ausführen (dh)

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

Offensichtlich ist die SELECT Abfrage, die die Grundlage Ihrer DELETE Operation bildet, ziemlich schnell, so dass ich denke, dass entweder die Fremdschlüsseleinschränkung oder die Indizes die Gründe für Ihre extrem langsame Abfrage sind.

Versuchen

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

Dies würde die Überprüfung des Fremdschlüssels deaktivieren. Leider können Sie die Schlüssel-Updates mit einer InnoDB-Tabelle nicht deaktivieren (zumindest weiß ich nicht). Mit einer MyISAM-Tabelle können Sie so etwas tun

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

Ich habe tatsächlich nicht getestet, ob diese Einstellungen die Abfragedauer beeinflussen. Aber es ist einen Versuch wert.


Das mache ich immer dann, wenn ich mit supergroßen Daten arbeiten muss (hier: eine Beispieltesttabelle mit 150000 Zeilen):

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;

In diesem Fall filtert der SQL-Server 50000 Zeilen in die Backup-Tabelle. Die Abfragekaskade wird in 5 Sekunden auf meiner langsamen Maschine ausgeführt. Sie können die Einfügung in select durch Ihre eigene Filterabfrage ersetzen.

Das ist der Trick, um eine Massenlöschung in großen Datenbanken durchzuführen!





sql-execution-plan