데이터 타입: ENUM, BLOB, JSON, 가상 컬럼

테이블 컬럼의 각 데이터 타입을 결정할 때는 아래의 사항을 유의해야 한다.

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

컬럼의 데이터 타입을 결정할 때 실제 저장되는 값의 특성을 고려하기 보다는 저장되는 값의 최대 길이를 기준으로 컬럼의 길이를 선택하는 것이 일반적이다. 하지만 컬럼의 크기가 너무 무분별하게 커진다면 디스크 공간을 많이 차지할 것이고 해당 컬럼이 인덱스라면 검색 효율도 떨어질 것이다.

 

 

☁️ ENUM 과 SET

ENUM 과 SET을 사용하면 문자열 값을 내부적인 작엽을 통해 숫자 값으로 매핑하여 관리한다.

레코드의 타입이나 상태와 같은 코드 형태의 컬럼(ex. 상품 유형 코드 E(전자), A(가전), F(가구))은 인코딩된 알파벳이나 숫자 값만 저장되므로 그 의미를 직관적으로 파악하기 힘들다는 단점이 존재한다. ENUM 과 SET은 이러한 단점을 보완하는 타입이다.

 

ENUM

ENUM 타입은 테이블의 메타 데이터에 저장된 문자열 목록 중 하나의 값을 가질 수 있는 타입이다. 코드화된 값을 관리하는 것이 ENUM 타입의 가장 큰 용도이다. 값들은 내부적으로 숫자로 관리된다.

 

매핑된 숫자를 레코드에 저장함으로써 문자열을 저장할 때보다 용량을 대폭 줄이는 효과가 있다. ENUM 타입의 컬럼이 여러 인덱스에 사용된다면 인덱스의 크기가 대폭 줄어들어 메모리(버퍼 풀)에 효율적으로 저장되어 사용할 수 있을 것이다.

CREATE TABLE tb_enum ( fd_enum ENUM('PROCESSING', 'FAILURE', 'SUCCESS') );
INSERT INTO tb_enum VALUES ('PROCESSING'), ('FAILURE');

select * from tb_enum;
+------------+
| fd_enum    |
+------------+
| PROCESSING |
| FAILURE    |
+------------+

-- 내부적으로 숫자를 통해 관리 되는 것을 확인할 수 있음
select fd_enum*1 from tb_enum;
+-----------+
| fd_enum*1 |
+-----------+
|         1 |
|         2 |
+-----------+

select * from tb_enum where fd_enum=1;
+------------+
| fd_enum    |
+------------+
| PROCESSING |
+------------+

ENUM 타입의 가장 큰 단점은 ENUM 타입의 값을 추가해야 된다면(예를 들어 위의 테이블의 ENUM 목록으로 “PENFDING”을 추가하는 경우) 테이블의 구조를 변경하고 리빌드 해야한다는 점이다. MySQL 5.6 이후부터는 목록의 값을 추가만 하는 작업은 리빌드 없이 즉시 완료된다.

 

정렬:

ENUM 타입의 정렬은 저장된 문자열이 아닌 매핑된 숫자를 기준으로 정렬된다. 문자열로 정렬하고 싶다면 CAST함수를 사용하여 문자열로 변경한 뒤 정렬해야 한다. CAST를 사용한 정렬은 인덱스를 사용할 수 없다는 점을 유의해야 한다.

 

SET

SET 타입도 ENUM 타입과 동일하게 문자열 목록을 정수값으로 매핑하여 저장한다. SET은 하나의 컬럼에 1개 이상의 값을 저장할 수 있다. 여러 개의 값을 저정할 수 있지만 실제로 여러 값을 가지는 공간을 가지는 것은 아니다.

 

