MySQL: Indizes verteilen /performance verbessern

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

Liebe/-r Experte/-in,

ich habe MySQL-Tabellen, in die ich auf einen Schlag 25.000 Datensätze integriert habe.
Diese verarbeite ich mit php.
Seitdem sich meine Tabellen auf so schlagartig vergrößert haben, brechen meine Skripte ab.
Ich habe deshalb versucht herauszufinden, wie ich meine MySQL performance erhöhen kann und festgestellt, dass ich wohl indizes hätte vergeben sollen.

Ich bin mir nun allerdings nicht sicher, wie ich diese Indizes verteile und ob ich danach meine Queries ändern muss.

Hier mal ein Bsp:

eine meiner queries:
SELECT location FROM locations
WHERE location_code =‚DEA58‘
ORDER BY FIELD(language,‚DE‘, ‚EN‘, ‚CZ‘, ‚DE‘, ‚DK‘, ‚EE‘, ‚EN‘, ‚ES‘, ‚FI‘, ‚FR‘, ‚HU‘, ‚IT‘, ‚LT‘, ‚LV‘, ‚NL‘, ‚PL‘, ‚PT‘, ‚RO‘, ‚SE‘, ‚SI‘, ‚SK‘, ‚TR‘, ‚BG‘, ‚HR‘, ‚GR‘, ‚IS‘, ‚IE‘, ‚GE‘, ‚RU‘) LIMIT 1

Sollte ich nun einen Index auf location_code legen? Oder muss der Index auf location?

Wenn ich den Index nachträglich anlege mit
ALTER TABLE locations ADD INDEX index_location_code ( location_code ),
werden dann alle Einträge automatisch indiziert, oder nur neue Einträge?

Bleibt meine query unverändert, oder muss ich dann nach index_location_code suchen, um den Index zu nutzen?

Vielen Dank für eure Hilfe!!

mfg,
David

#2

Hallo David,

beim Indizieren gibt es viele Regeln, für die man ein wenig Gefühl benötigt. Allgemein werden Indexe dafür eingesetzt, daß etwas schneller gefunden wird!

Es macht kein Sinn, Felder zu indizieren, nach denen NICHT gesucht wird! Je mehr Indizies angelegt wurden, um so mehr wird „geschrieben“, wenn ein oder viele Records in die Tabelle eingefügt werden. Also jeder Index muß dann zusätzlich verändert werden!

Kleine Tabellen indiziert man nicht, wenn diese nicht extrem anwachsen! Die Anzahl der Records können dabei 100 oder 10000 sein, je nach Breite der Tabelle, also abhängig von der Anzahl der Spalten und Spaltenbreite!

Kommen wir zu Ihrem Beispiel zurück:

Sie haben eine WHERE-Anweisung und suchen nach location_code, also sollte location_code indiziert sein! Der SQL-Parser wird also zuerst die Menge an Datensätzen eingrenzen, die aufgrund der Where-Anweisung, gefiltert werden.

Als Nächstes, führen Sie eine Sortierung durch, wobei der Parser, die vorher gefilterte Menge durchsuchen und ordnen muß. Auch hier hilft ein zusätzlicher Index auf language!

Einen Index auf locations zu setzen, macht hier kein Sinn, weil dieses Feld zum Suchen oder Sortieren nicht verwendet wird. Sollte dies natürlich in einem weiteren Script erfolgen und es darauf nicht nur seltene Einzelabfragen geben, kann man natürlich auch dieses Feld indizieren.

Wenn eine Tabelle statisch ist und nicht oft verwendet wird, kann die natürlich viele Indizies enthalten, weil kein „schreiben“ erfolgt. Eine Tabelle, die oft verwendet wird, sollte wirklich nur dort indiziert werden, wo es nötig ist.

Ist die Tabelle zu klein oder erfolgen die Such- bzw. Sortierabfragen in Verbindung mit für den Parser nicht auflösbaren Formeln, wird der Index unter Umständen nicht verwendet!

