[SQL] 성능 개선을 위한 Query Tuning

서비스 차원

일반적인 차원에서는 Query 개선, Index 개선, Schema 재설계, 서버 설정과 자원 개선, 병목현상을 제거하는 방법으로 개선을 꾀할 수 있습니다.

Query 개선

Query 최적화

HAVING 대신 WHERE 절을 사용하고, 조건을 효율적으로 개선하여 불필요한 JOIN을 제거합니다. 또한, SELECT 절에서 필요한 column만 선택하여 검사하는 row를 최소화합니다.

Subquery → JOIN

Subquery가 반복되는 경우, 가능하면 JOIN으로 대체하여 처리 속도를 향상시킵니다. 또한 WITH 사용을 고려해볼 수 있습니다.

Index 개선

Index 검토 및 추가

자주 사용되는 검색 column과 JOIN column에 Index를 걸어 줍니다

Index 관리

index 통계를 정기적으로 갱신하고, 사용 되지 않거나 비효율적이면 제거합니다.

Schema 재설계

정규화 및 비정규화

데이터 중복을 제거하고 관계를 적절히 정의하여 데이터의 일관성과 성능을 동시에 관리합니다. 특정 조회 작업이 빈번하다면 비정규화를 고려할 수도 있습니다.

Clustered Index

데이터의 물리적 저장 순서를 조정하여 조회 성능을 향상시킬 수 있습니다.

서버 설정 및 자원

하드웨어 성능 개선

CPU, 메모리, 저장소 성능을 증가시키는 것도 하나의 방법입니다. ($돈₩!)

Cache및 Buffer 설정

데이터베이스의 캐시 크기를 조정하여 입출력 작업을 최적화합니다.

병목 현상

동시성 관리

Transaction과 Lock 경쟁 상황이 잦다면, 격리시키거나 Lock을 회피할 . 수있는 방법을 찾아봅니다.

Batch 처리

대량의 데이터 처리 작업은 배치 처리를 통해 시스템의 부하를 분산시킬 수 있습니다.

데이터베이스 구성 요소

실행 계획 분석

실행 계획을 분석하여 비효율적인 부분을 파악하고, 필요에 따라 쿼리를 재작성하거나 index 조정합니다.

Partition 혹은 Shard

큰 테이블을 여러 부분으로 나누어 관리하여 검색 속도를 개선하고 유지 보수를 쉽게 할 수 있습니다.

네트워크

네트워크 인프라를 개선하여 네트워크 지연을 최소화합니다.

결과 저장

결과를 Cache server에 저장

자주 접근하는 데이터라면 cache하여 hit한 경우 빠르게 응답시킵니다. 그러나 CRUD가 잦다면 오히려 성능이 저하할 수도 있습니다.

Query 차원의 일반적인 방법

Query 구조 최적화

• SELECT 최적화: 필요한 Column만 선택하여 데이터 전송량을 최소화합니다.
• HAVING 보다 WHERE를 사용합니다.
• WHERE 조건 최적화: 조건을 명확하게 작성하여 불필요한 데이터 스캔을 줄입니다. 가능한 한 인덱스가 활용될 수 있도록 조건을 구성합니다.
• Subquery 최적화: Subquery를 JOIN으로 변경하여 성능을 향상시키는 경우가 많습니다.
• WITH를 사용하여 반복 Subquery 줄이기 : Subquery리가 반복적으로 실행되는 것을 피합니다.

Index 활용

Index 추가

WHERE 절, JOIN 조건, ORDER BY 절에서 자주 사용하는 column에 Index를 추가하여 데이터 검색 속도를 높입니다.

• Index 검토: 사용되지 않거나 비효율적인 index를 식별하여 제거하거나 수정합니다.
• Clustered Index 고려: 여러 컬럼이 조건에 사용될 때 복합 인덱스를 고려할 수 있습니다. 인덱스의 컬럼 순서도 성능에 영향을 미칩니다.

JOIN 최적화

• 적절한 조인 유형 선택: Nested Loops, Hash Join, Merge Join 등 쿼리의 성격에 맞는 조인 방식을 선택합니다. 실행 계획을 분석하여 더 효율적인 조인 방식을 선택할 수 있습니다.
• LEFT JOIN 순서 최적화: 다중 테이블 조인 시 데이터 양이 작은 테이블부터 조인하는 것이 좋습니다. LEFT TABLE row를 줄여서 탐색하는 양을 줄일 수 있습니다

실행 계획 분석

• 실행 계획 검토: Query 실행 계획을 확인하여 각 단계의 비용과 성능 병목을 식별합니다.
• 통계 정보 업데이트: DB 통계 정보를 최신 상태로 유지하여 DB 자체 최적화를 효율적으로 사용합니다.

Query 배치 및 최적화

