HashJoin으로 성능 개선하기

Yoon Uk·2024년 10월 8일

목록 보기

20/20

1. 배경

현재 콜드체인 플랫폼 서비스를 운영하는 팀에서 근무하고 있습니다.
물류/유통 도메인 특성 상 재고 관련 데이터를 사용해 집계/통계성 기능(재고 조회, 재고수불부 생성 및 조회 등)을 다룰 일이 많습니다.

대규모 재고 관리 시스템에서 1000만 건의 데이터 조회로 인한 성능 저하 문제를 SQL 쿼리 최적화를 통해 해결한 사례를 소개합니다. 특히 재고 조회와 관련된 기능의 개선 과정에 초점을 맞추어 설명하겠습니다.
결과적으로 약 10초에서 약 3초로 3배 가량 조회 성능을 개선했습니다.

데이터베이스는 MySQL8.0.34(RDS, db.r6g.large)을 사용하고 있습니다.

아래의 쿼리 예시는 서비스 보안상 어느 정도 각색한 예시이고,
실행 속도는 실제 시간을 가져왔습니다.

기존 쿼리 입니다.

select g.name, i.total_count, l.lct_name, ob.outb_ymd
from inventory i
inner join goods g
	on g.goods_id = i.goods_id
inner join location l
	on l.lct_seq = i.lct_seq
inner join outbound ob
    on ob.goods_id = g.goods_id
inner join center c
    on i.center_id = c.center_id
where c.center_id = 'DC001'
    and ob.outb_ymd >= 20240901 
    and ob.outb_ymd <= 20240930

위 쿼리의 실행계획 입니다.(필요한 컬럼만 정리했습니다.)

+--+-----------+-----+------+---------------+-----------+
|id|select_type|table|type  |ref            |Extra      |
+--+-----------+-----+------+---------------+-----------+
|1 |SIMPLE     |c    |const |const          |null       |
|1 |SIMPLE     |i    |ALL   |null           |Using where|
|1 |SIMPLE     |l    |eq_ref|rdms.i.lct_seq |null       |
|1 |SIMPLE     |g    |eq_ref|rdms.i.goods_id|null       |
|1 |SIMPLE     |ob   |ref   |rdms.i.goods_id|Using where|
+--+-----------+-----+------+---------------+-----------+

위의 실행계획에서 type, Extra를 중점으로 보시면 됩니다.

c : center table(물류 센터 관련)
i : inventory table(재고 관련)
l : location table(로케이션: 적재된 위치 관련)
g : goods table(상품 관련)
ob : outbound table(출고 관련)

위의 실행계획에서 join 방식만을 해석해보면,

[inventory 테이블]

inventory 테이블에 대해 풀 테이블 스캔이 발생하고 있다.(type = ALL)
WHERE 절의 조건으로 인해 일부 필터링이 되지만, 인덱스를 사용하지 않고있다.(key = null)
이 테이블이 드라이빙 테이블(첫 번째 테이블)로 사용되고 있을 가능성이 높다.

[location, goods 테이블]

Nested Loop Join 을 사용해 조인하고 있다.(type = eq_ref)
inventory의 각 행마다 location에서 한 행을 조회하므로, Nested Loop Join이 사용되고 있다.

2. 실행계획에서 type: eq_ref 설명

1) EXPLAIN의 type 컬럼 의미

EXPLAIN 명령어의 결과에서 type 컬럼은 MySQL 옵티마이저가 각 테이블에 접근하는 방식을 나타냅니다.
접근 방식의 효율성은 다음 순서로 높습니다:
- system: 테이블에 단 하나의 행만 있을 때.
- const: 프라이머리 키나 유니크 인덱스로 한 행만 조회할 때.
- eq_ref: 이전 테이블의 각 행에 대해 현재 테이블에서 프라이머리 키나 유니크 인덱스를 사용하여 한 행을 조회할 때.
- ref: 인덱스를 사용하여 여러 행을 조회할 때.
- range: 인덱스 범위를 검색할 때.
- index: 인덱스 전체를 스캔할 때.
- ALL: 테이블 전체를 스캔할 때.

2) eq_ref의 의미

