MySQLのプレフィックスインデックスの概要

wp-admin

9年前

MySQLのプレフィックスインデックスの概要についてまとめました。参考としてFULLTEXTインデックスの概要についても付け加えました。

※目次をクリックすると目次の下部にコンテンツが表示されます。

１．プレフィックスインデックスの概要
２．プレフィックスインデックスと選択性（selectivity）
３．プレフィックスインデックスを定義
４．FULLTEXTインデックスの概要

プレフィックスインデックスの概要

・非常に長い文字数のカラムをインデックスとするとインデックスの容量が大きくなり、速度も遅くなる。
　
プレフィックスインデックスを使って最初の数文字をインデックスとする事によってインデックスファイルのサイズを減少でき、速度も向上する。

プレフィックスインデックスと選択性（selectivity）

１）概要
　
・重複しているキーのデータが少なければ選択性が高く、インデックスは有効に働く。例えば、誕生日を表すカラムは選択性が高いが、性別を表すカラムは選択性が低い。
　
・選択性を高く維持したままプレフィックスインデックスで少ない文字数を指定出来るとパフォーマンスを向上できる。
　
●選択性が高いプレフィックスインデックスの長さを求める例
　
①プレフィックスインデックスを使用しない場合の分布を調べる
　
SELECT COUNT(*) AS cnt, name FROM sample GROUP BY name ORDER BY cnt DESC;
　
②各プレフィックスの長さで分布状況を調べ、最適な長さを求める
　
SELECT COUNT(*) AS cnt, LEFT(name, 3) AS pref FROM sample GROUP BY pref ORDER BY cnt DESC;
　
SELECT COUNT(*) AS cnt, LEFT(name, 4) AS pref FROM sample GROUP BY pref ORDER BY cnt DESC;
　
SELECT COUNT(*) AS cnt, LEFT(name, 5) AS pref FROM sample GROUP BY pref ORDER BY cnt DESC;

プレフィックスインデックスを定義

・インデックス指定でcol_name(N)構文を用いて、文字列カラムの最初のNキャラクタのみを使用したインデックスを作成できる。
　
・BLOBまたはTEXTカラムにインデックスを張る場合、インデックスに対して接頭辞長を指定しなければならない。
　
例）
ALTER TABLE sample ADD KEY (name(4));
　
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

FULLTEXTインデックスの概要

・FULLTEXTインデックスの定義は、テーブルを作成するときに、CREATE TABLEステートメントで提示することができるほか、ALTER TABLE、CREATE INDEXを使用してあとで付け加えることも可能。
　
FULLTEXT [INDEX|KEY] [index_name] (index_col_name,…) [index_option] ..
　
・フルテキスト検索に使用される。
　
・FULLTEXTインデックスをサポートするのはMyISAMストレージエンジンのみで、CHAR、VARCHAR、TEXTカラムについてのみサポートされる。
　
・インデックス設定は常にカラム全体を対象として、カラム接頭辞のインデックス設定は行われない。