카디널리티와 복합키 순서 그리고 PK Index

PK 복합키 순서에 따라 인덱스가 타지 않을 수 있다!

JPA는 복합키를 생성할 때 컬럼명의 알파벳 순으로 생성한다. Entity Class에 정의된 순서로 생성되는 게 아니기 때문에 조회할 때 기대했던 PK Index가 타지 않을 가능성이 높다. 먼저 PK index 실행 조건에 대해 알아보겠다.

---

PK 인덱스 실행 조건 (MySQL/Oracle)

DB에 테이블을 생성할 때 PK Key 들에 대해서 자동으로 Index를 생성해준다. 이를 PK 인덱스라고 한다. 생성된 PK 순서대로 PK Index가 적용되기 때문에 큰 범위에서 작은 범위의 컬럼으로 나열하는 것이 좋다.

아래와 같은 테이블이 있을 때, 조회 조건에 어떤 PK컬럼을 매핑하느냐에 따라 옵티마이저가 PK Index를 탈지 말지 결정한다.

 

 

PK Index는 아래와 같이 적용된다. 아래의 컬럼 조합으로 조회를 하면 PK index가 적용되는 것이다.

1. [PK_A, PK_B, PK_C]
2. [PK_A, PK_B]
3. [PK_A]

좀 더 자세히 살펴보자. 아래 모두 PK 인덱스가 적용되어 조회되는 케이스다.

# 복합키를 모두 조회할 때
# used_key_parts : PK_A, PK_B, PK_C
SELECT *
  FROM sample
 WHERE 1=1
   AND PK_A = 'aaa'
   AND PK_B = 'bbb'
   AND PK_C = 'ccc'
---
# 복합키의 순서를 다르게 적용했을 때
# used_key_parts : PK_A, PK_B, PK_C
# 복합키 생성 순서의 조합으로 컬럼이 존재한 한다면 where절에 기재하는 순서는 옵티마이저의 플랜에 영향을 주지 않는다. (그래도 예쁘게 순서대로 작성하자)
SELECT *
  FROM sample
 WHERE 1=1
   AND PK_C = 'ccc'
   AND PK_A = 'aaa'
   AND PK_B = 'bbb'
---
# 복합키를 두 개만 사용했을 때
# used_key_parts : PK_A, PK_B
SELECT *
  FROM sample
 WHERE 1=1
   AND PK_A = 'aaa'
   AND PK_B = 'bbb'
--
# 복합키를 하나만 사용했을 때
# used_key_parts : PK_A
SELECT *
  FROM sample
 WHERE 1=1
   AND PK_A = 'aaa'

즉, 조건절에 복합키의 순서대로 조합하지 않는다면 PK 인덱스는 타지 않거나 일부만 탄다. 아래 케이스를 봐보자.

# 첫번째와 세번째 컬럼으로 조회했을 때는
# used_key_parts : PK_A
# 복합키를 하나만 사용했을 때와 마찬가지로 PK_A에 대해서만 index가 걸리고, 
# PK_C 컬럼에 대해서는 index_merged 된다.
# PK_A로 필터를 해도 데이터가 굉장히 많다면, 이후 PK_C컬럼에 대해 필터를 할 때에는 속도가 느릴 것이다.
SELECT *
  FROM sample
 WHERE 1=1
   AND PK_A = 'aaa'
   AND PK_C = 'ccc'
---
# 두번째와 세번째 컬럼으로 조회했을 때
# full access table
# PK 인덱스가 전혀 타지 않고 테이블을 처음부터 끝까지 순회
SELECT *
  FROM sample
 WHERE 1=1
   AND PK_B = 'bbb'
   AND PK_C = 'ccc'

만 건 이하의 테이블에서는 full scan을 해도 밀리초가 소요된다. 하지만 데이터가 굉장히 많고 한번에 조회/처리해야하는 양이 많다면 PK 인덱스가 제대로 타지 않을 경우 성능 이슈가 발생한다.

PK 인덱스는 테이블 설계 시점부터 고민해야 한다. 설계 때부터 어떻게 조회할 것인 지를 먼저 생각해봐야 한다는 뜻이다.

---

복합키 설계 예제

어떻게 조회해야할 지를 알고 있다면 복합키 구성할 수 있다. 일반적으로는 복합키는 카디널리티가 낮은 쪽에서 은 쪽으로 구성한다.

1. 카디널리티가 낮은 순서(일반적인 경우)로 복합키 구성

예제 : 사용자별로 걸음수를 구한다.

 

복합키를 카디널리티가 낮은 순서로 구성함

 

