article thumbnail image
Published 2022. 4. 21. 19:43

우리는 PK 에 왜 int 형 데이터를 고집할까

Data/Data Engineering
반응형

우리는 PK 에 왜 int 형 데이터를 고집할까

최근에는 분석용 DB는 Bigquery 나 Snowflake, Redshift 등을 많이 쓰는 것으로 보이나 운영용 DB(원천 DB)는 여전히 Aurora, RDS 또는 자체 서버를 구축하여 사용하는 경우가 많다고 생각한다. 데이터 인프라나 DB 관련 얘기를 나누다 보면 “Server 에서 생성하는 unique ID(nchar) 값을 PK로 잡아 저장하는 게 데이터 분석가 입장에선 훨씬 편할 텐데 굳이 운영 DB에 int type id(idx) 컬럼을 만들어 PK를 따로 잡아야 할까요?”라는 질문을 받곤 한다.

 

이러한 주제가 나올 때마다 int 형을 추천했지만 정작 완벽하게 이유를 설명하지는 못했던 것 같다. 차후에 이러한 대화가 다시 생길 땐 적어도 더 논리적으로 설명 할 수 있도록 갖고 있는 지식 + 검색한 내용을 바탕으로 내용을 정리하고자 포스팅을 작성한다.

  • 본 글은 기본적으로 MySQL 을 베이스로 내용이 전개됩니다.
  • 잘 모르기에 더 잘 알고자 기록하는 글로 일부 잘못된 정보가 포함될 수 있습니다.

 

기본키(Primary Key)

Primary Key(이하 PK) 는 해당 테이블 내에서 각 행을 구별하는 가장 기본적인 값이 된다. 때문에 PK는 1개 이상 column의 조합으로 구성할 수 있지만 RDB 특성상 테이블 하나에 PK 가 다수일 수는 없다.PK 는 기본적으로 해당 테이블 내에서 절대적으로 Unique 하며, Null 값을 가질 수 없다. 이때 PK로 지정할 수 있는 column 또는 column의 조합을 candidate key라고 부른다.

 

가령 회원 정보 table이라면 회원 ID, 핸드폰 번호, 주민등록번호, 가입일, . 이 존재할 텐데 이때 candidate key는 회원 ID 와 주민등록번호를 꼽을 수 있다.

 

PK로 지정된 컬럼은 자동으로 B-tree Index 로 설정되게 되어 있고, 이 때문에 PK가 있는 테이블과 없는 테이블 조회에서 성능 차이를 보인다. 또한 unique라는 특성 때문에 데이터의 무결성을 확보하는데도 의의가 있다.

Index 와 B-tree

Index

DB에서 index 는 단어 그대로 색인자의 역할을 한다. 보통 한 개의 테이블에는 수십 개의 column이 존재하며, MySQL은 데이터 조회 시 가장 첫 번째 column부터 차례대로 검색하기 시작한다. 만약 index 가 존재하지 않는다면 데이터를 조회할 때 언제나 테이블 전체를 Full scan 해야 하는데 이는 비용과 성능 면에서 매우 비효율적이다.

 

하지만 데이터가 Create Update Delete 되는 경우 Index도 함께 수정돼야 하기 때문에 index가 없는 경우보단 효율이 떨어진다. 즉 read 성능을 살리고 cud를 성능을 희생한다고 볼 수 있다. 따라서 잦은 수정이 필요한 테이블의 경우는 오히려 index를 활용하지 않는게 더 이로울 수 있다.

B-tree / B+ -tree

<made by author>

MySQL 은 PK 를 설정하면 해당 값을 index 로 잡아 데이터를 B-tree 구조로 저장한다.(실제로는 B+tree)

 

B-tree 는 Root - Branch - Leaf 형태를 갖는데, 이 때 모든 Leaf 노드가 같은 레벨(수준)에 위치하도록 Balance 를 맞추기 때문에 B-tree 라고 칭한다. B-tree 에서 각각의 노드는 N 개의 자식을 갖을 수 있고, N이 홀수냐 짝수냐에 따라 다른 알고리즘이 적용된다.

 

B-tree 에서 데이터를 찾는 경우 Root 노드부터 시작하여 검색하려는 값과 key point 값을 비교하며 Branch - Leaf 로 차례대로 찾아 내려간다. 따라서 원하는 값을 찾을 때 까지 모든 자료를 순회해야 하는 단점이 있다.

 

