MySQL > Datenbank-Konzept m. relationaler Datenbank

[Guma]

Hallo zusammen,

derzeit beschäftige ich mich viel mit relationalen Datenbanken unter MySQL 4.3. . Vor allem die Selectabfragen stellen den größten Geschwindigkeitsfaktor dar.  Bei realtionalen Datenbanken habe ich mit Joins die Möglichkeit Datensätze aus zwei Tabellen zu verbinden. Dafür nutze ich z.B. Inner Join. Inklusivverknüpfungen erreiche ich mit Left Join. So jetzt stellt sich bei mir die Frage ob Join - Abfragen die Performance stark verlangsamen.

Dazu habe ich mir folgendes Überlegt:

Beispieldatenbank:
Tabelle 1: id, name, ort, plz, tel, email
Tabelle 2: id, leistung

Tabelle 1 enthält also Kundendaten wie Adresse und Kontaktdaten. Derzeit sind ca. 9.000 Kunden in der Tabelle 1.

Tabelle 2 enthält alle Leistungen, die über den Primärschlüssen id verknüpft sind. In meinem Fall haben einzelne Kunden so ca. 20-40 Leistungen. Das Inhalte der Tabelle 2 sehen dann so aus:

id  |  Leistung
----------------
1   |  Golf
2   |  Joggen
3   |  Schwimmen
4   |  Tauchen
5   |  Surfen
... u.s.w.

So weit so gut. jetzt ist in den letzten Wochen meine Tabelle 2 sehr groß geworden: ca. 45.000 Datensätze sind jetzt drin. Tendenz steigend!

Die Abfrage der Datenbank:
Mein SELECT durchfostet mit Join neben der Tabelle 1 die Tabelle zwei durch. Beim Listen der Inhalte auf der Webseite (mit PHP) werden jetzt für jede gewählte Tabellenzeile mit Join alle 45.000 Einträgen nach passenden Leistungen durchsucht und mit ausgegeben.

Frage zur Geschwindkeit bei Verwendung von Joins:
Kann das nicht die Performance stark drücken? Derzeit dauert der Aufruf nicht all zu lang, nur wie wird es in der Zukunft sein?!

Jetzt habe ich mir folgende Alternative überlegt:
Tabelle 1: id, name, ort, plz, tel, email, alleleistungen

Tabelle 2: id, leistung

In die Spalte "alleleistungen" schreibe ich mit Komma separiert die gewählten Leistungen der Kunden beim speichern gleich mit rein. Nur im Redaktionssystem wird jetzt die Tabelle 2 genutzt. Die Ausgabe und Listung findet jetzt über ein SELECT statt, das ganz ohne Joins auskommt und nut Tabelle 1 nutzt. Es wird jetzt z.B. mit LIKE im der Spalte "alleleistungen" gesucht, anstatt die 45.000 Leistungen in Tabelle 2 nach passenden Einträgen zu durchforsten.

Fragen: Ist LIKE über ein feld mit 20-40 Wörtern besser und schneller als ein JOIN über 45.000 Datensätze? Wie geht ihr mit einer solchen Datenmenge um?

Danke fürs Lesen und Gedanken-Machen.
Es grüßt Euch Guma

[Cruz]

Hallo Guma,

probieren geht über studieren. Investier doch mal ein bisschen Zeit in einen gründlichen Performancetest. Lass dein PHP Skript so wie es ist und schreib ein zusätzliches, was dir in automatisierter Form ein Performancetest durchführt, indem es inkrementell die Tabellen mit immer mehr künstlich erzeugten Daten füllt und danach das PHP Skript aufruft und die Zeit nimmt, wie lange das Script braucht um fertig zu werden. Oder setze in Perf.testskript direkt das SELECT mit JOIN ab, um dich rein auf die Dauer der Datenbankabfrage zu konzentrieren. Es kann ja sein, dass das PHP Skript selbst bei grossen Datenmengen zum Problem wird, wenn es unglücklich programmiert wird. Dann kriegst du ein Gefühl dafür wie viele Daten tatsächlich vorliegen müssen, damit die Abfragegeschwindigkeit spürbar in die Knie geht.

In die Spalte "alleleistungen" schreibe ich mit Komma separiert die gewählten Leistungen der Kunden beim speichern gleich mit rein.

Das halte ich für eine sehr schlechte Strategie. Du erzeugst damit sowohl eine Redundanz, als auch eine Abhängigkeit, da diese Spalte mit Tabelle 2 immer snychron gehalten werden muss.

Zwei Vorschläge habe ich noch, wie du die Performance möglicherweise verbessern könntest, obwohl es vielleicht niemals notwendig wird.

