Jan 05

MySQL Multi Update

Tag: Allgemein,EntwicklungPhoscur @ 16:56

Ich traue mich jetzt einfach mal einen Artikel über Performance zu verfassen. Ein paar Leute meinten schon ich würde mir viel zu viel Sorgen über Performance und Optimisation machen, dabei möchte ich es doch nur gleich beim ersten Anlauf richtig machen.

Multi Update: Mehrere Datensätze einer Tabelle haben sich geändert und sollen aktualisiert werden. Anstatt nun jede Menge einzelne Queries zu schreiben gibts es nun zwei Methoden dies möglichst schnell ablaufen zu lassen.

1. Die CASE Methode

UPDATE test

SET bla = CASE id

WHEN 1 THEN ‚flubb‘

WHEN 2 THEN ‚blubb‘

END

WHERE id IN (1,2)

Innerhalb eines einzigen Querys werden mehrere Einträge verändert.

2. Die PREPARED Methode

Zuerst wie gewünscht eine kleine Einführung in mysqli bzw. meine Datenbankabstraktion:

Link (englisch) Wer mysqli oder PDO noch nicht kennt sollte sich umbedingt informieren!

$stmt = $db->prepare(‚UPDATE test SET bla = ? WHERE id = ?, ’sd‘);

$stmt->execute(array(‚flubb‘, 5));

$stmt->execute(array(‚blubb‘, 13));

Ein Query wird mit Platzhaltern gefüllt und mehrmals ausgeführt.

Performancevergleich

Ich habe das nun mit zwei Einträgen getestet und 100 bis 10000 Einträge verändert.

Interessantes:

  • CASE  skaliert sehr schlecht, bei mehr Arbeit braucht das Query vergleichsweise viel länger
  • Mein MySQL Client meldet sich ab, wenn ich 10000 Einträge mit CASE updaten will
  • PREPARED skaliert sehr gut, bei doppelter Anzahl Einträge brauchen die Queries doppelt so lange
  • Bei unter 1000 Einträgen ist CASE schneller als PREPARED
  • Für 10000 Einträge braucht PREPARED weniger lange als CASE für 5000

Schlussfolgerung:

CASE und PREPARED haben verschiedene Vor- und Nachteile. Für wenige Einträge (<1000 Felder upzudaten) bietet sich CASE an. Für große Datenmengen sollte PREPARED verwendet werden.

Ich werde CASE verwenden, zumal ich schon den nötigen Code geschrieben habe um automatisch Queries zu erzeugen.

** Anmerkung: Die Tests wurden auf meinem Laptop (C2D 2×2,4Ghz, 2GB RAM) ausgeführt und sind nur subjektiv. Ich denke dennoch, dass die Ergebnisse auf anderen Systemen durchaus ähnlich ausfallen werden.

6 Kommentare zu “MySQL Multi Update”

  1. Big Mäc schrieb:

    Erklär doch vielleicht mal was prepared statements sind bzw. was die machen. (gibt leute die kennen mysqli net) ^^
    Daraus kann man sicherlich auch noch weitere Sachen folgern, z.B. dass es (vermutlich) etwas unklug sein dürfte, die prepared-variante zu nehmen, falls die datenbank auf einem anderen rechner ist und man wirklich so viele verschiedene updates machen will 😉

    Und aus dem zweiten Beispiel wird man auch nicht so wirklich schlau, wenn man deine (mysqli-)Datenbankklasse nicht kennt.

  2. Phoscur schrieb:

    Ookay. Seh ich ein, morgen oder die Tage kommt ein Update mit ausholenden Erklärungen. Danke für den Anstoß.

  3. wegr xD oder so schrieb:

    Dein 2. Beispiel gibt für mich wenig sinn, da doch zuerst mittels bind() die Fragezeichen „ersetzt“ werden. Nach dem 1. Execute sollte eigentlich ein Fehler kommen, da nur ein Fragezeichen ausgefüllt ist, oder?^^

  4. Phoscur schrieb:

    Nein bind($param1[, $param2..]) Setzt alle Parameter ein. Das scheint ein Missverständnis zu sein, auf das du gestoßen bist, weil du – wie Big Mäc schon schrieb – die Datenbankklasse nicht kennst. Ich dachte das könnte man sich denken…

    Edit: Beitragsupdate.

  5. brian schrieb:

    und jetzt noch InnoDB + eine Transaktion, dann muss nicht die ganze zeit (f)SYNChronisiert werden.

  6. JÜRGEEEEEN schrieb:

    Ich möchte nur kurz das Zauberwort „nested sets“ einwerfen, prepared Statements kenn ich nur aus sicherheitsaspekten, da damit das Quoten von den einzelnen Parametern wegfällt

Dein Kommentar

Du musst eingelogt sein, um einen Kommentar zu schreiben.