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

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

在给定的场景中，我们有一个表'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"
        }
    }
]

结论

优化器跟踪确认：

• “fe”（范围列在前）仅使用用于过滤的第一列。
• “ef”（低基数列在前）利用了用于过滤的索引。
• 构建复合索引时，优化器会优先使用范围条件涉及的列，而不考虑基数。
• 在这种类型的查询场景中，基数与复合索引的相关性较小.

因此，考虑 EXPLAIN 输出和优化器跟踪，最佳索引配置为ef (ext, filetime) 用于同时涉及 ext 和 filetime 范围条件的查询。通过将低基数列放在索引中的第一位，我们使优化器能够有效地使用这两列，从而产生更高效的执行计划。

以上是哪种索引配置（范围列优先与低基数列优先）最适合高基数列和低基数列的范围查询？的详细内容。更多信息请关注PHP中文网其他相关文章！