Top-Themen

Aktuelle Themen (A bis Z)

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit

gelöst Indizes vernünftig aufbauen

Mitglied: Maik87

Maik87 (Level 2) - Jetzt verbinden

17.10.2019, aktualisiert 19:20 Uhr, 365 Aufrufe, 8 Kommentare

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
Mitglied: SeaStorm
LÖSUNG 17.10.2019 um 20:53 Uhr
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.
Bitte warten ..
Mitglied: Maik87
17.10.2019 um 21:01 Uhr
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??
Bitte warten ..
Mitglied: SeaStorm
17.10.2019 um 22:13 Uhr
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?
Bitte warten ..
Mitglied: Maik87
17.10.2019 um 23:01 Uhr
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.
Bitte warten ..
Mitglied: SeaStorm
18.10.2019 um 10:15 Uhr
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 ...
Bitte warten ..
Mitglied: akretschmer
20.10.2019 um 09:48 Uhr
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.
Bitte warten ..
Mitglied: SeaStorm
20.10.2019 um 13:19 Uhr
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 ?
Bitte warten ..
Mitglied: akretschmer
20.10.2019 um 14:16 Uhr
Was ist Oracle?

Vermutlich aber, ja.
Bitte warten ..
Ähnliche Inhalte
Netzwerkgrundlagen
Aufbau Firmen Lan . . .
Frage von LaBombaNetzwerkgrundlagen12 Kommentare

Hallo, da das Forum hier wirklich TOP ist wende ich mich mit meinen Fragen mal wieder an euch :) ...

Netzwerkmanagement
Router + Firewall aufbau
Frage von zxSpectrumNetzwerkmanagement11 Kommentare

Hallo, ich bin gerade dabei mein LAN zu planen und habe ein paar grundlegende Fragen. Ich habe gehofft, dass ...

Datenbanken
Aufbau einer MYSQL Datenbank
Frage von WPFORGEDatenbanken12 Kommentare

Hallo, Ich habe etwa 1 mio Datensätze. Mit ekelhaft vielen Feldern (ca. 120). Je nach Kategorie des Datensatzes weichen ...

Netzwerkgrundlagen
Aufbau Heimnetzwerk mit Firewall
gelöst Frage von KodaCHNetzwerkgrundlagen11 Kommentare

Guten Abend Für eine andere Wohnung würde ich gerne das komplette Netzwerk neu aufbauen. Ich habe bei offenen Fragen ...

Neue Wissensbeiträge
Humor (lol)
Das IoT wird schlimmer
Erfahrungsbericht von Henere vor 7 StundenHumor (lol)

Nun auch schon über den WSUS:

Sicherheit

Win10 1809 und höher erlauben nun das Sperren und Whitelisten von bestimmten Geräten

Tipp von DerWoWusste vor 17 StundenSicherheit1 Kommentar

Vor 1809 konnten nur Geräteklassen gesperrt werden, nun können endlich einzelne Device instance IDs gewhitelistet werden (oder andersherum: gesperrt ...

Windows 10

Hands-On: What is new in the Windows 10 November 2019 Update?

Information von DerWoWusste vor 23 StundenWindows 10

Die wenigen (aber zum Teil interessanten) Neuheiten werden in diesem Video sehr schnell erklärt und vorgeführt.

Grafik

Gute Spiele aus der Ubuntu Repository: SuperTuxKart

Information von NetzwerkDude vor 1 TagGrafik1 Kommentar

Fall jemand die Firmenpolicy hat das man Linux Software nur aus dem default Repository installieren kann: Ich habe festgestellt ...

Heiß diskutierte Inhalte
Netzwerke
VPN auf Firmennetzwerk (Festplatten, Computer) einrichten, aber wie?
Frage von 81083Netzwerke34 Kommentare

Hallo, es ist ein Bisschen frustrierend. Wir haben einen 2012 R2 Server, eine Fritzbox und etwa 10-12 PC die ...

Ubuntu
Ubuntu-Putty hilfe
Frage von Nickolas.GroheUbuntu29 Kommentare

Hallo Wie ändere ich einen ssh Port auf Linux Ubuntu? LG Nickolas

Windows 7
Festplatte in einen anderen PC umziehen lassen
Frage von Ghost108Windows 725 Kommentare

Hallo zusammen, ich bekomme die nächsten Tage einen neuen PC (komplett andere Hardware als in meinem jetzigen) Was für ...

Windows Tools
Suche Suchprogramm
Frage von tsunamiWindows Tools24 Kommentare

Hallo, ich brauche einen Tipp für ein profesionelles Suchprogramm. Es geht um rund 3 TB Dokiumente auf ner externen ...