nicht angemeldet
Hallo Stephan,
Dabei stellen sich mir einige Fragen bzgl. Datenbank-Design:
ich versuchs mal kurz zu fassen, was leider nicht leicht ist. Denn was du fragst, ist eigentlich der Stoff ganztaegiger Schulungen und in den Details von DBMS zu DBMS verschieden. Zu den Interna von MySQL kann ich nicht viel sagen, da ich selbst noch nicht damit gearbeitet habe. Allen relationalen Datenbanksystemen sind jedoch einige Dinge gemeinsam. Und darauf versuche ich mal einzugehen:
Macht es Sinn, eine Datenbank bis in die letzten Details zu normalisieren?
Es kommt darauf an ;-)
Dadurch steigt doch gleichzeitig die Anzahl der beteiligten Tabellen beim SELECT,
was für die Performance wohl eher schlecht ist.
Richtig. Kleine Tabellen, die ohnehin nicht mehr als ein oder zwei DB-Pages umfassen, zu normalisieren oder auch indizieren, macht keinen Sinn, da das DBMS in jedem Fall mindestens eine Page laden muß.
Eine Page ist in der Regel ein Block in der vom Filesystem vorgegebenen Groesse. Es gibt zwar auch Mechanismen, mit Subpages zu arbeiten, aber grundsaetzlich gilt: Datenbanken sind letztlich auch Dateien auf der Festplatte und werden Daten aus der Datenbank geladen, so muss immer wenigstens ein Block von der Festplatte in den Arbeitsspeicher geladen werden. Auf grossen Festplatten kann ein solcher Block locker mal 32 Kb umfassen.
Ist eine Tabelle so klein, dass sie vollkommen in eine Page passt, dann wird also beim Zugriff auf die Tabelle durch einlesen einer Page die gesamte Tabelle in den Speicher geladen. Wuerde man eine solche Tabelle indizieren, dann muesste fuer den Index eine weiteres Page angelegt werden. Da DBMS i.A. Daten und Indices getrennt halten (ich kenne keine Ausnahme), wuerde eine solch kleine Tabelle mit dem Index nichts gewinnen. Alle Eintraege im Index zeigen wieder auf die gleiche Datenpage. Deshalb ignoriert ein gutes DBMS bei kleinen Tabellen den Index vollkommen, wenn die Daten nicht wenigstens mehrere Pages umfassen. Wozu auch extra im Index nachsehen, wenn am Ende doch stets die gleich Datenpage geladen wuerde.
Nehmen wir nun an, diese schon recht kleine Tabelle ist nicht normalisiert und wir wuerden sie normalisieren, dann entstehen aus dieser einen Tabelle mehrere noch kleinere. Es ist nicht garantiert, dass diese in der gleichen Page liegen. Abgesehen vom Verwaltungsaufwand des Joinens waere das DBMS auf einmal gezwungen, auch noch mehrere Pages von Platte einzulesen.
Darum gilt im Allgemeinen: Je kleiner die Tabelle, desto weniger Sinn macht eine Normalisierung.
Der Umkehrschluss gilt jedoch nicht automatisch, dass grosse Tabellen auf jeden Fall normalisiert werden sollten.
Vor jedem groesseren Datenbankdesign sollte man sich ueberlegen, welche Daten in welchen Prozessen verarbeitet werden. Wird Wert auf eine leichte Pflege gelegt oder Wert auf Performance? Wie lauten die wichtigsten SQL-Statements? Wie sehen die Arbeitsablaeufe aus? Haeufige Abfrage und wenig "Bewegung" in den Daten selbst? Oder permanante Datenaenderungen? usw.
All diese Fragen spielen eine Rolle beim Datenbankdesign. Erst wenn der Anforderungskatalog mit Prioritaetenliste steht und eine vollstaendige (!) Beschreibung der Daten vorhanden ist, sollte das eigentliche DB-Design beginnen.
In der Praxis sieht es oft anders aus, was dann schnell zu Beschwerden ueber lahme DB-Server fuehrt und tatsaechlich fast immer am unpassenden Design liegt.
Einsatz von char- oder varchar-Feldern:
Benutze man nur char-Felder, erreicht man eine feste Satzlänge.
Kann das Vorteile beim Durchsuchen bringen?
Ja, kann es eindeutig.
Varchar bedeutet, dass im Datendatz nur soviel Platz reserviert wird, wie auch tatsaechlich Daten gespeichert werden. Dies kann in jedem Satz anders aussehen. Woher aber weiss das DBMS, wieviele Bytes eine Spalte gerade in diesem Satz enthaelt? Diese Laengeninformation muss also zusaetzlich gespeichert werden und wird in der Regel direkt im Row mitgespeichert.
Ein typischer Row besteht immer aus einem Header und den aneinandergereihten Spalten. Bei Spalten mit fester Laenge muss wieder unterschieden werden, ob eine Spalte nullable ist oder mit der Option NOT NULL definiert wurde. Darf eine Spalte fester Laenge nicht NULL sein, ist alles klar, es muss nur der Inhalt der Spalte gespeichert werden. Ist eine Spalte nullable, muss zusaetzlich zu den Daten die Information abgelegt werden, ob die Spalte in diesem Row NULL ist. D.h. nullable Spalten enthalten zusetzlich zu dem fuer die Daten reservierten Platz noch ein oft 1 Byte grosses Feld, das Auskunft ueber die NULL-Eigenschaft gibt. Ist eine Spalte fester Laenge NULL, liegt der fest fuer die Daten reservierte Platz ungenutzt brach.
Bei Spalten variabler Laenge ist dies nicht der Fall. Ist eine Varcharspalte nullable, muss auch hier wieder die NULL-Eigenschaft separat zu den Daten abgelegt werden. Im Falle von NULL wird jedoch kein weiterer Platz benoetigt. Ist die Spalte in diesem Row aber nicht NULL, muss noch festgehalten werden, wie lang die Daten denn nun wirklich sind. Es wird also noch eine Laengeninformation benoetigt (und meist unabhaengig von der NULL-Eigenschaft in jedem Fall abgelegt).
Richtig interessant wird all dies aber erst, wenn man sich nun die Reihenfolge der Spalten ansieht. Dazu ein kleines Beispel:
Tabelle A bestehe aus den Spalten
Col1 Integer
Col2 Varchar(50)
Col3 Char(7) NOT NULL
Dies ist eine typische Tabelle, wie sie gerne angelegt wird, und bereits einen Kardinalsfehler enthaelt. Ein Row dieser Tabelle wuerde etwa folgendermassen aussehen:
|Header|nullinfo1,daten1|nullinfo2,laenge2,daten2|daten3|
Am Anfang jedes Rows befindet sich ein Header. Die ist eine DB-interne Information und hier nicht weiter interessant. Wenn nun Col1 ermittelt werden soll, wird zunaechst die nullinfo1 ausglesen. Ist die Splate NULL, wird NULL als Wert ausgegeben. Ist nullinfo1 nicht NULL, wird daten1 ausgelesen. Bei Col2 verhaelt es sich genauso, nur dass hier beim Zugriff auf daten2 (falls noetig) noch laenge2 beruecksichtigt wird. Ganz anders aber sieht es beim Zugriff auf Col3 aus. Woher weiss das DBMS an welcher Position im Row Col3 beginnt? Col3 hat keine feste Startposition im Row, da zuvor ein Feld variabler Laenge gespeichert ist. D.h., um Col3 ermitteln zu koennen, muss zunaechst die Laenge von Col2 bestimmt werden, um herauszufinden, wo die Daten von Col3 ueberhaupt beginnen. Oder anders gesagt, die Postion aller nach der ersten variablen Spalte liegenden weiteren Tabellenspalten muss zunaecht berechnet werden. Je weiter hinten im Row sich die gewuenschte Spalte befindet und je mehr Varcharspalten davor liegen, desto mehr Aufwand entsteht.
Daher gilt: Spalten fester Länge an den Anfang, Varchar-Spalten ans Ende der Tabelle.
Haeufiger benoetigte Spalten nach vorne, selten benoetigte Spalten ans Ende.
Was kann z.B. bei INTEGER-Felder die genaue Abschätzung der möglichen Werte bringen (--> Grösse der Spalte)?
Bei über 100 int-Feldern kann das ziemlich nervig sein, aber wenn es der Performance dient ...
Je genauer die noetige Laenge der Felder abgeschaetzt wird, desto weniger Platz wird unnoetig verschwendet und desto mehr Rows einer Tabelle passen in eine Page. Bei der Abfrage jeweils nur eines einzigen Satzes aus einer Tabelle ist dies irrelavent. Werden aber grosse Bereich der Tabele abgefragt, dann sind kuerzere Saetze in sofern interessant, dass das DBMS weniger Pages einlesen muss.
Was kann man generell für böse Fouls begehen?
Uff, da gibt es viele Moeglichkeiten. Vielleicht ein paar klassische Beispiele:
Eine Tabelle enthaelt 12 Monatswerte je Satz. Aenderungen erfolgen einmal pro Monat, wenn der aktuelle Monat upgedatet wird. Abgefragt wird die Datenbank haeufig, jedoch immer nur einzelne Monate. Die Performance der Datenbank laesst sich durch simples Kippen und umsortieren drastisch erhoehen. Kippen meint: je Satz werden nicht alle 12 Monate gespeichert, sondern nur die Monatsnummer und der jeweilige Wert. Dadurch werden zunaechst zusaetzliche Informationen eingefuegt, die die Tabelle scheinbar aufblaehen,. Tatsaechlich werden wesentlich kuerzere Saetze erreicht, wodurch wieder mit einer Page mehr Saetze auf einmal eingelesen werden koennen. Mit Umsortieren ist gemeint, dass die Speicherung der Daten innerhalb der Tabelle nach Monaten sortiert erfolgen soll. (Am Besten in der Doku nachsehen, wie sich die Sortierung beeinflussen laesst. Dies muss nicht immer der Primaerindex sein.) Somit liegen jeweils die Daten eines Monats beieinander und nicht, wie zuvor ueber die gesammte Datenbank verstreut. Zuvor war praktisch ein Tablescann erforderlich um die Daten eines Monats komplett zu ermitteln, nun reicht das Einlesen eines bestimmten Pagebereiches aus. Beherrscht nun das DBMS auch noch Clustering, kann der Platzbedarf pro Monat wieder reduziert werden auf etwas das gleich Mass wie zuvor.
Eher ein Fehler der aus mangelnder Erfahrung oder Nachlassigkeit entsteht ist die falsche Wahl der Speicheroptionen. Insbesondere wenn laufend Aenderungen an den Daten erfolgen und Saetze hinzugefuegt werden, verdienen die Parameter zur Einstellung der Freibereiche Beachtung. Diese Freibereiche werden ja nach DMBS am Ende jeder Page gelassen oder in Form von eingeschobenen Leerpages in der Tabelle. Sie dienen dazu, neue Saetze aufzunehmen und passend in die vorgegebene Sortierung der Tabelle einordnen zu koennen. Enthaelt eine Page beispielsweise alle Saetze zu "A" und soll ein weiterer Satz zu "A" hinzugefuegt werden, dann waere es sinnvoll, wenn er auch in diese Page eingefuegt werden koennte. Beim Auslesen waeren somit wieder alle Rows zu "A" beisammen. Ist dieser Freibereich zu klein gewaehlt, fuegt das DBMS die neuen Saetze am Ende der Tabelle ein, was zu haufigem Hin-und-Herspringen fuehren kann bei der Abfrage, und Performance kostet. Wird der Wert zu groß gewaehlt, blaeht sich die Tabelle unnoetig auf, was auch wieder Performance kostet.
Welches DBMS hier welche Einstellungen ermoeglicht, muß jeweils der Doku entnommen werden.
Eine regelmaeßige Reorganisation wird dadurch uebrigens nicht ueberfluessig. ;-)
Ein weiterer beliebter Fehler oft aus Faulheit ist die Anwendungsentwicklung, ohne die SQL-Statements per Explain zu überprüfen. Explain zeigt die Art des Zugriffes auf die Daten an. Indexbasiert, Indescann, Tablescann, ... Es hat schon manch ein Programmierer ruhigen Gewissens den Arbeitsplatz verlassen , im Glauben, seine Querys wuerden den Index nutzen, und tatsaechlich den Server in die Knie gezwungen, weil ein Tablescann erfolgte.
Puh, das sind nur drei beispiele. Daneben gibt es noch tausend Dinge, die Performance kosten koennen. Datenbanktuning gehoert in den Bereich DB-Administration und der ist nicht ohne Grund ein eigener Beruf. Ein Blick in die Doku und besonders in den Abschnitt Tuning ist imho immer noch die beste Informationsquelle.
HTH
Viele Gruesse
Kess