[GUIDE]Datenbank durch MySQL-Indizes beschleunigen

[Mashkin]

Hallo Comm.

Heute wollte ich euch noch mal etwas technisch anspruchvolleres nahebringen.

Das Thema: Indizes in MySQL

Zunächst einmal eine kleine Erklärung, was das bringt:

 Spoiler

Ein Index ist ein irgendwie geordnetes oder ungeordnetes Verzeichnis, mit dem sich Datensätze zu indizierten Schlüsseln zuordnen lassen.

Ist ein Index nicht geordnet, ergibt er in unserem Fall keinen Sinn - gehen wir also von einem sortierten Index aus.
Wählt man zum Beispiel einen numerischen Schlüsseltyp (int, shortint, longint etc.), wird dieser nach Größe sortiert und in einer Baum-artigen Struktur gespeichert.
Dadurch ist möglich, mit sehr kleinen und schnellen Operationen den richtigen Schlüssel zu finden.

Aber das sei erst einmal genug der Theorie.
Hier jetzt mal das (bei Metin2 wohl denkbar beste) Beispiel dazu:
Die "player.item"-Tabelle:

Betritt ein Spieler den Server, so werden unter anderem alle seine Items aus der Datenbank abgerufen. In vereinfachter Form sieht das etwa so aus:

SELECT * FROM player.item WHERE owner_id = 12345;

Hier mal einige Charakteristika dieser Query:

• gesucht wird anhand der gegebenen "owner_id",
• es ist nicht bekannt, wie viele Ergebnisse die Query haben wird,
• die gesuchten Datensätze könnten überall in der Tabelle zu finden sein.

Daraus ergibt sich, dass zur Ausführung dieser simplen Query die gesamte "item"-Tabelle gelesen und durchsucht werden muss.
Bei einem gut besuchten Server können das gut zigtausende bis Millionen von Datensätzen (Items) sein.

Da ein Spieler einen Server nur recht selten betritt, bringt das Verbleiben der Ergebnisse im RAM sehr wenig.
Ganz im Gegenteil: für jeden Spieler muss erneut die gesamte Tabelle durchsucht werden.

Hier kommen jetzt Indizes ins Spiel:
Da die meisten Queries wie oben beschrieben als Suchkriterium das Feld "owner_id" besitzen, ist es sinnvoll einen Index über jenes Feld anzulegen.
Dann können in diesem Index sehr schnell alle gesuchten Datensätze anhand des Feldes "owner_id" gefunden werden (Erinnerung: der Index mit dem Schlüssel "owner_id" ist nach dessen Größe aufsteigend sortiert).
Die Suche des richtigen Bereiches im Index kann mit (vergleichsweise) simplen größer-/kleiner-Vergleichen durchgeführt werden.

Generell lassen sich das Problem und dessen Lösung auf viele andere Szenarien übertragen.
Ein sinnvoller Einsatz des Schemas wäre z.B. auch für die Tabelle "player.player" ein Index über das Feld "level".
Damit ließe sich eine Rangliste mit Level-Sortierung deutlich beschleunigen.

Einrichtung:

 Spoiler

Ich habe festgestellt, das einige im Umlauf befindliche Datenbanken bereits über verschiedene Indizes verfügt.

In Navicat lassen sich Indizes einer Tabelle recht simpel unter Design Table > Indexes verwalten.
Alternativ kann man mit folgender Query einen Index erzeugen (passend zum obigen Beispiel):

ALTER TABLE `item` ADD INDEX `owner_id_idx` (`owner_id`) USING BTREE
-- oder --
CREATE INDEX `owner_id_idx` USING BTREE ON `item` (`owner_id`);

Aber ACHTUNG: Wenn ein Index komplett neu erstellt wird, kann dies sehr lange dauern und arbeitsintensiv sein.
Bei sehr großen Servern kann das indizieren der "item"-Tabelle mehrere Stunden dauern!
Besser erledigt ihr solche Aufgaben nachts oder sehr früh morgends oder aber während einer (längeren) Wartung.
Am Besten legt ihr die Indizes schon vor dem Serverstart an, dann wird er sich "ganz natürlich" füllen.

Detailiert Informationen findet man auch unter folgenden Links:

Vom Admin eines führenden Servers (ich weiß nicht ob er genannt werden will) habe ich bisher nur positive Rückmeldungen erfahren.
Vor der Einrichtung der Indizes beklagten sich Spieler über sehr lange Ladezeiten bei Mapwechseln und Login.


