데이터 타입

log.yunsik·2023년 8월 15일

칼럼의 데이터 타입과 길이를 선정할 때 가장 주의할 사항

저장되는 값의 성격에 맞는 최적의 타입을 선정
가변 길이 칼럼은 최적의 길이를 지정
조인 조건으로 사용되는 칼럼은 똑같은 데이터 타입으로 선정

칼럼의 길이가 크게 선정되면 디스크 공간은 물론 메모리나 CPU 자원이 함께 낭비된다.
칼럼의 타입이 잘못 선정되거나 길이가 너무 부족하면 서비스 도중에 스키마 변경이 필요할 수도 있다.
항상 실제로 저장되는 값의 성격을 정확히 분석하고 최적의 타입과 길이를 선정하는 것이 중요하다.

15.1 문자열(CHAR와 VARCHAR)

15.1.1 저장 공간

우선 CHAR 와 VARCHAR 의 공통점은 문자열을 저장할 수 있는 데이터 타입이라는 점이고, 가장 큰 차이는 고정 길이냐 가변 길이냐다.

CHAR 타입은 이미 저장 공간의 크기가 고정적이다.
VARCHAR 타입은 저장된 값의 유효 크기가 얼마인지를 별도로 저장해 둬야 하므로 1~2바이트의 저장 공간이 추가로 더 필요하다.

VARCHAR 타입의 길이가 255바이트 이하이면 1바이트만 사용하고 256바이트 이상으로 설정되면 2바이트를 사용한다. VARCHAR 타입의 최대 길이는 2바이트로 표현할 수 있는 이상은 사용할 수 없다.
즉 VARCHAR 타입의 최대 길이는 65,536 바이트 이상으로 설정할 수 없다.

MySQL 에서는 하나의 레코드에서 TEXT와 BLOB 타입을 제외한 컬럼의 전체 크기가 64KB 를 초과할 수 없다. 테이블에 VARCHAR 타입의 컬럼 하나만 있다면 이 VARCHAR 타입의 최대 64KB 크기의 데이터를 저장할 수 있다. 하지만 이미 다른 컬럼에서 40KB의 크기를 사용하고 있다면 VARCHAR 타입은 24KB만 사용할 수 있다. 이때 24KB를 초과하는 크기의 VARCHAR 타입을 생성하려고 하면 에러가 발생하거나 자동으로 VARCHAR 타입이 TEXT로 대체된다.

문자열 타입의 저장 공간을 언급할 때는 1문자와 1바이트를 구분해서 사용한다. 1문자는 실제 저장되는 값의 문자 집합에 따라 1~4바이트까지 공간을 사용할 수 있기 때문이다. 위의 VARCHAR 타입의 컬럼 하나만 가지는 테이블의 예에서 VARCHAR 타입의 최대 64KB 크기의 데이터를 저장할 수 있다고 했는데 이 수치는 바이트 수를 의미하므로 실제 65,536 개의 글자를 저장할 수 있는 것은 아니다. 실제 저장되는 문자가 아시아권의 언어라면 저장 가능한 글자 수는 반으로 줄고 UTF-8 문자를 저장한다면 실제 저장 가능한 글자 수는 1/4로 줄어들 것이다.

VARCHAR 타입을 선택해도 기껏 1바이트만 더 사용할 뿐인데 항상 VARCHAR를 사용하는 것이 좋을까?
실제 문자열 값의 길이가 정적이냐 가변적이냐만으로 CHAR와 VARCHAR 타입을 결정하는 것은 적절하지 않다.

CHAR와 VARCHAR를 결정하는 기준

저장되는 문자열의 길이가 대개 비슷한가?
칼럼의 값이 자주 변경되는가?

CHAR(10) 에 ABCD 저장

fd2 컬럼은 정확히 10 바이트를 사용하면서 앞쪽의 4바이트만 유효한 값으로 채워졌고 나머지는 공백 문자로 채워져 있다.

VARCHAR(10) 에 ABCD 저장

fd2 컬럼은 5바이트의 공간을 차지하는데 첫 번째 바이트에는 저장된 칼럼값의 유효한 바이트 수인 숫자 4가 저장되고 두 번째 바이트부터 다섯 번째 바이트까지 실제 컬럼값이 저장된다.

fd2 컬럼을 "ABCDE"로 UPDATE 했다고 가정해보자.

CHAR(10) 타입을 사용하면 fd2 칼럼을 위해 공간이 10바이트가 준비돼 있으므로 그냥 변경되는 칼럼의 값을 업데이트만 하면 된다.
VARCHAR(10) 타입을 사용하면 fd2 컬럼에 4바이트밖에 저장할 수 없는 구조로 만들어져 있다. 그래서 "ABCDE"와 같이 길이가 더 큰 값으로 변경될 때는 레코드 자체를 다른 공간으로 옮겨서 저장해야 한다.

