MySQL 데이터 타입 및 최적의 선택 방법 3가지

MySQL은 다양한 데이터 타입을 지원하고 있는데, 데이터를 저장하는 데 적합한 데이터 타입을 고르는 것은 좋은 성능을 얻는 데 필수적이다. MySQL 데이터 타입 및 최적의 선택 하는 방법에 관하여 다양한 타입과 특징에 대해서 살펴보고 MySQL 데이터 타입 및 최적의 선택 방법 3가지에 대해서 알아보는 시간을 가지도록 하겠습니다.

정수 타입

MySQL 데이터 타입 및 최적의 선택 방법으로 정수를 저장할 거라면 정수(integer) 타입인 TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 중 하나를 사용할 수 있다. 이 타입들은 8, 16, 24, 32, 64 비트의 저장 공간이 필요하다. 정수 타입에는 UNSIGNED (부호 없음) 속성을 사용할 수도 있으며, 이 속성은 음수 값을 허용하지 않는 대신 저장 가능한 양수 값의 한도를 거의 두 배로 늘려준다.

어떤 MySQL data type 선택했는지에 따라 MySQL에서 데이터를 메모리나 디스크에 저장하는 방식이 결정된다. 하지만 32비트 아키텍처라도 정수를 계산할 땐 보통 64비트 BIGINT 정수를 사용한다(일부 집합 함수는 예외적으로 DECIMAL이나 DOUBLE을 사용한다)

MySQL에선 INT(11)과 같이 정수 타입의 ‘길이’를 명시할 수 있다. 하지만 이 기능은 값의 유효 범위를 제한하진 않고, MySQL 클라이언트 같은 프로그램의 화면 출력용으로 예약하는 문자의 개수를 지정할 뿐이므로 대부분의 응용프로그램에 선 별다른 의미가 없다. 저장이나 계산 측면에서 MySQL int 범위 INT(1)은 INT(20)과 동일하다.

참고로, Unsigned 옵션과 함께 사용하면 음의 정수 사용시 아래와 같은 에러가 발생한다.

Error Code: 1264. Out of range value for column 'total' at row 1

정수 타입 범위

실수 타입

MySQL 데이터 타입 및 최적의 선택 방법으로 실수는 소수부가 있는 숫자다. 하지만 단지 소수부 때문에 실수가 있는 건 아니다. MySQL BIGINT 범위로 표현할 수 없을 만큼 큰 정수도 DECIMAL을 이용하면 저장 가능하다. MySQL에서는 정확한 타입과 부정확한 타입을 모두 지원한다.

DECIMAL

• 고정소수점 (fixed-point types)
• NUMERIC 타입도 포함
• 소수부를 정확하게 저장하는 용도로 사용
• DECIMAL(M,D)
  • M은 소수 부분을 포함한 실수의 총 자릿수를 나타내며 최대값은 65
  • D는 소수 부분의 자릿수를 나타내며, D가 0이면 소수 부분을 가지지 않음

FLOAT, DOUBLE

• 부동소수점 (floating-point types)
• Decimal과 마찬가지로 정수부와 소수부를 구분하여 사용할 수 있다.
• FLOAT 타입도 포함
• Float은 4 바이트, Double은 8 바이트를 사용한다.
• 대략적인 숫자 데이터 값(근사값)을 표현할 때 사용
• DOUBLE(M,D)
  • M은 소수 부분을 포함한 실수의 총 자릿수
  • D는 소수 부분의 자릿수

문자열 타입

MySQL 데이터 타입 및 최적의 선택 방법으로 문자열 데이터 타입을 몇 가지 밖에 지원하지 않지만 각 타입마다 여러 가지 변형이 있으며 특히 VARCHAR 타입과 CHAR 타입의 서로 다른 특징을 파악해두면 MySQL data type 선택 방법에 도움이 될 것으로 보여집니다.

CHAR

• 고정 길이이므로 MySQL에서는 항상 지정된 문자 개수에 맞춰 충분한 공간을 할당한다.
• 아주 짧은 문자열을 저장하고자 할 때나 모든 값이 거의 같은 길이일 때 유용하다. 예를 들면 MD5의 경우 값의 길이가 언제나 같기 때문에 이 값을 저장할 땐 CHAR가 적합하다. 고정 길이의 행은 단편화가 거의 발생하지 않으므로 자주 바뀌는 데이터를 저장할 때라면 CHAR가 VARCHAR보다 유리하다.
• 아주 짧은 컬럼에도 CHAR를 사용하는 것이 VARCHAR를 사용하는 것보다 효율적이다. Y와 N값만 저장하는 것처럼 한 바이트짜리 문자 셋이라면 CHAR(1)은 1바이트만 사용하지만 VARCHAR(1)에선 길이 바이트 때문에 2바이트를 쓴다.

VARCHAR

• 가변 길이의 문자열을 저장하는 가장 흔한 문자열 data type
• 필요한 만큼만 공간을 사용하기에 고정 길이 타입보다 저장 공간을 적게 사용하기도 한다.
• 공간을 절약하므로 성능에 도움이 되지만 행이 가변 길이이므로 행을 업데이트할 때 행의 길이가 길어지기도 하며, 이러한 이유로 추가적인 작업이 필요할 수도 있다.
• 보통 컬럼의 최대 길이가 평균 길이보다 훨씬 클 때나 필드가 업데이트되는 일이 드물어서 단편화가 문제되지 않을 때 혹은 UTF-8 처럼 문자마다 사용하는 바이트 수가 다른 복잡한 문자 셋을 사용할 때 유용하다.

예를들어 ‘good’ 값을 저장하면 VARCHAR(4) 컬럼이든 VARCHAR(100) 컬럼이든 같은 공간을 차지한다. 하지만 MySQL 에서 내부적으로 값을 저장할 때 고정 크기의 메모리를 할당하므로 큰 컬럼을 사용하면 메모리를 더 많이 사용하게 될수도 있다. 그러면 메모리 안에 있는 임시 테이블을 사용하는 연산이나 정렬이 필요한 경우 성능이 안 좋을 수 있다. 그러므로 가장 좋은 방법은 필요한 만큼만 공간을 할당하는 것이다.

BLOB / TEXT

• MySQL 데이터 타입 및 최적의 선택 방법으로 BLOB 및 TEXT 군의 유일한 차이점이라곤 BLOB 타입은 문자 셋이나 콜레이션이 없는 바이너리 데이터를 저장하는 반면, TEXT 타입은 문자 셋과 콜레이션이 있는 텍스트 데이터를 저장한다는 것이다.
• MySQL data type 레코드 최대 길이는 64K로 제한되기 때문에 그 이상의 대량 데이터를 저장해야 할 때 사용한다.

• BLOB – 바이너리 형식 (TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB)
• TEXT – 텍스트 형식 (TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT)

비교 및 정렬 연산

• text : character set에 의한 연산
• blob : 저장된 값(binary)에 의한 연산

JSON

• MySQL 5.7 버전부터 JSON 타입 데이터를 지원합니다.
• JSON 타입 데이터 적재할 경우 Binary 형태로 저장하게 됩니다.

날짜 및 시간 타입

MySQL 데이터 타입 및 최적의 선택 방법으로 날짜와 시간 값에 쓰는 데이터 타입이 다양하다. MySQL이 저장할 수 있는 가장 작은 시간 단위는 1초이지만 시간 연산은 마이크로초 단위로 수행 가능하다.

DATETIME

• 날짜와 시각을 표현하는 ANSI 표준 방식
• 기본적으로 날짜와 시간을 초단위 까지 저장하는 타입
• Fractional Seconds를 지정함으로써 초단위 이하 까지 저장 가능한다. (최대 6자리로 백만분의 1초까지 저장 가능)
• 1001년부터 9999년까지의 값을 1초 단위로 저장할 수 있으며, 날짜와 시각을 YYYYMMDDHHMMSS 포멧의 정수 값으로 묶는데 시간대에는 영향을 받지않는다.
• DATETIME은 8바이트의 저장 공간을 사용한다.

DATE, TIME

• 날짜 또는 시간만 저장

TIMESTAMP

• 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07
• datetime과 마찬기지로 Fractional Seconds의 표현이 가능하다. (최대 6자리로 백만분의 1초까지 저장 가능)
• TIMESTAMP 타입은 1970년 1월 1일 자정(그리니치 평균시)을 기준으로 몇 초가 지났는지를 저장하며, Unix 타임스탬프와 동일하다.
• TIMESTAMP는 저장 공간을 4바이트 사용하므로 값의 범위가 DATETIME보다 훨씬 작고, 1970년 ~ 2038년 사이의 값만 저장할 수 있다.
• MySQL에서 TIMESTAMP 컬럼에 값을 지정하지 않은 채로 행을 추가하면 첫 번째 TIMESTAMP 컬럼에 현재 시작을 넣는다. 또 UPDATE문에 업데이트 할 첫 번째 TIMESTAMP 값을 명시적으로 할당하지 않은 채로 행을 업데이트 하면 TIMESTAMP 컬럼의 값을 자동으로 업데이트한다.
• 다른 데이터 타입과는 달리 TIMESTAMP 컬럼은 기본적으로 NOT NULL 이다.
• time_zone의 값에 의존한다. 즉, time_zone이 변경되면 값도 변경된다.
• 타입 정의시 CURRENT_TIMESTAMP 옵션으로 데이터 입력 시간이 자동 저장 가능하다.

CREATE TABLE timestamp_update (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
 
insert into timestamp_update (name) values ('A');
 
select * from timestamp_update;
+----+------+------------------------+-----------------------+
| id   | name | created_at              | updated_at             |
+----+------+------------------------+-----------------------+
|  1   | B      | 2021-01-28 14:29:57 | 2021-01-28 04:29:57 |
+----+------+------------------------+-----------------------+

• 데이터 변경 시 CURRENT_TIMESTAMP로 자동 업데이트가 가능한다.

update timestamp_update
    -> set name = 'B'
    -> where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
select * from timestamp_update;
+----+------+------------------------+-----------------------+
| id   | name | created_at              | updated_at             |
+----+------+------------------------+-----------------------+
|  1   | B      | 2021-01-28 14:29:57 | 2021-08-31 14:32:36 |
+----+------+------------------------+-----------------------+

• 단, 데이터가 실제로 업데이트가 되어야 해당 칼럼도 업데이트가 된다.

update timestamp_update
    -> set name = 'B'
    -> where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0 ===> 실제로 업데이트 된 로우가 없음
 
select * from timestamp_update;
+----+------+------------------------+-----------------------+
| id   | name | created_at              | updated_at             |
+----+------+------------------------+-----------------------+
|  1   | B      | 2021-01-28 14:29:57 | 2021-08-31 14:32:36 |
+----+------+------------------------+-----------------------+

DATETIME vs TIMESTAMP

• 가장 큰 차이점은 time_zone의 영향 여부이다.

비트(Bit) 데이터 타입

• Bit 타입은 비트의 값을 저장하며 0과 1로 구성되는 바이너리(Binary) 값을 저장할 수 있다.

• Bit(M) : M의 범위는 1 부터 64까지 설정할 수 있으며, 명시한 M 비으틔 값을 저장할 수 있다.

• MySQL data type 중 Bit를 숫자 타입이 아닌 문자열 타입으로 취급한다. bit(1) 값을 조회하게 되면 그 결과 타입도 문자열이지만 내용물은 바이너리 값인 0이나 1이지 ASCII 값인 “0”이나 “1”이 아니다. 하지만 값을 숫자 기반으로 조회한다면 비트 문자열이 변환된 숫자가 결과로 나온다.

LOB 타입과 Off-Page

• Page 사이즈가 16K인 BTree 형태의 Clustered Index에 최소 2개(8K2)의 Row를 저장해야 하는데, 해당 Row의 크기가 너무 클 경우 별도의 공간에 저장한다.
  • off-page 또는 overflow-page라고 한다.
• InnoDB의 Row Format 종류
  • Redundant
  • Compact
  • Dynamic (기본값)
  • Compressed
  • 참고 : https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html
• Dynamic 타입
  • InnoDB의 기본 Row Format
  • Row의 크기가 너무 클 경우 가장 길이가 긴 컬럼이 off-page로 선정
  • 길이가 긴 컬럼(text, blob)의 경우 20byte의 포인터 정보만 저장하고 실제 내용은 별도의 off-page에 저장
• Compact 타입
  • 가변길이 컬럼의 길이가 768byte를 초과할 경우에 나머지를 별도의 off-page에 저장

MySQL 데이터 타입 및 최적의 선택 방법 3가지

타입은 작을수록 좋다

MySQL 데이터 타입 및 최적의 선택 방법으로 일반적으로 데이터를 저장하고 표현하는 데 문제가 없는 데이터 타입 중 가장 작은 것을 골라야 한다. 보통 작은 데이터 타입일수록 디스크나 메모리, CPU 캐시에 더 적은공간을 사용하기 때문에 더 빠르다. 게다가 작은 데이터 타입일수록 CPU 리소스를 덜 소비한다.

하지만 저장할 값의 크기를 너무 작게 추정하지 않도록 주의해야 한다. 스키마의 이곳저곳에서 데이터 타입의 범위를 늘리는 것은 어렵고, 시간을 많이 소용하게 되는 작업이기 때문이다.

타입은 단순한 게 좋다

MySQL 데이터 타입 및 최적의 선택 방법으로 보통 간단한 데이터 타입을 처리할수록 CPU 사이클로 덜 소비한다. 예로 문자 비교보다는 정수 비교가 비용이 더 저렴하다. 문자 비교는 문자 셋 (Character set)과 콜레이션 (Collation) 으로 인해 복잡해지기 때문이다. 날짜와 시간은 문자열로 저장하지 말고, MySQL의 내장 형식에 저장해야 하며 IP 주소는 정수를 이용해 저장해야 한다.

IP 주소를 사용할 수 있는 함수로 INET_ATON 함수와 INET_NTOA 함수 활용 가능

가능하면 NULL을 쓰지 말자

MySQL 데이터 타입 및 최적의 선택 방법으로 칼럼은 되도록이며 NOT NULL (NULL 허용 안함)로 정의해야 한다. NULL 허용이 기본값이다 보니 사용하는 응용 프로그램이 NULL (값이 없음)을 저장할 필요가 없을 때조차 테이블에 NULL을 허용하는 컬럼을 포함시킨 경우가 많다. NULL을 저장할 의도가 아니라면 컬럼을 NOT NULL로 정의하자.

NULL 허용 컬럼은 저장 공간도 더 많이 사용하며 NULL 허용 컬럼을 인덱싱할 땐 항목마다 한 바이트씩 더 들어간다. 만약 ‘값이 없다’는 사실을 테이블에 기록해야 하더라도 굳이 NULL을 쓸 필요는 없을지 모른다. NULL 대신 0이나 특수한 어떤 값, 빈 문자열 등을 사용하는 걸 고려해보자.

마치며

지금까지 MySQL 데이터 타입 및 최적의 선택 방법 대해서 알아보았는데, 위의 MySQL data type 이외에도 더 많은 타입이 있지만 여기에서는 범용적으로 많이 사용하고 있는 MySQL data type에 대해서 알아보았고 데이터베이스 설계 시 각각의 Data Type 특성을 파악하여 활용하고 더불어 SQL 작성시에도 병행하여 참고하면 좋을 것 같습니다.

• 참고자료
  • https://dev.mysql.com/doc/refman/8.0/en/data-types.html
  • https://www.mysqltutorial.org/mysql-data-types.aspx
  • https://dev.mysql.com/doc/refman/8.0/en/json.html
  • MySQL 성능최적화 Book