각 문자열 값에 대해 BIT-OR 연산을 거쳐 어떤 문자열을 가지는 지 판단한다. 즉, 8개 이하의 문자열을 가지는 경우 SET 컬럼은 1바이트로 표현할 수 있다. 각 비트는 해당 문자열의 유무를 나타낸다. 8배수로 증가할 때마다 SET컬럼을 저장하기 위해 1바이트를 추가로 가져가고 최대 8바이트까지 할당 가능하다.

 

예를 들어, SET('Red', 'Green', 'Blue')라는 SET 타입이 있다고 가정해보자.

• 'Red'를 나타내는 비트는 001.
• 'Green'을 나타내는 비트는 010.
• 'Blue'를 나타내는 비트는 100.

RED 와 BLUE 가 포함된 SET 컬럼의 데이터는 아래와 같다.

001 (Red) OR 100 (Blue) = 00000101

 

SET을 정의하고 사용하는 예제를 살펴보자.

CREATE TABLE tb_set (         
    fd_set SET('TENNIS','SOCCER','GOLF','TABLE-TENNIS','BASKETBALL','BILLIARD') 
);

INSERT INTO tb_set (fd_set) VALUES ('SOCCER'), ('GOLF,TENNIS');

SELECT * FROM tb_set;
+-------------+
| fd_set      | 
+-------------+
| SOCCER      | 
| TENNIS,GOLF | 
+-------------+

-- 특정 값을 가지는 SET 컬럼을 검색하는 경우
-- 해당 검색이 빈번하게 발생하는 경우 SET타입 컬럼을 정규화하여 
-- 새로운 자식 테이블을 생성하는 것이 좋다.
SELECT * FROM tb_set WHERE FIND_IN_SET('GOLF', fd_set); 
+-------------+
| fd_set      |
+-------------+
| TENNIS,GOLF | 
+-------------+

mysql> SELECT * FROM tb_set WHERE fd_set LIKE '%GOLF%';
+-------------+
| fd_set      |
+-------------+
| TENNIS,GOLF |
+-------------+

SET 컬럼의 동등 비교 시 문자열이 저장된 순서대로 나열해야 한다. 또 동등 조건을 제외한 쿼리는 인덱스를 사용할 수 없다는 단점이 존재한다.

 

 

☁️ TEXT와 BLOB

TEXT와 BLOB은 MySQL에서 대용량 데이터를 저장할 때 사용하는 타입이다. 두 타입의 차이점은 TEXT 타입은 문자열을 저장하는 대용량 컬럼이기에 문자 집합(character set)과 콜레이션을 가진다는 것이고, BLOB(Binary Large Object)은 이진 데이터 타입이라 문자 집합이나 콜레이션을 가지지 않는다는 것이다.

두 타입 모두 대용량 데이터이기에 사용에 주의해야 한다. 보통 아래와 같은 상황에 해당 타입을 사용한다.

• 레코드 전체 크기(보통 64KB)를 넘어서서 컬럼을 추가할 수 없을 때, 일부 컬럼을 TEXT, BLOB으로 대체
• 저장되는 문자열이나 이진 값의 길이를 예측할 수 없을 만큼 클 때

 

주의할 점

인덱스 생성:

MySQL에서 인덱스 레코드의 모든 컬럼은 최대 제한 크기를 가지고 있다. InnoDB 스토리지 엔진에서는 ROW_FORMAT값이 COMPACT인 경우 767바이트, YNAMIC, COMPRESSED인 경우 3072바이트로 길이를 제한한다. ROW_FORMAT이 DYNAMIC, COMPRESSED인 경우 두 타입에 대해 인덱스를 생성할 시, TEXT의 문자 집합이 utf8mb4인 경우 최대 768글자(768*4 = 3072)까지 latin1인 경우 3072글자까지 인덱스로 생성할 수 있다.

 

임시 테이블을 메모리에 저장:

BLOB 이나 TEXT의 임시테이블을 메모리에 저장하고 싶은 경우 internal_tmp_mem_storage_engine 시스템 변수를 TempTable로 설정해야 한다. MySQL 8.0부터 MEMORY 엔진의 두 타입에 대한 메모리 저장을 지원하지 않기 때문이다.

 

