nicht angemeldet
MySQL: Index richtig setzen
Guten Tag.
Bin leider ziemlich unerfahren mit der Thematik und bitte daher um Tipps.
Bei einer MyISAM-MySQL-Tabelle mit ca 2 Mio Zeilen bei insgesamt 1,5GB Größe kommen hauptsächlich SELECT-Queries diese 3 Anfragen:
SELECT COUNT(*) WHERE eineID IN (1,2,3);
SELECT COUNT(*) WHERE eineID IN (1,2,3) AND andereID IN (4,5,6);
SELECT COUNT(*) WHERE eineID IN (1,2,3) AND andereID IN (4,5,6) AND einDatum>"2011-01-01";
eineID und andereID sind vom Typ INT. einDatum ist vom Typ DATE.
Auf eineID liegt der Primärschlüssel und nun ist die Frage, ob es hier sinnvoll ist, 3 verschiedene Indizes zu verwenden, wobei jeweils 1,2 oder 3 Felder (eineID,andereID,einDatum) abgedeckt werden oder jeweils 1 Index pro Feld oder etwas ganz anderes?
Großes Dankeschön
-
Hello,
Bin leider ziemlich unerfahren mit der Thematik und bitte daher um Tipps.
Bei einer MyISAM-MySQL-Tabelle mit ca 2 Mio Zeilen bei insgesamt 1,5GB Größe kommen hauptsächlich SELECT-Queries diese 3 Anfragen:
SELECT COUNT(*) WHERE eineID IN (1,2,3);
SELECT COUNT(*) WHERE eineID IN (1,2,3) AND andereID IN (4,5,6);
SELECT COUNT(*) WHERE eineID IN (1,2,3) AND andereID IN (4,5,6) AND einDatum>"2011-01-01";eineID und andereID sind vom Typ INT. einDatum ist vom Typ DATE.
Auf eineID liegt der Primärschlüssel und nun ist die Frage, ob es hier sinnvoll ist, 3 verschiedene Indizes zu verwenden, wobei jeweils 1,2 oder 3 Felder (eineID,andereID,einDatum) abgedeckt werden oder jeweils 1 Index pro Feld oder etwas ganz anderes?Es erscheint im ersten Moment sicherlich sinnvoll, auf jede der betroffenen Spalten einen eigenen Index zu setzen. Allerdings geht das dann auf Kosten des Zeitbedarfs für Einfüge- und Updateoperationen.
Da nun aber durch die Bildung des Subsets über eineID (die auf jeden fall einen Index haben sollte, geht i.d.R. auch gar nicht anders) die auszuwertende Menge bereits erheblich eingeschränkt ist, würden die weiteren Indexe vermutlich keinen Vorteil ehr bringen.
Wenn also sichergestellt ist, dass nie Selects nur über andereID oder nur über einDatum stattfinden sollen, solltest Du die Indexe auf diese Spalten mMn weglassen zugunsten des besseren Insert-/Update-Verhaltens.
Liebe Grüße aus dem schönen Oberharz
Tom vom Berg
-
Es erscheint im ersten Moment sicherlich sinnvoll, auf jede der betroffenen Spalten einen eigenen Index zu setzen. Allerdings geht das dann auf Kosten des Zeitbedarfs für Einfüge- und Updateoperationen.
Sorry wenn ich nochmal nachfrage, aber empfiehlst du 1 Index für jede einzelne der Spalten oder 1 einzigen Index über alle 3.
INSERT wird in dem System nicht mit Priorität behandelt.
Am wichtigsten wären die 3 angegebenen SELECT...WHERE Queries und ggf. noch UPDATE...WHERE eineID=... (also Update erfolgt anhand des primary key).-
Hello,
Es erscheint im ersten Moment sicherlich sinnvoll, auf jede der betroffenen Spalten einen eigenen Index zu setzen. Allerdings geht das dann auf Kosten des Zeitbedarfs für Einfüge- und Updateoperationen.
Sorry wenn ich nochmal nachfrage, aber empfiehlst du 1 Index für jede einzelne der Spalten oder 1 einzigen Index über alle 3.
"...auf jede der betroffenen Spalten einen eigenen Index zu setzen."
Also wenn überhaupt, dann auf jede Spalte einen eigenen. Alles andere muss der Query-Optimizer leisten.
INSERT wird in dem System nicht mit Priorität behandelt.
Am wichtigsten wären die 3 angegebenen SELECT...WHERE Queries und ggf. noch UPDATE...WHERE eineID=... (also Update erfolgt anhand des primary key).Jede Änderung an einem Index setzt dessen Neuerstellung in Gang. Das ist teuer. Also sollte man Indexe nur dann verwenden, wenn sie einem für das Finden des Datensatzes wirklich helfen.
Da du aber bereits eine (erhebliche?) Einschränkung der Ergebnismenge über den Primärschlüssel vornimmst, würden Indexe über die beiden anderen Spalten nicht mehr helfen.
Du hast uns allerdings nicht gesagt, wieviele Elemente das Set (1,2,3,...,n) für
eineIDbekommen kann.Liebe Grüße aus dem schönen Oberharz
Tom vom Berg
-
Du hast uns allerdings nicht gesagt, wieviele Elemente das Set (1,2,3,...,n) für
eineIDbekommen kann.Gibt es hier eine Einschränkung, ab wann der Query Optimizer "versagt"?
Bisher war ich nämlich im System flott unterwegs und erst seit Überschreiten der Datenbankgröße von 1,4GB dauert viel zu lange.
-
Jede Änderung an einem Index setzt dessen Neuerstellung in Gang.
Wenn bei den UPDATEs keines der indizierten Felder geändert wird, bleibt der Index bestehen.
-
-
-
-
Hallo,
Bei einer MyISAM-MySQL-Tabelle mit ca 2 Mio Zeilen bei insgesamt 1,5GB Größe kommen hauptsächlich SELECT-Queries diese 3 Anfragen:
SELECT COUNT(*) WHERE eineID IN (1,2,3);
SELECT COUNT(*) WHERE eineID IN (1,2,3) AND andereID IN (4,5,6);
SELECT COUNT(*) WHERE eineID IN (1,2,3) AND andereID IN (4,5,6) AND einDatum>"2011-01-01";befrage EXPLAIN nach dem Ausführungsplan.
eineID und andereID sind vom Typ INT. einDatum ist vom Typ DATE.
Auf eineID liegt der Primärschlüssel und nun ist die Frage, ob es hier sinnvoll ist, 3 verschiedene Indizes zu verwenden, wobei jeweils 1,2 oder 3 Felder (eineID,andereID,einDatum) abgedeckt werden oder jeweils 1 Index pro Feld oder etwas ganz anderes?einzig interessante Alternative ist ein kombinierter Index in der Reihenfolge
(eineID, andereID, einDatum), siehe MySQL-Doku, weil dieser für die drei von Dir angegebenen Abfragearten genutzt werden könnte.EXPLAIN kann Dir erläutern, ob es was gebracht hat.
Frage am Rande: sehen die Statements wirklich so aus oder darf man statt der Werteliste der IN-Klausel ein Subselect annehmen?Freundliche Grüße
Vinzenz
-
Hello Vinzenz,
Bei einer MyISAM-MySQL-Tabelle mit ca 2 Mio Zeilen bei insgesamt 1,5GB Größe kommen hauptsächlich SELECT-Queries diese 3 Anfragen:
SELECT COUNT(*) WHERE eineID IN (1,2,3);
SELECT COUNT(*) WHERE eineID IN (1,2,3) AND andereID IN (4,5,6);
SELECT COUNT(*) WHERE eineID IN (1,2,3) AND andereID IN (4,5,6) AND einDatum>"2011-01-01";befrage EXPLAIN nach dem Ausführungsplan.
einzig interessante Alternative ist ein kombinierter Index in der Reihenfolge
(eineID, andereID, einDatum)Jetzt mal Hand aufs Herz. Meinst Du wirklich, dass ein kombinierter Index Vorteile bringt?
Das erste Selektionskriterium ist die ID aus dem Primärschlüssel. Die ist zwangsweise unique inzidiert. Es bleiben dann also sowieso nur die Datensätze aus dem ersten Set (eineID) übrig.
Diese noch auf die restlichen Bedingungen hin zu untersuchen dürfte ohne Index sogar schneller gehen, als mit.
Einzige vorstellbare Ausnahme: Das primäre Set wird doch sehr groß, also nicht nur drei Datensätze, sondern Dreihunderttausend. Aber auf diese Rückfrge habe ich noch keine Antwort erhalten.
Liebe Grüße aus dem schönen Oberharz
Tom vom Berg
-
Hi Tom,
kombinierte Indizes machen durchaus Sinn gegenüber einfachen Indizes. Das kommt aber immer auf den Einzelfall an. EXPLAIN bei mySQL sollte da Auskunft geben können, ob der gesetzte Index angezogen wird oder nicht. Das ist unter anderem abhängig von der Selektivität der Spalten im Index. Eine Pauschalantwort kann man nicht geben
Cheers,
Frank-
moin,
Eine Pauschalantwort kann man nicht geben
meinst du nicht auch, dass Tom diesen einen konkreten fall anspricht, ob dort ein kombinierter index sinn macht und gar keine eine pauschalge aussage wollte ?
Ilja
-
Hello,
Eine Pauschalantwort kann man nicht geben
meinst du nicht auch, dass Tom diesen einen konkreten fall anspricht, ob dort ein kombinierter index sinn macht und gar keine eine pauschalge aussage wollte ?
So ist das!
Ilja, was meinst Du in _diesem_ Fall?Ich habe die Einlassung auf mehrere Indexe (oder auch auf einen Kombinationsindex) durchaus gemacht. Ich meine nur, dass das hier teurer wäre, als wenn man für die weiteren Spalten darauf verzichtet.
Tanja hat uns leider noch nicht erzählt, wieviele Elemente die Menge in der Selektionstufe 1 (nur nach der ID) typischerweise haben kann.
Man darf auch nicht vergessen, dass eine Erweiterung des Primärschlüssels durch einen weiteren Kombinationsschlüssel auch Speicherplatz benötigt, und bei der angedeuteten Datenmenge vermutlich nicht wenig.
Liebe Grüße aus dem schönen Oberharz
Tom vom Berg
-
moin,
Ilja, was meinst Du in _diesem_ Fall?
vom gefühl her würde ich sagen, der primary key sollte reichen, um die abfragen schnell genug zu machen. wichtig ist auch immer, dass die objekte analysiert sind, damit der optimizer die richtigen entscheidungen treffen kann. uns selbst dann ist immer die frage, ob er die richtigen trifft.
Ilja
-
-
nochmal im Klartext: probieren geht über studieren ...
-
-
-
-