Ich hoffe, ich konnte euch ein wenig weiter bringen und euch näher an MySQL heranführen.
Vielleicht können einige meine beispielhaften Erläuterungen auf andere Anwendungsgebiete übertragen.
Durch richtige Datenpflege lässt sich einiges an Leistung aus MySQL herausholen und einige Vorgänge erheblich beschleunigen.

MfG,
~M@shkin

Blog-Post:

[iYoshix3]

Danke hat mir ja sehr geholfen und ich denke auch das es anderen
helfen kann / wird.

Mfg

Danke hat mir ja sehr geholfen und ich denke auch das es anderen
helfen kann / wird.

Mfg

[lautlosertot]

kann ganz erlich in den kleinen release part sorry aber ich gucks mir später man an^^

[Mijago]

@lautlosertot, ich finde den "Kleinen Release Thread" sowieso total Sinnlos, denn eigentlich steht es im Auge des Betrachters, ob ein Release "Groß" (=sehr nützlich) oder "klein" ist..

Aber nunja, ich Release ja eh nichts mehr..


btt:
Ich wurde letztens auch nach den Indizes gefragt (da hat einer meiner Kontakte mal Navicat durchstöbert hehe), da hab ich's ihm auch erklärt, dass das die DB beschleunigen könnte..

Besonders bei der Quest- und Log-Tabelle ist das von vorteil

lg Mijago

[exi°]

Klasse Struktur des Textes und super erklärt. Top!

[Zymos]

Kann man das nachträglich jederzeit machen
oder direkt am serverstart?

Man bemerkt man einen unterschied?

[Nick]

Gute Struktur & Vorgehensweise! Muss man an dieser Stelle mal erwähnen.
Dementsprechend bringst du das Ganze rüber.

[Perfection-]

sry das ich jetzt frage aber die querys bzw mysql reagiert dan auf die indexe von selber oder muss man dann am query oder ähnliches noch irgend etwas machen?

also so wie ich es verstanden habe ist es nur eine mysql technische sache und mysql entscheidet selber ob es sich an dem index orientiert oder nicht?

[Mashkin]

Originally Posted by Mijago

btt:
Ich wurde letztens auch nach den Indizes gefragt (da hat einer meiner Kontakte mal Navicat durchstöbert hehe), da hab ich's ihm auch erklärt, dass das die DB beschleunigen könnte..

Besonders bei der Quest- und Log-Tabelle ist das von vorteil

lg Mijago

Quest-Tabelle ja, Log-Tabelle wüsste ich nicht (ist ja eigentlich eher Read-Only).

Originally Posted by Zymos

Kann man das nachträglich jederzeit machen
oder direkt am serverstart?

Man bemerkt man einen unterschied?

Wie ich schon sagte:

Originally Posted by M@shkin

Aber ACHTUNG: Wenn ein Index komplett neu erstellt wird, kann dies sehr lange dauern und arbeitsintensiv sein.
Bei sehr großen Servern kann das indizieren der "item"-Tabelle mehrere Stunden dauern!
Besser erledigt ihr solche Aufgaben nachts oder sehr früh morgends oder aber während einer (längeren) Wartung.
Am Besten legt ihr die Indizes schon vor dem Serverstart an, dann wird er sich "ganz natürlich" füllen.

Je voller die Tabelle wird, desto länger wird das Erstellen des Index dauern.
Ist der Index erst einmal mit der Tabelle synchron, stört er nicht mehr.

Bei großen Servern kann man sehr gut merken, wie sich Ladezeiten beim Mapwechsel, Login, Lager etc. verkürzen.
Aber auch bei kleineren Servern ist es den Aufwand sicher wert.

Originally Posted by .Xero

sry das ich jetzt frage aber die querys bzw mysql reagiert dan auf die indexe von selber oder muss man dann am query oder ähnliches noch irgend etwas machen?

also so wie ich es verstanden habe ist es nur eine mysql technische sache und mysql entscheidet selber ob es sich an dem index orientiert oder nicht?

Beim Ausführen der Query sucht die Engine automatisch nach dem besten "Pfad" zu den gesuchten Datensätzen. "Where"-Klauseln, die das indizierte Feld (z.B. "owner_id") als verpflichtende Bedingung anführen, gewinnen am meisten:

SELECT * FROM item WHERE owner_id = 12345 AND vnum = 54321;

Ist das indizierte Feld nur optional, sinkt der Vorteil dieses einen Index:

SELECT * FROM item WHERE owner_id = 12345 OR vnum = 54321;

Solche Queries sollten aber rein logisch ziemlich selten vorkommen.