데이터 타입

칼럼의 데이터 타입을 선정하는 작업은 물리 모델링에서 빼놓을 수 없는 중요한 작업이다. 칼럼의 데이터 타입과 길이를 선정할 때 가장 주의할 사항은 다음과 같다.

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

칼럼의 데이터 타입을 선정할 때 실제 저장되는 값의 특성을 고려하지 않고 가능한 최대 길이 값을 기 준으로 칼럼의 길이를 선택하는 것이 일반적이다. 하지만 무분별하게 칼럼의 길이가 크게 선정되면 디 스크의 공간은 물론 메모리나 CPU의 자원도 함께 낭비된다. 또한 그로 인해 SQL의 성능이 저하되는 것은 당연한 결과다. 칼럼의 타입이 잘못 선정되거나 길이가 너무 부족하면 서비스 도중에 스키마 변경이 필요할 수도 있다. 그런데 스키마 변경 작업은 서비스 중지나 읽기 전용 모드로의 전환 작업이 필요할 수도 있다. 데이터 타입의 길이는 너무 넉넉하게 선택해도 문제가 되고, 부족하게 선택해도 문제가 된다. 항상 실제로 저장되는 값의 성격을 정확히 분석하고 최적의 타입과 길이를 선정하는 것이 중요하다.

CHAR와 VARCHAR

문자열 칼럼을 사용할 때는 우선 CHAR 타입과 VARCHAR 타입 중 어떤 타입을 사용할지 결정해야 한다. 지금까지 모든 DBMS에서 CHAR나 VARCHAR 타입을 구분해서 제공하는 것을 보면 그만큼의 장단점을 가지고 있음을 짐작할 수 있다.

MySQL에서 CHAR(n)과 VARCHAR(n)에서 괄호 안의 값은 문자 수를 의미한다.

저장 공간 크기

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

• 고정 길이(CHAR): 실제 입력되는 칼럼값의 길이에 따라 사용하는 저장 공간의 크기가 변하지 않는다.
• 가변 길이(VARCHAR): 최대로 저장할 수 있는 값의 길이는 제한돼 있지만, 그 이하 크기의 값이 저장되면 그만큼 저장 공간이 줄어든다. 하지만 저장된 값의 유효 크기를 별도로 저장해야 하므로 1~2바이트의 저장 공간이 추가로 더 필요하다.

하나의 글자를 저장하기 위해 CHAR(1)과 VARCHAR(1) 타입을 사용할 때 실제 사용되는 저장 공간의 크기를 한번 살펴보자. 우선 두 문자열 타입 모두 한 글자를 저장할 때 사용하는 문자 집합에 따라 실제 저장 공간을 1~4바이트까지 사용한다. 여기서 하나의 글자가 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 타입으로 대체된다. 그래서 칼럼을 새로 추가할 때는 VARCHAR 타입이 TEXT 타입으로 자동으로 변환되지 않았는지 확인해 보는 것이 좋다.

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

문자열 값의 길이가 항상 일정하다면 CHAR를 사용하고 가변적이라면 VARCHAR를 사용하는 것이 일반적 이다. 왜 길이가 고정적일 때 CHAR를 사용하면 좋을까? VARCHAR 타입을 선택해도 기껏 디스크에서 1바 이트만 더 사용할 뿐인데, 이렇게 고민해가면서 시간을 투자할 가치가 있는 것일까? 실제 문자열 값의 길이가 정적이냐 가변적이냐만으로 CHAR와 VARCHAR 타입을 결정하는 것은 적절하지 않다.

문자열의 변경

CHAR 타입과 VARCHAR 타입을 결정할 때 중요한 판단 기준은 다음과 같다.

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

CHAR와 VARCHAR 타입의 선택 기준은 값의 길이도 중요하지만, 해당 칼럼의 값이 얼마나 자주 변경되느냐가 기준이 돼야 한다. 칼럼의 값이 얼마나 자주 변경되느냐가 왜 중요한지 한번 살펴보자.

CHAR(9)와 VARCHAR(9)에 "CHOCO"를 저장하면 다음과 같다.

하지만 중요한 것은 레코드 한 건이 저장된 상태가 아니라 이름 칼럼의 값이 변경될 때 어떤 현상이 발생하느냐다. 칼럼의 값을 "CHOCOCHIP"으로 UPDATE했다고 가정해 보자.

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

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

다른 DBMS에 익숙한 사용자에게는 상당히 혼란스러울 수 있는데, MySQL에서 CHAR나 VARCHAR 뒤에 지정하는 숫자는 그 칼럼의 바이트 크기가 아니라 문자의 수를 의미한다. 즉, CHAR(10) 또는 VARCHAR(10)으로 칼럼을 정의하면 이 칼럼은 10바이트를 저장할 수 있는 공간이 아니라 10글자(문자)를 저장할 수 있는 공간을 의미한다. 그래서 CHAR(10) 타입을 사용하 더라도 이 칼럼이 실제로 디스크나 메모리에서 사용하는 공간은 각각 달라진다.

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

MySQL 서버에서는 utf8과 utf8mb4 문자 집합이 별도로 존재한다. utf8 문자 집합은 MySQL 5.5 이전 버전 까지 UTF-8 인코딩을 저장하기 위해서 사용됐으며, utf8mb4는 MySQL 5.5 버전부터 지원되기 시작했다. MySQL 서버의 utf8 문자 셋은 한 글자당 최대 3바이트까지만 지원됐다. UTF-8 인코딩에서는 각 문자가 저장 공간에 따라 대략 다음과 같이 구분된다.

• 1바이트 저장 공간 사용: 아스키(ASCII) 문자
• 2바이트 저장 공간 사용: 추가 알파벳 문자
• 3바이트 저장 공간 사용: BMP(Basic Multilingual Plane) 문자
• 4바이트 저장 공간 사용: SMP(Supplementary Multilingual Plane) & SIP(Supplementary Ideographic Plane) & MySQL 5.5 이전 버전에서 주로 사용되던 utf8

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

저장 공간과 스키마 변경(Online DDL)

MySQL 서버에서는 데이터가 변경되는 도중에도 스키마 변경을 할 수 있도록 온라인 DDL 기능을 제공한다.

VARCHAR 데이터 타입을 사용하는 칼럼의 길이를 늘리는 작업은 길이에 따라 매우 빠르게 처리될 수도 있지만 어떤 경우에는 테이블에 대해 읽기 잠금을 걸고 레코드를 복사하는 작업이 필요할 수도 있다. 다음과 같이 길이가 60 으로 정의된 VARCHAR 타입의 칼럼을 가진 테이블에서 확장하는 길이에 따른 ALTER TABLE 명령의 결과를 살펴보자.

• VARCHAR(60)에서 VARCHAR(63)으로 변경: ALGORITHM=INPLACE, LOCK=NONE
• VARCHAR(63)에서 VARCHAR(64)으로 변경: ALGORITHM=COPY, LOCK=SHARED
  • 스키마 변경을 하는 동안 테이블에는 INSERT나 UPDATE. DELETE를 실행할 수 없다.

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

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