[MySQL] 슬로우 쿼리 튜닝 회고

슬로우 쿼리 발생

내가 운영하던 그룹웨어 게시판 서비스에는 '최근 게시글'이라는 특수한 게시판이 존재했다. 

 

보통 게시글은 사용자가 등록한 게시판에 접근해서 조회하지만, 해당 게시판은 모든 게시판의 게시글을 등록 순으로 보여주는 게시판이었다.

 

내가 막 게시판 서비스를 맡게 되었을 때 전임자 분께서 "해당 API가 속도가 안나오니 언젠간 튜닝을 해야 할거에요"라는 말을 하셨는데, 그 때 속도 체크 해봤을 때는 2~3초 나와서 아직 튜닝할 때는 아니라고 판단하고 냅두고 있었다. 

 

그런데 어느날 인프라 팀에서 특정 고객사에서 해당 게시판의 게시글 리스트를 조회할 때 속도가 너무 오래걸린다는 안내를 받았고, 해당 고객사에 들어가서 확인해보니 API 속도가 약 7초 가량 소요되고 있었다.

 

속도 저하의 원인이 되었던 곳은 '게시글 리스트 조회' 쿼리였고, 다른 API에서도 공통으로 많이 사용하는 게시판 메인 쿼리였다.

 

잘못 수정하면 다른 API가 전부 이슈가 터지는 참사가 일어날 수 있어서 되도록 안건들이고 싶었던 쿼리였지만...  7초나 소요되는 것을 보고 심각성을 느껴 바로 쿼리 튜닝을 시작하게 되었다.

 

쿼리 튜닝 방법 선택

서두에서도 말했듯 문제가 되었던 쿼리가 게시판 시스템의 메인 쿼리였기 때문에 매우 길고 복잡했다. 

 

그리고 SI 프로젝트 특성 상 여러 개발자가 거쳐간 쿼리다보니 기준이 없었고, 불필요한 Join을 추가한 부분이나 무분별한 서브쿼리 등의 문제가 있었다. 

 

그래서 현재 상황에서는 인덱스를 적용하는 것 보다는, 위의 문제가 되는 쿼리를 정리하는것 만으로 속도가 빨라질 것이라고 생각했고 다음과 같이 쿼리 튜닝 계획을 설정했다. 

 

 

1. 쿼리 및 API 분석

2. 불필요한 Join 삭제

3. 불필요한 LEFT JOIN은 INNER JOIN으로 변경

4. 불필요한 서브쿼리 삭제 (이미 Join 되어 있는 케이스)

5. Mybatis 동적 쿼리 적용

6. 쿼리 실행 순서 변경

 

 

쿼리 튜닝  진행

Join 방법 변경

1. 불필요한 LEFT JOIN 은 INNER JOIN으로 바꾸기 

첫번째로 진행한 것은 불필요한 LEFT JOIN을 INNER JOIN으로 변경한 것이다 

 

아래의 예시와 같이 LEFT JOIN을 하는 경우 A 테이블 (driving table) 기준 FULL SCAN을 하게 된다. 

 

B 테이블 (inner table) 에 id 값으로 존재하지 않는 A테이블의 레코드도 조회되기 때문에 INNER JOIN에 비해 속도가 느리다. 

select A.* from A LEFT JOIN B ON B.id = A.id

 

그래서 보통 A 테이블의 키값 기준으로 B 테이블의 레코드를 가져오려 할 때, B 테이블에 데이터가 없을 수도 있는 구조로 설계되어 있다고 하면 LEFT JOIN을 사용하지만 

 

두개의 테이블이 무조건 매칭되는 구조라면 INNER JOIN을 사용하는 것이 좋다.

 

내가 수정하게 된 쿼리에도 위와 같은 JOIN 방법이 많았다. 

 

대표적으로 게시판 테이블과 게시글 테이블을 LEFT JOIN 하는 경우가 있었는데, 테이블 구조상 무조건 매칭되는 구조였기 때문에 INNER JOIN으로 변경했다.

 

2. 쿼리 분리 

쿼리 JOIN 절에 게시판의 트리구조 정보를 조회하기 위해 LEFT JOIN이 걸려 있는 부분이 있었다.