주민등록번호처럼 항상 값의 길이가 고정적일 때는 당연히 CHAR 타입을 사용해야 한다. 또한 값이 2~3바이트씩 차이가 나더라도 자주 변경될 수 있는 부서 번호나 게시물의 상태 값 등은 CHAR 타입을 사용하는 것이 좋다. 자주 변경돼도 레코드가 물리적으로 다른 위치로 이동하거나 분리되지 않아도 되기 때문이다. 레코드의 이동이나 분리는 CHAR 타입으로 인해 발생하는 2~3바이트 공간 낭비보다 더 큰 공간이나 자원을 낭비하게 만든다.

CHAR 와 VARCHAR 키워드 뒤에 인자로 전달하는 숫자 값의 의미를 알아야 한다.
MySQL에서 CHAR와 VARCHAR 뒤에 지정하는 숫자는 그 컬럼의 바이트 크기가 아니라 문자의 수를 의미한다. 즉 CHAR(10) 또는 VARCHAR(10)으로 컬럼을 정의하면 이 컬럼은 10 바이트를 저장할 수 있는 공간이 아니라 10 글자를 저장할 수 있는 공간을 의미한다. 그래서 CHAR(10) 타입을 사용하더라도 이 컬럼이 실제로 디스크나 메모리에서 사용하는 공간은 각각 달라진다.

일반적으로 영어를 포함한 서구권 언어는 각 문자가 1바이트를 사용하므로 10바이트를 사용한다.
한국어나 일본어와 같은 아시아권 언어는 각 문자가 최대 2바이트를 사용하므로 20바이트를 사용한다.
UTF-8과 같은 유니코드는 최대 4바이트까지 사용하므로 40바이트까지 사용할 수 있다.

MySQL 서버에는 utf8과 utf8mb4 문자 집합이 별도로 존재한다. utf8 문자 집합은 1~3 바이트까지만 저장을 지원했기 때문에 SMP와 SIP, 그리고 그 이후 플레인 문자는 저장할 수 없었다. 하지만 이모티콘의 발전으로 이는 예상외로 심각한 문제가 되어버렸다. 그래서 utf8mb4 라는 새로운 문자 집합을 도입했다. utf8mb4 문자 집합은 최대 4바이트까지의 문자를 저장할 수 있기 때문에 유니코드에서 지원하는 대부분의 문자를 지원했다. utf8mb4는 utf8 문자 집합의 수퍼 셋이개 때문에 utf8 문자 집합을 사용하는 테이블을 utf8mb4 문자 집합으로 전환하는 것은 아무런 문제를 유발하지 않는다. utf8mb3은 utf8을 가르치는 별칭으로 정의돼 있다.

15.1.2 저장 공간과 스키마 변경

MySQL 서버에는 데이터가 변경되는 도중에도 스키마 변경을 할 수 있도록 Online DDL 이라는 기능을 제공한다. 하지만 모든 스키마 변경이 온라인으로 가능한 것은 아니며 변경 작업의 특성에 따라 SELECT 는 가능하지만 INSERT 나 UDPATE 같은 데이터 변경은 허용되지 않을 수도 있다. VARCHAR 데이터 타입을 사용하는 칼럼의 길이를 늘리는 작업은 길이에 따라 매우 빠르게 처리될 수도 있지만 어떤 경우에는 테이블에 대해 읽기 잠금을 걸고 레코드를 복사하는 작업이 필요할 수도 있다.

칼럼의 타입을 VARCHAR(63) 으로 늘리는 경우는 잠금 없이 매우 빠르게 변경된다.
하지만 VARCHAR(64)로 변경하는 경우에는 읽기 잠금이 걸리고 상당히 오랜 시간이 걸리게 된다.

이러한 차이가 발생하는 이유는 VARCHAR 타입의 컬럼이 가지는 길이 저장 공간의 크기 때문이다. VHARCHAR(60)은 utf8mb4 문자 집합을 사용하는 VARCHAR(60) 칼럼은 최대 길이가 240 바이트이기 때문에 문자열 값의 길이를 저장하는 공간은 1바이트면 된다. 하지만 VARCHAR(64) 타입은 저장할 수 있는 문자열의 크기가 최대 256 바이트까지 가능하기 때문에 문자열 길이를 저장하는 공간의 크기가 2바이트로 바뀌어야 한다. 이처럼 문자열 길이를 저장하는 공간의 크기가 바뀌게 되면 MySQL 서버는 스키마 변경을 하는 동안 읽기 잠금을 걸어서 아무도 데이터를 변경하지 못하도록 막고 테이블의 레코드를 복사하는 방식으로 처리한다.

이러한 이유로 문자열 타입의 컬럼을 설계할 때는 앞으로 요건이 바뀌어서 VARCHAR 타입의 길이가 크게 변경될 것으로 예상된다면 길이 저장 공간의 크기가 바뀌지 않도록 미리 조금 크게 설계하는 것이 좋다. 레코드 건수가 많은 테이블에서 읽기 잠금을 필요로 하는 스키마 변경을 실행하기 위해서는 스키마를 변경할 때마다 서비스를 점검 모드로 바꿔야 할 수도 있으며 이는 서비스의 가용성을 훼손한다.

15.1.3 문자 집합

