高カーディナリティ列と低カーディナリティ列の範囲クエリには、どちらのインデックス構成 (範囲列が最初か、低カーディナリティ列が最初) が最適ですか?

高カーディナリティ列と低カーディナリティ列を含む範囲クエリを使用する場合、どのインデックス構成が最適ですか?

指定されたシナリオでは、テーブルがあります。 「files」には「did」と「filename」に主キーがあり、2 つの追加インデックスがあります。 「filetime」と「ext」には「fe」、「ext」と「filetime」には「ef」。このクエリでは、範囲条件を使用して「ext」と「filetime」の両方に基づいて行をフィルタリングする必要があります。

このクエリではどのインデックス構成がより効率的かを調べてみましょう。

インデックス オプションの評価

最適なインデックスを決定するには、次を使用して潜在的なインデックスの使用量とコストの見積もりを分析できます。 EXPLAIN:

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

Forcing 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 は、「ef」 (カーディナリティの低い列を最初に使用する) を使用すると、「fe」と比較してより効率的な実行計画が得られることを示唆しています。 。これは、「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 出力とオプティマイザー トレースの両方を考慮すると、最適なインデックス構成はext と filetime の両方の範囲条件を含むクエリの場合は、ef (ext, filetime) です。カーディナリティの低い列をインデックスの最初に置くことで、オプティマイザが両方の列を効果的に使用できるようになり、より効率的な実行計画が得られます。

以上が高カーディナリティ列と低カーディナリティ列の範囲クエリには、どちらのインデックス構成 (範囲列が最初か、低カーディナリティ列が最初) が最適ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。