Effiziente Datenbankstruktur

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Hallo,

Ich bin gerade dabei, eine Datenbank zu entwerfen, die auf grosse Datenmengen und gleichzeitig trotzdem schnelle Zugriffszeiten ausgelegt ist. Da sich in solchen Angelegenheiten die grossen Firmen ungern auf die Finger schauen lassen und ich zweifel hab, dass trotz Indizierung die Suche bei tlw. mehreren Millionen Rows wohl mehrere Sekunden dauern wird, hab ich mir selber Gedanken gemacht.

Und hier meine Frage:
Waere es sinnvoll, vor die grossen Tabellen eine Art Cache-Tabelle zu schalten, in die die aktuellen Arbeitsdaten aus der „Muttertabelle“ geladen werden? Vorbild sind saemtliche Speichermedien im PC, wo aehnlich verfahren wird. Wuerde es Geschwindigkeitsvorteile bringen und ist sowas gebraeuchlich bei grossen Tabellen?

#2

Hallo Burkhard,

Ich bin gerade dabei, eine Datenbank zu entwerfen, die auf
grosse Datenmengen und gleichzeitig trotzdem schnelle
Zugriffszeiten ausgelegt ist. Da sich in solchen
Angelegenheiten die grossen Firmen ungern auf die Finger
schauen lassen und ich zweifel hab, dass trotz Indizierung die
Suche bei tlw. mehreren Millionen Rows wohl mehrere Sekunden
dauern wird, hab ich mir selber Gedanken gemacht.

Das sollte mit keiner professionellen Datenbank ein Problem sein. Sprich: DB2, Oracle, Informix, MS-SQL.

Und hier meine Frage:
Waere es sinnvoll, vor die grossen Tabellen eine Art
Cache-Tabelle zu schalten, in die die aktuellen Arbeitsdaten
aus der „Muttertabelle“ geladen werden? Vorbild sind
saemtliche Speichermedien im PC, wo aehnlich verfahren wird.
Wuerde es Geschwindigkeitsvorteile bringen und ist sowas
gebraeuchlich bei grossen Tabellen?

Das ist deshalb nicht sinnvoll, weil das die DB-Systeme selbst schon machen, und zwar mit Sicherheit effizienter als Du das machen könntest. Allerdings bieten die DBMSs jeweils verschiedene Möglichkeiten den Zugriff mit ihren Bordmitteln zu optimieren. Stichwort wäre da zum Beispiel Partitionierung (zumindest heißt das bei Oracle so, bei den anderen Herstellern evtl. anders).

Gruß
Martin

#3

Aus Kostengruenden wuerde ich gerne auf mySQL mit InnoDB zurueckgreifen. Ich hoffe, das ist damit ebenso gut moeglich? Gibts da Erfahrungen?

#4

hallo

martin hat eh das wesentliche schon beschrieben.

indizes sind z.b. unter oracle als b*trees ausgelegt. die haben den vorteil, dass sie auch bei wirklich grossen tabellen immer schön performant sind. die grösste unserer tabellen hat knapp 100 mio einträge. eine suche nach einem sauber indizierten feld dauert trotzdem weit unter einer sekunde. bei der suche nach einem unique key ist die suche nur unwesentlich langsamer als bei einer kleinen tabelle.

das wichtigste mittel zur performanceoptimierung ist, darauf zu achten, dass die daten gut indizierbar sind. und dann natürlich sinnvolle indizes anlegen. da gibt es einige grundregeln zu beachten:

null-values werden üblicherweise nicht indiziert. eine suche nach „where column is null“ endet daher in einem full-table-scann, egal ob index vorhanden ist oder nicht.