eq_ref는 효율적인 조인 방식 중 하나로, Nested Loop Join에서 사용됩니다.
이전 테이블의 각 행에 대해 현재 테이블에서 프라이머리 키나 유니크 인덱스를 사용하여 정확히 한 행을 조회합니다.
이는 조인 조건이 현재 테이블의 프라이머리 키나 유니크 인덱스 컬럼과 동등(equal) 조건으로 연결되어 있을 때 발생합니다.

3) Nested Loop Join과의 관계

MySQL에서 기본적인 조인 방식은 Nested Loop Join입니다.
Nested Loop Join에서는 외부 테이블의 각 행에 대해 내부 테이블을 반복적으로 조회합니다.
eq_ref 타입은 이러한 Nested Loop Join에서 가장 효율적인 방식으로 내부 테이블을 접근하고 있음을 나타냅니다.

4) Extra 컬럼 확인

EXPLAIN 결과의 Extra 컬럼에 Using join buffer 관련 내용이 없다면 기본적으로 Nested Loop Join이 사용되고 있습니다.
Using join buffer (Hash Join)이나 Using join buffer (Block Nested Loop)가 표시되면 다른 조인 방식이 사용되고 있음을 의미합니다.
제공된 실행 계획에서 Extra 컬럼에 이러한 내용이 없으므로, Type: eq_ref는 Nested Loop Join의 일부로 이해할 수 있습니다.

3. Nested-Loop Join을 사용했을 때의 조회 성능

약 10초 정도 걸립니다.

4. Nested-Loop Join 이란?

Nested-Loop Join은 관계형 데이터베이스에서 두 개 이상의 테이블을 조인할 때 사용되는 가장 기본적인 조인 알고리즘 중 하나입니다. 이 알고리즘은 한 테이블의 각 행에 대해 다른 테이블을 반복적으로 검색하여 조인 조건을 만족하는 행을 찾는 방식으로 작동합니다.

1) 작동 원리

외부 루프 (Outer Loop)

첫 번째 테이블(외부 테이블)의 각 행을 하나씩 읽어옵니다.

내부 루프 (Inner Loop)

외부 테이블의 현재 행에 대해 두 번째 테이블(내부 테이블)을 검색합니다.
내부 테이블에서 조인 조건을 만족하는 행을 찾습니다.
조인 조건을 만족하면 외부 테이블의 현재 행과 내부 테이블의 해당 행을 결합하여 결과 집합에 추가합니다.
이러한 과정을 외부 테이블의 모든 행에 대해 반복합니다.

2) 장점

단순하고 이해하기 쉬움
- 알고리즘이 간단하여 구현이 용이합니다.
인덱스 활용 가능
- 내부 테이블의 조인 컬럼에 인덱스가 있으면 성능이 향상됩니다.
작은 데이터셋에 효율적
- 데이터 크기가 작을 때는 빠른 성능을 보입니다.

3) 단점

성능 저하
- 테이블 크기가 크거나 데이터 양이 많을 경우 성능이 크게 떨어집니다.
높은 연산 비용
- 외부 테이블의 각 행마다 내부 테이블을 검색하므로 전체 연산량이 증가합니다.
인덱스 없을 시 비효율적
- 내부 테이블에 인덱스가 없으면 전체 테이블 스캔이 발생하여 비효율적입니다.

4) 적합한 경우

데이터 크기가 작은 경우
- 테이블의 행 수가 적으면 오버헤드가 적으므로 효율적입니다.
인덱스가 잘 구성된 경우
- 내부 테이블의 조인 컬럼에 인덱스가 있어 검색 속도가 빠른 경우 효과적입니다.
조인 결과가 적은 경우
- 조인 조건을 만족하는 행의 수가 적을 때는 불필요한 연산이 줄어듭니다.

5. HashJoin 이란?

Hash Join은 데이터베이스에서 두 개 이상의 테이블을 조인할 때 사용되는 조인 알고리즘 중 하나로, 조인 조건이 동등(equal) 조건일 때 주로 사용됩니다. 이 알고리즘은 한 테이블의 데이터를 메모리에 해시 테이블로 만들고, 다른 테이블의 데이터를 순회하면서 해시 테이블을 통해 빠르게 매칭하는 방식으로 작동합니다.

