데이터 타입

문자열(CHAR와 VARCHAR)

문자열 컬럼을 사용할 는 우선 CHAR와 VARCHAR 타입 중 어떤 타입을 사용할지 결정해야 한다. 우선 저장 공간과 비교 방식의 관점에서 CHAR와 VARCHAR를 비교해보고, MySQL 내부적으로 어떤 차이가 있는지도 한 번 살펴보자.

 

저장 공간

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

• CHAR 타입은 이미 저장 공간의 크기가 고정적이다. 실제 저장된 값의 유효 크기가 얼마인지 별도로 저장할 필요가 없으므로 추가로 공간이 필요하지 않다.
• VARCHAR 타입은 최대로 저장할 수 있는 값의 길이는 제한돼 있지만, 그 이하 크기의 값이 저장되면 그만큼 저장 공간이 줄어든다. 하지만 저장된 값의 유효 크기가 얼마인지를 별도로 저장해 둬야 하므로 1~2바이트의 저장 공간이 추가로 더 필요하다.

MySQL에서 하나의 레코드에서 TEXT와 BLOB 타입을 제외한 컬럼의 전체 크기가 64KB를 초과할 수 없다. 테이블에 다른 컬럼들이 40KB의 크기를 사용하고 있다면 VARCHAR 타입 하나는 최대 24KB까지만 사용할 수 있다. 이때 24KB를 초과하면 에러가 발생하거나 자동으로 TEXT 타입으로 대체된다. 그래서 컬럼을 새로 추가할 때는 VARCHAR 타입이 TEXT 타입으로 자동으로 변환되지 않았는지 확인해 보는 것이 좋다.

 

문자열 값의 길이가 항상 일정하다면 CHAR를 사용하고 가변적이라면 VARCHAR를 사용하는 것이 일반적이지만 이것보다 CHAR 타입과 VARCHAR 타입을 결정할 때 중요한 판단 기준은 다음과 같다.

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

즉, CHAR와 VARCHAR 타입의 선택 기준은 값의 길이도 중요하지만, 해당 컬럼의 값이 얼마나 자주 변경되느냐가 기준이 돼야 한다. 만약 fd라는 컬럼에 CHAR(10) 타입과 VARCHAR(10) 타입으로 설정해서 "ABCD"에서 "ABCDE"로 UPDATE했다고 가정하면 다음과 같다.

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

즉, 주민등번호처럼 고정적이거나 자주 변경될 수 있는 부서 번호나 게시글의 상태 값은 CHAR 타입을 사용하는 것이 좋다. 자주 변경돼도 레코드가 물리적으로 다른 위치로 이동하거나 분리되지 않아도 되기 때문이다. 또한 MySQL에서는 CHAR나 VARCHAR 뒤에 지정하는 숫자는 그 컬럼의 바이크 크기가 아니라 문자의 수를 의미한다. 즉, CHAR(10) 또는 VARCHAR(10)으로 컬럼을 정의하면 이 컬럼은 10바이트를 저장할 수 있는 공간이 아니라 10글자(문자)를 저장할 수 있는 공간을 의미한다. 그래서 CHAR(10) 타입을 사용하더라도 이 컬럼이 실제로 디스크나 메모리에서 사용하는 공간은 각각 달라진다.

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

 

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

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

위 사진에서 볼 수 있듯이 value의 컬럼 타입을 63으로 늘리는 경우 잠금 없이(LOCK=NONE) 매우 빠르게 변경된 것을 확인할 수 있다. 하지만 컬럼의 타입을 VARCHAR(64)로 늘리는 경우는 INPLACE 알고리즘으로 스키마 변경이 허용되지 않는다는 것을 알 수 있다. 그래서 VARCHAR(64)로 변경하는 경우에는 COPY 알고리즘으로 스키마 변경을 실행햇으며, 스키마 변경 시간도 상당히 많이 걸리게 된다. 그뿐만 아니라 COPY 알고리즘의 스키마 변경은 읽기 잠금(LOCK=SHARED)까지 필요로 하기 때문에 test 테이블 스키마가 변경되는 동안 INSERT나 UPDATE, DELETE를 실행할 수 없게 된다.

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

 

콜레이션

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

 

콜레이션 이해

문자 집합은 2개 이상의 콜레이션을 가지고 있는데, 하나의 무자 집합에 속한 콜레이션은 다른 문자 집합과 공유해서 사용할 수 없다. 또한 테이블이나 컬럼에 문자 집합만 지정하면 해당 문자 집합의 디폴트 콜레이션이 해당 컬럼의 콜레이션으로 지정된다. 반대로 컬럼의 문자 집합은 지정하지 않고 콜레이션만 지정하면 해당 콜레이션이 소속된 문자 집합이 묵시적으로 그 컬럼의 문자 집합으로 사용된다.

 