• 배치 처리: 대량의 데이터 처리는 적절한 배치 사이즈로 나누어 처리하는 것이 좋습니다. 이는 특히 대용량 데이터를 INSERT, UPDATE, DELETE 할 때 중요합니다.
• 임시 테이블 사용: 복잡한 query를 여러 단계로 나누어 각 단계의 결과를 임시 Table에 저장하여 활용합니다.

SQL 함수와 계산 최적화

• 가능한 DB server에서 처리: 가능한 많은 계산을 DB서버에서 처리하도록 합니다. Applicatiion 서버 보다 데이터베이스에서 처리하는 것이 효율적인 경우가 많습니다. 그러나 형변환을 주의해야합니다.
• 함수 사용: 내장 함수의 사용은 성능에 영향을 미칠 수 있으므로, 필요한 경우만 합니다.

형 변환의 문제

형변환에 의한 성능 저하 이해

index를 사용 불가한 상황

형변환으로 인해 index를 사용하지 못하고 Full-Scan을 하게 되는 경우가 있습니다. 예를 들어, 문자열 column 대해 숫자로 형변환을 하고 비교하는 경우, 해당 column 설정한 index를 사용할 수 없습니다.

자원 소모 증가

데이터 타입을 변환하는 과정에서 CPU와 메모리 사용량이 증가할 수 있으며, 이는 전체적인 시스템 성능에 부담을 줄 수 있습니다.

암시적 형변환 피하기

• 데이터 유형 일치: Query 작성 시 상수, 변수, 그리고 데이터베이스 칼럼의 데이터 유형이 일치하도록 주의합니다. 이는 데이터베이스가 자동으로 형변환을 수행하는 것을 방지합니다.

명시적 형변환 최소화

• 필요한 곳에서만 형변환 사용: 형변환이 필요한 경우에만 최소한으로 사용하고, 가능한 쿼리의 다른 부분에서는 피하도록 합니다. 특히 JOIN 조건이나 WHERE 절에서는 형변환을 피하는 것이 중요합니다.

적절한 데이터 유형 사용

• 데이터 모델링 단계에서 처리: 데이터 모델을 설계할 때부터 데이터 유형을 적절히 선택하여, 애플리케이션과 데이터베이스 간에 데이터 유형 불일치를 방지합니다.

Computed Column Index

• Computed Column Index : 데이터 유형 변환을 필요로 하는 비즈니스 로직이 있는 경우, computed column을 만들고 이에 index를 설정할 수 있습니다. 이 방법으로 데이터 변환 로직을 데이터베이스가 처리하게 하고, 이 column에 대한 query 성능을 향상시킬 수 있습니다.

적절한 Cast 함수 사용

• 표준 SQL 함수 사용: 각 데이터베이스 시스템에서 제공하는 표준 SQL 함수를 사용하여 형변환을 수행하면, 불필요한 overhead를 줄이고 최적화된 방법으로 데이터 유형을 변환할 수 있습니다.

Nested Loops, Hash Join, Merge Join

JOIN 종류

Nested Loops Join

Nested Loops Join은 가장 기본적인 조인 유형으로, 외부 테이블(Outer Table)과 내부 테이블(Inner Table) 두 개의 테이블을 사용합니다.

• 외부 Table의 각 행에 대해 내부 테이블 전체를 순회하면서 조인 조건에 맞는 행을 찾습니다.
• 외부 Table의 각 행에 대해, 내부 테이블의 모든 행을 순회하면서 조인 조건에 맞는 행을 찾습니다.
• 이 과정은 외부 Table의 각 행마다 내부 Table을 처음부터 끝까지 검색하는 방식으로 진행됩니다.
• 작은 데이터 세트를 처리할 때 효과적이며, 특히 내부 Table에 적절한 인덱스가 있을 경우 매우 빠를 수 있습니다. 각 외부 행에 대한 검색이 빠르기 때문입니다. 데이터 크기가 큰 경우 성능이 저하될 수 있습니다.
• 따라서 행이 적은 JOIN 또는 WHERE 조건이 많을 수록 유리합니다.

Hash Join

Hash Join은 두 테이블 중 하나의 Table(보통 작은 쪽)을 메모리 내에 해시 테이블로 생성한 후, 다른 테이블을 스캔하면서 메모리의 해시 Table과 큰 Table(외부 Table)을 스캔하면서 각 행의 조인 Column 값을 Hash하여 Hash Table에서 일치하는 행을 찾습니다.

• 적합한 상황: 대용량 데이터를 처리할 때 유리하며, 특히 조인할 컬럼에 Index가 없는 경우 좋은 성능을 보일 수 있습니다. 큰 데이터 세트와의 조인에서 메모리를 효율적으로 사용할 수 있을 때문입니다. 두 테이블 중 작은 테이블을 메모리에 올릴 수 있을 정도로 충분한 메모리가 필요합니다. 대신 충분한 메모리가 확보된 환경에서 성능이 크게 향상될 수 있습니다.

Merge Join