B+ tree는 각각의 노드들은 index 값만 가진 상태에서 최하단 leaf 에 실제 데이터가 존재하며 linked list 로 연결되어 있다. B+ tree는 각 상위 node에는 index 값(key)만 저장하고 데이터는 leaf 노드에 존재하기 때문에 하나의 저장 공간(page)에 더 많은 key 값을 저장할 수 있어 효율적이며 범위 검색에 훨씬 유리하지만 언제나 최하단 leaf 까지 접근해야 하는 단점이 있다.

 

PK 를 int 로 잡으면 좋은 이유

<made by author>

MySQL 은 INNO DB storage engine 으로 되어 있으며, Index 와 Data 는 On-Disk 구조위에 존재한다. 디스크 특성상 데이터를 읽어낼 때 블럭 단위로 읽게 된다. 만약 블럭 크기가 1,024kb 라면 1kb만 들어있던 1,024kb가 가득 차있던 읽는 사이즈는 1,024kb 가 된다.

INNO DB에서 각 데이터를 저장하는 공간(page) 는 기본적으로 innodb_page_size setting 을 건들지 않는다면 16KB의 크기를 갖는다. 또한 전체 공간을 fully 사용하지 않고 Insert 또는 Update 상황을 대비해 1/16의 공간(1KB) 는 비워둔다. 때문에 한 page에 저장할 수 있는 데이터의 양은 15,000Byte 정도 된다고 볼 수 있다.

< MySQL Documentation >

int type은 항상 4byte의 크기를 갖고, UNSIGNED 속성을 가질 때 4,294,967,294개의 unique 한 값을 표현할 수 있다. char type은 1글자당 1byte를 사용하는데 만약 int type과 같은 개수의 unique 한 데이터를 알파벳 조합으로 표현하기 위해서는 최소 6byte의 크기가 요구된다. 따라서 1개 page 에 저장할 수 있는 index의 갯수는 int index table = 15,000/4 = 3,750 개, char index table = 15,000/6 = 2,500 개가 된다.

 

즉, 똑같이 3,000개의 데이터를 조회할 때 int 를 index로 잡으면 1개의 page만 조회하면 되지만, char를 index로 잡은 테이블은 2개의 page를 조회해야 하는 비효율이 발생한다. index 저장을 위한 storage 공간 역시 같은 데이터를 저장할 때 int를 사용한 테이블이 더 적은 page가 필요하므로 훨씬 효율적이다.

 

새롭게 index가 추가되거나 update 되는 경우 정렬과 node balance 를 맞춰야 하는데, 이 과정에서 page split이 발생할 수 있다. 문제는 split 과정에서 비효율이 발생할 수 있다는 점이다. 가령 page 2 중간에 데이터가 insert 돼야 하고 이미 해당 page가 꽉 찬 상태라면 한 칸씩 뒤로 밀면서 다음 page로 데이터를 넘기면 될 것 같으나 실제로는 아예 새로운 page를 만들게 된다. 이 때문에 위에서 언급한 page split 상황에서 공간을 제대로 활용하지 못하는 비효율이 발생한다.

 

int type 은 char type에 비해 조회시에도 이점을 갖는다. int type은 <,>,= 으로 빠르게 비교가 가능하지만 char type은 “%something” 이나 “something%” 와 같은 방법으로 비교해야 하는데, 이 경우 가장 앞글자부터 순회비교를 하기에 성능이 떨어진다.

 

또한 char type은 Whit space (’ ’) 도 1 byte 로 취급한다.(ver 5.0 ≥) 따라서 ‘a’ ≠ ‘a ‘ 라는 결과가 나타나 공간 효율성을 망칠뿐 아니라 데이터의 무결성에도 영향을 미친다.

 

varchar 같은 가변형 값을 PK로 지정해 index로 쓴다면 앞서 말한 공간 확보 문제 등에서 조금 더 자유로워지니 괜찮지 않은가? 라고 생각할 수 있다. 만약 user id라는 컬럼이 ‘a’ 부터 가입한 이용자에게 차례로 부여되고 이것을 PK로 잡은 테이블이 있다고 생각해보자. 초기에는 분명 int로 사용할 때보다 더 효율적인 부분이 존재할 수 있다. 하지만 신규 이용자가 늘어날수록 user id는 길어질 수밖에 없다. 이는 곧 신규 이용자 증가에 비례해 Index field size가 커지는 것을 뜻하며 DB 성능도 비례해 하락할 것을 의미하기 때문에 좋다고 볼 수 없다. 또한 현실적으로 대부분 회사에서 생성되는 ID 값은 (꽤 긴) 고정된 길이를 갖는 경우가 많기에 Int보다 효율을 내는 경우는 거의 없다고 생각한다.

 

참고자료

 
반응형
저작자표시
복사했습니다!

티스토리툴바