Prefix Index를 기본적으로 사용하지 않는 이유

데이터베이스를 설계하거나 성능 최적화를 고민하다 보면, Prefix Index 사용을 고려하게 될 때가 있다. 특히, 긴 문자열 컬럼에서 인덱스를 설정할 때 자주 논의되는 옵션 중 하나로, Prefix Index는 데이터베이스의 성능을 높일 수 있는 유용한 도구이지만, 무조건 사용해서는 안 되는 이유도 존재한다.

 

Prefix Index란?

---

Prefix Index는 문자열 컬럼의 일부 접두어(앞부분)만 인덱싱하는 방식이다. 일반적으로 긴 문자열 컬럼에서 인덱스를 생성할 때, 전체 문자열을 인덱싱 하면 인덱스 크기가 너무 커지고 성능이 저하되는 경우가 존재한다. 이를 방지하기 위해, 문자열의 앞부분만 인덱싱하는 방식이 Prefix Index이다.

 

예시

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    INDEX (email(10)) -- email 컬럼의 앞 10글자만 인덱싱
);

 

• email 컬럼에서 앞 10글자만 인덱싱한다.
• 만약 이메일이 "exampleuser123@gmail.com"이라면, "exampleuse"만 인덱스에 저장된다.

 

---

Prefix Index를 사용하는 이유

---

1. 인덱스 크기 절감

• 긴 문자열 컬럼 전체를 인덱싱 하면 많은 스토리지가 필요하다.
• Prefix Index는 일부 접두어만 저장하므로 인덱스 크기를 줄이고 저장 공간을 절약할 수 있다.

 

2. 성능 향상

• 인덱스 크기가 작아지면, 디스크 I/O와 메모리 사용량이 감소해 조회 성능이 향상될 수 있다.
  • 인덱스 크기가 작아지면, 데이터베이스가 검색 시 더 작은 인덱스를 메모리에 적재할 수 있어 디스크 접근 빈도가 줄어든다.
  • 디스크에서 읽어야 할 데이터 양이 감소해 I/O 비용이 절감된다.
  • 인덱스가 메모리 공간에 더 잘 적합하므로 캐싱 효율성이 증가해 검색 속도가 빨라진다.

 

---

Prefix Index를 기본으로 사용하지 않는 이유는?

---

Prefix Index는 위와 같은 장점에도 불구하고, 기본적으로 사용하지 말아야 할 이유가 존재한다.

 

1. 정확한 검색과 정렬의 한계

Prefix Index는 문자열의 일부만 인덱싱 하기 때문에, 데이터베이스가 전체 문자열에 대한 정확한 검색을 수행하기 어렵다.

 

예시

email

exampleuser123@gmail.com

exampleuser456@gmail.com

exuser789@yahoo.com

 

email(5)로 Prefix Index를 생성

CREATE INDEX email_prefix_idx ON users(email(5));

 

이 경우, 인덱스에는 다음과 같은 데이터만 저장된다.

example
example
exuse

 

이때 WHERE email = 'exampleuser123@gmail.com'이라는 쿼리를 실행하는 경우

• 데이터베이스는 인덱스를 사용해 "example"로 시작하는 레코드를 찾아야 하지만, 인덱스 레코드가 충돌하므로 데이터베이스는 전체 데이터를 다시 스캔하여 결과를 찾아야 한다.
• 결과적으로, 인덱스를 사용한 빠른 조회가 불가능할 수 있다.

 

2. ORDER BY와 GROUP BY 제한

Prefix Index는 정렬과 그룹화에서도 문제가 발생할 수 있다. 이는 데이터베이스가 Prefix Index를 사용해 전체 데이터를 정렬하지 못하기 때문이다.

 

예시

email

alpha_user@gmail.com

bravo_user@gmail.com

charlie_user@gmail.com

 

email(5)로 Prefix Index 생성 후, 다음 쿼리 실행

SELECT * FROM users ORDER BY email;

 

Prefix Index는 데이터베이스가 앞 5글자만 기준으로 정렬하므로, 정확한 결과를 보장할 수 없다.

 

 

3. 범위 검색의 한계

Prefix Index는 일반적으로 범위 조건(ex. LIKE, BETWEEN)에서 사용하기 어렵다.

 

예시

email

alice@gmail.com

bob@yahoo.com

carol@hotmail.com

 

다음 쿼리 실행

SELECT * FROM users WHERE email LIKE 'a%';

 

email(5) Prefix Index가 있다면, 데이터베이스는 접두어만 기준으로 검색하므로 일부 결과가 누락될 수 있다.

 

 

---

Prefix Index를 사용하면 좋은 경우

---

Prefix Index는 긴 문자열 컬럼에서 저장 공간 절약과 효율적인 조회를 목표로 사용할 수 있지만, 특정 상황에서만 효과적이다.

 

1. 긴 문자열 컬럼에서 저장 공간이 중요한 경우

• 긴 문자열을 전체 인덱싱 하면 인덱스 크기가 지나치게 커져 저장 공간과 메모리를 많이 소비한다.
• Prefix Index를 사용해 필요한 만큼만 인덱싱하면 저장 공간을 절약할 수 있다.
• ex) UUID, Token, Hash 등

예시

• 컬럼: url (VARCHAR(2048))
• 인덱스

CREATE INDEX idx_url_prefix ON links(url(100));

• URL 전체(최대 2048바이트)를 인덱싱 하지 않고, 앞 100바이트만 저장

 

2. 데이터의 접두어로 충분히 구분 가능한 경우

• 문자열 컬럼의 앞부분만으로 데이터가 고유하게 구분되는 경우, Prefix Index가 적합하다.
• 전체 문자열을 인덱싱 하지 않아도 성능이 크게 저하되지 않는다.

예시

• 이메일 주소: email 컬럼
• 대부분의 이메일 주소는 앞 10~20글자로 구분 가능
• 인덱스

CREATE INDEX idx_email_prefix ON users(email(20));

 

 

3. 정렬이 필요 없는 단순 조회 쿼리의 경우

• Prefix Index는 정확한 정렬을 보장하지 못하므로, 정렬이 없는 단순 WHERE 조회에서만 사용하는 것이 적합하다.

예시

SELECT * FROM products WHERE name LIKE 'abc%';

 

 

---

Prefix Index를 사용할 때의 고려사항

---

Prefix Index는 기본적으로 사용하기에는 단점이 많지만, 특정 상황에서 유용할 수 있다. 이를 적절히 사용하기 위해서는 다음과 같은 사항들을 고려해야 한다.

 

1. Prefix 길이

• 충돌 가능성을 최소화하려면, 충분히 긴 Prefix를 선택해야 한다.
• Prefix 길이를 결정할 때는 데이터 분포를 분석해야 한다.
• ex) SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) FROM users;

 

2. 인덱스 사용 가능성 테스트

• Prefix Index를 생성한 후, 쿼리가 인덱스를 사용하는지 EXPLAIN PLAN으로 확인해야 한다.

 

---

결론

---

Prefix Index는 긴 문자열 인덱싱을 최적화하기 위한 도구로, 저장 공간 절약과 효율성 향상에 유용하지만, 기본적으로 사용하기에는 여러 단점이 있습니다. 특히, 정확한 검색, 정렬, 범위 검색에서 한계가 있기 때문에, 이를 기본적으로 사용하지 않고 데이터 구분 가능성과 쿼리 특성을 신중이 고려하고 데이터 분포와 쿼리 패턴을 분석한 후 적용하는 것이 중요하다.