SQL allgemeine Engine Fragen

[Domi83]

Hallo Leute, ich habe da mal eine kleine Frage zu den Unterschieden MyISAM und InnoDB..
Ich habe hier gelesen, wo die Unterschiede dieser Varianten sind. Da ich für die Firma eine Buchungsmaske baue, wo online eine Versicherung gebucht werden kann, bin ich ein wenig am grübeln.

Es geht um folgendes, aktuell haben die Tabellen in der Datenbank das MyISAM Format, was laut der genannten Seite schneller bei INSERT und UPDATE aber dafür langsamer im SELECT ist. Vorteil bei InnoDB wäre allerdings die referentielle Integrität.

Beispiel: eine Versicherung (Tabelle A), mehrere versicherte Personen (Tabelle B)

Ein weiterer Vorteil ist auch das man Schreibzugriffe auf Datensätze (ich nehme mal an, dass nicht Felder, sondern ganze Zeilen gemeint sind) gesperrt werden können. Wenn ich das verfahren nun richtig verstanden habe, könnte ich ja nun mit InnoDB mir eine Auto_Increment ID sichern, den Datensatz schon einmal sichern, und hätte die ID zur Hand, um die 1 zu N Beziehung besser gestalten zu können, richtig?

Ich vermute mal, dass der Vorteil von InnoDB sich auch am Ende im Backend bemerkbar macht, da SELECT ja schneller arbeitet. Wenn ich dann eine Übersicht öffne mit z.B. 10000 Verträge, würde ein MyISAM bestimmt wesentlich länger brauchen zum Öffnen..

Kann man mir mal grob erklären, wie ich mir die IDs im Vorfeld sichern kann? Oder ist mein Gedankengang falsch?

Gruß, Domi

 

[ice-breaker]

ganz ehrlich? der verlinkte Artikel ist großer Humbug und basiert einfach nur auf Hören-Sagen. Ich unterstelle dem Autor ganz direkt, dass er eigentlich keine Ahnung von dem hatte, von dem er schrieb, sondern einfach nur Informationen aus dem Internet zusammengetragen hat.

Es geht um folgendes, aktuell haben die Tabellen in der Datenbank das MyISAM Format, was laut der genannten Seite schneller bei INSERT und UPDATE aber dafür langsamer im SELECT ist. Vorteil bei InnoDB wäre allerdings die referentielle Integrität.

Das war vor vielen vielen vielen Jahren mal so, heute hat InnoDB das viel ausgereiftere Modell mit Festplatten-I/O umzugehen.
Es gibt leider aber keine aktuellen guten Benchmarks dazu, 99% der MyISAM vs. InnoDB Benchmarks sind absoluter Blödsinn.

Ein weiterer Vorteil ist auch das man Schreibzugriffe auf Datensätze (ich nehme mal an, dass nicht Felder, sondern ganze Zeilen gemeint sind) gesperrt werden können. Wenn ich das verfahren nun richtig verstanden habe, könnte ich ja nun mit InnoDB mir eine Auto_Increment ID sichern, den Datensatz schon einmal sichern, und hätte die ID zur Hand, um die 1 zu N Beziehung besser gestalten zu können, richtig?

Bei MyISAM wird für einen Schreibzugriff auf eine Tabelle die ganze Tabelle für Lese- und Schreibanfragen gesperrt. Bei InnoDB kann weiterhin gelesen werden (Repeatable Read) und auch weiterhin geschrieben werden, einzig der gleiche Datensatz kann nicht gleichzeitig beschrieben werden.

Ich vermute mal, dass der Vorteil von InnoDB sich auch am Ende im Backend bemerkbar macht, da SELECT ja schneller arbeitet. Wenn ich dann eine Übersicht öffne mit z.B. 10000 Verträge, würde ein MyISAM bestimmt wesentlich länger brauchen zum Öffnen..

Man kann mit beiden effiziente Anwendungen bauen, mit MyISAM ist es eben etwas schwerer. Du hast mit InnoDB eben mehr Möglichkeiten, so z.B. geclustered Indexe, um das maximale aus Leseanfragen herauszuholen, wofür die Inserts langsamer sind. Oder eben das normale auto_increnment.
Eines solltest du auf jedenfall nicht vergessen: Stelle einen Server hin, lass dauerhaft in eine MyISAM-Tabelle schreiben und ziehe das Stromkabel (simuliert Hardwareausfall), deine MyISAM-Tabelle ist garantiert defekt, denn MyISAM hat keinerlei Vorkehrungen, deine Daten wirklich sicher zu halten, InnoDB schon.

