8-5. MySQL入門⑤インデックス
前回までは、classsicsテーブルを変更したり、MySQLの検索をしたりしてきました。行がどんどん増えていくと、データベースのアクセスは新しい行を追加するごとに遅くなっていきます。
それは、クエリ(データベースへの問い合わせ、照会)が発行されるたびに、MySQLすべての行を検索しなければならないからです。
その際に、「インデックス」を追加することで、テーブルの"カード目録"を作成し、高速な検索を実現することができます。
1-1.インデックスの作成
インデックスを追加する際に悩ましいのは、どのようなインデックスにするかの判断が難しいという点です。インデックスはどの列に必要なのかを決める必要があり、そのためにはその列のデータを全部検索するのかどうかを見極めることが求められます。
前回作ったclassicsテーブルを使ってインデックスを作成していきます。
本をページ数で検索する人はまずいないと思われるので、これにインデックスを付ける必要はないということで、以下のコマンドを入力します。
ALTER TABLE classics ADD INDEX(author(20));
ALTER TABLE classics ADD INDEX(title(20));
ALTER TABLE classics ADD INDEX(category(4));
ALTER TABLE classics ADD INDEX(year);
DESCRIBE classics;
INDEX(author(20))の20は、初めの20文字までに限ったインデックスを作成するという意味です。
これはインデックスのサイズを最小化するために行う作業で、データべ^スのアクセススピードが最適化されます。
1-2.テーブル作成時にインデックスを追加する
インデックスの追加は、テーブルを作成する際に同時に行うことができます。
以下コマンドを入力し、インデックスをつけたclassicsテーブルを作成してみます。
CREATE TABLE classics1 (
author VARCHAR(128),
title VARCHAR(128),
category VARCHAR(16),
year SMALLINT,
INDEX(author(20)),
INDEX(title(20)),
INDEX(category(4)),
INDEX(year)) ENGINE MyISAM;
うしろにindexを追加すればいいのか。。。
1-3.主キー(ISBN番号)
MySQLの検索を高速化させるために、各行に一意の値のキー(主キー、プライマリキー)を持たせる方法があります。複数の異なるテーブルのデータを組み合わせて扱うときにより重要度が増します。
ISBN番号は世界中で認識可能な13桁の文字らしいです。
isbn列にデータを入れ、主キーにしていきます。
ALTER TABLE classics ADD isbn CHAR(13);
UPDATE classics SET isbn='9781598184891' WHERE year='1876';
UPDATE classics SET isbn='9780582506206' WHERE year='1811';
UPDATE classics SET isbn='9780517123201' WHERE year='1856';
UPDATE classics SET isbn='9780099533474' WHERE year='1841';
UPDATE classics SET isbn='9780192814968' WHERE year='1594';
ALTER TABLE classics ADD PRIMARY KEY(isbn);
DESCRIBE classics;
1-4.FULLTEXTインデックスの作成
MySQLのFULLTEXT院で㏍数を使用すると、標準的なインデックスと異なり非常に高速な、列の全テキストの検索(全文検索)が行えます。
特殊なインデックスに、全データストリングのすべてのワード(語)を保持するということです。
classicsテーブルにFULLTEXTを追加する際には、以下のコマンドを入力します。
ALTER TABLE classics ADD FULLTEXT(author,title);
・FULLTEXTインデックスが使用できるのは、MyISAMテーブルのみ
※MyISAMはMySQLのデフォルトのストレージエンジン
・FULLTEXTインデックスが作成できるのは、CHARとVACHAR、TEXT列のみ
・FULLTEXTインデックスの定義は、テーブルの作成時にはCREATE、TABLEステートメントで、あとから追加するときにはALTER TABLE(またはCREATE INDEX)を使って行うことができる
・データセットが大きい場合は、データをまずFULLEXTインデックスを持たないテーブルにロードし、それからインデックスを作成するほうがFULLTEXTインデックスを持っているテーブルにデータをロードするよりも処理ははるかに高速。
今日はいじょーーーー