MySQL 서버에서 각 테이블의 칼럼은 모두 서로 다른 문자 집합을 사용해 문자열 값을 저장할 수 있다. 문자 집합은 문자열을 저장하는 CHAR, VARCHAR, TEXT 타입의 컬럼에만 설정할 수 있다. MySQL에서 최종적으로는 칼럼 단위로 문자 집합을 관리하지만 관리의 편의를 위해 MySQL 서버와 DB 그리고 테이블 단위로 기본 문자 집합을 설정할 수 있는 기능을 제공한다. 즉 테이블의 문자 집합을 UTF-8로 설정하면 컬럼의 문자 집합을 별도로 지정하지 않아도 해당 테이블에 속한 컬럼은 UTF-8 문자 집합을 사용한다. 물론 테이블의 기본 문자 집합이 UTF-8이라고 하더라도 각 컬럼에 대해 문자 집합을 EUC-KR 이나 ASCII 등으로 별도 지정할 수 있다.

한국에서 MySQL을 사용한다면 대부분 euckr이나 utf8mb4 만으로도 충분할 것이다.

euckr은 한국어 전용으로 사용되는 문자 집합으로 모든 글자는 1~2바이트를 사용한다.
utf8mb4은 다국어 문자를 포함할 수 있는 컬럼에 적합하다.

15.1.4 콜레이션

콜레이션은 문자열 컬럼의 값에 대한 비교나 정렬 순서를 위한 규칙을 의미한다. 즉 비교나 정렬 작업에서 영문 대소문자를 같은 것으로 처리할지 아니면 더 크거나 작은 것으로 판단할지에 대한 규칙을 정의하는 것이다.

MySQL의 모든 문자열 타입의 칼럼은 독립적인 문자 집합과 콜레이션을 가진다. 각 칼럼에 대해 독립적으로 문자 집합이나 콜레이션을 지정하든 그렇지 않든 독립적인 문자 집합과 콜레이션을 가지는 것이다. 각 컬럼에 대해 독립적으로 지정하지 않으면 MySQL 서버나 DB의 기본 문자 집합과 콜레이션이 자동으로 설정된다. 콜레이션이란 문자열 컬럼의 값을 비교하거나 정렬하는 기준이 된다. 그래서 각 문자열 컬럼의 값을 비교하거나 정렬할 때는 항상 문자 집합뿐 아니라 콜레이션의 일치 여부에 따라 결과가 달라지며 쿼리의 성능 또한 상당한 영향을 받는다.

15.1.5 비교 방식

MySQL에서 문자열 컬럼을 비교하는 방식은 CHAR와 VARCHAR가 거의 같다. CHAR 타입의 컬럼에 SELECT를 실행했을 때 다른 DBMS 처럼 사용되지 않는 공간에 공백 문자가 채워져서 나오지 않는다. 그리고 MySQL 서버에서 지원하는 대부분의 문자 집합과 콜레이션에서 CHAR 타입이나 VARCHAR 타입을 비교할때 공백 문자를 뒤에 붙여서 두 문자열의 길이를 동일하게 만든 후 비교를 수행한다.

mysql> SELECT 'ABC' = 'ABC      ' AS is_equal;

true

mysql> SELECT 'ABC' = '    ABC' AS is_equal;

false

문자열 앞쪽에 위치하는 공백 문자는 유효한 문자로 비교된다. 이러한 문자열 비교 방식은 문자열의 크다 작다 비교와 문자열 비교 함수인 STRCMP() 에서도 똑같이 적용된다.

하지만 utf8mb4 문자 집합이 UCA 버전 9.0.0을 지원하면서 문자열 뒤에 붙어있는 공백 문자들에 대한 비교 방식이 달라졌다. utf8mb4_bin 콜레이션을 사용하는 경우 문자열 뒤에 붙어있는 공백은 비교에 영향을 미치지 않는다. 하지만 utf8mb4_0900_bin 콜레이션을 사용하는 경우 문자열 뒤의 공백이 비교 결과에 영향을 미친다.

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
mysql> SELECT 'a ' = 'a';

true

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_0900_bin;
mysql> SELECT 'a ' = 'a';

false

이는 지금까지 MySQL 서버의 문자열 비교 규칙에 큰 영향을 미치는 변경이므로 utf8mb4 문자 집합을 사용하는 경우 주의해야 한다. 문자열 뒤의 공백이 비교 결과에 영향을 미치는지 아닌지는 다음과 같이 information_schema 데이터베이스의 COLLATIONS 뷰에서 PAD_ATTRIBUTE 칼럼의 값으로 판단할 수 있다.

문자열 비교의 경우 예외적으로 LIKE를 사용한 문자열 패턴 비교에서는 공백 문자가 유효 문자로 취급된다.

mysql> SELECT 'ABC    ' LIKE 'ABC' AS is_same_pattern;

false

mysql> SELECT '    ABC' = 'ABC' AS is_same_pattern;

false

mysql> SELECT 'ABC    ' = 'ABC%' AS is_same_pattern;

true

15.1.6 문자열 이스케이프 처리

MySQL 에서 SQL 문장에 사용하는 문자열은 프로그래밍 언어처럼 "\"를 이용해 이스케이프 처리를 하는 것이 가능하다. 즉 "\t"나 "\n"으로 탭이나 개행문자를 표시할 수 있다.

