Column(칼럼)의 데이터 타입을 선정하는 작업은 물리 모델링에서 빼놓을 수 없는 중요한 작업이다.
그렇다면 칼럼의 데이터 타입을 선정할 때 가장 주의해야 할 사항은 무엇이 있을까?
- 저장되는 값의 성격에 맞는 최적의 타입을 선정하라.
- (varchar 같은) 가변 길이 칼럼은 최적의 길이를 지정하라.
- 조인 조건으로 사용되는 칼럼은 똑같은 데이터 타입으로 선정하라.
15.1 문자열 (CHAR vs VARCHAR)
문자열 칼럼을 사용할 때는 우선 CHAR 타입과 VARCHAR 타입 중 어떤 타입을 사용할지 결정해야 한다.
잘 알다시피 가장 큰 차이는 고정 길이냐, 가변 길이냐다.
15.1.1 저장 공간
결론부터 말하자면 문자열 값의 길이가 어느정도 일정하거나, 값이 자주 변경된다면 CHAR을 사용하고,
길이가 가변적이거나, 값이 자주 변경되지 않는다면 VARCHAR을 사용하는 것이 일반적이다.
머리 아프게 CHAR의 길이를 정하는 것보다 간단하게 VARCHAR을 사용하면 좋을텐데 왜 CHAR을 사용하는 것일까?
두개의 타입에 똑같은 값 "ABC" 를 넣으면 내부적으로 디스크에는 그림 15.1과 같이 저장될 것이다.
한 눈에 보기에는 VARCHAR가 공간을 절약하는 것 같아 좋아보인다.
하지만 중요한 것은 레코드 한 건이 저장된 상태가 아니라 이 값이 변경될 때, 어떤 현상이 발생하느냐다.
이 값을 "ABCDE"로 변경했다고 가정해보자.
1) CHAR(5) 타입을 사용하면 공간이 딱 2글자 남아 있으므로 그냥 변경되는 값을 업데이트 하면 된다.
2) 반면 VARCHAR(5) 타입을 사용하면, "ABCDE"와 같이 길이가 더 큰 값으로 변경될 때는
레코드 자체를 다른 공간으로 옮겨서(Row migration) 저장해야 한다.
레코드의 이동이나 분리는 CHAR 타입으로 인해 발생하는 2~3바이트 정도의 공간 낭비보다
더 큰 공간이나 자원을 낭비하게 만든다!
때문에 항상 길이가 고정적인 주민등록번호나
값이 자주 변경될 수 있는 부서 번호나 게시물의 상태 값 등은 CHAR 타입을 사용하는 것이 좋다.
※ 참고
MySQL에서 CHAR나 VARCHAR 뒤에 지정하는 숫자는 그 칼럼의 바이트 크기가 아니라 문자의 개수를 의미한다.
15.1.4 콜레이션(Collation)
콜레이션은 문자열 칼럼의 값에 대한 비교나 정렬 순서를 위한 규칙이다.
원래 콜레이션의 이름은 3개 또는 2개의 파트로 구성되어 있었다.
3개로 구성된 경우 (euckr_korean_ci)
- 첫 번째 파트 : 문자 집합의 이름 (ascii, euckr, utf8, utf8mb4)
- 두 번째 파트 : 해당 문자집합의 하위 분류
- 세 번째 파트 : ci(Case Insensitive)는 대소문자를 구분하지 않고, cs(Case Sensitive)는 대소문자를 별도의 문자로 구분한다.
2개로 구성된 경우 (utf8_bin)
- 첫 번째 파트 : 문자 집합의 이름 (ascii, euckr, utf8, utf8mb4)
- 두 번째 파트 : bin이라는 키워드를 사용하는데 이는 이진 데이터를 뜻하며, 별도의 규칙 없이 문자 데이터의 바이트 값을 기준으로 정렬을 수행한다.
최근 자주 사용하는 문자 집합인 utfmb4는 콜레이션의 이름이 더 복잡해졌다.
1) 0900, 520 같은 숫자는 [문자 비교 규칙인 UCA]의 버전을 의미한다.
2) ai(Accent Insensitive)는 악센트를 구분하지 않고, as(Accent Sensitive)는 악센트를 별도의 문자로 구분한다.
15.2 숫자
숫자를 저장하는 타입은 크게 값의 정확도에 따라 참값(Exact value)과 근사값 타입으로 나눌 수 있다
- 참값 : 소수점 이하 값의 유무에 관계없이 정확히 그 값을 그대로 유지하는 것을 의미한다. (INTEGER, BIGINT, DECIMAL)
- 근사값 : 부동소수점, 즉 처음 칼럼에 저장한 값과 나중에 조회된 값이 정확하게 일치하지 않고 매번 조금씩 다른 값 (FLOAT, DOUBLE)
15.2.2 부동 소수점
MySQL에서 FLOAT이나 DOUBLE과 같은 부동 소수점 타입은 동등 비교를 사용할 수 없고,
소스 서버와 레플리카 서버 간의 데이터가 달라질 수 있기 때문에 잘 사용하지 않는다.
15.2.3 DECIMAL
따라서 금액이나 대출이자 같은 중요한 데이터를 부동 소수점 대신 고정 소수점으로 정확하게 관리하고 싶다면
DECIMAL 타입을 사용하면 된다. 디지털 논리 수업에서 배우는 BCD Code 같은 개념이다.
DECIMAL(20, 5)라고 정의하면 정수부를 15자리까지, 소수부를 5자리까지 저장할 수 있는 DECIMAL 타입을 생성한다.
그 대신 DECIMAL 타입은 이진 표기법을 사용하는 타입보다 저장 공간을 2배 이상 필요로 한다.
따라서 매우 큰 숫자 값이나 고정 소수점을 저장해야 하는 것이 아니라면,
일반적으로 정수를 저장하거나 관리할 때는, INTEGER나 BIGINT 타입을 사용하는 것이 좋다!
15.3 날짜와 시간
MySQL 5.6 버전부터 TIME, DATETIME, TIMESTAMP 타입은 밀리초 단위의 값을 관리할 수 있는 기능이 추가됐다.
따라서 밀리초 단위로 데이터를 저장하기 위해서는 타입뒤에 괄호와 숫자를 표기하면 된다. NOW() 함수도 똑같다.
EX) DATETIME(6), NOW(6) - 마이크로초까지 관리
DATETIME이나 DATE 타입은 현재 DBMS 커넥션의 타임존에 관계없이
클라이언트로부터 입력된 값을 그대로 저장하고, 조회할 때도 변환 없이 그대로 출력한다.
하지만 TIMESTAMP는 항상 UTC 타임존으로 저장되므로 타임존이 달라져도 값이 자동으로 보정된다.
system_time_zone 시스템 변수는 MySQL 서버의 타임존을 의미하며,
일반적으로 이 값은 운영체제의 타임존을 그대로 상속받는다.
time_zone 시스템 변수는 MySQL 서버로 연결하는 모든 클라이언트 커넥션의 기본 타임존을 의미한다.
system_time_zone과 time_zone 시스템 변수는 MySQL 서버를 시작할 때,
--timezone과 --default-time_zone 명령행 옵션으로 변경할 수 있다.
15.5 TEXT와 BLOB
MySQL에서 대량의 데이터를 저장하려면 TEXT나 BLOB 타입을 사용해야 하는데,
이 두 타입은 많은 부분에서 거의 똑같은 설정이나 방식으로 작동한다.
TEXT 타입과 BLOB 타입의 유일한 차이점은
TEXT 타입은 문자열을 저장하는 대용량 칼럼이라 문자 집합이나 콜레이션을 가진다는 것이고,
BLOB 타입은 이진 데이터 타입이라서 별도의 문자 집합이나 콜레이션을 가지지 않는다는 것이다.
TEXT나 BLOB은 칼럼 하나에 저장되는 문자열이나 이진 값의 길이가 예측할 수 없이 클 때 사용해야 한다.
예를 들어, MySQL에서는 일반적으로 하나의 레코드는 전체 크기가 64KB를 넘을 수 없다.
VARCHAR나 VARBINARY와 같은 가변 길이 칼럼은 최대 저장 가능 크기를 포함해 64KB로 크기가 제한된다.
따라서 레코드의 전체 크기가 64KB를 넘어서서 더 큰 칼럼을 추가할 수 없다면 일부 칼럼을 TEXT나 BLOB 타입으로 전환해야 한다.
TEXT나 BLOB과 같은 칼럼이 포함된 테이블에서는
SELECT * FROM ... 과 같은 쿼리 보다는 꼭 필요한 칼럼만 조회하는 쿼리를 사용하길 권장한다고 한다.
15.7 JSON 타입
MySQL 5.7 버전에서 JSON을 처음 지원한 이후,
MySQL 8.0으로 업그레이드되면서 JSON 타입에 대한 많은 기능과 성능 개선 사항이 추가 되었다.
15.7.1 저장 방식
JSON 타입의 칼럼은 JSON 데이터를 문자열로 저장하는 것이 아니라,
MongoDB와 같이 바이너리 포맷의 BSON(Binary JSON)으로 변환해서 저장한다.
그래서 JSON 데이터를 BLOB이나 TEXT 타입의 칼럼에 저장하는 것보다 공간 효율이 높은 편이다.
MySQL 서버에서 매우 큰 용량의 JSON 도큐먼트(Document)가 저장되면,
MySQL 서버는 16KB 단위로 여러 개의 데이터 페이지로 나뉘어서 저장된다.
15.7.2 부분 업데이트 성능
MySQL 8.0 버전부터는 JSON 타입에 대해 부분 업데이트(Partial Update) 기능을 제공한다.
부분 업데이트 기능은 JSON_SET(), JSON_REPLACE(), JSON_REMOVE() 함수를 이용해
JSON 도큐먼트의 특정 필드 값을 변경하거나 삭제하는 경우에만 작동한다.
예를 들어,
1MB JSON 데이터를 저장해도 MySQL 서버는 16KB 데이터 페이지를 64개나 사용하게 된다.
부분 업데이트의 경우 그 중 단 한 페이지만 변경하면 되지만,
그렇지 않다면, 64개 데이터 페이지를 다시 디스크로 기록해야 한다.
15.7.4 JSON 칼럼 선택
이 책은 처음부터 끝까지 MySQL 서버의 성능에 가장 큰 중점을 두고 있다!
JSON 칼럼과 정규화된 칼럼(일반적인 칼럼)중에도 성능을 중심으로 판단한다면 정규화된 칼럼을 추천한다!
정규화된 칼럼은
1) 칼럼의 이름을 메타 정보로만 저장하기 때문에 칼럼의 이름이 별도의 공간을 차지하지 않는다.
2) BLOB이나 TEXT와 같이 대용량 데이터의 경우 외부 페이지로 관리된다.
이것을 응용 프로그램과 적절히 활용하면 메모리 효율이나 쿼리의 성능을 훨씬 더 끌어올릴 수 있다.
그렇다고 해서 JSON 칼럼의 장점이 전혀 없는 것은 아니다!
1) 각 레코드가 가지는 속성들이 너무 다르고, 다양하지만 레코드별로 선택적으로 값을 가지는 경우라면
가능한 모든 속성에 대한 칼럼을 만드는 것보다는 JSON 칼럼을 만들어서 저장하는 것이 좋다.
2) 또한 너무 정규화된 테이블 구조를 유지하면 테이블 개수도 많아지고, 응용 프로그램의 코드도 더불어 길어진다.
이런 경우에도 중요도가 낮은 데이터라면, JSON 칼럼에 비정규화된 형태로 데이터를 저장할 수도 있다.
'데이터베이스 > MySQL' 카테고리의 다른 글
| [Real MySQL 8.0 2권] 12장. 확장 검색 (0) | 2023.03.15 |
|---|