회원권 거래 관리 시스템의 검색은 운영에서 60초가 걸렸습니다. 인덱스와 쿼리를 고치고 서버 메모리를 늘린 뒤 3초로 줄었습니다. 다만 두 조치를 함께 적용해서, 어느 쪽 효과였는지는 운영에서 나누지 못했습니다. 그래서 나중에 로컬 MySQL에 비슷한 환경을 만들어 조치를 한 가지씩 바꿔 가며 다시 쟀습니다. 재현에서는 서브쿼리가 찾는 거래 테이블의 인덱스가 시간을 거의 다 줄였고(카운트 약 105초에서 약 23 ms), 버퍼 풀 크기는 뚜렷한 차이를 만들지 못했습니다.
운영에서 한 일
기존 프로그램을 웹으로 옮긴 뒤, 검색 화면이 결과를 보여 주는 데 60초가 걸렸습니다. 직원이 검색할 때마다 1분을 기다려야 했습니다.
제가 적용한 조치는 두 가지입니다.
- 인덱스와 쿼리 수정: 복합 인덱스를 넣고 쿼리를 고쳤습니다.
- 서버 메모리 증설: 서버 메모리를 늘렸습니다.
두 조치를 적용한 뒤 검색은 3초로 줄었습니다. 60초와 3초는 운영 화면에서 본 API 호출 시간이고, 데이터 건수와 서버 사양 같은 측정 조건은 기록하지 않았습니다.
로컬 재현: 어느 조치의 효과였나
방법
운영 데이터는 가져올 수 없어서, 검색 쿼리에 나오는 테이블과 컬럼 이름으로 합성 스키마와 데이터를 만들었습니다. 그다음 조건 컬럼 인덱스, 서브쿼리용 인덱스, 힌트 유무, 카운트 분리, 버퍼 풀 크기를 한 번에 한 가지씩 바꿔 가며 목록과 카운트의 시간을 쟀습니다.
테스트 조건
실제 DDL 덤프가 없어서 타입, 인덱스, 데이터 분포는 가정입니다.
- 환경: Docker Desktop(VM 메모리 약 4.8 GiB), MySQL 8.4, 컨테이너 메모리 2 GiB 제한
- 데이터: 고객 마스터 5만 건, 고객 소유자 5만 건, 거래 테이블 30만 건(고정 시드)
- 측정: 서버가 잰 실행 시간. 질의마다 먼저 한 번 실행해 그 시간은 버리고(데이터를 메모리로 올리는 준비 비용 제외), 그다음 3회를 재서 중앙값을 냅니다. 같은 조건에서 다시 실행하면 기준선 카운트가 약 7% 달랐습니다.
검색 쿼리의 구조
검색 화면이 실행하는 질의와, 그 안에서 도는 서브쿼리는 다음과 같습니다.
- 목록: 화면에 보이는 50행의 내용을 가져오는 질의입니다. 고객 이름, 회사, 담당 직원별 거래 건수처럼 행마다 필요한 값을 고르고, 앞 50행만 받습니다.
- 카운트: 검색 조건에 맞는 전체 건수를 세는 질의입니다. 서비스 코드는 1페이지를 열 때만 카운트를 실행합니다.
- 카운트2: 목록과 카운트 안에서 고객 한 명마다 거래 건수를 세는 서브쿼리입니다. 담당 직원의 거래 건수를 세는 칸과 다른 샵(SHOP)의 거래 건수를 세는 칸, 두 개가 있습니다. 행마다 한 번씩 실행되므로, 조건에 맞는 고객이 많을수록 거래 테이블을 찾는 횟수도 늘어납니다.
목록은 50행만 돌려주지만, 카운트는 조건에 맞는 모든 행에서 카운트2를 실행하기 때문에 시간 차이가 큽니다.
측정 결과
바꿔 본 인덱스는 두 종류입니다.
- 조건 컬럼 인덱스: 고객 테이블의 미사용 여부, 구분, 변경일 세 열을 묶은 복합 인덱스입니다. 목록과 카운트의 검색 조건에 쓰입니다.
- 서브쿼리용 인덱스: 거래 테이블의 고객 번호와 직원 코드 두 열을 묶은 복합 인덱스입니다. 카운트2가 고객 번호와 직원 코드로 거래를 찾기 때문에, 이 인덱스가 있으면 해당 거래만 바로 찾습니다.
아래 표의 각 행은 윗줄까지의 변경을 모두 적용한 상태에서 잰 값입니다. 버퍼 풀 두 행은 처음 잰 뒤에 다시 잰 값입니다(순서를 번갈아 3라운드, 각 9회의 중앙값). 힌트가 새 인덱스의 사용을 막는지 보려고 힌트 유지와 제거를 나눠 쟀습니다. 힌트 유지는 목록 질의에 USE INDEX (PRIMARY)를 넣어 기본키 인덱스만 쓰도록 지정한 상태이고, 힌트 제거는 이 구문을 뺀 상태입니다.
| 조건 | 목록 · 힌트 유지 | 목록 · 힌트 제거 | 카운트 · 힌트 유지 | 카운트 · 힌트 제거 |
|---|---|---|---|---|
| 기준선 (인덱스는 기본키뿐) | 2,492 ms | 2,522 ms | 105,581 ms | 105,474 ms |
| 조건 컬럼 인덱스 추가 | 2,605 ms | 2,628 ms | 106,030 ms | 107,034 ms |
| 서브쿼리용 인덱스 추가 | 1.0 ms | 1.4 ms | 24.3 ms | 22.7 ms |
| 서브쿼리용 인덱스 + 버퍼 풀 16 MB | 1.0 ms | 1.3 ms | 24.6 ms | 22.5 ms |
| 서브쿼리용 인덱스 + 버퍼 풀 1 GB | 1.0 ms | 1.3 ms | 24.5 ms | 21.5 ms |
| 서브쿼리용 인덱스 + 서브쿼리 제거 | 해당 없음 | 해당 없음 | 해당 없음 | 15.4 ms |
1페이지를 열면 목록과 카운트가 함께 돌기 때문에, 기준선에서 두 질의를 합치면 약 108초입니다.
이 숫자에서 읽을 수 있는 것은 다음과 같습니다.
- 기준선에서 목록은 약 2.5초, 카운트는 약 105초였습니다. 두 질의의 시간은 대부분 서브쿼리에서 나왔습니다. 실행 계획에서 서브쿼리는 거래 테이블 약 30만 행을 풀스캔하고 있었습니다.
- 조건 컬럼 인덱스는 실제로 쓰였습니다(옵티마이저 추정 약 9,800행). 읽는 행 수는 줄었지만, 행마다 실행되는 서브쿼리는 그대로 남아 있었습니다. 그래서 힌트를 뺀 경우에도 시간은 거의 줄지 않았습니다.
- 서브쿼리용 인덱스를 넣자 목록은 1.0에서 1.4 ms, 카운트는 22.7에서 24.3 ms가 됐습니다. 행 수가 같아도 행 하나를 찾는 비용이 풀스캔에서 인덱스 조회로 바뀌면서 시간이 크게 줄었습니다.
- 서브쿼리를 뺀 카운트는 15.4 ms였습니다. 다만 이 값은 서브쿼리용 인덱스가 있는 상태에서 잰 것이라, 인덱스 없이 서브쿼리만 뺀 경우와는 비교할 수 없습니다.
- 버퍼 풀을 16 MB와 1 GB로 바꿔도 목록과 카운트 모두 차이가 1 ms 안팎이었습니다. 처음 잰 값에서 보였던 힌트 유지 카운트의 차이(16 MB 38.8 ms, 1 GB 24.5 ms)는 다시 잰 값에서 나타나지 않았습니다.
결과와 한계
운영에서는 검색이 60초에서 3초로 줄었습니다. 재현에서는 서브쿼리용 인덱스 하나가 카운트를 약 105초에서 약 23 ms로 줄였습니다. 버퍼 풀 크기(16 MB와 1 GB)에 따른 차이는 1 ms 안팎이었습니다. 처음 잰 값에서는 힌트를 유지한 카운트가 16 MB에서 느렸지만, 다시 잰 값에서는 같았습니다.
한계는 다음과 같습니다.
- 재현은 합성 데이터로 했습니다. 타입, 인덱스, 데이터 분포가 가정이라서, 운영에서도 같은 비율로 줄었다고 말할 수 없습니다.
- 운영의 3초는 브라우저에서 본 API 호출 시간이고, 재현의 23 ms는 DB 서버가 잰 질의 시간입니다. 데이터 건수와 서버 환경도 달라서 두 값을 그대로 비교할 수 없습니다.
- 버퍼 풀 크기에 따른 차이가 1 ms 안팎이어서, 이 실험으로는 메모리 효과가 드러나지 않았습니다. 운영에서 메모리 증설이 얼마나 기여했는지는 이 실험으로 알 수 없습니다.
배운 점
- 조치를 한꺼번에 적용하면 무엇이 효과였는지 남지 않습니다. 운영에서도 한 가지씩 적용하고 재야 했습니다.
- 조건 컬럼에 인덱스를 넣어도, 행마다 도는 서브쿼리가 남아 있으면 시간은 줄지 않습니다. 실행 계획에서 가장 많이 반복되는 부분을 먼저 봐야 합니다.