비교 방식

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

 

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

 

숫자

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

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

DBMS에서는 근삿값은 저장할 때와 조회할 때의 값이 정확히 일치하지 않고, 유효 자릿수를 넘어서는 소수점 이하의 값은 계속 바뀔 수 있다. 특히 STATEMENT 포맷을 사용하는 복제에서는 소스 서버와 레플리카 서버 간 데이터 차이가 발생할 수도 있다. MySQL에서 FLOAT나 DOUBLE과 같은 부동 소수점 타입은 잘 사용하지 않는다.

 

정수

데이터 타입	저장공간(Bytes)	최솟값(Signed)	최솟값(Unsigned)	최댓값(Signed)	최댓값(Unsigned)
TINYINT	1	-128	0	127	255
SMALLINT	2	-32768	0	32767	65535
MEDIUMINT	3	-8388608	0	8388607	16777215
INT	4	-2147438648	0	2147438647	4294967295
BIGINT	8	-263	0	263-1	264-1

DECIMAL 타입을 제외하고 정수를 저장하는 데 사용할 수 있는 데이터 타입으로는 5가지가 있다. 이것들은 저장 가능한 숫자 값의 범위만 다를 뿐 다른 차이는 거의 없다. 정수 타입은 UNSIGNED라는 컬럼 옵션을 사용하여 0보다 큰 양의 정수만 저장할 수 있게 하면 최댓값이 SIGNED 타입보다 2배가 더 커진다. AUTO_INCREMENT 컬럼과 같이 음수가 될 수 없는 값을 저장하는 컬럼에 UNSIGNED 옵션을 명시하면 작은 데이터 공간으로 더 큰 값을 저장할 수 있다.

 

DECIMAL

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

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

 

날짜와 시간

데이터 타입	MySQL 5.6.4 이전	MySQL 5.6.4부터
YEAR	1바이트	1바이트
DATE	3바이트	3바이트
TIME	3바이트	3바이트 + (밀리초 단위 저장 공간)
DATETIME	8바이트	5바이트 + (밀리초 단위 저장 공간)
TIMESTAMP	4바이트	4바이트 + (밀리초 단위 저장 공간)

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

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

밀리초 단위 자릿수	저장 공간
없음	0바이트
1, 2	1바이트
3, 4	2바이트
5, 6	3바이트

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

 

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

따라서 JDBC 드라이버에서 날짜 및 시간 정보를 JVM의 타임존으로 변환해서 출력하면 TIMESTAMP이든 DATETIME이든 관계없이 해당 타임존으로 변환해서 출력한다.

 

자동 업데이트

MySQL 5.6 버전부터는 TIMESTAMP와 DATETIME 컬럼 모두 INSERT와 UPDATE 문장이 실행될 때마다 해당 시점으로 자동 업데이트되게 하려면 테이블을 생성할 때 컬럼 정의 뒤에 위처럼 옵션을 정의해야 한다. "DEFAULT CURRENT_TIMESTAMP" 옵션은 레코드가 INSERT될 때의 시점을 자동으로 업데이트하며, "ON UPDATE CURRENT_TIMESTAMP" 옵션은 해당 레코드가 UPDATE될 떄의 시점을 자동으로 업데이트하게 해준다.

 

ENUM과 SET

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

 

ENUM

ENUM 타입은 테이블 구조(메타 데이터)에 나열된 목록 중 하나의 값을 가질 수 있다. ENUM 타입의 가장 큰 용도는 코드화된 값을 관리하는 것이다. 위 예제는 fd_enum 컬럼에 값으로 'PROCESSING'과 'FAILURE', 'SUCCESS'를 가질 수 있게 정의했다. ENUM 타입은 INSERT나 UPDATE, SELECT 등의 쿼리에서 CHAR나 VARCHAR 타입과 같이 문자열처럼 비교하거나 저장할 수 있다. 하지만 MySQL 서버가 실제로 값을 디스크나 메모리에 저장할 때는 사용자로부터 요청된 문자열이 아니라 그 값에 매핑된 정숫값을 사용한다. ENUM 타입에 사용할 수 있는 최대 아이템의 개수는 65,535개이며, 아이템 개수가 255개 미만이면 저장 공간으로 1바이트, 그 이상인 경우에는 2바이트까지 사용한다.

