SQLAlchemy 에서 Join을 할 때 적절한 방법으로 DB 부하를 줄이는 방법

SQLAlchemy 에서 Join 을 할때 적절한 방법으로 DB 부하를 줄이는 방법

SQLAlchemy 에서 Join을 할 때 적절한 방법으로 DB 부하를 줄이는 방법

안녕하세요. 저는 “오프라인에 고여있는 여행 데이터를 IT 기술 혁신으로 흐르게 하여 온라인 판매를 자동화한다” 는 비전을 가진 AX 에서 개발자로 있는 Drake 입니다. 글의 주제는 SQLAlchemy 에서 Join 을 할때 적절한 방법으로 DB 부하를 줄이는 방법 입니다.

저는 이전 직장이 DB를 쓰는 ETL 직군이긴 했지만, SI 업체여서 주어진 명세서대로 data pipeline 만 작성하는 일을 해왔습니다. 이후 iOS를 공부해서 AX 팀에서 뽑아주셨고, 이후엔 풀스택을 제안해주셔서 전향하게 되었습니다. 파이썬을 업무로 쓰는건 처음이었고, 여러가지 피드백을 받으면서 덕분에 성장하게 되었습니다.

그중 Join 에 관련된 피드백을 저희 팀원이신 Noah 께서 장문으로 작성해주셨는데, 이해하고 나니 이후에 작성한 join문들에서는 피드백을 받는일이 거의 없게 되었습니다.

받은 피드백이 너무 좋아서 내부에서만 보기 아까워서 공유하고자 이 글을 작성하게 되었습니다.

db, sql, join 을 이해하고 계시고, python 에 어느정도 경험이 있다고 가정하고 진행하겠습니다. 이미 잘 사용하시는 분들은 혹시 제가 틀린 부분이 있다면 많은 피드백을 부탁드립니다.

1. SQLAlchemyquery

아시다시피 DataBase(이하 db)에 데이터를 저장하는 경우 테이블 단위로 저장을 합니다. db 의 데이터를 불러오는 과정을 query 라고 합니다. SQL 문을 실행하는 행위를 query 를 날린다 라고 표현하기도 합니다.

테이블 구조를 가정하겠습니다. 예를 들면 테이블 구조가 회사-팀-직원 으로 연결되어있다고 가정할때, 직원목록 페이지에서 팀도 같이 보고 싶은겁니다. 이럴때 직원 테이블과 팀 테이블을 join 으로 연결해서 불러오면 같이 출력할 수 있습니다.

query 수를 줄이는게 유리하다보니 SQL toolkit 에선 query 를 줄이기 위한 적절한 방법들을 제공하고 있습니다.

문제는 query 횟수에 대한 고려 없이 평소 코드를 짜듯이 파이썬 로직에서만 조건문을 작성하는 경우입니다. SQLAlchemy의 이해도가 부족해서 query 에 별다른 작업 없이 파이썬 로직에서 조건문만 작성해도 실행은 잘 되기 때문에 문제가 없다고 생각하기 쉽고, 저도 그랬습니다. 실행은 되기 때문에 문제는 없지만, 개선의 여지가 많은 코드가 완성되게 됩니다.

원인은 SQLAlchemy가 적절한 join 없이 query하여 데이터를 처리할 때에는 필요한 데이터를 그때그때 추가로 query하여 가져오기 때문입니다.

예를 들어, 한 회사에 팀이 몇 개인지를 확인하는 작업을 진행한다고 할 때, join을 설정했다면 1번이면 될 쿼리 횟수는 join 설정 없이 작업햇다면, 회사 수만큼 쿼리가 추가로 실행되어 버립니다.

만약, 회사가 100개가 있다면, 추가로 100번의 쿼리가 더 필요하게 됩니다. 왜 100번씩 실행되는지는 밑에서 자세히 살펴보도록 하겠습니다.

이하 2~6번 내용은 Noah 께서 작성하신 내용입니다. 저는 설명만 조금 추가했습니다.

2. ORM

아래의 테이블이 있다고 가정하겠습니다.

class Task(Base):
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Text)
    is_done = Column(Boolean, default=False)


class Subtask(Base):
    id = Column(Integer, primary_key=True, autoincrement=True)
    task_id = Column(Integer, ForeignKey("task.id"))
    task = relationship("Task", backref=backref('subtask_list'))
    name = Column(Text)
    is_done = Column(Boolean, default=False)

위 내용은 ORM 이라고 하며, Object-relational Mapping의 약자로 실제 DB에 있는 table들을 가상의 Object라는 것으로 표현하는 방법을 말합니다. python의 경우, class를 통해서 각각의 table 을 정의하고 있습니다.

