【MySQL】オンメモリで動かしてみる2

前回（【MySQL】オンメモリで動かしてみる - footmark）の結果に差が出なかったことに関して、SQL文に問題があったのではと思い、INNER JOIN を使ったSQL文を用意していろいろ弄ってみました。
話が前後しますが、前回のプログラムではwhere句を用いたselect文しか使っていないので、少し処理を重くしてみようということです。

とりあえず実行。

# MyISAM
mysql> select * from doc_word_table inner join word_table on doc_word_table.word_id = word_table.word_id limit 100000;
100000 rows in set (0.38 sec)

# MEMORY
mysql> select * from doc_word_table_memory inner join word_table_memory on doc_word_table_memory.word_id = word_table_memory.word_id limit 100000;
100000 rows in set (5.70 sec)

MEMORYストレージエンジン側がめっちゃ遅い！なんで！？
explain句を使って内部でどんな処理をしているのか見てみます。

# MyISAM
mysql> explain select * from doc_word_table inner join word_table on doc_word_table.word_id = word_table.word_id;
+----+-------------+----------------+--------+---------------+---------+---------+-------------------------------+----------+-------+
| id | select_type | table          | type   | possible_keys | key     | key_len | ref                           | rows     | Extra |
+----+-------------+----------------+--------+---------------+---------+---------+-------------------------------+----------+-------+
|  1 | SIMPLE      | doc_word_table | ALL    | NULL          | NULL    | NULL    | NULL                          | 16358934 |       |
|  1 | SIMPLE      | word_table     | eq_ref | PRIMARY       | PRIMARY | 4       | db.doc_word_table.word_id |        1 |       |
+----+-------------+----------------+--------+---------------+---------+---------+-------------------------------+----------+-------+
2 rows in set (0.00 sec)

# MEMORY
mysql> explain select * from doc_word_table_memory inner join word_table_memory on doc_word_table_memory.word_id = word_table_memory.word_id;
+----+-------------+-----------------------+------+---------------+------+---------+------+----------+--------------------------------+
| id | select_type | table                 | type | possible_keys | key  | key_len | ref  | rows     | Extra                          |
+----+-------------+-----------------------+------+---------------+------+---------+------+----------+--------------------------------+
|  1 | SIMPLE      | word_table_memory     | ALL  | NULL          | NULL | NULL    | NULL |    68058 |                                |
|  1 | SIMPLE      | doc_word_table_memory | ALL  | NULL          | NULL | NULL    | NULL | 16358934 | Using where; Using join buffer |
+----+-------------+-----------------------+------+---------------+------+---------+------+----------+--------------------------------+
2 rows in set (0.00 sec)

MEMORYストレージエンジン側は word_table に key を使っていないので毎回全検索をしていますね。
どうやらここがネックっぽいです。
というかそもそもなんで key 使ってないんだ？設定されていない？
確認してみます。

# MyISAM
mysql> show columns from doc_word_table;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| doc_word_id | int(11) | NO   | PRI | NULL    | auto_increment |
| doc_id   | int(11) | NO   | MUL | NULL    |                |
| word_id     | int(11) | NO   |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

# MEMORY
mysql> show columns from doc_word_table_memory;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| doc_word_id | int(11) | NO   |     | 0       |       |
| doc_id   | int(11) | NO   | MUL | NULL    |       |
| word_id     | int(11) | NO   |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

あれ、主キーが設定されてない。
なるほど、前回MyISAMストレージエンジンのテーブルからコピーしてくるときに設定し忘れていたようです。
というわけで、主キーを貼る。

mysql> alter table doc_word_table_memory add primary key (doc_word_id);
Query OK, 16358934 rows affected (21.18 sec)
Records: 16358934  Duplicates: 0  Warnings: 0

再びexplain句で調べてみます。

mysql> explain select * from doc_word_table_memory inner join word_table_memory on doc_word_table_memory.word_id = word_table_memory.word_id;
+----+-------------+-----------------------+--------+---------------+---------+---------+--------------------------------------+----------+-------+
| id | select_type | table                 | type   | possible_keys | key     | key_len | ref                                  | rows     | Extra |
+----+-------------+-----------------------+--------+---------------+---------+---------+--------------------------------------+----------+-------+
|  1 | SIMPLE      | doc_word_table_memory | ALL    | NULL          | NULL    | NULL    | NULL                                 | 16358934 |       |
|  1 | SIMPLE      | word_table_memory     | eq_ref | PRIMARY       | PRIMARY | 4       | db.doc_word_table_memory.word_id |        1 |       |
+----+-------------+-----------------------+--------+---------------+---------+---------+--------------------------------------+----------+-------+
2 rows in set (0.00 sec)

MyISAMストレージエンジンのそれと同じになりました。
改めてSQL文を打ってみます。

mysql> select * from doc_word_table_memory inner join word_table_memory on doc_word_table_memory.word_id = word_table_memory.word_id limit 100000;
100000 rows in set (0.07 sec)

はええ。
やっぱりMEMORYストレージエンジンさんは速かったんや。

（前回の結果が同じだったのも主キーを貼り忘れたせいじゃね？と思い、前回のプログラムを再実行してみたのですが、結果は変わらずでした。）

考察

前回のプログラム中のSQL文はもともと処理が軽く、メモリ云々では速くしようがない、というのが僕の考察です。

ちなみに、

MyISAM : インデックスはMySQLがキャッシュ管理する(key_buffer_sizeで大きさを設定)。データはOSのキャッシュ機構におまかせ

MyISAMを使っている時key_buffer_sizeは大きくし過ぎてもいけない | b.l0g.jp

なる仕様だそうで、プラス、動かした環境はメモリを128GB積んでいるサーバだったこともあり、差が出なかったのにはOSの仕事っぷりも一枚噛んでいるかもしれません。