MySQL ‐ Why don't use prefix index in default

접두사 인덱스란(Prefix Index)

• 보통 인덱스는 컬럼 값 "전체"를 대상으로 만든다.
• 접두사 인덱스는 문자열 컬럼의 앞에서부터 지정한 길이만큼만 잘라서 인덱스를 만든다.

-- email 컬럼 전체가 아니라, 앞 10글자만으로 인덱스 생성
CREATE INDEX idx_email ON member (email(10));

왜 이런 게 있는가(존재 이유)

• 인덱스도 디스크와 메모리를 차지하는 별도의 자료구조라는 점을 명심해야 한다.
  • 인덱스가 클수록, 저장 공간을 많이 차지한다.
  • 인덱스가 클수록, 메모리(버퍼 풀)에 적게 올라가서 캐시 효율이 떨어진다.
  • 인덱스가 클수록, 인덱스 갱신(INSERT/UPDATE) 비용도 커진다.
• VARCHAR(1000) 이나 URL, 긴 설명글처럼 값이 아주 긴 컬럼은 전체를 인덱싱하면 인덱스가 비대해진다.
• 이럴 때 "어차피 앞부분만 봐도 대부분 구분되니까" 앞 N글자만 인덱싱해서 크기를 확 줄이는 것이 접두사 인덱스다.
• 참고 : TEXT, BLOB 타입은 전체 인덱싱이 불가능해서, 인덱스를 걸려면 반드시 접두사 인덱스를 써야 한다.

그런데 왜 기본으로 쓰지 않는가(핵심)

• 접두사 인덱스는 크기를 아끼는 대신 아래 기능들을 포기한다. 그래서 "기본"이 아니라 "필요할 때만"이다.

1. 선택도(Selectivity)가 떨어질 수 있다

• 앞 N글자가 짧으면 서로 다른 값인데도 접두사가 같아지는 경우가 많아진다.
• 예시 : 이메일을 email(3)으로 인덱싱했는데 abc...로 시작하는 회원이 수천 명이면, 인덱스를 타도 결국 수천 건을 다시 걸러내야 한다 → 인덱스 효과가 약해진다.

2. 커버링 인덱스로 쓸 수 없다

• 인덱스에는 값의 앞부분만 있으므로, 인덱스만 읽어서 원래 컬럼 값을 되돌려줄 수 없다.
• 따라서 항상 실제 테이블 행을 다시 읽어야 한다.(Using index 최적화 불가)

3. 정렬(ORDER BY) / 그룹핑(GROUP BY)에 쓸 수 없다

• 앞부분만으로는 전체 값의 순서를 알 수 없다.
• 예시 : apple과 application은 앞 5글자가 appl...로 같아서, 접두사만으로는 둘의 정확한 정렬 순서를 판단하지 못한다.
• 그래서 접두사 인덱스는 ORDER BY나 GROUP BY의 정렬을 대신해 줄 수 없다.

접두사 길이는 어떻게 정하나?

• 너무 짧으면 → 선택도가 낮아 인덱스 효과가 없다.
• 너무 길면 → 공간 절약이라는 목적 자체가 사라진다.
• 그래서 "전체 컬럼의 선택도"에 최대한 근접하면서도 가장 짧은 길이를 찾는다.

-- 1) 전체 컬럼의 선택도 (기준값)
SELECT COUNT(DISTINCT email) / COUNT(*) AS full_selectivity
FROM member;

-- 2) 여러 접두사 길이의 선택도를 비교
SELECT
  COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel_5,
  COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS sel_8,
  COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS sel_12
FROM member;

• sel_N 값이 full_selectivity에 충분히 가까워지는 지점의 길이를 고른다.

언제 쓰면 좋은가 / 언제 피해야 하는가

• 쓰면 좋은 경우
  • 값이 매우 긴 문자열 컬럼인데, 앞부분만으로도 값이 충분히 구분되는 경우
  • TEXT / BLOB처럼 애초에 전체 인덱싱이 불가능한 컬럼
• 피해야 하는 경우
  • 해당 컬럼으로 ORDER BY / GROUP BY를 자주 하는 경우
  • 커버링 인덱스로 조회 성능을 끌어올리고 싶은 경우
  • 앞부분이 거의 비슷해서 선택도가 낮은 값(예시 : https://www. 로 시작하는 URL, 공통 접두어가 긴 코드값)