실전튜닝 4 질문 - group by 순서와 index 순서

gmldk3214

1 câu hỏi đã được viết

0

안녕하세요, 인프런 AI 인턴입니다.

gmldk3214님, 핵심은 GROUP BY의 논리적인 순서와 인덱스가 실제로 만들어 내는 물리적인 정렬 순서가 다르다는 점입니다.

1. GROUP BY 컬럼의 순서는 결과 의미상 중요하지 않습니다

다음 두 쿼리는 그룹의 의미가 같습니다.

GROUP BY member_id, member_name, grade

GROUP BY grade, member_name, member_id

세 컬럼의 조합이 동일하기 때문입니다.

하지만 인덱스를 이용해서 임시 테이블 없이 그룹핑하려면, 인덱스 리프 노드에서 읽히는 행이 GROUP BY 대상 기준으로 연속해서 나타나야 합니다.

즉, 논리적으로는 순서가 상관없지만, 인덱스 관점에서는 순서가 중요합니다.

---

2. (grade, member_name, member_id)가 GROUP BY 순서와 맞지 않는 이유

현재 인덱스가 다음과 같다고 가정하겠습니다.

(grade, member_name, member_id)

grade = 'VIP' 조건이 있으므로 실제로 VIP 데이터만 보면 인덱스 정렬 순서는 다음과 같습니다.

member_name, member_id

즉, 다음과 같은 순서입니다.

grade가 VIP인 회원들
 └─ member_name 순서
     └─ 같은 이름이면 member_id 순서

하지만 GROUP BY 기준은 다음과 같습니다.

member_id
 └─ member_name
     └─ grade

따라서 member_id가 먼저 연속적으로 정렬되어 있지 않습니다.

예를 들어 다음과 같은 데이터가 있다면,

member_id	member_name	grade
1	김철수	VIP
2	이영희	VIP
3	김철수	VIP

(grade, member_name, member_id) 인덱스에서는 대략 다음 순서로 읽힙니다.

김철수, 1
김철수, 3
이영희, 2

하지만 GROUP BY 기준인 (member_id, member_name, grade) 순서로 보면 다음과 같습니다.

1, 김철수, VIP
2, 이영희, VIP
3, 김철수, VIP

따라서 인덱스를 읽으면서 같은 그룹이 항상 연속해서 나온다고 보장할 수 없습니다.

그룹별 집계를 위해서는 중간 결과를 임시 테이블에 저장하고 합쳐야 할 수 있습니다.

---

3. 더 적절한 인덱스 순서

이 쿼리의 GROUP BY 기준을 고려하면 다음과 같은 인덱스가 더 적합할 수 있습니다.

CREATE INDEX idx_member_grade_id_name
ON member (grade, member_id, member_name);

grade = 'VIP'가 상수 조건이므로, grade 이후의 실제 정렬 순서는 다음과 같습니다.

member_id, member_name

그리고 grade는 모든 행에서 VIP로 동일하므로 논리적으로 다음 GROUP BY 순서와 대응할 수 있습니다.

member_id, member_name, grade

즉, 일반적으로는 다음과 같이 생각할 수 있습니다.

WHERE 등가 조건 컬럼
→ GROUP BY 정렬 순서에 필요한 컬럼

다만 실제로 이 인덱스를 생성했다고 해서 반드시 Using temporary가 사라지는 것은 아닙니다.

---

4. 왜 올바른 인덱스가 있어도 임시 테이블을 사용할 수 있나요?

인덱스가 그룹핑에 적합하더라도 옵티마이저가 다음과 같은 이유로 다른 실행 계획을 선택할 수 있습니다.

조인 순서가 달라지는 경우

이 쿼리는 member와 orders를 조인합니다.

FROM member m
JOIN orders o ON m.member_id = o.member_id

member를 인덱스 순서대로 읽더라도, orders에서 가져오는 행의 순서와 실행 방식에 따라 전체 조인 결과가 GROUP BY 순서를 유지하지 못할 수 있습니다.

예를 들어 orders를 기준으로 먼저 읽는 실행 계획을 선택하면 결과 행은 member_id 순서가 아닐 수 있습니다.

인덱스보다 다른 실행 계획이 더 저렴하다고 판단하는 경우

grade = 'VIP'인 회원이 매우 많다면, 인덱스로 회원을 읽는 것보다 다른 방식으로 조인하는 편이 빠르다고 판단할 수 있습니다.

또한 orders의 데이터가 매우 많다면, 집계를 위해 내부 임시 테이블을 사용하는 해시 집계 또는 정렬 기반 집계를 선택할 수도 있습니다.

ORDER BY 때문에 별도의 작업이 필요한 경우

현재 쿼리는 다음과 같이 집계 결과를 정렬합니다.

ORDER BY total_spent DESC
LIMIT 20

total_spent는 다음 집계 결과입니다.

SUM(o.total_price)

이 값은 여러 주문 행을 읽고 계산해야 알 수 있으므로 일반적인 인덱스로 미리 정렬할 수 없습니다.

따라서 전체 회원의 집계 결과를 만든 뒤 다음 작업이 필요합니다.

1. 회원별 COUNT, SUM 계산
2. 집계 결과를 total_spent 기준으로 정렬
3. 상위 20개 선택

따라서 GROUP BY를 인덱스로 처리할 수 있더라도, ORDER BY total_spent DESC 때문에 집계 결과를 저장하거나 정렬하는 작업이 남을 수 있습니다.