Ich kann dir nur eines ans Herz legen: Geh auf Amazon.de und bestell dir die englische Ausgabe von "High Performance MySQL", entweder die aktuelle 2. Ausgabe oder warte noch die 3. Wochen auf die 3. Ausgabe. Das Buch stammt von den Leuten, die bei Percona arbeite, das ist DIE Firma, wenn es um MySQL-Consulting oder Entwicklung geht. Da erhälst du in dem Buch wenigstens echte Infos und nicht irgendein minderwertiges Geschreibsel von irgendwem aus dem Netz, eine Gegenüberstellung der Storage Engines ist am Anfang auch enthalten.

Kann man mir mal grob erklären, wie ich mir die IDs im Vorfeld sichern kann? Oder ist mein Gedankengang falsch?

Der Gedankengang ist richtig. Mit der referentiellen Integrität kannst du gewährleisten, dass der Datensatz in der Tabelle mit der ID 1 auf den du verweist existiert und nicht gelöscht werden kann, bevor nicht auch dein Datensatz, der darauf verweist, gelöscht wird. MyISAM kann dir solche Garantien nicht geben.


Von der Nutzung von MyISAM ist heute einfach abzuraten (vor allem auch oben wegen dem Serverausfall-Beispiel), mit MySQL 5.5 wurde auch MyISAM als Default-Engine abgelöst und InnoDB ist die neue Default-Engine.




Edit: Ich habe direkt von Oracle einen Benchmark gefunden, da dort Sysbench als Benchmarking-Platform genutzt wurde, und nicht so dämliche Tests wie in den meisten Blogs und zudem in dem Paper auf die Tuning-Optionen von InnoDB eingegangen wird, gehe ich stark davon aus, dass die Ergebnisse repräsentativ sind. Beachtenswert ist, dass InnoDB so eingestellt wurde, dass Daten nur alle Sekunde auf die Festplatte geflusht werden, statt wie bei MyISAM bei jeder Operation. Dabei fällt das Ergebnis natürlich zu Gunsten von InnoDB aus, aber die meisten Setups operieren genau in dem Modus.

 

[Domi83]

Alles klar, ich bedanke mich schon mal für die ausführliche Aufklärung. Ich hätte das vor einem Jahr schon mal raus suchen sollen. Ich hatte mir über die Engine nie Gedanken gemacht, aber das da solche Aspekte mit spielen, hätte ich nicht gedacht. Insbesondere die Geschichte mit der Ausfallsicherheit.

Ich werde das mal mit meinem Kollegen durch gehen, damit wir step by step mal unsere MyISAM umstellen und später nur noch InnoDB verwenden. Ist zwar nicht zu vergleichen, aber ich habe mir auch angewöhnt immer UTF8 zu verwenden.. dann sollte das mit InnoDB auch gehen

Was den Tipp mit dem Buch angeht, das klingt auf jeden Fall gut. Jetzt kommt dann allerdings mein gebrochenes Englisch durch. Aber ich behalte es im Hinterkopf

Gruß, Domi

 

[ice-breaker]

Selbst mit gebrochenem Englisch solltest du es lesen, oder den Chef überzeugen, dass er es jedem Mitarbeiter besorgt.
Jetzt wirklich ohne Scherz, was die Bibel für das Christentum ist, ist dieses Buch für MySQL. Das ist vollgepackt mit Informationen, die ich mir über Jahre angelesen habe, mir sind damals die Augen aus dem Kopf gefallen, als ich die 1. Ausgabe in der Hand hatte, weil ich solch einen Detailgrad in noch keinem Buch gesehen habe.
Und du wirst das Buch 20 mal lesen können und immernoch "neue Dinge" entdecken, weil du einiges davon wieder vergessen hast, das Buch ist kein typisches Blabla-Buch sondern wirklich randvoll gepackt mit Infos.

Sollte dein Englisch nicht ausreichen, kannst du auch die deutsche 1. Auflage lesen, die ist zwar mitlerweile ziemlich alt und hat deutlich weniger Infos als die 2. und 3. Auflage, ist aber trotzdem gut. Ich habe beide Auflagen im Regal und die 3. schon vorbestellt...

 

[Domi83]