Merge Join은 두 테이블이 조인 키 기준으로 이미 정렬되어 있거나, 정렬 작업을 수행한 후에 정렬된 두 테이블을 한 번에 비교하면서 조인합니다.

정렬된 두 테이블을 병합하면서 조인 조건에 부합하는 행을 결합합니다.

• 적합한 상황: 두 테이블이 이미 정렬되어 있거나, 정렬 비용이 비싸지 않을 경우에 효과적입니다. 조인 키가 중복 값이 많지 않을 때 유리합니다. 또한, 두 테이블을 한 번씩만 스캔하므로 대용량 데이터 처리에도 적합하며, 정렬된 데이터 스트림을 효율적으로 처리할 수 있습니다.

선택 가이드

데이터 크기와 index 유무, 데이터 정렬 여부 및 메모리 크기와 사용량에 따라 4가지로 나눌 수 있습니다.

• 데이터 크기: 작은 데이터 세트는 Nested Loops가 적합할 수 있고, 큰 데이터 세트는 Hash Join이나 Merge Join이 적합할 수 있습니다.
• Index 유무: 인덱스가 있는 경우 Nested Loops가 유리하며, 인덱스가 없다면 Hash Join을 고려할 수 있습니다.
• 데이터 정렬 상태: 데이터가 이미 정렬되어 있다면 Merge Join이 유리합니다.
• 메모리 사용량: 충분한 메모리가 확보된 경우, 메모리를 적극적으로 활용하는 Hash Join이 좋을 수 있습니다.

Subquery 최적화

Query 자체 특성과 DB 자체 최적화 성능에 따라 달라질 수 있습니다. Subquery가 성능을 개선하는 경우도 있지만, 항상 그렇지는 않습니다. 그렇지만 일반적으로, Subquery는 특정 상황에서 가독성을 높이고, 로직을 분리함으로써 관리에 있어서 큰 도움이 되기도 합니다. 그러나 다음 조건에서는 오히려 성능 저하를 일으킬 수도 있습니다.

Subquery 성능 저하의 원인

비효율적인 실행 계획

각 외부 행 마다 Subquery가 실행되어 전체적인 성능 저하를 초래할 수 있습니다. 이 경우 WITH를 사용해봅니다.

Index를 효과적으로 사용할 수 없는 경우

Subquery가 Index를 효과적으로 활용하지 못하는 경우 성능이 저하될 수 있습니다. (Subquery가 복잡하거나 DB자체 최적화가 잘 작동하지 않는 경우)

반복으로 인한 자원 사용

복잡한 Subquery 실행 시 많은 메모리와 CPU 리소스를 소모할 수 있으며, 이는 특히 대용량 데이터를 처리하는 경우 더욱 문제가 될 수 있습니다.

성능을 개선할 수 있는 경우

데이터 분리 및 감소

데이터를 미리 필터링하거나 집계하여 주 Query가 처리해야 할 데이터 양을 줄일 수 있습니다. 특히 Nested Loop JOIN의 경우 유리할 수 있습니다.

복잡한 조건을 간소화

여러 단계에 걸친 복잡한 데이터 처리 요구 사항을 Subquery를 통해 단계별로 나누어 처리할 수 있습니다. 이는 DBMS가 더 효율적인 실행 계획을 수립할 수 있게 하며, 각 단계에서 최적화를 적용하기 쉬워집니다.

최적화된 실행 계획

일부 DBMS는 Subquery를 사용할 때 더 효율적인 실행 계획을 생성할 수 있습니다. 예를 들어, Subquery 결과를 임시 테이블로 저장하고, 이를 메인 query에서 참조하는 방식은 계산 비용을 줄일 수 있습니다.

최적의 사용을 위한 팁

• JOIN 사용 고려: JOIN은 Subquery 보다 index를 효율적으로 활용하는 경우가 있습니다. DBMS 자체 최적화를 수월하게 하도록 도움이 됩니다.
• 성능 테스트: 개발 환경에서 Subquery 성능을 테스트하고, 대안을 비교해 봅니다.

Subquery VS Join

데이터의 양, 구조, 인덱스의 존재 유무, DB 자체 성능 최적화 등 여러 요소에 의해 영향을 받습니다.

Subquery

장점

• 가독성이 우수합니다.
• 중간 결과를 생성하기 때문에 이를 바탕으로 재활용이 가능합니다.

단점

• 데이터가 많은 경우, subquery마다 실행하기 때문에 with를 잘 사용해야합니다.
• index를 활용하지 못하는 경우가 많아, 전체 Table을 조회하는 경우가 잦습니다.

JOIN

장점

• index를 잘 활용합니다.
• JOIN 종류가 다양하기 때문에 적절한 방식을 사용해서 성능 개선을 기대할 수 있습니다.

단점

• JOIN 순서와 종류를 잘 모를 경우
• 가독성이 낮음

정리

• 데이터 크기가 작고 로직이 복잡한 경우: Subquery
• 대량의 데이터 처리가 필요한 경우: JOIN