글을 작성하게 된 계기

인덱스를 생성할 때, 길이 제한이 있다는 것을 알게 되었고, 이를 정리하기 위해 글을 작성하게 되었습니다.

1. 문제 상황

인덱스를 생성할 때, 다음과 같은 오류를 만났습니다.

  
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

이를 재현하기 위해 다음과 같은 테이블을 생성해보겠습니다.

  
CREATE TABLE fail_idx_table
(
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(1000) CHARACTER SET utf8mb4,
    INDEX idx_name (name)
) ENGINE = InnoDB;

해당 DDL을 실행하면, 회사에서 만난 오류와 동일한 오류가 발생합니다. 왜 이런 문제가 발생했을까요?

  
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

2. 원인 분석

인덱스의 길이 제한은 데이터베이스 시스템에서 인덱스를 효율적으로 관리하기 위해 설정된 제한입니다. MySQL에서는 InnoDB 스토리지 엔진을 사용할 때, 인덱스의 최대 길이는 3,072 byte로 제한되어 있습니다. 이는 인덱스가 너무 커지면 성능 저하를 초래할 수 있기 때문입니다.

The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

위 상황을 다시 살펴보면 utf8mb4 문자셋은 한 글자가 최대 4 bytes를 차지하기 때문에, VARCHAR(1000)을 선언하면 최대 4000 bytes까지 필요합니다. 즉, MySQL은 VARCHAR(1000) utf8mb4 컬럼에 그냥 인덱스를 걸려고 하면 최대 4000 bytes가 필요하다고 판단하는데, 이는 3072 bytes를 초과하므로 에러를 발생시킨 것입니다.

  
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

참고로 MySQL 5.6 이하에서는 767 bytes 제한이라 해당 제약이 더 심했고, MySQL 5.7부터 innodb_large_prefix가 도입되며 3072 bytes까지 확장되었습니다. 이는 InnoDB 테이블의 ROW_FORMAT이 DYNAMIC 또는 COMPRESSED인 경우에만 적용됩니다.

If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.

3. 문제 해결

이는 인덱스에 사용할 컬럼의 길이를 제한해 해결할 수 있습니다. MySQL에서는 문자열 컬럼에 인덱스를 생성할 때 부분 인덱스 라는 기능을 제공하는데요, 즉, 문자열 전체가 아닌 일정 길이까지만 인덱스를 걸 수 있도록 제한하는 방식입니다.

If a search term exceeds the index prefix length, the index is used to exclude non-matching rows, and the remaining rows are examined for possible matches.

예를 들어, utf8mb4 문자셋을 사용할 경우 한 글자가 최대 4byte를 차지하므로, 191 글자까지만 인덱스를 걸면 764(191*4) bytes로 3072 bytes 제한에 걸리지 않습니다.

  
CREATE TABLE fail_idx_table
(
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(1000) CHARACTER SET utf8mb4,
    INDEX idx_name (name(191))
) ENGINE = InnoDB;
Query OK, 0 rows affected (0.05 sec)

4. 주의할 점

물론 이를 사용할 때 주의할 점도 있는데요, 프리픽스 인덱스를 사용할 때는 인덱스 길이 설정, 쿼리 패턴 고려, 문자셋에 따른 byte 수, UNIQUE 인덱스 사용 와 같은 사항을 반드시 고려해야 합니다.

인덱스 길이 설정
쿼리 패턴 고려
문자셋에 따른 byte 수
UNIQUE 인덱스 사용

4-1. 인덱스 길이 설정

프리픽스 길이를 너무 짧게 지정하면 인덱스 효율이 크게 떨어질 수 있습니다. 프리픽스 인덱스의 길이가 짧아서 데이터가 중복될 가능성이 높다면 오히려 성능을 악화시킬 수 있습니다. 예를 들어, INDEX idx_name (name(5)) 처럼 앞 5글자만 인덱스로 지정했을 때, abcdea, abcdef, abcdeg와 같이 앞 부분이 동일한 값들이 많다면, 인덱스가 중복된 값으로 몰리게 됩니다. 이로 인해 인덱스 효율이 떨어지고, 조회 성능도 기대만큼 개선되지 않을 수 있습니다. 따라서 데이터 특성상 충분히 구분될 수 있는 길이로 프리픽스를 지정하는 것이 중요합니다.

  
INSERT INTO fail_idx_table (name) VALUES ('abcdea');   # ---> 인덱스에는 'abcde' 저장
INSERT INTO fail_idx_table (name) VALUES ('abcdef');   # ---> 인덱스에는 'abcde' 저장
INSERT INTO fail_idx_table (name) VALUES ('abcdeg');   # ---> 인덱스에는 'abcde' 저장