하지만 ENUM 타입의 가장 큰 단점은 컬럼에 저장되는 문자열 값이 테이블 구조(메타 정보)가 되면서 기존 ENUM 타입에 새로운 값을 추가해야 한다면 테이블의 구조해야 한다는 점이다. MySQL 5.6 버전부터는 새로 추가하는 아이템이 ENUM 타입의 제일 마지막으로 추가되는 형태라면 INSTANT 알고리즘으로 테이블의 구조(메타데이터) 변경만으로 즉시 완료된다. 하지만 ENUM 타입의 아이템들이 순서가 변경되거나 중간에 새로운 아이템이 추가되는 경우에는 COPY 알고리즘에 읽기 잠금까지 필요하다.

 

SET

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

위 예제처럼 "SOCCER"라는 하나의 값만 저장하거나 "GOLF"와 "TENNIS"라는 두 개의 값을 하나의 컬럼에 저장할 수 있다. SET 타입의 컬럼에서 "GOLF"라는 문자열 멤버를 가진 레코드를 검색해야 할 때는 두 번째나 세 번째의 SELECT 쿼리에서와 같이 FIND_IN_SET() 함수나 LIKE 검색을 이용할 수 있다. SET 타입의 컬럼에 대해 동등 비교를 수행하려면 컬럼에 저장된 순서대로 문자열을 나열해야만 검색할 수 있다. 또한 SET 타입의 컬럼에 인덱스가 있더라도 동등 비교 조건을 제외하고 FIND_IN_SET() 함수나 LIKE를 사용하는 쿼리는 인덱스를 사용할 수 없다.

 

TEXT와 BLOB

MySQL에서 대량의 데이터를 저장하려면 TEXT나 BLOB 타입을 사용해야 하는데, 이 두 타입은 많은 부분에서 거의 똑같은 설정이나 방식으로 동작한다. TEXT 타입과 BLOB 타입의 유일한 차이점은 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 타입으로 전환해야 할 수도 있다.

MySQL 서버에서 TEXT와 BLOB 타입 컬럼의 값이 어떻게 저장되는지는 혼란스러운 부분 중 하나다. MySQL 서버에서 BLOB과 TEXT 타입 컬럼의 데이터가 어떻게 저장될지를 결정하는 요소는 테이블의 ROW_FORMAT 옵션이다. 테이블을 생성할 때 ROW_FORMAT 옵션이 별도로 지정되지 않으면 MySQL 서버는 innodb_default_row_format 시스템 변수에 설정된 값을 적용한다. MySQL 서버에서 별도로 innodb_default_row_format 시스템 변수를 설정하지 않으면 기본으로 최신 ROW_FORMAT인 dynamic이 설정된다.

TEXT나 BLOB 컬럼이 너무 긴 경우에는 모두 외부 페이지로 저장한다. BLOB이나 TEXT 컬럼이 외부 페이지로 저장될 때 길이가 16KB를 넘는 경우 MySQL 서버는 컬럼의 값을 나눠서 여러 개의 외부 페이지에 저장하고 각 페이지는 체인으로 연결한다. 따라서 하나의 테이블에 여러 개의 BLOG이나 TEXT 컬럼이 있다면 하나의 레코드는 여러 개의 외부 페이지 체인을 가질 수도 있다.

 

JSON 타입

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

 

저장 방식

MySQL 서버는 내부적으로 JSON 타입의 값을 BLOB 타입에 저장한다. 하지만 JSON 컬럼에 저장되는 값은 사용자가 입력한 값 그대로 저장하는 것이 아니라 바이너리 포맷인 BSON 타입으로 변환해서 저장한다. 그래서 JSON 데이터를 BLOB이나 TEXT 타입의 컬럼에 저장하는 것보다 공간 효율이 높은 편이다.

 

위는 JSON 컬럼의 값이 이진 포맷으로 변환했을 떄 길이가 몇 바이트인지 확인하는 예제다. 첫 번째 user_id 필드의 값을 정수 타입으로 저장했으며, 두 번째 레코드는 user_id 필드의 값을 문자열 타입으로 저장했다. 그 결과 두 레코드의 JSON 값을 이진 포맷으로 변환하면 7바이트의 공간 차이가 발생했다.

 

부분 업데이트 성능

MySQL 8.0 버전부터는 JSON 타입에 대해 부분 업데이트(Partial Update) 기능을 제공한다. JSON 컬럼의 부분 업데이트 기능은 JSON_SET()과 JSON_REPLACE(), JSON_REMOVE() 함수를 이용해 JSON 도큐먼트의 특정 필드 값을 변경하거나 삭제하는 경우에만 작동한다.

 

