Mysql Eine Tabellen auf mehrer verteilen, atomar 1:n

[Tim]

Hallo,

ich versuche mich gerade an der Optimierung einer Tabellenstruktur. Dabei fand ich hier im Forum viele Hinweise, wie man es atomar machen sollte. Gut, ich verstehe das alles noch nicht so ganz und meine Experimente sind doch sehr, sagen wir mal, amateurhaft.

Bisher war es so:
Eine Tabelle:

id | land  |Highlights | comments | usw...

Higlights sind Sehenswürdigkeiten
Comments sind Kommentare
jeweils mit Trennzeichen versehen um einzelne Werte daraus darzustellen.

Das funktioniert sehr gut aber natürlich mit enorm viel unnötigem Ballast für die DB.

Nun der neue Ansatz: mind. 4 Tabellen

Tab1:
id | land

Tab2:
id | highlights

Tab3:
id | comments

Tab4:
landID | highlightsID

Das würde jetzt bedeuten ich kann alle Sehenswürdigkeiten in Tabelle 4 eintragen und durch die LandID dem jeweiligen Land aus Tabelle 1 zuordnen. Soweit so gut, in der Praxis aber habe ich einen Blackout.

Ich schaffe es zwar bei einem neuen Eintrag sowohl Land als auch Highlight in die jeweilige Tabelle einzutragen aber für die richtige Zuordnung in Tabelle 4 benötige ich dann ja die ID's aus Tab1 und Tab2 welche durch Auto_incremet erzeugt wurden.

Klar, ich könnte jetzt mühselig wieder die Tabellen abfragen und dann die ID's auslesen um diese dann einzutragen, aber das ist doch bestimmt nicht im Sinne des Erfinders?

Ich bin schon so verzweifelt, dass ich über Trigger nachdenke obwohl ich nicht mal sicher weiss, ob das gehen würde. Wie löst Ihr so etwas?

Gruss
Tim

[dedlfix]

Tach!

Tab1:
id | land

Tab2:
id | highlights

Tab3:
id | comments

Tab4:
landID | highlightsID

Kann man denn Sehenswürdigkeiten mehreren Ländern zuordnen? Also, theoretisch ja, wenn man solche Dinge wie Gebirge als eine Sehenswürdigkeit betrachtet. Aber kommt das bei deinen Daten vor? Wenn nicht, dann Tabelle 4 streichen und in Tabelle 2 einen Fremdschlüssel zu Tabelle 1 einbauen.

Ebenso fehlt die Beziehung zwischen Kommentaren und der Sehenswürdigkeit. Das wäre ebenfalls ein Fremdschlüssel, aber zu Tabelle 2.

Ich schaffe es zwar bei einem neuen Eintrag sowohl Land als auch Highlight in die jeweilige Tabelle einzutragen aber für die richtige Zuordnung in Tabelle 4 benötige ich dann ja die ID's aus Tab1 und Tab2 welche durch Auto_incremet erzeugt wurden.

Ja, und die liefert MySQL, man muss sie nur abfragen. Dazu gibt es die MySQL-Funktion LAST_INSERT_ID(), aber meist fragt man sie über eine ähnlich benannte Funktion in der API ab (mysql(i)_insert_id() beispielsweise in PHP).

dedlfix.

[Tim]

Hallo,

Kann man denn Sehenswürdigkeiten mehreren Ländern zuordnen? Also, theoretisch ja, wenn man solche Dinge wie Gebirge als eine Sehenswürdigkeit betrachtet. Aber kommt das bei deinen Daten vor? Wenn nicht, dann Tabelle 4 streichen und in Tabelle 2 einen Fremdschlüssel zu Tabelle 1 einbauen.

Ja, ein schöner Sandstrand ist ein z.B. Highlight bei einigen Ländern

Ebenso fehlt die Beziehung zwischen Kommentaren und der Sehenswürdigkeit. Das wäre ebenfalls ein Fremdschlüssel, aber zu Tabelle 2.

Genau, es wird immer komplexer.

Ich schaffe es zwar bei einem neuen Eintrag sowohl Land als auch Highlight in die jeweilige Tabelle einzutragen aber für die richtige Zuordnung in Tabelle 4 benötige ich dann ja die ID's aus Tab1 und Tab2 welche durch Auto_incremet erzeugt wurden.

Ja, und die liefert MySQL, man muss sie nur abfragen. Dazu gibt es die MySQL-Funktion LAST_INSERT_ID(), aber meist fragt man sie über eine ähnlich benannte Funktion in der API ab (mysql(i)_insert_id() beispielsweise in PHP).

Und genau da haben wir schon das Problem:
1. Bin ich nicht sicher ob bei zeitgleichen Zugriffen diese LAST_INSERT_ID() dann auch noch tatsächlich passt. Unabhängig davon trage ich aber mehrere Werte mit einer einzigen SQL-Anweisung ein, das bedeutet ich benötige alle dadurch erzeugten ID's.

Vielleicht sollte ich doch bei meiner Single-Tabelle bleiben ;-(

[T-Rex]

Genau, es wird immer komplexer.

Ich kann dich beruhigen, die 3 Tabellen sind alles andere als komplex ;).

