Movatterモバイル変換

O/R Mapper によるトラブルを未然に防ぐMakoto Kuwata <kwa@kuwata-lab.com>http://www.kuwata-lab.com/PostgreSQLカンファレンス 2014ver 1.1.0

copyright © 2014 kuwata-lab.com all rights reservedまえがき現在、アプリケーション開発の現場では O/R Mapper (ORM) が普及しています。今後も ORM を使った開発は、増えることはあっても減ることはないでしょう。しかし ORM は、アプリケーション開発者にとっては便利でも、DB 管理者 (DBA) からみたらトラブルの種でもあります。それが特にパフォーマンスに関する問題であることが多いため、開発者と DBA が対立することも珍しくありません。とはいえ、ORM による問題はすでに解決策が用意されている場合があります。本当の問題は、すでに存在する解決策があまり知られていないことではないでしょうか。そこで本発表では、ORM によってどのような問題が起こりやすいか、どう解決・予防すればいいか、そして ORM とどう「折り合い」をつけるかを説明します。特に、よくトラブルとなる「N+1 問題」については説明を多めにしています。また本発表を通じて、開発者と DBA が「互いが互いを知らないままに批判しあう」という状況を改善し、両者が協調できる「まだ見ぬ丘の向こう」を目指します。

copyright © 2014 kuwata-lab.com all rights reservedわかってない人によるORM批判その１(ORMは) テーブルから全行をスキャンし、クライアントアプリケーションへネットワーク越しに転送する“”『nabokov7; rehash : O/Rマッパーはなぜ悪か』http://nabokov.blog.jp/archives/1529263.htmlちゃんとDB側で絞りこんでから転送してますよぉ！

copyright © 2014 kuwata-lab.com all rights reservedわかってない人によるORM批判その２たとえば、SQL を書くことでフィルタリングを DB で実行していたのに、O/R を使うことでフィルタリングは、そのプログラミング言語がやることになります。“”where句もhaving句もDB側でやってますよぉ！https://twitter.com/kazu_yamamoto/status/280872190805680129

copyright © 2014 kuwata-lab.com all rights reserved本発表の目的は、DBAの皆さんに…✓ ORMでよくあるトラブルとその予防策を知ってもらう嫌悪感の本当の原因は、ORMがトラブルを起こすからではなく、トラブルが解決できない (＝解決策を知らない) から✓ そのためにORMの仕組みを知ってもらう解決方法を知らないのは、DBAがORMを知らなすぎるから✓ 「O/R Mapperなんか使うな！」とは違う問題解決方法があることを知ってもらう「問題があるから禁止」は問題の解決になっていない

copyright © 2014 kuwata-lab.com all rights reservedSQLを動的に組み立てる：文字列結合var cond = [], args = [];if ( params.gender == "M"|| params.gender == "F") {cond.push("gender = ?");args.push(params.gender);}if (params.max_age) {cond.push("age <= ?");args.push(params.max_age);}var sql = "select * from users";if (cond.length) {sql += " where " + cond.join(" and ");}sql += " order by name";とても面倒なうえに、SQL Injectionを誘発しやすい

copyright © 2014 kuwata-lab.com all rights reservedSQLを動的に組み立てる：ORMSQLテンプレート方式専用のテンプレートエンジンを使って、SQLを生成するselect * from studentswhere/** if (gender) { **/gender = :gender/** } **/order by nameクエリオブジェクト方式SQLを表現するデータを作成し、それをSQL文字列に変換する{ table: "students", where: [["gender=","F"]], orderby: ["name"] }select * from studentswhere gender = 'F'order by name

copyright © 2014 kuwata-lab.com all rights reservedSQLテンプレート方式select *from students/** if (gender || max_age) { **/where true/** if (gender) { **/and gender = :gender/** } **//** if (max_age) { **/and age <= :max_age/** } **//** } **/order by name文字列結合よりは読みやすい、SQL Injectionも誘発しないこの「true」はSQLのoptimizerが除去してくれる (PostgreSQL)

copyright © 2014 kuwata-lab.com all rights reservedSQLテンプレート方式select *from students/** if (gender || max_age) { **/where true/** if (gender) { **/and gender = :gender/** } **//** if (max_age) { **/and age <= :max_age/** } **//** } **/order by name不要な "and" や "while" をORMが自動的に取り除いてくれるSQLを解釈できるORMなら、より簡潔に書けることも