1) 작동 원리

Build 단계 (해시 테이블 생성)

작은 테이블(Small Table) 또는 왼쪽 테이블을 선택하여 해당 테이블의 조인 키를 기반으로 해시 테이블을 생성합니다.
각 행의 조인 키에 해시 함수를 적용하여 해시 버킷에 저장합니다.

Probe 단계 (매칭 검색)

큰 테이블(Big Table) 또는 오른쪽 테이블을 순회하면서 각 행의 조인 키에 동일한 해시 함수를 적용합니다.
해시 테이블에서 해당 해시 버킷을 찾아 조인 조건을 만족하는지 확인합니다.
만족하면 결과 집합에 추가합니다.

2) 장점

빠른 조인 성능
- 큰 데이터셋에서도 효율적으로 작동하며, 특히 조인 키에 인덱스가 없을 때 효과적입니다.
- 데이터의 순서에 영향을 받지 않으므로 정렬이 필요하지 않습니다.
메모리 효율성
- Build 단계에서 작은 테이블만 메모리에 로드하므로 메모리 사용량을 관리할 수 있습니다.

3) 단점

메모리 의존성
- 해시 테이블을 생성하기 위해 메모리에 충분한 공간이 필요합니다.
- 메모리 부족 시 디스크를 사용하게 되어 성능이 저하될 수 있습니다.
조인 조건 제한
- 주로 동등 조건(equal join)에만 적용 가능하며, 범위 조건(range join)에는 적합하지 않습니다.
해시 함수 충돌
- 해시 함수 충돌로 인해 성능이 저하될 수 있으나, 일반적으로 해시 함수는 충돌을 최소화하도록 설계됩니다.

4) 적합한 경우

대용량 데이터셋
- 테이블 크기가 크고, 인덱스가 없거나 사용할 수 없는 경우 효율적입니다.
동등 조인 조건
- 조인 조건이 = 연산자로 이루어진 경우에 가장 효과적입니다.
메모리 여유가 있는 경우
- 해시 테이블을 메모리에 생성해야 하므로 메모리 리소스가 충분한 환경에서 적합합니다.

5) 주의사항

메모리 관리
- 해시 테이블이 메모리에 적재되므로, 메모리 사용량을 모니터링하고 필요 시 쿼리를 최적화해야 합니다.
데이터 분포
- 조인 키의 데이터 분포가 불균형한 경우 해시 테이블의 일부 버킷에 데이터가 집중되어 성능이 저하될 수 있습니다.
옵티마이저 지원
- 일부 데이터베이스에서는 Hash Join을 기본적으로 지원하지 않거나 제한적으로 지원합니다.
  - MySQL은 8.0.18부터 지원됩니다.
- 데이터베이스의 버전과 설정을 확인하여 Hash Join 사용 가능 여부를 확인해야 합니다.

6. HashJoin을 적용하기 위해 쿼리 수정

1) subquery

직접 Join 하지 않고, subquery를 사용해 한 번 필터링 한 후, 그 결과물을 Join에 활용하는 방법

SELECT *
FROM
  (SELECT *
   FROM inventory
   WHERE center_id = 'DC001') i
INNER JOIN goods g ON g.goods_id = i.goods_id
INNER JOIN location l ON l.lct_seq = i.lct_seq
INNER JOIN
  (SELECT *
   FROM outbound
   WHERE outb_ymd >= 20240901 AND outb_ymd <= 20240930) ob ON ob.goods_id = g.goods_id
-- 이미 inventory에서 center_id를 필터링했으므로 center 테이블과의 조인을 제거합니다.
;

성능 개선 포인트

조인 대상 데이터 감소
- 조인 전에 서브쿼리를 사용하여 필요한 데이터만 선택함으로써 조인 시 처리해야 할 데이터의 양을 줄였습니다.
- 이는 디스크 I/O 및 메모리 사용량을 줄여 성능 향상에 기여합니다.
불필요한 조인 제거
- center 테이블과의 조인을 제거하여 조인 횟수를 감소시켰습니다.

2) with

with 구문을 통해 데이터를 미리 필터링한 임시 테이블을 생성하고, 이 임시 테이블을 사용하여 join에 활용하는 방법