Hier sollte man SQL-Abfragen mit EXPLAIN prüfen und die Ausführungspläne verstehen lernen. Dabei sind eine höhere Anzahl von Schritten, die der Parser durchführen muß, teilweise dennoc schneller, als wenn dies in einem Schritt durchgefürt wird. Beim Auflösen der Abfrage, werden unter Umständen in Bruchteilen einer Sekunde, kleine Zwischentabellen im Speicher angelegt und diese Tabellen mit einander verknüpft. Nur beim Ausgeben, erfolgt dann der Abruf aller anderen Daten aus der Tabelle.

Beachten Sie, daß Schreiben immer länger dauert, als lesen! Alles, was im Speicher (RAM) bearbeitet werden kann, ist viel schneller, als Zugriffe auf die Platte (so lange diese noch magnetisch sind - es kommen ja inzwischen neue SSD-Platten auf Basis Flash Memory, die fast so schnell, wie RAM-Speicher sind - nur halt noch sehr teuer und klein sind)

Ach so, Sie können die Indizierung (also Index neu anlegen - oder löschen), jederzeit ändern und dann werden die Indexes über den ALTER - Befehl direkt angelegt (für alle Sätze in der Tabelle) oder gelöscht.

Viel Spaß weiterhin mit MySQL

1awww.com - Internet-Service-Provider
Detlef Bracker

Ich habe deshalb versucht herauszufinden, wie ich meine MySQL
performance erhöhen kann und festgestellt, dass ich wohl
indizes hätte vergeben sollen.

#3

Hallo,

Liebe/-r Experte/-in,

ich habe MySQL-Tabellen, in die ich auf einen Schlag 25.000
Datensätze integriert habe.

Ist nicht wirklich viel, ich hatte schon Tabellen mit als einer Million Datensätze, bei denen es keine Fehler gab.

eine meiner queries:
SELECT location FROM locations
WHERE location_code =‚DEA58‘
ORDER BY FIELD(language,‚DE‘, ‚EN‘, ‚CZ‘, ‚DE‘, ‚DK‘, ‚EE‘,
‚EN‘, ‚ES‘, ‚FI‘, ‚FR‘, ‚HU‘, ‚IT‘, ‚LT‘, ‚LV‘, ‚NL‘, ‚PL‘,
‚PT‘, ‚RO‘, ‚SE‘, ‚SI‘, ‚SK‘, ‚TR‘, ‚BG‘, ‚HR‘, ‚GR‘, ‚IS‘,
‚IE‘, ‚GE‘, ‚RU‘) LIMIT 1

Sollte ich nun einen Index auf location_code legen? Oder
muss der Index auf location?

Auf location_code, weil das steht im WHERE.
Wenn da viel übrig bleibt, was hinter noch sortiert werden muss, ist es ggfs. auch effektiver, statt FIELD() jedes Mal zu benutzen das Ganze mit einer 2. Tabelle (language, field_order) zu joinen und nach dieser field_order dann zu sortieren.

Wenn ich den Index nachträglich anlege mit
ALTER TABLE locations ADD INDEX index_location_code (
location_code ),
werden dann alle Einträge automatisch indiziert, oder nur neue
Einträge?

Es werden alle indiziert, das ADD INDEX dauert daher eine Weile.

Bleibt meine query unverändert, oder muss ich dann nach
index_location_code suchen, um den Index zu nutzen?

Die Query bleibt gleich.
Du kannst ggfs. mit explain select … nachschauen, ob es funktioniert, also welche Indexe er verwendet.

Alex

#4

Hey David,
also es werden auch die alten indiziert und alles sollte schneller laufen. D.h. das die mysql brauchte einige Zeit um das Kommando auszuführen und der Index wird grösser auf der Platte.
Details, wie man Queries optimiert findest Du hier:
http://dev.mysql.com/doc/refman/5.5/en/execution-pla…
Der ‚explain‘ Befehl ist da sehr hilfreich.
In Deinem Beispiel sollte aber alles funktionieren, ohne dass Du am Quelltext (oder den SQL Statements) etwas ändern musst.

#5

Leg mal den index auf location_code

#6

