maik87
Goto Top

Indizes vernünftig aufbauen

Hallo zusammen,
ich möchte eine Datenbank mit Indizes deutlich beschleunigen. Dazu fehlen mir aber ein paar grundlegende Infos. Ich mache mal ein Beispiel:

Ich habe eine Tabelle:
JAHR - MONAT - TAG

In der sind für sagen wir mal 10 Jahre für jeden Kalendertag ein Eintrag darin. Möchte ich nun den 03.05.2018 auswerten, was geht dann schneller?
Ein Index JAHR - MONAT - TAG oder TAG - MONAT - JAHR? Sprich von der Datenmenge her eine Aufteilung in 10 - 12 - 31 oder 31 - 12 -10.

Ist es ein Unterschied in welcher Reihenfolge die Felder in der WHERE-Klausel sortiert sind oder ist MONAT = 5 AND JAHR = 2018 AND TAG = 3 genau so schnell wie JAHR = 2018 AND MONAT = 5 AND TAG = 3 (also gleiche Reihenfolge wie Index)?

Denke das war erstmal das Wichtigste...

Danke euch
Viele Grüße
Maik87

Content-ID: 505867

Url: https://administrator.de/contentid/505867

Printed on: December 2, 2024 at 20:12 o'clock

SeaStorm
Solution SeaStorm Oct 17, 2019 at 18:53:19 (UTC)
Goto Top
Hi

In dem Fall ist es egal wie Rum die Indexe erstellt werden, so lange alle 3 Felder im Index sind. Die Reihenfolge ist auch im query egal.
Wenn ein query mehrere Ergebnisse ausgibt und diese auf oder absteigend sortiert, dann sollte der Index auch entsprechend die Sortierung haben. Das nennt sich dann Covering-Index.
Maik87
Maik87 Oct 17, 2019 at 19:01:51 (UTC)
Goto Top
Super, danke dir! Das hilft schon mal sehr.

Wenn ich jetzt weniger Felder eingrenze als der Index beinhaltet, dann funktioniert das so lange, bis von links gesehen das erste Index-Feld nicht mehr in der Bedingung ist, oder?

Wieder Beispiel:
Tabelle JAHR - MONAT - TAG und Index genau so.
Abfrage mit Jahr Monat und Tag ist perfekt.
Jahr und Monat ist auch gut.
Jahr und Tag so naja.
Monat und Tag doof
und Tag allein ganz blöd, oder??
SeaStorm
SeaStorm Oct 17, 2019 at 20:13:15 (UTC)
Goto Top
Ne. Wenn du das im Index hast ändert das where hier in diesem Fall nix.
Das blickt der Query optimizer und sortiert halt nach dem was er hat und braucht.
Doof und ganz doof ist zu viel im Index nicht.
Man könnte mehr rausholen, aber es wirkt sich erst Mal nicht negativ aus.
Doof wäre es, wenn du einen Index mit Jahr DESC und Monat DESC hast, deine Abfrage aber DESC + ASC hat.

Grundsätzlich gilt: wenn man die Queries an die Tabelle in der Hand hat und das überschaubar ist, dann versucht man Covering Indexe zuachen. Also den Index mit Feldern und Sortierung genau wie die Abfrage.

Wenn nicht, dann lieber einzelne Felder in einzelne Indexe. Der Query Optimizer nimmt dann die Indexe die er braucht jeweils. Außerdem lernt er auch, wenn er eine Konstellation findet, die oft genutzt wird, und kompiliert sich das dann schon Mal im vorraus, so daß er da nicht mehr gucken muss.

Auch nicht zu vernachlässigen ist die korrekte Verwendung von Grouped und nongrouped Indexen!

Reden wir hier von MSSQL?
Maik87
Maik87 Oct 17, 2019 at 21:01:54 (UTC)
Goto Top
Mein Problemfall bezieht sich jetzt auf Datenbanktabellen in SAP, die nicht performant laufen. Wird vermutlich Oracle dahinter liegen - bin mir aber nicht sicher. Natürlich ist es aber auch schön, mal grundsätzlich zu verstehen und auch in anderen Datenbanken anwenden zu können.

