Sollten Spalten mit höherer Kardinalität in mehrspaltigen Indizes mit Bereichsabfragen an erster Stelle stehen?

Indizierung mit Spalten höherer Kardinalität zuerst, wenn ein Bereich einbezogen wird

Beachten Sie die folgende Tabelle:

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 ;

Mit a Millionen Zeilen und einer hohen Kardinalität für filetime und einer niedrigeren Kardinalität für ext stellt sich die Frage, welcher Index vorteilhafter ist: fe oder ef.

Analyse mit Force Index und EXPLAIN

Die Verwendung von FORCE INDEX zum Testen beider Indizes zeigt einen deutlichen Leistungsunterschied:

-- Forcing the range on filetime first
mysql> 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;

-- Forcing the low-cardinality ext first
mysql> 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 EXPLAIN-Ausgabe zeigt, dass ef deutlich schneller ist und weniger Zeilen zum Abrufen benötigt Ergebnisse.

Analyse mit dem Optimizer Trace

Der Optimizer Trace bestätigt die Überlegenheit von ef:

"potential_range_indices": [
    ...
    {
        "index": "fe",
        "usable": true,
        ...
    },
    {
        "index": "ef",
        "usable": true,
        ...
    }
],
"analyzing_range_alternatives": {
    "range_scan_alternatives": [
        {
            "index": "fe",
            "ranges": [
                "2015-01-01 00:00:00 <= filetime < 2015-02-01 00:00:00"
            ],
            "cost": 20022,   -- Higher cost
        },
        {
            "index": "ef",
            "ranges": [
                "gif <= ext <= gif AND 2015-01-01 00:00:00 <= filetime < 2015-02-01 00:00:00"
            ],
            "cost": 646.61,  -- Lower cost
        }
    ],
}

Der Trace zeigt, dass ef verwenden kann beide Spalten des Index, was zu einer effizienteren Suche führt. Darüber hinaus wird hervorgehoben, dass der Optimierer nur die erste Spalte „Bereich“ untersucht, wodurch die Kardinalität von ext irrelevant wird.

Schlussfolgerungen

Basierend auf der Analyse ist dies der Fall Es ist klar, dass bei einer Bereichsabfrage mit mehreren indizierten Spalten die Reihenfolge der Spalten wie folgt lauten sollte:

• Beteiligte Spalten einfügen Gleichheitstests zuerst, unabhängig von der Kardinalität.
• Andere am Bereich beteiligte Spalten sollten nach den Gleichheitsspalten platziert werden.

Dieser Ansatz stellt sicher, dass der Index am effektivsten verwendet wird, was zu … optimale Abfrageleistung.

Das obige ist der detaillierte Inhalt vonSollten Spalten mit höherer Kardinalität in mehrspaltigen Indizes mit Bereichsabfragen an erster Stelle stehen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!