1. Ich kann mir gut vorstellen, dass in der 2. Tabelle für die Leistungen viele Einträge doppelt und mehrfach vorkommen, weil z.B. mehrere Kunden die Leistung "Golfen" beanspruchen. Erstelle eine atomare Tabelle für die Leistungen, also keine Leistung doppelt, und führe eine dritte Tabelle ein, die nur für die Zuordnung von Leistungen zu Kunden verwantwortlich ist.

2. Anstatt für jeden Kunden immer wieder eine neue Abfrage in die Datenbank zu schicken, kannst du sicherlich eine einzige Abfrage geschickt so formulieren, dass sie eine Ergebnistabelle zurückgibt,
die den zusammengesetzten Ergebnissen der einzelnen Abfragen entspricht. Dann muss mysql nur ein einziges mal JOINen.

Ansonsten kannst du dich noch unter dem Stichwort "INDEX" schlau machen, wie du deine Tabellen geschickt indizieren kannst. Da du anscheinend nur über INTEGER Felder JOINts, weiss ich jetzt nicht genau, ob ein INDEX überhaupt was bringt, aber besser mal nachgeschlagen.

Gruß,
Cruz

[Guma]

Danke Cruz für die Anmerkungen und Vorschläge. Ich werde weiter testen und prüfen was genaudie Geschwindikeit bremsen könnte.

Danke auch für die Hinweise bezüglich INDEX.

Ist es wirklich so schlecht ein Feld wie 'alleleistungen' zu haben?

Es grüßt Guma

[Cruz]

Ist es wirklich so schlecht ein Feld wie 'alleleistungen' zu haben?

Ja es ist schrecklich. :) Die Datenmenge der Leitungen wird dadurch (fast) verdoppelt und du musst immer dafür sorgen, dass der Inhalt des "alleleistungen" Feldes mit den Daten in der Tabelle 2 übereinstimmt. Was machst du zum Beispiel, wenn mal eine Leistung aus Tabelle 2 gelöscht wird, die ja jetzt auch irgendwo mitten drin in einem kommagerennten Textfeld drin steht (oder vielleicht auch Ende oder am Anfgang, man weiss es ja nicht)? Was wenn die Leistungen alphabetisch sortiert ausgegeben werden sollen? Wie löst du gescheiht eine Stichwortsuche über so ein Feld? Es ist eine unnütze Fehlerquelle.

Gruß,
Cruz

[Guma]

Danke für die Antwort. Klar die Stichwortsuche mit LIKE ist möglich aber eine Fehlerquelle und Bremse.

Ich mache mir weiter Gedanken.

Grüße von Guma

[Sven Rautenberg]

Moin!

Tabelle 1 enthält also Kundendaten wie Adresse und Kontaktdaten. Derzeit sind ca. 9.000 Kunden in der Tabelle 1.

Tabelle 2 enthält alle Leistungen, die über den Primärschlüssen id verknüpft sind. In meinem Fall haben einzelne Kunden so ca. 20-40 Leistungen. Das Inhalte der Tabelle 2 sehen dann so aus:

Wie kriegst du es denn hin, die Leistungen mit den Kunden zu verknüpfen? Üblicherweise würde man das doch als n:m-Beziehung gestalten, d.h. du hast insgesamt DREI Tabellen.

Frage zur Geschwindkeit bei Verwendung von Joins:
Kann das nicht die Performance stark drücken? Derzeit dauert der Aufruf nicht all zu lang, nur wie wird es in der Zukunft sein?!

Deine "Kann"-Frage muß man eindeutig mit Ja beantworten: Ja, das KANN sein. Muß aber nicht, wenn man es vernünftig macht.

Es wird jetzt z.B. mit LIKE im der Spalte "alleleistungen" gesucht, anstatt die 45.000 Leistungen in Tabelle 2 nach passenden Einträgen zu durchforsten.

Das ist nach meiner Meinung schlimmer, weil du zwingend nach %WORT% suchen mußt - das aber bedeuten einen Full Table Scan über die gesamte Kundentabelle, während beim JOIN ein Index genutzt werden könnte. Indexsuche ist meist deutlich schneller, als komplette Tabellensuchen im gesamten Datenbestand.

Fragen: Ist LIKE über ein feld mit 20-40 Wörtern besser und schneller als ein JOIN über 45.000 Datensätze?

Nein, nur in Ausnahmefällen, in der Regel aber nicht. Weil du kein LIKE über 20-40 Wörter machst, sondern über 9000 Kundendatensätze.

Ein JOIN über 45.000 Datensätze könnte ja immerhin einen Index benutzen, wenn einer definiert ist. Das weiß man aber nur, wenn man seine Querys erstens von der Laufzeit her analysiert und zweitens von ihrer Indexnutzung her. Der Befehl EXPLAIN ist bei zweiterem unabdingbar.