SQL문이 너무 길어서 오류가 발생하는 경우:

max_allowed_packet 시스템 변수를 필요한 만큼 충분히 늘려야 한다.

 

레코드에 저장할 때:

두 컬럼 모두 레코드의 최대 길이 제한에 걸려서 한 레코드에 저장되지 못할 때가 있다.

COMPACT 포맷에서 레코드 하나에 저장할 수 있는 최대 길이는 8126바이트이다. (데이터 페이지 16K의 절반) 레코드의 총 저장 길이 8126바이트를 초과하는 경우 해당 레코드에서 가장 큰 길이를 가지는 컬럼부터 외부 페이지로 옮기면서 레코드 크기를 조정한다.

만약 두 타입이 너무 길어서 외부 페이지에 저장되는 경우, 컬럼의 앞쪽 768바이트(BLOB 프리픽스) 만 잘라서 프라이머리 키 페이지에 같이 저장한다. (COMPACT, REDUDANT의 경우)

 

 

☁️ 공간 데이터 타입

• POINT: 점 (MULTIPOINT: 점들)
• LINESTRING: 선 (MULTIPOINT: 선들)
• POLYGIN: 다각형 (MULTIPOINT: 다각형들)
• GEORMETRY: 위의 모두를 포함하는 상위 클래스? 느낌 (GEOMETRYCOLLECTION: ~들)

GEOMETRY, POINT, LINESTRING, POLYGON 타입은 메모리나 디스크에서 BLOB 객체로 관리되고 클라이언트로 전송될 때도 BLOB으로 전송된다. 자세한 내용은 Real MySQL 8.0 2권을 참고하자.

 

 

☁️ JSON타입

json 데이터를 바이너리 포맷의 BSON(Binary JSON)으로 저장할 수 있는 타입이다. 사용자가 입력한 JSON데이터는 내부적으로 BSON으로 변환되어 BLOB 타입에 저장된다.

CREATE TABLE tb_json (id INT, fd JSON);
INSERT INTO tb_json VALUES          
    (1, '{"user_id":1234567890}'),          
    (2, '{"user_id":"1234567890"}');

SELECT id, fd, 
    JSON_TYPE(fd->"$.user_id") AS field_type, 
    JSON_STORAGE_SIZE(fd) AS byte_size 
FROM tb_json 
ORDER BY id;
+------+---------------------------+------------+-----------+
| id   | fd                        | field_type | byte_size |
+------+---------------------------+------------+-----------+
|    1 | {"user_id": 1234567890}   | INTEGER    |        23 |
|    2 | {"user_id": "1234567890"} | STRING     |        30 |
+------+---------------------------+------------+-----------+

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

UPDATE tb_json          
    SET fd=JSON_SET(fd, '$.user_id', "12345")       
WHERE id=2;

 

JSON 컬럼 선택

정규화된 컬럼과 JSON 컬럼중 어떤 타입을 선택하는 것이 일반적일까?

 

일반적으로는 정규화된 컬럼으로 테이블을 생성한다. 왜냐하면 JSON 컬름은 각 필드의 이름이 데이터 파일에 저장되어야 하기 때문에 더 큰 저장공간을 필요로 하기 때문이다. 레코드 건수가 많아짐에 따라 JSON 필드의 이름이 차지하는 디스크 공간은 더욱 더 커질 것이다. 따라서 이름 값을 컬럼으로 추출한 정규화된 테이블을 사용하는 것이 더 좋다.

 

만약 각 레코드가 가지는 속성이 엄청 다양하고 선택적인 값을 가진다면, 이는 JSON 컬럼을 사용하여 저장하는 방식이 더 좋다. 하지만 해당 데이터가 검색 조건으로 자주 사용되고, 자주 접근하는 테이블이라면 정규화된 테이블을 사용하자.

 

 

