Hohe Kardinalität vs. niedrige Kardinalität: Welche Spalte sollte in einem Bereichsindex an erster Stelle stehen?

Spaltenreihenfolge mit hoher Kardinalität in einem Bereichsindex

Beim Datenbankdesign kann beim Erstellen eines Indexes für mehrere Spalten die Reihenfolge dieser Spalten geändert werden wirken sich erheblich auf die Leistung bei Bereichsabfragen aus. Insbesondere bei Abfragen, die sowohl eine Spalte mit hoher Kardinalität als auch eine Spalte mit niedriger Kardinalität umfassen, kann die optimale Spaltenreihenfolge im Index kontraintuitiv sein.

Szenario:

Bedenken Sie a Tabelle mit dem folgenden Schema:

CREATE TABLE `files` (
  `did` int(10) unsigned NOT NULL DEFAULT '0',
  `filename` varbinary(200) NOT NULL,
  `ext` varbinary(5) DEFAULT NULL,
  `fsize` double DEFAULT NULL,
  `filetime` datetime DEFAULT NULL,
  PRIMARY KEY (`did`,`filename`),
  KEY `fe` (`filetime`,`ext`),          -- This?
  KEY `ef` (`ext`,`filetime`)           -- or This?
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

Diese Tabelle speichert Dateimetadaten mit etwa 1 Million Zeilen. Die Spalte „filetime“ hat größtenteils unterschiedliche Werte, was auf eine hohe Kardinalität hinweist, während die Spalte „ext“ eine endliche Anzahl von Werten aufweist, was auf eine niedrige Kardinalität hinweist.

Abfrage:

Die folgende Abfrage wird verwendet, um Dateiinformationen basierend auf ext und filetime abzurufen:

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

Bester Index Auswahl:

Es stellt sich die Frage, welcher Index, fe oder ef, für diese Abfrage besser wäre. Intuitiv könnte man annehmen, dass der Index mit der Spalte mit hoher Kardinalität (filetime) zuerst effizienter wäre. Die Analyse zeigt jedoch, dass das Gegenteil der Fall ist.

Erklärung:

Mit dem EXPLAIN-Befehl können wir den Abfrageplan auf verschiedene Indexoptionen untersuchen:

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

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

Die Ergebnisse zeigen, dass der Index ef, mit der ext-Spalte mit niedriger Kardinalität zuerst, deutlich besser abschneidet als fe.

Eine weitere Analyse mit dem Optimierer-Trace zeigt, dass der Optimierer mit dem fe-Index schätzungsweise eine große Anzahl von Zeilen (16684) durchsucht hat, um nach dem ext-Wert zu filtern. Mit dem ef-Index könnten beide Indexspalten effizient genutzt und nur die relevanten Zeilen (538) abgerufen werden.

Schlussfolgerung:

Beim Erstellen eines zusammengesetzten Index zur Unterstützung des Bereichs Bei Abfragen empfiehlt es sich, die am Gleichheitstest beteiligte Spalte (in diesem Fall ext) unabhängig von ihrer Kardinalität zuerst zu platzieren. Dadurch kann der Index im Abfrageausführungsplan effektiver genutzt werden, was zu einer verbesserten Leistung führt.

Das obige ist der detaillierte Inhalt vonHohe Kardinalität vs. niedrige Kardinalität: Welche Spalte sollte in einem Bereichsindex an erster Stelle stehen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!