2개의 테이블 Task와 Subtask가 있고, Subtask에 task_id가 Task로 FK를 연결하고 있는 가장 기본적인 Todo List 구조입니다. 앞으로 이 예시를 이용해서 설명할 예정입니다.

모든 Task 와 연결된 Subtask 를 출력해 보겠습니다.

for task in session.query(Task):
    print(task.name, "(O)" if task.is_done else "(X)")
    for subtask in task.subtask_list:
        print(f"    {subtask.name}", "(O)" if subtask.is_done else "(X)")

출력 결과는 다음과 같습니다.

할일1 (X)
    할일1-1 (O)
    할일1-2 (X)
할일2 (O)
    할일2-1 (O)
할일3 (O)

총 3개의 Task가 있고 그 중에 2개를 완료하지 못했고 하나는 완료를 했습니다.

예시 코드에서 중요한 부분은 쿼리문을 작성하기 위해 session.query(Task) 를 사용했고, subtask를 불러오기 위해서는 task.subtask_list 를 사용했습니다. SQLAlchemy의 기능인 relationship을 사용하면 attribute를 이용해서 접근할 수 있습니다.

그.런.데.

이 경우 DB로의 쿼리 요청이 총 4번 일어납니다.

우선 전체 Task 목록을 부릅니다. query는 1번 실행되고, 결과로 Task가 3개가 있다는걸 알게됩니다.

다음으로 모든 Task 의 모든 Subtask 를 불러야 하기 때문에 각각의 Task 마다 query 를 진행해서, 총 3번의 query 를 실행합니다.

결과적으로 query 는 1 + 3 = 4번 실행되게 됩니다.

만약 Task가 100개 였다면, 쿼리는 100+1번이 일어납니다. 그렇기 때문에 이런 쿼리 요청을 줄이기 위해서는 방법이 필요합니다.

3. Joinedload

joinedload는 relationship으로 정의해서 연결되어있는 다른 테이블의 entity를 같이 가져올 수 있도록 해주는 역할을 합니다.

query = session.query(
    Task
).options(
    joinedload(Task.subtask_list)  # joinedload 추가!
)
for task in session.query(Task):
    print(task.name, "(O)" if task.is_done else "(X)")
    for subtask in task.subtask_list:
        print(f"    {subtask.name}", "(O)" if subtask.is_done else "(X)")

SQL을 곁들여보자면, joinedload를 하지 않은 경우 앞의 부분은 다음 4번의 쿼리문을 실행시킨 것과 같습니다.

select * from task;
select * from subtask where subtask.task_id = 1;
select * from subtask where subtask.task_id = 2;
select * from subtask where subtask.task_id = 3;

joinedload를 한 경우에는

SELECT * FROM task 
LEFT OUTER JOIN subtask AS subtask_1 ON task.id = subtask_1.task_id

를 한 것이 되어 쿼리 1번으로 해결할 수 있습니다.

4. Join

이제 다른 usage에 대해서 생각해 보도록 하겠습니다.

subtask와 연관된 task 중 완료되지 않은 task와 연관된 subtask만 가져오고 싶은 경우를 생각해 봅시다. 추가하고자 하는 조건이 되는 table과, 불러오려고 하는 table이 서로 다른 경우, join을 사용하게 됩니다.

다음의 예시를 보도록 하겠습니다.

query = session.query(
    Subtask
).join(
    Task
).filter(
    Task.is_done == False
)
for subtask in query:
    print(subtask.name, 'of', subtask.task.name)

SELECT * FROM subtask
JOIN task ON task.id = subtask.task_id
WHERE task.is_done = 0

할일1-1 of 할일1
할일1-2 of 할일1

예시 데이터에서 is_done이 False인 task는 할일1 뿐이었으므로 할일1을 task로 가지는 subtask들만 보이면 됩니다.

그.런.데

저 문구를 실행시켜 보면 2번의 쿼리문이 실행됩니다.

SELECT * FROM subtask
JOIN task ON task.id = subtask.task_id
WHERE task.is_done = 0;
SELECT * FROM task WHERE task.id = 1;

첫번째 쿼리문을 통해서 이미 task의 내용도 다 가져왔음에도 불구하고, subtask.task.id를 하면서 task를 또 불러오고 있습니다. 우리는 이 문제를 해결하기 위한 방법을 알고 있습니다.

query = session.query(
    Subtask
).join(
    Task
).options(
    joinedload(Subtask.task)
).filter(
    Task.is_done == False
)
for subtask in query:
    print(subtask.name, 'of', subtask.task.name)

joinedload 못참지. 이럴때 쓰려고 배워뒀죠. 쿼리문을 확인해 볼까요?

SELECT * FROM subtask
JOIN task ON task.id = subtask.task_id
LEFT OUTER JOIN task AS task_1 ON task_1.id = subtask.task_id
WHERE task.is_done = 0