"\%" 와 "_" 는 LIKE를 사용하는 패턴 검색 쿼리의 검색어에서만 사용할 수 있다. LIKE 패턴 검색어에서는 "%"와 ""를 와일드 카드를 표현하기 위한 패턴 문자로 사용하므로 실제 "%" 문자나 "" 문자를 검색하려면 "\"를 이용해 이스케이프 처리를 해야 한다.

MySQL에서는 다른 DBMS에서와 같이 홑따옴표와 쌍따옴표의 경우에는 홑따옴표나 쌍따옴표를 두 번 연속으로 표기해서 이스케이프 처리할 수도 있다.

15.2 숫자

숫자를 저장하는 타입은 값의 정확도에 따라 크게 참값과 근삿값 타입으로 나눌 수 있다.

참값은 소수점 이하 값의 유무와 관계없이 정확히 그 값을 그대로 유지하는 것을 의미한다. 참값을 관리하는 데이터 타입으로는 INTEGER를 포함해 INT로 끝나는 타입과 DECIMAL이 있다.
근삿값은 흔히 부동 소수점이라 불리는 값을 의미하며 처음 칼럼에 저장한 값과 조회된 값이 정확하게 일치하지 않고 최대한 비슷한 값으로 관리하는 것을 의미한다. 근삿값을 관리하는 타입으로는 FLOAT와 DOUBLE이 있다.

또한 값이 저장되는 포맷에 따라 십진 표기법과 이진 표기법으로 나눌 수 있다.

이진 표기법이란 흔히 프로그래밍 언어에서 사용하는 정수나 실수 타입을 의미한다. 이진 표기법은 한 바이트로 한 자리 또는 두 자리 숫자만 저장하는 것이 아니라 256까지의 숫자를 표현할 수 있기 때문에 숫자 값을 적은 메모리나 디스크 공간에 저장할 수 있다.
십진 표기법은 숫자 값의 각 자릿값을 표현하기 위해 4비트나 한 바이트를 사용해서 표기하는 방법이다. 이는 우리가 흔히 이야기하는 십진수가 아니라 디스크나 메모리에 십진 표기법으로 저장된다는 것을 의미한다. MySQL의 십진 표기법을 사용하는 타입은 DECIMAL 뿐이며 DECIMAL 타입은 금액처럼 정확하게 소수점까지 관리돼야 하는 값을 저장할 때 사용한다. 또한 DECIMAL 타입은 65자리 숫자까지 표현할 수 있으므로 BIGINT로도 저장할 수 없는 값을 저장할 때 사용된다.

DBMS에서는 근삿값은 저장할 때와 조회할 때의 값이 정확히 일치하지 않고, 유효 자릿수를 넘어서는 소수점 이하의 값은 계속 바뀔 수 있다. 특히 STATEMENT 포맷을 사용하는 복제에서는 소스 서버와 레플리카 서버 간 데이터 차이가 발생할 수도 있다. MySQL 에서 FLOAT나 DOUBLE과 같은 부동 소수점 타입은 잘 사용하지 않는다. 또한 십진 표기법을 사용하는 DECIMAL 타입은 이진 표기법을 사용하는 타입보다 저장 공간을 2배 이상을 필요로 한다. 매우 큰 숫자 값이나 고정 소수점을 저장해야 하는것이 아니라면 일반적으로 INTEGER나 BIGINT 타입을 자주 사용한다.

15.2.1 정수

DECIMAL 타입을 제외하고 정수를 저장하는 데 사용할 수 있는 데이터 타입으로는 5가지가 있다.

정수 타입은 UNSIGNED라는 칼럼 옵션을 사용할 수 있다. 정수 칼럼을 생성할 때 UNSIGNED 옵션을 명시하지 않으면 기본적으로 음수와 양수를 동시에 저장할 수 있는 숫자 타입이 된다. 하지만 UNSIGNED 옵션이 설정된 정수 컬럼에서는 0보다 큰 양의 정수만 저장할 수 있게 되면서 저장할 수 있는 최댓값은 SIGNED 타입보다 2배 더 커진다. AUTO_INCREMENT 칼럼과 같이 음수가 될 수 없는 값을 저장하는 칼럼에 UNSIGNED 옵션을 명시하면 작은 데이터 공간으로 더 큰 값을 저장할 수 있다.

물론 정수 타입에서 UNSIGNED 옵션은 조인할 때 인덱스의 사용 여부까지 영향을 미치지는 않는다. 즉 UNSINGED 정수 컬럼과 SIGNED 정수 컬럼을 조인할 때 인덱스를 이용하지 못한다거나 하는 문제는 발생하지 않는다. 하지만 서로 저장되는 값의 범위가 다르므로 외래 키로 사용하는 컬럼이나. 조인의 조건이 되는 컬럼은 SIGNED나 UNSIGNED 옵션을 일치시키는 것이 좋다.

15.2.2 부동 소수점

