実践SQL入門のPostgresSQLとOracleの例をMySQLで確認していく

２章の見出しに母国語を話すようにって書いてあって、母国語が不自由であることに定評がある自分は...。

続くかどうかは知らないが、一章分。

相変わらず、 実践SQL入門 と http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html を眺めています。

一章

単純なSELECT

EXPLAIN select * from shops;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | shops | ALL  | NULL          | NULL | NULL    | NULL |   60 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

typeがALLなので、すべてのレコードに頭からアクセスしている状態です。使われているキーも特にありません。 サブクエリも、UNIONもしていないので、select_typeもSIMPLEです。

インデックススキャンの実行計画

EXPLAIN select * from shops where id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | shops | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

typeがconstに変わりました。主キーのidの等値比較による検索が行われています。

possible_keysm keyも主キーを指しています。refがconstなのは定数(1)をidの比較対象にしているからですね。

ポスグレの場合は、対象オブジェクトがテーブル名からインデックス名に変わったりするそうです。

範囲検索の場合も見てみましょう。

mysql> EXPLAIN select * from shops where id > 30;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | shops | range | PRIMARY       | PRIMARY | 4       | NULL |   30 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

typeがrange(主キーやユニークインデックスを使った範囲検索)に変わりました。 またrowsが30件に絞り込まれています。

簡単な結合をつけたSQL

mysql> EXPLAIN SELECT s.name
    -> FROM shops s INNER JOIN reservations r
    -> ON s.id = r.shop_id;
+----+-------------+-------+--------+---------------------+---------------------+---------+-----------------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys       | key                 | key_len | ref                         | rows | Extra                    |
+----+-------------+-------+--------+---------------------+---------------------+---------+-----------------------------+------+--------------------------+
|  1 | SIMPLE      | r     | index  | fk_rails_0f0d6903af | fk_rails_0f0d6903af | 5       | NULL                        |   10 | Using where; Using index |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY             | PRIMARY             | 4       | dbms_tutorial_dev.r.shop_id |    1 | NULL                     |
+----+-------------+-------+--------+---------------------+---------------------+---------+-----------------------------+------+--------------------------+
2 rows in set (0.00 sec)

おお、これは、上から順に検索されているのかな？

最初にreservationsのテーブル(table列には別名のrが入っている)から10行をとってくる。このとき、SELECTのタイプはindex. インデックスで検索すると速い、とかそういうこととは関係なく、MySQLでinnoDBを使っている場合はindexにデータも載っており、 indexの方がテーブルよりサイズが小さいのでそちらへアクセスしているのでしょう。

2行目のshopsのタイプにはeq_refが入っています。これはJOINをしたとき、JOINで結合対象のテーブル(ここではr)と結合するとき、結合する行をテーブルから探すときにPRIARY KEYまたはUNIQUE KEYが使用されているという意味です。

shops.idはユニークキーですからね。

refには、一行目の dbms_tutorial_dev.r テーブルのshop_idの列が入っています。

MySQLのJOINはnested loop joinだけです。

nested loop join は、最初に一方のテーブルを読み込んで(ここではr)、次にそのテーブルの一行ごとに、結合先のテーブルのレコードから結合できる行を探していくという手続きをとります。

他のexplainでは最初に実行される方がネストの中の方に置かれると行った表現がなされるそうなのですが、MySQLの場合は、入れ子の表現がなされないので同じidの上の方から実行されていく感じです。

ActiveRecordで.explainしてみる

ActiveRecordでもActiveRecordのメソッドチェインの最後に.explainとつけたらEXPLAINの結果を見る事ができます。

Reservation.joins(:shop).select('shops.name').explain
  Reservation Load (0.3ms)  SELECT shops.name FROM `reservations` INNER JOIN `shops` ON `shops`.`id` = `reservations`.`shop_id`
=> EXPLAIN for: SELECT shops.name FROM `reservations` INNER JOIN `shops` ON `shops`.`id` = `reservations`.`shop_id`
+----+-------------+--------------+--------+---------------------+---------------------+---------+----------------------------------------+------+--------------------------+
| id | select_type | table        | type   | possible_keys       | key                 | key_len | ref                                    | rows | Extra                    |
+----+-------------+--------------+--------+---------------------+---------------------+---------+----------------------------------------+------+--------------------------+
|  1 | SIMPLE      | reservations | index  | fk_rails_0f0d6903af | fk_rails_0f0d6903af | 5       | NULL                                   |   10 | Using where; Using index |
|  1 | SIMPLE      | shops        | eq_ref | PRIMARY             | PRIMARY             | 4       | dbms_tutorial_dev.reservations.shop_id |    1 | NULL                     |
+----+-------------+--------------+--------+---------------------+---------------------+---------+----------------------------------------+------+--------------------------+
2 rows in set (0.00 sec)