DBIxでN+1問題に直面した・・其の壱
いにしえのコードを修正していたらDBIxでN+1問題に直面している部分を発見した！！
なので解決策をサクッと書こうと思います(/･ω･)/
初めにN+1問題とは
PerlのDBIxのようなORMは最適化のために余分なカラムは読み込まないようになっていることが多いです。
そのため、読み込まれていないカラムを表示しようとすると、
本来のSQL(1)に加えて、各レコードごとに1回のSQL(N)が実行されてしまいます／(^o^)＼ﾅﾝﾃｺｯﾀｲ
つまり、無駄にSQLが発行され、表示に時間がかかります。
Rubyあまり触ったことないんですが、参考にした記事がRubyなのでRubyにもいえる話なんでしょうね・・('A`)
たとえば以下のようなn_plus_one_problemデータベースがあって
authorテーブル
| author_id | name |
|---|---|
| 1 | hoge |
| 2 | huga |
bookテーブル
| book_id | title | author_id |
|---|---|---|
| 1 | aaaa | 1 |
| 2 | bbbb | 1 |
| 3 | cccc | 2 |
| 4 | dddd | 2 |
authorテーブルとbookテーブルに1対多のリレーションが貼られているとします。
本の情報を表示したいとき、
Controller側は
sub index : Path :Args( 0 ) { my ( $self, $c ) = @_; my $book_rs = $c->model( 'Master::Book' ); $c->stash( book_rs => $book_rs ); return; }
View側は
<table border="1"> <tr> <th>Book ID</th> <th>Book Title</th> </tr> [% WHILE ( book = book_rs.next ) %] <tr> <td>[% book.book_id %]</td> <td>[% book.title %]</td> <tr> [% END %] </table>
とすればいいですね。
すると、クエリログはこうなります(DBIx::QueryLogモジュール)
[2015-06-13T06:57:56] [DBIx::Class::Storage::DBI] [0.000502] SELECT COUNT( * ) FROM book me JOIN author author ON author.author_id = me.author_id at.. [2015-06-13T06:57:56] [DBIx::Class::Storage::DBI] [0.000452] SELECT me.book_id, me.title, me.author_id FROM book me JOIN author author ON author.author_id = me.author_id at..
While文を回すためのカウントSQLは例外として、本体のSQLで1回のクエリが走っているのが分かります。
そこに著者の情報も表示したくなったとき、
Controller側は
sub index : Path :Args( 0 ) { my ( $self, $c ) = @_; my $book_rs = $c->model( 'Master::Book' )->search( undef, { join => 'author' } ); $c->stash( book_rs => $book_rs ); return; }
View側は
<table border="1"> <tr> <th>Book ID</th> <th>Book Title</th> <th>Author ID</th> <th>Author Name</th> </tr> [% WHILE ( book = book_rs.next ) %] <tr> <td>[% book.book_id %]</td> <td>[% book.title %]</td> <td>[% book.author.author_id %]</td> <td>[% book.author.name %]</td> <tr> [% END %] </table>
とすればいいですね。
すると、クエリログはこうなります。
[2015-06-13T07:00:28] [DBIx::Class::Storage::DBI] [0.000439] SELECT COUNT( * ) FROM book me JOIN author author ON author.author_id = me.author_id at.. [2015-06-13T07:00:28] [DBIx::Class::Storage::DBI] [0.000330] SELECT me.book_id, me.title, me.author_id FROM book me JOIN author author ON author.author_id = me.author_id at.. [2015-06-13T07:00:28] [DBIx::Class::Storage::DBI] [0.000434] SELECT me.author_id, me.name FROM author me WHERE ( me.author_id = '1' ) at.. [2015-06-13T07:00:28] [DBIx::Class::Storage::DBI] [0.000213] SELECT me.author_id, me.name FROM author me WHERE ( me.author_id = '1' ) at.. [2015-06-13T07:00:28] [DBIx::Class::Storage::DBI] [0.000601] SELECT me.author_id, me.name FROM author me WHERE ( me.author_id = '2' ) at.. [2015-06-13T07:00:28] [DBIx::Class::Storage::DBI] [0.000196] SELECT me.author_id, me.name FROM author me WHERE ( me.author_id = '2' ) at..
わ～～お！！なんかたくさん走ってますね！！
さらに、SELECT句をダンプするとこうなります。(Data::Printerモジュール)
select [
[0] "me.book_id",
[1] "me.title",
[2] "me.author_id"
]
JOINはしたものの結合先のテーブル情報はロードされていないことが分かります。
しかし、Viewでauthorテーブルの情報を表示しようとしているため、
リレーションを便りにクエリが実行され、テーブル情報を取ってこようとします。
結果、N+1回クエリが実行されてしまいます。
少し古い情報ですが、Catalystのオレンジ本ではN+1問題を解決するために、別テーブルのカラムを参照する際にはJOINではなくPREFETCHを推奨しています。
実際、JOINではなくPREFETCHを用いてみると、
[2015-06-16T06:16:31] [DBIx::Class::Storage::DBI] [0.000633] SELECT COUNT( * ) FROM book me JOIN author author ON author.author_id = me.author_id at.. [2015-06-16T06:16:31] [DBIx::Class::Storage::DBI] [0.000285] SELECT me.book_id, me.title, me.author_id, author.author_id, author.name FROM book me JOIN author author ON author.author_id = me.author_id at..
なんかいけましたね(。-`ω´-)ﾝｰ.
select [
[0] "me.book_id",
[1] "me.title",
[2] "me.author_id",
[3] "author.author_id",
[4] "author.name"
]
このようにauthorテーブルの情報がすでに読み込まれていることが分かります。
PREFETCHは結合したテーブルのカラムを自動的に読み込んでくれます。
デメリットとして無駄なデータの読み込みは発生しますが、このほうがミスは少なそうですね。
もしRubyのORMにPREFETCHに相当するものがあるのであれば、それを使えばいいかもしれません(調べてないけどねw)
しかし、DBIxのPREFETCHはクセの強いやつで、そういう訳にもいきません。
少なくとも僕の使ったバージョンでは・・
その辺の話は次回にさせていただく予定です(まとめるのつらい・・)