MySQL에서는 부동 소수점을 저장하기 위해 FLOAT과 DOUBLE 타입을 사용할 수 있다. 부동 소수점이라는 이름에서 부동은 소수점의 위치가 고정적이지 않다는 의미인데 숫자 값의 길이에 따라 유효 범위의 소수점 자릿수가 바뀐다. 그래서 부동 소수점을 사용하면 정확한 유효 소수점 값을 식별하기 어렵고 그 값을 따져서 크다 작다 비교를 하기가 쉽지 않은 편이다. 부동 소수점은 근삿값을 저장하는 방식이라서 동등 비교는 사용할 수 없다.

FLOAT는 일반적으로 정밀도를 명시하지 않으면 4바이트를 사용해 유효 자릿수를 8개까지 유지하며, 정밀도가 명시된 경우에는 최대 8바이트까지 저장공간을 사용할 수 있다. DOUBLE의 경우 8바이트의 저장 공간을 필요로하며 최대 유효 자릿수를 16개까지 유지할 수 있다.

복제에 참여하는 MySQL 서버에서 부동 소수점을 사용할 때는 특별히 주의해야 한다. 부동 소수점 타입의 데이터는 MySQL 서버의 바이너리 로그 포맷이 STATEMENT 타입인 경우 복제에서 소스 서버와 레플리카 서버 간의 데이터가 달라질 수 있다. 물론 유효 정수부나 소수부는 달라지지 않겠지만 우효 정수부나 소수부를 눈으로 판별하기는 쉽지 않다.

부동 소수점 값을 저장해야 한다면 유효 소수점의 자릿수만큼 10을 곱해서 정수로 만들어 그 값을 정수 타입의 칼럼에 저장하는 방법도 생각해볼 수 있다. 소수점으로 된 좌푯값에 10000을 곱해서 저장하고 조회할 때는 10000으로 나눈 결과를 사용하면 된다.

15.2.3 DECIMAL

부동 소수점에서 유효 범위 이외의 값은 가변적이므로 정확한 값을 보장할 수 없다. 금액이나 대출이자 등과 같이 고정된 소수점까지 정확하게 관리해야 할 때는 FLOAT나 DOUBLE 타입을 사용해서는 안 된다. 그래서 소수점의 위치가 가변적이지 않은 고정 소수점 타입을 위해 DECIMAL 타입을 제공한다.

MySQL에서 소수점 이하의 값까지 정확하게 관리하려면 DECIMAL 타입을 이용해야 한다. DECIMAL 타입은 숫자 하나를 저장하는데 1/2 바이트가 필요하므로 한 자리나 두 자릿수를 저장하는데 1바이트가 필요하고 세자리나 네 자리 숫자를 저장하는데는 2바이트가 필요하다. 즉 DECIMAL 로 저장하는 (숫자의 자릿수) / 2 의 결괏값을 올림 처리한 만큼의 바이트 수가 필요하다. 그리고 DECIMAL 타입과 BIGINT 타입의 값을 곱하는 연산을 간단히 테스트해 보면 아주 미세한 차이지만 DECIMAL 보다는 BIGINT 타입이 더 빠르다는 사실을 알 수 있다. 결론적으로 소수가 아닌 정숫값을 관리하기 위해 DECIMAL 타입을 사용하는 것은 성능상으로 공간 사용면에서 좋지 않다. 단순히 정수를 관리하고자 한다면 INTEGER나 BIGINT를 사용하는 것이 좋다.

15.2.4 정수 타입의 칼럼을 생성할 때의 주의사항

부동 소수점이나 DECIMAL 타입을 이용해 컬럼을 정의할 때는 타입의 이름 뒤에 괄호로 정밀도를 표시하는 것이 일반적이다. 예를 들어 DECIMAL(20, 5) 라고 정의하면 정수부를 15(=20-5)자리까지 그리고 소수부를 5자리까지 저장할 수 있는 DECIMAL 타입을 생성한다. 그리고 DECIMAL(20)이라고 정의하는 경우에는 소수부 없이 정수부만 20자리까지 저장할 수 있는 타입의 컬럼을 생성한다. DECIMAL 타입은 저장 공간의 크기가 가변적인 데이터 타입이어서 DECIMAL 타입에 사용하는 정밀도는 저장 가능한 자릿수를 결정함과 동시에 저장 공간의 크기까지 제한한다.

MySQL 5.7 버전 까지는 정수 타입도 BIGINT(10) 과 같이 괄호로 값의 크기를 명시할 수 있는 문법을 지원했다. 하지만 MySQL 8.0 부터는 정수 타입에 화면 표시 자릿수를 사용하는 기능은 제거됐다. 그래서 정수 타입 뒤에 사용된 (10) 은 무시된다.

15.2.5 AUTO_INCREMENT 옵션

테이블의 프라이머리 키를 구성하는 칼럼의 크기가 너무 크거나 프라이머리 키로 사용할 만한 칼럼이 없을 때는 숫자 타입의 칼럼에 자동 증가 옵션을 사용해 인조 키(Artivical Key)를 생성할 수 있다. MySQL 서버의 auto_increment_increment와 auto_increment_offset 시스템 설정을 이요해 AUTO_INCREMENT 칼럼의 자동 증가값이 얼마가 될지 변경할 수 있다. 일반적으로 이 두 시스템 변숫값은 모두 1로 사용되지만 auto_increment_offset 을 5로 auto_increment_increment 를 10으로 변경하면 자동 생성되는 값은 5, 15, 25, 35, 45 와 같이 증가한다.