Hallo David,
ein Index ist dann hilfreich, wenn:

  1. die Tabelle sehr viele Datensätze enthält,
    2.a. für Felder, auf die mit WHERE oder mit ORDER BY zugegriffen wird, und
    2.b. in denen relativ wenige verschiedene Werte in entsprechender Häufung stehen.
    Dein Feld location_code ist mit seinen 25000 Datensätzen und anscheinend nur 29 verschiedenen Werten ein solcher Kandidat. Ob das auch für das Feld location zutrifft, kann ich nicht sagen, weil ich nicht weiß, wieviele verschiedene Werte darin enthalten sind. Wenn das Feld im Extremfall unique ist, also keine 2 gleichen Werte enthält, dann bringt auch ein Index nix.
    An dem Query ändert sich durch das Anlegen eines Index’ nichts. Das macht MySQL alles brav automatisch.
    Möglicherweise liegt der Performanceeinbruch an der nicht-alphabetischen Sortierung, was dann tiefere Eingriffe in die Struktur der Datenbank erforderlich machte.
    MfG, Marius Winter.
#7

Hallo David,

die Frage ist nicht MySQL Spezifisch:
* für die genannte Abfrage dürfte der Location_code die geeigente Indexspalte sein. (Ein Index lohnt sich nur, wenn die gewünschte Abfragemenge kleiner als ein Achtel der Gewsamtmenge ist [Faustregel])
* Ich frage mich aber gerade, wieso in locations eine Sortierung nach Sprache notwendig ist: Hast Du mehrere Einträge pro Locationcode -nur differenziert nach Sprache- in der Tabelle (dann solltest Du das Datenbanklayout überarbeiten)?
* Ein Index auf location.location_code und(!) location.location würde nur Sinn machen, wenn die Tabelle sehr(!) breit ist, sodass ein sogenannter Index-Only Scan wesentlich weniger Datenseiten lesen muss. Ein Index auf location.location macht absolut keinen Sinn (denn die gesamte Tabelle muss dann für die Bestimmung der Where gelesen werden und dann könnte man erst auf den Index zugreifen, aber der gesuchte Wert wäre schon in den gelesen Werten vorhanden -> also wird er nicht genutzt.
* Ein Index wird über alle Werte angelegt und bei jeden Insert und jeden Update auch geändert. Deshalb direkt eine Warnung: Zuviele Indizes können auch die Änderung der Daten langsamer machen!
* Nein ein Index ist dazu da, den Datenbankserver bei einer performaten Abarbeitung der Anfrage zu helfen, Du kannst / must bei Deiner Abfrage auf der Tabelle bleiben. SQL ist übrigens dafür gebaut worden, den Programmierer von der Struktur der Daten (und Indizierung) zu trennen.
* Es gibt Situationen, wo man (und jetzt wird es SQL-Dialekt abhängig) seine Abfrage mit Hinweisen auf Indizes ändern muss. Das sind aber Situationen, bei den die Datenbank ungenaue Statistikwerten von den vorhandenen Daten besitzt und / oder die Abfrage sich in der Nähe eines chaotischen Schnittpunkt des Abfragealgorithmus befindet. Hier darfst Du dann gerne nochmal nachfragen, aber ich bin mir sicher, dass dies noch lange dauern wird, bist Du in diese Situation kommst.

MfG Georg V.

#8

meiner meinung nach wirkt sich ein index auf alle einträge der tabelle aus und dein query kann bleiben wie es ist.
aber warum sortierst du eine tabelle nach so vielen feldern wenn du am ende nur ein ergebnis haben willst (limit 1). musst du dann überhaupt sortieren?

#9

hi,

sorry, kann dir da im moment auch nicht weiterhelfen.

cu
harald
*******************

#10

Hallo,

ich hoffe die Anfrage ist noch aktuell. Das mit den Indizes ist schon richtig, ich möchte mal folgende Hinweis grob geben, da mit dem aktuellen Informationsstand keine konkrete Hilfe möglich ist.

  • es sollten die Spalten indexiert werden, die in der where oder order by Klausel verwendet werden.
  • beim erstellen eines neuen Index werden alle Informationen in den Index übernommen, der Index ist also ab sofort verwendbar.
  • ob ein Index in der SQL verwendet wird kann man sehen, wenn man vor die SELECT-Anweisung einfach EXPLAIN schreibt.

Für mehr Informationen hilft Google sicher gerne, aber Indizes werden sicher gut tun in diesem Fall.

Viele Grüße

Alex

#11

Hallo David,

ein Index auf location_code ist der richtige, denn das ist das Suchkriterium.
Das Anlegen eines Index führt dazu, dass der vorhandene Datenbestand indiziert wird (Achtung! das kann ein Weilchen dauern), und natürlich auch alle folgenden Einträge in dieser Tabelle.
Der Query bleibt unverändert.

Allerdings … könnte dieser Query Probleme durch das ORDER BY FIELD(…) verursachen. Du rufst zwar letztendlich nur einen Record ab (LIMIT 1), aber das ORDER BY muss der MySQL Server zunächst auf die gesamte Ergebnismenge anwenden. Durch die FIELD Angabe ist keine Indizierung möglich, daher muss der Server eine temporäre Tabelle erzeugen und darin sortieren. Das muss als solches noch kein Drama sein - aber wenn die Ergebnismenge groß ist und der RAM Speicher begrenzt, muss diese Op sogar auf Festplatte durchgeführt werden, und dann geht die Performance wirklich in den Keller.

Ich empfehle, nach Anlegen des Index zunächst den Query mit EXPLAIN zu prüfen: In irgendeinem Interface (phpMyAdmin oder was du sonst benutzt) den Query mit vorangestelltem EXPLAIN absetzen.
http://dev.mysql.com/doc/refman/5.1/de/explain.html
Wenn du in der Ausgabe von EXPLAIN in der Spalte rows eine mehrstellige Angabe findest, oder der Query im Betrieb nach wie vor schlecht performed, muss das noch optimiert werden.
Eine reine SQL-Lösung fällt mir dafür nicht ein.
Mein Vorschlag:

  1. Statt des einfachen Index auf location_code einen zusammengesetzten auf location_code und language.

  2. In der Programmierung alle Sprachen in einer Schleife abklappern, bis es ein Ergebnis gibt. Um das nicht unnötigerweise zu machen, wenn es gar kein Ergebnis gibt, vorher das noch prüfen.
    Das erzeugt zwar maximal soviel Queries, wie du Sprachen hast, plus einem für das Count. Aber jeder einzelne Query ist blitzschnell beantwortet wg. optimaler Indizierung. Nach meiner Erfahrung mit sehr großen Datenbeständen ist das wesentlich performanter als eine Op, die eine große Ergebnismenge sortiert. Und ein vielleicht nutzbare Mehrwert ist noch, dass du weißt, für welche Sprache du ein Ergebnis bekommen hast.
    Pseudocode, PHP-Style:

    // Prüfung, ob überhaupt ein Ergebnis
    $sql = „SELECT COUNT(*) AS recordCount FROM locations WHERE location_code =‚DEA58‘“;
    // Query absetzen, Ergebnis in Variable
    // …
    $recordCount = $result[‚recordCount‘];

    if (! $recordCount) {
    // Kein Ergebnis => Fehlermeldung o.ä.
    // …
    }

    else {
    $languages = array(‚de‘, ‚en‘, …);
    $location = null;
    $locationLanguage = null;
    foreach ($languages as $language) {
    $sql = „SELECT location FROM locations
    WHERE location_code =‚DEA58‘ AND language = ‚$language‘“;
    // Query absetzen, Ergebnis in Variable
    // …
    $location = $result[‚location‘];
    if ($location) {
    $locationLanguage = $language;
    break;
    }
    }
    if (! $location) {
    // Kein Ergebnis => Fehlermeldung o.ä.
    // das kann wg. dervorab-Prüfung nur passieren, wenn die
    // languages-Liste nicht vollständig ist!
    // …
    }
    else {
    // $location (und $locationLanguage) verwenden …
    // …
    }

Viele Grüße
Thomas