インデックス

構文

ALTER TABLE テーブル名 ADD INDEX インデックス名 (カラムA, カラムB);
ALTER TABLE テーブル名 ADD UNIQUE INDEX インデックス名 (カラムA, カラムB);
※MySQL等テーブルのオプションコンポーネントの場合

CREATE INDEX インデックス名 ON テーブル名(カラムA, カラムB);
CREATE UNIQUE INDEX インデックス名 ON テーブル名(カラムA, カラムB);
※Oracle等独立したスキーマオブジェクトの場合

主キーに対するインデックス

デフォルトで作成されている

外部キーに対するインデックス

参照先のテーブルで主キーではない場合はインデックスを作成する
※外部キーのレコードが参照先のテーブルに存在するか内部的に確認しているため

主キー以外の場合どのカラムにインデックスを作成するか

Cardinality(一意の値の数の推定値)が高いものでWHERE句で使われるもの
※カラムAだけとカラムAとBでselectする2パターンがある場合はカラムAとBの複合インデックスを作成する
※インデックスを作成するとレコードの追加・変更・削除の際にインデックスのテーブルも変更されるため取得以外は遅くなる

実態

インデックスを設定したカラムの値と対応するデータが保存されている場所が格納される
※テーブルのオプションコンポーネント(MySQL)
※独立したスキーマオブジェクト(Oracle)

確認

SHOW INDEX FROM customer;

Non_unique: 重複を含むことができない場合は0, できる場合は1
Collation: カラムのソート方法(A: 昇順, NULL: ソートしない)
Cardinality: 一意の値の数の推定値(高いほどインデックスを使う方が速い)
Index_type: デフォルトはBTREE(balanced-tree index)

EXPLAINでどのインデックスが使われるか(keyカラム)確認する
※possible_keysは使用される可能性のあるインデックスで実際に使われたのはkeyカラムのインデックス

複合インデックス

片方のカラムだけを指定するクエリでは使えない
※もう片方のカラムのCardinality(一意の値の数の推定値)が低ければ複合インデックスにする効果小さい

BTREEインデックス

デフォルトのインデックスタイプ

Cardinalityが高い場合に適している
※Cardinalityが低い場合には適していない

FULLTEXTインデックス

・テーブル作成時に全文検索対象のカラムを指定
FULLTEXT (カラムA,カラムB)

・SELECT
SELECT
  *
FROM
  articles
WHERE
  MATCH (カラムA,カラムB) AGAINST ('検索文言' IN NATURAL LANGUAGE MODE);

・全文検索モード
IN NATURAL LANGUAGE MODE: AGAINST ('検索文言' IN NATURAL LANGUAGE MODE)
※デフォルト

IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
※2回検索される(1回目はデフォルトと同じ検索、2回目はQUERY EXPANSION)

IN BOOLEAN MODE: AGAINST ('+検索文言1 -除外文言 検索文言2' IN BOOLEAN MODE)
※検索文言2はOR検索
※AND検索の場合は'+検索文言1 +検索文言2'

WITH QUERY EXPANSION: 短い検索文言を元に意味を拡張して関連する文言もヒットさせる

※データを入れた後にインデックスを作成するのが速い
※パーティションテーブルではサポートされていない
※組み込みのフルテキストパーサーは日本語は単語がどこから始まりどこで終わるかを判別できない(単語間の空白がない)
※日本語にはフルテキストngramパーサーかMeCabフルテキストパーサープラグインが必要
※MySQLの全文検索機能には、ユーザーが調整できるパラメーターがほとんどない

※通常は全文検索エンジンを使う

関連

• 性能を測る — 張る前に実行計画と実測で確かめる
• WHERE 句 — インデックスが効く条件の書き方
• テーブル設計 — キーの設計と表裏