<<<back

MySQLについて　伍

インデックスについて
　通常、MySQLでは、selectコマンドを実行した場合、1レコード目から最終レコードまで、シーケンシャルに検索を行っていきます。
　しかしながら、レコード数が大量になってくると、検索速度の問題が生じます。
　そこで、より高速な検索を行うために、インデックスを作成するのが一般的です。
　インデックスを作成することによって、検索速度は劇的に改善されます。
　但し、MySQLでは、1,000件以下であればシーケンシャルに検索した方が速いとされています。

　さて、インデックスとはどのようなものであるかというと、直感的には、図書の巻末に付されている索引（インデックス）と同じです。
　索引語はアイウエオ順、あるいは、アルファベット順に並べられていて、各々の索引語には、その索引語が登場するページ数（位置情報）が示されています。
　読者はその位置情報を頼りに、ページを捲って、目的のキーワードのある部分を読むことができるようになるという訳です。
　逆に、索引がない場合、1ページ目から順番に目的のキーワードを見つけるために図書を読んでいくことの煩わしさを想像すれば、データベースにおいても、シーケンシャルに読んでいくことがいかに非効率であるかが理解できるでしょう。

　最も単純なものは、各レコードの先頭の一文字だけを用いたインデックスです。
　例えば、アルファベットだけで構成されているレコードの場合、先頭の文字がa〜zまでの26のグループに分割されます。
　ここで、検索語keyで検索を行った場合、先頭文字がkであるグループを検索しに行くだけで良い訳ですから、シーケンシャルな検索に較べて、検索速度は圧倒的に速くなります。
　仮に、レコード件数が10万件あったとして、そのうち、先頭がkで始まるレコードが4,000件ならば、単純計算で、検索速度は25倍速くなるということになります。
　実際には、先頭1文字ではなく、先頭5文字とか先頭10文字のインデックスを作成しておき、B-tree（B木）と呼ばれる左右均等なツリー構造にデータを格納しておきます。

インデックスの作成と削除
　では、実際にインデックスを作成してみましょう。
　基本構文は、createコマンドを使用して、

create index インデックス名 on テーブル名 (フィールド名);

 とする方法と、tableを作成する際に、同時に

create table テーブル名 (index インデックス名 (フィールド名));

 とする方法があります。
　同様に、alterコマンドを使って、

alter table テーブル名 add index インデックス名 (フィールド名);

 としても構いません。
　create tableとalter tableを使用する場合は、インデックス名を省略することができます。
　その場合、インデックス名はインデックスを作成するフィールド名と同じになります。
　ちなみに、インデックス化できるフィールドは必ず必須フィールド（not null）でなければなりません。
　また、単にフィールド名のみを入力すると、レコードの全てをインデックス化しますが、先頭何文字かまでをインデックス化したいならば、フィールド名(数字)のように指定します。
　例えば、bib_tbテーブルのtitleフィールドのレコードを先頭5文字目までをインデックス化したいならば、

create index title_index on bib_tb (title(5));

 とします（インデックス名はtitle_index）。

mysql> create index title_index on bib_tb (title(5));[Enter Key]
Query OK, 6595 rows affected (0.25 sec)
Records: 6595 Duplicates: 0 Warnings: 0

mysql>

　作成したインデックスを確認するには、showコマンドを使います。
　↓下図を見ると、作成した記憶のないPRIMSRYインデックスが作成されていますが、これは主キーのことです。
　主キーを設定すると自動的にインデックスが作成されます。

mysql> show index from bib_tb;[Enter Key]
+--------+------------+-------------+--------------+-------------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name |
+--------+------------+-------------+--------------+-------------+
| bib_tb |          0 | PRIMARY     |            1 | id          |
| bib_tb |          1 | title_index |            1 | title       |
+--------+------------+-------------+--------------+-------------+

-----------+-------------+----------+--------+---------+
 Collation | Cardinality | Sub_part | Packed | Comment |
-----------+-------------+----------+--------+---------+
 A         |        6595 |     NULL | NULL   |         |
 A         |        3297 |        5 | NULL   |         |
