JOINが遅すぎて泣ける ― MySQLは漢(オトコ)のコンピュータ道?: 発火後忘失

はじめての永続化、全2話中第2話、最終回です。第1話はこちら。サブタイトルに意味は無いです。

下のような関連を持つありがち、とはいかないまでも珍しくは無いクラス群をクエリしようと思いました。(クリックで拡大)

Earthにいくつかのマスタデータがくっついているようなイメージです。ちなみにクラス名は適当につけているので意味はありません。

今回記載しているのはMySQL5.5です。MySQL5.1でも試しましたが、パフォーマンスは同程度でした(ただし後で述べますがEXPLAINの結果は若干変わっていました)。その他テストに使用したものは以下の通りです。

使用したソースコード(NetBeans7.0プロジェクト)
実行されるSELECT文(のうちメインとなるもの)
使用したテストデータ(mysqldump)
- テーブルのレコード数は、いずれも10件程度、カラムは主キーと外部キーのみです。
EXPLAINの結果
PROFILINGの結果

クエリはこんな感じです。これを実行すると、上で添付したSQLと、select-joinのクエリがいくつか走ります。

        1: Criteria c = session.createCriteria(Earth.class);

       2: List<Earth> res = c.list();

結果は6件得られます。

問題は実行にかかる時間なのですが、この程度のデータ数で1分程度かかってしまいました。上で添付しているメインのSQLをEXPLAINした結果の抜粋は下記の通りです(結果の完全なものは上に添付しています)。IDと他の関連先テーブルのIDから成る外部キーしか持っていないので当然ですが、”key”は全てPRIMARYになっています。

   1: +----+-------------+-----------------+--------+---------+--------------------------------+

   2: | id | select_type | table           | type   | key     | Extra                          |

   3: +----+-------------+-----------------+--------+---------+--------------------------------+

   4: |  1 | SIMPLE      | beta2_          | index  | PRIMARY | Using index                    |

   5: |  1 | SIMPLE      | beta4_          | index  | PRIMARY | Using index; Using join buffer |

   6: |  1 | SIMPLE      | beta2_1_        | eq_ref | PRIMARY |                                |

   7: |  1 | SIMPLE      | beta4_1_        | eq_ref | PRIMARY |                                |

   8: |  1 | SIMPLE      | this_           | ALL    | NULL    | Using where; Using join buffer |

   9: |  1 | SIMPLE      | shot15_         | eq_ref | PRIMARY |                                |

  10: |  1 | SIMPLE      | block5_         | eq_ref | PRIMARY | Using index                    |

  11: |  1 | SIMPLE      | block5_1_       | eq_ref | PRIMARY |                                |

  12: |  1 | SIMPLE      | departure6_     | eq_ref | PRIMARY | Using index                    |

  13: |  1 | SIMPLE      | direct7_        | eq_ref | PRIMARY |                                |

  14: |  1 | SIMPLE      | problem8_       | eq_ref | PRIMARY |                                |

  15: |  1 | SIMPLE      | problem9_       | eq_ref | PRIMARY |                                |

  16: |  1 | SIMPLE      | arm10_          | eq_ref | PRIMARY |                                |

  17: |  1 | SIMPLE      | computer11_     | eq_ref | PRIMARY |                                |

  18: |  1 | SIMPLE      | curry12_        | eq_ref | PRIMARY |                                |

  19: |  1 | SIMPLE      | exhibition13_   | eq_ref | PRIMARY | Using index                    |

  20: |  1 | SIMPLE      | exhibition13_1_ | eq_ref | PRIMARY |                                |

  21: |  1 | SIMPLE      | radio14_        | eq_ref | PRIMARY |                                |

  22: |  1 | SIMPLE      | piano3_         | eq_ref | PRIMARY |                                |

  23: |  1 | SIMPLE      | piano3_1_       | eq_ref | PRIMARY | Using index                    |

  24: |  1 | SIMPLE      | piano3_2_       | eq_ref | PRIMARY | Using index                    |

  25: |  1 | SIMPLE      | departure6_1_   | eq_ref | PRIMARY |                                |

  26: +----+-------------+-----------------+--------+---------+--------------------------------+

  27: 22 rows in set (56.69 sec)

にも関わらず、遅い。たったこれだけのデータ量に対してクエリするだけで私の環境で1分程度かかります。

