MySQL: 프리픽스 인덱스

※ MySQL: Prefix index.

 

안녕하세요. 듀스트림입니다.

 

오늘 포스팅은 MySQL 인덱스 기법 중 하나인 프리픽스 인덱스에 관련된 내용입니다.

(PostgreSQL은 Expression index로 구현 가능합니다.)

PostgreSQL: 표현식 인덱스

---

1. Prefix index?

프리픽스 인덱스는 데이터베이스에서 문자열 컬럼의 앞부분만 잘라서 인덱스에 저장하는 기법을 말합니다.

긴 문자열 전체를 인덱싱하면 인덱스 크기가 커지고, 디스크 사용량과 성능에 부담이 생기기 때문에 접두어 일부만 인덱싱하여 이를 방지합니다.

 

• 장점:
  • 인덱스 크기 감소 → 디크스 효율 향상
  • 문자열이 긴 컬럼에서도 빠른 검색 가능
• 주의점:
  • 접두어가 동일한 값이 많으면 선택성(low cardinality) 감소 → 인덱스 효율 저하
  • 전체 문자열 검색이나 LIKE '%abc' 패턴 검색에는 활용 불가
• 예시

-- 이메일 컬럼의 앞 10자만 인덱스
CREATE INDEX idx_email_prefix10 ON users(email(10));

---

2. PostgreSQL에서 구현 방법

▸ 표현식 인덱스로 프리픽스 인덱싱 구현

-- Expression index로 email 앞 10글자만 인덱스
CREATE INDEX idx_email_prefix10 ON users (left(email, 10));

 

▸ LIKE 'abc%' 패턴 검색까지 최적화 하려면 아래 오퍼레이터 클래스 인덱스 추가 생성

-- 문자열 컬럼 최적화 text_pattern_ops 인덱스 생성
CREATE INDEX idx_email_like ON users (email text_pattern_ops);

---

오늘은 여기까지~