Wir haben mehrere größere Tabellen. Primary-Key ist natürlich so aufgebaut, wie die Datensätze eindeutig sind. Manchmal sind aber auch Felder mitten drin enthalten, die für die Abfragen irrelevant sind und niemals mit in einer Bedingung stehen werden. Zugegriffen wird unterschiedlich auf die gleichen Tabellen. Also mal mehr und mal weniger Bedingungsfelder, mal nur welche aus dem Key, mal aber auch aus den Datenspalten.

Also wieder Beispiel oben:
Key: Jahr, Monat, Tag
Datenfeld: Wochentag

Mal soll gesucht werden, welcher Wochentag an einem bestimmten Datum ist. Mal ist es interessant in welchem Monat der Freitag auf den 13. fällt. Das wiederum mal nur für ein bestimmtes Jahr, mal ganz egal in welchem Jahr. Ich denke, dass dieses Beispiel mein Problem ziemlich genau trifft.

Für mich ist es jetzt die Frage, wie ich die Indexe aufbaue. Meine ersten googleergebnisse sagten sinngemäß, dass ich einen Index aufbauen soll der quasi von links nach rechts gelesen die Felder von "in allen Querys genutzt" in Richtung "in nur einer Query genutzt" hinterlegen soll. Es würden wohl - so wie ich es verstanden habe - immer nur die Felder von links an genutzt bis er eins findet, welches ich nicht in der Bedingung habe. Dann würde wohl der Index aufgrund der Vorsortierung aussteigen und den Rest dann wieder "langsam in der Tabelle suchen".

Doof und ganz doof war nicht als schlechter und noch schlechter gemeint als ohne Index sondern als Index mit wenig und Index mit noch weniger Wirkung.
SeaStorm
SeaStorm Oct 18, 2019 at 08:15:53 (UTC)
Goto Top
Zitat von @Maik87:

Wir haben mehrere größere Tabellen. Primary-Key ist natürlich so aufgebaut, wie die Datensätze eindeutig sind. Manchmal sind aber auch Felder mitten drin enthalten, die für die Abfragen irrelevant sind und niemals mit in einer Bedingung stehen werden. Zugegriffen wird unterschiedlich auf die gleichen Tabellen. Also mal mehr und mal weniger Bedingungsfelder, mal nur welche aus dem Key, mal aber auch aus den Datenspalten.
deshalb kann man ja auch mehrere kleinere Indexe bauen.

Meine ersten googleergebnisse sagten sinngemäß, dass ich einen Index aufbauen soll der quasi von links nach rechts gelesen die Felder von "in allen Querys genutzt" in Richtung "in nur einer Query genutzt" hinterlegen soll.
Das impliziert ja, das man nur einen Index hat, der dann alle Felder beinhaltet. Das wäre ein beschissener Index.
Es würden wohl - so wie ich es verstanden habe - immer nur die Felder von links an genutzt bis er eins findet, welches ich nicht in der Bedingung habe. Dann würde wohl der Index aufgrund der Vorsortierung aussteigen und den Rest dann wieder "langsam in der Tabelle suchen".
Das wäre mir neu. Evtl. OracleDB\HANA speziefisches verhalten. glaube ich aber nicht. Wäre ja eine total idiotische Vorgehensweise.

Ich würde hier einen Convering mit JAHR MONAT TAG machen, und den WochenTag als Include definieren. Da man bei der suche nach einem definierten Datum ja höchstwahrscheinlich den WT dazu auch haben will, hängt der im Ergebnis schon mal an und muss nicht mehr einzeln gepicked werden.
Dazu einen Index auf jedes Feld einzeln. Damit ist für den Optimizer jede Spalte vorsortiert erreichbar und er kann sich das zurechtlegen wie er es braucht. Damit sind Abfragen wie "gib alle Freitage, die ein 13er sind auch schnell erledigt.

