ENUM

• 테이블의 구조에 나열된 목록 중 하나의 값을 가질 수 있다.

1
2
3
4
5
6

  create table tb_enum (
    fd_enum enum('PROCESSING', 'FAILURE', 'SUCCESS')
  );

  -- 매핑된 문자열이 아닌 내부적으로 저장된 숫자 값으로 연산이 실행됨
  select fd_enum*1 as fd_enum_real_value from tb_enum;

• enum 타입은 쿼리에서 char, varchar 타입과 같은 문자열로 비교하거나 저장할 수 있다.
• enum 타입에 사용할 수 있는 최대 아이템 갯수는 65535개
  • 아이템 갯수가 255개 미만이면 1바이트를 사용
• 장점
  • 테이블 구조에 정의된 코드 값만 사용할 수 있게 강제한다.
  • DB 서버의 디스크 저장 공간의 크기를 줄여줌
    • 레코드가 많아질수록 효율적
• 단점

  • enum 타입에 새로운 값을 추가한다면 테이블의 구조를 변경해야 한다.

    • MySQL 5.6 이전까지는 항상 테이블을 리빌드해야 했음 -> 별로 사용하지 않음
    • MySQL 5.6 부터는 새로 추가하는 아이템이 enum 타입의 제일 마지막에 추가되는 형태하면 텡블의 구조 변경만으로 즉시 완료된다.

    1 2 3 4 5 6 7

    alter table tb_enum modify fd_enum enum('PROCESSING', 'FAILURE', 'SUCCESS', 'REFUND'), algorithm=instant; alter table tb_enum modify fd_enum enum('PROCESSING', 'FAILURE', 'REFUND', 'SUCCESS'), algorithm=copy, lock=shared;

    • 테이블이 매우 크다면 가독성이 조금 떨어지더라도 새로운 아이템을 enum 마지막에 추가하는 것이 MySQL 서버의 가용성을 높이는 방법
• enum 타입 값으로 정렬하면 매핑된 코드 값으로 정렬이 수행됨
  • enum은 문자열이 아닌 정수 타입의 컬럼
  • 되도록이면 enum 기준으로 정렬은 안하는게 좋음

SET

• enum과의 차이: set은 하나의 컬럼에 1개 이상의 값을 저장할 수 있다.
  • MySQL 서버는 내부적으로 BIT-OR 연산을 거쳐서 1개 이상의 선택된 값을 저장한다.
  • 여러 개의 값을 저장할 수 있지만 그만큼의 공간을 가지지는 않음
• 아이템 수가 8개 이하면 1바이트의 저장공간을 가짐

1
2
3
4
5
6
7
8
9

  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 where find_in_set('GOLF', fd_set);

  select * from tb_set where fd_set='GOLF,TENNIS';

• find_in_set 함수나 like로 검색할 수 있음
  • find_in_set 함수는 해당 컬럼에 인덱스가 있어도 효율적으로 인덱스를 이용할 수 없다.
  • 이런 형태의 검색이 빈번히 사용된다면 set 타입의 컬럼을 정규화해서 별도로 인덱스를 가진 자식 테이블을 생성하는 것이 좋다.
• equals 비교 시에는 컬럼에 저장된 순서대로 문자열을 나열해야 검색 가능
• 중간에 아이템이 추가된다면 테이블의 읽기 잠금과 리빌드 작업이 필요

  1 2 3

  alter table tb_set modify fd_set set('TENNIS', 'SOCCER', 'GOLF', 'TABLE-TENNIS', 'Football', 'BASKETBALL', 'BILLIARD'), algorithm=copy, lock=shared;

  • 아이템 갯수가 8 -> 9개로 변경될 때는 set 타입을 저장하기 위한 공간을 2바이트로 변경해야하기 때문에 읽기 잠금과 테이블 리빌드 작업이 필요하다.

TEXT와 BLOB

• TEXT, BLOB 타입 모두 주의하고 남용하면 안됨
  • 컬럼 하나에 저장되는 문자열이나 이진 값의 길이가 예측할 수 없이 클 때
  • 일반적으로 하나의 레코드는 전체 크기가 64KB를 넘을 수 없지만 이를 초과할 때
• blob/text 컬럼을 조작하는 sql문의 길이가 많이 길어질 수 있음
  • max_allowed_packet 시스템 변수에 설정된 값보다 큰 sql 문장은 MySQL 서버로 전송되지 못하고 오류가 발생할 수 있음
  • max_allowed_packet 값을 충분히 늘리도록 설정
• MySQL 서버에서 text, blob 컬럼의 값이 어떻게 저장되는지는 테이블의 row_format 옵션을 사용한다.
  • 별도로 지정되지 않으면 innodb_default_for_format 시스템 변수에 저장된 값으로 적용한다.
  • MySQL 8.0에서는 사용 가능한 모든 row_format에서는 가능하다면 text/blob 컬럼의 값을 다른 레코드와 같이 저장하려고 하지만 레코드의 최대 길이가 제한적인 문제가 있음
    • 레코드의 최대 길이를 초과하면 용량이 큰 컬럼 순서대로 외부 페이지로 옮기면서 레코드의 크기를 맞춘다.

