SQL Query Time 줄여보기

2019.12.29, 현재 작성 중인 글입니다. 계속 업데이트될 예정입니다.

 

기초적인 웹 게시판을 만들었다. 포스트 테이블을 기반으로 User, Like, Comment, Hitcount 등의 기능이 있는 가장 기초적이고 보편적인 형태의 BBS이다. ERD를 보자.

 

편의상 Hitcount는 생략.

Comment와 Like는 Post 테이블 뿐만 아니라 다른 테이블도 범용적으로 바라볼 수 있는 FK를 가져야 한다. 따라서 장고의 Generic Relation을 사용했다. Content Type 테이블에는 장고 프로젝트의 모든 앱과 모델의 정보가 저장되어 있다. Generic Relation은 타겟이 되는 객체의 Content Type과 Object id를 저장해 해당 객체가 어떤 객체를 참조하는지 알 수 있다.

 

구현하고 싶은 비주얼은 아래와 같았다. 어느 커뮤니티, 어느 게시판을 가도 흔하게 볼 수 있는 모습이다.

 

내용이 썩 좋진 않지만 넘어가자. 그게 요지가 아니니까

Post의 리스트를 불러올 때 해당 글에 달린 추천 수, 댓글 수, 조회 수를 같이 불러와야 한다.

기본적으로 Post의 뷰는 장고의 generic view로 구현한다. 생각해보니 function base view는 제대로 구현해본적도 없는거 같다.

generic list view를 이용하면 간단하게 Post의 리스트를 불러올 수 있다.

 

model, context object name, template name을 지정해주면 해당 템플릿에서 특정 모델을 내가 원하는 변수 이름으로 불러올 수 있다. 

 

하지만 우리의 게시판은 Post 모델에 관한 정보만 필요한게 아니다. owner라는 필드가 가진 User 테이블의 정보도 필요하고 Like, Comment, Hitcount 테이블의 정보도 같이 가져와야 한다.

물론 템플릿에서 post.owner.name 하는 식으로 해당 값을 가져올 수 있다. 하지만 이렇게 하면 N+1 쿼리 문제가 발생한다.

generic relation으로 엮인 필드는 더욱 복잡하다. 자칫 잘못하면 8개의 댓글이 달린 하나의 게시글 객체를 요청할 때 48개의 쿼리가 날라갈 수도 있다.

 

클래스라이언을 작업할 때 해당 문제를 경험했고, 일부는 해결했지만 대부분은 해결하지 못한채로 남겨놨다. 이번 포스팅에서는 해당 문제를 최대한으로 해결해본다. 

 

---

1. Select Related (Eager Loading)

owner 필드의 User 정보는 간단하게 해결할 수 있다. 기본적으로 장고의 쿼리셋은 게으르다. 장고의 select_related 메서드를 이용하면 Eager Loading을 수행할 수 있다. List View에서 지정한 쿼리셋을 템플릿에서 사용하면 아래와 같을 것이다.

owner.name에서 N+1 문제가 발생한다. 10개의 post를 불러온다면 11개의 쿼리, 100개의 post를 불러온다면 101개의 쿼리가 요청될 것이다. select related는 List View의 get queryset 함수에서 지정할 수 있다.

 

select related는 Forigen Key, OneToOne 관계에서만 사용할 수 있다. 적용하기 전과 후의 Query 요청은 아래와 같다.

 

적용 전

적용 후, INNER JOIN으로 User 정보를 가져온 것을 확인할 수 있다.

적용하지 않은 상태로 owner.name을 요청하면 Line by Line으로 User 테이블에 접근하게 될 것이다. 하지만 아래는 User 테이블을 다시 요청할 필요가 없다.

 

 

2. Subquery

이제 User 테이블은 해결됐다. 다음은 Like, Comment의 정보가 필요하다.