-- 필요한 데이터만 미리 필터링하는 CTE(Common Table Expression)를 사용합니다.
WITH
FilteredInventory AS (
    SELECT *
    FROM inventory
    WHERE center_id = 'DC001'
),
FilteredOutbound AS (
    SELECT *
    FROM outbound
    WHERE outb_ymd >= 20240901 AND outb_ymd <= 20240930
)
SELECT *
FROM FilteredInventory i
INNER JOIN goods g ON g.goods_id = i.goods_id
INNER JOIN location l ON l.lct_seq = i.lct_seq
INNER JOIN FilteredOutbound ob ON ob.goods_id = g.goods_id
-- 이미 inventory에서 center_id를 필터링했으므로 center 테이블과의 조인을 제거합니다.
;

성능 개선 포인트

데이터 필터링을 통한 조인 대상 감소:
- 서브쿼리 또는 CTE를 사용하여 미리 필요한 데이터만 선택함으로써 조인 시 처리해야 할 데이터의 양을 줄였습니다.
- 이는 디스크 I/O 및 메모리 사용량을 감소시켜 성능 향상에 기여합니다.
불필요한 조인 제거:
- center 테이블과의 조인을 제거하여 조인 연산의 수를 감소시켰습니다.
- inventory 테이블에서 이미 center_id를 사용하여 필터링하므로 center 테이블의 추가 정보가 필요하지 않다면 조인을 생략할 수 있습니다.

7. HashJoin 적용 후의 실행계획

+--+-----------+-----+------+---------------+------------------------------------------+
|id|select_type|table|type  |ref            |Extra                                     |
+--+-----------+-----+------+---------------+------------------------------------------+
|1 |SIMPLE     |c    |const |const          |null                                      |
|1 |SIMPLE     |i    |ALL   |null           |Using where                               |
|1 |SIMPLE     |l    |eq_ref|rdms.i.lct_seq |Using where; Using join buffer (Hash Join)|
|1 |SIMPLE     |g    |eq_ref|rdms.i.goods_id|Using where; Using join buffer (Hash Join)|
|1 |SIMPLE     |ob   |ref   |rdms.i.goods_id|Using where; Using join buffer (Hash Join)|
+--+-----------+-----+------+---------------+------------------------------------------+

위의 실행계획에서 type, Extra를 중점으로 보시면 됩니다.(필요한 컬럼만 정리했습니다.)

c : center table(물류 센터 관련)
i : inventory table(재고 관련)
l : location table(로케이션: 적재된 위치 관련)
g : goods table(상품 관련)
ob : outbound table(출고 관련)

8. HashJoin을 사용한 후의 성능

약 3초로 Nested-Loop Join을 사용한 것(약 10초)보다 약 3배의 조회 성능을 개선했습니다.

9. HashJoin을 활용하도록 유도하기

MySQL 8.0 이상에서 사용하는 방법만을 다룹니다.

1) SQL

SELECT /*+ HASH_JOIN(i, g) HASH_JOIN(i, l) HASH_JOIN(g, ob) HASH_JOIN(i, c) */ *
FROM inventory i
INNER JOIN goods g ON g.goods_id = i.goods_id
INNER JOIN location l ON l.lct_seq = i.lct_seq
INNER JOIN outbound ob ON ob.goods_id = g.goods_id
INNER JOIN center c ON i.center_id = c.center_id;

2) JPA

1. Hibernate의 SQL Comments 기능 사용

Hibernate를 JPA 구현체로 사용하는 경우, SQL 주석을 활용하여 옵티마이저 힌트를 SQL 쿼리에 포함시킬 수 있습니다.
1) Hibernate 설정에서 SQL Comments 활성화

// properties
hibernate.use_sql_comments=true

2) setComment() 메서드를 사용하여 힌트 추가

import org.hibernate.Session;
import org.hibernate.query.Query;

// 엔티티 매니저에서 Hibernate 세션 가져오기
Session session = entityManager.unwrap(Session.class);

// HQL 쿼리 작성
String hql = "SELECT e FROM YourEntity e WHERE e.field = :value";