(특정 게시판 유형에서는 트리구조 정보가 없기 때문에 INNER JOIN이 아닌 LEFT JOIN을 사용하고 있었다)

 

그냥 넘어갈까 했지만 속도가 지연에 가장 큰 비중을 차지하는 부분이었기 때문에 어떤 방식으로라도 개선이 필요했고 

 

고민 끝에 내린 결론으로는 쿼리를 2개로 분리하기로 결정했다. 

 

SELECT 절은 대부분 비슷했기 때문에 Mybatis 기능인 <sql> <include> 문을 사용해서 하나로 관리하기로 했고

 

트리구조 정보가 없는 게시판을 조회하는 쿼리는 LEFT JOIN을, 그 외에는 INNER JOIN을 사용하도록 했다.

 

이 작업을 수행하고 속도를 체크해보니 3초 이상 단축된 것을 확인했다.

 

서브쿼리 최소화

1. 서브쿼리 최소화 불필요한 서브쿼리 제거

쿼리가 너무 긴 탓인지 아래 예시와 같이 JOIN절에 있는 테이블을 SELECT 절에서 서브쿼리로 조회하고 있는 부분이 있었다.

SELECT A.id, (select name from B where A.id=B.id) as name 
FROM A
JOIN B on A.id = B.id

이러한 서브쿼리들은 꼼꼼하게 확인을 한 뒤 필요없는 경우 제거를 해주었다.

 

2. 서브쿼리로 자주 사용되는 테이블 JOIN 절로 빼기 

이런 케이스 외에 JOIN 절에는 없지만 서브쿼리에서 빈번하게 사용되고 있는 테이블이 몇개 있었다 

SELECT (SELECT name FROM EMP WHERE EMP.user_id = A.user_id) as creator_name 
     , (SELECT address FROM EMP WHERE EMP.user_id = A.user_id) as creator_address 
FROM A

 

이런 테이블은 서브쿼리 대신 JOIN으로 조회하도록 수정했다. 

SELECT EMP.name as creator_name 
     , EMP.address as creator_address 
FROM A
LEFT JOIN EMP ON A.user_id = B.user_id

 

ETC

1. Mybatis 동적 쿼리 사용

쿼리 내에 특정 상황에만 사용되는 SELECT절과 JOIN 절이 있었다.  

 

예를 들어 관리자가 게시물 목록을 조회하는 경우에는 권한 테이블을 JOIN하지 않아도 됐고, 일부 SELECT절의 컬럼도 사용하지 않고 있었다.

 

이런 케이스에는 Mybatis의 동적쿼리 (if문, choose문)을 사용해서 쿼리 자체를 수행하지 않도록 개선했다.

 

2. 쿼리 실행 순서 변경 

JOIN한 테이블에 대한 조건을 WHERE절에서 체크하고 있는 부분이 있었다.

SELECT A.* 
FROM A 
JOIN EMP ON A.user_id = EMP.user_id
WHERE EMP.use_yn = 'Y'

 

SQL문의 쿼리 실행 순서는 WHERE 절 보다 JOIN절이 먼저 실행된다. 

그렇기 때문에 JOIN한 테이블에 대한 조건은 WHERE 절 보다는 JOIN 절에 작성하도록 변경했다. 

SELECT A.* 
FROM A 
JOIN EMP ON A.user_id = EMP.user_id AND EMP.use_yn = 'Y'

 

아래의 SQL문 실행 순서는 아래의 이미지를 참고하면 좋을 것 같다.

 

 

결론

많은 개발자가 거쳐가면서 누적되기만 했던 한방쿼리를 2~3개월이라는 꽤 오랜 시간을 투자해서 쿼리 튜닝을 해봤다. 

 

불필요한 부분은 걷어내고 중구난방이었던 쿼리를 개선해보니 실행 속도가 7초 -> 3초 이내로 감소되는 것을 경험했다. 

 

이렇게 쿼리 튜닝을 하면서 빠르게 문제를 해결 하는것도 중요하지만, 어느정도 시스템의 확장성과 유지보수성을 고려하면서 짜야 되겠다 라는 교훈을 얻었다.