Post List View에서는 해당 글에 달린 좋아요의 수와 댓글의 수가 필요하다. User 테이블처럼 특정 필드의 데이터가 아닌 Count 값이 필요한 것이다. 가장 간단하게 생각해보면 다음과 같은 해결책이 있다.

 

해당 클래스는 Like 모델의 generic relation 필드를 가지고 있는 Abstract 클래스이다. Like와 LikeMixIn으로 나눠서 구성하면 Like의 대상이 되는 모델 클래스에서 LikeMixIn 클래스를 상속받으면 해당 모델 클래스에서 like 필드를 사용할 수 있게 된다.

 

Post 모델은 당연히 LikeMixIn 클래스를 상속받았기 때문에 Post 객체에서 위에 나열된 property를 사용할 수 있게 된다. 탬플릿에서 보면 아래와 같을 것이다.

 

마찬가지로 N+1 문제가 발생하는 상황이다.

심지어 저 세 가지 값을 모두 써야한다면 N+1이 아닌 (N*3)+1이 될 것이다.

 

여기서 고민을 오래 했다. 장고에서는 ManyToMany, ManyToOne 등 모든 관계에 적용할 수 있는 Prefetch related 메서드가 있다. User 값을 Select related로 해결했으니 왠지 Comment, Like는 Prefetch related를 사용해 해결해야 전체적인 통일성이 생길 것 같다. 유지 보수도 쉬울 것 같고, 가독성도 좋아질 것만 같다.

 

하지만 선택하지 않았다. 탬플릿에서 사용할 객채가 복잡해질거 같고(왠지) Form, TemplateTags, Ajax 등 다양한 클래스에서 사용하기도 해야 하는데 필드의 이름을 통제할 수 있어야 할 것 같다(괜히).

안된다 가 아니라 안될 것 같다, 라는 가정 하에 내린 결정이었다. 우선 만들어보고 아니면 수정하기로 했다. 결국 선택한 방법은 클래스라이언에서도 이미 사용했던 Subquery와 annotation이었다.

 

like/utils/annotate_queryset_with_likes_count. Comment에도 유사한 역할을 수행하는 함수가 있다.

위에 등장하는 SQCount. https://stackoverflow.com/a/47371514/7738766

Post List View에서 context_object_name는 쿼리셋을 사용해 가져오니까 애초에 쿼리셋에 like count, dislike count, total like count 혹은 comment count 등의 필드를 추가해주면 좋을 것 같았다.

장고의 쿼리셋은 이것을 annotate를 사용해 구현한다. Like 앱 안에 해당 annotation을 수행하는 함수를 만들고 이걸 원하는 쿼리셋에 붙이면 될 것 같았다.

 

total like count를 제외한 Subquery

서브쿼리를 사용한 annotation이 적용된 쿼리는 이렇게 된다. 개인적으로 가독성이 상당히 많이 떨어진다고 생각된다.

Post 테이블에 요청할 때 Like 테이블에서 content type과 object id가 Post 객체와 일치하는 Like 컬럼의 Count 값을 같이 가져온다.

위에서 사용한 방식을 이용해 List View의 쿼리셋에 Comment, Like, Hitcount의 annotation을 모두 적용하면 쿼리 요청은 아래와 같다.

 

Post 테이블에서 id, title, created_at, owner_id 등등을 가져오는데 Like 테이블에서 "like count"라는 이름으로 content_type, object_id가 post의 그것하고 같은거도 같이 가져오고 마찬가지로 "dislike_count"라는거도 같이 가져오고 또 Comment 테이블에서 마찬가지로 content_type, object_id가 같은거도 가져오고 Hitcount 테이블에서 hit count라는 컬럼 중에 또 같은거 가져오는데 그건 "hits"라고 부르고 User 테이블에서 email, created_at, updated_at, is_superuser, is_active 등등의 정보를 가져올건데 이거는 INNER JOIN 해서 혹시 필요하면 가져다 쓰는데 이 전체의 값은 post의 created_at을 기준으로 Ordering 해주고 내림차순으로 10개만 먼저 가져와라.

