[DB 성능 분석] EXPLAIN 기반 쿼리 진단 및 인덱스 개선 포인트 정리

열일하는 개발자 키티

 

최근에 시간적 여유가 생기면서, 운영 중인 서비스의 주요 쿼리들을 점검해 성능 개선이 가능한 부분이 있는지 확인해보려고 한다. 

이를 위해 EXPLAIN 을 활용해 주요 SELECT 쿼리의 실행 계획을 분석하고, 인덱스 효율성과 조인 순서 및 조건 최적화 방향을 정리하려고 한다. 

 

👉🏻 이번 진단을 통해 실제 운영 환경에서의 인덱스 설계가 적절히 이루어졌는지 검토함으로써 서비스 전반의 성능 안정성을 높이는 것이 목표다 ! 

 

 

📘 분석 방법 

주요 SELECT 쿼리들의 실행 계획을 EXPLAIN 으로 확인하며, 각 쿼리가 실제로 어떤 방식으로 데이터를 읽고 조인하는지를 점검했다. 

type, possible_keys, key, rows, Extra 항목을 중심으로 쿼리의 접근 방식(풀스캔, 인덱스 스캔, 조인 방식 등)을 분석하고 인덱스 효율성과 조인 순서가 적절히 선택되고 있는지 확인했다. 

또한 Using temporary, Using filesort와 같이 정렬, 집계 시 추가 비용이 발생하는 항목을 체크하여 인덱스 추가 또는 WHERE 조건 구조 변경을 통한 개선 가능성을 검토하였다. 

 

 

✏️ EXPLAIN 개념 및 주요 타입 정리 

 

EXPLAIN 이란 ? 

MySQL 에서 EXPLAIN 은 쿼리의 실행 계획을 확인하기 위한 명령어로, 옵티마이저가 쿼리를 어떻게 해석하고, 어떤 순서로 테이블을 읽는지를 보여준다. 

즉, 쿼리가 실제로 인텍스를 타는지, 풀스캔을 하는지, 조인 순서가 어떻게 되는지 미리 예측할 수 있는 도구이다. 

 

항목	설명
id	쿼리 실행 단계 번호. 서브쿼리나 파생쿼리가 많을수록 여러 id가 생김.
select_type	단순 SELECT인지, 서브쿼리인지, UNION 등인지 구분.
table	접근하는 테이블명 또는 파생 테이블(alias).
type	조인 방식(=접근 방식). 쿼리 성능 판단의 핵심 지표.
possible_keys	옵티마이저가 사용할 수 있다고 판단한 인덱스 후보.
key	실제로 사용된 인덱스. NULL이면 인덱스 미사용.
rows	옵티마이저가 예측한 스캔 행 수(값이 작을수록 효율적).
Extra	추가 작업 정보 (임시 테이블 생성, 정렬 등).

 

type (접근 방식) 

: type 은 실행 계획에서 가장 중요하게 보는 항목으로, 값이 ALL 에 가까울수록 느리고, const에 가까울수록 효율적인 접근이다. 

type	의미	성능	설명
system	단 1행만 존재하는 테이블	⭐️최상	테스트용·조회용 작은 테이블
const	PK 또는 UNIQUE 인덱스로 단일 행 조회	⭐️	WHERE id = 1 형태
eq_ref	JOIN 시 양쪽이 PK/UNIQUE 인덱스로 1:1 매칭	⭐️	조인 최적
ref	인덱스로 매칭하지만 여러 행 가능	✅	일반적인 FK 조회
range	인덱스를 이용한 범위 조회	✅	BETWEEN, <, >
index	인덱스 전체 스캔 (테이블보다 빠르지만 여전히 많음)	⚠️	인덱스만 읽음
ALL	테이블 전체 스캔	❌	인덱스 미사용 (최악)

 

👉🏻 여기서 ALL 이 나오는게 가장 최악,, 풀스캔하고 있다는 뜻이다. 

 

 

Extra 주요 항목 

: Extra 는 쿼리 실행 중 추가적인 작업이나 최적화 여부를 알려주는 부분이다. 

