Les colonnes à cardinalité plus élevée devraient-elles venir en premier dans les index multi-colonnes avec des requêtes par plage ?

Indexation avec des colonnes de cardinalité plus élevée en premier lorsqu'il s'agit d'une plage

Considérez le tableau suivant :

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 ;

Avec un millions de lignes et une cardinalité élevée pour filetime et une cardinalité plus faible pour ext, la question se pose de savoir quel index est le plus avantageux : fe ou ef.

L'analyse avec Force Index et EXPLAIN

L'utilisation de FORCE INDEX pour tester les deux index révèle une nette différence de performances :

-- 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;

La sortie EXPLAIN indique que ef est nettement plus rapide, utilisant moins de lignes pour récupérer le résultats.

Analyse avec l'Optimizer Trace

La trace Optimizer confirme la supériorité de 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
        }
    ],
}

La trace révèle que ef peut utiliser les deux colonnes de l'index, ce qui permet une recherche plus efficace. De plus, il souligne que l'optimiseur n'examinera que la première colonne « plage », ce qui rend la cardinalité de ext non pertinente.

Conclusions

Sur la base de l'analyse, il est clair que lorsqu'il s'agit d'une requête de plage impliquant plusieurs colonnes indexées, l'ordre des colonnes doit être :

• Put les colonnes impliquées dans les tests d'égalité en premier, quelle que soit la cardinalité.
• Les autres colonnes impliquées dans la plage doivent être placées après les colonnes d'égalité.

Cette approche garantit que l'index est utilisé le plus efficacement possible. , ce qui permet d'obtenir des performances de requête optimales.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!