データベースのパフォーマンスを損なう3つの書き方(翻訳)|TechRacho(テックラッチョ)〜エンジニアの「?」を「!」に〜|BPS株式会社エンジニアの「?」を「!」に。原著者の許諾を得て翻訳・公開いたします。データベースのパフォーマンスを損なう3つの書き方(翻訳)2005年にRailsのActiveRecordを初めて見たときに、稲妻のような天啓を感じたのを未だに覚えています。当時はPHPアプリで生SQLクエリを書いていましたが、それまで面倒で退屈でたまらなかったデータベースの扱いが、そのときを境に突如として簡単で楽しいものに変身したのです。...やがて、ActiveRecordのパフォーマンスの問題に気づくようになりました。ActiveRecordそのものが遅いわけではありませんでした。その頃には実際に実行されるクエリに注意を払わなくなっていたのです。CRUDアプリで用いられる最も定番のデータベースクエリの中に、データセットが巨大化したときのデフォルトのパフォーマンスがかなり見劣りするものがあることがわかってきました。本記事では、パフォーマンスを損なう3つの主要な犯人について解説します。最初に、データベースクエリが正常にスケールしているかどうかを調べる方法について説明しましょう。パフォーマンスの測定データセットが十分小さければ、どんなデータベースクエリでも十分パフォーマンスを発揮できます。したがって、本当のパフォーマンスを実感するには本番のサイズでデータベースのベンチマークを取る必要があります。ここでは22,000レコードを持つfaultsというテーブルを用いることにします。データベースはPostgreSQLです。PostgreSQLでパフォーマンスを測定するには次のようにexplainを使います。loops=1)の両方が表示されます。読みやすくしたいのであれば、次のようにYAML形式で出力することもできます。Rows」の2つだけに注目することにします。Rows」が1の場合、このクエリは正常にスケールすると見込まれます。Rows」がデータベースの行数と等しい場合、クエリが「フルテーブルスキャン」を行っていることが示されます。この場合クエリはうまくスケールできないでしょう。クエリパフォーマンスの測定方法の説明が終わりましたので、Railsのいくつかの定番コードでどんな問題が起きているかを見てみましょう。count以下のコードはRailsビューで非常によく見かけます。%>このコードから生成されるSQLは次のような感じになります。faults;explainで調べてみましょう。row)これは...シンプルなcountクエリが22,265回もループしているではありませんか。これはテーブルの全行数です。PostgreSQLでは、countは常に全レコードセットをループします。このレコードセットのサイズを減らすには、クエリにwhere条件を追加します。要件によっては、パフォーマンスが十分受け入れられる程度にサイズを減らすことができるでしょう。この問題を回避する他の方法として、唯一、count値をキャッシュする方法があります。Railsにはそのための仕組みがあるので、以下のように設定できます。trueクエリが何らかのレコードを返すかどうかのチェックにも別の方法があります。0をやめて、代わりにUsers.exists?をお試しください。Shawに感謝いたします)。gemを使う方法もあります。ソートindexページは、ほぼどんなアプリにも1つや2つあるでしょう。indexページでは、データベースから最新の20レコードを取り出して表示します。これをどうやってもっとシンプルにできるのでしょうか?レコード読み出し部分はおおよそ以下のような感じになっていると思います。:desc)このときのSQLは次のような感じになります。desc;分析してみましょう。row)このクエリを実行するたびに、データベースが22,265行をソートしていることがわかります。これはあかんやつです。BY句のたびにレコードセットをその場でソートします。これにはキャッシュも効きませんし、うまいマジックもありません。解決法は、インデックスを用いることです。この例のようにシンプルであれば、created_atカラムにソート済みインデックスを追加するだけでクエリはかなり高速になります。Railsのマイグレーションに以下を追加します。endendこのマイグレーションで、以下のSQLが実行されます。(created_at);末尾の(created_at)はソート順を指定しています。デフォルトは昇順です。これでソートのクエリを再度実行してみると、ソートが行われなくなることがわかります。インデックスからソート済みのデータを読み出すだけで済むようになりました。row)複数のカラムでソートする場合は、複数カラムでソートしたインデックスを作成する必要があります。Railsマイグレーションでは以下のような感じで記述します。:desc)より複雑なクエリに対処する場合は、explainで確認するとよいでしょう。早いうちに頻繁に行うのがポイントです。クエリによっては、わずかな変更をかけただけで、PostgreSQLでソートのインデックスが効かなくなることに気づくかもしれません。limitとoffsetデータベースの全項目をindexページに表示することはめったにありません。普通はページネーションを使って、一度に10件から30件、50件程度を表示します。このときに最もよく使われるのがlimitとoffsetの組み合わせです。Railsでは次のような感じになります。Fault.limit(10).offset(100)このときのSQLは次のような感じになります。100;ここでexplainを実行してみると奇妙なことに気づきます。スキャンされた行数は110件で、ちょうどlimitとoffsetを足したのと同じです。...ここでoffsetを10,000に増やしてみると、スキャンされた行数も一気に10010件に増加し、クエリの実行時間も64倍に増えます。...ここから残念な結論が得られます。ページネーションでは後のページになるほど速度が低下します。上の例では1ページあたり100件を表示するので、100ページ目になると1ページ目より13倍も時間がかかってしまいます。したらよいでしょうか?正直に申し上げると、これについて完全な解決法をまだ見つけられていません。私なら、ページ数が100ページや1000ページにならないよう、まずはデータセットのサイズを減らせないか検討するかもしれません。レコードセットのサイズを減らすのが無理であれば、offsetとlimitをwhereに置き換える方法が一番有望でしょう。200)まとめ本記事が、PostgreSQLのexplain関数を利用してデータベースクエリに潜むパフォーマンスの問題を検出するのにお役に立てば幸いです。コボラー、ITコンサル、ローカライズ業界、Rails開発を経てTechRachoの編集・記事作成を担当。チュートリアル第2版の監修および半分程度を翻訳、Railsガイドの初期翻訳ではほぼすべてを翻訳。その後も折に触れて更新翻訳中。enno.jpを運営。実は最近Go言語が好きで、Goで書かれたRubyライクなGoby言語のメンテナーでもある。仕事に関係ないすっとこブログ「あけてくれ」は2000年頃から多少の中断をはさんで継続、現在はnote.muに移転。Storageを使ってはいけない(翻訳)#Tags#会社紹介#自社サービス#勉強会#Rails#PHP#デザイン#実績#週刊RailsウオッチCONTACTお気軽にご相談ください。(翻訳)CONTACTTechRachoでは、パートナーシップをご検討いただける方からのご連絡をお待ちしております。お気軽にご意見・ご相談ください。