15. 데이터 타입

칼럼의 데이터 타입과 길이 선정할 때 주의 사항

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

실제 저장되는 값의 특성을 고려하지 않고 가능한 최대 길이 값을 기준으로 칼럼의 길이를 선택하면

디스크의 공간, 메모리, cpu의 자원이 함께 낭비되고 SQL의 성능이 저하된다

만약 칼럼의 타입이 잘 못 선정되거나 길이가 부족하면

서비스 도중에 스키마 변경이 필요할 수 있다
스키마 변경 작업은 서비스 중지나 읽기 전용 모드로의 전환 작업이 필요할 수 있다
실제로 저장되는 값의 성격을 정확히 분석하고 최적의 타입과 길이를 선정하는 것이 중요하다

15.1 문자열(CHAR와 VARCHAR)

문자열 칼럼은 CHAR 와 VARCHAR 타입 중 어떤 타입을 사용할지 결정해야 한다

1.5.1.1 저장 공간

CHAR 와 VARCHAR 공통점은 문자열을 저장할 수 있는 데이터 타입이고 가장 큰 차이는 고정 길이인지 가변 길이인지이다

CHAR - 고정 길이

실제 입력되는 칼럼 값의 길이에 따라 사용하는 저장 공간의 크기가 변하지 않는다
실제 저장되는 값의 크기가 얼마인지 별도로 저장할 필요가 없어서 추가 저장 공간은 필요하지 않음

VARCHAR - 가변 길이

최대로 저장할 수 있는 값의 길이는 제한돼 있지만, 그 이하의 값이 저장되면 그만큼 저장 공간이 줄어든다
값의 유효 크기가 얼마인지를 별도로 저장해 둬야 하므로 1~2바이트의 저장 공간이 추가로 더 필요

CHAR(1) vs VARCHAR(1)

사용자는 문자 집합에 따라 실제 저장 공간을 1~4바이트 까지 사용한다

CHAR 타입에 저장될 때는 추가 공간이 더 필요하지 않지만

VARCHAR는 문자열의 길이를 관리하기 위한 1~2바이트의 공간을 추가로 사용한다

VARCHAR 타입의 길이가 255바이트 이하면 1바이트, 256~65535이하의 길이는 2바이트를 사용한다
VARCHAR 타입의 최대 길이는 2바이트로 표현할 수 있는 이상은 사용할 수 없다

MySQL 에서는 하나의 레코드에서 TEXT 와 BLOB 타입을 제외한 칼럼의 전체 크기가 64KB를 초과할 수 없다

만약 VARCHAR 타입의 칼럼 하나만 있다면?

최대 64KB 크기의 데이터를 저장할 수 있다

하지만 다른 칼럼이 40KB를 사용하고 있다면?

VARCHAR 타입은 24KB만 사용할 수 있다
초과하는 VARCHAR 타입의 칼럼을 생성하려고 하면 에러가 발생하거나 자동으로 TEXT 타입으로 대체된다
칼럼을 새로 추가할 때 VARCHAR 타입이 TEXT 타입으로 자동 변환됐는지 확인해 보는 것이 좋다

문자열 타입에 대해 얘기할 때 1문자와 1바이트는 구분해서 사용

문자 집합에 따라 1~4바이트까지 공간 사용할 수 있기 때문

VARCHAR가 64KB 크기까지 저장할 수 있다고해서 65536개의 글자를 저장할 수 있는 것은 아니다

아시아권의 언어라면 저장 가능한 글자 수는 반으로 준다
UTF-8 문자를 저장한다면 실제 저장 가능한 글자 수는 1/4로 줄어든다

문자열 값의 길이가 고정이냐 가변이냐만으로 정하지 않고 다음과 같은 판단 기준을 적용한다

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

길이 말고도 값이 얼마나 자주 변경되느냐가 중요하다

CREATE TABLE tb_test (
    fd1 INT NOT NULL
    fd2 CHAR(10) NOT NULL
    fd3 DATETIME NOT NULL
);

다음 테이블에서 fd2가 ABCD인 값으로 데이터를 추가했다가 ABCDE로 변경해보면

CHAR(10) 타입에 경우

fd2 칼럼을 위해 공간이 10바이트가 준비돼 있으므로 변경되는 칼럼의 값을 업데이트만 하면 된다

VARCHAR(10) 타입에 경우

그림 15.2와 같이 fd2 칼럼에 4바이트밖에 저장할 수 없는 구조로 만들어져 있어서 길이가 더 큰 값으로 변경될 때는 레코드 자체를 다른 공간으로 옮겨서 (ROW migration) 저장해야 한다

주민등록번호처럼 항상 값의 길이가 고정일 때는 당연히 CHAR 타입 사용하고

값이 2~3바이트 차이가 나더라도 자주 변경될 수 있는 부서 번호나 게시물의 상태 값 등은 CHAR 타입을 사용하는 것이 좋다

자주 변경돼도 레코드의 이동이나 분리는 CHAR 타입으로 인해 발생하는 2~3바이트 공간 낭비보다 더 큰 공간이나 자원을 낭비하게 된다

CHAR 와 VARCHAR 타입 뒤에 지정하는 숫자는 칼럼의 바이트가 아니라 문자의 수를 의미한다

다른 DBMS를 사용하면 혼동할 수 있음

그래서 CHAR(10) 라고 정의해도 실제로 디스크나 메모리에 사용되는 공간이 각각 달라진다

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

MySQL 서버에서는 utf8 과 utf8mb4 문자 집합이 별도로 존재

utf8