copyright © 2014 kuwata-lab.com all rights reservedSQLテンプレート方式select st.* from students stwhere/** if (gender) { **/st.gender = :gender/** } **/---------------------select st.*, cl.* from students stjoin classes cl on st.class_id = cl.idwhere/** if (gender) { **/st.gender = :gender/** } **/それぞれで条件式が重複している(DRYではない)似たようなSQLを複数書く必要があり、DRYではない所属クラスがいらない場合のSQL所属クラスが必要な場合のSQL

copyright © 2014 kuwata-lab.com all rights reservedクエリオブジェクト方式var query = {table: "students", where: [], orderBy: []};//if (params.gender) query.where.push(["gender=", params.gender]);if (params.max_age) query.where.push(["age<=", params.max_age]);query.orderBy.push("name");//var sql = generateSQL(query);オブジェクトに各種条件を追加し、最後にSQLへ変換where や order by を データとして操作できる

copyright © 2014 kuwata-lab.com all rights reservedクエリオブジェクト方式var query = new Query(Student);//if (params.gender) query.where("gender", params.gender);if (params.max_age) query.where("age<=", params.max_age);query.orderBy("name");//var sql = query.generateSQL();通常は専用のクエリクラスを使うので、簡潔に書ける

copyright © 2014 kuwata-lab.com all rights reservedクエリオブジェクト方式var query = new Query(Student);//if (params.gender) query.where(Student.gender == params.gender);if (params.max_age) query.where(Student.age <= params.age);query.orderBy(Student.name);//var sql = query.generateSQL();よくできた言語とよくできたORMなら「式」を指定可能演算子オーバーライドやAST変換を活用

copyright © 2014 kuwata-lab.com all rights reservedクエリオブジェクト方式==x nilx is nullx == nil 評価変換構文解析ではないことに注意!("==" の評価結果がtrue/falseではなく構文木)（ソースコード）（構文木）（SQL）「x = null」ではなく「x is null」になってくれる!演算子オーバライドを利用した、構文木生成の仕組み

copyright © 2014 kuwata-lab.com all rights reserved余談：LINQの実態はクエリオブジェクト// SQLのようだが実はC#from st in Studentwhere st.gender == "F"order by st.nameselect st;LINQ：変換前// だいたいこんな感じFrom(Student).Where(x => x.gender == "F").OrderBy(Student.name).ToArray();LINQ：変換後

copyright © 2014 kuwata-lab.com all rights reserved特徴：SQLテンプレート方式✓ 仕組みがわかりやすいORMの学習コストが低い、トラブルに対処しやすい✓ SQLが予想しやすいつまりチューニングしやすい✓ SQLの欠点はそのままDRYにする仕組みがない、'=' と 'is' の使い分けが必要、など✓ SQL Injectionはほぼ発生しない文字列結合をするコードを書かなくて済むため

copyright © 2014 kuwata-lab.com all rights reserved特徴：クエリオブジェクト方式✓ 仕組みが複雑ORMの学習コストが高い、トラブル対応がしにくい✓ どんなSQLになるかを確認する必要がある予想外のSQLが生成されることも✓ SQLではできないことができる詳細は『なぜORMが必要か？』でggr✓ SQL Injectionはほぼ発生しない構文木（or 類似した構造）を作ってからSQLに変換するため

copyright © 2014 kuwata-lab.com all rights reservedORMのアーキテクチャは「PoEAA」を読めObject-Relational Structural Patterns• Identity Field• Foreign Key Mapping• Association Table Mapping• Dependent Mapping• EmbeddedValue• Serialized LOB• Single Table Inheritance• Class Table Inheritance• Concrete Table Inheritance• Inheritance MappersData Source Architectural Patterns• Table Data Gateway• Row Data Gateway• Active Record• Data MapperObject-Relational Behavioral Patterns• Unit of Work• Identity Map• Lazy LoadObject-Relational Metadata MappingPatterns• Metadata Mapping• Query Object• Repository（注）PoEAA …『Pattern of Enterprise Application Architecture』(Martin Fowler, 2002)

ORMでよくあるトラブルとその対策

copyright © 2014 kuwata-lab.com all rights reserved「N+1 問題」とは？一覧を取得するSQLを発行してから、 各要素ごとに個別のSQLを発行してしまうこといわゆる「ぐるぐる系SQL」のこと。パフォーマンスが極端に落ちるusers = User.all()for user in users print user.group.nameendselect * from groups where id = :id をN回発行select * from users を1回発行（注）実態をより正確に表すなら「1+N問題」と呼ぶべき（注）

