개요
프로젝트 ERD 전반을 1차적으로 구성하고 팀원에게 피드백을 받던 와중, 위와 같이 VARCHAR 타입을 남발하는 설계에 대해 재고해보라는 의견을 들었다. 개인적으로 CHAR 는 고정 길이, VARCHAR 는 가변 길이로 데이터를 저장한다고 알고 있었다. 따라서 데이터 길이가 변경할 수 있으면 VARCHAR 를 사용하는 것이 효율적이라는 상식만 있었을 뿐 대용량 데이터를 저장할 시 고려해야할 점에 관해 너무 무지하다는 판단이 들었다.
MySQL을 기준으로 문자열 데이터 타입을 결정할 시 고려해야할 부분을 정리해보자.
최대 길이 65,535
Row Record Format
출처 : https://www.sobyte.net/post/2022-08/mysql-varchar/#&gid=1&pid=1
MySQL에서 row는 다음과 같은 형식으로 저장된다.
- 헤더 부분은 가변 길이 칼럼들의 길이를 나타내기 위해 쓰이며 칼럼의 길이가 255바이트 이하일 시 1바이트로, 255바이트보다 클 경우 2 바이트로 표현된다.
- 두번째 부분은 NULL flag로 NULL 값이 될 수 있는 열이 row에 존재하는 지 나타내고 만약 모든 칼럼이 NOT NULL로 설정되어 있다면 테이블 생성시 NULL flag가 생성되지 않는다.
- 세번째 부분은 고정된 5바이트 크기의 row 헤더이다.
- 네번째 부분은 row의 PK를 나타내는 부분으로 6바이트 크기이다. 테이블 생성시 PK가 정의되어 있지 않더라도 MySQL에서 자동 생성해준다.
- 마지막은 두 숨겨진 부분으로, TrasactionID는 row가 마지막으로 수정된 트랜잭션 ID를 저장하는데 쓰이며 6바이트의 크기이다. Roll Pointer는 롤백 포인터로 7바이트 크기이다.
최대 길이 65.535의 출처
앞서 살펴본 row 포맷에서 헤더 부분은 255바이트 이상의 크기를 2바이트로 나타낸다고 설명하였다. 따라서 다음 계산에 따라 표현할 수 있는 최대 크기가 결정된다.
이는 곧 row의 최대 크기가 칼럼의 길이를 제한한다고 판단할 수 있다.
CHAR vs VARCHAR
CHAR
- 선언한 길이에 대한 크기 고정, 지정된 길이보다 길이가 짧은 값이 저장될 경우 문자열 우측에 공백을 넣어 길이를 맞추어 저장한다.
- 최대 길이 255byte
VARCHAR
- 가변적 길이를 가진다.
- 최대 65.535byte까지 길이 가능
- 길이에 대한 정보를 저장하는 byte 필요
- 255 이하는 1byte, 이상은 2byte의 추가 크기가 필요하다.
기타 상식
- 유의할 점은
CHAR(n),VARCHAR(n)에서n은 바이트가 아니라, 글자 수를 의미한다.utf8mb4인코딩에서 칼럼은 byte의 공간을 차지할 수 있으며 따라서 한 열에 저장될 수 있는 최대 글자 수는 으로 측정할 수 있다.
- MySQL의 경우
CHAR추출시 공백을 제거한 상태로 추출한다. - 문자열 타입 열이 정렬시 MySQL에서는 공백이 제거된 상태로 비교를 진행한다. 다른 DB의 경우 공백을 유지한 채로 비교
VARCHAR vs TEXT
앞선 한 row에 저장될 수 있는 길이의 제약으로 인해 VARCHAR 칼럼이 너무 큰 길이를 사용하면, 다른 칼럼들이 사용할 수 있는 최대 공간의 크기가 영향을 받을 수 있다는 점을 알 수 있다.
VARCHAR vs TEXT
TEXT 는 앞서 살펴본 VARCHAR 타입의 길이 제한 문제를 해결할 수 있는 타입으로 이를 활용하면 훨씬 크고 유연하게 테이블을 생성할 수 있다. TEXT , BLOB 과 같은 대용량 데이터를 저장하는 칼럼 타입을 LOB (Large Object) 타입이라고 한다. RDBMS는 LOB 데이터를 Off-Page 라고 하는 외부 공간에 저장한다. 이에 일반적인 RDBMS와 같이 MySQL도 레코드의 칼럼 데이터는 B-Tree(Clustering Index 에 저장(Inline 저장)하지만, 용량이 큰 LOB 데이터는 B-Tree 외부의 Off-Page 페이지로 저장한다.
하지만 LOB 칼럼을 항상 Off-Page 로 저장하지 않고 길이가 길어서 저장 공간이 많이 필요할 경우에만 해당 방식을 이용한다.
MySQL 서버의 레코드 크기 제한은 65.535 바이트이지만, InnoDB 스토리지 엔진의 레코드 크기 제한은 페이지(블록)의 크기에 따라 달라지는데, 대부분 페이지 크기의 절반이 엔진의 최대 레코드 크기 제한으로 동작한다.
엔진은 레코드의 최대 크기가 이 제한 사항(16KB 페이지에서는 8,117바이트)을 초과하면 길이가 긴 컬럼을 선택해서 Off-Page 로 저장한다. 한편, VARCHAR 타입 칼럼도 저장된 값이 클 때 Off-Page 로 저장된다.
두 타입의 차이를 인덱스 생성 가능 여부로 설명하는 경우도 있다. 하지만 두 타입 다 사실 최대 크기 길이 제한만 충족하면 인덱스를 생성할 수 있다. (적절한 길이의 prefix 지정)
VARCHAR와 TEXT의 메모리 활용
MySQL 서버는 스토리지 엔진과 Handler API를 이용해 데이터를 주고 받는데, 이때 uchar*records[2] 메모리 포인터를 이용해 레코드 데이터를 주고 받는다. records[2] 메모리 객체는 실제 레코드 최대 크기에 상관 없이 최대 크기로 메모리를 할당해둔다. VARCHAR 의 경우 최대 크기가 설정되기 때문에 메모리 공간을 records[2] 버퍼에 미리 할당받아둘 수 있지만, TEXT 와 같은 LOB 의 경우 실제 최대 크기만큼 메모리를 할당해 두면 메모리 낭비가 너무 심해진다. 그래서 records[2] 포인터가 가리키는 메모리 공간은 VARCHAR 는 포함하지만 TEXT 칼럼을 위한 공간은 포함하지 않는다.
uchar*records[2] 공간은 테이블 구조체 내에 정의되어 있으며 이 구조체는 MySQL 서버 내부에 캐싱되어서 여러 커넥션에서 공유해서 사용될 수 있다. 즉, 처음 한 번 할당되면 많은 커넥션들이 재사용 가능하다.
하지만 TEXT 같은 LOB 칼럼을 위한 메모리 공간은 records[2] 에 미리 할당되어 있지 않아 매번 레코드를 읽고 쓸 때마다 필요한 만큼 메모리가 할당되어야 한다.
이에 LOB 타입의 칼럼을 읽을 시 미리 할당해 둔 메모리 공간이 없기 때문에 매번 필요한 크기만큼 ‘메모리 할당 - 사용후 해제’가 이뤄져야 한다. 이 때 사용되는 공간은 MySQL 8.0.33 기준으로 Performance_schema 에 의해서 측정되지 않는다. 따라서, LOB 용 메모리 할당 해제가 실행되는지 알 수 없어 성능 영향도를 파악하기가 어렵다.
한편, 또 주의해야 할 점은 VARCHAR 타입에 저장된 값이 길어서 Off-Page 로 저장된 경우 MySQL 서버는 records[2] 버퍼를 사용하지 못하고 새롭게 메모리 공간을 할당해서 사용한다. 그래서 VARCHAR 타입에 매우 큰 값이 빈번하게 저장되는 경우는 주의가 필요하다.
칼럼 선정 규칙
MySQL 서버의 내부적 작동에서 VARCHAR , TEXT 의 큰 차이점을 바탕으로, 타입을 선택할 때의 고려사항들을 다음과 같이 정리할 수 있다.
VARCHAR 선택
- 최대 길이가 상대적으로 크지 않은 경우
- 테이블 데이터를 읽을 때 항상 해당 칼럼이 필요한 경우
- DBMS 서버의 메모리가 (상대적으로) 충분한 경우
TEXT 선택
- 최대 길이가 상대적으로 큰 경우
- 테이블에 길이가 긴 문자열 타입 칼럼이 많이 필요한 경우
- 테이블 데이터를 읽을 때 해당 칼럼이 자주 필요치 않은 경우
DBMS 서버 스팩이나 데이터 모델, 유입되는 트래픽에 따라 영향도가 항상 달라질 수 있다. 이를 적절히 고려하여 결정해야 한다.
참고