공간 데이터 타입

• MySQL 서버는 OpenGIS에서 제시하는 표준을 준수함
  • WKT (Well Known Text) or WKB (Well Known Binary)를 이용해서 공간 데이터를 관리할 수 있게 지원
  • MySQL에서 제공하는 공간 정보 저장용 데이터 타입: POINT, LINESTRING, POLYGON, GEOMETRY, GEOMETRYCOLLECTION 등
• geometry로 하나의 점/선/다각형을 저장하거나 geometrycollection으로 여러개의 데이터를 저장할 수 있음

공간 데이터 생성

• MySQL 서버에서 공간 데이터를 생성할 때는 WKT 포맷을 이진 데이터 포맷의 데이터로 변환할 수 있음
  • WKT 포맷: POINT(x y)
  • 객체 생성: ST_PointFromText(‘POINT(x y)’)
    • FromText 대신 FromWKB를 사용하면 WKB를 이용한 공간 데이터 객체를 생성한다.
• 두번째 파라미터로 SRID를 설정 가능

  1 2 3 4 5

  ST_PointFromText('POINT(10,20)') ST_PointFromText('POINT(37.123123, 127.233243)') ST_PointFromText('POINT(10,20)', 0) ST_PointFromText('POINT(37.123123, 127.233243)', 4326)

  • SRID를 명시하지 않으면 SRID=0인 객체를 생성한다.

공간 데이터 조회

• MySQL 서버가 관리하는 공간 데이터를 조회하는 방법
  • 이진 데이털 조회 (WKB 포맷 or MySQL 이진 포맷)
  • 텍스트 데이터 조회 (WKT 포맷)
  • 공간 데이터의 속성 함수를 이용한 조회
  • 1, 2번째는 공간 데이터 타입과 관계없이 ST_AsText() / ST_AsWKT() or ST_AsBinary() / ST_AsWKB() 함수로 조회 가능

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

  set @poi:=ST_PointFromText('POINT(37.123123 127.123123)', 4326)

  select
    ST_SRID(@poi) as srid,
    ST_X(@poi) as coord_x,
    ST_Y(@poi) as coord_y,
    ST_Latitude(@poi) as coord_latitude,
    ST_Longitude(@poi) as coord_longitude;
  
  -- ST_Latitude ST_Longitude는 위도/경도를 사용하는 좌표시스템의 공간 데이터에서만 사용 가능

JSON 타입

• MySQL 5.7 부터 JSON 데이터를 저장할 수 있는 JSON 타입이 지원되기 시작
• MySQL 8.0 부터 많은 기능과 성능이 개선됨
• MySQL 서버에서 TEXT, BLOB 컬럼에 JSON 데이터를 저장할 수는 있음
• MySQL 5.7부터 지원되는 JSON 데이터 컬럼은 문자열 저장이 아닌 MongoDB와 같이 바이너리 포맷의 BSON(Binary JSON)으로 변환해서 저장한다.

저장 방식

• BSON 타입으로 변환해서 저장하기 때문에 TEXT/BLOB보다 공간 효율이 높은 편

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

  create table tb_json (id int, fd json);

  insert into tb_json values
    (1, '{"user_id": 123}'),
    (2, '{"user_id": "123"}');
  
  select id, fd,
    json_type(fd -> "$.user_id") as field_type,
    json_storage_size(fd) as byte_size
  tb_json;

• json document가 MySQL 서버의 JSON 컬럼에 저장되면 이진 데이터로 변환하여 저장한다.
• 용량이 매우 큰 document가 저장되면 MySQL 서버는 16KB 단위로 여러 개의 데이터 페이지로 나뉘어 저장한더.
  • MySQL 5.7 까지는 BLOB 페이지들이 단순 연결 리스트처럼 관리되었음
  • MySQL 8.0 부터는 BLOB 페이지들의 인덱스를 관리하고, 각 인덱스는 실제 BLOB 데이터를 가진 페이지들의 링크를 갖도록 개선함

부분 업데이트 성능

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

1
2
3
4
5
6

  update tb_json
    set fd=json_set(fd, '$.user_id', "12345")
  where id=2;

  select id, fd, json_storage_size(fd), json_storage_free(id)
  from tb_json;

• 위 변경 작업이 부분 업데이트로 처리되었는지는 확인할 수 없음
  • json_storage_size, json_storage_free 함수로 대략 예측
  • 두번째 레코드는 json_storage_free가 5로 표시됨
    • “12345"로 변경되면서 앞 부분의 5바이트 사용, 5바이트는 비움
  • 해당 필드의 값을 10바이트보다 큰 값으로 저장할 경우
    • json_set() 함수를 사용해도 부분 업데이트 진행 x
    • 최초 할당된 공간이 부족해서 MySQL 서버가 JSON 컬럼 or 두번째 레코드를 다른 위치로 복사해서 저장