AUTO_INCREMENT 옵션을 사용한 칼럼은 반드시 그 테이블에서 프라이머리 키나 유니크 키의 일부로 정의해야 한다. 그런데 프라이머리 키나 유니크 키가 여러 개의 컬럼으로 구성되면 AUTO_INCREMENT 속성의 컬럼값이 증가하는 패턴이 MyISAM 스토리지 엔진과 InnoDB 스토리지 엔진에서 각각 달라진다.

MyISAM 스토리지 엔진을 사용하는 테이블에서는 자동 증가 옵션이 사용된 칼럼이 프라이머리 키나 유니크 키의 아무 위치에나 사용될 수 있다.
InnoDB 스토리지 엔진을 사용하는 테이블에서는 AUTO_INCREMENT 칼럼으로 시작되는 인덱스를 생성해야 한다. 즉 InnoDB 테이블에서 AUTO_INCREMENT 칼럼을 프라이머리 키의 뒤쪽에 배치하면 오류가 발생한다.

AUTO_INCREMENT 칼럼은 테이블당 하나만 사용할 수 있다. AUTO_INCREMENT 칼럼이 없는 테이블에 새로운 AUTO_INCREMENT 칼럼을 추가하면 새로 추가된 칼럼은 1부터 자동으로 증가된 값이 할당된다. AUTO_INCREMENT 칼럼의 현재 증가 값은 테이블의 메타 정보에 저장돼 있는데, 다음 증가 값이 얼마인지는 SHOW CREATE TABLE 명령으로 조회할 수 있다.

15.3 날짜와 시간

MySQL 에서는 날짜만 저장하거나 시간만 따로 저장할 수도 있으며 날짜와 시간을 합쳐서 하나의 칼럼에 저장할 수 있게 여러 가지 타입을 지원한다. MySQL에서 지원하는 날짜나 시간에 관련된 데이터 타입으로 DATE와 DATETIME 타입이 많이 사용된다.

MySQL 5.6 버전부터 TIME 타입과 DATETIME, TIMESTAMP 타입은 밀리초 단위의 데이터를 저장할 수 있게 됐다. 그래서 컬럼의 저장 공간 크기는 밀리초 단위를 몇 자리까지 저장하느냐에 따라 달라진다.

다음과 같이 밀리초 단위는 2자리당 1바이트씩 공간이 더 필요하다. 그래서 MySQL 8.0에서는 마이크로초까지 저장 가능한 DATETIME(6) 타입은 8바이트 (5바이트 + 3바이트)를 사용한다.

밀리초 단위로 데이터를 저장하기 위해서는 다음과 같이 DATETIME이나 TIME, TIMESTAMP 타입 뒤에 괄호와 함께 숫자를 표기하면 된다. NOW() 함수를 이용해 현재 시간을 가져올 때도 NOW(6) 또는 NOW(3) 과 같이 가져올 밀리초의 자릿수를 명시해야 한다. 그렇지 않고 NOW() 로 현재 시간을 가져오면 자동으로 NOW(0)으로 실행되어 밀리초 단위는 0으로 반환된다.

MySQL의 날짜 타입은 칼럼 자체에 타임존 정보가 저장되지 않으므로 DATETIME이나 DATE 타입은 현재 DBMS 커넥션의 타임존과 관계없이 클라이언트로부터 입력된 값을 그대로 저장하고 조회할 때도 변환 없이 그대로 출력한다. 하지만 TIMESTAMP는 항상 UTC 타임존으로 저장되므로 타임존이 달라져도 값이 자동으로 보정된다.

MySQL 서버의 컬럼 타입이 TIMESTAMP 이든 DATETIME 이든 관계없이 JDBC 드라이버는 날짜 및 시간 정보를 MySQL 타임존에서 JVM 타임존으로 변환해서 출력하는 것을 확인할 수 있다. 사실 자바의 ResultSet 클래스에서 MySQL 서버의 DATETIME 컬럼의 값을 온전히 가져올 수 있는 함수가 getTimestamp() 뿐이기 때문에 DATETIME이나 TIMESTAMP 타입 컬럼 모두 타임존으로 변환이 된 것이기도 하다. 그런데 DATETIME 타입 칼럼의 값을 다른 방식으로 가져온다면 타임존 변환이 되지 않을 수도 있다. 단순히 조회뿐만 아니라 으용 프로그램에서 데이터베이스로 날짜 및 시간 데이터를 저장할 때도 동일한 규칙이 적용된다.

요즘은 Hibernate나 MyBatis 등과 같은 ORM 을 사용하기 때문에 ORM 코드 내부적으로 값을 자동으로 페치해서 응용 프로그램 코드로 반환한다. 이렇게 ORM 을 사용하는 경우에는 DATETIME 타입의 칼럼값을 어떤 JDBC API를 이용해서 페치하는지, 그리고 타임존 변환이 기대하는 대로 자동 변환하는지를 실제 응용프로그램에서 테스트해 볼 것을 권장한다. 최대한 응용 프로그램에서 시간 정보를 강제로 타임존으로 변환을 하거나 MySQL 서버의 SQL 문장으로 CONVERT_TZ() 함수를 이용해 타임존 변환을 하지 않도록 하자.