EXPLAIN ANALYZE의 다음 문구가 정확히 어느 단계에서 발생했는지도 확인해야 합니다.

Aggregate using temporary table

이 문구는 단순히 “(grade, member_name, member_id) 인덱스가 GROUP BY와 다르기 때문이다”라고만 단정할 수 없습니다.

전체 실행 계획에서 다음 항목을 함께 확인해야 합니다.

• member와 orders 중 어느 테이블을 먼저 읽는지
  
• 실제 조인 방식이 무엇인지
  
• orders.member_id 인덱스를 사용하는지
  
• 집계 전에 정렬이 발생하는지
  
• 임시 테이블이 GROUP BY 때문인지, ORDER BY 때문인지
  
• 실제로 member의 인덱스를 사용했는지
  

---

5. (grade, member_name, member_id)에서 PK가 뒤에 자동으로 붙는 문제

말씀하신 것처럼 InnoDB의 세컨더리 인덱스에는 기본 키가 함께 저장됩니다.

예를 들어 member_id가 PK이고 다음 인덱스를 만들었다면,

CREATE INDEX idx_member_grade_name
ON member (grade, member_name);

실제 리프 노드에서는 개념적으로 다음과 같은 정렬이 됩니다.

grade, member_name, member_id(PK)

하지만 여기서 중요한 점은 member_id가 인덱스의 세 번째 정렬 기준이라는 것입니다.

즉, 다음과 같은 정렬입니다.

grade 우선
→ member_name 우선
→ member_name이 같을 때 member_id

다음 정렬과는 다릅니다.

grade
→ member_id
→ member_name

따라서 PK가 자동으로 붙는다는 이유만으로 member_id를 인덱스의 선행 컬럼처럼 사용할 수는 없습니다.

다음 두 인덱스는 GROUP BY 관점에서 서로 다릅니다.

(grade, member_name, member_id)

(grade, member_id, member_name)

PK가 자동으로 추가되는 경우에도 기존 인덱스 컬럼보다 앞으로 이동하지 않습니다.

---

6. (grade, member_name)만 만들어도 되는가?

다음 인덱스는 현재 GROUP BY 순서와는 맞지 않습니다.

(grade, member_name)

그 이유는 member_id가 리프 노드에 존재하더라도 다음 순서이기 때문입니다.

grade → member_name → PK(member_id)

현재 GROUP BY는 다음 순서를 요구하는 형태입니다.

member_id → member_name → grade

grade가 상수라는 점을 감안해도 필요한 흐름은 대략 다음과 같습니다.

member_id → member_name

따라서 GROUP BY를 인덱스 순서로 처리하려는 목적이라면 다음이 더 적합합니다.

(grade, member_id, member_name)

다만 member_name이 member_id로 유일하게 결정되는 구조라면 다음과 같이 GROUP BY를 단순화할 수 있는지도 검토할 수 있습니다.

GROUP BY m.member_id

member_id가 PK이고 member_name, grade가 회원 테이블의 회원 ID에 종속된다면, MySQL의 설정과 SQL 모드에 따라 이런 형태가 가능할 수 있습니다.

SELECT m.member_id,
       m.member_name,
       m.grade,
       COUNT(o.order_id),
       SUM(o.total_price)
FROM member m
JOIN orders o ON m.member_id = o.member_id
WHERE m.grade = 'VIP'
GROUP BY m.member_id
ORDER BY total_spent DESC
LIMIT 20;

다만 이는 데이터베이스의 함수적 종속성 처리와 SQL 모드에 영향을 받을 수 있으므로, 강의에서 다룬 기준과 프로젝트의 MySQL 버전에 맞춰 확인해야 합니다.

---

7. 실무적으로 우선 확인할 인덱스

이 쿼리에서는 다음 인덱스들이 중요합니다.

CREATE INDEX idx_member_grade_id_name
ON member (grade, member_id, member_name);

CREATE INDEX idx_orders_member_id
ON orders (member_id);

다만 실제 최적의 인덱스는 데이터 분포에 따라 달라집니다.

특히 orders.member_id 인덱스는 회원별 주문을 빠르게 찾는 데 중요합니다.

최종적으로는 다음 명령으로 실제 사용 인덱스와 조인 순서를 확인해야 합니다.

EXPLAIN ANALYZE
SELECT ...

그리고 단순히 Using temporary가 있다는 사실만으로 인덱스가 잘못되었다고 판단하기보다는, 이 임시 작업이 다음 중 무엇 때문에 발생했는지 구분해야 합니다.

GROUP BY 집계
ORDER BY 집계 결과 정렬
조인 결과의 순서 보장 실패
옵티마이저가 선택한 실행 방식

정리하면 다음과 같습니다.

(grade, member_name, member_id)

는 grade 조건에는 유리할 수 있지만, member_id 기준 GROUP BY 순서를 보장하지 않습니다.

(grade, member_id, member_name)

가 현재 GROUP BY 순서에 더 잘 대응합니다.

또한 세컨더리 인덱스에 PK가 자동으로 포함되더라도, PK는 항상 명시된 인덱스 컬럼 뒤에 정렬되므로 member_id가 선행 컬럼처럼 동작하지는 않습니다.

참고로 Spring Boot와 jOOQ에서 데이터베이스 스키마 기반 코드를 생성하거나 타입 안전한 쿼리를 작성할 때에도, 실제 성능은 생성된 코드보다 데이터베이스의 실행 계획과 인덱스 설계에 의해 결정됩니다.

원문 참고:

https://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#data.sql.jooq