• MySQL 내부적으로 json 컬럼의 값은 BLOB 타입으로 저장되는데 실제 json 컬럼의 값은 4GB까지 값을 가질 수 있음
  • 1MB json 데이터를 저장해도 MySQL 서버는 16KB * 64개 페이지를 사용함
    • 부분 업데이트를 한다면 1개의 페이지만 변경하면 되고, 안되면 모든 페이지를 다시 디스크로 기록해야함
• 단순 정수필드의 값을 변경하는 작업은 항상 부분 업데이트 기능이 적용
• 문자열 타입의 필드라면 문자열의 길이에 따라 부분 업데이트가 되지 않을수도 있음
  • 자주 길이가 변한다면 해당 필드가 가질 수 있는 최대 길이로 초기화하거나 애플리케이션에서 추가로 패딩해서 고정 길이의 문자열로 만들어서 저장하는 방법이 있음

JSON 타입 콜레이션과 비교

• json 컬럼에 저장되는 데이터와 이를 가공해서 나온 결과는 모두 utf8mb4 문자 집합과 utf8mb4_bin 콜레이션을 가진다.
  • utf8mb4_bin 콜레이션은 바이너리 콜레이션이므로 json을 가공해서 나온 문자열은 대소문자나 액센트 문자도 구분해서 비교한다.

JSON 컬럼 선택

• TEXT/BLOB vs JSON
  • TEXT/BLOB에 json 문자열을 저장하는 경우 아무런 변환과정을 거치지 않고 값을 디스크에 저장한다.
  • JSON 타입은 json 데이터를 이진 포맷으로 컴팩션해서 저장하고, 필요한경우 부분 업데이트를 통한 빠른 변경 기능을 제공한다.
  • A무조건 json 타입 사용이 좋음
• 정규화한 컬럼 vs json 컬럼
  • json 컬럼만 유지하는 경우에도 필요한 인덱스를 모두 생성할 수 있음
  • MySQL 8.0부터 멀티 밸류 인덱스 기능이 지원되기 때문에 json document에서 array 타입의 필드에도 인덱스를 생성할 수 있게됨
  • 성능을 중심으로 판단한다면 정규화된 컬럼을 추천
  • 테이블을 너무 정규화했을때 테이블 갯수가 많아지더간 응용 프로그램의 코드도 길어질 수 있음
    • 중요도가 낮다면 json 컬럼 사용도 가능

가상 컬럼 (파생 컬럼)

• MySQL 서버의 가상 컬럼은 가상 컬럼/스토어드 컬럼으로 구분

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

  -- 가상 컬럼
  create table tb_virual_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,
    primary key (id)
  );

  -- 스토어드 컬럼
  create table tb_stored_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) stored,
    primary key (id)
  );

• 컬럼의 정의 뒤에 as절로 계산식을 정의한다.
  • 마지막에 stored 키워드가 사용되면 스토어드 컬럼으로 생성되며, 그 외에는 가상 컬럼으로 생성된다. (default는 virtual)
• 가상 컬럼의 표현식은 입력이 동일하면 시점과 관계없이 결과가 항상 동일한 표현식만 사용할 수 있음
  • 사용자 변수나 not-deterministic 옵션의 함수나 표현식 사용할 수 없음
  • MySQL 8.0 까지는 가상 컬럼의 표현식에 서브쿼리나 스토어드 프로그램을 사용할 수 없음
• 가상/스토어드 컬럼 모두 다른 컬럼의 값을 참조해서 새로운 값을 만들어 관리한다는 공통점이 있음
  • 단점
    • 가상 컬럼
      • 컬럼의 값이 디스크에 저장되지 않음
      • 컬럼의 구조 변경은 테이블 리빌드를 필요로 하지 않음
      • 컬럼의 값은 레코드가 읽히기 전 또는 before 트리거 실행 직후에 계산되어 만들어짐
    • 스토어드 컬럼
      • 컬럼의 값이 물리적으로 디스크에 저장됨
      • 컬럼의 구조 변경은 다른 일반 테이블과 같이 필요 시 테이블 리빌드 방식으로 처리됨
      • insert, update 시점에만 컬럼의 값이 계산됨
  • 차이
    • 계산된 컬럼의 값이 디스크에 실제 저장되는지 여부
      • 가상 컬럼은 디스크에 저장되지 않지만 항상 그런건 아님
  • 가상 컬럼은 데이터를 조회하는 시점에 매번 계산되기 때문에 가상 컬럼의 값을 계산하는 과정이 복잡하고 시간이 오래걸린다면 스토어드 컬럼으로 변경하는 것이 성능 향상에 좋음