読者です 読者をやめる 読者になる 読者になる

「外部キーを張らない」設計への雑感

SQLAlchemy

僕はOracleRDBMSとかSQLを勉強した人間なので、絶対に外部キーを張り、可能であればチェック制約もかけて、絶対に不正なデータは入れさせたくないと思う人間なのだが、LAMPサーバーを並べてスケールさせるっていう今時のサイトでは、外部キーを張らない設計の方が主流らしい。・・・本当!?

確かに、アプリケーションやORMで頑張れば、外部キーを張るメリットが消え、外部キーを張るデメリットだけが残り、そしてMySQLRDBMSではなく、SQLをサポートする単なるストレージになるだろうが・・・

ちなみにSQLAlchemyならばテーブル定義から外部キーを消しても、mapperの定義で明示的に示してやれば、いままで通りのコードが動くはず。以下は「飼い主(Owner)と犬(Dog)の間に一対多の関係がある」という場合の例。

# -*- coding: utf-8 -*-
from sqlalchemy import *
from sqlalchemy.orm import *

MYSQL_ENGINE='InnoDB'

metadata = MetaData()

owner_table = Table(
    'owner', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', Unicode(50), nullable=False),
    mysql_engine=MYSQL_ENGINE)

dog_table = Table(
    'dog', metadata,
    Column('id', Integer, primary_key=True),
    Column('owner_id', Integer, nullable=False), # NO FK!!
    Column('name', Unicode(50), nullable=False),
    mysql_engine=MYSQL_ENGINE)

Index('owner_id', dog_table.c.owner_id)

engine = create_engine('mysql://root@127.0.0.1/test?charset=utf8&use_unicode=0')
metadata.bind = engine

metadata.create_all(checkfirst=True)

class Owner(object):
    def __init__(self, name):
        self.name = name

class Dog(object):
    def __init__(self, name):
        self.name = name

mapper(Dog, dog_table)
mapper(Owner, owner_table,
       properties = { 'dogs': relation(Dog,
                                       primaryjoin=dog_table.c.owner_id==owner_table.c.id,
                                       foreign_keys=[dog_table.c.owner_id]),
                      })

## 
Session = sessionmaker(bind=engine)
sess = Session()

## create new objects
try:
    owner = Owner(name=u'Kolya')
    dog = Dog(name=u'perezvon')
    owner.dogs.append(dog)
    sess.add(owner)
except:
    sess.rollback()
else:
    sess.commit()

## retrieve objects
owner = sess.query(Owner).first()
print "Owner", owner
print "Dogs", owner.dogs

さらに進んで、「飼い主テーブル(owner)」と「犬テーブル(dog)」を別のデーターベースに分割しなくてはならないとなった場合でも、以下のようにengineの定義を増やして、

engine1 = create_engine('mysql://root@127.0.0.1/ownerdb?charset=utf8&use_unicode=0')
engine2 = create_engine('mysql://root@127.0.0.1/dogdb?charset=utf8&use_unicode=0')

owner_table.create(bind=engine1, checkfirst=True)
dog_table.create(bind=engine2, checkfirst=True)

二相コミットをするようにSessionを設定すれば、トランザクションを必要とする既存のコードもそのまま動くのでアプリケーションへの影響は少ない。この辺りは実践では試したことがないので、SQLAlchemyのドキュメントの受け売り。

Session = sessionmaker(twophase=True)
Session.configure(binds={Owner: engine1, Dog: engine2})

sess = Session()

## create new objects
try:
    owner = Owner(name=u'Kolya')
    dog = Dog(name=u'perezvon')
    owner.dogs.append(dog)
    sess.add(owner)
except:
    sess.rollback()
else:
    sess.commit()

さらに大規模なサイトになれば、ここの例にあるように水平分割を行うのかなぁ。

もしmemcachedによるキャッシュが必要であれば、というか、ほとんどすべての環境でmemcached的なソリューションは必要になると思うけど、SQLAlchemyのSession, Engine, Queryクラス等を拡張してキャッシュを使わせるようにするのではなく(ORMを拡張してmemcacheを使うようにするのではなく)、ORMを使ってDBにアクセスする部分に一枚レイヤーをかぶせるのがいいのかなと思っている。

問題は、皆が大好きなPHPPropelとかDoctrineでも、当然同じようなことができるよね?ってことだ。