Wie geht ihr mit einer solchen Datenmenge um?

Das sind doch keine Datenmengen, das ist noch Popelkram.

Allerdings ist deine Datenbankstruktur mutmaßlich extrem suboptimal aufgebaut, wie ich im ersten Absatz ja andeutete.

- Sven Rautenberg

[Guma]

Danke Sven R.,

Wie kriegst du es denn hin, die Leistungen mit den Kunden zu verknüpfen? Üblicherweise würde man das doch als n:m-Beziehung gestalten, d.h. du hast insgesamt DREI Tabellen.

Ich mach das über die ID, aber du hast recht es gibt noch ein auto-increment-feld namens idleistung.

Allerdings ist deine Datenbankstruktur mutmaßlich extrem suboptimal aufgebaut.

Bestimmt hast Du recht aber ich setze es eben um so gut ich das kann. deshalb die fragen, wenn ich es besser könnte würde ich es tun.

Danke für die Antwort. Grüße von Guma

[Sven Rautenberg]

Moin!

Wie kriegst du es denn hin, die Leistungen mit den Kunden zu verknüpfen? Üblicherweise würde man das doch als n:m-Beziehung gestalten, d.h. du hast insgesamt DREI Tabellen.

Ich mach das über die ID, aber du hast recht es gibt noch ein auto-increment-feld namens idleistung.

Das ist keine Antwort auf meine Feststellung. Was ist mit den drei Tabellen?

Es ist ja blödsinnig, zwei verschiedene Datensätze mit der gleichen Leistung "Tauchen" zu haben, wenn zwei Kunden diese Leistung zugeordnet kriegen.

Allerdings ist deine Datenbankstruktur mutmaßlich extrem suboptimal aufgebaut.

Bestimmt hast Du recht aber ich setze es eben um so gut ich das kann. deshalb die fragen, wenn ich es besser könnte würde ich es tun.

Ich erkenne nicht, dass du interessiert an Verbesserungen bist und beispielsweise auf meinen Einwurf, du würdest DREI Tabellen benötigen, nachfragst, wie das denn gemeint sein könne - sofern du es nicht verstanden hast.

Stattdessen machst du dir vollkommen sinnlos Gedanken über irgendeine Performanceproblematik, die du noch nicht mal ansatzweise praktisch erfahren hast durch lange Laufzeiten, sondern die du dir nur gedanklich ausmalst. Das ist der falsche Ansatz. Sorge erst mal für eine gute Datenbankgestaltung - danach kommt dann die Performancefrage, sofern sie relevant wird.

- Sven Rautenberg

[Guma]

Sven R. was wäre ich ohne dich,

ich habe missverstanden was du mir drei tabellen meinst. Ich dachte an drei spalten, wie ich sie auch habe:

tabelle leistungen mit drei spalten:

id  | kundennummer  |  leistung
--------------------------------
1   | 101063        |  Golf
2   | 101133        |  Klettern
3   | 101213        |  Golf

... u.s.w.

Mit Join Left kann ich über die kundennummer (Primärschlüssel) für jeden Kunden die Leistungen ausgeben.

Ist das verkehrt? geht es besser?
Wie würde eine lösung mit drei Tabellen statt drei Spalten aussehen.

Grüße von Guma

[Sven Rautenberg]

Moin!

ich habe missverstanden was du mir drei tabellen meinst. Ich dachte an drei spalten, wie ich sie auch habe:

Das ist schlecht. :)

tabelle leistungen mit drei spalten:

id  | kundennummer  |  leistung

1   | 101063        |  Golf
2   | 101133        |  Klettern
3   | 101213        |  Golf

Wo ist der Unterschied zwischen dem Golf in der ersten und in der dritten Zeile?

Mit Join Left kann ich über die kundennummer (Primärschlüssel) für jeden Kunden die Leistungen ausgeben.

Ja klar, aber überlege mal, was passieren muß, damit du eine komplette Liste aller existierenden Leistungen ausgeben kannst. Das wäre ja keine unübliche Fragestellung, denn beispielsweise wenn man dem ausfüllenden Benutzer eine Liste an die Hand geben muß mit allen möglichen Leistungen, dann braucht man diese Liste.

Ist das verkehrt? geht es besser?
Wie würde eine lösung mit drei Tabellen statt drei Spalten aussehen.

Diskutieren wir das am besten mal aus. Beantworte die Fragen, dann sehen wir weiter.

- Sven Rautenberg

[Guma]

Hallo Sven R.,

hier meine antworten...

tabelle leistungen mit drei spalten:

id  | kundennummer  |  leistung

1   | 101063        |  Golf
2   | 101133        |  Klettern
3   | 101213        |  Golf

Wo ist der Unterschied zwischen dem Golf in der ersten und in der dritten Zeile?