5.5 이전 버전 UTF-8 인코딩을 저장하기 위해 사용
한 글자당 최대 3바이트까지만 지원

utf8mb4

5.5 버전 부터 지원되기 시작
한 글자당 최대 4바이트까지 지원

UTF-8 인코딩에서 각 문자가 저장 공간에 따라 다음과 같이 구분됨

1바이트 : ASCII 문자
2바이트 : 추가 알파벳 문자
3바이트 : BMP(Basic Multilingual Plane) 문자
4바이트 : SMP & SIP & ...

utf8은 3바이트까지만 저장을 지원하니 SMP와 SIP와 이후 플레인 문자는 저장할 수가 없었다

이모티콘의 발전으로 예상외로 심각한 문제가 되어버림

이 문제를 해결하기 위해 utf8mb 라는 새로운 문자 집합이 도입됐다

utf8mb4 문자 집합은 utf8 문자 집합의 수퍼 셋(상위 셋)이기 때문에 utf8 문자 집합을 사용하던 테이블을 utf8mb4 문자 집합으로 전환(Convert)하는 것은 아무런 문제가 안됨

utf8mb4가 만들어지면서 utf8mb3라는 문자 집합이 만들어 졌는데 MySQL 8.0 버전에서는 utf8 문자 집합은 utf8mb3을 가리키는 별칭으로 정의돼 있다

utf8mb3 문자 집합은 곧 Deprecated 될 예정이고 이 문자 집합이 제거되면 utf8 별칭은 utf8mb4를 가리킬 예정

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

(참고 11.7.1) MySQL 서버는 데이터가 변경되는 도중에도 스키마 변경을 할 수 있도록 온라인 DDL 기능을 제공
- 모든 스키마 변경이 온라인 DDL로 가능한 것은 아님
p.361
- value 칼럼을 VARCHAR(60) 으로 정의
- case1. value 컬럼을 VARCHAR(63) 변경
- 기존 60으로 설정한 상태에서 길이저장을 위해 1바이트가 추가로 할당되었고, 63까지는 추가된 1바이트 내에서 해결 가능
- ALGORITHM=INPLACE, LOCK=NONE옵션으로 스키마 변경 가능
- case2. value 컬럼을 VARCHAR(64) 변경
- 64의 경우 2바이트가 필요하므로 저장공간 이동 필요
- ALGORITHM=COPY LOCK=SHARED옵션으로 스키마 변경 가능
컬럼값의 길이가 바뀔 가능성이 있는 경우 미리 조금 크게 설계하는 것을 권장

15.1.3 문자집합(캐릭터 셋)

MySQL 서버에서 각 테이블의 칼럼은 모두 서로 다른 문자 집합을 사용해 문자열 값을 저장할 수 있음
- CHAR, VARCHAR, TEXT
테이블의 기본 문자열이 UTF-8이라고 해도 각 칼럼에 대해 EUC-KR 등으로 별도 지정이 가능함
여러나라의 언어를 동시에 지원하기 위해 기본적으로 UTF-8 문자집합(utf8mb4)을 사용하는 추세
- ANSI 표준에서는 NCHAR, NATIONAL CHAR 과 같은 칼럼으로 지원
- MySQL에서는 NCHAR 타입을 사용하면 UTF-8 문자 집합을 사용하는 CHAR 타입으로 생성
사용가능한 문자 집합은 SHOW CHARCTER SET 명령으로 확인 가능 (p.363 참고)
- 해당 문자 집합의 기본 코릴레이션이 무엇인지 Default collation 칼럼에서 확인 가능
- 명시하지 않고 문자집합만 지정한 경우 설정되는 콜레이션을 의미
MySQL 에서 문자 집합을 설정하는 시스템 변수 (p.364 참고)
- 대부분 기본값은 utf8mb4로 설정되어 있음

15.1.3.1 클라이언트로부터 쿼리를 요청했을 때의 문자 집합 변환

MySQL 서버는 클라이언트로 받은 메세지가 character_set_client시스템 변수에 지정된 문자 집합으로 인코딩돼 있다고 판단하고 받은 문자열 데이터를 character_set_connection에 정의된 문자집합으로 변환
하지만 SQL 문장에 별도 문자집합이 지정된 문자열은 반환 대상에 포함하지 않음
- ex) SELECT * FROM employees WHERE first_name=_latin1'Matt';
- 인트로듀서: 쿼리 내에서 별도로 문자집합을 설정하는 지정자. 위 예시에서는 '_latin1' 이 인트로듀서
- 언더스코어 기호와 문자 집합의 이름을 붙여서 표현

15.1.3.2 처리 결과를 클라이언트로 전송할 때의 문자 집합 변환

MySQL 서버는 쿼리의 결과를 character_set_results 변수에 설정된 문자 집합으로 변환해 클라이언트로 전송
이때 결과 셋에 포함된 칼럼값이나 칼럼명과 같은 메타데이터도 모두 동일한 시스템 변수에 설정된 문자집합으로 인코딩되어 전송됨
변환 전의 문자 집합과 변환해야 할 문자 집합이 똑같다면 별도의 문자 집합 변환 작업은 모두 생략함
시스템 변수 character_set_client, character_set_results, character_set_connection 3개는 클라이언트 프로그램이나 클라이언트의 GUI 도구에서 마음대로 변경할 수 있는, 세션 변수이면서 동적 변수
- 변수값 변경 설정 방법 p.366 참고

mojh7 / real-mysql-study

2023/06/09 ~ 2023/06/13 #56

다음 스터디

정리 범위

정리 범위 무작위 선택 봇 🔎