Extra	의미	조치 방향
Using where	WHERE 조건으로 필터링 중	정상
Using index	커버링 인덱스 사용 (테이블 접근 없음)	효율적
Using temporary	임시 테이블 생성 (GROUP BY 등)	성능 저하 가능
Using filesort	정렬 작업 발생 (ORDER BY 등)	인덱스로 정렬 처리 고려
Using join buffer (BNL)	블록 네스티드 루프 조인 발생	조인 키 인덱스 필요
NULL	추가 작업 없음	정상

 

👉🏻 ORDER BY 등 때문에 filesort 가 일어나면 생각보다 성능이 많이 떨어진다,, 따라서 인덱스로 정렬 처리를 고려하는것이 좋다. 

 

 

 

🔎 분석 예시 

* 회사 쿼리를 공개할 수는 없으니, 글 작성을 위해 테이블/컬럼명을 전부 변경한 예시입니다.* 

 

1️⃣ 

<select id="selectLatestLocation" parameterType="int" resultType="Location">
    SELECT *
    FROM LOCATIONS
    WHERE PARENT_ID = #{parentId}
    ORDER BY CREATED_AT DESC
    LIMIT 1
</select>

 

 

 

이 쿼리는 이전에 인덱스가 PK 만  설정되어 있고, 아무런 설정이 되어있지 않았다. 

따라서 EXPLIAN 해본 결과 풀스캔에 심지어 filesort 까지 이뤄져 굉장히 성능이 안 좋은 쿼리임을 발견했다. 

 

CREATE INDEX IDX_locations_parent_created
ON LOCATIONS (PARENT_ID, CREATED_AT DESC);

이렇게 복합 인덱스로 PARENT_ID, CREAT_AT 을 순서대로 선언해줬고, 

그 후에 다시 EXPLAIN 해본 결과 옵티마이저가 풀 스캔 대신 인덱스를 선택했고, 조회 성능이 훨씬 좋아짐을 확인할 수 있었다. 

(이때 정렬을 보통 내림차순으로 조회를 하고 있길래 DESC로 설정했다.) 

 

개선 후 EXPLAIN 요약 비교

