MySQL의 VARCHAR(1000) vs TEXT의 차이점은 무엇일까?
왜 VARCHAR(500), VARCHAR(1000) 등 이런식으로 사용하는걸까?
TEXT로 데이터형으로 저장하면 되지. 왜 굳이 VARCHAR(500) 이런식으로 사용하는지가 의문일 것이다.
그럼 서두는 제쳐두고 간단하게 설명부터 하도록 하겠다.
제약사항
VARHCAR는 공간적으로 제약이 있다. VARCHAR는 테이블에 하나의 레코드가 저장할 수 있는 최대 길이가 65,535 바이트이다. 그렇다보니 레코드 사이즈 한계에 따라서 최대 저장 길이 설정시 공간을 아껴 쓸 필요가 있다. 하지만 이에 비해 BLOB, TEXT 같은 타입은 제약이 없다. BLOB, TEXT과 같은 LOB (Large Object)는 B-Tree 외부의 Off-Page 페이지에 저장을 한다. LOB 타입의 컬럼을 항상 Off-Page로 저장한다는 것이 아니라 길이가 길어서 저장 공간이 많이 필요한 경우에만 Off-Page로 저장을 한다. (이하 아래 설명)
TEXT 타입과 같은 LOB타입에서 InnoDB 스토리지 엔진은 레코드의 전체 크기가 이 제한 사항(16KB 페이지에서는 8,117 바이트 )을 초과하면 길이가 긴 컬럼을 선택해서 Off-Page로 저장하게 된다.
메모리 활용
결론적으로는 VARCHAR(500), VARCHAR(1000) 이런식으로 사용하는 이유는 메모리에 미리 할당하여 높은 성능을 이끌어내기 위함이다.
InnoDB 스토리지 엔진은 uchar* records[2] 메모리 포인터를 이용해서 레코드 데이터를 주고 받고있다. records[2] 메모리 객체는 실제 레코드의 데이터 크기에 관계 없이 최대 크기로 메모리를 할당한다.
VARCHAR 타입은 최대 크기가 설정되기 때문에 메모리 공간을 records[2] 버퍼에 할당이 가능하나 TEXT와 같은 LOB 컬럼 데이터의 경우 실제 최대 크기만큼 메모리를 할당해 두면 메모리 낭비가 너무 심해지는 문제가 발생하기때문에 records[2] 포인터가 가리키는 메모리 공간은 VARCHAR는 포함하지만 TEXT 컬럼을 위한 공간은 포함하지 않는다.
VARCHAR 타입의 컬럼을 읽을 때는 새롭게 메모리를 할당받는 것이 아니라 TABLE 구조체의 records[2] 버퍼를 이용하기때문에 메모리를 유용하게 활용할 수 있다는 장점이 있다는 반면에 TEXT와 같은 LOB 타입의 컬럼을 읽을 때는 매번 필요한 크기만큼 메모리를 할당해서 사용후 해제한다.
이미 DB 업계에서 정점을 찍으신 이성욱님 블로그 포스트를 보시면 이해가 빠를 것이며 그 내용을 이 블르그에서 조금 더 보충했다고 보면 되겠다. 나또한 이 분의 열혈한 팬이기도 하다.
https://medium.com/daangn/varchar-vs-text-230a718a22a1
VARCHAR vs TEXT
개요
medium.com
VARCHAR의 최대 길이는 255가 아닌가?
독설을 날리자면 아직도 이런 지식을 가지고 있다면 정말 올드한 사람이고 DB 개발에 대한 성장이 멈춘 사람이다.
MySQL의 VARCHAR 열의 최대 크기가 65,535 바이트로 바뀐 것은 MySQL 5.0.3 버전부터 적용되었다.
이전 버전의 MySQL에서는 VARCHAR 열의 최대 크기가 255 바이트였다.
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
출처 : http://download.nust.na/pub6/mysql/doc/refman/5.4/en/char.html
VARCHAR의 테이블의 최대 길이는 65535바이트인걸까?
엄밀히 말하자면 최대길이 65535 바이트는 맞지만 캐릭터셋에 따라 계산법이 달라진다고 보는 것이 맞다.
자, 간단하게 아래 예를 들어보자
1. 테이블을 charset을 euckr, utf8mb4로 생성하자.
# charset=euckr
CREATE TABLE `test1` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=euckr;
# charset=utf8mb4
CREATE TABLE `test2` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;2. 그리고 컬럼을 varchar(65535) 추가해보자.
ALTER TABLE test1 ADD name2 VARCHAR(65535);
# 오류 코드: 1074
# Column length too big for column 'name2' (max = 32766); use BLOB or TEXT instead
ALTER TABLE test2 ADD name2 VARCHAR(65535);
# 오류 코드: 1074
# Column length too big for column 'name2' (max = 16383); use BLOB or TEXT instead
결과를 보면 알 수 있듯이 아래와 같이 varchar의 최대길이는 문자열마다 다를 수 있다.
- euckr : 65535 / 2 = 약 32767
- utf8mb4 : 65535 / 4 = 약 16383
(euckr일때 max = 32766이 나왔는데 실제 계산해보니 32767이 나왔냐하는 의문점은 MySQL은 1~2바이트의 추가 오버헤드를 가지며, VARCHAR 열의 최대 크기는 실제 데이터 크기보다 약간 작을 수 있다라는 점 )
VARCHAR 의 위험성은?
서버 리소스 비용이랑 이것저것 계산을 잘해보면서 사용해야된다. 그냥 난 초보다. 모르겠다하는 사람들은 그냥 TEXT 형 사용하면 된다. 하지만 실력은 제자리일꺼라는 점....
자 아래와 같은 테이블을 예시로 들고 우리가 싫어하는 수학을 해보자.
CREATE TABLE `large_table` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`value1` VARCHAR(5000) DEFAULT NULL,
`value2` VARCHAR(5000) DEFAULT NULL,
`value3` VARCHAR(5000) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
컬럼 비용 계산
- id : 4byte
- value1 : 4byte x 5000
- value2 : 4byte x 5000
- value3 : 4byte x 5000
= 4 + (5000 x 4 x 3) = 60,004 (58.59 KB) 비용이 테이블당 발생한다.
총합 계산
커넥션 수 x 메모리 총합 x 2 (record[2]) 일 경우는 배수로 많아질 것이므로 잘 고려하여 사용해야한다.
결론 : 어떤식으로 사용해야 VARCHAR vs TEXT를 잘 사용할 수 있을까?
용도에 성능에 맞게 잘 활용해야한다. 아는만큼 보인다라는 말이 있듯이 정답은 없다. 각 특성에 맞게 사용하는 것이 효과적인 방법이라고 할 수 있겠다.
VARCHAR
- 최대 길이가 (상대적으로) 크지 않은 경우
- 테이블 데이터를 읽을 때 항상 해당 컬럼이 필요한 경우
- DBMS 서버의 메모리가 (상대적으로) 충분한 경우
TEXT
- 최대 길이가 (상대적으로) 큰 경우
- 테이블에 길이가 긴 문자열 타입 컬럼이 많이 필요한 경우
- 테이블 데이터를 읽을 때 해당 컬럼이 자주 필요치 않은 경우