[DB Deep Dive] 3. "인덱스를 걸었는데 왜 안 탈까요?" EXPLAIN 실행 계획 분석과 튜닝

1. 시작하며: 내 쿼리는 정말 인덱스를 타고 있을까?

우리가 아무리 열심히 B+Tree 복합 인덱스를 설계해 두었다고 해도, 쿼리를 잘못 작성하면 DB의 옵티마이저(Optimizer)는 인덱스를 쿨하게 무시하고 테이블 풀 스캔(Full Scan)을 때려버립니다. 대규모 트래픽 환경에서 이는 곧바로 장애로 이어집니다.

따라서 백엔드 개발자는 쿼리를 작성한 후 반드시 EXPLAIN(실행 계획) 명령어를 통해 옵티마이저의 속마음을 들여다보고 쿼리가 의도대로 작동하는지 검증해야 합니다. 수많은 실행 계획 컬럼 중, 실무에서 쿼리 튜닝을 할 때 가장 중요하게 쳐다보는 핵심 컬럼들(type, Extra, rows)을 완벽하게 분석해 보겠습니다.

실행계획 예시

2. 튜닝의 핵심 지표: type 컬럼 (접근 방식)

type 컬럼은 옵티마이저가 테이블의 레코드를 어떤 방식(풀 스캔 vs 인덱스 스캔)으로 읽었는지 알려주는 가장 중요한 지표입니다. 여러 가지 타입이 있지만, 성능이 가장 좋은 순서대로 알아야 할 4대장은 다음과 같습니다.

🚀 성능 최고: const (또는 eq_ref)

• 조회 결과가 반드시 1건임을 보장하는 최고의 접근 방식입니다.
• 주로 WHERE id = 5 처럼 PK(Primary Key)나 Unique Key를 동등 조건(=)으로 검색할 때 나타납니다.

🏃‍♂️ 성능 좋음: ref

• 동등 조건(=)으로 검색하지만, PK나 Unique Key가 아닌 일반 인덱스를 타서 결과가 여러 건일 수 있을 때 나타납니다.
• 충분히 매우 빠른 레코드 조회 방식입니다.

🚶 성능 보통: range

• 인덱스를 하나의 값이 아닌 특정 범위로 스캔하는 방식입니다.
• <, >, IS NULL, BETWEEN, IN, LIKE 연산자를 사용할 때 주로 나타나며, 실무에서 가장 많이 보게 되는 준수한 성능의 타협점입니다.

🚨 튜닝 대상 1순위: ALL (Table Full Scan)

• 가장 끔찍한 단어입니다. 인덱스를 타지 못하고 테이블을 처음부터 끝까지 전부 다 읽어버렸다는 뜻입니다.
• 데이터가 적을 땐 괜찮지만, 대용량 테이블에서 type: ALL이 뜨면 무조건 인덱스를 추가하거나 쿼리를 수정해야 합니다.

 

3. 내 쿼리의 성적표: Extra 컬럼

Extra 컬럼은 옵티마이저가 쿼리를 어떻게 가공하고 처리했는지에 대한 내부적인 힌트를 제공합니다. 여기서 개발자를 울고 웃게 만드는 대표적인 메시지 2가지를 소개합니다.

✨ 축복의 메시지: Using index (커버링 인덱스)

지난 2편에서 배운 커버링 인덱스(Covering Index)가 제대로 발동했다면, Extra 컬럼에 당당하게 Using index가 출력됩니다! 데이터 파일을 읽기 위한 디스크 점프(Data Lookup) 없이, 메모리에 올라와 있는 인덱스 트리만 읽어서 쿼리가 끝났다는 뜻으로, 엄청나게 빠른 속도를 보장합니다.

⚠️ 경고 메시지: Using filesort

쿼리에 ORDER BY가 들어있는데 정렬 기준이 인덱스를 타지 못할 때 발생합니다. DB가 데이터를 다 가져온 뒤 별도의 메모리 버퍼(Sort Buffer)에 올려두고 직접 퀵 소트 등으로 정렬했다는 뜻으로 엄청난 부하를 일으킵니다. 복합 인덱스의 순서를 정렬 기준에 맞게 재설계하는 등의 튜닝이 시급합니다.

💡 보너스 팁: 성능 감각을 키워주는 rows 컬럼

rows 컬럼은 옵티마이저가 쿼리 처리를 위해 "대략 몇 건의 데이터를 읽어야 할까?"라고 예측한 수치입니다. 만약 type이 인덱스를 잘 탔다고 나오더라도, 이 rows 값이 전체 데이터 수에 육박할 정도로 지나치게 크다면 비효율적인 스캔이 발생하고 있다는 뜻입니다. 무늬만 인덱스 스캔일 뿐 실질적인 성능은 풀 스캔과 다를 바 없으므로 튜닝을 의심해봐야 하는 아주 좋은 통찰력 지표입니다.

 

4. 면접 핵심: "인덱스를 걸었는데 왜 ALL(풀 스캔)이 뜰까요?"

면접에서 가장 자주 묻는 함정 질문입니다. 인덱스는 잘 만들어져 있지만, 개발자가 쿼리를 잘못 짜서 B+Tree 구조를 무력화시키는 3대 악마의 쿼리 패턴이 있습니다.

-- 1. 인덱스 컬럼을 직접 가공(함수 사용)하는 경우
[BAD] SELECT * FROM USER WHERE YEAR(created_at) = 2024;
[GOOD] SELECT * FROM USER WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
(B+Tree는 원본 데이터 기준으로 정렬되어 있습니다. 컬럼 자체에 함수를 씌우면 정렬 구조가 깨져 풀 스캔을 합니다.)

-- 2. LIKE 검색 시 와일드카드(%)가 앞에 있는 경우
[BAD] SELECT * FROM USER WHERE name LIKE '%길동';
[GOOD] SELECT * FROM USER WHERE name LIKE '홍%';
(영어 사전을 찾을 때 'ple'로 끝나는 단어를 찾는다고 상상해 보세요. 첫 글자를 모르면 무조건 처음부터 다 뒤져야 합니다.)

-- 3. 묵시적 형 변환이 일어나는 경우
[BAD] SELECT * FROM USER WHERE phone = 01012345678;  -- (phone이 VARCHAR일 때)
[GOOD] SELECT * FROM USER WHERE phone = '01012345678';
(문자열 컬럼에 숫자 타입으로 검색하면, DB가 몰래 문자열을 숫자로 형변환하는 함수를 씌워버려 인덱스를 타지 못합니다.)

 

5. 3편을 마무리하며

지금까지 우리는 B+Tree의 원리(1편), Clustered와 Non-Clustered의 물리적 구조 차이와 커버링 인덱스(2편), 그리고 마지막으로 내가 짠 쿼리의 성능을 증명해 내는 EXPLAIN 실행 계획 분석(3편)까지 달려왔습니다. 이 세 가지 포스팅은 서로 유기적으로 연결되어 "데이터베이스의 병목을 구조적으로 분석하고 해결하는 백엔드 개발자"라는 완벽한 서사를 만들어냅니다.

이론적 기초 체력을 기르셨으니, 이제부터는 이 무기들을 실제 프로젝트 코드에 적용하고 장애를 해결한 생생한 트러블슈팅 경험으로 넘어가 볼 차례입니다!

 

6. 추가 링크

https://zzang9ha.tistory.com/436#google_vignette

MySQL EXPLAIN 실행계획 마스터하기(feat. RealMySQL 8.0)