MYSQL: Wozu dient ein Index über mehrere Spalten?

Hi,

ich möchte eine meiner (u.a.) Websites optimieren ( http://janeway.exotica.org.uk/search.php ) und habe da eine Tabelle die hat bis dato keine eigene (auto increment) Indexspalte, diese Zahl finde einfach nirgendwo sonst eine Verwendung, sondern einen Index über drei Felder, die UNIQUE sein sollten. Jetzt hat sich heraus gestellt, dass zu dieser Uniqueness noch eine vierte Spalte hinzu kommen muss, um bestimmte Fälle zu ermöglichen, in denen es in den drei bisherigen Spalten zu doppelten Schlüsselsätzen kommt. Und wenn ich aber schonmal dabei bin, frage ich, warum ich nicht gleich den Index durch einen Auto-Increment ersetze, als dass ich ihn um diese neue Spalte erweitere. Also, was bringt mir das eine oder das andere?

danke
ags

@AnGSt

Möchtest du verschiedene Fälle rausfiltern können, indem du nach bestimmten Kombinationen in diesen Spalten suchst? Dann lass die Spalten bleiben.

Möchtest du nur, dass jeder Datensatz unique ist? Dann ist es natürlich viel simpler, einfach einen auto-increment-key zu nehmen.

@Thawra

Das ging ja schnell, :), warst Du im life mode? Ja was möchte ich denn, mal sehn. Soweit ich weiß, werden diese drei als kombinierter Index bestehenden Spalten auch einzeln abgefragt, so nach dem Motto: Bringe mir alles aus Spalte 2 was in Spalte 1 den Wert A hat. Die Kombination benutze (benutzte) ich eigentlich nur um mir einen Index zu sparen und das ganze auch gleich unique zu haben was bisher eindeutig sein soll. Und der andere Grund warum ich keinen Autoinkrement Index verwende, ist, dass ich bis dato einen solchen Wert in keiner anderen Kombination gebraucht habe. Alles was ich mir jetzt denken kann, ist höchstens, dass einmal noch ne Tabelle mit Infos zu manchen Datensätzen aus der fraglichen Tabelle hinzu kommt. Aber ich wüsste jetzt nicht warum und wozu.

@AnGSt

Wenn die auch einzeln abgefragt werden, dann lass die Spalten vielleicht lieber bleiben und füge halt noch eine hinzu, um diese letzten Fälle auch noch abzufangen. Ist zwar nicht ideal, aber wenn man die Spalten sowieso braucht...

Ergibt halt einen etwas grösseren Schreibaufwand, wenn man bei z.B. in einer Tabelle alle nötigen Datensätze ermittelt hat und jetzt in der ersten andere zugehörige Informationen abfragen muss - statt "WHERE ID='...';" muss man halt so immer "WHERE spalte1='...' AND spalte2='...' AND spalte3='...' AND spalte4='...';" schreiben. Andererseits - es gibt Schlimmeres.

@Thawra

Die Tabelle wird eher selten geschrieben. Ok, ich mache es auf Index über vier Spalten. Falls sich da was ändern sollte kann ich ja immer noch eine extra Index-Spalte hinzu fügen.

Wobei, die Frage war auch, wozu ein Index über mehrere Spalten letztendlich gut ist?

@AnGSt

Genau. Wenn du jetzt eine unique-key-Spalte hinzufügst, musst du eh alle Abfragen, die ev. irgendwo automatisiert sind, umschreiben - oder aber die key-Spalte macht dann auch nicht so viel Sinn.

Wenn du hingegen einfach noch eine weitere Index-Spalte hinzufügst, dann musst du nur wo nötig die halt auch noch abfragen lassen.

@Thawra

Effektiv ergibt sich da, wenn ich die vierte Spalte aus dem Index weg lasse, nur das Problem, dass das Eingabe-Backend das Schreiben blockiert weil dann zwei oder mehr Datensätze gleich aussehen. In der Abfrage ist sowieso alles speziell. Mal wird eine Spalte gebraucht, mal nicht.

@AnGSt

Möchtest du denn diese Datensätze auf jeden Fall unterscheiden können, oder ist eher das automatische Blockieren des Backends das Problem?

@Thawra

Tja, wenn ich das so genau wüsste! Alles was ich mir denken kann, dass es dann ausversehen zu doppelten Einträgen kommen könnte. Wenn mir nicht noch ein anderer Grund ein fällt.

Ahja, ein primärer Index kann ja nur Unique sein ...

@AnGSt

Wenn du einen primary key einführst, den dann aber nicht wirklich benutzt, dann kann man natürlich leicht x-mal dieselben Daten eingeben, versehentlich oder nicht. Das ist dann zwar in der db durch den key unterschieden, aber wenn du den nicht wirklich beachtest, dann sieht jeder Eintrag gleich aus.

Ich weiss ja nicht, was du machst bzw. wozu die Datenbank dient, aber eventuell willst du das verhindern.