.. 내가 데이터베이스나 SQL에 대한 지식이 거의 없어서 잘 모르지만, 괴랄하기 짝이 없는 쿼리라는 생각이 들었다.

그래도 우선 돌려보기로 했다. 이번 테스트의 목적은 성능 확인이었으니까.

 

Factory boy를 사용해 더미 데이터를 채웠다. 테스트 코드를 작성할 수도 있었겠지만 눈으로 직접 보고싶었다. Post는 10만개, Comment는 50만개, Like는 100만개, User는 160만개 정도의 데이터가 들어있는 상태였다. 

 

 

url에 /posts를 요청했고.. 결과는 로딩 실패였다.

10분 이상을 기다렸고, 30분이 지나가도록 화면은 뜨지 않았다. 도대체 어느 정도의 시간이 필요한지 감이 오지 않았다.

적어도 한 번은 불러와야 도대체 어떻게 된 일인지 알 수 있을거라 생각해서 쿼리셋에 임의의 필터를 추가했다. 특정 유저가 작성한 글만 불러오도록 List View를 임시로 수정했다. 해당 유저는 열 개 남짓의 글을 작성했다.

 

결과는 어마어마했다. 십만개 중에 열 개 남짓한 글을 불러오는데 필요한 Query time은 20,000ms 이상이었다.

유저가 게시판에 접속하는 순간 20초 이상을 기다려야 한다는 소리였다. 감당이 되지 않는 시간이었다. 절대 전체 데이터를 불러오는데 소요되는 시간이 아니라 극히 일부 데이터를 불러오는데 이만큼의 시간이 걸리는거였다.

 

문제가 생기는 부분을 몇 가지 짚어봤다.

 

1. Pagenation에서 전체 페이지의 수를 계산할 때 엄청난 로드가 걸린다.

 

왼쪽같이 하면 충분한데 굳이 병합된 query 요청을 통해 값을 받아온다. 원래 오른쪽의 time 값은 저 숫자의 7배 이상이었다.

pagenation을 담당하는 context data.

2. 무엇보다, 쿼리가 너무너무 늦다.

 

2번을 해결하기 위해 DB Index를 테스트 해봤다. 각각의 테이블의 일부 필드에 index을 적용했다. 결과는 놀라운 수준이었다.

 

여전히 pagenation과 관련된 부분은 해결되지 않은 상태.

위에 말한 1번 문제는 해결되지 않아 3초에 가까운 로드가 있긴 하지만 어쨌든 전체 글이 불러와진다!

10만 건 중에 10건 남짓의 데이터를 불러오는데 20초가 걸리던걸 생각하면 인덱스가 쿼리 시간을 얼마나 줄여준지 감이 오지 않는다. 심지어 임시 필터링 WHERE절도 적용되지 않은 시간이다. pagenation에서 생기는 로드만 해결하면 꽤나 만족스러운 타임이 나올 것 같다.

 

 get_queryset을 없애고 context_data에서 해결한 모습이다.

페이지네이션이 먼저 수행될 때는 Post 객체만 object_list에 담기기 때문에 필요없는 annotation이 수행되지 않는다.

병합된 쿼리는 context 안에서 필요하기 때문에 annotation 과정을 그 안에서 처리했다.

예쁜 코드는 아니라 수정과 분리가 좀 더 필요할 것 같다. 어쨌든 결과는 이렇다.

 

함수의 분리, 서브쿼리에서의 중복 문제 등이 남아있지만 현 상태로는 거의 최선이라고 보인다. 드라마틱한 성능 향상도 이제는 아이디어가 고갈됐다.

SQL 쿼리와 데이터베이스 성능에 대해 검색해본 결과, 서브쿼리보다는 조인이 낫다는 얘기들이 계속해서 나오기 때문에 이번에는 현 코드를 모두 뜯어고쳐 다시 작업해봐야겠다. 그 후 양쪽의 성능을 비교해본다.