구분	type	key	rows	Extra
Before	ALL	NULL	2만+	Using where; Using filesort
After	ref	IDX_locations_parent_created	수십~수백	Using where (filesort 제거

 

 

✅ LEFT JOIN 조건이 올바르더라도 WHERE 절 필터링 컬럼이 인덱스가 없으면 조인 비용이 급격히 증가함   

✅ ORDER BY 패턴은 복합 인덱스 설계로 정렬 비용을 크게 줄일 수 있음 

 

 

2️⃣ 

SELECT *
FROM LOCATION_REQUESTS
WHERE PARENT_ID = 'user_parent_001'
  AND CHILD_ID = 'user_child_002';

이 쿼리는 부모 사용자와 자녀 사용자의 위치 요청 데이터를 조회하는 로직으로 두 개의 사용자 ID를 기반으로 단일 레코드를 조회한다. 

EXPLIAN 결과에서 type = ALL 이 발생하고 있었고, possible_keys, key 값 모두 NULL  상태로 인덱스가 아예 존재하지 않았음을 확인했다. 

 

CREATE INDEX IDX_location_request_parent_child
ON LOCATION_REQUESTS (PARENT_ID, CHILD_ID);

따라서 이렇게 PARENT_ID, CHILD_ID로 복합 인덱스를 설정해 풀스캔 방지를 해줬다. 

 

 

개선 후 EXPLAIN 요약 비교

구분	type	possible_keys	key	rows	Extra
Before	ALL	NULL	NULL	35,200	Using where
After	ref	IDX_location_request_parent_child	IDX_location_request_parent_child	1	Using where

 

type ALL 에서 ref 로 개선했으며, rows 를 보면 35,200 건  조회하던 결과를 1로 정확히 매칭될 수 있도록 했다. 

 

 

3️⃣ 인덱스 추가가 불필요한 예시  

SELECT *
FROM INQUIRIES
WHERE USER_ID = 'user_001'
ORDER BY CREATED_AT DESC;

 

 

이 쿼리의 경우에는 사용자의 문의 내역을 조회하는 SELECT 문으로 실제 테이블 데이터는 약 700행 정도로 매우 적은 편이었다. 

실행 계획을 보면 type = ALL, key = NULL 로 표시되어 인덱스가 사용되지 않았지만 

rows 값이 700으로 작기 때문에 풀스캔의 비용이 크지 않았다. 

따라서 인덱스가 없다고 해도 전체 스캔도 빠른 상황이라 인덱스를 따로 추가하지 않았다. 

 

✅ 테이블 크기가 수백 행 수준이라면, 풀 스캔이 인덱스 탐색보다 빠를 수 있다. 

✅ 그리고 옵티마이저가 통계적으로 '인덱스 탐색 + 랜덤 I/O' 비용이 '순차 I/O' 보다 크다고 판단되면 인덱스를 사용하지 않는다. 

 

⭐ 인덱스를 항상 추가하는 것이 아니라 데이터 양이 많고, 필터링 결과가 전체의 10% 미만일 때 만들어야 한다 !! 

작은 테이블은 풀 스캔이 빠르고, 인덱스는 오히려 쓰기(INSERT/UPDATE) 부하를 늘릴 수도 있다. 

 

 

💡 새로 알게된 점 

1️⃣ 풀스캔이 항상 나쁜 건 아니다.

풀스캔이면 나는 무조건 개선을 해야 한다고 생각을 했다. 하지만 테이블 크기가 수백 행 수준이라면 옵티마이저가 풀스캔을 더 효율적으로 판단할 수도 있다는 걸 알게 됐다. 

type=ALL 만 보고 인덱스를 무조건 추가하는 게 아니라 데이터 양과 접근 빈도를 함께 고려해야 함을 알게 됐다. 

 

2️⃣ 복합 인덱스는 단일 인덱스보다 "쿼리 구조"에 맞춰 설계해야 한다. 

위의 예시처럼 (PARENT_ID, CREATED_AT DESC) 처럼 WHERE + ORDER BY 조합으로 맞춰두면 filesort 없이 정렬이 된 것처럼 쿼리 구조를 먼저 보고 인덱스를 설계해야 함을 배웠다. 

 

3️⃣ 인덱스는 성능뿐 아니라 쓰기 부하도 고려해야 한다. 

로그인처럼 요청 수가 높은 조회는 인덱스가 필수겠지만, 하루 몇 번 호출되지 않는 쿼리에 인덱스를 늘리는 건 오히려 또 다른 관리 포인트가 될 수도 있음을 알게 됐다. (인덱스가 만능이 아니라는 점) 

 

 

 

👩🏻‍💻 결론 (실제 적용 여부 / 운영 고려) 

오늘 분석한 인덱스 후보를 바로 운영 DB에 적용하기는 당연히 어렵다. 

점검 사항들을 체크 후 적용하는 게 맞겠다는 판단이 드는 인덱스들은 추가를 할 예정이다. 

 

[점검 사항] 

❓ 트래픽 상위 쿼리 여부 

❓쓰기 부하 증가 가능성 여부 

❓중복 인덱스 여부 

❓데이터 규모 (테이블 건수) 

 

 

 

🤔💭 

오늘은 운영중인 서비스의 주요 SELECT 쿼리들의 실행 계획을 EXPLAIN 으로 분석하고, 인덱스 효율을 확인해봤다.

일부 쿼리에서 인덱스 미사용 중 등의 비효율적인 쿼리들을 확인했고, 각 쿼리에 맞게 복합 인덱스 생성, WHERE 조건 구조 개선 방향을 정리했다. 

이번 분석을 통해서 단순히 쿼리 성능 개선 뿐만 아니라, DB 옵티마이저가 어떻게 판단하는지를 알 수 있었던 시간이었다 ! 

또한 개발 서버에서 동작 잘 한다고 인덱스의 필요 여부를 모르고 넘어간 쿼리들도 발견할 수 있었다.. 

앞으로 신규 서비스 쿼리를 작성할 때는 좀 더 효율적으로 쿼리를 작성할 수 있도록 신경써야겠다는 교휸을 얻었다.