서브쿼리를 이용한 조인 쿼리 개선하기(1) - 현재 상황 분석

조인 쿼리 개선하기

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에 간략히 표현했습니다. )

 

조회 쿼리

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만번의 연산이 필요

 

.

.

 

그럼 왜 해당 조인 쿼리가 비효율적인가 ?

 

물론 지금 상태에서는 유저당 point_history 데이터가 별로 없기때문에 조인 연산이 효율적인 것 같지만 , 그렇지 않다.그 이유는 , point_history 데이터가 유저당 100개,1000개,10000개 ...N개가 된다면 3만건의 데이터 * N개 데이터 -> 3만 * N 번의 연산이 필요하기 때문에  해당 조인 쿼리는 비효율적이라고 생각한다..!!

(더 자세한 건 2탄에서 )

 

 

5. 다중 칼럼 인덱스

아니.. 그럼 24만번의 연산이 필요하다는 것은 이해가 됐으나,, 복합키는 어떻게 인덱스를 타는지 궁금하다 !

분명, Point_history 테이블의 PrimaryKey는 복합키로, (oauth_id,created_at,product_name) 3개의 컬럼이 복합된 키이다.

그럼 복합키는 어떻게 인덱스를 탈까? -  RealMysql 책을 찾아봤다.

다중 칼럼 인덱스는 인덱스 내에서 각 칼럼의 순서가 중요하며, 칼럼의 순서에 따라 인덱스 정렬 순서가 결정된다.

즉 총 2개의 칼럼 인덱스라면, 첫번째 칼럼을 정렬한 후,  첫번째 칼럼의 순서에 의존하여 두번째 칼럼이 정렬된다.

 

아래 다중 칼럼 인덱스를 예시로 들어보면 , dept_no를 기준으로 정렬하고, dept_no 순서에 의존하여 emp_no 칼럼이 정렬되고 있는 것을 확인할 수 있다.

출처 : Real Mysql 8.0

 

 

그렇다면 나의 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위여서 비교적 효율적으로 조회할 수 있는 것 같다.

 

 

 

6. 결론 - 현재상황

• 성능테스트 - MTT 는 18초 , TPS는 5.3을 기록
• 기존 쿼리는 조인 연산을 대략 24만번 수행
• 기존 쿼리에서 oauth_id를 기준으로 인덱스 타고 있었다...!

 

지금까진 현재 상황에 대해 분석해 봤고, 개선은 2탄에서 !
https://gani-dev.tistory.com/141?category=1180933

서브쿼리를 이용한 조인 쿼리 개선하기(2) -쿼리 개선기