[DB Deep Dive] 5. 내 쿼리의 성적표 EXPLAIN: Using filesort 제거와 Using where의 이해

1. 대용량 스파이크 부하 테스트: 옵티마이저를 깨워라

지난 [상편]에서 우리는 데이터가 고작 43건일 때 MySQL 옵티마이저가 복합 인덱스를 무시하고 풀 테이블 스캔(ALL)을 때려버리는 영리한 배신을 목격했습니다. 인덱스의 진짜 위력을 증명하기 위해, 저는 JMeter를 활용하여 운영 환경과 유사한 대용량 트래픽을 강제로 발생시키기로 했습니다.

단순히 데이터를 넣는 것을 넘어 서버의 비동기 처리(Async) 성능까지 함께 검증하기 위해, Ramp-up(진입 시간)을 단 1초로 설정하고 10,000건의 주문 요청을 동시에 쏘는 스파이크(Spike) 부하 테스트를 세팅했습니다. 찰나의 순간에 쏟아지는 폭격을 Tomcat 스레드와 비동기 큐가 어떻게 버텨낼지 확인하기 위함이었습니다.

 

2. 예기치 못한 에러, 그러나 완벽한 TPS 측정

JMeter 발사 버튼을 누르고 찰나의 시간이 흐른 뒤 결과를 확인해보니, 예기치 못한 상황이 발생했습니다. 10,000건의 주문이 모두 비즈니스 로직 단에서 "잔액 부족" 사유로 차단(실패)된 것입니다.

홈페이지 거래 내역

💡 실패한 테스트일까? 오히려 완벽한 시스템 한계 측정!

주문은 비록 잔액 부족으로 튕겼지만, JMeter의 결과 지표 중 Throughput: 400.3/sec라는 아주 유의미한 수치를 얻어냈습니다. 1초 만에 10,000개의 요청이 쏟아졌음에도 서버가 터지지 않고(Error 0%), 초당 400건(TPS)의 속도로 꾸역꾸역 예외 처리를 해내며 비동기 큐를 안정적으로 비워냈다는 뜻입니다. 단일 서버 기준 훌륭한 방어력을 증명한 셈입니다.

JMeter 결과

 

무엇보다 중요한 것은, 주문 상태가 '실패'로 처리되었을 뿐 orders 테이블에는 제 user_id와 order_date가 박힌 물리적인 데이터가 10,000건 이상 완벽하게 INSERT(적재) 되었다는 사실입니다. 드디어 옵티마이저를 자극할 충분한 모수가 모였습니다.

 

3. 짜릿한 성적표: type: ref와 사라진 filesort

데이터가 2만 건 가까이 적재된 상태에서, 드디어 떨리는 마음으로 [상편]에서 날렸던 그 쿼리 앞에 다시 EXPLAIN을 붙여 실행해 보았습니다.

실행계획 결과

✨ 최종 EXPLAIN 검증 결과 (데이터 누적 후)

• type : ref (복합 인덱스 사용 성공!)
• key : idx_user_date
• rows : 2 (필요한 소량의 데이터만 스캔 예측)
• Extra : NULL (Using filesort 완벽 제거!)

결과는 완벽한 대성공이었습니다. 데이터가 많아지자 옵티마이저는 풀 테이블 스캔을 포기하고 드디어 우리가 설계한 복합 인덱스(idx_user_date)를 타기 시작했습니다 (type: ref).

가장 짜릿한 부분은 악명 높았던 Using filesort가 감쪽같이 사라졌다는 점입니다. 복합 인덱스의 순서를 (user_id, order_date)로 묶어둔 덕분에, B+Tree 인덱스를 타는 순간 데이터가 이미 order_date 순으로 정렬되어 있어 DB가 메모리 위에서 추가적인 정렬(Sorting)을 할 필요가 완벽히 사라진 것입니다. (참고: SELECT * 로 엔티티 전체를 조회하여 Data Lookup이 발생했기에 커버링 인덱스(Using index) 대신 Extra가 비어있는 가장 건강한 상태가 출력되었습니다.)

 

4. [심화 트러블슈팅] 인덱스에 없는 조건을 추가하면 어떻게 될까?

인덱스가 완벽히 작동하는 것을 확인한 후, 저는 호기심이 생겼습니다. "방금 실패한 거래가 많았으니, 상태가 '실패'인 거래내역 20건만 가져오도록 WHERE 절 조건을 추가하면 어떻게 될까?"

WHERE user_id = 1 AND status = '실패' 조건을 넣고 실행 계획을 까본 결과, type은 여전히 ref로 인덱스를 잘 타고 있었지만, Extra 컬럼에 Using where가 등장하고 filtered 컬럼에 50.00이 찍혔습니다. 이것은 성능 저하를 의미하는 걸까요?

실행계획 결과

🔍 Using where와 카디널리티(Cardinality)의 미학

우리의 인덱스에는 status(상태) 정보가 없습니다. 따라서 스토리지 엔진은 user_id만 보고 인덱스를 타서 데이터를 가져온 뒤, MySQL 엔진 레벨에서 status = '실패'가 아닌 데이터들을 직접 쳐내는 필터링 작업을 수행합니다. 이때 등장하는 메시지가 바로 Using where입니다.

"그럼 복합 인덱스를 (user_id, status, order_date) 3개로 묶으면 Using where도 안 뜨고 더 빠른 거 아냐?" 라고 생각할 수 있지만, 이는 실무적으로 좋은 선택이 아닙니다. status(성공, 실패, 대기)처럼 값의 종류가 몇 개 없는 컬럼은 카디널리티(Cardinality)가 극도로 낮아 인덱스 트리에 추가해 봤자 변별력이 떨어지고 용량 오버헤드만 커집니다. 차라리 지금처럼 user_id로 1차 스캔 범위를 대폭 줄인 뒤, 나머지 데이터를 Using where로 가볍게 필터링하게 두는 것이 성능과 용량 사이의 완벽한 트레이드오프(Trade-off)입니다.

 

5. 시리즈를 마치며: 인덱스는 마법이 아니라 '구조적 설계'다

B+Tree의 기본 원리를 다룬 1편, 커버링 인덱스와 물리 구조의 차이를 분석한 2편에 이어, JMeter 부하 테스트와 EXPLAIN으로 제 프로젝트의 쿼리 성능을 수치로 증명해 낸 3편 실전 튜닝기까지 대장정이 마무리되었습니다.

이 과정을 통해 "인덱스만 걸면 무조건 빠를 것이다"라는 막연한 환상을 완벽하게 깰 수 있었습니다. 데이터의 모수(건수)에 따라 영리하게 노선을 변경하는 옵티마이저를 이해하고, 비동기 시스템의 TPS 한계를 뼈저리게 측정해 보았으며, Using filesort를 없애기 위한 컬럼 순서 설계의 중요성, 그리고 카디널리티를 고려한 Using where 필터링의 수용까지.

데이터베이스 성능 튜닝은 단순한 암기가 아니라, 철저한 통계적 검증과 구조적 설계의 산물이라는 것을 깨달은 값진 트러블슈팅 경험이었습니다.