15.3.1 자동 업데이트

MySQL 5.6 이전 버전까지는 TIMESTAMP 타입의 칼럼은 레코드의 다른 칼럼 데이터가 변경될 때마다 시간이 자동으로 업데이트되고 DATETIME은 그렇지 않은 차이를 가지고 있었다. 하지만 MySQL 5.6 버전부터는 TIMESTAMP와 DATETIME 칼럼 모두 INSERT와 UPDATE 문장이 실행될 때마다 해당 시점으로 자동 업데이트되게 하려면 테이블을 생성할 때 칼럼 정의 뒤에 다음 옵션을 정의해야 한다.

"DEFAULT CURRENT_TIMESTAMP" 옵션은 레코드가 INSERT 될 때의 시점을 자동으로 업데이트하며 "ON UDPATE CURRENT_TIMESTAMP" 옵션은 해당 레코드가 UPDATE될 때의 시점을 자동으로 업데이트하게 해준다.

15.4 ENUM과 SET

ENUM과 SET은 모두 문자열 값을 MySQL 내부적으로 숫자 값으로 매핑해서 관리하는 타입이다. 일반적으로 데이터베이스를 사용하다보면 타입이나 상태 등과 같이 수많은 코드 형태의 칼럼을 사용하게 되는데 실제 데이터베이스에는 이미 인코딩된 알파벳이나 숫자 값만 저장되므로 그 의미를 바로 파악하기가 쉽지 않다는 단점이 있다.

15.4.1 ENUM

ENUM 타입은 테이블의 구조에 나열된 목록 중 하나의 값을 가질 수 있다. ENUM 타입의 가장 큰 용도는 코드화된 값을 관리하는 것이다.

ENUM 타입을 사용할 때 일반적으로 특정 문자열 값이 어떤 정숫값으로 매핑됐는지 알 필요가 없다. 하지만 필요하다면 1을 곱한다거나 0을 더하는 산술연슨을 적용하는 방법으로 ENUM 타입의 실제 값을 확인할 수 있다. ENUM 타입에서 매핑되는 정숫값은 일반적으로 테이블 정의에 나열된 문자열 순서대로 1부터 할당되며 빈 문자열 ("")은 항상 0으로 매핑된다. 프로그램의 성격에 따라 다르겠지만 MySQL을 사용하는 프로그램에서는 별도의 코드 테이블을 사용하지 않을 때가 많다. 이때 실제 테이블에 저장된 코드 값이 어떤 의미인지 이해하기 쉽지 않은데, ENUM 타입은 이러한 단점을 보완할 수 있는 상당히 유용한 티입이라고 생각한다. ENUM 타입은 저장해야 하는 아이템 값이 길면 길수록 저장 공간을 더 많이 절약할 수 있다.

하지만 ENUM 타입의 가장 큰 단점은 칼럼에 저장되는 문자열 값이 테이블 구조가 되면서 기존 ENUM 타입의 새로운 값을 추가해야 한다면 테이블 구조를 변경해야 한다는 점이다. MySQL 5.6 버전부터는 새로 추가하는 아이템이 ENUM 타입의 제일 마지막으로 추가되는 형태라면 테이블의 구조 변경만으로 즉시 완료된다.

기존 ENUM 타입 아이템들이 순서가 변경되거나 중간에 새로운 아이템이 추가되는 경우에는 COPY 알고리즘에 읽기 잠금까지 필요하다. ENUM 테이블이 매우 크다면 가독성이 좀 떨어지더라도 새로운 아이템을 ENUM 타입의 마지막에 추가하는 것이 MySQL 서버의 가용성을 높이는 방법이다.

ENUM 타입은 우리가 일반적으로 사용하는 상태나 카테고리와 같이 코드화된 칼럼을 MySQL이 자체적으로 제공하는 기능이다. 그래서 ENUM 타입의 칼럼값으로 정렬을 수행하면 매핑되기 전의 문자열 값 기준으로 정렬되는 것이 아니라 매핑된 코드 값으로 정렬이 수행된다. ENUM 타입은 마치 CHAR나 VARCHAR와 같은 문자열 타입처럼 보이지만 사실은 정수 타입의 컬럼이기 때문이다. 가장 좋은 방법은 ENUM 타입의 컬럼에 대해서는 정렬을 수행하지 않는 것이다. 하지만 꼭 ENUM 타입의 인코딩된 값이 아니라 문자열 기준으로 정렬해야 한다면 테이블을 생성할 때 필요한 정렬 기준으로 ENUM 타입의 문자열 값을 나열하면 된다.

15.4.2 SET

