문자열 필드에 효율적으로 인덱스를 생성하는 방법

이메일 로그인 시스템을 운영한다고 가정해 보겠습니다. 사용자 테이블은 다음과 같이 정의되어 있습니다.

CREATE TABLE SUser (
    ID BIGINT UNSIGNED PRIMARY KEY,
    email VARCHAR(64),
    ...
) ENGINE=InnoDB;

이메일 로그인을 지원해야 하므로, 비즈니스 로직에는 다음과 같은 쿼리가 반드시 포함됩니다.

SELECT f1, f2 FROM SUser WHERE email = 'xxx';

만약 email 필드에 인덱스가 없다면, 이 쿼리는 전체 테이블 스캔을 수행해야 합니다.

1. 이메일 주소 필드에 인덱스를 생성할 수 있는가?

MySQL은 접두사 인덱스(Prefix Index)를 지원하므로, 문자열의 일부만 인덱스로 정의할 수 있습니다.

2. 인덱스 생성 시 접두사 길이를 지정하지 않으면 어떻게 되는가?

인덱스는 전체 문자열을 포함하게 됩니다.

3. 예시로 설명

ALTER TABLE SUser ADD INDEX index1(email);
-- 또는
ALTER TABLE SUser ADD INDEX index2(email(6));
  • index1은 각 레코드의 전체 이메일 문자열을 포함합니다.
  • index2는 각 레코드의 처음 6바이트만 인덱스로 저장합니다.

4. 두 인덱스의 데이터 구조 및 저장 차이

email(6) 인덱스 구조는 더 적은 공간을 차지합니다.

5. email(6) 인덱스의 단점

추가적인 레코드 스캔이 발생할 수 있습니다.

6. 두 인덱스에서의 쿼리 실행 과정

다음 쿼리를 예로 들어 보겠습니다.

SELECT id, name, email FROM SUser WHERE email = 'zhangssxyz@xxx.com';

index1 (전체 문자열 인덱스) 실행 순서

  1. index1 인덱스 트리에서 'zhangssxyz@xxx.com' 값을 찾아 ID2를 획득합니다.
  2. ID2로 기본 키 인덱스(클러스터드 인덱스)에서 해당 행을 가져와 email 값이 일치하는지 확인하고 결과 집합에 추가합니다.
  3. 인덱스 트리에서 다음 레코드를 확인했을 때 조건이 더 이상 일치하지 않으므로 루프를 종료합니다.

이 과정에서 기본 키 인덱스 접근은 한 번만 발생하며, 시스템은 1행만 스캔한 것으로 간주합니다.

index2 (email(6) 접두사 인덱스) 실행 순서

  1. index2 인덱스 트리에서 'zhangs' 값을 찾고, 첫 번째로 일치하는 ID1을 얻습니다.
  2. ID1으로 기본 키 인덱스에서 행을 가져와 email이 'zhangssxyz@xxx.com'인지 확인합니다. 일치하지 않으므로 해당 행을 폐기합니다.
  3. 인덱스 트리에서 이전 위치 다음 레코드를 가져옵니다. 여전히 'zhangs'이며 ID2를 획득하고 기본 키 인덱스에서 전체 행을 가져와 확인합니다. 이번에는 값이 일치하므로 결과 집합에 추가합니다.
  4. 인덱스 트리에서 더 이상 'zhangs' 값이 나오지 않을 때까지 위 단계를 반복합니다.

이 과정에서 기본 키 인덱스를 4번 접근하며, 시스템은 4행을 스캔합니다.

7. 위 비교를 통한 결론

접두사 인덱스를 사용하면 쿼리 읽기 횟수가 증가할 수 있습니다.

8. 접두사 인덱스는 항상 불리한가?

만약 index2를 email(7)로 정의했다면, 접두사 'zhangss'에 해당하는 레코드는 하나뿐이므로 ID2를 직접 찾아 1행 스캔으로 종료되었을 것입니다.

9. 접두사 인덱스 사용 시 주의사항

적절한 접두사 길이를 선택해야 합니다.

10. 접두사 길이 결정 방법

인덱스 내 고유한 값의 개수를 통계적으로 파악하여 적절한 길이를 결정합니다.

11. 고유 값 개수 통계 확인

SELECT COUNT(DISTINCT email) AS L FROM SUser;

12. 고유 값 개수 확인 후 다음 단계

다양한 접두사 길이에 대한 고유 값 개수를 확인합니다.

SELECT
    COUNT(DISTINCT LEFT(email, 4)) AS L4,
    COUNT(DISTINCT LEFT(email, 5)) AS L5,
    COUNT(DISTINCT LEFT(email, 6)) AS L6,
    COUNT(DISTINCT LEFT(email, 7)) AS L7
FROM SUser;

그런 다음 L4에서 L7 중 L * 0.95보다 작지 않은 첫 번째 값을 찾습니다. 이는 해당 접두사 인덱스로 95% 이상의 데이터를 검색할 수 있음을 의미합니다.

