dev/solution

[MySQL] 슬로우 쿼리 개선 (UNION ALL, 인덱스)

오이호박참외 2024. 10. 15. 19:00

🐢 슬로우 쿼리

DBMS 가 클라이언트로부터 요청받은 쿼리를 수행할 때 일정 시간 이상 수행되지 못한 쿼리를 의미한다.

길어야 1-2초 걸리는 DB 쿼리가 예상보다 오래 걸리는 경우를 말한다.

🌪️ 슬로우쿼리 개선 필요성

운영서버에서 특정 쿼리 실행 시 5.7초, 8.1초 소요되는 현상 발생하고 있었다. 따라서, 해당 쿼리 개선이 필요했다.

특정 사용자와 관련된 문서 데이터는 95,244건이 존재했고, 해당 사용자(5.7초) 기준으로 쿼리를 분석했다.

🧐 실행 계획 분석

📉 기존 쿼리

EXPLAIN 기존 쿼리

1) #5의 경우, 불필요하게 임시테이블을 만들어 사용하고 있어 제거 필요

2) #7, #8, #9를 보면 UNION을 사용하고 있음 → UNION ALL 사용으로 변경

  • 중복 제거 과정 생략
    • UNION은 결과 집합에서 중복된 행을 제거하기 위해 추가적인 오퍼레이션을 수행하지만 
    • UNION ALL은 중복 제거를 하지 않고 모든 결과를 그대로 반환함.
    • group by 로 중복 제거하는 부분이 존재
  • 리소스 절약
    • 중복 제거를 위한 정렬이나 해시 테이블 생성 등의 과정이 필요 없으므로 CPU와 메모리 사용량이 줄어듦

📈 수정 쿼리

Using temporary; Using filesort 발생

  • group by 사용으로 임시 테이블 사용하기 때문에 발생
  • tebe._id 컬럼에 인덱스 추가로 해결 가능

ALL 스캔

  • T_EAP_BOX_EMP 테이블의 seq01, seq02, org03, seq04 컬럼에 인덱스 추가로 해결 가능

📊 인덱스 추가 이슈

인덱스 추가로 슬로우 쿼리 개선이 가능하나,

  • 쓰기 작업마다 인덱스를 재구성해야 하므로 쓰기 작업의 속도가 느려질 수 있다.
    • 특히 tebe1 테이블은 데이터 변경이 빈번하게 발생하는 테이블이기 때문에 이러한 영향이 더 클 수 있다.
  • 이전에 잘 작동하던 쿼리가 느려질 수 있다.
  • 새로운 인덱스가 추가되면 쿼리 최적화기가 인덱스를 선택하는 방식이 변경될 수 있으며, 복잡한 쿼리에서는 여러 인덱스를 조합하여 사용할 때 인덱스의 선택이 최적이 아닐 경우 쿼리 성능이 저하될 수 있다.

🧪 인덱스 추가 테스트

실제 인덱스를 추가하여 쿼리 실행 속도를 확인해 보고자
개발서버에서 tebe01 테이블 내 데이터량이 많은 사용자(2,126,539건) 기준으로 테스트를 진행했다.

  • idx_04 (tebe01 테이블의 seq01, seq02, org03, seq04 컬럼에 인덱스 추가) 인덱스가 적용되는 경우 확실히 rows의 수 차이가 있는 것을 볼 수 있다.
  • 쿼리 처리 시 스캔해야할 데이터의 양이 줄어들어 더 빠르게 검색할 수 있다.

인덱스 추가한 경우와 쿼리 속도 비교

하지만 해당 서버에서 실제 인덱스를 추가했을 때와 제거했을 때 쿼리를 실행해 보면 각각 122ms, 113ms와 같이 거의 비슷한 속도로 실행되고 있다.

캐시 삭제

캐시 이슈로 생각되어 캐시 삭제해보았지만 동일한 결과를 나타낸다.

🏥 인덱스 추가 없이 개선

앞서 언급한 인덱스 추가 이슈(기존 운영되던 작업들의 성능 저하 발생 이슈)로 인해 인덱스 추가 없이 개선을 진행했다.

인덱스 추가 없이 개선

95,244건 데이터 기준 2.979초 → 2.687초 

실질적인 성능 향상을 위해서는 인덱스 추가 방법이 필요할 것으로 판단된다.

🤔 슬로우 쿼리 개선 후기

실질적으로 슬로우쿼리가 개선되어 큰 성능향상의 결과로 이어지지는 못했으나, 이 과정에서 여러 가지를 배우고 느꼈다. 슬로우 쿼리 문제를 해결하기 위해 진행한 분석과 최적화 과정은 단순히 속도를 개선하는 것 이상의 깊은 이해를 요구했다.

1. 쿼리 최적화의 중요성

처음에 쿼리를 분석하면서, 어떻게 쿼리의 실행 계획을 읽고 이해해야 하는지 배웠다. EXPLAN을 통해 얻은 정보를 바탕으로 불필요한 임시 테이블 사용과 UNION의 대체를 고려하는 과정은 쿼리 성능을 개선하는 데 매우 중요하다는 것을 깨달았다. 쿼리 최적화는 단순히 성능을 높이는 것이 아니라, 시스템 자원을 효율적으로 사용하는 방법이라는 점도 명확히 인식하게 되었다.

2. 인덱스의 역할과 한계

인덱스 추가가 쿼리 성능에 미치는 영향은 매우 크지만, 동시에 인덱스가 가져오는 부작용도 고려해야 한다는 점을 배웠다. 쓰기 작업이 빈번한 테이블에 인덱스를 추가하면 오히려 성능 저하를 초래할 수 있다는 사실은 매우 중요한 교훈이었다. 따라서 인덱스를 추가할 때는 해당 테이블의 사용 패턴을 충분히 분석하고, 인덱스가 실제로 필요한지 여부를 신중히 결정해야 한다.

쿼리 최적화는 단순한 기술적 작업이 아니라, 시스템 전반에 걸쳐 영향을 미치는 중요한 작업인 것 같다.