경험 저장소

현재 우리 서비스에서는 장비들을 관리하는 물류 관련 기능이 존재한다.

장비들이 회수 되는 경우 서비스에서 "입고 요청"이라는 기능을 통해 장비에 대한 회수 히스토리를 남기는 방식으로 진행된다.

입고 요청에 대해서 상태값을 전체 상태로 조회하는 경우 자동으로 입고요청일이 현재 날짜 기준으로 - 30일까지만 검색을 지원한다.
(입고 요청에 연결된 입고 장비가 많은 경우 검색 시 될 수 있기 때문에 정책적으로 제한한것으로 알고 있다.)

이번에 특정 조건을 기준으로 입고 요청 조회 시 Timeout이 되는 이슈가 발생했고, 이에 대한 에러 로깅 및 Slack 팀 채널에 관련 오류 내용이 공유되었다.

=> 아니 이렇게 말로만 설명을 하면 우리들은 제대로 이해가 가지 않는다! 이미지라도 올려서 함께 설명해주면 좋겠다.

그럴줄 알고 이미지를 준비했습니다.! ( 비식별화 처리해야해서 안올리고 싶었습니다 흑흑)

여기서 상태값을 "전체"에 두게 되면 자동으로 입고 요청일에 대한 필터가 설정이 되며, 상품명을 검색할때 신품/중고에 대한 필터링 없이는 약 3~4초 정도의 인터벌을 두고 응답이 반환된다.

하지만 문제는 추가 필터링 이후 API 호출 시 발생한다.

- 기본 조건(입고 요청 생성 날짜 + 상품명) +  추가 조건(신품/중고 여부)에 의한 검색

=> 어어어? 필터링이 겨우 한개가 추가되었다고 API 응답 시간이 4초 -> 400초가 될 수 있는건가요?

처음에는 "신품/중고 필터 하나 때문에 4초 정도 걸리던 작업이 400초가 된다고?" 그럴리가 없고, 다른 문제가 있을것이라고 생각했다.

의심이 되는 부분은 다음과 같다.

1. 필터링에 사용되는 메서드 코드 최적화 문제

2. 신품/중고 필드에 대한 인덱싱 문제?

3. 너무 많은 데이터를 호출하고 있어서 그런가?

의심 대상 1번 : 해당하는 필터에 사용되는 메서드에 대한 코드인데, 코드만 보면 그렇게 문제가 될만한 부분은 없어보였다.

- 즉 이미 쿼리 최적화가 어느정도 진행된 상황에서 필터에 사용되는 메소드의 문제는 아니라고 생각했다.

의심 대상 2번 : 신품/중고 필드는 입고 요청 하위의 입고 요청 장비들에 대한 필드를 검색하는 문제이므로 인덱싱 문제를 의심했었다.

- 신품/중고 필드과 함께 검색되는 조건에 대해서 복합 인덱싱을 추가했으나, API 호출 후 응답까지 400초가 발생하는 문제의 원인이 아니라는것을 알게 되었다.

의심 대상 3번 : 입고 요청 8개에 대해서 각각 입고 요청마다 하위의 장비가 많기는 해도 신품/중고 필터 없이 4초 정도 소요되는 작업이 400초가 되는 부분에 대해서는 설명할 수 없었다.

흐음.. 필터링이 없으면 4초, 필터링을 추가하면 400초가 발생하는데 무엇이 문제인지 감이 잡히지 않는 상태였다.

하지만 근본적인 원인인 "필터링 과정에서 발생" 한다는 단서를 계기로 쿼리셋에서 실행되는 쿼리를 확인해보기로 결정했다.

- 필터링에 사용되는 메서드 코드는 분명 문제 없어보이는데, 실행되는 쿼리를 직접 출력해서 확인한다면 원인 파악이 가능할것이라고 예상했다.

```python

      print(queryset.query)

```

직접 쿼리를 출력해보니 문제를 확인하게 되었다. 

원인은 바로 "카테시안 곱 문제" 이다.

카테시안 곱이란? 

- From절에 2개 이상의 Table이 있을때 두 Table 사이에 유효 join 조건을 적지 않았을때 해당 테이블에 대한 모든 데이터를 전부 결합하여 Table에 존재하는 행 갯수를 곱한 만큼의 결과값이 반환되는 것