Antwort: Die Kunden 101063 und 101213 haben Golf als leistung gewählt. Der Unterschied ist nur die Kundennummer. wIE KANN ich jetzt die besprochene dritte Tabelle ansetzen? Darauf willst Du doch raus?!

Ja klar, aber überlege mal, was passieren muß, damit du eine komplette Liste aller existierenden Leistungen ausgeben kannst. Das wäre ja keine unübliche Fragestellung, denn beispielsweise wenn man dem ausfüllenden Benutzer eine Liste an die Hand geben muß mit allen möglichen Leistungen, dann braucht man diese Liste.

Die komplette Liste ist die Werteliste, die kommt aus einer anderen Tabelle. Ist das jetzt die dritte von der du sprichst?!

Freue mich auf Deine Antwort Guma

[Sven Rautenberg]

Moin!

tabelle leistungen mit drei spalten:

id  | kundennummer  |  leistung

1   | 101063        |  Golf
2   | 101133        |  Klettern
3   | 101213        |  Golf

Wo ist der Unterschied zwischen dem Golf in der ersten und in der dritten Zeile?

Antwort: Die Kunden 101063 und 101213 haben Golf als leistung gewählt. Der Unterschied ist nur die Kundennummer. wIE KANN ich jetzt die besprochene dritte Tabelle ansetzen? Darauf willst Du doch raus?!

Aber klar.

Und die Lösung ist simpel: Du brauchst eine Tabelle mit deinen Kunden und darin der Kunden-ID. Dann brauchst du eine Tabelle mit allen möglichen Leistungen und einer Leistungs-ID. Und die Tabelle, die du jetzt hast, wird geändert in eine mit zwei Spalten, die die Verbindung zwischen Kunden-ID und Leistungs-ID enthält.

Dann sparst du dir die ganzen Dopplungen in den Leistungsbezeichnungen, und kannst außerdem wirklich exakt abfragen, welche Kunden beispielsweise "Golf" als Leistung gewählt hatten - ohne in der Gefahr zu stehen, dass manchmal auch "Golfen" eingetragen werden könnte.

Und mithilfe von EXPLAIN und der entsprechenden Analyse, welche Zugriffe auf diese Verbindungstabelle entstehen, würdest du dann einen oder mehrere Indices setzen - ggf. auch über die zwei Spalten gemeinsam. Anbieten würde sich beispielsweise ein UNIQUE-Index über beide Spalten dann, wenn ein Kunde eine Leistung garantiert nur ein einziges Mal zugewiesen bekommen darf, also nur einmal Golf - nicht zwei oder dreimal.

Die komplette Liste ist die Werteliste, die kommt aus einer anderen Tabelle. Ist das jetzt die dritte von der du sprichst?!

Ich habe ja keine Ahnung, was du noch so an Tabellen hast. Bisher weiß ich von zwei Tabellen, und deren Inhalt. Und das, was ich weiß, gefällt mir hinsichtlich der Anforderung an übliches DB-Design nicht.

- Sven Rautenberg

[Guma]

Danke für den rat. ich werde damit arbeiten und die datenbank entsprechend ändern. mal sehen ob ich das so hinbekomme.

Guma

[Hamster]

So jetzt stellt sich bei mir die Frage ob Join - Abfragen die Performance stark verlangsamen.

JOINs sind sehr natürlich. Mit geeigneten Indizes kannst Du die Performance hoch halten.

So weit so gut. jetzt ist in den letzten Wochen meine Tabelle 2 sehr groß geworden: ca. 45.000 Datensätze sind jetzt drin. Tendenz steigend!

Wenn ich Dich richtig verstehe umfasst Dein Leistungsnagebot nun 45.000 Einzelleistungen. Es geht mich ja nichts an, aber ist das gut fürs Geschäftsmodell?

Frage zur Geschwindkeit bei Verwendung von Joins:
Kann das nicht die Performance stark drücken? Derzeit dauert der Aufruf nicht all zu lang, nur wie wird es in der Zukunft sein?!

Easy, es ist ziemlich egal, ob Du eine Datentabelle mit 45k oder 900k Datensätzen durchsuchst, wenn die Indizes gesetzt sind. Stichwort: Binäres Suchen

Jetzt habe ich mir folgende Alternative überlegt:
Tabelle 1: id, name, ort, plz, tel, email, alleleistungen

Tabelle 2: id, leistung

Wenn Du Dir Gedanken zum Datenmodell machst, ist ein wenig Grundwissen - Stichwort "ERM" (entity relationship model) hilfreich.

Fragen: Ist LIKE über ein feld mit 20-40 Wörtern besser und schneller als ein JOIN über 45.000 Datensätze? Wie geht ihr mit einer solchen Datenmenge um?

1.) Nein 2.) s.o.