mysql - zusammenfassen - sql update mehrere zeilen gleichzeitig




Schneller Weg um passende Zeilen zu löschen? (10)

BTW, nachdem ich das oben auf meinem Blog gepostet habe, hat mich Baron Schwartz aus Percona darauf aufmerksam gemacht, dass sein maatkit bereits ein Tool maatkit für diesen Zweck hat - mk-archiver. http://www.maatkit.org/doc/mk-archiver.html .

Es ist wahrscheinlich das beste Werkzeug für den Job.

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


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!


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.


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


Löschen von Daten aus InnoDB ist die teuerste Operation, die Sie anfordern können. Wie Sie bereits festgestellt haben, ist die Abfrage selbst nicht das Problem - die meisten von ihnen werden sowieso für den gleichen Ausführungsplan optimiert.

Obwohl es schwer zu verstehen ist, warum DELETEs in allen Fällen am langsamsten sind, gibt es eine recht einfache Erklärung. InnoDB ist eine transaktionale Speicher-Engine. Das bedeutet, dass wenn Ihre Abfrage abgebrochen wurde, alle Datensätze noch vorhanden sind, als ob nichts passiert wäre. Sobald es abgeschlossen ist, werden alle im selben Augenblick verschwunden sein. Während des DELETE-Vorgangs werden anderen Clients, die eine Verbindung mit dem Server herstellen, die Datensätze angezeigt, bis DELETE abgeschlossen ist.

Um dies zu erreichen, verwendet InnoDB eine Technik namens MVCC (Multi Version Concurrency Control). Im Grunde gibt es für jede Verbindung eine Momentaufnahme der gesamten Datenbank, so wie sie war, als die erste Anweisung der Transaktion gestartet wurde. Um dies zu erreichen, kann jeder Datensatz in InnoDB intern mehrere Werte haben - einen für jeden Snapshot. Dies ist auch der Grund, warum COUNTing bei InnoDB einige Zeit in Anspruch nimmt - es hängt vom Momentaufnahmezustand ab, den Sie zu diesem Zeitpunkt sehen.

Für Ihre DELETE-Transaktion wird jeder Datensatz, der gemäß Ihren Abfragebedingungen identifiziert wird, zum Löschen markiert. Da andere Clients möglicherweise gleichzeitig auf die Daten zugreifen, können sie sie nicht sofort aus der Tabelle entfernen, da sie ihren jeweiligen Snapshot sehen müssen, um die Atomarität des Löschens zu gewährleisten.

Sobald alle Datensätze zum Löschen markiert wurden, wird die Transaktion erfolgreich festgeschrieben. Und selbst dann können sie nicht sofort von den eigentlichen Datenseiten entfernt werden, bevor alle anderen Transaktionen, die vor der DELETE-Transaktion mit einem Snapshot-Wert gearbeitet haben, ebenfalls beendet wurden.

In der Tat sind Ihre 3 Minuten nicht wirklich so langsam, wenn man bedenkt, dass alle Datensätze modifiziert werden müssen, um sie auf sichere Weise für die Entfernung vorzubereiten. Wahrscheinlich werden Sie hören, wie Ihre Festplatte funktioniert, während die Anweisung ausgeführt wird. Dies wird durch den Zugriff auf alle Zeilen verursacht. Um die Leistung zu verbessern, können Sie versuchen, die Pufferpoolgröße von InnoDB für Ihren Server zu erhöhen und anderen Zugriff auf die Datenbank zu beschränken, während Sie DELETE löschen. Dadurch verringert sich auch die Anzahl der historischen Versionen, die InnoDB pro Datensatz verwalten muss. Mit dem zusätzlichen Speicher kann InnoDB Ihre Tabelle (meistens) in den Speicher einlesen und so Zeit für die Festplattensuche sparen.


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.


Sie tun Ihre Unterabfrage auf 'b' für jede Zeile in 'a'.

Versuchen:

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

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

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)

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




sql-execution-plan