```sql

-- 기존에 이미 JOIN이 있었음

LEFT OUTER JOIN `logistics_new_storerequestitem` ON (...)

-- is_used 필터가 추가되면서 또 다른 JOIN 생성됨

INNER JOIN `logistics_new_storerequestitem` T3 ON (...)

-- WHERE 조건

WHERE (T3.`deleted` IS NULL AND T3.`is_used` = False)

```

즉 같은 테이블을 두번 JOIN 한 상황

- 첫 번째 JOIN : 완료 처리된 장비 개수 계산용(LEFT OUTER JOIN)

- 두 번째 JOIN : 신품/중고 필터용 (INNER JOIN, T3 별칭)

원활하고 빠른 이해를 위해 예시 데이터로 설명합니다.

# 예시 데이터

StoreRequest(id=1)

└── StoreRequestItem(id=101, is_used=False)

└── StoreRequestItem(id=102, is_used=False)

└── StoreRequestItem(id=103, is_used=True)

- 첫 번째 JOIN (완료 처리된 장비 개수 계산)
StoreRequest(1) → [item101, item102, item103]

- 두 번째 JOIN (신품/중고 필터용)

StoreRequest(1) → [item101, item102]

- 실제 결과 행은 다음과 같이 반환

StoreRequest(1) + item101 (첫번째) + item101 (두번째)

StoreRequest(1) + item101 (첫번째) + item102 (두번째)

StoreRequest(1) + item102 (첫번째) + item101 (두번째)

StoreRequest(1) + item102 (첫번째) + item102 (두번째)

StoreRequest(1) + item103 (첫번째) + item101 (두번째)

StoreRequest(1) + item103 (첫번째) + item102 (두번째)

즉 N개의 입고 요청, 각각 평균적인 M개의 입고 요청 장비가 연결되므로, N × M × M = N × M² 행을 처리하게 됩니다.

- 즉 1000개의 요청에 각각 50개의 아이템이 있다는 경우라면 

   - 정상 :1000 x 50 : 50,000행

   - 카테시안 곱 : 1000 x 50 x 50 = 2,500,000행

   - 즉 정상보다 50배의 쿼리를 검색해야하는 상황이 놓이게 된다.

   ( 동일한 상황에 놓이면서 4초 -> 400초로 응답 시간이 기하급수적으로 늘어난 것이다.)

그래서 해결 방법 중 가장 빠르게 해결할 수 있는 방법으로 JOIN을 2번 하지 않도록 id__in 서브쿼리를 적용하는 방법을 선택했다.

위와 같이 코드 수정 후 카테시안 곱 문제를 해결했으며, 실제 실행되는 쿼리에 대한 설명을 추가합니다.

-- 1단계: 별도의 독립적인 쿼리

SELECT request_id FROM storerequestitem WHERE is_used = False AND deleted IS NULL;

-- 2단계: 기존 쿼리에 WHERE 조건만 추가

SELECT ... FROM storerequest sr

-- 기존 JOIN은 그대로 (입고 완료 장비 개수 계산용)

LEFT OUTER JOIN storerequestitem sri ON (sr.id = sri.request_id) WHERE sr.id IN (1, 2, 3, 4, 5, ...) -- 서브쿼리 결과

=> 주인장 이렇게 설명해주는건 좋은데, 그래서 이게 왜 성능 개선이 되었는지 이해하기 쉽게 설명좀 해주시오

* 카테시안 곱으로 생성된 쿼리

→ Django가 하나의 쿼리로 모든것을 해결하려고 하기 때문에

→ queryset.filter(requestitem__is_used=value)

→ requestitem 과 연결하는 새로운 JOIN을 생성함.

* id__in 서브쿼리를 사용한 쿼리

→ 단계를 2단계로 분리
→ step1 = StoreRequestItem.objects.filter(...).values_list("request_id", flat=True)

→ step2 = queryset.filter(id__in=step1)

→ "이미 계산된 ID 목록으로 WHERE 조건만 추가하면 된다"

♣ 결론 

id__in 방식이 JOIN을 두 번 발생시키지 않는 이유는 Django ORM이 이를 완전히 별개의 두 쿼리로 처리하기 때문

첫번째 쿼리는 조건에 맞는 ID만 찾고, 두번째 쿼리는 기존 구조를 그대로 유지하면서 WHERE 조건만 추가하는 방식