SET 타입도 테이블의 구조에 정의된 아이템을 정숫값으로 매핑해서 저장하는 방식은 똑같다. SET과 ENUM의 차이는 하나의 칼럼에 1개 이상의 값을 저장할 수 있다는 점이다. MySQL 서버는 내부적으로 BIT-OR 연산을 거쳐 1개 이상의 선택된 값을 저장한다. 즉 SET 타입의 컬럼은 여러 개의 값을 저장할 수는 있지만 실제 여러 개의 값을 저장하는 공간을 가지는 것이 아니다. 그래서 각 아이템 값에 매핑되는 정숫값은 1씩 증가하는 정숫값이 아니라 2n의 값을 갖게 된다. SET 타입은 아이템 값의 멤버 수가 8개 이하면 1바이트 저장 공간을 사용하며 9개에서 16개 이하면 2바이트를 사용하고 똑같은 방식으로 최대 8바이트까지 저장공간을 사용한다.

SET 타입의 칼럼에 대해 동등 비교를 수행하려면 칼럼에 저장된 순서대로 문자열을 나열해야만 검색할 수 있다. 또한 SET 타입의 칼럼에 인덱스가 있더라도 동등 비교 조건을 제외하고 FIND_IN_SET() 함수나 LIKE 를 사용하는 쿼리는 인덱스를 사용할 수 없다.

ENUM 과 마찬가지로 SET 타입 또한 기존 SET 타입에 정의된 아이템 중간에 새로운 아이템을 추가하는 경우 테이블 읽기 잠금과 리빌드 작업이 필요한다.

하지만 SET 타입의 마지막에 새로운 아이템을 추가하는 작업은 ENUM과 동일하게 INSTANT 알고리즘으로 메타 정보만 변경하고 즉시 완료한다. 하지만 SET 타입의 아이템 개수가 8개를 넘어서서 9개로 바뀔때는 읽기 잠금과 테이블 리빌드 작업이 필요한다. 이는 SET 타입을 저장하기 위한 공간이 1바이트에서 2바이트로 변경돼야 하기 때문이다.

15.5 TEXT와 BLOB

MySQL에서 대량의 데이터를 저장하려면 TEXT나 BLOB 타입을 사용해야 하는데 이 두 타입은 많은 부분에서 거의 똑같은 설정이나 방식으로 작동한다. TEXT나 BLOB 타입의 유일한 차이점은 TEXT 타입은 문자열을 저장하는 대용량 칼럼이라서 문자 집합이나 콜레이션을 가진다는 것이고 BLOB 타입은 이진 데이터 타입이라서 별도의 문자 집합이나 콜레이션을 가지지 않는다는 것이다.

LONG이나 LONG VARCHAR라는 타입도 있는데 MEDIUMTEXT의 동의어이므로 기억할 필요는 없다. 이진 데이터를 저장하기 위한 데이터 타입과 문자열을 저장하기 위한 데이터 타입은 다음과 같이 고정 길이나 가변 길이 타입이 정확하게 매핑된다.

MySQl의 TEXT 타입은 오라클에서 CLOB라고 하는 대용량 타입과 동일한 역할을 하는 데이터 타입이므로 TEXT와 BLOB 칼럼 모두 사용할 때 주의하고 남용해서는 안 된다. TEXT와 BLOB 타입은 주로 다음과 같은 상황에서 사용하는 것이 좋다.

칼럼 하나에 저장되는 문자열이나 이진 값의 길이가 예측할 수 없이 클 때 TEXT나 BLOB을 사용한다. 하지만 다른 DBMS 와는 달리 MySQL 에서는 값의 크기가 4000바이트를 넘을 때 반드시 BLOB 이나 TEXT를 사용해야 하는 것은 아니다. MySQL 에서는 레코드의 전체 크기가 64KB를 넘지 않는 한도 내에서 VARCHAR 나 VARBINARY의 길이는 제한이 없다. 그래서 용도에 따라서 다음 예제와 같이 4000바이트 이상의 값을 저장하는 칼럼도 VARCHAR나 VARBINARY 타입을 이용할 수 있다.
MySQL에서는 하나의 레코드는 전체 크기가 64KB를 넘어설 수 없다. VARCHAR나 VARBINARY 와 같은 가변 길이 칼럼은 최대 저장 기능 크기를 포함해 64KB로 크기가 제한된다. 레코드의 전체 크기가 64KB를 넘어서서 더 큰 칼럼을 추가할 수 없다면 일부 컬럼을 TEXT나 BLOB 타입으로 전환해야 할 수도 있다.

추가 정리하기

15.7 JSON 타입

MySQL 5.7 버전부터 JSON 데이터를 저장할 수 있는 JSON 타입이 지원되기 시작했으며 MySQL 8.0 버전으로 업그레이드되면서 많은 기능과 성능 개선 사항이 추가됐다. 물론 MySQL 서버에서 TEXT 컬럼이나 BLOB 칼럼에 JSON 데이터를 저장할 수도 있었다. 하지만 MySQL 5.7 버전부터 지원되는 JSON 타입의 칼럼은 JSON 데이터를 문자열로 저장하는 것이 아니라 MongoDB와 같이 바이너리 포맷의 BSON으로 변환해서 저장한다.

15.7.1 저장 방식

MySQL 서버는 내부적으로 JSON

log.yunsik

이전 포스트

옵티마이저와 힌트

다음 포스트