Indexierung von Primary Key Was macht das für nen Sinn?

[Mik3e]

Hi zusammen,

Habe gerade gesehen, dass man einen Primary Key in einer MyIsam Tabelle theoretisch zusätzlich mit einem Index belegen kann!?

Hat das eigentlich irgendeinen Sinn Mein Wissensstand war bisher, dass Primary Keys sowieso automatisch indiziert werden!? Oder bring ich da jetzt etwas durcheinander

LG
Mike

 

[bn]

Hallo,

rein theoretisch kann ein zusätzlicher Index durchaus Vorteile bringen, was aber von verschiedenen Faktoren abhängt und in der Praxis selten Sinn macht. Es bringt zum Beispiel nichts, einen weiteren Index nur über das Feld des Primary Key´s zu legen, da dieser ja bereits ein Index ist.
Ein Vorteil entsteht erst dann, wenn möglichst viele Felder einer WHERE-Bedingung in einem mehrspaltigen Index enthalten sind. Sofern sich deine Suchabfragen also lediglich auf den Primary Key beziehen (SELECT * FROM bla WHERE id = 10), bringt ein weiterer Index eher Nachteile als Vorteile. Hier müßte die Datenbank nun entscheiden, welcher Index der geeignetere ist (kostet minimal Zeit) und beim Beschreiben der Datenbank müssen nun 2 Indexe "gewartet" werden. Bei "komplexeren" Abfragen (SELECT * FROM bla WHERE name LIKE 'jochen' AND ort LIKE 'berlin' AND id IN(10, 20, 23, 35355,535); (völlig sinnfreie Abfrage)) könnte ein zusammengesetzter Index aus bestehend aus name, ort und id Sinn machen, auch wenn id bereits PRIMARY KEY ist.

Betrachte die Beispiele bitte nicht zu akribisch - mir ist nichts sinnvolles eingefallen. In der Regel lohnt es sich nämlich nicht, den Primary Key als weiteren Index anzulegen. Aber Ausnahmen bestätigen ja gewöhnlich die Regel.

mfg bloddy newbie

 

[Mik3e]

Hi,

Vorweg danke..
Für alphanumerische Textfelder gibt es ja sowieso die Volltextindexierung, die (soweit ich jetzt gelesen habe) mit anderen algorithmen und einem etwas anderen Strukturbaum arbeitet...
Am häufisten bestehen Tabellen für gewöhnlich ja aus:

- Primary Key
- Foreign Key 1
- Foreign Key 2
....
- Daten A
- Daten B
- Daten C

Hier lege ich normalerweise einen Index auf die Foreign Keys, da diese ja häufig in Joins genutzt werden.

Mein Problem ist nun, das diese Indexierung natürlich enorme Vorteile bei der Suche in der Where Clause bringen (bzw. im Join, da dort die Indexierung meines wissens nach auch greift), dementgegen steht aber wieder der Performanceverlust beim Schreiben (wie Du bereits erwähnt hast).

Daher bin ich eigentlich auf der Suche nach einer Art "Kochrezept", wann man Indexierung anwenden soll und wann nicht. Ich stelle mir da etwas vor wie:
"Wenn in der Tabelle X im Schnitt 80% Lesequeries und 20% Schreibqueries abgesetzt werden, ist eine Indexierung sinnvoll"..

Ist wie gesagt nur ein Beispiel, aber ich konnte nirgendwo ein wirklich gutes Tutorial zum Thema Indexierung finden. Die waren etweder sehr "low-level" mäßig oder extrem technisch (d.h. erklärung der Algorithmen etc.).

Kennst Du evtl. ein gutes Tutorial zum Thema Indexierung

LG
Mike

 

[WieGehts]