Okay, dann werde ich wohl in den sauren Apfel beißen und das Buch in EN besorgen. Denn wir wollen ja ein wenig mit der Zeit gehen. Vielleicht kann ich dadurch ja auch mein Englisch noch ein wenig auffrischen

Kleine Frage noch so am Rand.. zur Zeichenkodierung, was bevorzugst Du?
Im Moment verwende ich "UTF8_general_ci" aber der eine oder andere verwendet gerne "UTF8_bin", dass hat was mit der Sortierung und Zuordnung zu tun. Bei dem einen oder anderen Format wird ß und ss gleich gesetzt, aber wie ist das bei den beiden Formaten, was wäre sinnvoller?

Gruß, Domi

 

[IceMatrix]

Auf jeden fall InnoDB verwenden. Über die Performance würde ich mir bei der Anforderung aber keine allzu großen Sorgen machen. Bei "nur" zwei Tabellen, die über einen einfachen Equi-Join verbunden sind, lassen sich problemlos enorm viele Requests pro Sekunde verarbeiten. Und dein Beispiel mit 10000 Verträgen... naja 10000 ist 1) für eine Datenbank ein mickriger Wert und 2) Ein normaler Benutzer lädt Ergebnisse immer seitenweise (z. B. 100 pro Seite) und selten 10000 Ergebnisse auf einmal.

 

[ice-breaker]

Kleine Frage noch so am Rand.. zur Zeichenkodierung, was bevorzugst Du?
Im Moment verwende ich "UTF8_general_ci" aber der eine oder andere verwendet gerne "UTF8_bin", dass hat was mit der Sortierung und Zuordnung zu tun. Bei dem einen oder anderen Format wird ß und ss gleich gesetzt, aber wie ist das bei den beiden Formaten, was wäre sinnvoller?

nimm was dir am besten passt.

Auf jeden fall InnoDB verwenden. Über die Performance würde ich mir bei der Anforderung aber keine allzu großen Sorgen machen.

jup sehe ich auch so, da beschäftigt man sich mit, wenn man die Probleme hat.
Und wer wirklich gewaltige Performance braucht, der nimmt Handlersocket und eliminiert damit jegliches SQL, und schwups ist InnoDB gleichauf mit den ganzen NoSQL-Datenbanken, jedoch mit einigen Unterschieden: InnoDB ist über Jahre gereift und deutlich stabiler als jede frische NoSQL-Datenbank und unterstützt ACID.

Und dein Beispiel mit 10000 Verträgen... naja 10000 ist 1) für eine Datenbank ein mickriger Wert und 2) Ein normaler Benutzer lädt Ergebnisse immer seitenweise (z. B. 100 pro Seite) und selten 10000 Ergebnisse auf einmal.

Jup und das seitenweise Blättern kann man auch optimal optimieren: kein LIMIT x,y sondern sich merken welches z.B. der letzte Forenpost (auf eine Forensoftware bezogen) auf der Seite war und dann eben URLs nach dem Muster

read.php?topic=1337&lastpost=400

und im Query dann eben

WHERE topic = 1337 AND postid > 400 ORDER BY postid ASC LIMIT 10

Das ganze noch mit nem geclusterten PK auf (topic, postid) und es ist die effizienteste Forensoftware, die ich je gesehen habe, aber das ist auch echt kein Problem

Hmm, vllt sollte ich mal so nen 60 seitiges Buch schreiben "Real-World MySQL-Optimierung"

 

[Domi83]

He he.. okay, dann habe ich das ja schon mal.
Das mit den 10.000 Verträgen sollte nur eine Zahl sein. Irgendwann sollen es ja mal mehr als 10.000 sein, da wären dem Chef dann schon 100.000 schon eine schönere Zahl, aber wir wollen ja erst einmal anfangen und schauen was passiert

Ich habe mir erst einmal zwei Test-Tabellen erstellt, mit InnoDB, Fremdschlüssel und "CASCADE" Direktiven. Ich war erst einmal erstaunt, dass die Tabelle selbst größer war als die MyISAM Tabelle inklusive Daten, aber egal. Speicher ist genügend vorhanden..

Was die Char-sets und die Sortierung angeht, dachte ich das es da noch etwas besonderes zu beachten gibt. Daher hatte ich das kurz in den Raum geworfen. Aber gut, dann habe ich die Info auch schon mal.

Gruß, Domi