MariaDB　はじめてのMariaDB　【第６回 INDEXを作成してみよう】

MariaDB はじめてのMariaDB　【第６回 DBを作成してみよう】

今回は、インデックス(INDEX/索引)作成について記載します。

インデックス

まず、インデックスとは何か？

インデックスとは、テーブルに格納されているデータを高速に取り出す仕組みのことを言い、
適切に使用することでデータの取り出しが劇的に改善される可能性があります。

インデックスには下記のものがあり、用途により有用性が変わってきます。

ユニークインデックス
特定カラムで特定の値を一意(1度しか出てこない)にすることができる。
一般的に非常に良く使用される。
マルチカラムインデックス(複合インデックス、合成インデックスとも言われる)
複数カラムにインデックスを作成することができる。
2つのインデックスを作成すれば良いではないか？という話もあるが、
MySQLでは1つのクエリを実行する際に1つのテーブルに付き、1つのインデックスしか使用できない。
という決まりがある。
そのため、複数カラムを1つのインデックスに含める必要が出てくる場合がある。
このインデックスも良く使用される。
部分的インデックス
あるカラムの先頭4桁のみインデックスを付加することができる。
私の経験上、あまり使用されていないのではないかと思う。

上記全てに言えることですが、インデックスを作成すると
テーブルの容量とは別に新たにインデックス用の容量が必要になります。
そのため、インデックスを作成する際は容量と良く相談して行う必要が出てくることを念頭に置いておいてください。

インデックスの構造

インデックスは、どういった場合に使用されるのか？
ということを覚えておくとメンテナンスやカスタマイズの際に役に立つと思います。

インデックスが使用される場合

カラム値を定数と比較する(WHERE name = ‘taro’)
カラム値全部で結合(JOIN)する(WHERE a_tbl.name = b_tbl.name)
カラム値の範囲を取り出す(WHERE age > 20)
あいまい検索(LIKE)の先頭が固定値で始まる(WHERE name LIKE ‘ta%’)
※%はワイルドカードと言い、不特定文字列を意味する
WHERE句の全カラムがインデックスの一部である

等・・・

インデックスが使用されない場合

あいまい検索の先頭がワイルドカードで始まる(WHERE name LIKE ‘%ro’)
WHERE句の左辺が算術演算子や関数を使用している

等・・・

インデックスを使用する際の注意点として覚えておいてください。

インデックスの作成

インデックスの作成方法は、大きく分けて2通りあります。

テーブル作成時に一緒に作成
```
CREATE TABLE テーブル名(カラム名 型, カラム名 型, ・・・INDEX(カラム名))
```
※主キー(PRIMARY KEY)を指定した場合、自動的にインデックスが作成されるので、改めて作成する必要はなし。

テーブル作成後に別枠に作成

CREATE INDEX インデックス名 ON テーブル名 (カラム名)

どちらで作成しても良いですが、個人的にはテーブル作成のSQLとは別にしておいた方が
管理しやすいような気がします。
インデックスを再生成する場合にテーブル作成のSQLと一緒だと結局別枠に
インデックス作成用のSQLを作成しなくてはいけなくなるためです。
この辺は各人の好みが出てくるところだと思います。
ちなみにインデックスの削除は「DROP INDEX インデックス名 ON テーブル名」でできます。

インデックスに関するコマンドのまとめ

インデックスの作成、変更、削除に関するコマンドの一覧を以下に記載します。
参考にしてください。

内容	コマンド
作成(テーブル作成時)	CREATE TABLE テーブル名 (カラム名型, カラム名型, ・・・) INDEX インデックス名 (カラム名, カラム名, ・・・);
作成(INDEXのみ作成)	CREATE INDEX インデックス名 ON テーブル名 (カラム名, カラム名, ・・・);
作成(INDEXのみ作成)	ALTER TABLE テーブル名 ADD INDEX インデックス名 (カラム名, カラム名, ・・・);
情報照会	SHOW INDEX FROM テーブル名;
分析(クエリ単位でのINDEXの使用有無)	EXPLAIN クエリ例) EXPLAIN SELECT * FROM sample_tbl WHERE sample_name = ‘taro’;
削除	DROP INDEX インデックス名 ON テーブル名;
削除	ALTER TABLE テーブル名 DROP INDEX インデックス名;