데이터 타입

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

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

문자열(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의 모든 문자열 타입 칼럼은 독립적인 문자 집합과 콜레이션을 가짐
    • 칼럼에 독립적으로 지정하지 않으면 MySQL 서버나 DB의 기본 문자 집합과 콜레이션이 설정됨
  • SHOW COLLATION;
    • 3개의 파트로 구성된 콜레이션 이름
      • 첫 번째는 문자 집합
      • 두 번째는 문자 집합의 하위 분류
      • 세 번째는 대문자나 소문자 구분 여부를 나타냄
        • ci(case insensiive)는 대소문자를 구분하지 않음
        • cs(case sensiive)는 대소문자를 구분
    • 2개의 파트로 구성된 콜레이션 이름
      • 첫 번째는 문자 집합
      • 두 번째는 항상 bin
        • 이진 데이터(binary)를 의미, 이진 데이터로 관리되는 문자열은 별도의 콜레이션을 가지지 않음
        • 비교 및 정렬은 실제 문자 데이터의 바이트값을 기준으로 수행됨
    • utf8mb4 문자 집합의 콜레이션은 이름이 더 많이 복잡해짐
    • latin1, euckr, utf8mb4 문자 집합의 디폴트 콜레이션은 각 latin_swedish_ci, euckr_korean_ci, utf8mb4_0900_ci
      • 대소문자를 구분하지 않는데 별도로 _cs 계열의 콜레이션을 가지지 않음
      • 이 경우 _bin를 사용하면 대소문자를 구분함
  • MySQL의 문자열 칼럼은 타입, 길이, 문자 집합, 콜레이션까지 같아야 똑같은 타입이라고 할 수 있음
    • 문자 집합과 콜레이션까까지 모두 일치해야만 조인이나 WHERE 조건이 인덱스를 효츌적으로 사용할 수 있음
    • 조인을 수행하는 양쪽 테이블의 칼럼이 문자 집합이나 콜레이션이 다르면 콜레이션의 변환이 필요해서 인덱스를 효율적으로 이용하지 못할때가 많으므로 주의해야함
  • utf8mb4 문자 집합의 콜레이션
    • 최근 응용은 다국어 지원이 필수여서 대부분 utf8mb4 문자 집할을 사용할 것임
    • 콜레이션에 숫자는 비교 알고리즘 버전
    • 콜레이션에 로케일이 포함되어있는건 특정 언어에 종속적인 콜레이션
      • 비종속적 콜레이션은 문자 셋의 기본 정렬 순서에 의해 정렬 및 비교
      • 종속적 콜레이션은 해당 언어에서 정의한 정렬 순서에 의해 정렬 및 비교
    • 범용 응용에서는 utf8mb4_0900_ai_ci로 충분할것
      • NO PAD 옵션
        • 문자열 뒤에 존재하는 공백도 유효문자로 취급되어 비교됨
  • MySql 메뉴얼에는 UCA 9.0.0버전은 그 이전 버전의 콜레이션보다 빠르다고 소개함
    • 테스트 결과 그렇지 않다는 것을 확인가능
    • 그렇지만 단순히 성능 때문에 콜레이션을 선택하면 안됨
  • 5.7에서는 utf8mb4의 기본 콜레이션이 utf8mb4_general_ci였음
    • 8.0부터는 utf8mb4_0900_ai_ci로 변경됨

비교 방식

  • MySQL에서 문자열 비교방식은 CHAR와 VARCHAR가 거의 같음
    • 다른 DBMS처럼 CHAR뒤에 공백 문자가 채워져서 나오지 않음
    • CHAR와 VARCHAR를 비교할때 공백 문자를 뒤에 붙여서 두 문자열의 길이를 동일하게 만든 후 비교 수행
  • utf8mb4가 UCA 9.0.0을 지원하면서 문자열 뒤 공백에 대한 비교방식이 달라짐
    • 뒤에 공백이 있는거와 없는것이 다르다고 판단됨
      SELECT collation_name, pad_attribute
FROM information_schema.COLLATIONS
WHERE collation_name LIKE 'utf8mb4%';
    • PAD SPACE: 뒤에 공백을 추가해서 비교
    • NO PAD : 뒤에 공백을 추가하지 않고 그대로 비교

문자열 이스케이프 처리

숫자

  • 숫자를 지정하는 타입은 값의 정확도에 따라 참값(Exact value)과 근삿값으로 구분가능
    • 참값은 소수점 이하의 값의 유무와 관계없이 정확히 그 값을 그대로 유지
      • INTEGER
      • INT
      • DECIMAL
    • 근사값은 부동소수점
      • FLOAT
      • DOUBLE

정수

부동 소수점

DECIMAL

정수 타입의 컬럼 생성시 주의사항

AUTO_INCREMENT

  • auto_increment_offset
  • auto_increment_increment
  • AUTO_INCREMENT 옵션을 사용한 칼럼은 반드시 프라이머리키나 유니크키의 일부로 정의해야함
    • 프라이머리키나 유니크키가 여러개의 칼럼으로 구성되면 AUTO_INCREMENT로 증가하는 패턴이 스토리지 엔진에 따라 달라짐
      • MyISAM: 프라이머리키나 유니크키 아무 위치에나 사용 가능
      • InnoDB: AUTO_INCREMENT 칼럼으로 시작되는 인덱스가 존재해야함

날짜와 시간

  • 날짜만 저장, 시간만 저장 둘다 저장 가능
  • 여러가지 날짜 타입 지원
    • YEAR: 1바이트
    • DATE: 3바이트
    • TIME: 3바이트 + 밀리초 단위 저장 공간
    • DATETIME: 5바이트 + 밀리초 단위 저장 공간
    • TIMESTAMP: 4바이트 + 밀리초 단위 저장 공간
  • 마이크로초까지 저장가능한 DATETIME(6)는 5 + 3으로 8 바이트 사용
    • 밀리초 단위 자릿수
    • 1,2 1바이트
    • 3,4 2바이트
    • 5,6 3바이트
  • NOW(6)와 같이 밀리초를 가져올수 있음
  • MySQL의 날짜 타입 칼럼은 타임존 정보가 저장되지 않음
    • DATETIME, DATE는 현재 DBMS 커넥션의 타임존과 관계없이 클라이언트로부터 입력된 값을 그대로 저장, 조회
    • TIMESTAMP는 항상 UTC 타임존으로 저장
      • 타임존이 달라져도 값이 자동으로 보정됨
  • JDBC 연결시 타임존에 따른 동작 
    // JVM의 타임존
System.setProperty("user.timezone", "Asia/Seoul");
// JDBC
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306?serverTimezone=Asia/Seoul");
    • 이는 MySQL의 타임존을 알려주는것
    • JDBC는 자동으로 MySQL 서버의 타임존을 인식
      • 인식하지 못하는 경우 위 serverTimezone을 사용
    • MySQL 서버의 칼럼 타입이 TIMESTAMP이든 DATETIME이든 관계없이 JDBC 드라이버는 날짜 및 시간정보를 MySQL 타임존에서 JVM 타임존으로 변환해서 출력
      • 이는 ResultSet 클래스에서 MySQL의 DATIME 칼럼 값을 가져올 수 있는 함수가 getTimestamp()뿐이기 때문
      • 다른방식으로 가져온다면 타임존이 적용되지 않을 수 있음
      • ORM은 코드 내부적으로 값을 자동으로 패치해서 응용의 코드로 변환
        • 타임존 작동여부는 확인해보는 것을 권장
      • 응용에서 시간정보를 강제로 타임존 변환하거나 MySQL의 CONVERT_TZ()를 이용해 타임존 변환을 하지 않도록 해야함
      • 타임존 관련 설정은 한 번 문제가 되기 시작하면 해결하기 매우 어려운 문제가 될 수 있음
  • 이미 데이터를 가지고 있는 경우 MySQL 서버의 타임존을 변경해야한다면 타임존 설정과 함께 DATETIME 칼럼의 값도 변환필요
    • 서버의 타임존은 system_time_zone 변수
    • DATEIME은 CONVERT_TZ()와 같은 함수 사용
    • TIMESTAMP는 항상 UTC로 저장되므로 별도 처리 필요 없음
  • system_time_zone은 일반적으로 운영체제의 타임존을 상속 받음

자동 업데이트

  • DEFAULT CURRENT_TIMESTAMP: 레코드가 INSERT될 때 시점을 자동으로 업데이트
  • ON UPDATE CURRENT_TIMESTAMP: 레코드가 UPDATE될때 자동으로 업데이트

ENUM과 SET

  • ENUM과 SET은 모두 문자열 값을 MySQL 내부적으로 숫자값으로 매핑해서 관리하는 타입
    • 데이터베이스에는 인코딩된 알파벳이나 숫자 값만 저장되므로 그 의미를 파악하기 쉽지않다는 단점이 있음

ENUM

  • ENUM의 가장큰 용도는 코드화된 값을 관리하는것

SET

  • SET과 ENUM의 가장큰 차이점은 SET은 하나의 컬럼에 1개 이상의 값을 저장할 수 있음

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