[MySQL] Error Code: 1071. Specified key was too long

2020. 7. 22. 16:30

길이가 긴 칼럼에 인덱스를 걸다보면 가끔 아래의 메세지를 볼 수 있다.

Error Code: 1071. Specified key was too long; max key length is 767 bytes

인덱스의 전체 길이에는 제한이 있다. 이는 MySQL설정값에에 따라 max length가 767 또는 3072 bytes가 된다.

767 bytes라고 에러메세지가 발생하는 경우에는 일부 설정값 변경으로 3072bytes 까지 늘릴 수 있지만, 3072bytes라고 메세지가 발생하는 경우에는 칼럼사이즈를 축소하거나 칼럼의 일부길이에만 인덱스를 생성해야한다.

해결 방법

키 사이즈를 줄인다.

칼럼 사이즈 수정

너무 큰 사이즈의 칼럼이 인덱스로 지정되면 해당 칼럼의 사이즈가 적합한지, 혹은 인덱스에 포함할 칼럼이 맞는지 확인하는 것이 우선이라고 생각 됨.

각 각의 칼럼의 사이즈는 크지 않지만 인덱스에 여러 칼럼이 추가되면서 전체 길이가 길어지는 경우는 제외

문자형 칼럼의 경우 charecter set에 따라 가능한 글자 수가 달라지니 계산 시 참고해야함.

CREATE TABLE test2 (
    a INT NOT NULL AUTO_INCREMENT,
    b VARCHAR(1000) NOT NULL,
    PRIMARY KEY (a),
    INDEX idx_test1 (b)
)  ENGINE=INNODB DEFAULT CHARSET={$charset};

여기서 b칼럼 사이즈는 charset에 설정되는 인코딩에 따라 달라진다.

utf8의 경우 1000 * 3byte(utf8)

charset	할당되는 byte
euckr	2 bytes
utf8	3 bytes
utf8mb4	4 bytes

키에 속한 칼럼의 사이즈 중 전체가 아닌 일부만 인덱스를 건다.

유니크 인덱스에 칼럼의 일부만 설정할 경우 해당 길이까지만 유니크하게 비교하고 그 이상의 값들은 유니크함을 보장할 수 없게 된다.

create index idx_test1 on test2(name(100));

index size의 limit 값 변경.

아래 표에 나온 variable 값을 설정하여 index size 제한을 737 bytes 에서 3072 bytes 까지 올릴 수 있다.
노출되는 에러메세지에 3072bytes라고 표시된다면 더 이상의 확장은 불가하다.

num	설정 level	Variable name	value
1	system	innodb_large_prefix	'on'
2	system	innodb_file_format	'BARRACUDA'
3	table	ROW_FORMAT	'DYNAMIC' or 'COMPRESSED'

innodb_large_prefix는 5.7.7 부터 default value가 'on'
innodb_file_format는 5.7.7 부터 default value는 Barracuda. 해당 옵션은 이후 버전에서 삭제 예정.
ROW_FORMAT 5.7.9부터 추가된 innodb_default_row_format 옵션의 default 값이 'DYNAMIC' MySQL5.7