// 쿼리 생성 및 힌트 추가
Query<YourEntity> query = session.createQuery(hql, YourEntity.class)
    .setParameter("value", someValue)
    .setComment("+ HASH_JOIN");

// 결과 조회
List<YourEntity> result = query.getResultList();

setComment("+ HASH_JOIN")를 사용하여 SQL 힌트를 주석 형태로 추가합니다.
Hibernate는 이 주석을 SQL 쿼리의 앞부분에 포함시킵니다.

실행되는 SQL 예시

/*+ HASH_JOIN */
SELECT e.field1, e.field2 FROM your_entity e WHERE e.field = ?

2. 네이티브 쿼리 사용

JPA의 네이티브 쿼리를 사용하여 직접 SQL을 작성하고 힌트를 포함시킬 수 있습니다.

String sql = "SELECT /*+ HASH_JOIN */ * FROM your_entity e WHERE e.field = :value";

Query query = entityManager.createNativeQuery(sql, YourEntity.class);
query.setParameter("value", someValue);

List<YourEntity> result = query.getResultList();

직접 SQL을 작성하므로 원하는 힌트를 자유롭게 포함할 수 있습니다.

3. @Query 어노테이션과 @QueryHints 사용

Spring Data JPA를 사용하는 경우 @QueryHints를 활용하여 힌트를 추가할 수 있습니다.

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.QueryHints;
import javax.persistence.QueryHint;

public interface YourEntityRepository extends JpaRepository<YourEntity, Long> {

    @Query("SELECT e FROM YourEntity e WHERE e.field = :value")
    @QueryHints({
        @QueryHint(name = "org.hibernate.comment", value = "+ HASH_JOIN")
    })
    List<YourEntity> findWithHint(@Param("value") String value);
}

org.hibernate.comment 힌트를 사용하여 SQL 주석을 추가합니다.
Hibernate 설정에서 hibernate.use_sql_comments=true로 설정되어 있어야 합니다.

3) QueryDsl

팀 내에서 복잡한 조회 쿼리를 사용할 때 QueryDsl을 활용하고 있습니다.

import com.querydsl.jpa.impl.JPAQueryFactory;
import com.querydsl.jpa.JPAExpressions;

JPAQueryFactory queryFactory = new JPAQueryFactory(entityManager);

QYourEntity e = QYourEntity.yourEntity;

List<YourEntity> result = queryFactory.selectFrom(e)
    .where(e.field.eq(someValue))
    .setHint("org.hibernate.comment", "+ HASH_JOIN")
    .fetch();

setHint() 메서드를 사용하여 Hibernate 힌트를 추가합니다.
Hibernate 설정에서 hibernate.use_sql_comments=true로 설정되어 있어야 합니다.

10. 마무리

별도의 인덱스를 만들지 않고 쿼리를 수정하고, join 방식을 변경해서 성능을 개선할 수 있다.
약 1000만건의 데이터에서는 3배 정도의 성능을 개선할 수 있다.(MySQL8.0.34, RDS(db.r6g.large) 에서의 성능)

Yoon Uk

이전 포스트

HashJoin으로 성능 개선하기

데이터베이스

1. 배경

2. 실행계획에서 type: eq_ref 설명

1) EXPLAIN의 type 컬럼 의미

2) eq_ref의 의미

3) Nested Loop Join과의 관계

4) Extra 컬럼 확인

3. Nested-Loop Join을 사용했을 때의 조회 성능

4. Nested-Loop Join 이란?

1) 작동 원리

2) 장점

3) 단점

4) 적합한 경우

5. HashJoin 이란?

1) 작동 원리

2) 장점

3) 단점

4) 적합한 경우

5) 주의사항

6. HashJoin을 적용하기 위해 쿼리 수정

1) subquery

성능 개선 포인트

2) with

성능 개선 포인트

7. HashJoin 적용 후의 실행계획

8. HashJoin을 사용한 후의 성능

9. HashJoin을 활용하도록 유도하기

1) SQL

2) JPA

1. Hibernate의 SQL Comments 기능 사용

2. 네이티브 쿼리 사용

3. @Query 어노테이션과 @QueryHints 사용

3) QueryDsl

10. 마무리

[데이터베이스] Lock

0개의 댓글