읽기 전
이 글은 문제를 처음 해결해본 내용이라 부족한 점이 많습니다. 예전의 제가 이렇게 문제를 해결하려 했구나 정도로 봐주시고, 마지막에 개선이 필요한 부분을 정리해 두었으니 참고하며 읽어주시면 좋겠습니다.
조인 쿼리 개선하기
1. 현재 상황
'당장' 서비스에는 유저 포인트 관리를 위해, 모든 유저의 포인트를 조회하는 API가 있는데, 속도가 너무 느려 개선해보려고 한다.
ERD
아래 ERD에서 User 테이블과 PointHistory 테이블을 확인해 보자
User 테이블에는 사용자 정보가 저장되어 있고, PointHsitory에는 포인트 적립 및 사용 내역이 기록되고 있다.
예를 들어, oauth_id가 1인 유저가 혈당을 기록하여 200 포인트를 적립 받았다면, PointHistory 테이블에는
oauth_id = 1, 포인트 유형(Product_name) = '혈당 기록' , 날짜시간(created_at) = '2024-08-25 11:26:23' , 반영된 포인트 (change_point) = 200
- User 테이블 : 사용자의 정보를 관리하는 테이블
- PointHistory 테이블 : 모든 사용자가 포인트를 적립하고 사용한 내역을 관리하는 테이블
( 글에서 필요한 부분만 ERD에 간략히 표현했습니다, 데드락 해결 과정에서 개선 전의 ERD입니다.)
조회 쿼리
PointHistory 테이블에서, 유저별 포인트 사용 금액을 계산하여 , 모든 유저의 최종 포인트를 구하는 쿼리이다.
모든 유저의 최종 포인트를 구해야하기 때문에, User 테이블과 PointHistory 테이블을 oauth_id를 기준으로 join한다.
Group by oauth_id로 유저별 그룹으로 묶고 , PointHistory 테이블의 SUM 함수를 이용해서 유저의 포인트 적립 및 사용 내역을 계산한다.
PointHisotry 테이블만으로도 구할 수 있지 않을까 고민했지만, 포인트에 대한 내역이 하나도 없는 유저가 존재할 수 있다.
또한 모든 유저를 기준으로 결과값을 구해야하기 때문에 User 테이블 기준으로 join하여 계산한다.
@Query("select u.oauthId , COALESCE(SUM(p.changePoint), 0) as totalPoint from users u left join PointHistory p on u.oauthId=p.user.oauthId group by u.oauthId")
List<Map<String, Object>> findAllUserPoint();
User 테이블 데이터 예시
PointHistory 테이블 데이터 예시
2. 기존 쿼리 성능 테스트
Vuser가 99명일 때 , 모든 유저의 포인트를 조회하는 API를 테스트해보자
User 테이블에는 3만건의 데이터가 있고, PointHistory 테이블에는 24만건의 데이터가 있다.
테스트 결과 , 1444번의 요청 중 3번의 에러가 있었고, MTT 는 18초 , TPS는 5.3을 기록했다.
3번의 에러는 "쓰레드 풀 부족" 문제였다. DB를 조회하는데 시간이 오래 걸려, 더 이상 생성할 수 있는 커넥션이 존재하지 않았다.
DB를 조회하는데 왜 시간이 오래걸릴까? 쿼리를 뜯어보자
3. 기존 쿼리 분석
user 테이블과 point_history 테이블조인 쿼리
explain select u.oauth_id , COALESCE(SUM(p.change_point), 0) as totalPoint from users u left join point_history p on u.oauth_id=p.oauth_id group by u.oauth_id;
위의 쿼리는 oauth_id를 기준으로 조인한 후 , SUM 집약 함수로 집계한다.
실행 계획을 확인해보자 !
User 테이블은 PK 값인 oauth_id 컬럼으로 인덱스를 탄다.
Point_history 테이블은 ref 타입으로 탐색되는데 , ref는 조인의 순서와 인덱스의 종류에 관계없이 동등 조건으로 검색한다.
또한 Point_history 테이블은 데이터가 24만건이나 되는데 , rows가 8인 이유는,, 나의 더미데이터 때문이다..
rows 값은 'MySQL 옵티마이저가 각 테이블 또는 조인 조건에 대해 읽을 것으로 예상하는 행의 수'를 의미하는데 , 내가 point_history 테이블 더미데이터를 한 유저당 8개 정도의 데이터를 갖게끔 구성했기 때문이다... 똑똑한...Mysql...큼...
4. 조인 연산 횟수 예측
그럼 , 여기서 한가지 의문이 들었다.
(User)3만 데이터 * (Point_History)24만 데이터를 조인하게 된다면 얼마나 많은 연산이 필요할까 ?
-> 실행계획대로라면 대략 24만(3만 데이터 * 8)번의 연산이 필요하다. -> 비효율적
오잉?! 24만번의 연산이 필요하다는 것도 이해가 안되지만 왜 효율적이지 않다는 것인지 이해가 안된다 ..!
그럼 우선 24만번의 연산이 왜 필요한지부터 이해해보자 !
당연히 조인 비교할 때, Outer Loop에서 User 테이블의 1개의 데이터마다 Inner Loop의 24만개의 데이터를 다 비교해야하는거 아닌가 ?? 그렇다면 3만 * 24만 -> 72만번의 연산이 필요한게 아닌가 ?! 라고 생각했다..!
하지만 우리의 Mysql은 똑똑했지..
Inner Loop에서 전체 테이블을 전부 탐색하지 않고, 인덱스를 통해 조건에 맞는 행을 효율적으로 찾기때문에, full scan이 필요하지 않았던것 ! 아차차... 위의 실행계획을 보면 PRIMARY키 인덱스를 사용하여 User.OAUTH_ID 값을 참조하는 것을 확인할 수 있다..
즉 3만건의 데이터 * 8개의 데이터 => 24만번의 연산이 필요
5. 쿼리 개선하기
문제점
현재 조인 쿼리는 아래와 같이 동작한다.
아래와 같이 N과 M을 정의해보자.
N : 외부 테이블 (User) 의 데이터 크기
M : 내부 테이블 (Point_history) 의 조인 조건에 일치하는 데이터 크기
동작 순서
1. N * M 번의 조인 연산 진행
2. Group by 로 그룹을 묶음
3. COALESCE(SUM(p.change_point), 0) 집계함수 실행
위 과정의 문제점은 조인하는데 많은 연산이 필요하다는 것이다.
예를 들어 100만명의 유저가, 각자 100개씩의 포인트 내역이 있다면 1억번의 연산이 필요하다. N과 M이 늘어나면 더 늘어날 것...
그렇기 때문에, 조인 연산 비용을 줄이는 방법을 고민했다.
조인되는 내부 테이블의 데이터 수를 줄이기
조인 연산을 줄이려면 어떻게 해야할까 ?
N 또는 M의 크기가 작아야한다.
N은 모든 유저의 수이기 때문에, 크기를 줄일 순 없다. 그렇다면 M을 줄여보자.
어차피, point_history 테이블은 SUM 집계함수가 필수인데, 미리 처리하고 join하면 안될까 ?
서브쿼리를 이용해서, 조인을 시도해보자
select u.oauth_id, COALESCE(p.total_point, 0) as total_point
from Users u left join (
select oauth_id, SUM(change_point) as total_point
from POINT_HISTORY
group by oauth_id
) p
on u.oauth_id = p.oauth_id;
서브쿼리로 유저별 포인트 금액을 그룹화하고, sum 집계함수를 실행했다. 실행 계획을 확인해 보자
실행계획을 확인해 보니, User 테이블에서 인덱스가 oauth_id가 아닌 Nickname으로 타서, Hint를 사용했다.
select u.oauth_id, COALESCE(p.total_point, 0) as total_point
from Users u USE INDEX(PRIMARY) left join (
select oauth_id, SUM(change_point) as total_point
from POINT_HISTORY
group by oauth_id
) p
on u.oauth_id = p.oauth_id;
결과
6. 성능 테스트 결과
이전 테스트와 동일한 설정에서 MTT 가 18초에서 9초로 줄었다 ! 그만큼 TPS도 2배 늘었다.
나머지 9초의 원인은 CPU가 100퍼센트를 찍으면서 발생한 속도 저하 문제였습니다.
7. 문제 해결 과정에서의 개선점
위에서 말씀드렸듯이, 해당 문제 해결 과정에는 빈틈이 많습니다..ㅎ
1) 목적 없는 부하 테스트
해당 API는 사용자의 포인트 상황을 관리하는 용도로, 별도의 모니터링 서비스가 없는 이상 부하 테스트는 큰 의미가 없다고 생각합니다. 또한, 부하 규모를 결정하는 기준도 없었고, 명확한 목적 없이 진행되었습니다.
2) 서브쿼리로 인한 임시 테이블 생성
서브쿼리를 사용하여 내부 테이블 크기를 줄이고 `JOIN` 연산 횟수를 줄이는 효과는 있었지만, 그 과정에서 임시 테이블이 생성되었습니다. 데이터 크기가 커지면 디스크를 사용하게 되어 오히려 성능이 저하될 수 있기 때문에, 기존 쿼리가 더 나은 선택일 수도 있습니다.
3) 잘못된 DB 설계
매번 point_history 테이블에서 SUM 연산을 통해 최종 포인트를 계산하는 방식은 비효율적입니다. 시간이 지날수록 포인트 사용 기록이 계속 쌓이면서 연산 비용이 증가하기 때문입니다. 따라서, 최종 포인트 값을 저장하는 별도의 테이블을 두고, 특정 이벤트 발생 시 이를 업데이트하는 방식이 더 적절해 보입니다.
4) 페이징 처리
한번에 모든 유저의 최종 포인트를 조회하기 보단, 페이징을 이용하여 특정 유저수만큼 조회하는 방법이 더 효율적일 것 같습니다.
+) 다중 칼럼 인덱스
분명, Point_history 테이블의 PrimaryKey는 복합키로, (oauth_id,created_at,product_name) 3개의 컬럼이 복합된 키이다.
그럼 복합키는 어떻게 인덱스를 탈까? - RealMysql 책을 찾아봤다.
다중 칼럼 인덱스는 인덱스 내에서 각 칼럼의 순서가 중요하며, 칼럼의 순서에 따라 인덱스 정렬 순서가 결정된다.
즉 총 2개의 칼럼 인덱스라면, 첫번째 칼럼을 정렬한 후, 첫번째 칼럼의 순서에 의존하여 두번째 칼럼이 정렬된다.
아래 다중 칼럼 인덱스를 예시로 들어보면 , dept_no를 기준으로 정렬하고, dept_no 순서에 의존하여 emp_no 칼럼이 정렬되고 있는 것을 확인할 수 있다.
그렇다면 나의 Point_history 테이블의 Primary 키 인덱스의 순서는 어떻게 되어 있을까 ?
아래 명령어를 통해 원하는 테이블의 인덱스 정보를 확인할 수 있다.
SHOW INDEX FROM point_history;
결과
역시 테이블 생성시 , PK를 선언한 순으로 인덱스가 정의되었다.
CREATE TABLE `POINT_HISTORY`
(
`OAUTH_ID` varchar(50) NOT NULL,
`CREATED_AT` dateTime NOT NULL,
`PRODUCT_NAME` varchar(255) NOT NULL,
`CHANGE_POINT` int,
PRIMARY KEY (`OAUTH_ID`, `CREATED_AT`, `PRODUCT_NAME`),
FOREIGN KEY (`OAUTH_ID`) REFERENCES USERS (`OAUTH_ID`) ON DELETE CASCADE,
FOREIGN KEY (`PRODUCT_NAME`) REFERENCES POINT_PRODUCT (`PRODUCT_NAME`)
);
다행히 , oauth_id 컬럼이 인덱스 순서 1위여서 비교적 효율적으로 조회할 수 있는 것 같다.
'당장 프로젝트' 카테고리의 다른 글
| 연속 요청으로 인한 데드락 문제 해결하기 (1) | 2024.07.24 |
|---|