[MySQL] GROUP BY 와 임시테이블(메모리 설정으로 쿼리 성능 개선이 가능했던 건에 대하여)

1. 문제상황과 해결

업무를 하는데 특정 화면에서 Proxy Error 라는 화면이 뜬다는 버그를 접수받았다.

확인해보니 쿼리 실행시간이 너무 오래걸려 타임아웃이 뜬것이었다.

141만건 정도 되는 데이터를 GROUP BY 집계하는 것이 문제였다

정확한 시간은 기억이 안나지만 약 5분간 로딩이 돌다 타임아웃이 뜬것으로 기억한다.

 

처음 1시간은 인덱스 설계 및 도입을 해보았으나 이미 인덱스는 많이 있었고 인덱스로 해결되는 문제가 아니었다.

다음 2시간 정도는 쿼리 최적화를 진행했으나 더이상의 최적화가 사실상 불가능한 구조였다.

 

이 과정중 쿼리를 쪼개 소요시간을 측정한 결과 group by가 포함된 서브쿼리에서 가장 많은 시간이 소요됨을 확인했다.

그리고 group by 실행시 내부적으로 임시테이블이 생성되는데, 이 테이블이 어디에 생성되는지(메모리 vs 디스크)에 따라 속도에 큰 영향을 미칠 수 있음을 알게되었다.

 

실제로 세션 레벨에서 메모리를 늘려 임시테이블이 메모리에 생성되게 해보니 기존에 쿼리 실행에만 2분이상 걸리던것이 20~25초로 줄어들었다.

2. GROUP BY의 내부 동작 원리 (MySQL 기준) - 임시테이블 생성

(1) 데이터 스캔

(2) 임시테이블 생성 ← 스캔한 데이터를 집계

(3) 임시테이블의 각 행을 순회하며 집계

(4) 결과 반환

# 회계원장에서 20XX-XX-XX 일자 이전의 모든 내역을 group by로 처리하던 상황


# MySQL은 GROUP BY를 처리할 때 다음 과정을 거침:

## 1단계: 데이터 스캔
┌──────────────────────────┐
│  부서  │  과목  │  금액  │
├──────────────────────────┤
│ A      │ X      │ 100    │
│ B      │ Y      │ 200    │
│ A      │ X      │ 150    │ ← 같은 그룹
│ C      │ Z      │ 300    │
│ A      │ X      │ 50     │ ← 같은 그룹
└──────────────────────────┘

## 2단계: 임시 테이블 생성
┌───────────────────────────┐
│  부서  │  과목  │  금액  │
├───────────────────────────┤
│ A      │ X      │ 0      │
│ B      │ Y      │ 0      │
│ C      │ Z      │ 0      │
└───────────────────────────┘

## 3단계: 각 행을 순회하며 집계
for each row:
    group_key = (row.부서, row.과목)
    temp_table[group_key] += row.금액

## 4단계: 결과 반환

 

 

문제는 임시테이블에 어디 생성되냐에 있었다

내 클로드는 메모리가 디스크보다 약 500빼 빠르다고한다(헉)

## 임시 테이블: 메모리 vs 디스크

### 메모리 임시 테이블 (빠름 ⚡)

RAM에 해시 테이블 생성
┌──────────────────────────┐
│ Hash Table (in Memory)   │
├──────────────────────────┤
│ (A,X) → 300             │ ← O(1) 접근
│ (B,Y) → 200             │
│ (C,Z) → 300             │
└──────────────────────────┘

접근 속도: 나노초 (0.000001ms)
처리량: ~50 GB/s


### 디스크 임시 테이블 (느림 🐌)

/tmp 디렉토리에 파일 생성
┌──────────────────────────┐
│ File: /tmp/ML123456      │
├──────────────────────────┤
│ Offset 0: (A,X) → 300    │
│ Offset 512: (B,Y) → 200  │
│ Offset 1024: (C,Z) → 300 │
└──────────────────────────┘

접근 속도: 밀리초 (5-10ms)
처리량: ~0.1 GB/s (HDD)

3. GROUP BY의 접근 패턴 (랜덤 I/O vs 순차 I/O)

group by 는 랜덤I/O 패턴을 가짐

 

왜 GROUP BY는 랜덤 I/O인가? → 데이터가 정렬되어 있지 않기 때문

 

141만건을 group by 할 때 각 행마다 임시테이블의 해당 그룹을 랜덤하게 찾아야 하고

디스크에서는 매번 헤드 이동이 필요하다.

 

적고보니 2~3분 걸린거만해도 적게걸린게 아닌가 싶긴하다.

# 회계원장 테이블 실제 저장 순서
일자 | 부서 | 계정
2023-01-15 | C      | Z
2023-01-10 | A      | X
2023-01-20 | B      | Y
2023-01-12 | A      | X  ← A-X가 떨어져 있음
2023-01-18 | C      | Z  ← C-Z도 떨어져 있음


# 임시 테이블 접근

