高カーディナリティと低カーディナリティ: 範囲インデックスではどの列を最初に置くべきですか?

範囲インデックスでの高カーディナリティ列の順序付け

データベース設計では、複数の列にインデックスを作成するときに、それらの列の順序を変更できます。範囲クエリのパフォーマンスに大きな影響を与えます。特に、高いカーディナリティの列と低いカーディナリティの列の両方を含むクエリの場合、インデックス内の最適な列の順序付けは直観に反する可能性があります。

シナリオ:

次のスキーマを持つテーブル:

CREATE TABLE `files` (
  `did` int(10) unsigned NOT NULL DEFAULT '0',
  `filename` varbinary(200) NOT NULL,
  `ext` varbinary(5) DEFAULT NULL,
  `fsize` double DEFAULT NULL,
  `filetime` datetime DEFAULT NULL,
  PRIMARY KEY (`did`,`filename`),
  KEY `fe` (`filetime`,`ext`),          -- This?
  KEY `ef` (`ext`,`filetime`)           -- or This?
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

このテーブルには、ファイルのメタデータが格納されます。 100万行。 filetime 列にはほとんど異なる値があり、カーディナリティが高いことを示しますが、ext 列には値の数が限られており、カーディナリティが低いことを示します。

クエリ:

次のクエリ拡張子とファイル時間の両方に基づいてファイル情報を取得するために使用されます:

WHERE ext = '...'
  AND filetime BETWEEN ... AND ...

Best Index選択肢:

このクエリには、fe と ef のどちらのインデックスが適しているかという疑問が生じます。直感的には、カーディナリティの高い列 (filetime) を最初に持つインデックスの方が効率的であると思われるかもしれません。しかし、分析の結果、その逆が真であることがわかりました。

説明:

EXPLAIN コマンドを使用すると、さまざまなインデックスの選択に対するクエリ プランを調べることができます:

-- Force index on fe (filetime first)
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;

-- Force index on ef (ext 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;

結果は、カーディナリティの低い ext 列を最初に持つインデックス ef のパフォーマンスが、インデックス ef よりも大幅に優れていることを示しています。 fe.

オプティマイザー トレースを使用したさらなる分析により、fe インデックスを使用して、オプティマイザーが ext 値でフィルター処理するために多数の行 (16684) をスキャンすると推定したことがわかります。 ef インデックスを使用すると、両方のインデックス列を効率的に使用して、関連する行のみを取得できます (538)。

結論:

範囲をサポートする複合インデックスを作成する場合クエリでは、カーディナリティーに関係なく、等価テストに関係する列 (この場合は ext) を最初に配置することをお勧めします。これにより、クエリ実行プランでインデックスをより効果的に使用できるようになり、パフォーマンスが向上します。

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