데이터베이스 성능 개선 - 채번에 대한 이해(Primary Key 컬럼의 용도)

 

프로젝트를 진행하다 보면 테이블을 설계하고 주키(primary key)를 설정하게 된다. 주키는 레코드들을 구별하는 고유한 값이며 테이블당 하나 이상의 컬럼으로 설정되어야 한다. 즉, 주키에 의해 레코드들을 구분할 수 있는 것이다. 

하지만 딱히 주키로 설정할만한 고유한 컬럼이 없다면 어떻게 할까? 그럴 때 '채번'을 이용하는 것이다.

채번은 'serial number' 즉, DB의 번호표이다. 채번은 일본의 영향을 받은 말로서 새로운 번호를 딴다는 의미이다.

데이터베이스에서 채번은 대부분 PK 컬럼의 용도로 사용한다. 보통은 위에서 언급한 데로 의미 있는 컬럼들을 PK로 사용하지만, 가끔 필요에 의해 아무런 의미가 없는 시스템적 일련번호 형식의 채번을 PK 컬럼으로 사용하기도 한다. 

채번을 사용하는 것이 좋냐 않좋냐는 상황에 따라 달라진다. 자식 테이블이 많다면 한 컬럼만 FK가 되기 때문에 조인이 많을 시 유리하다. 그리고 순차적으로 계속 증가해 업무적으로 PK를 지정하기 힘든 경우에 사용하면 좋다. 하지만 Insert시 유일하게 관리할 부담도 존재한다.

 

그렇다면 이제 채번을 생성하는 방식에 대해 알아보겠다.

 

채번을 생성하고 관리하는 채번 테이블을 이용하는 방식

제목에 나와있듯이 채번을 관리하는 테이블을 생성하고 관리하는 것이다.

즉 각 테이블의 채번을 구분하는 값을 PK로 두고 채번컬럼을 가지는 테이블을 만드는 곳이다. 

그리고 특정 테이블에 insert가 일어난다면 채번테이블에서는 그 테이블에 있는 특정 테이블을 가리키는 채번값에 1을 더해줘서 update를 시켜주면 된다. 

즉, 아래와 같은 흐름이다.

1. 채번 테이블에 채번을 update한다.
2. 대상 테이블에 채번 값을 insert하다.
3. commit한다.

위와 같은 방식은 중복에러의 가능성은 없고 순차적인 데이터입력이 가능하다. 하지만 채번 테이블 관리에 대한 부담과 잠금현상에 의한 성능 부하가 있다.

위의 1~3 순서로 트랜잭션이 진행하는 동안 다른 트랜잭션이  대기하는 현상을 잠금현상이라 하고 성능 저하의 원인이 될 수 있다. 

아래는 채번 테이블을 이용하는 방식의 예시이다.

 

출처: 개발자를 위한 인덱스 생성과 SQL 작성 노하우(이병국)

 

 

테이블에서 최댓값을 이용하는 방식

이 방식은 간단하다. insert를 할 레코드의 채번에는 MAX(컬럼) + 1 의 값을 넣어주면 된다. 이 방식은 추가적인 테이블 관리의 부담도 적고 순차적인 데이터 입력이 가능하다. 하지만 큰 문제가 있는데 바로 트랜잭션이 동시에 발생하는 중복 에러의 가능성이 존재한다는 것이다. 

예를들면 특정시점에 두 사용자가 데이터를 추가해서 insert작업이 실행된다면 어떻게 될까? 서로 이전의 채번값을 다룰텐데 둘다 이전의 값+1의 값을 채번에 저장하게 되니 에러가 발생할 것이다. 

이런 경우 PK를 추가로 구성해 아래와 같은 방법으로 중복 에러를 피할 수 있다. 

 

INSERT INTO 사용자, MAX(컬럼) + 1 ...
INSERT INTO 구분코드, MAX(컬럼) + 1 ...

 

 

이 예처럼 사용자별로 채번을 할수도 있고 구분코드별로 채번을 할 수도 있다. 

필자는 JPA를 사용하면서 트랜잭션의 낙관적 락과 위의 중복에러가 많이 혼란스러웠었다. 왜냐하면 낙관적 락은 최초커밋을 인정하는지와 마지막 커밋을 인정하는지에 대한 내용인데 위의 중복에러처럼 사용자가 동시에 처리하는 경우가 같기 때문이다. 

낙관적 락에 대한 내용은 https://khdscor.tistory.com/m/25 를 참고하길 바란다.

JPA - 트랜잭션과 락

 

하지만 중복 에러는 insert 시에 같은 채번값을 가진 두 레코드가 생기기에 발생하는 에러이고 낙관적 락이 발생할 때 최초 커밋인지 마지막 커밋인지에 구분은 데이터를 update할때 어느 커밋을 가지고 update를 할지에 대한 내용이므로 둘다 다른 내용이었다. 참고로 알아두면 좋을 것이다. 

 

 

시퀀스를 이용하는 방식

시퀀스는 각 DB마다 지원하는 DB도 있고 지원하지 않는 DB도 있다. 성능이 좋고 중복 에러나 잠금 가능성도 없다. 시퀀스를 이용한다는 말은 단순히 DB내 테이블을 접근할 때마다 채번이 1씩 자동으로 증가하는 것이다. 

하지만 이런 방식에도 단점이있는데 첫째로 시퀀스는 오브젝트이므로 관리에 대한 부담이 있다는 것이다. 두번 째로는 순차적 데이터 입력이 불가능하다는 것이다.

시퀀스 오브젝트는 읽기만 해도 증가하는 특징을 가지고 있다. 그렇기에 한번 insert하고 여러번 select한후 새로 insert를 한다면 두개의 insert되는 레코드의 채번 값은 연속적이지 않을 것이다. 

여러 테이블에서 하나의 시퀀스를 사용할 수도 있겠지만 가능하면 하나의 테이블당 하나의 시퀀스를 생성해 사용하는 것이 좋다.

 

 

각 채번 방식에 대한 장단점을 비교하면 아래와 같다

 

구분	채번 테이블 방식	테이블 최댓값 방식	시퀀스 방식
장점	중복 에러 없음 순차적 입력 가능	빠른 성능 순차적 입력 가능 관리 부하 없음	빠른 성능 중복 에러 없음 잠금 현상 없음
단점	잠금 현상 발생 성능 저하 관리 부하 존재	중복 에러 존재	순차적 입력 불가 관리 부하 존재​

 

 

위와 같이 각각의 방식에는 장단점이 존재하고 상황에 맞게 적합한 방식을 사용하면 될 것이다.

 

 

 

참고

https://devbab.tistory.com/m/15

23. 효율적인 채번 방식을 통한 성능 향상

 

https://bamdule.tistory.com/m/243

[JPA] 기본키 자동 생성 전략(Primary Key)

 

개발자를 위한 인덱스 생성과 SQL 작성 노하우 - 이병국