copyright © 2014 kuwata-lab.com all rights reservedORM側の対策：eager loading一覧を取得するときに、関連する要素もまとめて取得するよう指定する実装は、join だったり id in (…) だったり、まちまちusers = User.includes("group").all()for user in users print user.group.nameendselect文は発行されないselect * from users を1回発行してから、select * from groups where id in (…) を1回発行する（参考）『3 ways to do eager loading (preloading) in Rails 3 & 4 』http://blog.arkency.com/2013/12/rails4-preloading/

copyright © 2014 kuwata-lab.com all rights reservedORM側の対策：strategic eager loadingオブジェクトの関連が必要になったら、親となるコンテナに通知し、コンテナがまとめて取得する子であるオブジェクトが個別に取得するのを止めるusers = User.includes("group").all()for user in users print user.group.nameendgroups テーブルへのアクセスが必要になると、親となるコンテナである users へ通知され、users がまとめて groups テーブルにアクセスする明示的な指定が必要ない（参考）『Why DataMapper? (Section: Strategic Eager Loading)http://datamapper.org/why.html

copyright © 2014 kuwata-lab.com all rights reservedORM側の対策：bytecode manipulationbytecodeを解析してeager loadingが必要な箇所を判定し、それを行うコードを自動的に埋め込むbytecode操作のかわりにAST変換やpreprocessorでもよいList<User> users = query(User).all();for (User user: users) { System.out.print(user.group.name);}ループの中で関連を取得していることを検出し、ループ前にまとめて取得するようbytecodeを変更（参考）『スケーラブルラピッドプロトタイピングのためのJIT-ORM 』http://www.ipa.go.jp/ﬁles/000007122.pdf

copyright © 2014 kuwata-lab.com all rights reservedinsert文における「N+1 問題」1件ずつinsert文を発行するのをやめて、 bulk insert機能を使って一括作成するもちろん、大量すぎる場合は copy 文// Bad：１件ずつinsertfor s in names u = User.new(name: s) u.save()end// Good：まとめてinsertusers = names.map {|s| User.new(name: s)}User.import(users)

copyright © 2014 kuwata-lab.com all rights reservedupdate文における「N+1 問題」1件ずつupdate文を発行するのをやめて、 条件で指定した集合をまとめて更新するjQueryにおける $("..条件..").attr(key, value) と同じ// Bad：１件ずつの更新users = db.query(User).all()for u in users: u.value = u.value+1db.commit()// Good：まとめて更新db.query(User) .update({ "value": User.value+1 })db.commit()これは「値」(数値) これは「式」(構文木) （注）（注）まとめて更新するには、「値」ではなく「式」(構文木) を指定できる必要があり、そのためには演算子オーバーライドなどが使えると便利。

copyright © 2014 kuwata-lab.com all rights reservedDBAが取るべき予防策✓ N+1問題について開発側と話しあっておく使用するORMでの解決方法を事前に確認しておく、 また親-子だけでなく親-子-孫の場合も解決できるか要確認✓ 「N+1問題だけは許さんぞ！」と、開発側に しつこく言い続けてプレッシャーをかけるN+1問題の名前は知ってても深刻さを分かってない開発者が多い「N+1問題？何ですかそれ？」と開発者が言おうものなら「喝！」

