Quelle configuration d'index (colonne de plage en premier ou colonne de cardinalité faible en premier) est optimale pour les requêtes de plage sur les colonnes à cardinalité élevée et faible ?

Quelle configuration d'index est optimale lorsque vous travaillez avec des requêtes de plage impliquant des colonnes de cardinalité élevée et faible ?

Dans le scénario donné, nous avons une table 'files' avec une clé primaire sur 'did' et 'filename', et deux index supplémentaires : 'fe' sur 'filetime' et 'ext' et 'ef' sur 'ext' et 'filetime'. Notre requête implique de filtrer les lignes en fonction de « ext » et de « filetime » à l'aide de conditions de plage.

Explorons quelle configuration d'index est la plus efficace pour cette requête.

Évaluation des options d'index

Pour déterminer l'indice optimal, nous pouvons analyser l'utilisation potentielle de l'indice et les estimations de coûts en utilisant EXPLIQUER :

Forçage fe (colonne de plage en premier) :

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;

Forçage ef (colonne de cardinalité faible en premier) :

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;

L'analyse

EXPLAIN suggère que l'utilisation « ef » (colonne de faible cardinalité en premier) donne lieu à un plan d'exécution plus efficace que « fe ». En effet, « ef » permet à l'optimiseur de filtrer les lignes en utilisant les deux colonnes de l'index, ce qui entraîne un coût estimé inférieur.

Trace de l'optimiseur

La trace de l'optimiseur fournit informations supplémentaires sur l'évaluation de l'indice processus :

"potential_range_indices": [
    {
        "index": "fe",
        "usable": true
    },
    {
        "index": "ef",
        "usable": true
    }
],
"analyzing_range_alternatives": {
    "range_scan_alternatives": [
        {
            "index": "fe",
            "ranges": [...],
            "index_only": false,
            "rows": 16684,
            "cost": 20022
        },
        {
            "index": "ef",
            "ranges": [...],
            "index_only": false,
            "rows": 538,
            "cost": 646.61
        }
    ]
},
"attached_conditions_computation": [
    {
        "access_type_changed": {
            "table": "`files`",
            "index": "ef",
            "old_type": "ref",
            "new_type": "range",
            "cause": "uses_more_keyparts"
        }
    }
]

Conclusions

La trace de l'Optimizer confirme que :

• "fe" (colonne de plage en premier) utilise uniquement le première colonne pour le filtrage.
• "ef" (colonne à faible cardinalité en premier) exploite les deux colonnes de l'index pour filtrage.
• L'optimiseur donne la priorité à l'utilisation des colonnes impliquées dans les conditions de plage, quelles que soient les cardinalités, lors de la construction d'index composites.
• La cardinalité est moins pertinente pour les index composites dans ce type de scénario de requête.

Par conséquent, en considérant à la fois la sortie EXPLAIN et la trace Optimizer, la configuration d'index optimale est ef (ext, filetime) pour les requêtes impliquant à la fois des conditions de plage ext et filetime. En plaçant la colonne à faible cardinalité en premier dans l'index, nous permettons à l'optimiseur d'utiliser les deux colonnes efficacement, ce qui entraîne un plan d'exécution plus efficace.

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!