Dein Bsp. ist ein bisschen blöd. Das behandelt ein äusserst seltenes Szenario bei dem ein Index eigentlich eine untergeordnete Rolle spielt.
Hier sind kaum Sortiervorgänge vorhanden, was ja das eigentliche Performanceproblem bei SQL ist.
Das reine raussuchen von einem Datensatz anhand einer Bedingung ist für den SQL trivial, da die Daten per Heap und Leaf ja fix auffindbar sind.
Interessanter wird es wie gesagt, wenn grössere Ergebnissmengen vorhanden sind und diese sortiert werden.

Bsp:
Personalnummer [int]
Name [string],
Vorname [string],
Geburtstag [date],
Geschlecht [int] (würde ja bool sagen... aber das ist ja ein heißes Eisen ;) )

Sagen wir, wir haben hier 5000 Einträge und darunter befinden sich 20 Karen's


Jetzt willst du hier alle "Karen"'s haben, von Alt nach Jung. Ohne zusätzliche Indexe ist hier erst mal weder Heap noch Leaf vorhanden.
Suchst du jetzt nach "where name=Karen order by geburtstag ASC", wird erst mal eine FullTableScan über den Namen gefahren. FTS bedeutet: JEDER Datensatz wird angefasst und nach "Startet mit "K?" Wenn ja: "Geht weiter mit A?" usw abgearbeitet. Dann sind irgendwann alle Karens bekannt.
Hierbei entsteht eine Schnittmenge, die den Namen enthält und einen Verweis auf den Datensatz.
Diese Schnittmenge wird jetzt um die dahinterliegenden Geburtstage erweitert. Erweitern bedeutet in dem Fall:
Gehe über den Verweis zum Datensatz und lies das Geburtsdatum aus.
Dann wird die Schnittmenge sortiert und ausgegeben.

