VARCHAR vs TEXT for Index (MySQL)

개발 디비 어느 테이블에서 데이터 조회 시 풀스캔이 도는 것을 발견하여 확인해보니 인덱스가 없었,,,

 충격과 함께 인덱싱 작업을 하였는데, 그럼에도 불구하고 실행계획을 보니 '풀스캔'이 돌더라는 ㅠ

 

인덱스를 걸고 본 DDL도 좀 특이했으니,

create table 테이블
(
    id      bigint null,
    version text   null
);

create index idx_version
    on 테이블 (version(768));

 

인덱스 생성 시 768 이라는 숫자가 나타나는 것은, 특히 VARCHAR, CHAR, 또는 TEXT 타입의 컬럼에 인덱스를 생성할 때 볼 수 있는 현상입니다. 

이 숫자는 인덱스를 생성할 때 해당 컬럼의 문자 수를 제한하는 것을 의미합니다. MariaDB와 같은 일부 데이터베이스 시스템에서는 큰 텍스트 필드에 전체 인덱스를 생성하는 것 대신, 필드의 첫 부분만을 사용하여 인덱스를 생성할 수 있습니다. 이 방법은 인덱스의 크기를 줄이고 성능을 향상시킬 수 있습니다.

version(768)에서 768은 인덱스에 포함될 version 컬럼의 최대 바이트 수를 지정합니다. 이는 특히 TEXT 또는 큰 VARCHAR 컬럼에 인덱스를 생성할 때 유용하며, 인덱스 크기와 성능 사이의 균형을 맞추기 위해 사용됩니다.

왜 필요한가?
인덱스 크기 관리: 전체 컬럼 대신 컬럼의 일부분에만 인덱스를 생성함으로써 인덱스의 크기를 줄일 수 있습니다. 이는 디스크 공간을 절약하고, 인덱스 유지 관리에 필요한 시간을 단축시킬 수 있습니다.
성능 향상: 검색 속도는 인덱스의 크기에 영향을 받습니다. 일반적으로 인덱스의 크기가 작을수록 검색 성능이 향상됩니다. 따라서, 필요한 최소한의 길이로 인덱스를 제한하는 것이 좋습니다.

 

인덱싱 작업을 했음에도 풀스캔이 도는 이유는,

데이터베이스 쿼리 플래너가 인덱스를 사용하지 않기로 결정했기 때문..!!

 

방안?

데이터 타입 최적화
version 열의 데이터 타입이 text로 되어 있습니다. 이 열의 값이 상대적으로 짧다면, 데이터 타입을 VARCHAR로 변경하는 것이 좋습니다. VARCHAR 타입은 TEXT 타입보다 인덱스 효율성이 더 높습니다. 이 변경은 인덱스의 사용성과 효율을 개선할 수 있습니다.

 

Why?

VARCHAR 타입이 TEXT 타입보다 인덱스 효율성이 더 높은 이유는 몇 가지 주요 차이점에서 비롯됩니다.

1. 저장 메커니즘
VARCHAR: VARCHAR 타입은 일반적으로 테이블의 같은 데이터 페이지 내에 저장됩니다. 데이터의 길이가 고정되지 않았지만, 최대 길이가 정의되어 있어, 대부분의 관계형 데이터베이스 관리 시스템(RDBMS)에서 효율적으로 처리할 수 있습니다. 이로 인해 인덱스 조회 과정이 더 빠르고 효율적일 수 있습니다.

TEXT: TEXT 타입은 대용량의 텍스트를 저장하기 위해 설계되었으며, 데이터가 별도의 위치에 저장될 수 있습니다. 이는 데이터를 찾기 위해 추가적인 참조 단계가 필요하다는 것을 의미하며, 인덱스를 통한 조회 시 오버헤드가 증가할 수 있습니다.

2. 인덱스 생성과 사용
VARCHAR: VARCHAR 필드에 대한 인덱스는 값의 길이가 가변적이라 할지라도, 최대 길이 제한으로 인해 더 적은 저장 공간을 사용하고, 검색 속도가 빠릅니다. 이는 인덱스를 통한 데이터 액세스가 더 효율적임을 의미합니다.

TEXT: TEXT 필드는 일반적으로 더 큰 저장 공간을 필요로 하며, 인덱스를 생성할 때 전체 필드 대신 일부 문자만 인덱스에 포함할 수 있습니다(예: MySQL의 경우). 이는 TEXT 타입이 인덱스로 사용될 때 제한적일 수 있으며, 효율성이 떨어질 수 있습니다.

3. 성능 최적화
VARCHAR 타입은 쿼리 최적화기가 인덱스를 효과적으로 사용할 수 있게 합니다. 데이터베이스는 VARCHAR 열에 대한 인덱스를 더 잘 최적화할 수 있으며, 이는 쿼리 실행 시간을 단축시킵니다.

반면, TEXT 타입은 처리 시 더 많은 메모리와 CPU 자원을 필요로 할 수 있으며, 인덱스를 통한 검색이 VARCHAR보다 비효율적일 수 있습니다.

결론적으로, VARCHAR 타입은 일반적으로 인덱스를 통한 데이터 조회와 관리에 있어 TEXT 타입보다 더 효율적입니다. 
그러나 선택은 데이터의 성격과 애플리케이션의 요구 사항에 따라 달라져야 합니다. 
대용량 텍스트 데이터를 다룰 경우 TEXT 타입을 사용하는 것이 적합할 수 있으나, 인덱스 효율성이 중요한 상황에서는 VARCHAR 타입을 고려해야 합니다.