Und genau da haben wir schon das Problem:

1. Bin ich nicht sicher ob bei zeitgleichen Zugriffen diese LAST_INSERT_ID() dann auch noch tatsächlich passt. Unabhängig davon trage ich aber mehrere Werte mit einer einzigen SQL-Anweisung ein, das bedeutet ich benötige alle dadurch erzeugten ID's.

Und genau da haben wir schon die Lösung, teile es in mehrere SQL Anweisungen auf.
Wenn du die Sehenswürdigkeit in die Tabelle einträgst und den Schlüssel bekommst ist der eindeutig. Danach können Millionen von Eintragen in der Sekunde hinzukommen, der Schlüssel bleibt eindeutig und du kannst ihn benutzen.

Vielleicht sollte ich doch bei meiner Single-Tabelle bleiben ;-(

Ich bin zwar kein absoluter Verfechter der Normalisierungsregeln aber in diesem Fall - nein lös die Singletabelle auf! Ein einfacher Schreibfehler bei einer Sehenswürdigkeit zieht einen Update-Statement nach sich der die gesamte Datenbank unter Umständen lahmlegt (je nachdem wie viel Zeilen du erfasst hast).

Gruß
Schriebfehler verteilender
T-Rex

[dedlfix]

Tach!

Ein einfacher Schreibfehler bei einer Sehenswürdigkeit zieht einen Update-Statement nach sich der die gesamte Datenbank unter Umständen lahmlegt (je nachdem wie viel Zeilen du erfasst hast).

Was genau meinst du damit? Eine Änderung an einem einzelnen Datensatz hat nicht solche gravierenden Auswirkungen. Selbst bei seinem unglücklichen Datendesign wird es zumindest pro Sehenswürdigkeit einen Datensatz geben. Redundanzen treten nur beim Land auf. Auch wenn man da eine Änderung vornimmt, wird sich der Aufwand in Grenzen halten. Zu erwarten sind vermutlich kaum solche Datenmengen, die ein DBMS ins Schwitzen bringen werden.

dedlfix.

[T-Rex]

Was genau meinst du damit? Eine Änderung an einem einzelnen Datensatz hat nicht solche gravierenden Auswirkungen. Selbst bei seinem unglücklichen Datendesign wird es zumindest pro Sehenswürdigkeit einen Datensatz geben. Redundanzen treten nur beim Land auf. Auch wenn man da eine Änderung vornimmt, wird sich der Aufwand in Grenzen halten. Zu erwarten sind vermutlich kaum solche Datenmengen, die ein DBMS ins Schwitzen bringen werden.

Da er Kommentare auch noch in der Tabelle hat kann es schnell sehr viel werden. Nehmen wir mal an 10 Länder haben die gleiche Sehenswürdigkeit -> 10 Redundanzen. Jedes Land hat 10 Kommentare = 10 * 10 Redundanzen = 100 mal die Gleiche Sehenswürdigkeit. Das ist für die Datenbank auf jeden Fall ein Klacks (noch)!
Jetzt nehmen wir mal weiter an die Seite boomt und bekommt viele Kommentare. Dadurch würde die Tabelle (unnötig) wachsen. Selbst wenn es einfach ein "supi *Freu*" Kommentar ist, wird die Sehenswürdigkeit mit kopiert. Bei sagen wir mal 2 Millionen Datensätze kann eine Änderung von 100 Rechtschreibfehlern schon etwas dauern. Außer man hat natürlich einen Index auf das Feld gesetzt, dann dauert das Eintragen aber wiederum recht lange etc...

Ich kann aktuell nichts zur Datenmenge sagen, da ich die nicht kenne. Auch die restlichen Felder der Tabelle kenne ich nicht. Je mehr Tabellenfelder die Tabelle hat desto schneller geht sie in die Knie. Bei einer meiner Vergangenen Projekte hatten wir eine Tabelle mit vorbildlichen Index drauf, 50 Tabellenfelder und ca. 1 Millionen Datensätze. Da hat das Suchen nach einem Indexierten Datensatz an die 10 Sekunden gedauert.

Lerne an einer einfachen Struktur und baue die Datenbank so auf, dass sie von vornherein ein paar Millionen Datensätze schafft. Später das Ganze Umzubauen kann sich sehr schwierig herausstellen.

Gruß

[dedlfix]

Tach!

Da er Kommentare auch noch in der Tabelle hat kann es schnell sehr viel werden. Nehmen wir mal an 10 Länder haben die gleiche Sehenswürdigkeit -> 10 Redundanzen. Jedes Land hat 10 Kommentare = 10 * 10 Redundanzen = 100 mal die Gleiche Sehenswürdigkeit. Das ist für die Datenbank auf jeden Fall ein Klacks (noch)!

Das ist nach seinem alten Datenmodel genau ein Datensatz. Der hat aber 10 Länder und 10 Kommentare trennzeichensepariert in den entsprechenden Feldern stehen. Zumindest hat er das so für die Kommentare erwähnt.

Jetzt nehmen wir mal weiter an die Seite boomt und bekommt viele Kommentare. Dadurch würde die Tabelle (unnötig) wachsen. Selbst wenn es einfach ein "supi *Freu*" Kommentar ist, wird die Sehenswürdigkeit mit kopiert.

Dann bleibt es weiterhin ein Datensatz. Nur das Kommentarfeld wird länger.

Bei sagen wir mal 2 Millionen Datensätze kann eine Änderung von 100 Rechtschreibfehlern schon etwas dauern.

Nicht länger als ein paar Sekündchen, wenn überhaupt. Selbst ohne Index ist das einmal durch die Tabelle rödeln, fertig. Und es ist keine Aktion, die ständig durchzuführen ist. Kritischer sind da häufige Abfragen, die eine große Datenmenge erzeugen (auch wenn das nur vorübergehend und intern ist), zum Beispiel Joins bei denen die Datenmenge durch Bedingungen nicht genügend klein gehalten werden kann.

Ich kann aktuell nichts zur Datenmenge sagen, da ich die nicht kenne. Auch die restlichen Felder der Tabelle kenne ich nicht. Je mehr Tabellenfelder die Tabelle hat desto schneller geht sie in die Knie. Bei einer meiner Vergangenen Projekte hatten wir eine Tabelle mit vorbildlichen Index drauf, 50 Tabellenfelder und ca. 1 Millionen Datensätze. Da hat das Suchen nach einem Indexierten Datensatz an die 10 Sekunden gedauert.

Das sagt nicht viel aus. Wurde denn der Index überhaupt verwendet? Konnte er überhaupt verwendet werden? Bei LIKE zum Beispiel kann er nur verwendet werden, wenn man foo% sucht. Bei %foo oder %foo% kann ein einfacher (MySQL-)Index aus Prinzip nicht helfen und kann dann auch nicht mehr als vorbildlich bezeichnet werden.

Lerne an einer einfachen Struktur und baue die Datenbank so auf, dass sie von vornherein ein paar Millionen Datensätze schafft. Später das Ganze Umzubauen kann sich sehr schwierig herausstellen.

Naja, man darf auch von Anfang an mit realistischen Mengen planen und muss nicht gleich einen Cluster aufsetzen. Das ist ein Abwägung zwischen einem ständigen Arbeiten mit einer für zu große Anforderungen dimensionierten Struktur und einer einmaligen Umbauaktion. Es ist auch eine Geldfrage, ob man ein gleich Supersystem bezahlen will oder erst später einen Umbau, falls nötig. Zuviel des Guten ist nicht in jedem Falle wunderbar.

dedlfix.

[T-Rex]

Das ist nach seinem alten Datenmodel genau ein Datensatz. Der hat aber 10 Länder und 10 Kommentare trennzeichensepariert in den entsprechenden Feldern stehen. Zumindest hat er das so für die Kommentare erwähnt.

Tatsache :(.

Naja, man darf auch von Anfang an mit realistischen Mengen planen und muss nicht gleich einen Cluster aufsetzen.

Ja richtig und einen Datenbankexperten mit einem Jahresgehalt von 100.000 Euro aufwärts braucht es da auch nicht.
Aber wenn man mit einfachen Mitteln eine möglichst flexible Struktur die nach oben hin offen ist bekommt - wieso nicht. Außerdem scheint es so als ob der Kandidat dadurch noch was lernen würde.

Gß
Minimalist
Tx

[dedlfix]

Tach!

Ja, und die liefert MySQL, man muss sie nur abfragen. Dazu gibt es die MySQL-Funktion LAST_INSERT_ID(), aber meist fragt man sie über eine ähnlich benannte Funktion in der API ab (mysql(i)_insert_id() beispielsweise in PHP).

1. Bin ich nicht sicher ob bei zeitgleichen Zugriffen diese LAST_INSERT_ID() dann auch noch tatsächlich passt.

Dann befrag das MySQL-Handbuch, was es zu dieser Funktion sagt. (Antwort: Es ist ein sessionindividueller Wert, der nicht von anderen Vorgängen beeinflusst wird. Es wäre sonst reichlich sinnlos, solch einen Wert anzubieten, wenn er diese gravierenden Nebenwirkungen hätte.)

Unabhängig davon trage ich aber mehrere Werte mit einer einzigen SQL-Anweisung ein, das bedeutet ich benötige alle dadurch erzeugten ID's.

Mehrere Werte sind kein Problem, ein Insert für mehrere Datensätze allerdings schon. Wenn du den Schlüssel brauchst, musst du darauf verzichten und nur einen Datensatz pro Insert abschicken.

Alternativ kannst du dich auch selbst um die Schlüsselerzeugung kümmern, dann hast du den Wert bereits vorligen, bevor du mit dem DBMS sprichst. Der Schlüssel muss nur eindeutig sein, weswegen sich simple Zahlen weniger gut eignen. GUID/UUID wäre etwas eindeutiges, aber auch deutlich länger als ein Integer-Wert.

dedlfix.