☁️ 가상 컬럼 (파생 컬럼)

가상 컬럼은 실제 데이터를 저장하지 않고, 계산된 값을 동적으로 생성하는 컬럼이다. 크게 Virtual Column과 Stored Column으로 구분할 수 있다.

CREATE TABLE tb_virtual_column (         
    id INT NOT NULL AUTO_INCREMENT,         
    price DECIMAL(10,2) NOT NULL DEFAULT '0.00',         
    quantity INT NOT NULL DEFAULT 1,         
    total_price DECIMAL(10,2) AS (quantity * price) [VIRTUAL|STORED], --디폴트는 VIRTUAL
    PRIMARY KEY (id)
);

가상 컬럼의 표현식은 시점과 관계없이 입력값에 대해 동일한 결과를 반환하는 표현식만 사용할 수 있다. 예를 들어 NOW() 혹은 RAND() 함수 같이 매번 값이 변경되는 함수와의 연산은 동일한 입력값에 대해 동일한 결과를 반환한다는 보장을 할 수 없기에 가상 컬럼의 표현식에는 사용할 수 없다.

 

Virtual 컬럼은 컬럼의 값이 디스크에 저장되지 않고, 컬럼의 값은 해당 레코드가 읽히기 전에 계산되어 만들어진다. 반면에 Stored 컬럼은 컬럼의 값이 물리적으로 디스크에 저장되고 해당 레코드의 INSERT와 UPDATE 시점에 컬럼의 값이 계산되어 저장된다.

 

여기서 Virtual 컬럼을 인덱스로 생성하면 레코드에는 가상 컬럼이 저장되지 않지만 인덱스에서 가상 컬럼의 값이 저장된다. 가상 컬럼 인덱스는 계산식에 따라 덩적으로 값을 생성하여 인덱스에 저장된다.

 

가성 컬럼은 데이터를 조회하는 시점에 매번 계산되기 때문에 가상 컬럼의 값을 계산하는 과정이 복잡하고 오래 걸린다면 스토어드 컬럼으로 생성하는 것이 성능 향상에 도움이 될 수 있다. 반대로 계산 과정이 빠르지만 결과가 상대적으로 많은 저장 공간을 차지한다면 가상 컬럼으로 저장하는 것이 효율적일 것이다. CPU 사용량을 조금 높여 디스크 부하를 낮출지 디스크 사용량을 늘려 CPU 부하를 낮출지의 트레이드 오프 관계이다.

 

☁️ 결론

ENUM과 SET:

ENUM은 문자열 값을 숫자로 매핑하여 저장함으로써 테이블, 인덱스의 크기를 줄일 수 있다. 정렬 시 문자열 값 기준이 아닌 매핑된 숫자 기준으로 정렬되므로 유의해야 한다.

 

SET은 동등 비교 시 문자열이 저장된 순서와 동일하게 비교해야 한다. 그렇지 않은 경우 FIND_IN_SET, LIKE 절을 사용하여 비교해야 하는데 이러한 방법들은 인덱스를 사용할 수 없다는 단점이 존재하므로 해당 쿼리가 빈번하게 발생하는 경우 SET 컬럼을 정규화 하여 자식 테이블을 생성하는 것이 좋다.

 

가상 컬럼:

가상 컬럼은 virtual 컬럼과 stored 으로 나뉘는데, 실제로 디스크에 저장되냐 아니냐의 차이를 가진다.

virtual 컬럼은 인덱스로 생성하는 경우 인덱스에는 실제 데이터가 저장된다.

 

계산식이 CPU에 부하를 주는 경우 매번 계산식을 수행해야 하는 virtual 컬럼보다는 stored 컬럼이 성능 향상에 도움을 줄수 있다. 반대로 계산식은 간단하지만 계산 결과의 크기가 너무 큰 경우 메모리에 부하를 줄 수 있기에 stored 컬럼보다는 virtual 컬럼으로 저장하는 것이 효율적이