Hallo,
ob es ein gutes Tutorial zum Thema Indexierung gibt, weiß ich nicht.
Aber vielleicht kann ich dir mit ein paar Ideen weiterhelfen:
1. Grundsätzlich würde ich sagen, dass Felder für Verknüpfungen von Tabellen immer indiziert werden sollten.
2. es kommt im Wesentlichen nicht darauf an wie das Verhältnis zwischen Schreib- und Lesezugriffen ist. Viel wichtiger ist, wie lange dauert der Zugriff beim Lesen bzw. in welcher Zeit muss das Ergebnis zur Verfügung stehen.
Wird eine Abfrage z. B. einmal im Jahr gemacht und es spielt keine Rolle, wenn die Antwortzeit 1 Stunde ist, kann man auf den Index verzichten. Wird dagegen eine Abfrage jede Minute mehrmals ausgeführt, wird auf jeden Fall ein Index benötigt werden.
3. die meisten Datenbanken stellen Analysetools zur Verfügung, mit denen untersucht werden kann, wie lange was dauert. Wenn du deine DB optimal konfigurieren willst, musst du dich damit auseinandersetzen. Aber das ist nichts für ein paar Minuten, das ist was für DB-Admins und da haben die meisten auch Probleme.

 

[Exceptionfault]

Grundsätzlich würde ich sagen, dass Felder für Verknüpfungen von Tabellen immer indiziert werden sollten.

Richtig, und zwar unbedingt. In Datenbanksystemen welche Relationale Integrität unterstützen, sprich Constrainst über Primary- und Foreign Keys können bei fehlenden Indizes auf Foreign Keys sogar dramatische Locksituationen eintreten. Z.b. wenn ein Update auf die Kopftabelle gemacht wird ( enthält PK ) wird die koplette Details Tabelle ( mit FK ) gelockt und für den Zugriff durch andere Benutzer gesperrt.

es kommt im Wesentlichen nicht darauf an wie das Verhältnis zwischen Schreib- und Lesezugriffen ist. Viel wichtiger ist, wie lange dauert der Zugriff beim Lesen bzw. in welcher Zeit muss das Ergebnis zur Verfügung stehen.

Jein. Sicher spielt die erwartete Anwortzeit eine Rolle, besonders in OLTP Applikationen. Man darf den Overhead von Indizes bei DML aber nicht vernachlässigen. Bis zu 30% Performanceeinbruch bei Update, Insert und Delete sind pro Index zu kalkulieren. Hat man also sehr sehr viele Änderungen im Vergleich zu Abfragen macht es Sinn über eine Art Staging Area nachzudenken, also die reinen Daten mit mögl. wenig Indizes, und andere ( mgl. verdichtete ) Datentabellen mit Indizes für die Abfrage, allerdings mit Zeitversatz. (In Oracle heist so eine Lösung beispielsweise Materialzed View oder MView)

Wird eine Abfrage z. B. einmal im Jahr gemacht und es spielt keine Rolle, wenn die Antwortzeit 1 Stunde ist, kann man auf den Index verzichten

Nun, 1xpro Jahr ist natürlich extrem selten, man darf aber auch nicht vergessen, dass eine schlechte Abfrage an die Datenbank die komplette Performance der Applikation auch für längere Zeit in die Knie zwingen kann. Grund:
Eine Abfrage ohne Index erzeugt einen FULL TABLE SCAN, sprich jeder Satz in der Tabelle muss mindestens einmal gelesen werden. Bei Joins und Sortieroperationen müsssen zudem oft noch Daten auf der Platte ausgelagert werden. Jede Datenbank hat einen Buffer in dem häufig genutze Daten gehalten werden um Anfragen schneller abzuarbeiten. Führt man nun einen umfangreichen FULL TABLE SCAN durch wird ein großer Teil des Buffers dafür genutzt und die häufig verwendeten Daten rausgeworfen. Das führt dazu, dass nach der schlechten Abfrage auch die anderen Abfragen erst warten müssen bis die Daten wieder von der Platte in den Buffer geladen wurden.

die meisten Datenbanken stellen Analysetools zur Verfügung, mit denen untersucht werden kann, wie lange was dauert.

Interessant sind hier die sog. Execution Pläne. Die Datenbank erzeugt für jede Abfrage einen solchen Plan in dem versucht wird den bestmöglichen Zugriff auf die Daten zu sichern. D.h. welcher Index wird verwendet, welches SubSelect wird zuerst ausgeführt, welche Join Methode wird verwendet etc...
Anhand eines solchen Execution Plans sollte eigentlich jedes Statement einer Applikation optimiert werden, allein das kann bei komplexen Anwendungen mehrere 100! % Performancegewinn bringen ohne auch nur einen Parameter der Datenbank zu tunen.