[240513] mysql varchar(65535) 는 정말 65535 개의 한글을 입력할수있는걸까?
* 이 글은 mysql8 기준이다.
흔히들 varchar 의 길이는 입력가능한 문자열의 최대값으로 알려져있다. 정말 그런걸까? 이는 반은 맞고 반은 틀린 말이다.
지난 번(https://glqdlt.tistory.com/504)에 text 와 long text 에 대해 공부를 했었다. charset encoding 이 무엇이냐에 따라 text 에 입력가능한 글자수가 달라진다고 얘기한 바 있다. 이는 varchar 에서도 유효할까?
varchar 컬럼은 한글 65535 개를 입력할 수 없다.
varchar는 동적문자열이고, 최대 varchar(65535) 까지 지정이 가능하다. varchar 의 길이는 입력된 문자열의 길이와 같다고 알려져 있지만 정확히는 아니다. 테이블에 다른 컬럼이 몇개 있느냐와 charset encoding 이 무엇으로 정의되어있느냐에 따라서 varchar(n) 길이 n 값이 정해진다.
예를 들어서 utf8mb4 인코딩에 varchar 컬럼 단독으로 1개만 정의한다면, utf8mb4 가 최대4byte 를 쓸수있으니 (대략 65535 / 4 = 16383 이 나옴으로) varchar(16383) 정도의 컬럼 길이를 정의할수있다. 만약 varchar(16384) 로 정의하려면 에러가 난다. 결국 이 경우에서는 한글을 16383 까지만 입력할수 있다가 된다. 65535 개의 글자를 입력할수 있다는 성립하지가 않는다. utf8mb4 에서 한글은 1글자당 3byte 임에도, 길이 제한에는 utf8mb4의 최대 스펙으로 추산되니 억울하지만 어쩔수없다. utf8mb4 최대 입력가능 문자 크기가 4byte 니깐 말이다. 참고로 utf8mb3 는 최대 입력 가능 문자가 3byte 임으로 계산을 달리 해야한다.
ALTER TABLE `test_table`
CHANGE COLUMN `val_col` `val_col` VARCHAR(16384) NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin' FIRST;
/* SQL 오류 (1074): Column length too big for column 'val1' (max = 16383); use BLOB or TEXT instead */그렇다면 latin1 인코딩으로 하고 varchar 컬럼을 단독으로 쓴다면 어떨까? latin1 은 1byte 만 사용하기 때문에 당연히 varchar(65535) 로 정의가 가능하다. 이렇게보면 varchar 는 text 컬럼과 비슷하게 65535 byte 로 입력가능한 길이가 정해지는 것처럼 보인다. 하지만 결이 같을 뿐, 속은 다르다.
varchar 컬럼은 varchar 컬럼 단독으로 존재하지 않고 다른 여러 컬럼들과 함께 있을 시에는, varchar 최대 정의 가능 길이는 더 작아진다. 예를 들어 utf8mb4 인코딩에 val1 과 val2 라는 2개의 varchar 컬럼을 정의한다고 해보자. val1 은 16383 길이로 하고, val2 는 1로 했을 때엔 val2 컬럼의 생성이 불가능해졌다!
만약 val1 을 16382 로 -1 하고, val2 을 1 사이즈로 하면 성공한다! 와우
비밀은 레코드(row) 용량 65,535 byte 제한
이런 어처구니 없는 결과가 나오는 것은 mysql 에서는 (정확히는 mysql의 innodb) 1개의 레코드(row,행) 에 컬럼의 모든 값의 합산 용량이 65,535 byte 를 넘기면 안된다는 제약이 있어서 발생한다. varchar 의 길이와 charset encoding 을 조합해서 시뮬레이션해보고 레코드의 모든 컬럼의 입력가능한 최대값의 합산이 65535 byte 를 초과할것이라 판단되면 컬럼 생성이 불가능해진다. 이는 varchar 외에도 int, datetime 등의 모든 컬럼에도 해당된다. 다만 text, 와 blob 과 같은 컬럼들은 레코드에 직접적으로 컬럼의 값이 저장되는 것이 아니기 때문에 레코드 용량 제한에 우회가 된다. 다음 기회에 설명하겠지만 text, blob 컬럼은 레코드가 저장되는 데이터 페이지라는 곳에 저장되지 않고 별도의 데이터 페이지에 적재되기 때문이다. 레코드에는 text, blob 컬럼의 원본 값이 있는 데이터페이지에 대한 포인터만 남는다. text 컬럼은 컬럼 자기자신 단독으로 65,535 byte 에 대한 제약이 있지만, varchar 를 비롯한 다른 컬럼들은 컬럼들의 합산 용량이 레코드 용량 제한인 65,535byte 를 넘기면 안된다로 정리가 된다. 이는 테이블 정규화를 해야하는 이유에도 힘을 실어주는 내용이다.
결국 이 글의 주제였던 varchar 에 한글 65,535 를 입력할수있을까? 란 물음에 대한 답은, charset encoding 에 따라 한글은 2byte~3byte 를 먹음으로 절대 한글은 65,535 개 입력할수없다가 된다.
The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row. - https://dev.mysql.com/doc/refman/8.4/en/column-count-limit.html
MySQL has hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact column limit depends on several factors: - https://dev.mysql.com/doc/refman/8.4/en/column-count-limit.htm
varchar 인덱스 정의에서의 주의사항. 인덱스 길이 제한
varchar 컬럼의 사이즈에 대한 제한이었다면, 이번엔 인덱스 길이 제한에 대해서 알아보자. 앞서 살펴본 것처럼 varchar 컬럼은 레코드 용량 제한과 charset encoding 에 따라서 varchar 컬럼의 길이를 정할수 있다고 했다. utf8mb4 인코딩에 varchar 단독 컬럼인 경우에는 varchar(16383) 까지 정의할수 있다. 그러면 이 컬럼에 인덱스를 정의하는 건 어떻게 될까? 역시나 정답을 미리 말하면 인덱스를 정의할수없다. 왜냐면 인덱스도 레코드처럼 물리적인 제한이 있기 때문이고, 인덱스에 삽입되는 값은 3072 byte 을 넘기면 안된다.
utf8mb4 는 문자열 1개당 최대 4byte 를 쓸수 있다. 인덱스 길이 제한인 3072 byte 로 본다면 3072 / 4 = 768 개가 됨으로 varchar(768) 컬럼만 인덱스를 정의할수 있다가 되며, varchar(768) 컬럼으로 변경하고 인덱스를 추가하면 성공한다.
만약 이 상태에서 varchar(769) 로 변경하고 인덱스를 정의하려면 에러가 난다. 즉 앞서 계산한 공식인 3072 / 4 = 768 이 성립함을 알수 있다.
varchar 인덱스와 dynamic 모드 row format
주의할 점은 인덱스 사이즈 제한인 3072 byte 는 mysql 서버의 row format 설정이 dynamic 일때 가능한 수치이고, row format 설정이 compact 일 떄에는 767 byte 로 사이즈 제한이 줄어드니 유의해야한다.
For all character data types (CHAR, VARCHAR, and the TEXT types), the maximum number of characters that can be indexed is less for utf8mb4 columns than for utf8mb3 columns. (...) InnoDB has a maximum index length of 767 bytes for tables that use COMPACT or REDUNDANT row format, so for utf8mb3 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively.
row format 을 변경하는 것은 DB 운영에 대한 전략적 선택의 영역이다. compact 모드의 이점은 적은 데이터 사이즈로 인한 더 많은 메모리 캐싱(mysql 버퍼풀)을 할 수 있지만 높은 cpu 스펙을 요구한다는 단점이 있다. 반대로 dynamic 모드는 캐시에서의 손해를 볼수있지만, 더 긴 값을 인덱스할수 있다는 점이 있다. mysql 8 에서는 dynamic 모드가 기본 설정값이다.
The row format of a table determines how its rows are physically stored, which in turn can affect the performance of queries and DML operations. As more rows fit into a single disk page, queries and index lookups can work faster, less cache memory is required in the buffer pool, and less I/O is required to write out updated values. - https://dev.mysql.com/doc/refman/8.4/en/innodb-row-format.html
특정 table 의 charset encoding(Collation) , row format 이 무엇인지를 알고 싶다면 아래 쿼리를 통해 확인할수있다.
SHOW TABLE STATUS LIKE '{대상 테이블}'
결론
정리하면 varchar 는 65535 개의 길이를 정의할수있다고 하지만, 레코드에 정의된 컬럼 갯수와 charset encoding 에 따라서 최대 길이가 정해짐으로 varchar 65535 정의할수 있다고 확정지으면 안된다. 이는 레코드(row)의 용량 제한인 65535 byte 로 인한 것임을 알아두자. varchar 의 인덱스 정의에서도 인덱스의 용량 제한인 3072 byte 에 대한 제약이 있으니, 인덱스에서는 charset encoding 을 참조해서 vachar 의 최대 정의 길이를 가늠하도록 해야한다. rdbms 는 정적으로 딱 이렇다라고 정의내리기엔 환경과 변수값에 따라서 갈리는 부분이 있으니, 단순 암기식으로 공부하는 것은 주의를 해야한다.