読者です 読者をやめる 読者になる 読者になる

masayuki5160's diary

名古屋でWeb系を中心にエンジニアしてます

適切なインデックスが使われていることの確認(EXPLAINを使う)

DB MySQL

前回につづいてインデックスについて。
インデックスがちゃんと使われているか確認していきます。


まずは検証環境を構築。
インデックス付きのテーブルとインデックス無しのテーブルを作成していきます。

INDEX_BOOK_MST:インデックス付き
NON_INDEX_BOOK_MST:インデックス無し

CREATE TABLE INDEX_BOOK_MST(id INT(11) NOT NULL AUTO_INCREMENT,title VARCHAR(64),price INT(32),PRIMARY KEY(id));
CREATE INDEX index_title on INDEX_BOOK_MST(title);
CREATE TABLE NON_INDEX_BOOK_MST(id INT(11) NOT NULL AUTO_INCREMENT,title VARCHAR(64),price INT(32),PRIMARY KEY(id));


SHOW INDEX コマンドでインデックスができてることが確認できます。

mysql> show index from INDEX_BOOK_MST;

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
INDEX_BOOK_MST 0 PRIMARY 1 id A 0 NULL NULL BTREE
INDEX_BOOK_MST 1 index_title 1 title A NULL NULL NULL YES BTREE

2 rows in set (0.00 sec)

mysql> show index from NON_INDEX_BOOK_MST;

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
NON_INDEX_BOOK_MST 0 PRIMARY 1 id A 0 NULL NULL BTREE

1 row in set (0.01 sec)

んでとりあえず適当にデータをいれておきます。

INSERT INTO INDEX_BOOK_MST(title,price) VALUES('jojo','500'),('toriko','1000'),('komatu','200'),('spec','1000'),('zoro','500');
INSERT INTO NON_INDEX_BOOK_MST(title,price) VALUES('jojo','500'),('toriko','1000'),('komatu','200'),('spec','1000'),('zoro','500');

これで準備は完了。
EXPLAINコマンドつかってみます。



mysql> EXPLAIN SELECT * FROM NON_INDEX_BOOK_MST WHERE title='spec';

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NON_INDEX_BOOK_MST ALL NULL NULL NULL NULL 5 Using where

1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM INDEX_BOOK_MST WHERE title='spec';

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE INDEX_BOOK_MST ref index_title index_title 67 const 1 Using where

1 row in set (0.00 sec)

それぞれのテーブルからtitleがspecのフィールドを探しています。
みるポイントはいくつもあるらしいんですが、
とりあえずrowsの欄に注目。

NON_INDEX_BOOK_MSTのほうは"5"、
INDEX_BOOK_MSTのほうは"1"。

これは"spec"をみつけるまでにレコードにアクセスされる回数(の目安)。
というわけでINDEXをつくったほうはうまいことINDEXつかってうごいていることがわかります。

クエリを実行する前にEXPLAINコマンドをつかうことで
効率の悪いクエリをみつけることができます。

EXPLAINコマンドすげ。

参考サイト
SQL入門-インデックスの作成- http://www.syboos.jp/database/doc/20071107165832670.html

MySQLインデックス http://kozy.heteml.jp/pukiwiki/MySQL%2520%25A5%25A4%25A5%25F3%25A5%25C7%25A5%25C3%25A5%25AF%25A5%25B9/index.html