哪种索引配置（范围列优先与低基数列优先）最适合高基数列和低基数列的范围查询？-mysql教程-PHP中文网

哪种索引配置（范围列优先与低基数列优先）最适合高基数列和低基数列的范围查询？

DDD

发布： 2024-12-16 05:49:10

原创

170 人浏览过

Which Index Configuration (Range Column First vs. Low Cardinality Column First) Is Optimal for Range Queries on High and Low Cardinality Columns?

在处理涉及高基数列和低基数列的范围查询时哪种索引配置是最佳的？

在给定的场景中，我们有一个表'files' 的主键为 'did' 和 'filename'，以及两个附加索引： 'fe' on “filetime”和“ext”，以及“ext”和“filetime”上的“ef”。我们的查询涉及使用范围条件根据“ext”和“filetime”过滤行。

让我们探索哪种索引配置对此查询更有效。

评估索引选项

为了确定最佳索引，我们可以使用以下方法分析潜在的索引使用情况和成本估算解释：

强制 fe（首先是范围列）：

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;

登录后复制

强制 ef（低基数列） 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;

登录后复制

分析

EXPLAIN表明，与'fe'相比，使用'ef'（低基数列优先）会产生更高效的执行计划。这是因为“ef”使优化器能够使用索引的两列来过滤行，从而降低估计成本。

优化器跟踪

优化器跟踪提供对指数评估的更多见解过程：

"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"
        }
    }
]

登录后复制

结论

优化器跟踪确认：