MySQL インデックスのカーディナリティの概念と使用例

この記事では、例を使用して、MySQL インデックスカーディナリティの概念と使用方法を説明します。ご参考までに、詳細は以下の通りです。

カーディナリティ（インデックスカーディナリティ）はMySQLインデックスにおいて非常に重要な概念です。

インデックスのカーディナリティは、列に含まれる個別の値の数です。たとえば、データ列に値 1、2、3、4、5、1 が含まれている場合、そのカーディナリティは 5 になります。インデックスは、テーブル内の行数に比べてそのカーディナリティが高い場合（つまり、列に多くの異なる値が含まれ、重複する値が少ない場合）に最も効果的に機能します。列にさまざまな年齢が含まれている場合、インデックスによって行をすばやく区別できます。列が性別を記録するために使用される場合（「M」と「F」の 2 つの値のみ）、インデックスはあまり役に立ちません。値がほぼ等しい確率で出現する場合、どちらかの値を検索すると行の半分が返されます。このような場合、クエリオプティマイザーは、特定の値がテーブル内のデータ行の高割合に出現することを検出すると、通常はインデックスを無視して完全なテーブルスキャンを実行するため、インデックスをまったく使用しないことをお勧めします。一般的に使用されるパーセンテージカットオフは「30%」です。

もう一つの概念はインデックス選択性と呼ばれる。

インデックスの選択性 = インデックスのカーディナリティ / 合計データ。カーディナリティは、「テーブル名からインデックスを表示」で確認できます。
1 に近いほど、インデックスが使用される可能性が高くなります。1 は 100% 高いとも言えます。インデックスの選択性の利点は、MySQL が一致を検索するときにより多くの行をフィルターできることです。一意のインデックスの選択性は、値が 1 のときに最も高くなります。

これは私が作成した表です。データ量は多くありませんが、インデックスの選択性をテストするためだけのものです。

記事から*を選択
id タイトル 名前15 タイトル 0 ビッグベア16 タイトル 1 ビッグベア17 タイトル 2 ビッグベア18 タイトル 3 ビッグベア19 タイトル 4 ビッグベア20 タイトル 5 ビッグベア21 タイトル 6 ビッグベア22 タイトル 7 ビッグベア23 タイトル 8 2匹のクマ24 タイトル 9 2匹のクマ

idはデフォルトの整数自動増分主キーです

次に、著者列をインデックスとして追加し、記事テーブルのすべてのインデックスを表示します。

ALTER TABLE `articles` ADD INDEX (`author`)
記事からインデックスを表示
テーブル 非一意 キー名 seq_in_index 列名 照合 カーディナリティ サブパート パック NULL インデックスタイプ
記事 0 PRIMARY 1 id A 10 NULL NULL BTREE
記事 1 著者 1 著者 A 2 NULL NULL BTREE

各フィールドの意味を説明する

テーブルテーブル名
インデックスに重複する単語を含めることができない場合、 non_uniqueは 0 になります。できれば1。
key_nameインデックス名
seq_in_indexインデックス内の列のシーケンス番号。1から始まります。
column_name列名
照合列がインデックスに格納される方法。 MySQLのSHOW INDEX構文では、'A'（昇順）またはNULL（ソートなし）の値が存在します。
カーディナリティインデックスカーディナリティ
sub_part列が部分的にのみインデックス付けされている場合、インデックス付けされた文字数。列全体がインデックス化されている場合は NULL です。
packed はキーワードがどのようにパックされているかを示します。圧縮されていない場合は NULL になります。
null列に NULL が含まれている場合は YES が含まれます。そうでない場合、列には NO が含まれます。
index_typeで使用されるインデックスの保存方法 (BTREE、FULLTEXT、HASH、RTREE)

ご覧のとおり、articles テーブルにはすでに 2 つのインデックスがあります。

id インデックスには重複する単語は含まれていません。主キー列名は id です。インデックスのカーディナリティは昇順で 10 です。部分インデックスはありません。圧縮はありません。null はありません。保存方法は btree です。

著者インデックスには繰り返し単語が含まれています。インデックス名は author です。列名は author です。インデックスは昇順でソートされています。インデックスのカーディナリティは 2 です。部分インデックスはありません。圧縮はありません。NULL はありません。保存方法は btree です。

インデックス選択アルゴリズムによれば、id インデックス選択は 10/10 = 1、author インデックス選択は 2/10 = 0.2 です。これをテストし、explain を使用してステートメント分析を確認してみましょう。

説明: ID = 15 の記事から * を選択します
id select_type テーブル パーティション タイプ possible_keys キー key_len ref 行 フィルタリング 追加
1 SIMPLE記事 NULL const PRIMARY PRIMARY 4 const 1 100.00 NULL

もう一度、explain コマンドについてお話ししましょう。

EXPLAIN は、MySQL がインデックスを使用して選択ステートメントを処理し、テーブルを結合する方法を示します。より適切なインデックスを選択し、より最適化されたクエリステートメントを記述するのに役立ちます。各フィールドの意味を説明する

1. id SELECT識別子。これは SELECT クエリのシーケンス番号です。これは重要ではありません。クエリシーケンス番号は、SQL ステートメントが実行される順序です。

2. select_type選択タイプ