Wenn du hingegen mehrere Index-Spalten hast, die eine bestimmte Bedeutung haben (halt 0=bezahlt, 1=noch nicht bezahlt oder so was) und die bei Eingabe geprüft werden, dann ist es unmöglich, zweimal genau dieselben Daten einzugeben.

Die Tabelle hat 26000 Einträge. Es gibt die drei Unique-Index Spalten und daneben dann diese jetzt neue vierte von der ich spreche und dann noch ein Textfeld und zwei Bytefelder. Das Textfeld und die zwei Bytefelder werden nicht immer gebraucht, so dass ich diese über einen Increment Key in eine zugehörige Infotabelle auslagern könnte. Das spart ein paar Bytes pro Eintrag in der Haupttabelle, die sich aber für den Index dann wieder fast weg rechnen. Also bringt ein extra Index sogesehen zur Zeit wenig.

Ich glaube, dass es jetzt nicht zu doppelten Einträgen kommen kann, war nur ein Nebeneffekt aus der Notwendigkeit von überhaupt irgendeinem Index (?) und der Nichtnotwendigkeit von einem eigenen Schüssel. Ich habe mir halt wohl gedacht "wozu mehrere indizes wenn auch einer geht"... ob das stimmt ... ?

@Thawra

@AnGSt

Da sind wir wieder bei meiner Frage anfangs - sagen die Indizes was aus, was man abfragen können soll? Offenbar ja, drum müssen die da bleiben.

Ich hab in meinem Ruderclub alle Mitglieder mit einem unique key. Der ist aber ansonsten relativ nutzlos, denn er sagt genau gar nichts aus (ausser dass ich die Nummer Eins bin, weil ich die Datenbank erstellt habe... und weil ich Webmaster bin :D ). Die Sache ist eben: es könnte rein theoretisch mal zwei Mitglieder mit exakt denselben Daten geben, drum brauche ich noch was, was unique ist.

Wenn du dir jedoch keine Sorgen zu machen brauchst, dass zwei Einträge mal genau gleich sein könnten, dann kannst du die Eigenschaften der Datensätze, die da in den Index-Spalten gespeichert sind, natürlich auch gleich sozusagen als key mitbenutzen, sie identifizieren die Einträge bereits eindeutig.

@Thawra

Sie sagen etwas aus von dem mir im Moment bloß nicht bewusst ist, ob ich es brauche. ;)

Ich belasse es aber dann trotzdem bei einem primären Index aus jetzt vier spalten. Ob man was an der Performance machen kann, steht auf einem anderen Blatt. Ich wüsste jetzt nicht auswendig, welches Abfragemuster für die Tabelle überwiegt. Und Geschwindigkeitsprobleme habe ich da noch nicht bemerkt. :)

@AnGSt

Wenn diese Datenbank auf einem anständigem Server läuft, sollte Geschwindigkeit in dem Fall eigentlich kein Problem sein... kommt natürlich auch drauf an, ob man zwanzig Abfragen pro Tag hat oder hunderttausend. ^^

@Thawra

Wir haben ca 150 Besucher am Tag, die im Schnitt jeweils schon so einige Abfragen machen. Mir fällt bloß auf, dass gelegentlich meine eigenen Abfragen beim Ausprobieren etwas länger als erwartet dauern. Bei der Suche wenn es viele Treffer gibt oder wenn Seiten mit viel Inhalt angezeigt werden sollen.

Mindestens eine unique spalte sollte jede Tabelle haben. bei der mysql praktischerweise eine autoincrement.
Über welche Spalten du einen combined index erzeugst, hängt von deinen select-anweisungen ab.
du kannst auch mehrere anlegen. nur bei _wirklich_ großen Tabellen musst du dir gedanken bei inserts machen (wann und wie wird der index neu erstellt -> performance)

26000 zeilen könnte die datenbank als häppchen verstehen, und den index ignorieren/umgehen.

und das sagt dir mysql mit explain: explaint select * from foo

Unsere größte Tabelle hat 200.000 Zeilen. :)

weisst du wie die einzelnen selects aussehen? und welche die häufigste ist?
falls ja: mit explain sich diese ansehen.
wenn zb in einer select die spalten 12, 15 und 18 oft abgefragt werden, dann lege über diese einen index.
je nach arbeitsspeicher am cache der mysql drehen (macht sie zum teil auch selber).
oder die datenbank in ein ramdrive legen. oder die tabelle als memory definieren ....

ohne joins bzw subselects sind auch mehrere millionen zeilen gar kein thema.

AnGSt schrieb am 17.09.2014:Wobei, die Frage war auch, wozu ein Index über mehrere Spalten letztendlich gut ist?

Als Schnellzugriff auf in dieser Kombi häufig verwendete Daten.
Ich weiss nicht wie es in MySQL ist aber prinzipiell sind Index und Unique Constraint ja zwei paar Schuhe.
Auch wenn man sie in einem Statement erstellen kann.

Sinn eines Index ist prinzipiell der Performancegewinn va bei Suche / Sortieren durch Vermeiden sequentiellen Suchens.
Unique und andere Constraints dienen dem Einhalten von Daten-Integrität.