• 입력 조건 : 사용자의 기록방법(애플건강, 샤오미핏, 삼성헬스 등)별로 기록일시가 동일하다면 update, 신규 기록일시라면 insert한다.흔히 UPSERT, MERGE문이라고 하는 SQL 문법이다. KEY가 중복된 경우에는 UPDATE를, 그렇지 않은 경우에는 INSERT를 하게 된다. 내부적으로 SELECT를 수행하기 때문에 조회 시 PK인덱스를 이용할 것이다.
• INSERT INTO user_step (user_id, record_type, recorded_at, count) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE count = VALUES(count)
• 조회 조건 : 사용자별로 특정 기간(당일, 최근 일주일, 1개월, 3개월) 동안의 평균 걸음 수를 구한다. 단, 입력방법별로 합계가 제일 큰 수를 기준으로 함. 카디널리티가 낮은 순서(중복이 많은 순)로 PK를 구성했더니, 조회할 때 PK 인덱스가 제대로 타지 않는다. user_id컬럼만 인덱스가 적용되고, recorded_at에는 인덱스가 적용되지 않았다. 사용자별로 걸음수가 5분 단위로 기록되기 때문에 user_id에만 인덱스가 걸린다면 몇 천, 몇 만의 recorded_at 컬럼 조회 시에는 성능 이슈가 발생한다.
• SELECT SUM(count) FROM user_step WHERE user_id = '사용자ID' AND recorded_at BETWEEN '이날부터' AND '이날까지' GROUP BY record_type

2. 인덱스를 탈 수 있도록 설계 변경

1. 사용자ID + 기록일시 조합으로 인덱스를 추가 ⇒ 한 테이블에 인덱스가 많이 걸려 있어도 문제가 된다. 빠르게 조회하기 위해 DB 내에서 데이터의 순서를 정렬해서 저장하는데, 데이터 insert/update가 될 때 마다 인덱싱을 다시 하기 때문이다.
2. 복합키 순서 변경 ⇒ 복합키의 순서를 적절하게 바꿔서 입력조건, 조회조건을 모두 충족시킬 수 있다.
3. 이렇게 변경한다면 Upsert문으로 데이터를 생성할때에는 사용자ID+기록일시+기록방법 PK가 모두 인덱스를 타게되고, 조회 시에는 사용자ID+기록일시 조합으로 PK 인덱스가 수행된다.

 

복합키의 순서를 변경 (기록일시 <-> 기록방법)

 

정리 : 복합키는 일반적으로 카디널리티가 낮은 순으로 구성하되, 조회/입력 방법에 따라서 적절히 순서를 바꿔서 구성하거나 새로 인덱스를 추가하면 된다. (근데 인덱스 덕지덕지도 안좋으니 주의)

---

JPA는 복합키를 알파벳 순으로 생성한다

방금 전 예제를 통해 복합키 순서를 적절히 구성한다면 PK 인덱스를 효율적으로 사용할 수 있다는 것을 알게 되었다.
하지만 JPA는 복합키를 알파벳 순으로 생성한다. 이것은 개발자의 의도와 다르게 PK index를 타지 않게 될 가능성이 크다.
위 예제와 동일하게 걸음수를 관리하는 Entity 클래스를 만들어보자.

@Getter
@NoArgsConstructor
@IdClass(UserStepId.class)
@Entity
public class UserStep {
    @Id
    @ManyToOne(targetEntity = User.class, fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", referencedColumnName = "id", insertable = false, updatable = false)
    private User user;

    @Id
        @Column(nullable = false, columnDefinition = "timestamp")
    private ZonedDateTime recordedAt;

    @Id
    private String record_type;

    private Long count;
}

JPA는 알파벳 순서대로 복합키를 생성하기 때문에 record_type,recorded_at, user_id로 구성된다.

record_type 컬럼을 조건절에 포함하지 않는 조회 쿼리의 경우 테이블 전체(full scan)를 돌며 데이터를 탐색하게 된다. 만 건 이하의 데이터는 인덱스를 타지 않아도 성능 차이가 크지 않으나 몇 십, 몇 백 만 건의 데이터를 조회할 때에는 당연히 성능 이슈가 발생할 것이다.

---

어떻게 JPA Entity의 복합키 순서를 정할까?

현재 JPA에서 복합키 생성 순서를 컨트롤 할 수 있는 방법은 없다. JPA를 통한다면 복합키는 무조건 알파벳 순서로 생성이 되는 것이다.
그래서 보통 아래와 같은 방법을 사용한다.

• 직접 DB에 DDL ⇒ 테이블의 Key는 한번 설정하면 다른 컬럼에 비해 변경할 일이 거의 없기 때문에, 한번만 DDL 쿼리를 직접 수행한다면 이후 변경은 JPA를 통해서 테이블을 조작하면 된다.
• 컬럼명을 알파벳 순서로 명명 ⇒ ㅎㅎ
• (미확인) 직접 DDL scripter 생성 ⇒ https://medium.com/chequer/jpa-hibernate-hbm2ddl-컬럼-순서-지정하기-e0c4421a8d32
• (미확인) Override the embeddable column names ⇒ https://stackoverflow.com/questions/27998082/can-i-tell-spring-data-jpa-hibernate-order-of-composite-primary-key

위 방법 외에 추가로 구글링해서 다양한 후보군을 찾아보고, 마음에 드는 방법을 자세하게 알아본 후 적용하길 바란다.

---

추천 자료

PK 인덱스에 대한 친절하고 자세한 내용은 아래 블로그를 참고하길 바란다.

https://jojoldu.tistory.com/243