즉 같은 테이블을 여러 번 JOIN하는 복잡성을 회피함

Django + DRF를 활용한 서비스 개발 관련 내용이 Java/Kotlin 등에 비해서 많이 부족하다고 생각하고 해당 글을 작성하게 되었습니다.

이러한 경험을 베이스로 작성된 글들이 동일한 문제를 겪는 사람들에게 도움이 되었으면 좋겠습니다.

저의 부족한 글에 관심을 가지고 읽어주셔서 정말 감사합니다. 

마침

상황 : 가맹점의 사업자등록번호 리스트를 수집해야하는 상황이 생겼다.

• 사업자등록번호는 총 10자리로 구성되며, 이러한 사업자등록번호 리스트를 얻을 수 있는 곳이 존재하지 않는다.

  • ( 공공데이터 포털 같은 다양한 사이트를 참고했지만 공식적으로 리스트를 얻을 수 있는 방법이 없다는 의미)

• 그래서 사업자등록번호 10자리의 자리마다 1~9까지 무작위로 할당해서 중복 없이 사업자등록번호의 유효성을 검증하는 방법을 사용해야한다.

• 10자리로 무작위로 생성된 사업자등록번호를 검증하기 위해서는 국세청에서 제공하는 "국세청_사업자등록정보 진위확인 및 상태조회 서비스"를 활용할 예정

경우의 수가 너무 많지 않은가?

• 사업자등록번호의 첫자리는 0이 아닌 1부터 시작하므로 첫자리가 0이라는 경우의 수는 제외할 수 있다.

• 하지만 이렇게 경우의 수가 제외되더라도 첫자리는 19 나머지 자리는 09까지 할당될 수 있다.

  • 즉 이 경우에는 할당 가능한 경우의 수는 9 × 10^9 = 90억 개가 된다.

• 사업자등록번호의 마지막 자리수는 앞 9자리의 숫자가 결정되면 자동으로 계산되어 할당되므로 실제 생성 가능한 조합은 9 × 10^8 = 9억 개로 줄어들게 된다.

• 조합 가능한 경우의 수 9억개를 하루에 1억개씩 검사한다 하더라도 총 9일이 걸린다.
  • 추가로 1000000000 같은 10자리는 일치하지만 절대 존재할 수 없는 사업자등록번호 같이 검색이 필요 없는 케이스들도 존재한다.

• 영화 이미테이션 게임에서 암호 해독을 위해 발명된 기계인(크리스토퍼?)를 보면 모든 설정값을 하나하나 확인해서 암호를 해독하는 시간이 상당히 소요되는것을 알 수 있다.

조건을 가지고 경우의 수를 줄여보자.

• 사업자등록번호는 폐기되면 다시 사용되지 않는다.

• 정부 민원 사이트를 통해서 확인한 사업자등록번호 규칙은 다음과 같다.

- 일련번호 코드(3자리) : 신규개업자에게 사용 가능한 번호 101~999를 순차적으로 부여한다.

- 개인/법인 구분코드(2자리)
    - 개인 구분 코드
        - 개인과세사업자는 01~79까지 순차적으로 부여
        - 개인면세사업자는 90~99까지 순차적으로 부여
        - 법인이 아닌 종교 단체 : 89
        - 아파트관리사무소, 다단계 판매원 등 : 80

    - 법인 구분 코드
        - 영리법인의 본점 : 81, 86, 87, 88
        - 비영리법인의 본점 및 지점 : 82
        - 국가 및 지방자치단체, 단체조합 등 : 83
        - 외국법인의 본점 및 지점 연락 사무소 : 84
        - 영리법인의 지점 : 85

- 일련번호코드(4자리)
    - 0001 ~ 9999 부여

- 검증번호 (1자리)

• 앞 3자리 : 899개

• 가운데 2자리 : 97개

• 뒤 4자리 : 9999개

• 마지막 1자리 : 앞 9자리마다 오직 1개만 유효하

• 줄일 수 있는 경우의 수는 최대 8억 7천개로 좁혀진다.

소요 시간

• 소요 시간은 8.7일을 소요해야한다.
• 사업자등록번호조회 호출이 가능한 API 계정을 1개 더 늘리는 방법을 사용하면 소요 시간을 약 4.3일로 줄일 수 있을것이다.