MySQL 실행계획으로 성능 개선 (1)

Jiumn·2023년 9월 4일
1

MySQL

목록 보기
4/9
post-thumbnail

문제 상황

회사 서비스의 특정 API 호출 시 응답 시간이 12초 이상 소요되는 문제가 발생했다. 고객들이 자주 조회하는 페이지라서 개선이 시급해보였다. 우선 해당 API의 쿼리 문제가 있는지 알아보기 위해 실행 계획을 분석해보기로 했다.

원인 분석

해당 API에서는 총 3개의 쿼리가 SELECT 문으로 조회되고 있었다. 그 중 어떤 쿼리의 응답 속도에 문제가 있는지 알아보기 위해 우선 회사에서 사용 중인 AWS RDS 성능 모니터링 페이지를 확인해봤다.

최근 1시간 동안 상위 SQL을 확인해보니 3개 중 하나의 쿼리가 특히 조회하는 데 오랜 시간이 걸리는 것으로 나타났다. 해당 쿼리의 실행 계획을 분석해보기로 했다.

MySQL 실행 계획 확인

MySQL 실행 계획을 확인하려면 쿼리 문 앞에 EXPLAIN을 붙여주면 된다. 그럼 MySQL 옵티마이저가 해당 쿼리를 가장 효율적으로 실행할 수 있는 실행 계획을 짜서 표 형태로 보여준다.

MySQL 실행 계획 분석

실행 계획을 분석해보는 게 처음이라 먼저 실행 계획 테이블(query plan table)을 보는 방법부터 익혀야 했다.

  • id: 단위 SELECT 쿼리의 실행 순서. 오름차순으로 실행된다. 보통 가장 바깥에 있는 쿼리부터 실행되는데, id가 같은 쿼리들이 여러 개라면 JOIN으로 연결된 경우다.
  • select_type: 각 단위 SELECT 쿼리의 타입. 가장 바깥에 있는 SELECT 문이 PRIMARY다.
  • table: 단위 SELECT의 기준이 되는 테이블. alias를 설정한 경우 alias로 표기된다.
  • type: MySQL 서버가 각 테이블을 어떤 방식으로 읽었는지 표현. 인덱스로 읽었는지, 풀 테이블 스캔으로 읽었는지 알 수 있기 때문에 중요하다.
  • possible_keys: key로 사용될 수 있었던 후보 키들이다. (무시해도 된다.)
  • key: 인덱스로 사용되는 키들. 역시 중요하다.
  • key_len: 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려준다.
  • ref: type이 ref인 경우 표시된다. func로 표시되는 경우 확인해봐야 한다.
  • rows: 해당 실행 계획을 위해 얼마나 많은 레코드를 읽어야 하는지 통계상으로 예측한 수치다. 실제로 반환되는 값과 너무 많이 차이가 나면 좋지 않다.
  • filtered: rows에서 값을 반환하기 위해 반환되는 데 필터링된 비율이다. 단위는 %다. 따라서 숫자가 작을수록 효율이 좋지 않다.
  • Extra: 쿼리 실행 방식에 대한 추가적인 정보. 이름과 달리 성능 개선에 중요한 정보를 담고 있다.

위 내용을 바탕으로 해당 쿼리의 실행 계획을 확인해본 결과, 일부 실행계획에 문제가 있는 것으로 예상이 되었다.

문제가 된 쿼리의 실행 계획 정보

  • type: ALL (인덱스 없이 Full table scan 발생)
  • rows: 243029개 (최종 반환된 row가 16개인 것에 비하면 매우 높은 수치)
  • filtered: 1
  • Extra: Using temporary (임시 테이블이 사용된 것)

id 값에 기반해 해당하는 SELECT 쿼리를 찾아보니 다음과 같았다. (컬럼과 테이블 명은 임의로 수정)

WHERE idx IN (
			  SELECT 
			    MAX(idx) AS idx 
			  FROM table_name 
			  WHERE is_deleted = 'N' 
			  AND d = 1
			  GROUP BY a, b, c
			  HAVING c IN (1, 2, 3, 4)
			 )

GROUP BY와 HAVING 절로 인해 실행 속도가 느려진 것으로 보였다.

해결

쿼리를 확인해보니 HAVING 절을 사용한 부분이 성능을 저하시키는 것 같아 WHERE 절로 바꿔봤는데 결과가 동일하게 나타났다. (모든 HAVING 절을 WHERE 절로 대체할 수 있는 것은 아니다. 해당 쿼리의 경우 WHERE 절로 변경해도 무방했기 때문에 변경한 것)

그래서 다음과 같이 HAVING 절을 삭제하고 WHERE 절의 AND 조건으로 변경했다.

WHERE idx IN (
			  SELECT 
			    MAX(idx) AS idx 
			  FROM table_name 
			  WHERE is_deleted = 'N' 
			  AND d = 1
              AND c IN (1, 2, 3, 4)
			  GROUP BY a, b, c
			 )

쿼리 수정 후 조회 시

응답 속도가 약 12s → 약 84ms로 개선되었다.

쿼리 수정 후 실행계획 확인 시

  • type: ALL → range로 변경됨
  • rows: 243029 → 818로 변경됨
  • Extra: Using where; Using temporary → Using index condition; Using where; Using temporary 로 변경됨.

결과적으로 HAVING 절의 컬림이 WHERE 절로 가면서 인덱스를 타게 되었다.
다른 방법으로는 GROUP BY 절에 해당하는 컬럼을 인덱스로 만드는 방법도 있을 것 같다.

첫 실행 계획 분석 완료!

profile
Back-End Wep Developer. 꾸준함이 능력이다. Node.js, React.js를 주로 다룹니다.

0개의 댓글