[MySQL]大量データを格納したテーブルにはインデックスを。

郵便番号テーブル（mtb_zip）のデータ件数は約12万8000件。
これをまともにselectしたりすると、アブナイ。

そんなときはindexをはろう。
indexは本でいうところの索引。
これがあるかないかで検索スピードの差は歴然。

そもそもインデックスって？

通常、MySQLでは、selectコマンドを実行した場合、
1レコード目から最終レコードまで、シーケンシャルに検索を行っていく。
データが大量になってくると検索速度の問題が生じる。
そこで出てくるのがインデックス。
インデックスを作成すると検索速度は劇的に改善される。
ただし、MySQLでは1000件以下のデータの場合はインデックスを作成しないほうが
速いとされている。

インデックスとは先ほども記載したとおり、
本の索引のようなもの。

索引語はアイウエオ順、あるいは、アルファベット順に並べられていて、
各々の索引語には、その索引語が登場するページ数（位置情報）が示されています。
読者はその位置情報を頼りに、ページを捲って、
目的のキーワードのある部分を読むことができるようになるという訳です。

逆に、索引がない場合、
1ページ目から順番に目的のキーワードを見つけるために本を読んでいくことの煩わしさを考えれば、
データベースにおいても、シーケンシャルに読んでいくことがいかに非効率であるかがわかりますね。

最も単純なものは、各レコードの先頭の一文字だけを用いたインデックス。
例えば、アルファベットだけで構成されているレコードの場合、
先頭の文字がa～zまでの26のグループに分割されます。

ここで、検索語keyで検索を行った場合、
先頭文字がkであるグループを検索しに行くだけでいいので、
シーケンシャルな検索に較べて、検索速度は圧倒的に速くなります。
仮に、レコード件数が10万件あったとして、そのうち、
先頭がkで始まるレコードが4,000件ならば、
単純計算で検索速度は25倍速くなるということになります。

実際には、先頭1文字ではなく、先頭5文字とか先頭10文字のインデックスを作成しておき、
B-tree（B木）と呼ばれる左右均等なツリー構造にデータを格納しておきます。

インデックスの作成と削除

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）。

今回はmtb_zipテーブルに対し、
zipcodeを必須に変更し、
そのあとzipcodeの3桁目までをインデックス化してインデックスを作成。

create index zip_index on mtb_zip (zipcode(3));

作成したインデックスを確認するには、showコマンドを使います。

全文インデックスについて（日本語未対応）

　ちょっと寄り道します。
　さて、インデックスを使った検索が可能となるのは、基本的に、完全一致の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進数文字に変換したりして、あたかも日本語ではないかのように装う必要があります。
　ということを、無理矢理やっちゃったエライ方もいらっしゃいます。