哪一種索引配置（範圍列優先與低基數列優先）最適合高基數列與低基數列的範圍查詢？

在處理涉及高基數列和低基數列的範圍查詢時哪種索引配置是最佳的？

在給定的場景中，我們有一個表'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中文網其他相關文章！