-----------+-------------+----------+--------+---------+
2 rows in set (0.00 sec)

　さて、検索の際に、実際にインデックスが使用されているかどうかを確認するには、explainコマンドを使用します。
　通常のselect文の前にexplainと入力すると、select文がどのように実行されたのかを見ることができます。
　例えば、タイトルが日本から始まるレコードを検索する場合

explain select * from bib_tb where title like ('日本%');

 と入力します。
　使用されたインデックスはkeyフィールドに表示されます（ここでは、title_index）。

mysql> explain select * from bib_tb where title like '日本%';[Enter Key]
+--------+-------+---------------+-------------+
| table  | type  | possible_keys | key         |
+--------+-------+---------------+-------------+
| bib_tb | range | title_index   | title_index |
+--------+-------+---------------+-------------+

---------+------+------+------------+
 key_len | ref  | rows | Extra      |
---------+------+------+------------+
       5 | NULL |  224 | where used |
---------+------+------+------------+
1 row in set (0.00 sec)

　インデックスを削除するにはdropコマンドを使用します。
　構文は、

drop index インデックス名 on テーブル名;

 です。

mysql> drop index title_index on bib_tb;[Enter Key]
Query OK, 6595 rows affected (0.20 sec)
Records: 6595 Duplicates: 0 Warnings: 0

mysql>

　以下にインデックスに関するコマンドをまとめておきます。

操作内容 構文
インデックスの作成 create index [index] on [table] ([field](number));
インデックスの作成
テーブル作成時
 create table [table] (index [index] (field(number)));
インデックスの作成
テーブル変更時
 alter table [table] add index [index] (field(number));
複合インデックスの作成
複数フィールドの指定
 create index [index] on [table] ([field1](number), [field2](number)...);
インデックスの削除 drop index [index] on [table];
レコードの検索
完全一致
 select [field] from [table] where [field]='[value]';
レコードの検索
部分一致
 select [field] from [table] where [field] like '[value]';
select文の実行内容 explain select [field] from [table] where [condition];

全文インデックスについて（日本語未対応）
　ちょっと寄り道します。
　さて、インデックスを使った検索が可能となるのは、基本的に、完全一致のselect文です。
　このほか、あいまい検索（部分一致）のlike演算子を使った場合、前方一致（keyword%）であれば問題ないのですが、中央一致（%keyword%）や後方一致（%keyword）の場合は、インデックスを使用することができません。
　これは、レコードの前方から一文字ずつをインデックス化しているためです。
　しかし、実際の検索では、完全一致や前方一致で検索を行うことは稀であり、むしろ、レコード内のどこかに特定のキーワードを含むような検索を行うことの方が多いでしょう。
　そういった場合のために、MySQLでは、全文インデックスをサポートしています。

日本語を扱うことはできませんのでご注意下さい。

　また、全文インデックスはnot nullであるvarchar型かtext型のフィールドにしか適用できません。
　基本構文は以下の通りです。
　通常のindexを作成する場合とほとんど変わりありません。

create fulltext index インデックス名 on テーブル名 (フィールド名);

create table テーブル名 (fulltext インデックス名 (フィールド名));

alter table テーブル名 add fulltext インデックス名 (フィールド名);

 　やはり、create tableとalter tableでは、インデックス名を省略できます。

　全文インデックスの検索方法は、通常の検索や通常のインデックスを用いた際とは異なります。
　基本構文は以下の通りです。

select フィールド名 from テーブル名 where match (フィールド名) against ('検索語');

 　したがって、title中にjapanを含むレコードを検索する場合、

select * from bib_tb where match (title) against ('japan');

 となります。
　ちなみに、全文検索の場合、基本的に、適合度順出力を行います（詳しくはこちら）。

　MySQLで日本語全文検索を行うためには、まず、形態素解析やN-gramによって、文字列をワードごとに分割して、スペースで区切り、さらに、16進数文字に変換したりして、あたかも日本語ではないかのように装う必要があります。
　ということを、無理矢理やっちゃったエライ方もいらっしゃいます。