Welcher zusammengesetzte Index ist optimal für Bereichsabfragen: Spalten mit hoher oder niedriger Kardinalität?

Spaltenplatzierung mit hoher Kardinalität in zusammengesetzten Indizes mit Bereichsabfragen

Beim Abfragen einer Tabelle mit einem zusammengesetzten Index mit einer Bereichsbedingung wird die Platzierung von Spalten innerhalb des Index können sich erheblich auf die Leistung auswirken.

Betrachten Sie die Tabellendateien mit einem Primärschlüssel (did, Dateiname) und zwei zusammengesetzten Indizes: INDEX(filetime, ext) und INDEX(ext, filetime). Beide Indizes enthalten die Spalte „filetime“, die eine höhere Kardinalität als ext aufweist.

Die Abfrage:

WHERE ext = '...'
  AND filetime BETWEEN ... AND ...

erfordert den Zugriff auf Daten basierend auf ext und filetime. Es stellt sich die Frage: Welcher Index ist für eine solche Abfrage optimal?

Analyse

Um den optimalen Index zu ermitteln, können wir FORCE INDEX verwenden und die Ausführungspläne untersuchen:

-- Force range on filetime first
FORCE INDEX(fe) SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

-- Force low-cardinality ext first
FORCE INDEX(ef) SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

Die Ausgabe zeigt, dass INDEX(ext, filetime) (ef) eine deutlich geringere Zeilenanzahl hat, was auf eine effizientere Vorgehensweise hinweist scannen.

Optimierer-Trace

Um das Verhalten des Optimierers weiter zu analysieren, können wir den Optimierer-Trace verwenden:

SELECT explain_format = 'JSON';

SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

Der Trace zeigt, dass der Der Optimierer wählt INDEX(ext, filetime), da er beide Spalten des Index zum Filtern und Abrufen von Daten verwenden kann. Im Gegensatz dazu kann INDEX(filetime, ext) nur die erste Spalte (filetime) zum Filtern verwenden.

Schlussfolgerungen

Basierend auf der Analyse können die folgenden Schlussfolgerungen gezogen werden gezeichnet:

• Für zusammengesetzte Indizes, die in Bereichsabfragen verwendet werden, ist die Spalte, die am Gleichheitsprädikat beteiligt ist (ext in diesem case) sollte in der Indexdefinition an erster Stelle stehen.
• Die Abfrageleistung wird verbessert, wenn die Spalten in einem Index entsprechend der Reihenfolge sortiert werden, in der sie in der WHERE-Klausel verwendet werden.
• Kardinalität allein ist kein entscheidender Faktor für die Bestimmung des optimalen Index. In Szenarien, in denen die Bereichsspalte eine höhere Kardinalität aufweist, die Gleichheitsspalte jedoch an einem Gleichheitsprädikat beteiligt ist, führt die Platzierung der Gleichheitsspalte an erster Stelle zu einer besseren Leistung.

Das obige ist der detaillierte Inhalt vonWelcher zusammengesetzte Index ist optimal für Bereichsabfragen: Spalten mit hoher oder niedriger Kardinalität?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!