copyright © 2014 kuwata-lab.com all rights reserved「クエリ発行箇所が特定できない問題」とは？スロークエリが検出されても、それがプログラムのどこで発行されたかが分からず手が打てない問題DBAにとっては、とてもフラストレーションのたまる事案User.where(gender: "F") .where(deleted: nil) .order_by("name") .all()select * from userswhere gender = "F" and deleted is nullorder by nameこっち方向は特定できるけどこっち方向が特定できない ;(

copyright © 2014 kuwata-lab.com all rights reservedORM側の対策：SQL ID、クエリIDコメントを使って、SQLごとに一意なIDをつける１つのSQLが複数個所から呼ばれることがあるので、クエリごとにIDをつけるのが望ましい (注)-- [sql:fg9xk]select *from users/** if (gender) { **/where gender = :gender/** } **/User.where(gender: "F") .where(deleted: nil) .order_by("name") .comment("[q:yxe4m]") .all()多くのORMで未サポート ;(（注） IDのかわりに、呼び出し元のファイル名と行番号でもよい

copyright © 2014 kuwata-lab.com all rights reserved「インデックスつけ忘れ問題」とは？そのままの意味ORMでなくても発生する問題だが、発行されるSQLが具体的でないとexplainを実行できないため、ORMだとより発生しやすいといえるUser.where(gender: "F") .where(deleted_at: nil) .order_by("created_at") .all()実行しないとどんなSQLが分かりにくい→ explain で調べにくい → index つけ忘れに気付かない

copyright © 2014 kuwata-lab.com all rights reservedORM側の対策：スキーマ定義で指定カラムごとにインデックス作成を指定する複合インデックスも指定できることが多いclass User(Base): id = Column(Integer, primary_key=True) name = Column(String(255), nullable=False, index=True) email = Column(String(255), nullable=True, index=True)カラムごとにインデックス作成を 指定できるので忘れにくい（SQLはこれができないので忘れやすい）残念ながら、本質的な解決策がない ;(

copyright © 2014 kuwata-lab.com all rights reservedDBAが取るべき予防策✓ インデックスが必要そうなカラムをリストアップし、重点的にチェック・where で使いそう … 名前, コード, メアド, 生年月日, etc・order by で使いそう … 名前, 作成日時, 更新日時, etc・join で使いそう … 外部キー, 多対多の中間テーブル, etc✓ 大きいテストデータを用意してあげる小さいデータで開発しているから気付かない、 大きいデータを用意してあげれば遅いことに気付きやすい

copyright © 2014 kuwata-lab.com all rights reserved「select * 問題」とは？ORMが、使わないカラムもすべて select * で取得してしまう問題。特にBlobや長いtextでは重大な問題articles = db.query(Article)for x in articles: print(x.id, x.title)ここでは記事のIDとタイトルしか必要ないのに、記事の本文まで取得してしまう →負荷増大（注）たいていのORMではカラム名を指定できるはずだが、外部キー経由で取得した関連オブジェクトのカラムまでは指定できない。

copyright © 2014 kuwata-lab.com all rights reservedORM側の対策：遅延フェッチBlobや長いtextは、デフォルトではselect時に 除外するよう、ORMのスキーマで指定する明示的に指定した場合のみ取得するclass Article(Base): id = Column(Integer, primary_key=True) title = Column(String, nullable=False, index=True) body = deferred(Column(Text, nullable=False)デフォルトでは select 文で取得する対象にしない

copyright © 2014 kuwata-lab.com all rights reservedDBAが取るべき予防策✓ Blobや長いtextは、別テーブルに分離するかわりに N+1 問題が発生する可能性があるので注意すること✓ 必要なカラムだけを指定したビューを作る外部キーで参照しているテーブルまでは変えられないので注意✓ カラム名の細かい指定はある程度あきらめるパフォーマンス劣化が深刻でなければ許容する

ORMと折り合いをつける• • • •ための、DBAの心構え

copyright © 2014 kuwata-lab.com all rights reserved心構えその５：金を積む✓ 「SQLのできる開発者」は金を出せば手に入る札束で頬をひっぱたけばスキルのある開発者を囲えることは、 GREEやDeNAやLINEが証明してくれました✓ 金を惜しんでるなら文句を言うべきではない月20万30万そこそこの人材に多くを求めすぎない (※)（※）たいていのアプリ開発者にとって、SQLは「多く」に含まれることに注意

copyright © 2014 kuwata-lab.com all rights reservedQ&A：個人的にお勧めなO/Rマッパーは？✓ DataMapper (ruby) http://datamapper.org/Strategic Eager Loadingのような秀逸なアイデアを生み出している✓ Sequel (ruby) http://sequel.jeremyevans.net/使いやすさと簡潔さがよく考えられている印象✓ SQLAlchemy (python) http://www.sqlalchemy.org/教科書 (PoEAA) に忠実に作られている印象✓ 自作！自作マジお勧め！職人が自分の道具作って何が悪い！車輪の再発明なぞ知らんがな！ActiveRecord？あれはあんまり…

copyright © 2014 kuwata-lab.com all rights reservedこの資料を読んだ人はこんな資料も読んでます✓ O/R Mapperを支える技術http://rubykaigi.org/2011/ja/schedule/details/18S08✓ なぜO/Rマッパーが重要か？http://www.slideshare.net/kwatch/sqlor✓ ORM is an anti-patternhttp://seldo.com/weblog/2011/06/15/orm_is_an_antipattern✓ ORMのパフォーマンス最適化http://www.infoq.com/jp/articles/optimizing-orm-performance✓ Patterns Implemented by SQLAlchemyhttp://techspot.zzzeek.org/2012/02/07/patterns-implemented-by-sqlalchemy/

ORMごときに振り回される段階はとっとと卒業して、より高度で本質的な問題にエネルギーを使いましょう

Movatterモバイル変換

Change Language

O/Rマッパーによるトラブルを未然に防ぐ

Embed presentation

Recommended

More Related Content

What's hot

Similar to O/Rマッパーによるトラブルを未然に防ぐ

More from kwatch

O/Rマッパーによるトラブルを未然に防ぐ