Spaltenplatzierung mit hoher Kardinalität in zusammengesetzten Indizes mit Bereichsabfragen

Bei der Abfrage einer Tabelle mit einem zusammengesetzten Index mit einer Bereichsbedingung wird die Platzierung von Spalten im 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 Dateizeitspalte, die eine höhere Kardinalität als ext hat.

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'
  AND filetime 
Die Ausgabe zeigt, dass INDEX(ext, filetime) (ef) eine deutlich geringere Zeilenanzahl hat, was auf einen effizienteren Scan hinweist.
Optimierer-Trace
Um das Verhalten des Optimierers weiter zu analysieren, können wir den Optimierer verwenden Trace:
SELECT explain_format = 'JSON';

SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime 
Die Ablaufverfolgung zeigt, dass der Optimierer INDEX(ext, filetime) wählt, 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, sollte die am Gleichheitsprädikat beteiligte Spalte (in diesem Fall ext) sein an erster Stelle in der Indexdefinition platziert.
• 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 bei der Bestimmung des optimalen Indexes. In Szenarios, 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.