SQLAlchemy Relationship Loading 완전정복

Posted on by 솔천

안녕하세요. 이번 시간에는 SQLAlchemy의 relationship()에서 제공하는 loading옵션에 대해서 한번 자세히 파헤쳐 보려고 합니다. 이 글은 SQLAlchemy를 기본 이상으로 사용하고 계신분들을 위해 좀더 심도있는 내용을 다루고자 제작되었음을 미리 양해부탁드립니다.

자, 우리가 mapper에서 relationship을 정의하면요, 연관된 다른 테이블의 데이타 collections이나 scalar를 참조할수 있게 되는데요. 사실 두개의 테이블간의 연관 관계는요 두가지 방법으로 명시를 할수가 있습니다. 한가지는, 방금 말씀드린대로 mapper에서 relationship함수를 써서 relationship.lazy 파라메터를 이용하는 방법이 있고요, 다른 한가지는 Query object를 사용함으로써 관계를 명시할수도 있습니다.

자, 우리가 mapper에서 relationship을 정의하면요, 연관된 다른 테이블의 데이타 collections이나 scalar를 참조할수 있게 되는데요. 사실 두개의 테이블간의 연관 관계는요 두가지 방법으로 명시를 할수가 있습니다. 한가지는, 방금 말씀드린대로 mapper에서 relationship함수를 써서 relationship.lazy 파라메터를 이용하는 방법이 있고요, 다른 한가지는 Query object를 사용함으로써 관계를 명시할수도 있습니다.

Loading relationship의 종류는 세가지로 나뉘는데요, lazy로딩, eager로딩, 그리고 마지막으로 no로딩이 있습니다. 간단하게 설명을 드리자면요,

첫째, lazy로딩은 처음에 SELECT를 날릴때는 related 객체를 로딩하지 않은 상태로 결과값을 반환했다가 누군가 related 객체에 접근을 시도할때 추가로 SELECT를 날려서 데이타를 채운뒤에 해당 collection을 제공하는 식으로 로딩을 하는 방식이 바로 lazy 로딩입니다.
두번째, eager로딩은요 처음부터 아예 relationship에 명시된 애들을 전부다 쿼리해오는 방식입니다. Query가 실행될때 eager로딩이 설정되어있다 그러면, SELECT문을 확장해서 related객체를 가져오는데요 보통 JOIN을 통해서 다른 테이블의 연관된 레코드를 한번에 가져오거나, 아니면 메인 SELECT문 일단 날리고, 추가로 SELECT문을 또 날려서 필요한 데이타를 로딩해서 어쩄든 처음에 다 가져오는 방식입니다.
마지막으로, no로딩은요, 해당 relationship의 로딩을 아예 안하는거에요. 누군가 정의된 related객체에 접근을 하면 그 해당 객체는 비어있거나, 로딩이 안된상태 즉 여러분이 초기값을 먹여놓은 상태겠죠. 때론 해당 객체에 접근을 하려는 시도가 발생할시 에러를 raise하는 경우도 있습니다. 이것은 모두 원치 않는 lazy로딩을 보호하기 위한 것이에요. 아래에서 더욱 자세히 설명드리겠습니다.

relationship 로딩에서 자주 쓰이는 방식들을 옵션별로 간략하게 정리하자면 아래와 같습니다:

  • lazy loading – 이 방식은 lazy=select나 lazyload()옵션을 주었을때 사용되는 방식이구요, 이게 바로 누군가 해당 정보에 접근하고자 할때 그제서야 SELECT문을 날려서 정보를 충당하는 방식입니다. 그때그때 필요한 정보만 가져오기 때문에 미리 쿼리를 하지 않는 다는 특징이 있습니다.
  • joined loading – 이 방식은 lazy=joined나 joinedload()옵션을 주었을때 사용되는 방식이구요. 이 방식은 eager loading의 일종으로 Joined eager loading이라고 하고요. 이 방식은 메인 SELECT문에 relationship에 명시된 테이블을 JOIN하여 모든 테이블의 데이타들을 한꺼번에 가져오는 방식으로 related정보들이 메인테이블과 같은 결과 set에 포함되어 로딩이 됩니다.
  • subquery loading – 이 방식은 lazy=subquery나 subqueryload()옵션을 주었을때 사용되어지는 방식이구요. 마찬가지로 초반에 쿼리를 날리는 형태라서 eager loading의 일종으로 Subquery eager loading이라고 부릅니다. 그냥 joined loading과 다른점은 SELECT문을 재작성할때, 테이블을 바로 join하지 않고, related table을 subquery로 SELECT문을 작성하여 해당 subquery를 메인 table과 조인하는 방식입니다. 그렇게 하면 subquery를 통해서 related table의 데이타가 먼저 로딩을 하게 되고, 후에 main table과 join함으로써 데이타를 추려내는 방식입니다.
  • select IN loading – 이 방식은 lazy=selectin이나 selectinload()옵션을 주었을때 사용되는 방식이구요. 두번째 SELECT문, 즉 related table을 쿼리하는 SELECT문에 parent table의 primary key를 IN 조건문에 추가하는 방식으로, 테이블을 통째로 들었다놨다 하는게 아니라 데이타베이스에서 가져올때 primary key로 간추려서 selectively가져오는 방식입니다.
  • raise loading – 이 방식은 lazy=raise나 lazy=raise_on_sql 혹은 raiseload()옵션을 주었을때 사용되는 방식이구요. lazy load에 대한 요청이 왔을때 Error를 raise해서 원치 않는 lazy loading을 막아줍니다.
  • no loading – 이 방식은 lazy=noload나 noload()옵션을 주었을때 사용되어지는 방식이구요. 이 로딩방식은 related된 객체를 비워놓고 그냥 비워둔채로 놔두고 절대로 loading을 하지 않는것입니다. 이 방식은 loading옵션중에서 많이 사용되어지는 방식은 아닙니다.