4-2. 쿼리 패턴 고려

프리픽스 인덱스를 사용할 경우 WHERE 조건이나 LIKE 사용 패턴을 반드시 고려해야 합니다. 예를 들어 LIKE 'abc%' 처럼 문자열의 앞 부분을 검색하는 경우에는 프리픽스 인덱스가 잘 동작합니다. 그러나 LIKE '%abc' 처럼 와일드카드가 앞에 붙는 경우, 프리픽스 인덱스는 아예 사용되지 않습니다. 즉, 프리픽스를 걸어도 실제 쿼리가 이를 활용하지 못하면 성능 향상 효과가 없습니다. 인덱스를 잘라 사용할 때는 쿼리 패턴이 이에 적합한지 반드시 확인해야 합니다.

  
SELECT * FROM fail_idx_table WHERE name LIKE 'abc%'; # ----> 인덱스 사용 (idx_name)
SELECT * FROM fail_idx_table WHERE name LIKE '%abc'; # ----> 인덱스 사용 안 함 (풀스캔)

4-3. 문자셋에 따른 byte 수

문자셋에 따라 인덱스의 실제 byte 수가 다르다는 점을 반드시 유의해야 합니다. 예를 들어 utf8mb4 문자셋은 한 글자가 최대 4byte를 차지하지만, latin1 문자셋은 한 글자가 1byte만 차지합니다. 따라서 같은 VARCHAR(191)이라도 utf8mb4라면 764byte, latin1이라면 191byte가 됩니다. 이처럼 인덱스 길이를 계산할 때는 문자셋에 따른 byte 수를 반드시 고려해야 하며, 이를 무시하면 3072 byte 초과로 인해 다시 인덱스 생성 에러를 겪을 수 있습니다.

  
-- utf8mb4: 1글자 4byte
CREATE TABLE utf8_table (
    id INT,
    name VARCHAR(191) CHARACTER SET utf8mb4,
    INDEX (name)
);
-- --> 191 * 4 = 764 bytes

-- latin1: 1글자 1byte
CREATE TABLE latin1_table (
    id INT,
    name VARCHAR(191) CHARACTER SET latin1,
    INDEX (name)
);
-- --> 191 * 1 = 191 bytes

4-4. UNIQUE 인덱스 사용

프리픽스 인덱스를 UNIQUE로 사용할 때는 전체 문자열이 아닌 프리픽스로 지정한 길이까지만 중복 여부를 검사합니다. 따라서 이후에 다른 문자열이 붙더라도 앞 부분이 동일하면 중복으로 간주되어 삽입이 거부됩니다. 전체 문자열의 유일성을 보장하고 싶다면, 프리픽스 인덱스 대신 해시 컬럼을 만들어 고정된 길이의 해시 값에 대해 유니크 인덱스를 거는 방법이 더 안전합니다.

  
INSERT INTO fail_idx_table (name) VALUES ('abcde123'); # ----> OK
INSERT INTO fail_idx_table (name) VALUES ('abcde456'); # ----> ERROR 1062 (23000): Duplicate entry 'abcde' for key 'idx_name'

또 다른 방법으로는, 긴 문자열에 직접 인덱스를 걸기보다는 해당 문자열의 해시값을 별도의 컬럼으로 만들어 인덱스를 구성하는 방식도 있습니다. 이 경우 해시값은 고정된 길이를 가지므로 인덱스 크기 제한에 쉽게 맞출 수 있습니다. 물론 이는 추가적인 개발과 관리 비용이 필요합니다. 해시 충돌이 발생할 수도 있고요.

  
CREATE TABLE safe_idx_table
(
    id        INT AUTO_INCREMENT PRIMARY KEY,
    name      VARCHAR(1000),
    name_hash BINARY(16) GENERATED ALWAYS AS (UNHEX(SUBSTRING(MD5(name), 1, 32))) STORED,
    UNIQUE INDEX idx_name_hash (name_hash)
);

5. 정리

별 내용은 아닌데요, 인덱스에 길이 제한도 있네요. 인덱스를 생성할 때, 문자열 컬럼의 길이를 제한하는 프리픽스 인덱스를 사용하면 3072 bytes 제한에 걸리지 않고 인덱스를 생성할 수 있습니다. 하지만 프리픽스 길이를 너무 짧게 지정하면 인덱스 효율이 떨어질 수 있으므로, 데이터 특성에 맞는 적절한 길이를 설정하는 것이 중요합니다.

인덱스에도 길이 제한이 있다?