📻 SQL 성능 튜닝 - (2) 실행계획을 분석해 성능 튜닝을 해보자!

지난 1편에서 성능튜닝을 왜 해야하는지와 실행계획을 분석하는 방법에 대해서 살펴보았다.

실행계획의 정보를 보면서 어떤 것을 통해 성능을 개선할 수 있는지 약간은 감이 왔을 것이다.

그럼 이 분석한 실행계획을 바탕으로 정말 성능 개선을 해보자!

❓ 그럼 어떻게 실행되고 있는 SQL이 좋고 나쁜지 구분할 수 있을 까?

출처: 업무에 바로 쓰는 SQL 튜닝 책

명확히 실행 계획 보고 성능 개선이 필요한지 선 그어 구분하긴 어렵다.

하지만 각자 상황에 맞게 검토 대상 추출이 필요하다.

 

자세한 내용은 노션을 참고하자.

위 실행계획의 특정 값에 대해서 설명하자면, 다음과 같다.

 

1. DEPENDENT SUBQUERY, DEPENDENT UNION (select_type)

•  union, union all을 사용하는 서브쿼리가 메인 테이블의 영향을 받는 경우로, union으로 연결된 단위 쿼리들 중 처음으로 작성한 단위쿼리(DEPENDENT SUBQUERY), 두 번째 단위 쿼리(DEPENDENT UNION)를 말한다. 메인 테이블로 부터 값을 의존적으로 하나씩 공급받는 구조이기에 성능적으로 불리해 튜닝의 대상이 된다.

2. uncacheable subquery (select_type)

• 메모리에 상주하여 재사용되어야 할 서브쿼리가 재사용 되지 못할 때 출력되는 유형이다.

3. index, all (type)

• 인덱스 풀 스캔인지, 테이블 풀 스캔인지를 알 수 있다.

4. using filesort, using temporary (extra)

• using filesort는 정렬이 필요한 데이터를 메모리에 올리고 정렬 작업을 수행한다는 의미이다.
  보통 정렬된 인덱스를 사용하면 추가적인 정렬 작업이 필요 없지만 인덱스를 사용하지 못할때는 정렬을 위해 메모리 영역에 데이터를 올리게 되어 성능 튜닝의 대상이다
• using temporary는 데이터 중간 결과 저장을 위해 임시테이블 생성하겠다는 의미다.

 

❗️자, 그럼 진짜로 SQL 튜닝을 한번 해보자!

실습을 통한 자세한 성능 튜닝을 통한 성능 개선기는 노션에 잘 정리되어 있으니 아래 4장과 5장을 참고하자.

4장 노션 링크

5장 노션 링크

 

 

🫀SQL 튜닝을 진행하면서 느낀점 정리

위 노션을 보면 5장의 마지막 인덱스 생성과 콜레이션 부분 정도를 제외하고, SQL을 하나하나 작성해가며 설정했었다.

그러면서 학습한 내용을 종합적으로 스스로 정리해 보았다.

 

1. substring(), length() 와 같은 함수로 가공해서 사용하면 인덱스를 사용하지 않게 한다. 따라서 특정 인덱스를 타게 하고 싶은지 안타게 하고 싶은지 우리가 조절 할 수 있다.
2. extra 컬럼에 using temporary가 존재하면 가상 테이블을 만들지 않고 쿼리를 실행할 수는 없는지 고려해보자
3. 묵시적 형변환을 사용하면 인덱스가 잘 활용되지 못한다.
4. rows수를 통해 스토리지 엔진이 얼마만큼의 row에 접근했는지, filtered 통해서 몇퍼센트가 MySQL 엔진을 통해서 필터링 되고 남았는지를 알 수있기에 filtered 컬럼의 수가 낮을 때는 성능 개선을 고려해보자
5.  DISTINCT 키워드를 쿼리에 작성하는 것 만으로도 정렬 작업이 포함되기에 꼭 사용해야할 곳에만 사용하자. (따라서 임시테이블을 생성한다)
   물론 정렬된 기본키, 인덱스 활용하면 정렬 작업 부담 덜 수 있다
6.  UNION 키워드는 중복 제거를 위해 가상테이블을 사용하기 때문에 (메모리에 공간이 없으면 디스크에 임시파일을 생성한다) 사용을 지양하자. → 사용할 일이 있으면 UNION ALL을 사용하자
7. 다중 컬럼 인덱스가 있을때는 순서도 중요하다. grouping 할때 설정된 다중 컬럼 인덱스의 컬럼 순서대로 작성해야 가상테이블을 만들지 않는다.
8. 엉뚱한 인덱스를 탄다면 힌트를 통해 인덱스 설정을 해주거나, 데이터 범위를 줄일 수 있다.
9. 엉뚱한 인덱스를 타는지에 대한 방법은 전체 row 대비 조건에 해당하는 row가 몇건인지 비교해 비율이 더 적은 쪽의 인덱스를 사용하는게 낫다.
10. 인덱스를 사용하는 것보다 테이블 풀 스캔을 사용하는것이 나을 수도 있다. (Random access가 발생 하기 때문). 따라서 카디널리티가 낮은 컬럼에 인덱스를 사용한 것은 아닌지, 따라서 전체 데이터 대비 검색 하는 데이터 비율이 상당히 높은 것은 아닌지 따져봐야 한다.
    또한, 조회하려는 데이터가 전체 대비 소량의 데이터면 인덱스를 사용하는것이 낫고, 대량의 데이터면 테이블 풀 스캔이 더 나을 수도 있다
    
11. driven 테이블에서 대량의 데이터에 대해 랜덤 엑세스하면 비효율적이다.
12. driven / driving table 정할 시에는 driven 테이블 조회 하는게 더 비싸기에 인덱스가 없는 테이블을 driving으로 정하는게 효율적이다.
13. driving / driven 테이블을 지정하고 싶을때는 STRAIGHT_JOIN 키워드를 통해 순서를 쿼리를 통해 설정할 수 있다.
14. 서브쿼리보다 조인으로 수행하는게 성능 측면에서 유리할 가능성이 높다.
15. 값이 있는 여부만 확인이 필요하다면 조인하지 말고 EXIST 키워드 사용을 고려해 봐라
16. 페이징 시 데이터를 전부 가져와 페이징 하는게 아니라 페이징 후 조인하는 것을 고려하자
17. update시 인덱스가 존재한다면 update 시간이 오래 걸릴 수 있기에 사용하지 않는 인덱스는 삭제를 고려하자
18. 대소문자 구분해 조회할 경우 콜레이션 변경을 고려하자
19. 범위 검색의 경우 범위 방식 파티셔닝을 고려하자 

 

😆 SQL 학습을 진행 한 후기

진짜 SQL 성능 튜닝을 하면서 내가 작성하는 SQL에 대한 검증, 즉 내가 SQL을 제대로 잘 작성하고 있는가? 에 대한 의문점이 해소되었다.

REAL MySQL 같은 더 심화적인 책으로 DB에 대한 지속적인 학습은 필요하지만 그래도 성능 튜닝에 첫 걸음을 디딘 느낌이다!

 

 

📻 SQL 성능 튜닝 - (1) 왜 SQL 튜닝을 해야하지? 어떻게 하지? 링크

📻 SQL 성능 튜닝 - (2) 실행계획을 분석해 성능 튜닝을 해보자! 링크

 

'업무에 바로 쓰는 SQL 튜닝' 책을 읽고 정리한 노션 링크

업무에 바로쓰는 SQL 튜닝 | Notion

Reference

https://ebook-product.kyobobook.co.kr/dig/epd/ebook/E000002942550

업무에 바로 쓰는 SQL 튜닝 | 양바른 | 한빛미디어- 교보ebook