Mapping시 Loader설정 전략

어떤 특정 relationship을 설정하기 위한 loader 전략이 세워졌다면 테이블들을 mapping할때 이것들을 적용해야해요. 이 설정은 relationship()함수의 lazy인자를 통해서 이루어 집니다. 아까 말씀드린대로 lazy인자에 값으로 들어갈수 있는 애들은 select, joined, subquery, selectin등이 있습니다.

예를 들어보면요, 위에서 언급한 joined eager loading을 한번 parent object에 설정해볼게요.

 class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer, primary_key=True)
    children = relationship("Child", lazy='joined'

위의 코드는 언제든지 Parent object가 loading될때 Children collection이 추가적으로 로딩 될것입니다. 이것은 추가된 JOIN쿼리에 의해서 Parent object에 Children row가 fetch되었기 때문이죠.
lazy인자의 기본값은 select입니다. 처음엔 부르지 않다가, 나중에 related 객체에 접근이 일어나면 loading이 이루어지는 방식말이죠.

Loader Option을 사용한 Relationship Loading

Mapping당시 Lazy인자를 사용하는것 이외에 loading설정을 하는 방법이 있는데요. 바로 loader option, 즉 Query.options()를 사용하는 방법입니다. 아마도 이 방법이 mapper에서 설정하는 방법보다 더 흔하게 사용되고 있을지도 모를것 같기도 한데요. Loader option을 이용하면 보다 정교하게 설정이 가능해 집니다. 가장 흔하게 쓰이는 loader option으로는 joinedload(), subqueryload(), selectinload(), 그리고 lazyload()가 있습니다. 위의 함수들에 related객체를 연결하는 방법은 두가지, parent객체 안에 선언된 child객체의 이름을 string으로 그냥 보내도 되구요, 아니면 child node객체 자체를 보내도 됩니다. 몇가지 예를 들어볼까요?

# parent객체에 child node의 이름을 넘겨서 lazy load할때는 이렇게
session.query(Parent).options(lazyload('children')).all()

# parent객체에 선언한 child node 자체를 넘겨줄수도 있습니다.
session.query(Parent).options(lazyload(Parent.children)).all()

# 다른 함수도 마찬가지로 이렇게
session.query(Parent).options(joinedload('children')).all()

Loader option들은 쭉 연결해서 여러번 호출할 수도 있는데, 이걸 method chaining이라고 합니다. 아래의 코드를 보시면 Parent에 children은 JOIN으로 묶어서 함께 호출되고, 두번째 loader option은 자세히 보시면 Child니까 children의 item이 되겠죠? 이때, 그 JOIN된 쿼리에 subelements를 함께 불러올 subquery가 추가적으로 만들어진다는 겁니다.

session.query(Parent).options(
    joinedload(Parent.children).
    subqueryload(Child.subelements)).all()

이런 chaning형태는 lazy loding에서도 사용할수가 있는데요. 아래 코드를 보시면요. Join대신에 lazyload를 썼는데요. 그 밑에 또 subquery loading을 하도록 설정했어요. 이 경우에는 Parent의 children collection에 가장 처음 access를 했을때 일단 childred collection을 가져오는 쿼리를 날리는데요, 이때 해당 쿼리에 subquery를 붙여서 subelements도 함께 가져오라는 뜻입니다. 막연하게 subquery는 eager loading이니까 Parent를 쿼리해올때 SELECT문에 추가적으로 붙여서 갖고 오겠지 하고 추측하시면 안된다는 거죠.

session.query(Parent).options(
    lazyload(Parent.children).
    subqueryload(Child.subelements)).all()

method chaining에서 각 단계의 loader style은 함수명을 통해 확실하게 명시가 되어집니다. 그런데 어떤 특정한 arrtibute의 기존 loader style을 변경하지 않고 각 chain의 구조만 정의하고 싶다면 그때는, defaultload()를 사용할수 있습니다.

session.query(A).options(
    defaultload(A.atob).
    joinedload(B.btoc)).all()

이와 비슷하게 chain의 구조를 좀더 여러개의 depth로 만들고 싶을때, 물론 loader option을 여러번 정의하는 방법도 있지만 아래 코드와 같이 defaultload()를 사용해서 한번에 sub option을 정의할수 있습니다. 

session.query(A).options(
    defaultload(A.atob).options(
      joinedload(B.btoc),
      joinedload(B.btod)
    )).all()

참고로, lazyload()로 한번 로딩된 collection은 해당 데이타가 memory에 존재하는한 계속 접근해서 사용할수 있지만요, query가 expired된경우, 즉 Session이 commit이나 rolled back된 경우나, 혹은 Session.expire_all()함수를 호출해서 임의로 모든 session들을 expire시켜버린 경우에는 방금 보여드렸던 아래의 query의 경우 누군가 Parent.children에 접근을 시도하면 lazyload()가 다시 실행되고, 마찬가지로 이때 sub-query로 다시 loading이 됩니다. 

session.query(Parent).options(
    lazyload(Parent.children).
    subqueryload(Child.subelements)).all()

이때, 나는 children이 호출될때 subelements가 자동으로 끌려나오는게 싫다 하시는분은 Parent를 로딩한 이후에 loader option을 변경할수가 있습니다. 아래와 같이 populate_existing()을 사용하면 이미 로딩된 객체를 다시 로딩하지 않고도 설정을 변경할수가 있어요.

# change the options on Parent objects that were already loaded
session.query(Parent).populate_existing().options(
    lazyload(Parent.children).
    lazyload(Child.subelements)).all()

이렇게 해주면, garbage collection이 임의로 Session을 clear하거나 Session.expunge_all()등을 실행해서 Session이 없어진 경우에 기존의 객체는 사라지고 새로운 객체를 만들어지기 때문에 다시 정의한 loader option이 적용되게 됩니다.

앞으로 SQLAlchemy는 이미 로딩된 객체의 loader option을 변경하는데 필요한 더 많은 기능을 추가적으로 출시할 계획이에요.

Lazy Loading

기본적으로 내부 객체의 relationship들은 lazy loading입니다. 위에서도 말씀드렸다시피 lazy loading은 해당 attribute에 접근이 시도될때 필요한 collection이나 scalar가 relationship()을 통해서 연계가 되는거죠. At the point of access에 related object나 object들을 연계시키기 위해서 일반적으로 SQL문을 날리게 되는것이에요.

>>> jack.addresses
SELECT
    addresses.id AS addresses_id,
    addresses.email_address AS addresses_email_address,
    addresses.user_id AS addresses_user_id
FROM addresses
WHERE ? = addresses.user_id
[5]
[<Address(u'jack@google.com')>, <Address(u'j25@yahoo.com')>

SQL문을 날리지 않는 경우가 한가지 있는데 바로 부모와 자식테이블의 관계가 many-to-one 일경우 입니다. 이미 related 정보가 현재 Session에 불려와 있을때는 다시 쿼리를 실행하지 않고 있는 데이터를 활용하는 것이지요. 그런 이유에서 lazy loading이 때로는 너무 여러번의 쿼리를 날리는 바람에 자원이 낭비되는가 싶다가도, 하나의 related 데이타가 여러 다른 object들에 의해 호출이 될때는 새로 호출을 안하고 로컬에 있는 데이터를 사용하기 때문에 살짝 절약이 될수도 있다는 거죠.

이렇게 attribute에 접근을 할때 loading을 하는 방식을 lazy또는 select로딩이라고하는데 select라는 단어의 어원은 객체에 접근이 있을때 SELECT문을 날린다 그래서 select라고 합니다.
Lazy loading은 lazyloading() loader option을 통해서도 query에 직접 사용할수 있습니다. 

from sqlalchemy.orm import lazyload
session.query(User).options(lazyload(User.addresses))

Raiseload를 이용해서 원치 않는 lazy loading막기

lazyloading()의 가장큰 폐해는 바로 N + 1이슈인데요. 이게 뭐냐면요 예를들어 related 된 객체의 개수가 N개라고 할때 해당 객체를 모두 읽어서 결과를 보여주고 싶은경우에 각 객체별로 N번의 추가 쿼리를 날려야하기 때문에 parent객체를 위한 쿼리까지 더해서 총 N + 1개의 쿼리를 날리게 된다는 말이죠. SQLAlchemy에서 이 문제를 해결하기 위해 사용하는 방법은 eager load를 사용해서 초반에 한꺼번에 가져오도록 쿼리를 변경하는거에요. 여기서 또다른 문제가 생기는데 어떤 attribute들이 eager loading에 의해 로딩이 안되었을때, 접근을 시도하면 lazy loading이 발생할수 있는데 때로는 데이타가 없으면 없었지, 차후 loading자체를 허용하기 싫은 경우가 있을수 있잖아요. 그럴때 바로 raiseload()를 사용해서 차후 loading을 아래와 같이 차단할수 있어요.

from sqlalchemy.orm import raiseload
session.query(User).options(raiseload(User.addresses))

위의 코드가 실행된 후에는 .addresses에 접근했을때 쿼리를 날리기는 커녕, 오히려 ORM Exception error를 raise하게 됩니다.

raiseload()의 인자로 wildcard를 사용하면 해당 parent에 설정된 모든 relationship에 대해 raiseload()를 적용하라는 말이 됩니다. 

session.query(Order).options(
    joinedload(Order.items), raiseload('*'))

위의 코드는 처음에 한번 eager loading을 수행하고, 그 이후로 부터는 모든 relationship에 대해 Exception error를 raise하게 됩니다. 그런데 여기서 주의하셔야하실 부분은 raiseload('*')가 Order쿼리 안에서 사용되었음에도 불구하고, raiseload('*')는 Order객체에만 국한 된것이 아니라 모든 relationship에 적용이 된다는거에요. 굳이 Order객체안의 relationship만 제한하고 싶다면 반드시 Full path로 아래와 같이 명시해주셔야해요. 

from sqlalchemy.orm import Load

session.query(Order).options(
    joinedload(Order.items), Load(Order).raiseload('*'))

그리고 부득이 Order객체 안에 있는 여러개 relationship중에 단하나 items만 차후 로딩을 제한하고 싶으시다면, 아래와 같이 .으로 표기해서 적용되는 scope을 좁히실수도 있어요.

session.query(Order).options(
    joinedload(Order.items).raiseload('*'))

Joined Eager Loading

joined eager로딩은 eager loading중에 가장 기본적인 방식입니다. SELECT문에 JOIN을 추가해서 쿼리를 날리는데 이때 별다른 설정이 없다면 LEFT OUTER JOIN으로 join하게 됩니다. Query()가 실행될때 Parent obejct와 함께 related collection이나 scalar가 같은 result set에 저장되는 형태죠.
Mapping시 설정하시려면 다음과 같습니다:

class Address(Base):
    # ...

    user = relationship(User, lazy="joined")

joined eager로딩은 loading옵션의 기본값은 아니고, 보통 쿼리에 추가 옵션으로 적용이 되는데요, 특히 Parent객체와 추가로 불러오는 데이타와의 관계가 Many-to-one이 아니고, one-to-many인경우에 많이 쓰입니다. 코드는 joinedload()옵션을 줌으로써 아래와 같이 구현할수 있습니다.

>>> jack = session.query(User).\
... options(joinedload(User.addresses)).\
... filter_by(name='jack').all()
SELECT
    addresses_1.id AS addresses_1_id,
    addresses_1.email_address AS addresses_1_email_address,
    addresses_1.user_id AS addresses_1_user_id,
    users.id AS users_id, users.name AS users_name,
    users.fullname AS users_fullname,
    users.nickname AS users_nickname
FROM users
LEFT OUTER JOIN addresses AS addresses_1
    ON users.id = addresses_1.user_id
WHERE users.name = ?
['jack']

JOIN은 기본적으로 LEFT OUTER JOIN이기 때문에 related row가 없는 경우에도 lead object는 result set에 포함이 됩니다. 만약 이거를 허용하고 싶지 않다, 나는 releted row가 없는 lead object는 결과에 포함시키고 싶지 않다 하시는 분은 relationship.innerjoin플래그를 사용하셔서 related row가 있는 데이타만 가져오도록 쿼리를 변형할수 있습니다.

class Address(Base):
    # ...

    user_id = Column(ForeignKey('users.id'), nullable=False)
    user = relationship(User, lazy="joined", innerjoin=True)

그리고 query option에서 쓰고자할때는 joinedload.innerjoin플래그를 사용하시면 됩니다.

session.query(Address).options(
    joinedload(Address.user, innerjoin=True))

chain에 outer join과 inner join이 함께 쓰인경우에는 inner join이 오른쪽에 nested형태로 아래 쿼리와 같이 만들집니다.

>>> session.query(User).options(
...     joinedload(User.addresses).
...     joinedload(Address.widgets, innerjoin=True)).all()
SELECT
    widgets_1.id AS widgets_1_id,
    widgets_1.name AS widgets_1_name,
    addresses_1.id AS addresses_1_id,
    addresses_1.email_address AS addresses_1_email_address,
    addresses_1.user_id AS addresses_1_user_id,
    users.id AS users_id, users.name AS users_name,
    users.fullname AS users_fullname,
    users.nickname AS users_nickname
FROM users
LEFT OUTER JOIN (
    addresses AS addresses_1 JOIN widgets AS widgets_1 ON
    addresses_1.widget_id = widgets_1.id
) ON users.id = addresses_1.user_id

JOIN문에서 INNER는 기본값이기 때문에 생략된거에요. 옛날 버젼의 SQLite나 SQLAlchemy에서는 오른쪽의 nested join을 subquery로 바꾸도록 설계했었으니까 참고하세요.

Joined eager loading and result set batching

joined eager loading에서 가장 중점을 두어야하는 부분은 바로 leading entry의 결과가 중첩되지 않아야 한다는 것입니다. 예를 들어 위의 예시에서 User가 3개의 Address를 가지고 있다면 조인했을때 User는 3개의 row에 중첩된 채로 반환이 될것입니다. User데이타가 전에 받은거랑 똑같은게 나왔을때, Address object에 해당하는 칼럼의 데이타로 구성된 객체가 User.addresses에 추가적으로 들어가게 됩니다.

이 과정은 매우 투명하죠, 그런데 여기서 궁금증이 한가지 생깁니다. 과연 joined eager loading은 “batched” query의 결과와 양립할수 있을까요? Collection을 로딩할때 batched 쿼리는 Query.yield_per()함수를 통해서 제공됩니다. lead object와 related object가 1:1관계라면 문제없을거에요 하지만 Query.yield_per()이 collection based joined eager loader에서 실행되면는 Exception을 throw할겁니다.

collection-based joined eager loading을 할때 큰 result set을 가지는 “batch”쿼리는 다중의 SELECT문을 날리는데 이때 row를 묶어서 추가로 날리는 쿼리의 WHERE문을 조작해서 subset을 가져오도록합니다. 그렇게 실제 날리는 쿼리의 숫자를 줄이도록 하는 것이지요.
이를 보완하기 위해서 “select IN” eager loading을 사용할것을 고려해보세요. 얘는 잠재적으로 Query.yield_per()와 양립할수 있거든요. 왜냐면 DB driver가 multiple, simultaneous쿼리를 제공하기 때문인데요, 단 SQLite, PostgreSQL은 지원을 하는데 MySQL이나 SQL Server ODBC driver는 지원을 안한다는 점 알아두세요.

The Zen of Joined Eager Loading

지금까지의 이야기를 들어보면 joined eager loading이 Query.join()이랑 참 많이 닮은것 같죠? 근데 이 두가지가 굉장히 헷갈릴수가 있어요. 이 두가지 기능의 차이점을 분명히 아셔야하는데요. 그 차이점은 Query.join()은 퀴리결과를 갱신할때 사용되는 것이고, joinedload()는 lead object의 결과값은 결코 건드리지 않고 오직 related object를 가져오는데만 사용되는 join이라는거죠. loader 전략의 기본 철학은 어떤 종류의 scheme든 추가로 로딩할수 있지만, 결과값은 절대 바꾸지 않는다에요. 오직 데이타베이스에 날리는 SQL쿼리문의 개수에는 변동이 있을수 있지요. related object를 추가로 가져와야하니까요. 처음에는 일단 all lazy load로 관계를 설정하세요 그리고 추후에 쿼리를 보고 어떻게 하면 보다 효과적일지를 고민하시면 됩니다. Loader전략은 쿼리를 변경하지 않고도 이루어 질수 있어요. 결과값은 동일할거란 말이죠. 하지만 전략적으로 개선을 해서 더 적은 숫자의 SQL문을 날린다면 더 효율적인 시스템이 될거에요. 이론적으로, loader 전략을 변경하더라고 사실상 여러분들이 Query에 직접적으로 전혀 다른 primary result set이나 related objects를 가져오게 만드는건 못할거에요.

joinedload()가 어떻게 entity row에 영향을 받지 않은 동일한 결과를 그 어떤 경우에도 변함없이 반환하는지는 바로 내부적으로 임의의 join에 대한 alias를 생성한뒤에 그것을 당신의 쿼리에 추가하기 때문에 그 쿼리가 기본쿼리의 어떤 부분에서도 참조될수 없도록 원천봉쇄를 하기 때문입니다. 예를 들면, 아래의 query는 users의 addesses로부터 LEFT OUTER JOIN을 하기위해 joinedload()를 사용합니다. 그런데 여기서 Address.email_address로 ORDER BY를 하라고 추가하는것은 불가능합니다. 왜냐하면 Address라는 entiry는 쿼리안에 명시되어 있지 않기 때문입니다. 

... options(joinedload(User.addresses)).\
... filter(User.name=='jack').\
... order_by(Address.email_address).all()
SELECT
    addresses_1.id AS addresses_1_id,
    addresses_1.email_address AS addresses_1_email_address,
    addresses_1.user_id AS addresses_1_user_id,
    users.id AS users_id,
    users.name AS users_name,
    users.fullname AS users_fullname,
    users.nickname AS users_nickname
FROM users
LEFT OUTER JOIN addresses AS addresses_1
    ON users.id = addresses_1.user_id
WHERE users.name = ?
ORDER BY addresses.email_address   <-- this part is wrong !

위의 쿼리에 addresses라는 테이블은 FROM문에 없죠? 그런데 그냥 사용하는것은 오류죠. 진심으로 addresses.email_address로 ORDER BY를 하고 싶으시다면 Query.join()에 addresses테이블을 추가하신후에 아래와 같이 쓰실수 있으세요.

... join(User.addresses).\
... filter(User.name=='jack').\
... order_by(Address.email_address).all()

SELECT
    users.id AS users_id,
    users.name AS users_name,
    users.fullname AS users_fullname,
    users.nickname AS users_nickname
FROM users
JOIN addresses ON users.id = addresses.user_id
WHERE users.name = ?
ORDER BY addresses.email_address
['jack']

위의 쿼리결과는 이제 더이상 첫번째 쿼리와 같지 않을거에요. 그럼 여기에다 joinedload()를 한번 추가해 볼까요? 그래서 쿼리에 두개의 join이 하나는 ordering을 위해 하나는 related rows를 가져오기 위해 존재하도록 말이죠.

>>> jack = session.query(User).\
... join(User.addresses).\
... options(joinedload(User.addresses)).\
... filter(User.name=='jack').\
... order_by(Address.email_address).all()
SELECT
    addresses_1.id AS addresses_1_id,
    addresses_1.email_address AS addresses_1_email_address,
    addresses_1.user_id AS addresses_1_user_id,
    users.id AS users_id, users.name AS users_name,
    users.fullname AS users_fullname,
    users.nickname AS users_nickname
FROM users JOIN addresses
    ON users.id = addresses.user_id
LEFT OUTER JOIN addresses AS addresses_1
    ON users.id = addresses_1.user_id
WHERE users.name = ?
ORDER BY addresses.email_address
['jack']

위의 쿼리에서 보시면 이제 Query.join()은 JOIN을 만들어서 이후에 필요한 작업에 사용되도록 했구요, joinedload()는 오직 쿼리결과의 각 User에 User.addresses collections을 가져오는데만 쓰이게 됩니다. 그런데 왠지, 쿼리가 중복된다는 느낌을 지울수가 없네요. 사실 중복되고 있는게 맞죠. 만약 우리가 단 하나의 JOIN으로 collection도 로딩을 하고, ordering도 할수 있다면 참 좋겠는데 말이죠. 방법이 있어요 바로 contains_eager()로딩을 사용하면 됩니다. contains_eager()에 대해서는 아래에서 더 자세히 설명 드릴게요. joinedload()가 왜 이렇게 써야하는지를 설명하기 위해서 Address를 가지고 filtering을 한번 해볼게요.

>>> jack = session.query(User).\
... join(User.addresses).\
... options(joinedload(User.addresses)).\
... filter(User.name=='jack').\
... filter(Address.email_address=='someaddress@foo.com').\
... all()
SELECT
    addresses_1.id AS addresses_1_id,
    addresses_1.email_address AS addresses_1_email_address,
    addresses_1.user_id AS addresses_1_user_id,
    users.id AS users_id, users.name AS users_name,
    users.fullname AS users_fullname,
    users.nickname AS users_nickname
FROM users JOIN addresses
    ON users.id = addresses.user_id
LEFT OUTER JOIN addresses AS addresses_1
    ON users.id = addresses_1.user_id
WHERE users.name = ? AND addresses.email_address = ?
['jack', 'someaddress@foo.com']

위에서 보시다시피, 두개의 JOIN은 매우 다른 역할을 하고 있어요. Query.join() 즉, INNER JOIN으로 추가된 addresses는 정확하게 하나의 row를 반환할것이고, joinedload(), 즉 OUTER JOIN으로 추가된 addresses는 여전히 해당 사용자의 email address 3개 모두를 가져오는데 사용될 것입니다.

joinedload()을 다른 loading 스타일로 변경함으로 인해서, collection을 완전히 독립적으로 따로 쿼리를 해서 불러오도록 변형 시킬수 있습니다. 아래의 코드는 joinedload()를 subqueryload()로 바꿔서 구현한 쿼리입니다.

... join(User.addresses).\
... options(subqueryload(User.addresses)).\
... filter(User.name=='jack').\
... filter(Address.email_address=='someaddress@foo.com').\
... all()
SELECT
    users.id AS users_id,
    users.name AS users_name,
    users.fullname AS users_fullname,
    users.nickname AS users_nickname
FROM users
JOIN addresses ON users.id = addresses.user_id
WHERE
    users.name = ?
    AND addresses.email_address = ?
['jack', 'someaddress@foo.com']

# ... subqueryload() emits a SELECT in order
# to load all address records ...

joined eager loading을 이용했을때 쿼리안에 쿼리결과에 영향을 주는 modifier(DISTINCT, LIMIT, OFFSET…)가 있으면, 메인쿼리를 subquery로 감싸고, 그 subquery에 related 객체를 가져오는데 필요한 JOIN을 추가하도록합니다. SQLAlchemy는 loader option들이 최종결과에 어떠한 영향도 미치지 못하도록 하기위해 신중에 신중을 기하여 어떤 형태의 query라도 반드시 오직 related objects만을 로딩되어지도록 만반의 태세를 갖추고 있습니다.

Subquery Eager Loading

Subquery eager loading은 joined eager load와 같은 방법으로 설정이 됩니다. 물론 relationship.lazy에는 “joined”대신 “subquery”가 들어가야겠죠. 그리고 Query에서 option으로 사용될때는 joinedload()옵션 대신에 subqueryload()옵션을 사용하셔야 하구요.

Subquery eager loading은요, lead쿼리를 날린후에 모든 relationship에 대해서 새로운 SELECT쿼리를 추가적으로 날려서 collection을 받아다 결과객체에 추가하는데요, 이때 날리는 SELECT문 안에 original SELECT문이 subquery로 들어가게됩니다. Lead SELECT문에 날렸던 조건과 동일한 조건으로 SELECT문을 만든후에 칼럼은 primary key만 가져오도록해서 해당 subquery를 related table과 Join을 해서 related rows를 가져오는 방식이죠.

>>> jack = session.query(User).\
... options(subqueryload(User.addresses)).\
... filter_by(name='jack').all()
SELECT
    users.id AS users_id,
    users.name AS users_name,
    users.fullname AS users_fullname,
    users.nickname AS users_nickname
FROM users
WHERE users.name = ?
('jack',)
SELECT
    addresses.id AS addresses_id,
    addresses.email_address AS addresses_email_address,
    addresses.user_id AS addresses_user_id,
    anon_1.users_id AS anon_1_users_id
FROM (
    SELECT users.id AS users_id
    FROM users
    WHERE users.name = ?) AS anon_1
JOIN addresses ON anon_1.users_id = addresses.user_id
ORDER BY anon_1.users_id, addresses.id
('jack',)

subqueryload전략은 collection을 로딩할때 joined eager loading과 비교하여 이점이 매우 많습니다. 첫째, original 쿼리를 전혀 건드리지않고 그대로 날린다는 것인데, LEFT OUTER JOIN을 하면 쿼리 효율이 살짝 떨어진다는 점에서 효과적이고, 둘째, related table이 많을때 하나의 쿼리에서 처리해야한다면 해당 쿼리가 너무 비대해져서 마찬가지로 효율이 떨어지게 됩니다. 마지막으로, 추가적으로 실행되는 쿼리는 오직 collection에 item을 채우기 위한 것이므로, 이경우 INNER JOIN을 날리는게 효율적이기 때문입니다.

subqueryload 를 이용할때 단점은 original query가 복잡한 경우에 그 복잡한 쿼리의 조건문을 related item을 가져오는 쿼리에 그대로 사용해야한다는 점입니다. 때때로 이런 복잡한 쿼리는 어떤 경우에 (보통 MySQL) 쿼리 엄청나게 느려지는 상황이 생기기도 합니다. 추가로 subqueryload전략은 오직 한번에 모든 collection들을 load하기 때문에 Query.yield_per()를 이용한 “batched” loading과 양립할수 없다는 단점이 있습니다.

이러한 subqueryload()의 단점을 보완하기 위하여 새롭게 만들어진 loading style이 바로 selectinload() 입니다.

The Importance of Ordering

subqueryload()를 연결방식으로 사용하는 Query문에서 Query.first(), Query.limit(), 혹은 Query.offset()과 같은 limiting modifier를 사용하는경우에는 반드시 unique column으로 정렬을 하도록 Query.order_by()를 함께 사용해야합니다. 그래야 subqueryload()에 의해 쿼리를 날릴때, parent query와 동일한 정렬기준을 사용할수 있게 되니까요. 그렇지 않으면, subqueryload()로 실행한 쿼리가 잘못된 결과를 가져오는 사태가 발생할수도 있습니다. 

# 틀렸음, ORDER BY를 반드시 사용해야함
session.query(User).options(
    subqueryload(User.addresses)).first()

# 틀렸음, User.name은 고유하지 않을수 있음
session.query(User).options(
    subqueryload(User.addresses)
).order_by(User.name).first()

# 맞았음. 반드시 고유한 ID로 order by를 해야함.
session.query(User).options(
    subqueryload(User.addresses)
).order_by(User.name, User.id).first()

Select IN loading

select IN loading은 subquery eager loading과 매우 흡사하지만, 두번째 SELECT문이 보다 간결해 진다는 점에서 subquery eager loading과 구분이 됩니다. 추가적으로 select IN loading은 load result를 그룹으로 뭉뚱그려서 한번에 쿼리하기도 합니다. joined나 subquery eager loading은 Query.yield_per()를 사용한 batch쿼리와 양립할수 없는데 반해 select IN loading은 batch쿼리와도 양립이 가능하고, database driver에 동시 다발적으로 쿼리를 날릴수 있는 기능도 제공을 합니다.

종합적으로, 특히 SQLAlchemy의 1.3시리즈에서는 selectin loadin이 대부분의 경우 collection을 로딩하는데 가장 심플하고, 효율적인 eagerly load라고 할수 있습니다. 단, selectin eager loading이 불가능한 경우가 한가지 있는데, 바로 primary key가 여러개의 column들을 조합해서 만든경우에 backend database가 IN 조건문으로 tuple을 지원하지 않는 경우입니다. 지원하지 않는 데이타베이스로는 SQLite, Oracle 그리고 SQL Server가 있습니다.

Select IN eager loading은 mapping단계에서 relationship.lazy에 selectin를 값으로 주어 설정하거나, Query에 selectinload()옵션을 주어서 제공되어집니다. 이 방식은 이미 가져온 parent object에서 가져온 primary key들을 두번째 SELECT문의 조건문으로 사용하는 방식입니다. This also takes place for a non-collection, many-to-one load where the related column values are not loaded on the parent objects and would otherwise need to be loaded:

>>> jack = session.query(User).\
... options(selectinload('addresses')).\
... filter(or_(User.name == 'jack', User.name == 'ed')).all()
SELECT
    users.id AS users_id,
    users.name AS users_name,
    users.fullname AS users_fullname,
    users.nickname AS users_nickname
FROM users
WHERE users.name = ? OR users.name = ?
('jack', 'ed')
SELECT
    addresses.id AS addresses_id,
    addresses.email_address AS addresses_email_address,
    addresses.user_id AS addresses_user_id
FROM addresses
WHERE addresses.user_id IN (?, ?)
ORDER BY addresses.user_id, addresses.id
(5, 7)

위의 예제에서 addresses.user_id IN (5, 7)과 같이 이미 로딩된 User objects에서 primary key를 획득하여 두번째 SELECT쿼리의 조건으로 사용합니다. 다시 말해, parent objects의 batch가 완전히 로딩된 상태에서 그 안의 primary key값들을 가져다가 두번째 SELECT문의 IN안에 삽입하는거죠. 왜냐하면 위의 두 객체 User와 Address는 User의 primary key가 Address.user_id를 이끌어내는 관계에 있기 때문이죠. 그래서 두번째 SELECT문에서는 어떤 join이나 subquery도 필요하지 않고 primary key만 가지고 direct로 추가정보를 가져올수 있습니다.

Parent object에 primary key가 명시되어있지 않은 경우에 selectinload()를 사용하면, 그때는 selectin loading이 첫번째 SELECT문에 parent table을 추가로 join하여, primary key를 획득하도록 조작합니다. This also takes place for a non-collection, many-to-one load where the related column values are not loaded on the parent objects and would otherwise need to be loaded:

>>> session.query(Address).\
... options(selectinload('user')).all()
SELECT
    addresses.id AS addresses_id,
    addresses.email_address AS addresses_email_address,
    addresses.user_id AS addresses_user_id
    FROM addresses
SELECT
    addresses_1.id AS addresses_1_id,
    users.id AS users_id,
    users.name AS users_name,
    users.fullname AS users_fullname,
    users.nickname AS users_nickname
FROM addresses AS addresses_1
JOIN users ON users.id = addresses_1.user_id
WHERE addresses_1.id IN (?, ?)
ORDER BY addresses_1.id
(1, 2)

“Select IN” loading은 eager loading중에서도 가장 최근에 추가된 기능입니다. 아래는 알아두면 좋은 정보입니다.:

  • selectin loader를 통해 실행된 SELECT문은 subquery와 달리 subquery를 필요로 하지도 않고, original query에서 그 어떤 performance에 제약을 주는 조건문도 넣어줄 필요가 없습니다. 단순히 primary key를 통한 검색이기때문에 매우 높을 성능을 보입니다.
  • subqueryload에서는 필수적이었던 고유한 키로 정렬을 해야한다는 조건도 selectinload에서는 명시할 필요가 없습니다. selectin은 오직 primary key만 가지고 관계를 link하기 때문에 잘못된 결과를 가져올 가능성이 전혀 없습니다.
  • selectin load는 join이나 subquery와는 달리 parent objects가 로딩이 되자마자 바로 두번째 SELECT문을 날립니다. 그래서 eager loading이 여러개의 depth로 복잡하게 구성이 되어있다해도, selectin loading은 여전히 JOIN은 최대 한번만 이용하고 보통은 JOIN없이 여러번 쿼리를 해옵니다. joined이나 subquery eager loading과 비교하면 얘네들은 엄청나게 많은 다중 JOIN을 쿼리에 추가해서 가져오죠. 물론 original parent에 필요한 경우에 말이죠.
  • “selectin” loading은 original쿼리가 아무리 복잡해도 그와는 별개로 언제나 SELECT문이 예상가능한 심플한 형태입니다. 새로운 기능인 ColumnOperators.in_()을 이용하여, cached query를 허용하도록 할수도 있고, selectin loader는 한번 쿼리한 SQL을 캐시에 저장하기 위하여 sqlalchemy.ext.baked 확장자를 이용하기도 합니다. 그리고 저장된 캐시를 이용함으로 인해서 외부로 요청하는 요청횟수를 줄여줍니다.
  • 이 strategy는 한번에 최대 500개의 parent primary key를 두번째 쿼리에 명시하여 정보를 가져올수 있습니다. Oracle같은 데이타베이스는 IN 조건문에 넣을 수 있는 값의 제약이 많아요. 그리고 전체 SQL의 길이에도 제약이 있을수 있으니 값을 너무 많이 넣지는 않는게 좋기때문에 selectin loading은 넘길수 있는 최대 primary key의 값을 500개로 나눠서 여러번 쿼리를 함으로써 추가 정보를 획득합니다. These SELECT statements emit with minimal Python overhead due to the “baked” queries and also minimal SQL overhead as they query against primary key directly.
  • selectin loading은 eager loading입니다. 그래서 Query.yield_per()로 제공되는 batch와 함께 쓰일수 있어요. 그리고 데이타베이스 driver가 지원한다면 동시다발적으로 쿼리를 날릴수 있지요. 현재 simultaneous cursors를 지원하는 데이타베이스 driver는 SQLite, ProstgreSQL입니다. MySQL의 driver인 mysqlclient나 pymysql은 현재 simultaneous cursors를 지원하지 않고 있어요. 마찬가지로 SQL Server의 ODBC도 동시쿼리를 지원하지 않습니다.
  • selectin loading 은 primary key가 다중 컬럼으로 이루어 진 경우 IN조건문에서 tuple 데이타 타입을 허용하지 않는 데이타베이스라면 사용할수 없습니다. tuple이라 함은 WHERE (table.column_a, table.column_b) IN ((?, ?), (?, ?), (?, ?))와 같은 값의 그룹을 이야기하는데, 이 문법은 PostgreSQL, MySQL, SQLite의 최신버젼에서 지원이 되고 있습니다. SQLAlchemy는 여러분의 데이타베이스가 해당 기능을 지원하는지 검사하는 기능은 하지 않습니다. tuple을 포함한 쿼리를 날렸을때 에러를 반환한다면 지원하지 않는것으로 간주하시면 됩니다. SQLAlchemy는 데이타베이스가 tuple을 지원하든 안하든 안되는거만 쏙 빼고 문제없이 일을 처리할것입니다.

일반적으로 selectin loading은 대부분의 경우에서 subquery eager loading보다 우수한 성능을 보여줄 것입니다. 다시한번 말하지만, composite primary key로는 안되는 데이타베이스가 있다는것, 두번째 parent의 result set이 굉장히 큰 경우에 SELECT쿼리가 여러번 발생될수 있다는점. 그리고 언제나처럼, 개발자들은 쿼리문이 효율적으로 돌아가고 있는지를 확인하는데 시간을 할애하기를 바랍니다.

What Kind of Loading to Use ?

과연 어떤 loading타입을 이용하여 데이타베이스에 날리는 “Number of SQL execution”과 “complexity of SQL”, 그리고 “fetch해온 data의 amount” 간에 장점들을 tradeoff를 해서 optimizing을 해야할까요? 여기 두개의 예제가 있습니다. 하나는 collection을 가져오는 relationship()이고, 하나는 scalar를 가져오는데 parent테이블과의 관계는 many-to-one reference로 연결이 되어있습니다.

One to Many Collection

  • lazy loading을 이용하면, 당신이 만약 100개의 객체를 로딩해왔고 그후 각각의 collection을 불러온다면 총 101개의 쿼리를 하게 되는셈이에요. 비록 SELECT문이 비교적 간단하다지만 이 방법은 parent objects가 늘어날수록 SELECT문의 실행 횟수도 늘어난다는 단점이 있습니다.
  • joined loading을 이용하면, 100개의 objects와 각 object에 연계된 collection들은 단 한번의 쿼리로 가져오게 되는데요. fetch된 총 row의 개수는 모든 collections의 합과 비어있는 collection들의 합이 되겠죠. 각 row는 모든 테이블의 column들의 값을 full set으로 가져올것이고, collection item개수만큼 반복되어 보여질것이에요. 그래서 joined eager loading은 related collection들의 크기가 비교적 작을때 사용되어집니다. 추가로 joined loading이 사용하는 LEFT OUTER JOIN은 INNER JOIN과 비교할때 좀더 격렬(intensive)하기때문에 성능이 떨어질수 있어요.
  • subquery loading을 이용하면, 100개의 object들을 2개의 쿼리를 날려서 loading을 하는데요. 두번째 SELECT문에서 모든 related collection을 다 가져올거에요. 여기서는 INNER JOIN이 사용되고요, primary key이외에 추가로 가져오는 parent column도 없습니다. 따라서 collections의 크기가 큰 경우에는 subquery loading을 이용하는것이 바람직합니다.
  • 중첩으로 collections을 안에서 또 호출하고, 또 호출하고 하는 경우에는 joined나 subquery로딩을 사용하여 전부다 한번에 가져오면 collections-within-collections이 multiply되서 엄청나게 많은 row를 반환하게 될것 입니다. 게다가 joined나 subquery는 언제나 original parent class에서 비롯되므로, 만약 collection이 4 레벨로 구성되어있다면 original 쿼리는 4개의 JOIN을 가지게 될것입니다. 하지만, 그와는 달리 selectin loading을 이용한다면, depth에 상관없이 언제나 one JOIN의 쿼리를 날리게 될것 입니다.
  • selectin loading을 이용하면, 100개의 object를 가져올것이고 2개의 SQL문을 날릴것입니다. 두번째 쿼리는 가져온 100개의 primary key를 참고해서 보내므로 매우 빠르고 심플한 형태의 쿼리가 되겠죠. 만약 parent의 result set이 500개 이상인 경우에는 primary key를 500개씩 나눠서 쿼리를 필요한만큼 추가적으로 하게 될거에요.
  • 중첩된 level로 구성된 relationship에서 selectin loading을 사용할시 join이나 subquery에서 문제되었던, cartesian문제는 더이상 발생하지 않습니다. selectin loading은 가장 우수한 성능을 가진 loading방법이고, 가장 적은 수의 쿼리를 날립니다. 단 parent결과값이 너무 많을경우 나눠서 쿼리를 하기 때문에 추가적으로 날리는 쿼리가 1개 이상 될수 있습니다.

Sourse: https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html