[데이터베이스] DB 튜닝 정리
목차
1. DB 튜닝 개념
2. 튜닝 전략
1. DB 튜닝 개념
DB 튜닝(데이터베이스 튜닝)이란 DB 구조, DBMS 조정 등을 통해 DB 시스템의 성능을 최적화하는 작업을 의미한다. 수 만에서 수 억명의 사람들이 사용하는 서비스에서는 속도가 생명이기 때문에 이러한 DB 튜닝은 필수적이다.
DB 튜닝 = DB 구조, DBMS 조정 등을 통해 DB 시스템이 성능을 개선하는 작업
2. 튜닝 전략
이전에 배웠던 정규화 및 인덱스도 DB 튜닝 방식 중 하나라고 볼 수 있으며, 그 외에도 여러가지 튜닝 방법이 존재한다. 크게 아래와 같이 3가지 영역으로 분류할 수 있다.
1. DB 설계 튜닝
2. DBMS 튜닝
3. SQL 튜닝
1. DB 설계 튜닝
테이블과 인덱스의 설계를 최적화하는 튜닝 방법이다. 이전에 정규화 및 역정규화, 그리고 인덱스 설정 등이 이에 해당한다.
- 정규화를 통한 데이터 중복을 최소화한다.
- 역정규화를 통해 조인을 줄이는 등의 작업을 진행한다.
- 인덱스를 설계하여 검색 속도를 최적화한다.
위와 같은 작업을 통해 무결성을 보장하고 검색 속도를 향상시킬 수 있다.
2. DBMS 튜닝
DB 서버의 하드웨어를 업그레이드한다든지 DBMS 설정을 조정하여 튜닝을 진행할 수 있다.
- 캐시 사이즈 조정
- 동시 연결 세션 수 조정
- Block I/O 최적화
- Block 크기 조정
3. SQL 튜닝
마지막 튜닝 전략은 쿼리를 최적화하는 것이다. 백엔드 개발자라면 SQL 튜닝을 통해 성능을 향상 시켜야할 일이 많을 것이기 때문에 몇 가지만 짚고 넘어가자. SQL 튜닝 관련해서 더 깊게 알고 싶다면 여기를 참고하자.
1. Driving Table은 작게, 그리고 인덱스를 활용한다.
2. 조인 방법과 조인 순서를 최적화한다.
3. 조회화면은 페이징 처리를 한다.
1. Driving Table은 작게, 그리고 인덱스를 활용한다.
Driving Table(또는 Outer Table)이란 조인시 첫 번째로 엑세스하는 테이블을 의미하며, 이후 접근 되는 테이블을 Driven Table이라고 한다. Nested Loop Join 시 이중 for 문을 수행하듯이 [Driving Table > Driven Table] 을 순회하는데, 이때 Driving Table의 건수가 적을 수록 Driven Table의 조인 건수도 줄어들기 때문에 테이블의 규모가 작거나 건수가 적은 테이블을 Driving Table로 둬야 조인 건수가 줄어들어 비용을 절감할 수 있다. 이 때, Driven Table의 인덱스 설정은 필수이며, Driving Table의 Block I/O를 줄이기 위해 인덱스를 설정해준다.
2. 조인 방법과 조인 순서를 최적화한다.
앞서 Nested Loop Join 을 언급했는데 조인에는 여러 조인 기법이 존재한다. 대표적인 조인 기법 3가지를 알아보고 어느 상황에 사용하면 좋은지를 알아보자. 또한, 조인의 순서 변경을 통해 어떻게 튜닝을 할 수 있는지도 같이 살펴보자.
조인 방법
| 기법 | Nested Loop Join | Hash Join | Sort Merge Join |
| 개념 | 한 테이블의 각 행을 다른테이블의 모든 테이블과 비교하여 조인 조건이 일치하는 행을 찾는 방법 | 한 테이블을 기준으로 해시테이블을 생성하고, 다른 테이블을 스캔하며 해시테이블을 통해 조인 조건과 일치하는 행을 찾는 방법 | 두 테이블을 조인 조건 기준으로 정렬하고, 정렬한 튜플을 병합하면서 조인하는 방법 |
| 사용시기 | - 조인 건수가 소량인 경우 - 하나의 테이블은 작고, 다른 테이블의 크기는 큰 경우 - 또는, 조인 조건에 인덱스가 설정되어 있는 경우 |
- 대용량 테이터 처리시 효율적 - 두 테이블 모두 큰 경우 - 조인 조건이 '='로만 이뤄진 경우 |
- 두 테이블이 정렬되어 있거나 정렬 비용이 크지 않은 경우 - 또는, 범위 조건(>, <)이 사용되는 경우 |
조인 순서
SELECT의 경우 좁은 범위에 대한 검색을 하는 경우가 많으며 이때는 Nested Loop Join 이 사용된다. 여기서는 Nested Loop Join 에 대해서만 다루며, 조인 테이블의 수가 3개 이상인 경우에 대해 설명하겠다(2개인 경우는 1번에서 설명)
1. 중간 테이블에 인덱스 설정이 되어있지 않다면 다음 테이블과 순서를 변경한다.
2. 조인시 Block I/O 증가하는 경우 순서 변경을 검토한다.
① 중간 테이블에 인덱스 설정이 되어있지 않다면 다음 테이블과 순서를 변경한다.
예를 들어 A → B(인덱스 X) → C(인덱스 O) 의 순서대로 조인을 하는 상황이라면, B에 인덱스를 추가하기 전에 A → C(인덱스 O) → B(인덱스 X) 로 순서를 바꿔서 효율적인지를 확인한다. 만약 테이블 C가 중간에 오는 것이 비효율적이라면 그때 B에 인덱스를 설정해준다.
② 조인시 Block I/O 증가하는 경우 순서 변경을 검토한다.
특정 함수를 통해 Block I/O량을 관찰하여, 순서 변경을 고려한다. 예를 들어, A → B → C 의 순서로 조인을 하는데 B에서 Block I/O 량이 증가했다면 A → C → B 로 변경하여 Block I/O량을 관찰하여 성능을 비교한다.
3. 조회화면은 페이징 처리를 한다.
전체 데이터를 보여주는 화면(eg. 전체 게시판)이라 할지라도 화면에 보여지는 건수는 한정되어 있다. 그렇기 때문에 보여지는 단위로 끊어 출력하는 페이징을 사용함으로써 Block I/O 부하, 조인 부하 등을 한번에 감소시킬 수 있다. 프레임워크마다 이러한 페이징을 할 수 있는 라이브러리를 제공하며, 스프링의 경우엔 Pageable 이라는 페이징 관련 라이브러리를 지원한다.
정리
DB 튜닝 - DB 시스템의 성능을 개선시키는 작업
튜닝 전략
1. DB 설계 튜닝 - 정규화,역정규화,인덱스 설정 등
2. DBMS 튜닝 - Block 크기, 캐시 크기, 동시연결 세션 수 조정 등
3. SQL 튜닝 - 선행테이블 선택, 조인 방법&순서 조정, 페이징 처리 등
[참고 링크]
https://scidb.tistory.com/entry/SQL%ED%8A%9C%EB%8B%9D-%EB%B0%A9%EB%B2%95%EB%A1%A0