Baue ich einen (covering)Index auf Name und Geburtstag, ändert sich das in:
Der Index Schaut nach dem Leaf mit "K*", (sagen wir er unterteilt hier nicht weiter und der Leaf endet hier. (Je nach Datenmenge würde er das aber tun) und es fallen ein paar Datensätze raus, die die Personalnummer, Name und Datum sowie einen Verweis zum Datensatz enthält. Diese sind nach Datum ASC sortiert.
Jetzt werden die Datensätze noch über die obige logik nach Karen gefiltert. Sortiert ist das alles schon, also kann das in die Ausgabe.

Der langsamste Vorgang in dem ganzen ist erst mal der FTS, weil 5000 Datensätze angefasst werden müssen.
Danach die Eingrenzung auf die Textsuche nach Karen an den Datensätzen.
Danach das "folgen" vom Verweis mit dem Pick des zusätzlichen Datensatzen. Das entfällt allerdings beim Index, weil hier die Daten schon im Index mitgegeben wurden.
Dann die Sortierung nach dem Datum. Auch das entfällt beim Index, weil schon vorsortiert

Mit dem Index fällt der FTS weg. Hier würden vielleicht noch 200 Datensätze angefasst werden müssen, weil sich 200 Namen in der Tabelle befinden, die mit K anfangen.
Sortieren muss man das auch nicht mehr.


Will ich jetzt noch den Vornamen dazu haben, wäre ein zusätzlicher Schritt nötig, diesen vom Datensatz auszulesen.
Ohne Index muss erst mal alles so weit gefiltert werden wie bisher.
Dann folgt man den Verweisen und holt den zusätzlichen Datensatz.
Jetzt kann ich an dem Index ja eine Included Collumn mitgeben. Das bedeutet: An diesem Index wird DIESE Spalte immer mit gespeichert. Wird der Index verwendet, liegen diese Daten also sofort vor, ohne das man dem Verweis auf den eigentlichen Datensatz folgen muss.
Das hat den Vorteil, das es etwas schneller ist, aber auch den Nachteil, das diese Spalte also mindestens 2x existiert. 1x am Datensatz selbst natürlich, und am Index. Das kostet natürlich Speicherplatz. Ausserdem muss bei jeder Datenänderung(Update, Delete, insert) diese Information 2x gespeichert\geändert werden.
Hier muss man abwägen. Eine Tabelle die statisch ist, braucht halt etwas mehr Speicher, kann schneller Ergebnisse liefern und braucht keine zusätzlichen IOs für die Pflege des Indexes (weil sich ja nix oder wenig an den Daten ändert).
Eine Tabelle die permanente Änderungen hat, kann zwar auch schneller abgefragt werden und braucht mehr Speicher, erzeugt aber eine höhere Grundlast am System, weil die Indexe ja bei jeder Änderung mitgepflegt werden.

Ergo: Statische Tabellen: Lieber mehr Indexe mit passenden Includes. Speicher ist heute kaum noch ein Problem. Der performancegewinn überwiegt in der Regel.
Häufig geänderte Tabelle: Sparsamere Indexe vergeben. Convering wo möglich. Includes mit bedacht wählen.

Bei Clustered Indexen ist diese Problematik der sich ändernden Tabellen übrigens noch genauer zu betrachten.
Ein Clustered Index gibt die physikalische Speicherung der Daten auf der Festplatte vor. Ist dein CI z.B auf einem Text, dann wird bei einer Insert die richtige Position für den Datensatz gesucht, der Part darunter wegkopiert, der neue Datensatz eingefügt und das verschobene wieder drunter gehängt. Viel Arbeit also.
Entsprechend sollten Clustered Indexe so gewählt werden, das die Datensätze immer nur unten angehängt werden, und nicht irgendwo mitten drin eingefügt werden, und das dieser Index sich dann auch nicht mehr ändert. Eine Unique ID mit Autoincrement ist also der perfekte Clustered Index. Ausserdem ist der Clustered Index in jedem normalen Index als Include vorhanden.


Ok ... das wurde jetzt ein bisschen länger ...
akretschmer
akretschmer Oct 20, 2019 at 07:48:57 (UTC)
Goto Top
Zitat von @Maik87:

Hallo zusammen,
ich möchte eine Datenbank mit Indizes deutlich beschleunigen. Dazu fehlen mir aber ein paar grundlegende Infos. Ich mache mal ein Beispiel:

Ich habe eine Tabelle:
JAHR - MONAT - TAG

In der sind für sagen wir mal 10 Jahre für jeden Kalendertag ein Eintrag darin. Möchte ich nun den 03.05.2018 auswerten, was geht dann schneller?
Ein Index JAHR - MONAT - TAG oder TAG - MONAT - JAHR? Sprich von der Datenmenge her eine Aufteilung in 10 - 12 - 31 oder 31 - 12 -10.

Warum spaltest Du ein Datum so auf? Das ist Murks.



Ist es ein Unterschied in welcher Reihenfolge die Felder in der WHERE-Klausel sortiert sind oder ist MONAT = 5 AND JAHR = 2018 AND TAG = 3 genau so schnell wie JAHR = 2018 AND MONAT = 5 AND TAG = 3 (also gleiche Reihenfolge wie Index)?


Die Antwort gibt EXPLAIN.


Denke das war erstmal das Wichtigste...


Ähm, nein. Die Idee, ein Datum derart zu zertrümmern ist einfach Müll. DAS ist das Wichtigste, was Du verstehen solltest.
SeaStorm
SeaStorm Oct 20, 2019 at 11:19:48 (UTC)
Goto Top
ist doch nur ein Bsp ... Schlecht gewählt, ja. Aber es geht ja nur um den grundsätzlichen Aufbau.

Die Antwort gibt EXPLAIN.
Hat Oracle sowas auch ?
akretschmer
akretschmer Oct 20, 2019 at 12:16:28 (UTC)
Goto Top
Was ist Oracle?

Vermutlich aber, ja.