MySQLのbツリーインデックスの概要とマルチカラムインデックスに関する資料を読んでまとめました。
※目次をクリックすると目次の下部にコンテンツが表示されます。
MySQLのbツリーインデックスの概要
・最も一般的に使用されているインデックス。
・多くのストレージエンジンでサポートされているがその内部でのインデックスの構成や利用方法はそれぞれで異なっている。
MyISAMでは、インデックスのサイズを小さくするためにプレフィックス圧縮を使用するが、InnnoDBは圧縮しない、といった違いがある。
・bツリーでは、すべての値が順番に保存されていて、各leafページはrootから同じ深さになっている。
・bツリーは、インデックスされたカラムを順番に保存しているので範囲検索に有用。
・検索と同様にORDER BYによるソートにも使用される。
・多くのストレージエンジンでサポートされているがその内部でのインデックスの構成や利用方法はそれぞれで異なっている。
MyISAMでは、インデックスのサイズを小さくするためにプレフィックス圧縮を使用するが、InnnoDBは圧縮しない、といった違いがある。
・bツリーでは、すべての値が順番に保存されていて、各leafページはrootから同じ深さになっている。
・bツリーは、インデックスされたカラムを順番に保存しているので範囲検索に有用。
・検索と同様にORDER BYによるソートにも使用される。
マルチカラムのbツリーインデックス
１）概要
key(a,b,c）の場合を例とします。
・インデックス内で順序が先の列のみ利用可能。
key(a,b）の場合、where b = 5のクエリーはインデックスを使用しない。
・a,b,cのカラムの値が完全一致するかどうかのクエリーには有用。
・最初に指定したカラム（a）の一致の検索には有用。
・カラムの前方一致検索は、最初のカラムのみ利用可能。
・範囲検索は、最初のカラムのみ利用可能。
・最初のカラムが完全一致、次のカラムが範囲検索の場合は利用可能。
２）カラムを指定する順番、選択性の見積もり
原則としては選択性の高いカラムを最初に指定する。
①カラム全体のデータから選択性を見積もる
例）
mysql> SELECT COUNT(DISTINCT key1)/COUNT(*) AS key1_sel,
> COUNT(DISTINCT key2)/COUNT(*) AS key2_sel,
> COUNT(*) FROM sample\G
key2の方が選択性が高い場合は、下記のようにインデックスを定義する。
mysql> ALTER TABLE sample ADD KEY(key2, key1);
②実際に使用する検索条件の値を使って見積もる
カラム全体のデータでは選択性が高くてもある特定の値については重複データが多い、といった場合もあり、考慮が必要。
key(a,b,c）の場合を例とします。
・インデックス内で順序が先の列のみ利用可能。
key(a,b）の場合、where b = 5のクエリーはインデックスを使用しない。
・a,b,cのカラムの値が完全一致するかどうかのクエリーには有用。
・最初に指定したカラム（a）の一致の検索には有用。
・カラムの前方一致検索は、最初のカラムのみ利用可能。
・範囲検索は、最初のカラムのみ利用可能。
・最初のカラムが完全一致、次のカラムが範囲検索の場合は利用可能。
２）カラムを指定する順番、選択性の見積もり
原則としては選択性の高いカラムを最初に指定する。
①カラム全体のデータから選択性を見積もる
例）
mysql> SELECT COUNT(DISTINCT key1)/COUNT(*) AS key1_sel,
> COUNT(DISTINCT key2)/COUNT(*) AS key2_sel,
> COUNT(*) FROM sample\G
key2の方が選択性が高い場合は、下記のようにインデックスを定義する。
mysql> ALTER TABLE sample ADD KEY(key2, key1);
②実際に使用する検索条件の値を使って見積もる
カラム全体のデータでは選択性が高くてもある特定の値については重複データが多い、といった場合もあり、考慮が必要。
bツリーインデックスの制限
・最初のカラムを指定せず（又は最初のカラムの後方一致）、２番目以降のカラムを検索する際にはインデックスが使用されない。
・key(a,b,c）などの場合、aとcのカラムのみを指定してbのカラムをスキップするようなクエリーの場合は、aのカラムのみが使用される。
・key(a,b,c）などの場合、bのカラムで範囲検索を行った場合、cのカラムは使用できず、aとbのカラムのみ利用される。
・key(a,b,c）などの場合、aとcのカラムのみを指定してbのカラムをスキップするようなクエリーの場合は、aのカラムのみが使用される。
・key(a,b,c）などの場合、bのカラムで範囲検索を行った場合、cのカラムは使用できず、aとbのカラムのみ利用される。