für ein physisches db-model hat das ziemliche auswirkungen. angenommen, du speicherst z.b. nachrichten. du willst standardmässig alle ungelesenen nachrichten anzeigen. wenn du nun ein feld „gelesen date“ hast und fragst „where gelesen is null“ bekommst du zwar korrekte ergebnisse, die aber nur langsam. sinnvoller kann es sein, für ungelesene nachrichten einen wert in ferner zukunft zu nehmen (z.b. den 1.1.3000). oder gleich ein eigenes statusfeld, dass gelesen ja/nein abbildet. da du von vielen range-scanns auf das gelesenstatus-feld ausgehen kannst, ist es oft vorteilhaft, den wert NULL für false anzunehmen: dich interessieren nur die true-werte und null wird nicht indiziert => dein index beinhaltet nur die true-werte und der range-scann wird sehr flott sein.

like-abfragen auf textfelder können nur dann den index nutzen, wenn der platzhalter „%“ nicht an erster stelle kommt. vermeide also im db-design felder, in denen völlig frei gesucht werden kann (oder nimm einen volltext-index, der aber anders abgefragt werden muss).

wenn du case-insensitiv suchen willst (also gross-kleinschreibung ignorieren), brauchst du entweder einen function-based-index, der etwas langsamer als normale indizes ist. oder du braucht ein spezielles suchfeld, dass du in deiner tabelle extra mitführst und dass die daten optimiert für die suche abspeichert.

generell ist es immer notwendig, sich zu überlegen, über welche pfade du auf die daten zugreifen wirst - wie also die suchen aussehen werden. im nachhinein zu optimieren ist oft sehr mühsam.

lg
erwin

#5

Hey Erwin, danke dir fuer die ausfuehrliche Beschreibung und die guten Tipps! Noch eine letzte Frage: Was verstehst du unter „ordentlicher Indizierung“? Ich haette jetzt mit meinem beschraenkten Wissen einfach jede Row, die ich spaeter abfragen moechte, indiziert. Bin ich da auf dem Holzweg?

#6

hallo

Was verstehst du unter „ordentlicher Indizierung“?

soviele indizes wie nötig und so wenig wie möglich.

Ich haette jetzt mit meinem beschraenkten Wissen einfach jede Row,
die ich spaeter abfragen moechte, indiziert.

???

einen index legt man auf eine oder mehrere spalten einer tabelle. es werden dabei alle zeilen (rows) indiziert. ausnahme: zeilen, bei denen in der indizierten spalte null-values vorliegen, werden nicht indiziert.

grundsätzlich ist mal jede spalte, die in einer where-klausel auftaucht, ein guter kandidat für einen index. allerdings muss man höllisch aufpassen, nicht zuviel zu indizieren:

  • ein index benötigt selbst auch etwas platz.
  • ein index muss bei jeder änderung des tabelleninhaltes mitgepflegt werden, macht also inserts, updates und deletes ev. etwas langsamer.
  • ein index hat nur dann sinn, wenn er die datenmenge erheblich einschränkt. beispiel: du hast eine tabelle mit einer spalte, die nur 5 verschiedene werte hat. bei gleichmässiger verteilung der werte kommt also auf jeden wert ca. 20% des tabelleninhaltes. wenn du nun nach einem wert suchst und einen index nutzt, muss die db zuerst im index lesen gehen, mit diesen infos dann in der tabelle, dann wieder im index usw. das kann die db schon ordentlich unter stress setzen. oracle geht z.b. so vor, dass es einen index nur dann nutzt, wenn es vermutet, dass der index die datenmenge auf weniger als 7 % der gesammtmenge einschränkt. ansonsten geht oracle davon aus, dass ein fulltablescann effizienter ist - schließlich können da die daten schön der reihe nach gelesen werden ohne dass der lesekopf der festplatte ständig hin und her muss.
  • ein falsch gesetzter index kann die lesezugriffe sogar erheblich verlangsamen. die datenbank muss ja bei jedem select entscheiden, welcher abfragepfad der vermutlich effizienteste ist (macht im oracle der optimizer). hast du mehrere indizes auf eine tabelle und eine komplexe where-klausel, kann es vorkommen, dass die db den falschen index nimmt und daher erheblich mehr daten von der platte lesen muss - und platten-io ist das langsamste bei einer db!
  • üblicherweise nimmt eine db pro statement und tabelle nur einen index. wenn du in einer tabelle daher ständig nach den selben beiden spalten suchst, ist es besser, einen zusammengesetzen index (also einen index über beide spalten) anzulegen als zwei einzelne indizes. die reihenfolge der spalten im index ist dabei wichtig: die spalte, nach der häufiger gesucht wird bzw. die die datenmenge am meisten einschränkt, sollte vorne sein.

eine datenbank zu optimieren benötigt sowohl erfahrung als auch fingerspitzengefühl. oft muss man auch herumprobieren. oracle unterstützt einem dabei, in dem man sich anzeigen lassen kann, welchen abfragepfad der optimizer für ein bestimmtes statement wählt. zusätzlich kann man den db-kernel fragen, welche statements am meisten io verbraucht haben. auch bietet oracle ein erweitertes profiling, mit dem man ganz genau sagen kann, wieviel rechenzeit, hauptspeicher und platten-io ein statement braucht. ob mysql ähnliche funktionen abietet, kann ich leider nicht sagen.

lg
erwin

1 Like
#7

Hi,

einen index legt man auf eine oder mehrere spalten einer
tabelle. es werden dabei alle zeilen (rows) indiziert.
ausnahme: zeilen, bei denen in der indizierten spalte
null-values vorliegen, werden nicht indiziert.

Man kann natürlich ein Index darauf machen, nur wird er Null-Value nicht in den Index aufgenommen. Wenn man also auf mit „is Null“ abfragt bekommt man im schlechtesten Fall ein Full Table Scan, ansonsten wird der Index aber benutzt. Kann MySQL den mittlerweile echte Nulls überhaupt?

Neben den weiteren guten Tipps von Erwin ein paar von mir.

traue nie dem Optimizer - gerade den von MySQL gibt es eigentlich nicht. Teste kompliziertere Abfragen aus und gib die die Benutzung von Indices in Zweifel vor (USE INDEX)

Oft liegt die schlechte Performance nicht an der Datenbank oder den Indices, sondern an mangelhaften SQL Statement. Man kann mit schlechten JOINs mehr Unheil anrichten, als die Datenbank wieder ausbügeln kann.

Gruß
Joey

#8

Hallo Burkhard,

Aus Kostengruenden wuerde ich gerne auf mySQL mit InnoDB
zurueckgreifen. Ich hoffe, das ist damit ebenso gut moeglich?
Gibts da Erfahrungen?

Ich selbst habe damit keine Erfahrungen, und obwohl ich tendenziell OpenSource - Fan bin würde ich bei einer professionellen Anwendung eher auf einen der komerziellen Hersteller vertrauen. Eventuell ändert sich da nach der Übernahme durch Sun einiges, aber aktuell würde ich mySQL eher nicht einsetzen, wenn es um komerzielle Software mit kritischen Daten und vor allem Ausfallsicherheit geht.

Ich bin da sicherlich nicht am neuesten Stand, aber falls diese Features ([immer] noch) nicht von mySQL implementiert sind wären das für mich Killerargumente:

  • vollständige Transaktionssicherheit
  • Inline Views
  • Function Based Indexes
  • Partitionierung
  • prozedureler Code

Das Geld, das Du Dir bei den Lizenzen sparst ist im Extremfall - eine entsprechende Nutzerzahl vorausgesetzt - beim ersten Recovery-Fall wieder herinnen. Auch im Bereich Tuning kosten die DBA/Entwickler-Stunden sehr schnell mehr als die Lizenzen für Oracle oder DB2 oder wasauchimmer. Und die Kosten für den Support kannst Du im komerziellen Bereich ohnehin nicht einsparen.

Gruß
Martin