[MySQL] 단일 인덱스 2개는 복합 인덱스를 이길 수 없었다.

1부에서 인덱스가 어떻게 생겼는지, 2부에서 컬럼이 여러 개일 때 뭘 조심해야 하는지를 이론으로 봤다. 이번 글은 그 이론을 실제로 검증한 기록이다 — "컬럼별로 단일 인덱스를 하나씩 걸어도 되지 않을까"라는 질문에, 실제로 두 방식(단일 인덱스 2개 vs 복합 인덱스 1개)을 직접 만들어서 비교했다. 그리고 이 원리가 Full-Text 인덱스에서도 똑같이 반복되는지도 같이 확인했다.

사례 1 — index_merge, "인덱스 두 개"의 함정

"학과별 현재 개설 과목 조회" 쿼리로 확인했다.

SELECT c.id FROM course c
WHERE c.offering_department_id = 3 AND c.is_active = 1;

offering_department_id엔 FK 제약을 지지하는 단일 컬럼 인덱스가 이미 있었다. is_active엔 단일 컬럼 인덱스가 없어서, "컬럼별로 인덱스를 각각 걸었다면 어떻게 됐을지"를 재현하려고 임시로 하나 만들었다.

Intersect rows sorted by row ID — 두 인덱스를 각각 스캔한 뒤 합치는 게 실행계획에 그대로 찍힌다. offering_department_id = 3으로 620행, is_active = 1로 4,481행을 각각 읽어서 교집합을 냈다. 최종 결과는 536행인데, 그걸 얻으려고 5,101행(620+4,481)을 읽었다.

원인은 is_active의 치우침이다. true 값 하나가 전체의 89.6%를 차지한다. 이 컬럼만 놓고 보면 인덱스가 있어도 사실상 거의 못 거른다 — 4,481행 중 4,481행을 다 가져와야 하는 것과 다르지 않다.

복합 인덱스로 바꿨다.

CREATE INDEX idx_course_dept_active
    ON course (offering_department_id, is_active);

Covering index lookup — 정확히 536행만 읽고 끝났다. 읽은 행 5,101 → 536, 9.5배 차이. 이유는 2부에서 설명한 그대로다: offering_department_id로 먼저 좁혀서 620행까지 줄이고, 그 안에서 is_active를 확인하는 건 애초에 index_merge가 필요 없을 만큼 저렴하다. 인덱스를 "몇 개" 거느냐가 아니라 "쿼리가 컬럼을 조합하는 방식"에 인덱스 구조를 맞추는 게 핵심이었다.

사례 2 — Full-Text에서도 똑같은 실수를 할 뻔했다

과목 검색(name 또는 course_code 부분 일치)에 Full-Text 인덱스를 붙이면서, 처음엔 "컬럼마다 하나씩 걸고 OR로 묶으면 되겠지" 생각했다.

-- 컬럼별로 각각
ALTER TABLE course ADD FULLTEXT INDEX ft_course_name (name) WITH PARSER ngram;
ALTER TABLE course ADD FULLTEXT INDEX ft_course_code (course_code) WITH PARSER ngram;

SELECT COUNT(*) FROM course c
WHERE c.school_id = 1 AND c.is_active = 1
  AND (MATCH(c.name) AGAINST('"디자인"' IN BOOLEAN MODE)
       OR MATCH(c.course_code) AGAINST('"디자인"' IN BOOLEAN MODE));

Table scan on c — 인덱스를 두 개나 만들었는데 하나도 안 탔다. 5,000행 전체를 스캔해서 156건을 걸러냈다. B-Tree의 index_merge처럼 서로 다른 Full-Text 인덱스에 걸친 OR을 병합해서 타는 전략 자체가 옵티마이저에 없었다.

두 컬럼을 하나의 복합 Full-Text 인덱스로 합쳤다.

ALTER TABLE course ADD FULLTEXT INDEX ft_course_name_code (name, course_code) WITH PARSER ngram;

SELECT COUNT(*) FROM course c
WHERE c.school_id = 1 AND c.is_active = 1
  AND MATCH(c.name, c.course_code) AGAINST('"디자인"' IN BOOLEAN MODE);

Full-text index search on c using ft_course_name_code — 5,000행 대신 180행만 후보로 가져온 뒤 156건으로 좁혔다. 최종 결과 156건은 풀스캔 때와 정확히 같다 — 빨라졌다고 결과가 달라진 게 아니라는 것까지 같은 화면에서 확인됐다.

원인 메커니즘은 사례 1과 다르다(index_merge의 병합 비용 vs 옵티마이저가 애초에 그 조합을 못 탐). 하지만 처방은 똑같았다 — 쿼리가 여러 컬럼을 동시에 조건으로 쓴다면, 인덱스도 그 조합에 맞춰 하나로 만든다.

검증도 따로 했다. LIKE 부분일치와 Full-Text 검색이 정말 같은 행을 찾는지, 한글·영문·숫자·영숫자 경계를 포함한 2글자 이상 검색어 13개로 비교했고 전부 일치했다. 1글자 검색어만 ngram 최소 토큰 길이(2글자) 제약으로 원천적으로 안 되는데, 이건 API 단에서 2글자 미만 검색어를 400으로 막는 걸로 정리했다 — 결과가 없는 게 아니라 검색 자체가 불가능한 거라, 조용히 빈 결과를 주는 것보다는 명시적으로 막는 게 맞다고 판단했다.

인덱스는 공짜가 아니다

두 사례 모두 "인덱스가 없어서"가 아니라 "있는 인덱스가 쿼리가 실제로 컬럼을 조합하는 방식과 맞지 않아서" 생긴 문제였다. 컬럼별로 인덱스를 쌓는 대신, WHERE 절이 어떤 컬럼들을 함께 필터링하는지 먼저 보고 그 조합에 맞는 복합 인덱스 하나로 설계하는 게 원칙이었다.

다만 이걸 무조건 다 걸어도 되는 건 아니다. 이번에 추가한 인덱스 전체를 반영한 쓰기 경로(INSERT)를 측정했더니 약 8% 느려졌다. 1부 5번에서 말했듯 인덱스는 읽기와 쓰기를 맞바꾸는 트레이드오프이고, 이 프로젝트는 읽기가 압도적으로 빈번한 워크로드라 감수할 만하다고 판단했다.

---

정리하면:

사례	이전	이후	차이
학과별 개설 과목 조회 (index_merge)	5,101행 읽음	536행 읽음 (Covering index)	9.5배
과목 키워드 검색 (Full-Text)	5,000행 스캔	180행 검색 (Full-text index search)	결과는 156건으로 동일

돌아보며

이 3부작을 관통하는 하나의 태도가 있다면 — 추측 대신 확인이다. "인덱스를 걸었으니 빠를 것이다"가 아니라 EXPLAIN ANALYZE로 실제 실행계획을 확인했고, "응답시간이 줄었으니 개선됐다"가 아니라 같은 쿼리를 여러 번 측정해서 응답시간 자체가 노이즈에 얼마나 흔들리는지부터 확인한 뒤 읽은 행 수를 기준으로 삼았다. 처음 세운 가설이 재현되지 않았던 순간에도 숨기지 않고, 실제로 재현되는 사례를 다시 찾아 검증했다.

인덱스 설계는 결국 "몇 개를 거느냐"의 문제가 아니라, 내 쿼리가 실제로 데이터를 어떻게 필터링하는지를 이해하고 그 모양에 맞춰 하나씩 판단하는 문제였다.