데이터베이스를 사용하다 보면 동일한 쿼리라도 어떻게 작성하고 최적화하느냐에 따라 처리 시간이 몇 초에서 몇 시간까지 크게 달라질 수 있습니다. 이번 글에서는 쿼리 최적화의 개념부터 구현 방식, 그리고 성능 향상을 위한 다양한 팁까지 상세히 알아보겠습니다. 🧐
쿼리 최적화란? 🚀
쿼리 최적화(Query Optimization)는 관계형 데이터베이스(RDB), NoSQL, 그래프 데이터베이스 등 다양한 데이터베이스 시스템에서 사용되는 기능으로, 주어진 쿼리를 가장 효율적으로 실행할 수 있는 방법을 찾는 과정입니다. 쿼리 최적화 프로그램은 여러 가능한 쿼리 실행 계획 중에서 비용이 가장 낮은 계획을 선택하여 데이터베이스의 성능을 향상시킵니다.
쿼리 최적화의 중요성
동일한 쿼리라도 어떤 실행 계획을 선택하느냐에 따라 처리 시간은 몇 초에서 몇 시간까지 크게 차이날 수 있습니다. 모든 가능한 쿼리 계획 중 최상의 것을 찾는 것은 매우 복잡하고 시간이 많이 소요되기 때문에, 일반적으로는 합리적인 시간 내에 충분히 좋은(near-optimal) 계획을 찾는 것이 목표입니다.
쿼리 최적화 시 고려해야 할 요소들 📝
1. 물리적 리소스 ⚙️
I/O 연산: 디스크 접근 횟수를 최소화하여 성능을 향상시킵니다.
디스크 버퍼 공간: 충분한 버퍼 공간을 활용하여 데이터를 효율적으로 읽고 씁니다.
CPU 경로 길이: CPU 연산을 최적화하여 처리 시간을 단축합니다.
병렬 처리: 멀티코어 및 다중 프로세서를 활용하여 작업을 병렬로 수행합니다.
2. 액세스 방법 🔍
- 기본 인덱스 액세스: 프라이머리 키를 통한 빠른 데이터 검색.
- 보조 인덱스 액세스: 보조 인덱스를 활용하여 검색 속도 향상.
- 전체 스캔(Full Scan): 인덱스가 없을 경우 테이블의 모든 행을 검사.
3. 조인 방법 🔗
- 중첩 루프 조인(Nested Loops Join): 작은 테이블을 기반으로 큰 테이블과 조인.
- 해시 조인(Hash Join): 해시 함수를 사용하여 조인 키를 매칭.
- 병합 조인(Merge Join): 조인 대상 테이블이 정렬되어 있을 때 효율적.
- 카티전 프로덕트 조인(Product Join): 모든 가능한 행의 조합을 생성(비효율적).
4. 조인 순서 🔄
조인 순서는 쿼리의 성능에 직접적인 영향을 미칩니다. 예를 들어, 각기 다른 크기의 테이블 A(10행), B(10,000행), C(1,000,000행)를 조인할 때, 먼저 B와 C를 조인하면 큰 데이터셋을 처리해야 하므로 시간이 오래 걸립니다. 반면에 A와 C를 먼저 조인하면 작은 테이블과 큰 테이블을 조인하므로 상대적으로 효율적입니다.
쿼리 최적화의 유형
논리적 최적화
정의: 쿼리를 해결하기 위해 관계 대수(Relational Algebra) 시퀀스를 생성하는 과정입니다.
목적: 쿼리의 구조를 재작성하여 보다 효율적인 실행이 가능하도록 합니다.
예시: 조건 푸시다운, 중복 제거, 불필요한 조인 제거 등.
물리적 최적화
정의: 각 작업을 실제로 어떻게 수행할지 결정하는 과정입니다.
목적: 최적의 실행 계획을 선택하여 리소스 사용을 최소화합니다.
예시: 인덱스 사용 여부 결정, 조인 알고리즘 선택, 병렬 처리 적용 등.
쿼리 최적화의 구현 방법
쿼리 계획 트리
쿼리 실행 계획은 “계획 노드”의 트리 구조로 표현됩니다.
계획 노드: 쿼리를 실행하는 데 필요한 단일 작업을 캡슐화합니다.
데이터 흐름: 트리의 하단에서 상단으로 결과가 전달됩니다.
노드 관계: 자식 노드의 결과가 부모 노드의 입력으로 사용됩니다.
계획 노드의 종류
- 조인 노드: 두 개의 자식 노드를 가지며, 각각 조인할 테이블이나 결과셋을 나타냅니다.
- 정렬 노드(Sort Node): ORDER BY, DISTINCT, GROUP BY 등의 연산을 수행하며 하나의 자식 노드를 가집니다.
- 리프 노드(Leaf Node): 실제 데이터를 읽어들이는 노드로, 인덱스 스캔이나 순차적 스캔을 수행합니다.
인덱싱 기법
데이터베이스에서 인덱싱은 쿼리 성능에 매우 중요한 역할을 합니다. 일반적으로 B-트리 인덱스가 가장 많이 사용되지만, 경우에 따라서는 비트맵 인덱스가 더 효율적일 수 있습니다.
B-트리 인덱스
- 특징: 균형 잡힌 트리 구조로, 검색, 삽입, 삭제 연산의 시간 복잡도는 O(log N)입니다.
- 장점: 범위 검색 및 순차적인 데이터 접근에 효율적입니다.
- 사용 사례: 고유한 값이 많고 데이터 변경이 빈번한 컬럼에 적합합니다.
비트맵 인덱스
- 특징: 2차원 비트맵을 사용하여 각 행의 존재 여부를 비트로 표시합니다.
- 장점: 공간 효율성이 높고, 비트 연산을 통해 빠른 검색이 가능합니다.
- 사용 사례: 카디널리티가 낮고 중복되는 값이 많은 컬럼에 적합합니다.
- 예시: 성별, 상태, 부서 코드 등 도메인이 좁은 컬럼.
성능 비교
B-트리의 시간 복잡도가 O(log N)인 반면, 비트맵 인덱스는 비트 연산을 통해 O(1)에 접근 가능합니다.
주의 사항: 데이터 변경이 빈번한 컬럼에는 적합하지 않습니다.
SARGable 쿼리와 Non-SARGable 쿼리
💡 SARGable(Search ARGument able)은 데이터베이스가 인덱스를 활용하여 쿼리를 효율적으로 실행할 수 있는지 여부를 나타내는 용어입니다.
SARGable 쿼리
인덱스를 활용하여 빠르게 데이터를 검색할 수 있는 쿼리.
특징: 인덱스가 존재하는 컬럼에 직접적인 비교나 범위 검색을 수행, 인덱스 시크(Index Seek)를 통해 효율적인 데이터 접근.
예시
SELECT * FROM Users WHERE Age = 30;
SELECT * FROM Orders WHERE OrderDate BETWEEN '2021-01-01' AND '2021-12-31';Non-SARGable 쿼리
인덱스를 효과적으로 활용하지 못해 전체 테이블을 스캔해야 하는 쿼리.
특징: 컬럼에 함수나 연산이 적용되어 인덱스 사용이 불가능, 인덱스 스캔(Index Scan)이나 테이블 스캔(Table Scan)을 수행.
예시
SELECT * FROM Users WHERE LEFT(Name, 1) = 'A';
SELECT * FROM Products WHERE Price * Quantity > 1000;최적화 팁
함수 사용 지양: WHERE 절에서 컬럼에 함수를 적용하지 말고, 가능하면 컬럼 자체를 조건으로 사용합니다.
- ❌ 잘못된 예시: WHERE UPPER(Name) = 'JOHN';
- ✅ 올바른 예시: WHERE Name = 'John';
인덱스 설계: 자주 조회되는 컬럼에 적절한 인덱스를 생성합니다.
와일드카드 사용 주의: LIKE 연산자 사용 시 와일드카드(%)를 문자열의 시작 부분에 사용하지 않습니다.
- ❌ 잘못된 예시: LIKE '%abc' (인덱스 미사용)
- ✅ 올바른 예시: LIKE 'abc%' (인덱스 사용 가능)
조인 순서가 성능에 미치는 영향
쿼리 플랜의 성능은 테이블이 조인되는 순서에 크게 의존합니다.
예를 들어:
테이블 크기:
- A 테이블: 10행
- B 테이블: 10,000행
- C 테이블: 1,000,000행
조인 순서 비교: B와 C를 먼저 조인: 대량의 데이터를 처리해야 하므로 시간이 오래 걸림.
A와 C를 먼저 조인: 작은 테이블과 큰 테이블을 먼저 조인하여 중간 결과셋의 크기를 줄일 수 있음.
결론: 작은 테이블을 우선적으로 조인하여 중간 결과셋의 크기를 최소화하는 것이 성능 향상에 유리합니다.
결론
쿼리 최적화는 데이터베이스 성능을 향상시키기 위한 필수적인 과정입니다. 물리적 리소스의 효율적인 사용, 적절한 인덱스 설계, SARGable 쿼리 작성, 그리고 조인 순서의 최적화 등을 통해 쿼리의 실행 시간을 크게 단축할 수 있습니다. 이러한 원리와 기법을 이해하고 적용함으로써 더욱 효율적인 데이터베이스 운영이 가능해집니다. 🚀
reference
https://en.wikipedia.org/wiki/Query_optimization
https://www.youtube.com/watch?v=BHwzDmr6d7s
https://www.youtube.com/watch?v=TukZd6LjeBc