PROFILINGの結果を見ると、statisticsという部分で時間のほとんどが費やされています。ソースコードをチラ見してみると下のようなコメントがあり、やはりJOINに関わるところなんだろうな、と推測できます。

       1: /* Calculate how to do the join */

       2: thd_proc_info(thd, "statistics");

       3: if (make_join_statistics(this, select_lex->leaf_tables, conds, &keyuse) ||

       4:     thd->is_fatal_error)

       5: {

コンフィグをデフォルトのまま使っているのが問題なのでは、と、書籍を参照したりwebを検索したりして目ぼしい設定を変更してみたのですが改善されず。というか、私の感じたところ、コンフィグ設定というのは(RDBMSにとって潤沢とは言えない)メモリ資源をどこに分配するのかを決定するものなのかなと。一方、今回の件はタスクマネージャーを見てる限りmysqld.exeが25%に張り付いており(4コアなので)、メモリではなくCPUバウンドな問題のように思われます。従ってあまりコンフィグ設定は結果に影響を与えるものではないと結論付け、諦めました。

上のSQL文から、Shotテーブルに属すカラムを削除し、それに伴って不要となったShotテーブルのJOINも削除することで、大分速度は改善されました。それでも6秒程度はかかっていますが。

同じデータに対して同じクエリをPostgreSQL9.0とOracle10g XEで試したところ、どちらも即答でした。

素人考えだと、同じ結果を得られるのであればSELECT文は少ない方が速い、と考えていたのですが(少なくともMySQLにおいては)そうではない状況もあるのだと気付かされたのも新鮮でした。

実際のエンティティクラスはもちろん他の属性も持っていますし関連もこれより多く、更にMySQLが苦手であるということで有名な相関サブクエリなんかも行っていますので私はPostgreSQLに逃げることにしました(あとMySQLではJOINの最大数が61というのも心許ない)が、もしMySQLを使用するのであれば、JOINを控えめにした方が良さそうです(Lazy Fetchにするとか、Select-Fetchにするとか、何らかの対価で回避できる状況はあると思います)。ただし、前回記載したような強制的にJoin-Fetchになるような場合もある(HHH-3538)のでFetch戦略を自分で制御できない部分があるかどうかは注意して見ておくべきでしょう(自分は一回目のクエリはIDにProjectionして2回目のクエリでそのIDをキーに引っ張ってくる、という自力Select-Fetchみたいなこともやってました)。

本筋から外れますが最後に。上で示したEXPLAIN結果の5行目Extra列ではUsing indexとUsing join bufferが同時に表れています。他の方が書かれたMySQLパフォーマンスチューニングに関するいくつかのblogエントリでは『join bufferが使用されるのはindexが用いられない場合のみであり、コンフィグのjoin_buffer_sizeを増加させるよりindexを利用できるクエリに書き換えることを検討すべきだ』とあるのを見かけました。おそらくMySQL5.1の時代かそれ以前に書かれたものだとは思います(今回試した結果でも、確かに5.1ではjoin bufferは使われていなかったように記憶しています)。MySQLを使用しないことに決めたので詳しくは調べていないのですが、この差異はMySQL5.5(正確には5.4?)で導入されたBatched Key Accessから来ているのではないかと思います。ですので、必ずしも昔書かれたチューニングが現在でも適用可能であるとは限らないので、自身でもちゃんと調べた方が良いのかな、と思いました。

[追記]

これを書いた後に確認のため、と再実行したときに驚愕の事実が発覚しました。mysqldumpしたファイルを読み込ませた直後にクエリを実行すると確かに上のような結果になるのですが、リストア後に一度MySQLをrestartしてから同じクエリを実行すると全然スピードが違いました。リストア直後は前述の通り1分弱かかるのですが、再起動後だとなんと4秒！(ただしこれでも他のRDBMSよりは遅いです)

実際のデータで検証してたときは何度もMySQLの再起動は行ったはずで、そのときはやっぱり遅かったのでクエリがスローダウンするJOIN数の閾値が変わってるだけなんでしょうかね…(前述の通り、restartしない場合でもJOINを一つ減らすだけで高速化しましたし)。でも検証の後半で行っていたDbUnitでデータ突っ込んでクエリして、という自動化した検証の結果は間違っている可能性もあるのか…うーん。