MySQL-Mehrere Indizes vs. Mehrspaltenindex für die Suche

Question

In der Software, die ich schreibe, hat man die Möglichkeit, in einer bestimmten Tabelle nach Informationen zu suchen. Das Suchformular hat 5 Felder, die alle natürlich verschiedenen Spalten in einer Tabelle entsprechen, aber alle Felder sind optional.

Meine Frage bezieht sich darauf, ob Multi-Spalten-Indizierung funktionieren wird und wie man eine Abfrage dafür richtig erstellt.

Wenn ich einen einzigen Index über 5 Spalten habe und eine Abfrage erstelle, um sie zu durchsuchen, wenn es um Felder in diesem Index geht, die ich nicht durchsuche, mache ich dann etwas wie:

field1 = 10 UND field2 > 0 UND ...

Oder sollte ich die nicht verwendeten Spalten überhaupt nicht einschließen?

Ich habe nach Informationen über Mehrspalten-Indizes gesucht, aber ich kann anscheinend nicht finden, was zu tun ist, wenn Sie eine der Spalten in einem gegebenen Index überspringen müssen oder wenn Sie sich einfach in diesem speziellen Fall nicht darum kümmern.

Answer 1

Sie müssen verstehen, dass in MySQL (im Fall von InnoDB) nur das am weitesten links stehende Präfix Ihrer Indizes verwendet wird. Wenn der Index also in Field1 + Field2 liegt, kann eine Abfrage mit nur Field2 im WHERE den gesamten Index nicht nutzen.

Und wenn Field1 + Field2 + Field3 der Index ist, würde dasselbe passieren, wenn eine Abfrage nur mit Field1 & Field3 im WHERE gemacht wird.

Option 1

Sie müssten für jedes Suchszenario einen separaten Index erstellen, wenn Sie jede Suche optimieren möchten. Wenn die Tabellen jedoch groß sind, würden auch die Indizes extrem groß werden. Wenn diese Suchabfragen jedoch sehr häufig gestellt werden, würde sich das lohnen.

Option 2

Sie können einen cleveren Trick anwenden, wenn Ihre Suchen eine niedrige Selektivität aufweisen (z. B. Geschlecht), indem Sie die Spalten mit niedriger Selektivität am weitesten links platzieren und Gender IN(M, F) verwenden, um sie in das WHERE der Klausel einzuschließen zusammen mit den anderen Spalten, um den gesamten Index zu nutzen.

Answer 2

Für Abfragen mit Kriterien

field1 = 10 UND field2 > 0

Sie müssen einen zusammengesetzten Index field1 + field2 (in dieser speziellen Reihenfolge) haben

Answer 3

Es hängt von Ihren Abfragen ab. Wenn Sie viele Abfragen mit Feld1 und Feld2 häufig durchführen werden, dann macht es Sinn, einen zusammengesetzten Index zu haben. Andernfalls können Sie mit mehreren einzelnen Spaltenindizes gut zurechtkommen.