응? join을 두번...? 하네요. 뭐 쿼리문을 1개로 줄인건 좋은데, 그렇다고 굳이 join을 2번 할 필요도 없습니다. 우리는 이제 새로운 기능이 또 필요해 졌습니다.

5.Contains Eager

이미 불러왔으니, 또 불러올 필요 없도록 표시를 해 주는 것이 contains_eager의 역할입니다. 다음의 예시를 보도록 하겠습니다.

query = session.query(
    Subtask
).join(
    Task
).options(
    contains_eager(Subtask.task)
).filter(
    Task.is_done == False
)
for subtask in query:
    print(subtask.name, 'of', subtask.task.name)

SELECT * FROM subtask
JOIN task ON task.id = subtask.task_id
WHERE task.is_done = 0

쿼리문이 이제 다시 가장 처음으로 돌아왔고 저 쿼리문 하나만으로 우리가 필요한 값들을 모두 불러올 수 있습니다.

  1. task를 불러오기 위해 쿼리를 한번 더 하지 않아도 됩니다.
  2. join을 불필요하게 한번 더 안해도 됩니다. Task를 이미 부르고 있으니 Subtask.task로 접근 할 때 불러온 Task를 사용할 수 있도록 해줍니다.

6. 반대의 경우에 있어서도 마찬가지입니다.

Subtask를 불러와야 하는데 Task에 조건을 추가할 때 뿐만 아니라, Task를 불러와야 하는데 Subtask에 조건을 추가해야 할 때에도 마찬가지 입니다.

다음 예시는 아직 완료하지 않은 Subtask를 가지고 있는 Task를 가지고 오는 경우입니다.

만약 joinedload 를 사용하게 된다면

query = session.query(
    Task
).join(
    Subtask
).options(
    joinedload(Task.subtask_list)
).filter(
    Subtask.is_done == False
)
for task in session.query(Task):
    print(task.name, "(O)" if task.is_done else "(X)")
    for subtask in task.subtask_list:
        print(f"    {subtask.name}", "(O)" if subtask.is_done else "(X)")

SELECT * FROM task
JOIN subtask ON task.id = subtask.task_id
LEFT OUTER JOIN subtask AS subtask_1 ON task.id = subtask_1.task_id
WHERE subtask.is_done = 0

할일1 (X)
    할일1-1 (O)
    할일1-2 (X)

아직 완료되지 않은 subtask는 할일1-2 뿐이기 때문에 할일1-2의 task인 할일1만 불러와졌습니다.

하지만 joinedload를 통해서 또 subtask를 join해서 불러오게되고 이 때에는 새로 join을 한 것이기 때문에 이전 조건을 무시하고 모든 subtask를 불러오게 됩니다.

만약 contains_eager 를 사용하게 된다면

query = session.query(
    Task
).options(
    contains_eager(Task.subtask_list)
).join(
    Task.subtask_list
).filter(
    Subtask.is_done == False
)
for task in query:
    print(task.name, "(O)" if task.is_done else "(X)")
    for subtask in task.subtask_list:
        print(f"    {subtask.name}", "(O)" if subtask.is_done else "(X)")

SELECT * FROM task
JOIN subtask ON task.id = subtask.task_id
WHERE subtask.is_done = 0

할일1 (X)
    할일1-2 (X)

contains_eager가 뜻하는 바는, 이미 다른 곳에서 불러오고 있으니 자동으로 부르지 않아도 된다는 것이였습니다.

그래서 이미 조건문을 사용하기 위해 subtask를 join하고 있으니, 또 join하지 않습니다.

그래서 기존에 부르고 있는 subtask를 사용해서 subtask_list를 구성할 때 쿼리문이 적용되어 subtask.is_done == False 조건을 만족하는 subtask 만 subtask_list에 들어가게 됩니다.

사실 sqlalchemy 에서 DB 부하를 줄이는 방법에는 lazy loading 방법이 있습니다. 읽어올 확률이 낮은 join 에 대해서 나중에 로딩할수 있는 방법이고 sqlalchemy 기본값입니다만, 제 경우 대부분 테이블에 모든값을 출력해야해서 명시적으로 쓰지는 않았습니다. lazy loading 에 대해서 추가요청을 받았습니다만, 제가 아는게 적어서 이 글에선 다루지 않았습니다.

귀한 피드백을 주신 Noah 께 감사드리며, 제가 받은 피드백을 보시고 다른분들도 좋은 query 를 만들기를 바라겠습니다.

감사합니다.

액스(AX)로 다 채널 상품 동시 판매 및 운영


코로나 이후, 투어&액티비티 온라인 판매 준비를 끝내세요. 수십 개의 온라인 판매 채널에 한 번에 상품을 업로드해드립니다. 무료로 상품 생성을 시작해보세요!