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

O/R Mapper による
トラブルを未然に防ぐ
Makoto Kuwata <kwa@kuwata-lab.com>
http://www.kuwata-lab.com/
PostgreSQLカンファレンス 2014
ver 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 が「互いが互いを知らないままに批判しあう」と
いう状況を改善し、両者が協調できる「まだ見ぬ丘の向こう」を目指します。

まとめ
✓ ORMによるトラブルは (たいてい) 解決策がある
✓ 解決策を知れば嫌悪感は (許容半以内に) 抑えられる
✓ 我々のゴールは「プロジェクトやサービスの 
成功」であることを思い出す (対立することではない)

背景：ORMによるトラブルが多発
✓ ORMが生成するSQLがクソ
いわゆる「ぐるぐる系SQL」
✓ SQLをろくに勉強しないアプリ開発者
そのくせOOPやデザインパターンを得意げに語る ;(
✓ 開発効率を上げるためのORMなのに話が違う!
ある面では効率が上がっても、別の問題を引き起こしている

どこかでみた風景
実はオブジェクト指向って
しっくりこないんです…
SQLの書けないやつなぞ
エンジニアとして三流！
今はORMもKVSもある！
SQLよりOOPのほうが重要！
Staticおじさんｗｗ
老害ｗｗｗｗｗ
OOP信者
自分の得意分野に入り浸って、それ以外の分野に飛び込めない人たちっているよね…

背景：DBAがORMを知らなすぎる
✓ ORMのことをろくに知らずに批判している人
が多すぎ
✓ 「SQLをろくに知らない開発者」 
vs 「ORMをろくに知らないDBA」
✓ ORMを知らない→トラブルが解決できない→
「ORMなんかクソ！」

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

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

なぜORMを嫌うのか？
✓ 理由： 
ORMがトラブルを引き起こすから？
✓ 本当の理由： 
ORMによるトラブルが解決できないから！
トラブルの解決方法を知れば
そこまで嫌うことはないはず

理想と現実の間には、妥協点が存在する
・アプリ開発者がSQLを勉強してくれる
・ORMの吐くSQLにDBAが反吐を吐く
・ORMによるトラブルの芽を早期に潰す
【理想】
【現実】

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

ORMの役目は、大きく３つ
• • •
SQL
RecordSet
SQLを組み立てて発行
スキーマを作成・変更
トラブルはここで発生
（今日はここのお話）
レコードセットを
オブジェクトに変換
Database Application

Prepared Statementじゃだめなの？
✓ 要件：実行時に検索条件を変えたい
✓ Prepared Statementではwhere句への追加
などが行えない
性別：男性女性
年齢： ∼ 歳歳
無指定
検索条件

SQLを動的に組み立てる：文字列結合
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を誘発しやすい

SQLを動的に組み立てる：ORM
SQLテンプレート方式
専用のテンプレートエンジンを
使って、SQLを生成する
select * from students

where

/** if (gender) { **/

gender = :gender

/** } **/

order by name
クエリオブジェクト方式
SQLを表現するデータを作成し、
それをSQL文字列に変換する
{ table: "students",

where: [["gender=","F"]],

orderby: ["name"] }
select * from students

where gender = 'F'

order by name

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)

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なら、より簡潔に書けることも

select st.* from students st
where
/** if (gender) { **/
st.gender = :gender
/** } **/
---------------------
select st.*, cl.* from students st
join classes cl on st.class_id = cl.id
where
/** if (gender) { **/
st.gender = :gender
/** } **/
それぞれで条件式が重複している
(DRYではない)
似たようなSQLを複数書く必要があり、DRYではない
所属クラスが
いらない場合のSQL
所属クラスが
必要な場合のSQL

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 を 
データとして操作できる

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();
通常は専用のクエリクラスを使うので、簡潔に書ける

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変換を活用

==
x nil
x is nullx == nil 評価変換
構文解析ではないことに注意!
("==" の評価結果がtrue/falseではなく構文木)
（ソースコード）（構文木）（SQL）
「x = null」ではなく
「x is null」になってくれる!
演算子オーバライドを利用した、構文木生成の仕組み

余談：LINQの実態はクエリオブジェクト
// SQLのようだが実はC#

from st in Student

where st.gender == "F"

order by st.name

select st;

LINQ：変換前
// だいたいこんな感じ

From(Student)

.Where(x =>

x.gender == "F")

.OrderBy(Student.name)

.ToArray();
LINQ：変換後

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

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

ORMのアーキテクチャは「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 Mappers
Data Source Architectural Patterns
• Table Data Gateway
• Row Data Gateway
• Active Record
• Data Mapper
Object-Relational Behavioral Patterns
• Unit of Work
• Identity Map
• Lazy Load
Object-Relational Metadata Mapping
Patterns
• Metadata Mapping
• Query Object
• Repository
（注）PoEAA …『Pattern of Enterprise Application Architecture』(Martin Fowler, 2002)

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

ORMでよくあるトラブル
✓ N+1 問題
深刻度：極
✓ クエリ発行箇所が特定できない問題
深刻度：大
✓ インデックスつけ忘れ問題
深刻度：中
✓ select * 問題
深刻度：小

「N+1 問題」とは？
一覧を取得するSQLを発行してから、 
各要素ごとに個別のSQLを発行してしまうこと
いわゆる「ぐるぐる系SQL」のこと。パフォーマンスが極端に落ちる
users = User.all()

for user in users

print user.group.name

end

select * from groups where id = :id をN回発行
select * from users を1回発行
（注）実態をより正確に表すなら「1+N問題」と呼ぶべき
（注）