1행 처리: C-Z 그룹 찾기 (임시테이블 offset 5000)
2행 처리: A-X 그룹 찾기 (임시테이블 offset 0)     ← 점프
3행 처리: B-Y 그룹 찾기 (임시테이블 offset 2500)  ← 또 점프
4행 처리: A-X 그룹 찾기 (임시테이블 offset 0)     ← 다시 점프

→ 매 행마다 임시 테이블의 다른 위치로 점프

---
### 메모리 vs 디스크에서 랜덤 I/O

#### 디스크 (HDD)에서 랜덤 I/O

디스크 헤드가 물리적으로 이동해야 함:

1. 현재 위치: Track 100, Sector 50
2. 목표 위치: Track 500, Sector 30
3. 헤드 이동 시간: 5-10ms (Seek Time)
4. 회전 대기 시간: 0-8ms (Rotational Latency)
총 접근 시간: 평균 10ms

141만 건 × 10ms = 14,100초 (약 4시간!)
→ 실제로는 캐싱으로 더 빠르겠지만 여전히 매우 느림


#### 메모리 (RAM)에서 랜덤 I/O

전기 신호로 즉시 접근:

1. 메모리 주소 계산: 0x1A2B3C4D
2. 메모리 컨트롤러가 해당 주소로 즉시 접근
3. 접근 시간: 50-100ns (나노초)

141만 건 × 0.0001ms = 0.14초
→ 랜덤이든 순차든 거의 차이 없음


# 핵심 차이

디스크 랜덤 I/O: 위치 이동이 병목
메모리 랜덤 I/O: 위치 이동 비용이 거의 0


### 실제 성능 차이

141만 건 GROUP BY 처리

디스크 임시 테이블 (랜덤 I/O):
┌────────────────────────────┐
│ 1. 원장 테이블 읽기: 5초     │ (순차 I/O)
│ 2. 임시테이블 업데이트:    │
│    - 141만번 랜덤 접근     │
│    - 각 접근마다 10ms      │
│    - 캐시 적중률 50%       │
│    = 약 28초              │ (랜덤 I/O 병목)
│ 3. 정렬: 8초              │
├────────────────────────────┤
│ 총: 33초                   │
└────────────────────────────┘

메모리 임시 테이블 (랜덤 I/O):
┌────────────────────────────┐
│ 1. 원장 테이블 읽기: 5초     │ (순차 I/O)
│ 2. 임시테이블 업데이트:    │
│    - 141만번 랜덤 접근     │
│    - 각 접근마다 0.0001ms  │
│    = 약 0.14초            │ (랜덤 I/O 영향 없음)
│ 3. 정렬: 6초              │
├────────────────────────────┤
│ 총: 11초                   │
└────────────────────────────┘

개선: 28초 감소 (랜덤 I/O 병목 제거)


### 왜 메모리는 랜덤 I/O에 강한가?

#### HDD (기계식)
┌─────────────────────────┐
│   회전하는 플래터        │
│   ●───────────────●     │
│   │               │     │
│   └─ 헤드가 이동 ─┘     │
└─────────────────────────┘

랜덤 접근 시:
1. 헤드를 목표 트랙으로 이동 (물리적 이동)
2. 플래터가 회전해서 목표 섹터가 올 때까지 대기
3. 데이터 읽기/쓰기

→ 물리적 이동이 병목!

#### SSD (전자식)
┌─────────────────────────┐
│   Flash 메모리 셀        │
│   [][][][][][][]        │
│   [][][][][][][]        │
└─────────────────────────┘

랜덤 접근 시:
1. 컨트롤러가 주소 계산
2. 해당 셀로 전기 신호 전달
3. 데이터 읽기/쓰기

→ 순차보다는 느리지만 HDD보다 훨씬 빠름

#### RAM (메모리)
┌─────────────────────────┐
│   메모리 칩              │
│   [][][][]...           │
│   주소 버스로 직접 접근  │
└─────────────────────────┘

랜덤 접근 시:
1. 메모리 주소 계산 (즉시)
2. 주소 버스로 접근 (즉시)
3. 데이터 읽기/쓰기 (즉시)

→ 순차든 랜덤이든 속도 동일

4. 세션단에서 메모리 증가

- 왜 세션단에서 메모리 증가시켰나?

- (1) 나에게 DB 설정을 변경할 수 있는 권한은 없음

- (2) 설정파일 수정하면 mysql 재시작 필요. 운영중인 고객사 서버라 중단할 수가 없었음

SET SESSION tmp_table_size = 536870912;      -- 512MB
SET SESSION max_heap_table_size = 536870912; -- 512MB
SET SESSION sort_buffer_size = 67108864;     -- 64MB

# 자바에서 쿼리 실행하기전에 위의 쿼리 먼저 실행
Statement stmt = conn.createStatement();

 stmt.execute("SET SESSION tmp_table_size = 536870912");
 stmt.execute("SET SESSION max_heap_table_size = 536870912");
 stmt.execute("SET SESSION sort_buffer_size = 67108864");