2.1. SIMPLE Union 操作を必要としない、またはサブクエリを含まない単純な選択クエリを実行する場合、応答クエリステートメントの select_type は simple になります。クエリステートメントがどれだけ複雑であっても、実行プランには select_type が simple の単位クエリが 1 つだけ存在する必要があります。
2.2. PRIMARY Union 操作を必要とするか、サブクエリを含む選択クエリ実行プランでは、最も外側の select_type は primary です。 simple と同様に、select_type をプライマリとするユニット選択クエリは 1 つだけです。
2.3. union union 操作によって形成された単位選択クエリでは、最初のクエリを除き、2 番目以降のすべての単位選択クエリの select_type は union です。 union の最初のユニット選択の select_type は union ではなく、DERIVED です。これは、結合後のクエリ結果を格納するために使用される一時テーブルです。
2.4. DEPENDENT UNION dependent UNION select_typeと同様に、 dependent union は union または union all によって形成されるセットクエリ内に表示されます。ここでの「依存」という言葉は、union または union all によって形成された単位クエリが外部要因の影響を受けることを意味します。
2.5.結合結果結合結果は結合結果を含むデータテーブルである。

3.テーブルテーブル名

4.タイプ接続タイプ、複数のパラメータがあり、まず最高のタイプから最悪のタイプまでがこの記事の焦点でもあります

4.1 const 、テーブルには一致する行が最大 1 つあり、const は主キーまたは一意のインデックスを比較するために使用されます。一致するのは 1 行のデータのみなので非常に高速であり、これは最も最適化されたインデックスと定数検索とも言えます。
4.2 eq_ref eq_ref の説明に関して、MySQL マニュアルには次のように書かれています。「前のテーブルの各行の組み合わせに対して、このテーブルから行を読み取ります。const 型を除いて、これが最適な結合型である可能性があります。」
4.3 ref前のテーブルの行の組み合わせごとに、一致するインデックス値を持つすべての行がこのテーブルから読み取られます。結合でキーの左端のプレフィックスのみが使用される場合、またはキーが UNIQUE または PRIMARY KEY ではない場合 (つまり、結合でキーに基づいて単一の行を選択できない場合) は、ref が使用されます。この結合タイプは、使用されるキーが少数の行にのみ一致する場合に適しています。
4.4 ref_or_nullこの結合タイプは ref に似ていますが、MySQL が NULL 値を含む行を具体的に検索できる点が追加されています。この結合タイプの最適化は、サブクエリの解決によく使用されます。
4.5 index_mergeこの結合タイプは、インデックスマージ最適化方法が使用されることを示します。この場合、キー列には使用されるインデックスのリストが含まれ、key_len には使用されるインデックスの最長のキー要素が含まれます。
4.6ユニークサブクエリ
4.7インデックスサブクエリ
4.8範囲指定された範囲内で、インデックスを使用して行をチェックして検索する
4.9インデックスこの結合タイプは、インデックスツリーのみがスキャンされる点を除いて、ALL と同じです。通常、インデックスファイルはデータファイルよりも小さいため、これは ALL よりも高速です。 (つまり、all と Index はどちらもテーブル全体を読み取りますが、index はインデックスから読み取り、all はハードディスクから読み取ります)
4.10 ALL前のテーブルの行の組み合わせごとに、完全なテーブルスキャンが実行されます。テーブルが const としてマークされていない最初のテーブルである場合、これは通常悪い結果となり、それ以外の場合にも通常非常に悪い結果となります。多くの場合、ALL を使用する代わりにインデックスを追加して、前のテーブルの定数値または列値に基づいて行を取得できます。

5. possible_keysは、テーブル内の行を見つけるためにどのインデックスが使用されるかを示しますが、これはそれほど重要ではありません。

6.キーはMYSQLクエリで使用されるインデックスを示します

7. key_len MYSQLインデックスの長さ

8. refは、テーブルから行を選択するためにキーと一緒に使用される列または定数を示します。

9. rows は、MYSQL がクエリを実行する行数を示します。値が大きいほど悪い状態であり、インデックスが適切に使用されていないことを示します。

10.追加この列には、MySQL がクエリを解決する方法に関する詳細な情報が含まれています。

id クエリが id インデックスを使用していることがわかります。クエリタイプは最適な定数クエリです。次に、今度は author インデックスを使用して別のクエリを試してみましょう。

著者が「Big Bear」である記事から*を選択して説明してください
1 シンプル記事 NULL すべて 著者 NULL NULL NULL 10 80.00 whereの使用

単純なクエリに著者インデックスが使用されていることがはっきりとわかります。クエリの種類は最悪のフルテーブルスキャンです。説明を急ぐのはやめましょう。代わりに同じステートメントを使用しましょう。

explain select * from articles where author = "二熊"
1 シンプル記事 NULL ref author author 1022 const 2 100.00 NULL

今回のクエリタイプはrefであることがわかります。

つまり、著者 Daxiong が書き込んだデータ行の数が総データの 30% を超えるため、MySQL では、インデックスを使用するよりもテーブル全体をスキャンする方が高速であると判断されます。これが、インデックスカーディナリティとインデックス選択性の概念の意味です。したがって、インデックスを作成するときは、インデックスカーディナリティの高い列にインデックスを作成することに注意する必要があります。

MySQL 関連のコンテンツに興味のある読者は、このサイトの次のトピックをチェックしてください: 「MySQL インデックス操作スキルの概要」、「MySQL 共通関数の概要」、「MySQL ログ操作スキルの概要」、「MySQL トランザクション操作スキルの概要」、「MySQL ストアドプロシージャスキルの概要」、および「MySQL データベースロック関連スキルの概要」。

この記事が皆様のMySQLデータベース設計に役立つことを願っています。

以下もご興味があるかもしれません: