[240513] MYSQL 모델링에서 TEXT 와 LONGTEXT 의 차이

* 이 글은 mysql8  기준이다.

MYSQL 테이블을 모델링 하다 보면, INT 를 써야할지, BIGINT 를 써야할지. 또는 TEXT 를 써야할지 LONG TEXT 를 써야할지 고민이 될 때가 있다. 고민에 대한 나름의 답을 내고자,공식 문서를 참고하여 내용을 정리해 보았다.

참고 문서(https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html)

이번 글에서는 INT vs BIGINT, 그리고 TEXT vs LONGTEXT 각각의 차이를 설명할 예정이다.숫자와 문자는 성격이 전혀 다른 데이터 타입이므로, 설명도 구분해서 진행하겠다.

먼저 숫자이다. 참고 문서 (https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-numeric)

숫자는 컬럼의 정의에 따라서 동일한 숫자 1을 저장하더라도, 1 byte가 될 수있고, 8 byte 가 될 수 있다. 예를 들어 게임 플레이 로그 테이블(이력 테이블)과 같이 레코드의 삽입 순서를 나타내는 식별 컬럼(가상키)을 만들 때, 주로 컬럼명을 'seq' ,'id', 'no' 등으로 지정하고, 여기에 AUTO_INCREMENT 옵션을 추가하여 순차적인 정수 값이 자동으로 할당되도록 구현하는 경우가 많다.

여기서 이 컬럼의 타입을 INT 를 쓸지 BIGINT를 쓸지에 대해 고민하게 될텐데, INT 보다는 BIGINT 가 표현할수 있는 숫자가 더 많음으로 관성적으로 BIGINT를 많이 쓴다. 그런데 이렇게 무조건적으로 BIGINT만 사용한다는 건, MySQL에서 어떤 의미를 가지게 될까?

결론부터 말하자면 INT 는 4byte 를 쓰고 BIGINT 는 8byte 를 사용하기 때문에, 동일한 정수 '1' 값을 삽입하더라도, INT에서는 4byte 가 쓰여지고, BIGINT 에서는 8byte 를 쓰는 디스크 사용량에서 차이가 발생하게 된다. 만약 로그 테이블의 레코드가 10 건이라면 INT 컬럼에서는 40byte 를 썻을 것이고, BIGINT 에서는 80 byte 를 썻을 것이다. 레코드가 100만개 라면, 4.7mb 의 용량 차이가 발생한다.

테이블의 크기도 문제지만, 또다른 문제는 인덱스 구조에도 영향을 준다는 점이다. 만약 seq 컬럼이 PK 사양으로 정의가 되어있다면, 모든 보조 인덱스에는 PK 값이 함께 포함하기 때문에 디스크 사용량 차이가 반복적으로 발생한다. 쉽게 말해 PK의 크기가 크다면 모든 보조 인덱스의 크기도 커진다는 의미이다.

문자에서도 이와 유사한 문제점이 있다.

mysql의 varchar, text 는 charset encoding (또는 collation)이 무엇으로 정의하였느냐에 따라서 하드디스크 사용 용량이 달라진다. 영어 숫자만 지원하는 latin1 의 경우에는 글자마다 1byte 가 사용된다. 한글을 지원하는 euckr 의 경우에는 영문 숫자는 1byte 그 외의 글자는 2byte 가 사용된다.

오늘날의 사실상 mysql 기본값이 된 utf8mb3 의 경우에는 1~ 3byte 를 사용하는데, 영어 숫자라면 1byte, 유로 기호(euro sign)라면 2byte, 한글과 한자의 경우에는 3byte 를 사용한다. 최근에 새로 추가된 utf8mb4 의 경우에는 utf8mb3 와 동일하지만, 4byte를 사용하는 이모티콘(이모지) 를 다룰 수 있게 되었다. 즉 utf8mb4는 1~4byte 를 사용한다.

utf8mb3 and utf8mb4 character sets can require up to three and four bytes per character, -https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-numeric

TEXT, MEDIUMTEXT, LONGTEXT는 저장하는 문자열에 대한 메커니즘은 기본적으로 완벽히 똑같다. 그런데 왜 TEXT는 최대 65,535 byte까지만 저장할 수 있고, LONGTEXT는 약 4GB까지 저장할 수 있을까? 이는 저장되는 문자열의 길이를 나타내는 메타데이터(prefix number)가 어떤 크기의 숫자 타입으로 저장되느냐의 차이 때문이다.

TEXT는 UNSIGNED SMALLINT 를 사용하는데, UNSIGNED SMALLINT 는 2byte 를 사용한다. 2byte 는 2의 16승을 의미하고, 따라서 TEXT 컬럼은 2^16-1 = 65,535 개의 글자 까지 표현할 수 있다는 제약이 생기게 된다. (여기서 65535 개의 글자란 의미는 latin1 의 영문은 1글자당 1byte 임으로 65,535 개의 글자를 표현할 수있다가 성립 된다.)

동일한 개념으로 LONGTEXT는 4byte(prefix) 사용하는 UNSIGNED INT 를 채택하고 있는데, 4byte는 2의 32승임으로 2^32-1 = 4,294,967,295 개의 글자(약 4gb)를 표현할 수 있다가 성립하게 된다. 

결국 TEXT, MEDIUMTEXT, LONGTEXT의 입력 가능 크기 차이는 문자열 길이를 나타내는 prefix number를 각각 어떠한 정수 타입을 쓰느냐에 따라서 차이가 나는 것이다. 아래는 영문으로만 된 글을 썻다고 가정했을 때를 정리한 내용이다.

타입	Prefix 크기	Prefix 타입	최대 글자 갯수	최대 저장 크기 (환산)
TEXT	2 byte	UNSIGNED SMALLINT	65,535 개	약 64 KB
MEDIUMTEXT	3 byte	MEDIUMINT	16,777,215 개	약 16 MB
LONGTEXT	4 byte	UNSIGNED INT	4,294,967,295 개	약 4 GB

 

* 참고로 text 와 varchar 는 같으면서도 살짝 다른 부분이 있다. 그래서 이에 대해서는 https://glqdlt.tistory.com/509 이 글에 정리하였으니 참고하도록 하자.

처음 보면 이러한 차이가 크게 의미 없어 보일 수 있지만 서비스가 고도화 되면 이야기가 달라지게 된다. 예를 들어 상황을 가정해보자. 예를 들어 "hello"라는 5글자의 문자열을 각각 TEXT 컬럼과 LONGTEXT 컬럼에 삽입하는 상황이라면, "hello"는 모두 영문자로 구성되어 있으므로, latin1 또는 utf8mb4 문자셋에서는 각 글자당 1byte가 사용된다. 따라서 "hello"라는 문자열은 5byte가 필요하다.

여기서 TEXT 컬럼에 저장할 경우에는 하드디스크를 7byte 사용하게 된다. 2byte(prefix) + 5byte(hello 라는 문자 값) = 총 7byte 사용. LONGTEXT 컬럼에 저장할 경우에는 하드디스크를 9byte 가 사용한다. 4byte(prefix) + 5byte(hello 라는 문자 값) = 총 9byte 사용.

결과적으로 동일한 문자열을 저장하더라도 항상 2byte의 하드디스크 낭비가 발생하게 된다. 이 차이는 누적될수록 커진다.

저장 건수	차이 (2byte × 건수)	환산 후 용량 차이
10 건	2 byte × 10 = 20 byte	20 byte
100 건	2 byte × 100 = 200 byte	200 byte
1,000 건	2 byte × 1,000 = 2,000 byte	약 1.95 KB
1 만 건	2 byte × 10,000 = 20,000 byte	약 19.5 KB
100 만 건	2 byte × 1,000,000 = 2,000,000 byte	약 1.95 MB

이처럼 누적되면 의외로 의미 있는 용량 차이가 발생할 수 있다. TEXT 와 LONGTEXT 는 항상 2byte 의 차이가 발생함(TEXT와 MIDEUM TEXT는 1byte 차이) 으로 레코드가 얼마나 쌓일지를 가늠해서 어떠한 컬럼을 선택했느냐에 따른 용량 차이를 가늠해볼 수 있다.

오늘날에는 하드디스크의 가격이 매우 저렴해졌기 때문에, 소규모 서비스에서는 이러한 차이가 크게 중요하지 않을 수 있다. 하지만 1990년대만 해도 이러한 미묘한 차이는 매우 중요한 문제였다. 하드디스크가 비쌌으니깐 말이다. 오늘날에도 장기간 운영되는 서비스나 수억~수십억 건 이상의 데이터를 다루는 대규모 서비스에서는 이러한 차이도 충분히 고려할 가치가 있다. 

byte와 bit, 그리고 아스키 코드

DB 문자열 컬럼에 대해 얘기를 하고 보니, 1글자당 실제로는 디스크를 얼마나 사용하는 지 궁금증도 따라온다. TEXT 나 LONG TEXT 나 문자열을 다루는 방식은 같지만, 실제로 물리적으로 얼마나 디스크를 사용하는지는 인코딩과 문자열 내용에 따라 달라진다. 예를 들어 한글은 2byte 일까, 3byte일까? euckr 인코딩에서는 2byte 이고 utf8mb4 에서는 3byte 를 사용한다.

mysql에서 공백문자는 몇 byte 를 사용할까? mysql 의 varchar 는 공백 문자 "" 를 넣어도 1byte 를 사용한다. " " 띄어쓰기도 아닌, "" 인데도 1byte 를 먹는다. 반면 "" 공백문자가 아닌 NULL 은 디스크를 0 byte 사용한다.

이게 무슨 일일까? 이유는 VARCHAR 의 문자열 길이를 나타내는 메타데이터의 정수값 때문이다. VARCHAR 는 문자열 길이에 따라서 메타데이터 정수를 다르게 쓴다. VARCHAR(255) 이하는 1 byte 정수를 사용하고, VARCHAR(256)개 이상은 2 byte 정수 체계를 쓴다. 1 byte 는 2^8 승이고 이는 256 값이 됨으로 VARCHAR(255) 까지 쓸수 있는 것이다. 마찬가지로 2 byte는 2^16승이기 때문에, 65535 개의 정수를 표현할 수있다.

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. https://dev.mysql.com/doc/refman/8.4/en/char.html

그렇다면 왜 1byte 는 256개의 숫자만 표현할수 있고, 2byte 는 65535 개나 표현할수있는걸까?

1byte 와 아스키

앞서 계속 얘기한 것처럼 1 byte 는 256개의 숫자를 표현할수있다. 1 byte 는 8비트이고, 8비트는 2^8 승이란 의미이다. 2^8승이란 의미에서 2는 전기적인 신호가 ON/OFF 2개의 값을 가질수 있음을 나타내며, 8승이란 의미는 전기적신호를 받아들이는 소자가 8개 있다는 말이다. 이 전기적신호를 받아들이는 소자를 트랜지스터라고 부른다. 따라서 물리적 관점으로 보면 메모리에는 8개의 트랜지스터 소자가 모여 1byte 를 구성하고 있다는 말이 된다. 오늘날 우리가 사용하는 컴퓨터를 64비트 컴퓨터라고 부르는데, 이것도 이와 관련이 있다. 최초의 컴퓨터는 8비트였고, 이후로 16비트 컴퓨터, 32비트 컴퓨터, 오늘날의 64비트 컴퓨터까지, 모두 기본적인 데이터 처리 단위가 8비트(1byte ) 를 바탕으로 발전해왔다.

2^8 승을 풀어보면 256 이란 수가 나오는 것이고, 0-255개의 정수를 표현할수있다는 말이된다. 이 0-255 범위를 기반으로 문자를 표기한 인코딩 체계가 아스키 코드이다. 아스키 코드는 ASCII (American Standard Code for Information Interchange, 미국 정보 교환 표준 부호) 란 의미를 가지는데, 아케리칸이란 단어가 참 골떄린다. 아스키 코드보다는 아스키 인코딩이란 말이 적절해보이지만, 최초의 글자 표현체계였기에 오직 영어만 썻기 때문에 당시에는 굳이 "인코딩"이라는 용어를 쓸 필요가 없어 "아스키 코드"라는 명칭이 널리 사용되었다.

아래는 챗GPT 한테 정리해달라고 한 아스키코드표이다. 일상 회화에서 쓰일법한 영문 체계만 뽑았다.

ASCII 코드 테이블 (숫자 0-9, 대문자 A-Z, 소문자 a-z, 특수문자)

문자	10진수	16진수	설명
0	48	0x30	숫자 0
1	49	0x31	숫자 1
2	50	0x32	숫자 2
3	51	0x33	숫자 3
4	52	0x34	숫자 4
5	53	0x35	숫자 5
6	54	0x36	숫자 6
7	55	0x37	숫자 7
8	56	0x38	숫자 8
9	57	0x39	숫자 9
A	65	0x41	대문자 A
B	66	0x42	대문자 B
C	67	0x43	대문자 C
D	68	0x44	대문자 D
E	69	0x45	대문자 E
F	70	0x46	대문자 F
G	71	0x47	대문자 G
H	72	0x48	대문자 H
I	73	0x49	대문자 I
J	74	0x4A	대문자 J
K	75	0x4B	대문자 K
L	76	0x4C	대문자 L
M	77	0x4D	대문자 M
N	78	0x4E	대문자 N
O	79	0x4F	대문자 O
P	80	0x50	대문자 P
Q	81	0x51	대문자 Q
R	82	0x52	대문자 R
S	83	0x53	대문자 S
T	84	0x54	대문자 T
U	85	0x55	대문자 U
V	86	0x56	대문자 V
W	87	0x57	대문자 W
X	88	0x58	대문자 X
Y	89	0x59	대문자 Y
Z	90	0x5A	대문자 Z
a	97	0x61	소문자 a
b	98	0x62	소문자 b
c	99	0x63	소문자 c
d	100	0x64	소문자 d
e	101	0x65	소문자 e
f	102	0x66	소문자 f
g	103	0x67	소문자 g
h	104	0x68	소문자 h
i	105	0x69	소문자 i
j	106	0x6A	소문자 j
k	107	0x6B	소문자 k
l	108	0x6C	소문자 l
m	109	0x6D	소문자 m
n	110	0x6E	소문자 n
o	111	0x6F	소문자 o
p	112	0x70	소문자 p
q	113	0x71	소문자 q
r	114	0x72	소문자 r
s	115	0x73	소문자 s
t	116	0x74	소문자 t
u	117	0x75	소문자 u
v	118	0x76	소문자 v
w	119	0x77	소문자 w
x	120	0x78	소문자 x
y	121	0x79	소문자 y
z	122	0x7A	소문자 z
!	33	0x21	느낌표
"	34	0x22	큰따옴표
#	35	0x23	해시 기호
$	36	0x24	달러 기호
%	37	0x25	퍼센트 기호
&	38	0x26	앰퍼샌드
'	39	0x27	작은따옴표
(	40	0x28	여는 괄호
)	41	0x29	닫는 괄호
*	42	0x2A	별표
+	43	0x2B	더하기 기호
,	44	0x2C	쉼표
-	45	0x2D	하이픈
.	46	0x2E	마침표
/	47	0x2F	슬래시
:	58	0x3A	콜론
;	59	0x3B	세미콜론
<	60	0x3C	작은따옴표
=	61	0x3D	등호
>	62	0x3E	큰따옴표
?	63	0x3F	물음표
@	64	0x40	골뱅이
[	91	0x5B	여는 대괄호
\	92	0x5C	역슬래시
]	93	0x5D	닫는 대괄호
^	94	0x5E	캐럿 기호
_	95	0x5F	밑줄
`	96	0x60	백틱
{	123	0x7B	여는 중괄호
|	124	0x7C	파이프 기호
}	125	0x7D	닫는 중괄호
~	126	0x7E	물결표

만약 당신이 영어를 쓰는 사람이라면 아스키코드로도 일상적인 컴퓨팅을 다 할수가 있을 거다. 아스키코드의 A가 Ameriaca 라는 이유가 체감 되는 부분이다. 예를 들어 "hello123" 이라는 단어를 예시로 들어보자. "hello123" 은 8글자로 8 byte 가 된다. 이가 실제 컴퓨터 세계에서 데이터로 취급할때에는 아래처럼 된다.

"hello123" 을 2진법으로 표기 하면 아래와 같다.

2진법	문자	ASCII (또는 10진수)
01101000	h	104
01100101	e	101
01101100	l	108
01101100	l	108
01101111	o	111
00110001	1	49
00110010	2	50
00110011	3	51

이제 문제는 256개의 숫자를 넘겨야 하는, 한국어와 같은 언어들은 어떻게 처리해야할지의 대답이 필요한 시점이다. 정답은 256개를 초과한 수를 써야 함으로, 1 byte 를 더 추가로 사용한다. 1 byte + 1 byte , 즉 2 byte 를 쓴다면 2^16승이고, 이는 512개의 숫자를 표현할수있다가 된다. 만약 3 byte 를 쓴다면 2^24 승이 되어 16,777,216 개의 숫자를 표현할수 있다.

앞선 설명에서 mysql 에서 euckr 인코딩에서는 한글이 2byte 를 사용하고, utf8mb4 에서는 3 byte를 사앙한다고 말한적이 있다. euckr 은 한국어에 특화된 인코딩이기에 한글만 표현하기 위해서는 512개의 숫자로도 충분히 다룰 수 있다. 그러나 utf8mb4 와 같이 전세계 범용 인코딩 체계에서는 한국어 외에도 러시아어 아랍어 프랑스어 등등의 다양한 언어를 다루어야 하기 때문에 512개는 턱없이 부족하기에 2byte 에서 1byte 를 더 추가해 3 byte 가 되었다.

"안녕하세요123" 2진법, 그리고 10진법 표기하면 아래와 같다.

2진법	문자	UTF-8 Hex	10진수	설명
11101001 10011010 10010100	안	E9 9A 94	233 154 148	한글 '안'
11101011 10000101 10010101	녕	EB 85 95	235 133 149	한글 '녕'
11101101 10010101 10011000	하	ED 95 98	237 149 152	한글 '하'
11101100 10000100 10111000	세	EC 84 B8	236 132 184	한글 '세'
11101100 10011010 10010100	요	EC 9A 94	236 154 148	한글 '요'
00110001	1	31	49	숫자 1
00110010	2	32	50	숫자 2
00110011	3	33	51	숫자 3

여기서 주의할 점이 있다.
3개의 8비트 블록(3 byte )을 사용한다고 해서 2^24승 (16,777,216) 개의 문자를 단일한 코드로 표현하는 것이 아니다. 각각의 byte는 독립적으로 취급되며, 3개의 byte 를 조합해 하나의 문자 체계를 만든 것이 utf-8 포맷 규격이다. 그래서 "안" 의 경우 11101001 10011010 10010100 총 3개의 byte 로 구성되고, 10진수로는 233 154 148로 표현된다. 재미있는 점은 UTF-8 인코딩 규격에는 명확한 "prefix(접두 패턴)" 규칙이 있다는 것이다. 규칙은 아래와 같다.

1 byte 문자
패턴: 0xxxxxxx

2 byte 문자
패턴: 110xxxxx 10xxxxxx

3 byte 문자
패턴: 1110xxxx 10xxxxxx 10xxxxxx

4 byte 문자
패턴: 11110xxx 10xxxxxx 10xxxxxx 10xxxxxx

이 규칙을 참고한 상태로 아래 2진법을 보자.

1	2	3	4	5	6	7	8
11101011 10000101 10010101	11101011 10000101 10010101	11101101 10010101 10011000	11101100 10000100 10111000	11101100 10011010 10010100	00110001	00110010	00110011
안	녕	하	세	요	1	2	3

기본적으로 컴퓨터는 1byte 씩 끊어서 읽는다. 이는 mysql 도 마찬가지이다. 그런데 utf-8 인코딩에서는 한 문자가 1-4 byte 까지 가변으로 저장이 된다. 예를 들어 한글 "안" 은 3byte 를 사용하는데, 이는 1byte 3개를 연결해서 문자를 완성한 형태이다. utf-8 인코딩은 글자가 1byte 일수도 있으며, 4byte도 될수 있다고 했다. 어떨 때에는 1byte 만 읽어야하고 어떨 때에는 1byte 를 4번 연속으로 읽어야 한다는 말이 된다. 컴퓨터는 몇 byte 를 연속으로 읽어야할지를 어떻게 아는 걸까?
정답은 앞서 설명한 각 byte 에 접두 패턴(prefix)을 새겨두는 것이다. 최초의 1byte 를 읽었을 때, 그 최초의 byte의 접두 패턴이 어떠하다면 2번연속으로 읽어야겠다, 또는 4번 연속으로 읽어야겠다를 판단하는 것이다.

• prefix 가 0으로 시작하면 -> 1byte 문자
• prefix 가 110 으로 시작하면 -> 2byte 문자
• prefix 가 1110 으로 시작하면 -> 3byte 문자
• prefix 가 11110 으로 시작하면 -> 4byte 문자

이런 패턴으로 되어있기에, "안" 이라는 한글을 3byte 로 저장하고, 다시 읽어들일 수 있다.

이러한 개념을 이해하면, 왜 문자 컬럼의 값에 따라 글자당 용량( byte )이 달라지는지를 쉽게 이해할 수 있다. 지금은 utf-8 인코딩 체계만 말해서 그렇지만, 또 다른 인코딩 역시 어떠한 규칙이 있는 셈이다. 조금 더 확장해서 이야기해보자. utf-8인코딩 체계의 글자와 euckr_bin 인코딩 체계의 컬럼의 글자가 왜 join 이 안 되는걸까? 왜 인덱스 검색에서 문제가 생기는 걸까? 이 역시 인코딩 체계에서 어떤 규칙으로 글자를 표현하냐가 다르기 때문에 빠른 비교가 안되는 것이다.

앞서서 "안녕하세요123" 에서 utf-8과 euc_kr 인코딩으로 각 글자를 2진법으로 표기하면 아래와 같다.

문자	UTF-8 2진수	EUC-KR 2진수
안	11100011 10010101 10001000	10111110 11001000
녕	11101011 10000101 10010101	10110011 11011001
하	11101101 10010101 10011000	11000111 11010001
세	11101100 10000100 10111000	10111100 10111100
요	11101100 10011010 10010100	11000000 11011010
1	00110001	00110001
2	00110010	00110010
3	00110011	00110011

이렇게 비교하고 보면 왜 문제가 생기는지 명확히 알 수 있다. 같은 "안" 이라는 글자도 실제 저장되어있는 byte 가 서로 다르기 때문에 join이나 인덱스 검색과 같은 비교 구문 자체가 성립되지 않는 것이다.

16진법

16진법은 사람을 위해서 나온 표기법이라고 한다. 왜 그럴까? 'Z' 라는 문자를 표현하면..

문자	아스키 코드 2진법	아스키 코드 16진법
Z	01011010	5A

2진법으로 표현하면 8개의 글자를 써야하고, 16진법으로 표기하면 2글자로 끝낼수있다. 1 byte 는 8비트라고 했고, 1 byte  를 2진법으로 표현하면 8개의 글자가 필요하다. 이는 2^8 이라 할수있다. 그런데 16진법은 2^4 이라 2개의 글자로 이를 표현할수가 있다. 2진법 8글자는 2^8 = 256개의 10진수를 표현할수 있다, 16진법 2글자는 16^2 = 256개의 10진수를 표현할수있다! 즉 단 2글자로 1 byte 를 문자로 표현할수 있는 게 16진법이다.

varchar 와 인덱스 길이

varchar 의 인덱스의 길이는 charset encoding 과 관련이 있다. 인덱스는 3072 byte 의 사이즈만 가져야 하기 때문이다. 관련해서는 이 글(https://glqdlt.tistory.com/509) 에서 자세히 다루었으니 참고하도록 하자