데이터 타입

칼럼의 데이터 타입과 길이를 선정할 때 가장 주의할 사항은 다음과 같습니다.

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

문자열(CHAR와 VARCHAR)

저장 공간

  • CHAR과 VARCHAR의 공통점은 문자열을 저장하는 타입이라는 점이고, 가장큰 차이점은 고정 길이냐 가변길이냐임
    • CHAR: 고정 길이는 실제 입력되는 길이에 따라 저장 공간의 크기가 변하지 않음
    • VARCHAR: 가변 길이는 최대로 저장할 수 있는 길이는 제한, 그 이하 크기의 값이 저장되면 그만큼 저장공간이 줄어듬

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

  • MySQL 서버는 데이터가 변경되는 도중에도 스키마 변경을 할 수 있도록 “Online DDL”을 지원
    • 자세한 사항은 11장 온라인 DDL 참조
  • 모든 스키마 변경이 온라인으로 가능한것은 아님
    • 변경 작업의 특정에 따라 SELECT는 가능하지만 INSERT나 UPDATE는 허용되지 않을 수 있음
  • VARCHAR의 경우 칼럼의 길이를 늘리는 작업은 길이에 따라 빠르게 처기될 수 있지만 어떤 경우에는 테이블에 대해 읽기 잠금을 걸고 레코드를 복사하는 작업이 필요할 수 있음

문자 집합(캐릭터 셋)

  • MySQL의 모든 문자열 타입의 칼럼은 독립적인 문자 집합을 가짐
  • MySQL에서 설장 가능한 문자 집합 관련 변수
    • character_set_system
    • character_set_server
    • character_set_database
    • character_set_filesystem
    • character_set_client
    • character_set_connection
    • character_set_results

콜레이션(Collation)

  • 콜레이션은 문자열 칼럼의 값에 대한 비교나 정렬 순서를 위한 규칙을 의미
    • 비교나 정렬 작업에서 영문 대소문자 처리방식 등의 규칙 정의
  • MySQL의 모든 문자열 타입의 칼럼은 독립적인 문자 집합과 콜레이션을 가짐

비교 방식

문자열 이스케이프 처리

숫자

날짜와 시간

ENUM과 SET

TEXT와 BLOB

공간 데이터 타입

  • MySQL 서버는 OpenGIS에저 제시하는 표준을 준수
    • WKT(Well Known Text) 또는 WKB(Well Known Binary)를 이용해 공간 데이터를 관시할 수 있도록 지원
  • 다음과 같은 공간 정보를 위한 타입을 지원
    • POINT: 하나의 점
    • LINESTRING: 하나의 라인
    • POLYGON: 하나의 다각형
    • GEOMETRY: 위 3개의 수퍼 타입, 셋중 하나 저장가능
    • MULTIPOINT: 여러개의 POINT
    • MULTILINESTRING: 여러개의 LINESTRING
    • MULTIPOLYGON: 여러개의 POLYGON
    • GEOMETRYCOLLECTION: 위 3개의 수퍼 타입, 셋중 하나 저장가능
  • GEOMETRY와 모든 자식 타입은 BLOB 객체로 관리
    • 클라이언트로 전송될 때고 BLOB
    • GEOMETRY은 BLOB을 감싸고 있는 구조
    • 디스크에 저장될때도 BLOB
      • InnoDB 기준
  • JDBC 표준에서는 아직 공간 데이터를 공식적으로 지원하지 않음
    • ORM 라이브러리들은 JTS같은 오픈소스 공간 데이터 라이브러리를 활용

공간 데이터 생성

공간 데이터 조회

JSON 타입

  • MySQL은 5.7부터 JSON 타입을 지원했고 8.0에서 많은 기능과 성능 개선 사항이 추가됨
    • TEXT나 BLOB에 JSON을 저장가능하지만 5.7부터 지원한 JSON은 MongoDB와 같이 BSON(Binary JSON)으로 변환해서 저장
  • 사이즈 확인
    • JSON_STORAGE_SIZE()

저장 방식

  • MySQL서버는 내부적으로 JSON을 BLOB으로 저장
    • 입력한 값 그대로 저장하는 것이 아니라 바이너리 포맷인 BSON으로 변환해서 저장
    • 그래서 BLOB이나 TEXT로 저장하는것보다 공간 효율이 높음

부분 업데이트 성능

  • 8.0부터는 JSON타입에 대해 부분 업데이트(Partial Update) 기능을 제공
    • JSON_SET()
    • JSON_REPLACE()
    • JSON_REMOVE()

JSON 타입 콜레이션과 비교

JSON 칼럼 선택

  • JSON 데이터를 처리하기 위해 BLOB이나 TEXT보다는 JSON컬럼을 사용하는 것이 좋음
    • 이진 포맷으로 컴팩션해서 저장, 부분 업데이트 지원, 가공에 필요한 여러가지 기능을 지원함
  • 그럼 정규화한 칼럼과 JSON 칼럼중 어떤것을 선택해야할까?
    • JSON을 인덱스로 설정 가능
    • 8.0 부터는 멀티 밸류 인덱스 기능이 지원되기 때문에 JSON의 배열 타입의 필드에도 인덱스 생성 가능
    • 이 책은 처음부터 끝까지 MySQL 서버의 성능게 가장 큰 무게를 두고 있음
    • 성능 중심으로 판단한다면 JSON 보다는 정규화된 칼럼을 추천

가상 칼럼(파생 칼럼)

  • 다른 DBMS에서는 가상 컬럼(Virual Column)이라는 이름으로 사용되지만 MySQL서버에서는 Generated Column으로 소개되고 있음
  • MySQL 서버의 가상 컬럼은 크게 두 가지로 구분 가능
    • 가상 컬럼(Virual Column)
    • 스토어드 컬럼(Stored Column)

References