두 번째 레코드의 JSON 컬럼의 값에서 "1234567890"이었던 "user_id" 필드의 값을 JSON_SET 함수를 이용하여 "12345"로 변경했다. JSON_SET() 함수를 이용한 JSON 컬럼의 필드 값 변경 작업이 "부분 업데이트"로 처리됐는지 확인할 수 있는 명확한 방법은 없지만 위에서처럼 JSON_STORAGE_SIZE() 함수와 JSON_STORAGE_FREE() 함수를 이용하여 대략 예측할 수 있다. JSON_STORAGE_FREE() 함수의 결괏값이 5로 표시됐는데 이는 "user_id" 필드의 값이 10바이트를 차지하고 있다가 "12345"로 변경되면서 앞 부분 5바이트만 사용하고 나머지 5바이트는 비워뒀기 때문이다. 그래서 JSON_STORAGE_SIZE() 함수의 결괏값은 변하지 않았지만 JSON_STORAGE_FREE() 값은 5로 표시된 것이다.

 

"user_id" 필드의 값을 10바이트 이상으로 변경하면 "user_id" 필드가 사용했던 공간의 크기가 10바이트인데, 11바이트 문자열로 업데이트된 것을 확인할 수 있다. JSON_SET() 함수를 이용해 업데이트했지만 이번에는 부분 업데이트 방식으로 처리되지 못했다. 단순히 정수 필드의 값을 변경하는 UPDATE는 항상 부분 업데이트 기능이 적용될 것이다. 하지만 문자열 타입의 필드라면 저장되는 문자열의 길이에 따라 부분 업데이트가 사용되지 못할 수도 있다. 특정 필드의 값이 작은 용량을 가지면서 자주 길이가 다른 값으로 변경된다면 해당 필드가 가질 수 있는 최대 길이의 값으로 초기화해 두거나 애플리케이션에서 추가로 패딩해서 고정 길이의 문자열로 만들어서 저장하는 방법도 부분 업데이트 기능을 활용할 수 있는 좋은 방법이다.

 

가상 컬럼(파생 컬럼)

MySQL 서버의 가상 컬럼은 크게 가상 컬럼(Virtual Column)과 스토어드 컬럼(Stored Column)으로 구분할 수 있다. 가상 컬럼과 스토어드 컬럼 모두 컬럼의 정의 뒤에 "AS" 절로 계산식을 정의한다. 이때 마지막에 "STORED" 키워드가 사용되면 스토어드 컬럼으로 생성되며, 그 이외의 경우에는 항상 가상 컬럼으로 정의된다. 가상 컬럼은 다른 컬럼의 값을 참조해서 계산된 값을 관리하기 때문에 항상 AS 절 뒤에는 계산식이나 데이터 가공을 위한 표현식을 정의한다.

가상 컬럼의 표현식은 입력이 동일하면 시점과 관계없이 결과가 항상 동일한(DETERMINISTIC) 표현식만 사용할 수 있다. 그래서 사용자 변수나 NON-DETERMINISTIC 옵션의 함수나 표현식을 사용할 수 있따.

가상 컬럼과 스토어드 컬럼 모두 다른 컬럼의 값을 참조해서 새로운 값을 만들어 관리한다는 공통점이 있다. 즉, 기존 컬럼의 값을 계산해서 관리하는 파생된 컬럼인 것이다. 하지만 가상 컬럼과 스토어드 컬럼은 다음과 같은 차이점이 있다.

• 가상 컬럼(Virtual Column)
  • 컬럼의 값이 디스크에 저장되지 않음
  • 컬럼의 구조 변경은 테이블 리빌드를 필요로 하지 않음
  • 컬럼의 값은 레코드가 읽히기 전 또는 BEFORE 트리거 실행 직후에 계산되어 만들어짐
• 스토어드 컬럼(Stored Column)
  • 컬럼의 값이 물리적으로 디스크에 저장됨
  • 컬럼의 구조 변경은 다른 일반 테이블과 같이 필요 시 테이블 리빌드 방식으로 처리됨
  • INSERT와 UPDATE 시점에만 컬럼의 값이 계산됨

가상 컬럼과 스토어드 컬럼의 가장 큰 차이는 계산된 컬럼의 값이 디스크에 실제 저장되는지 여부다. 가상 컬럼은 디스크에 저장되지 않지만 이것이 항상 사실은 아니다. 가상 컬럼에 인덱스를 생성하게 되면 테이블의 레코드는 가상 컬럼을 포함하지 않지만 해당 인덱스를 계산된 값을 저장한다. 그래서 인덱스가 생성된 가상 컬럼의 경우 변경이 필요하다면 인덱스의 리빌드 작업이 필요하다.