13. 접두사 인덱스가 커버링 인덱스에 미치는 영향

SELECT id, email FROM SUser WHERE email = 'zhangssxyz@xxx.com';

위 쿼리는 id와 email만 반환합니다.

  • index1(전체 문자열 인덱스)을 사용하면 인덱스만으로 id를 얻을 수 있어 기본 키 인덱스 접근이 필요 없습니다. 이것이 커버링 인덱스입니다.
  • index2(접두사 인덱스)를 사용하면 이메일 값을 확인하기 위해 기본 키 인덱스로 돌아가야 합니다.

14. index2를 email(18)로 변경하면 문제가 해결되는가?

길이 18은 사용자가 정의한 값일 뿐, 시스템은 이 길이가 실제 이메일 길이를 초과하는지 알 수 없습니다. 따라서 MySQL은 여전히 기본 키 인덱스로 돌아가 이메일 전체 값을 확인합니다. 즉, 접두사 인덱스는 커버링 인덱스의 성능 최적화를 활용할 수 없습니다.

15. 주민등록번호처럼 접두사 구분도가 낮은 경우 해결 방법

문자열의 접두사가 서로 유사한 경우(예: 주민등록번호의 지역 코드), 더 긴 접두사를 사용해야 합니다. 그러나 인덱스가 길어질수록 디스크 공간을 더 차지하고, 한 페이지에 저장되는 인덱스 값 수가 줄어들어 쿼리 효율이 떨어질 수 있습니다.

16. 등가 비교(Equal Query)만 필요한 경우 대안

방법 1: 문자열을 역순으로 저장

SELECT field_list FROM t WHERE id_card = REVERSE('input_id_card_string');

이 방법을 사용하기 전에 COUNT(DISTINCT)로 유효성을 확인하는 것이 좋습니다.

방법 2: 해시(Hash) 필드 추가

ALTER TABLE t ADD id_card_crc INT UNSIGNED, ADD INDEX(id_card_crc);

새 레코드를 삽입할 때마다 CRC32() 함수로 체크섬을 계산하여 이 필드에 저장합니다. CRC32는 충돌 가능성이 있으므로 쿼리 시 정확한 id_card 값도 함께 확인해야 합니다.

SELECT field_list FROM t WHERE id_card_crc = CRC32('input_id_card_string') AND id_card = 'input_id_card_string';

이렇게 하면 인덱스 길이가 4바이트(INT)로 줄어듭니다.

17. 역순 저장과 해시 필드 방법의 비교

공통점: 두 방법 모두 범위 검색(Range Query)을 지원하지 않습니다. 역순 저장 인덱스는 역순 문자열로 정렬되므로, 주민등록번호 범위 검색이 불가능합니다. 해시 필드 역시 등가 비교만 가능합니다.

차이점

  • 공간 사용: 역순 저장은 기본 키 인덱스에 추가 공간을 소모하지 않지만, 해시 필드는 새로운 컬럼을 추가해야 합니다. 단, 역순 저장 시 4바이트 접두사 길이로는 충분하지 않을 수 있으며, 길이를 늘리면 해시 필드와 공간 소모가 비슷해집니다.
  • CPU 소모: 역순 저장은 쓰기와 읽기 시 REVERSE() 함수를 호출해야 하고, 해시 필드는 CRC32() 함수를 호출합니다. 계산 복잡도 측면에서 REVERSE()가 더 적은 CPU를 소모합니다.
  • 쿼리 성능 안정성: 해시 필드 기반 쿼리는 충돌 확률이 매우 낮아 평균 스캔 행 수가 1에 가깝습니다. 반면 역순 저장은 접두사 인덱스를 사용하므로 여전히 스캔 행 수가 증가할 가능성이 있습니다.

사례: 학교 학생 정보 데이터베이스

학생 로그인 ID 형식은 '학번@gmail.com'이며, 학번은 15자리 숫자입니다. 앞 3자리는 도시 코드, 4~6자리는 학교 코드, 7~10자리는 입학 연도, 마지막 5자리는 순번입니다. 로그인 인증만 고려할 때 인덱스를 어떻게 설계해야 할까요?

한 학교에 연간 약 2만 명의 신입생이 있으며, 50년 동안 최대 100만 건의 레코드가 생성됩니다. 이러한 규모에서는 전체 필드 인덱스를 사용하는 것이 공간 측면에서도 효율적이며, 추가적인 변환 로직 없이 간단합니다. 대규모 데이터가 아니라면 굳이 접두사 인덱스나 해시 필드를 도입할 필요가 없습니다. 극단적인 대용량 상황에서만 위의 두 가지 대안을 고려하는 것이 현명합니다.

태그: MySQL 인덱스 접두사 인덱스 커버링 인덱스 crc32

6월 25일 00:41에 게시됨

인기 태그

©2026 괴물 클럽