ORM側の対策：eager loading
一覧を取得するときに、関連する要素もまとめて取
得するよう指定する
実装は、join だったり id in (…) だったり、まちまち
users = User.includes("group").all()

for user in users


end

select文は発行されない
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/

ORM側の対策：strategic eager loading
オブジェクトの関連が必要になったら、親となるコ
ンテナに通知し、コンテナがまとめて取得する
子であるオブジェクトが個別に取得するのを止める
users = User.includes("group").all()

for user in users


end

groups テーブルへのアクセスが必要になると、
親となるコンテナである users へ通知され、
users がまとめて groups テーブルにアクセスする
明示的な指定が必要ない
（参考）『Why DataMapper? (Section: Strategic Eager Loading)
http://datamapper.org/why.html

ORM側の対策：bytecode manipulation
bytecodeを解析して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

insert文における「N+1 問題」
1件ずつinsert文を発行するのをやめて、 
bulk insert機能を使って一括作成する
もちろん、大量すぎる場合は copy 文
// Bad：１件ずつinsert

for s in names

u = User.new(name: s)

u.save()

end
// Good：まとめてinsert

users = names.map {|s|

User.new(name: s)

}

User.import(users)

update文における「N+1 問題」
1件ずつupdate文を発行するのをやめて、 
条件で指定した集合をまとめて更新する
jQueryにおける $("..条件..").attr(key, value) と同じ
// Bad：１件ずつの更新

users =

db.query(User).all()

for u in users:

u.value = u.value+1

db.commit()
// Good：まとめて更新

db.query(User)

.update({

"value": User.value+1

})

db.commit()
これは「値」(数値) これは「式」(構文木) （注）
（注）まとめて更新するには、「値」ではなく「式」(構文木) を指定できる
必要があり、そのためには演算子オーバーライドなどが使えると便利。

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

「クエリ発行箇所が特定できない問題」とは？
スロークエリが検出されても、それがプログラムの
どこで発行されたかが分からず手が打てない問題
DBAにとっては、とてもフラストレーションのたまる事案
User.where(gender: "F")

.where(deleted: nil)

.order_by("name")

.all()
select * from users

where gender = "F"

and deleted is null

order by name
こっち方向は特定できるけど
こっち方向が特定できない ;(

ORM側の対策：SQL ID、クエリID
コメントを使って、SQLごとに一意なIDをつける
１つのSQLが複数個所から呼ばれることがあるので、クエリごとにIDをつ
けるのが望ましい (注)
-- [sql:fg9xk]

select *

from users

/** if (gender) { **/

where gender = :gender

/** } **/

.where(deleted: nil)

.order_by("name")

.comment("[q:yxe4m]")

.all()
多くのORMで未サポート ;(
（注） IDのかわりに、呼び出し元のファイル名と行番号でもよい

✓ SQL IDが付けられるなら、付けてもらう
✓ 呼び出し元のファイル名と行番号がわかるなら、
SQLコメントやログに記録してもらう
✓ Slow QueryがどのAPIで発行されたかを特定
できるような仕組みを用意する
要は、SQLだけでは特定できないなら別の方向から絞り込む

「インデックスつけ忘れ問題」とは？
そのままの意味
ORMでなくても発生する問題だが、発行されるSQLが具体的でないと
explainを実行できないため、ORMだとより発生しやすいといえる

.where(deleted_at: nil)

.order_by("created_at")

.all()
実行しないとどんなSQLが分かりにくい
→ explain で調べにくい
→ index つけ忘れに気付かない

ORM側の対策：スキーマ定義で指定
カラムごとにインデックス作成を指定する
複合インデックスも指定できることが多い
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はこれができないので忘れやすい）
残念ながら、本質的な解決策がない ;(

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

「select * 問題」とは？
ORMが、使わないカラムもすべて select * で取得
してしまう問題。
特にBlobや長いtextでは重大な問題
articles = db.query(Article)

for x in articles:

print(x.id, x.title)

ここでは記事のIDとタイトルしか
必要ないのに、記事の本文まで取
得してしまう →負荷増大
（注）たいていのORMではカラム名を指定できるはずだが、外部キー経由で
取得した関連オブジェクトのカラムまでは指定できない。

ORM側の対策：遅延フェッチ
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 文で
取得する対象にしない

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

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

心構えその１：相手を知る
✓ ORMの仕組みを知る
知っていればトラブルに対処しやすい、開発者に提案しやすい 
例：ぐるぐる系SQLが現れた！ 
× だからORMは止めよう！ 
◎ eager loadingを使うよう提案しよう！
✓ アプリ開発を知る
DBの知識だけで物事を考えない、トラブル対応しようとしない

心構えその２：先手を打つ
✓ 問題が起きるまえに対策を講じる
問題が起きてからどうするか？より、問題を起こさないためには
どうするか？（「ORMを使わない」というのはなしで ;）
✓ 開発初期から開発チームに助言する（特にN+1問題）
開発終盤になってから文句を言っても手遅れ

心構えその３：教えてあげる
✓ 開発者がSQLを知らないなら教えてあげる
一見面倒だが、それで重大なトラブルが減らせるなら安上がり
✓ 開発者からのSQLの相談に乗ってあげる
一見面倒だが、下手にORMだけでやられてトラブるより安上がり

心構えその４：心を広く持つ
✓ 最高速度を求めない
必要な速度が出ればそれでよしとする
✓ 最高品質を求めない
必要なサービス品質が提供できればよしとする

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

Q&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？あれはあんまり…

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

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

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

More Related Content

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

pFad - (p)hone/(F)rame/(a)nonymizer/(d)eclutterfier! Saves Data!