쿼리 최적화(Query Optimization)

출처: https://www.atlassian.com/data/sql/sql-join-types-explained-visually

 

데이터베이스 성능 최적화는 백엔드 개발자의 중요한 역할 중 하나입니다. 효율적인 쿼리 작성은 애플리케이션의 응답 속도와 전체적인 성능에 직접적인 영향을 미칩니다. 이번 포스트에서는 쿼리 최적화의 심화된 개념과 기법들을 살펴보며, 실무에서 자주 마주치는 성능 문제를 해결하는 방법을 살펴보겠습니다.

 

 

---

 

실행 계획(Execution Plan) 분석

 

실행 계획이란?

실행 계획(Execution Plan)은 데이터베이스가 특정 쿼리를 실행하는 방법을 설명하는 단계별 절차입니다. 실행 계획을 분석하면 쿼리가 어떻게 처리되는지, 어떤 인덱스가 사용되는지, 조인 순서가 어떻게 되는지 등을 파악할 수 있습니다. 이를 통해 병목 지점을 식별하고 쿼리 성능을 개선할 수 있습니다.

실행 계획 확인 방법

각 데이터베이스 시스템은 실행 계획을 확인하는 방법이 다릅니다. 주요 데이터베이스에서 실행 계획을 확인하는 방법은 다음과 같습니다.

 

PostgreSQL

EXPLAIN ANALYZE
SELECT u.username, o.order_id
FROM Users u
JOIN Orders o ON u.user_id = o.customer_id
WHERE u.email = 'john@example.com';

 

MySQL

EXPLAIN
SELECT u.username, o.order_id
FROM Users u
JOIN Orders o ON u.user_id = o.customer_id
WHERE u.email = 'john@example.com';

 

SQL Server

SET SHOWPLAN_ALL ON;
GO
SELECT u.username, o.order_id
FROM Users u
JOIN Orders o ON u.user_id = o.customer_id
WHERE u.email = 'john@example.com';
GO
SET SHOWPLAN_ALL OFF;
GO

실행 계획 분석하기

실행 계획을 분석할 때 주요하게 봐야 할 요소는 다음과 같습니다.

• Seq Scan vs Index Scan: 시퀀스 스캔은 전체 테이블을 읽는 반면, 인덱스 스캔은 인덱스를 활용하여 필요한 데이터만 읽습니다. 인덱스 스캔이 성능에 유리합니다.
• Join Method: Nested Loop, Hash Join, Merge Join 등의 조인 방법이 사용됩니다. 데이터 크기와 조인 조건에 따라 최적의 조인 방법을 선택해야 합니다.
• Cost Estimates: 쿼리 실행 비용을 추정한 값으로, 낮을수록 효율적입니다.
• Row Estimates: 예상되는 반환 행 수로, 정확한 추정이 성능 최적화에 중요합니다.

 

---

 

효율적인 조인 전략

 

조인 유형 이해하기

데이터베이스에서는 여러 조인 유형이 있으며, 각 조인은 특정 상황에서 최적의 성능을 발휘합니다.

• Nested Loop Join: 작은 테이블과 인덱스가 있는 큰 테이블 조인에 적합합니다.
• Hash Join: 큰 테이블 간의 조인에 적합하며, 메모리에 해시 테이블을 생성하여 빠르게 매칭합니다.
• Merge Join: 정렬된 데이터를 조인할 때 유리하며, 이미 정렬된 인덱스를 활용할 수 있습니다.

 

적절한 조인 순서 선택

조인의 순서는 쿼리 성능에 큰 영향을 미칩니다. 일반적으로 먼저 작은 테이블을 조인하고, 그 결과를 큰 테이블과 조인하는 것이 효율적입니다.

예제: 작은 테이블을 먼저 조인

SELECT u.username, o.order_id
FROM Users u
JOIN Orders o ON u.user_id = o.customer_id
WHERE u.email = 'john@example.com';

여기서 Users 테이블이 작은 경우, 먼저 Users 테이블을 필터링하여 조인하면 성능이 향상됩니다.

 

인덱스를 활용한 조인 최적화

 

조인에 사용되는 컬럼에 인덱스를 생성하면 조인 성능이 향상됩니다. 특히 외래 키 컬럼에 인덱스를 생성하는 것이 좋습니다.

예제: 외래 키 컬럼에 인덱스 생성

CREATE INDEX idx_orders_customer_id ON Orders(customer_id);

이 인덱스는 Orders 테이블과 Users 테이블 간의 조인을 빠르게 수행할 수 있게 합니다.

 

 

---

 

서브쿼리와 조인의 성능 비교

 

서브쿼리의 장단점

서브쿼리(Subquery)는 쿼리 내에 또 다른 쿼리를 포함하는 형태로, 복잡한 조건을 표현할 때 유용합니다.

장점:

• 가독성이 높아 복잡한 논리를 간결하게 표현할 수 있습니다.
• 특정 조건에 따라 데이터를 필터링하는 데 유리합니다.

단점:

• 일부 데이터베이스에서는 서브쿼리 실행 시 반복적으로 쿼리가 수행되어 성능 저하가 발생할 수 있습니다.
• 최적화가 어려워 대규모 데이터셋에서 비효율적일 수 있습니다.

 

조인의 장단점

조인(Join)은 여러 테이블을 결합하여 데이터를 조회하는 방식으로, 관계형 데이터베이스에서 기본적인 데이터 조회 방법입니다.

장점:

• 최적화된 실행 계획을 통해 효율적인 데이터 조회가 가능합니다.
• 인덱스를 활용하여 빠른 조인 성능을 발휘할 수 있습니다.
• 대규모 데이터셋에서도 안정적인 성능을 제공합니다.

단점:

• 복잡한 조인 조건에서는 쿼리 성능이 저하될 수 있습니다.
• 조인 순서와 조인 유형에 따라 성능 차이가 발생할 수 있습니다.

 

성능 비교 사례

아래는 동일한 기능을 수행하는 서브쿼리와 조인의 성능 비교 예제입니다.

서브쿼리 사용

SELECT username, order_id
FROM Users
WHERE email = 'john@example.com'
  AND user_id IN (SELECT customer_id FROM Orders);

조인 사용

SELECT u.username, o.order_id
FROM Users u
JOIN Orders o ON u.user_id = o.customer_id
WHERE u.email = 'john@example.com';

비교 결과:

• 조인이 인덱스를 잘 활용하면 서브쿼리보다 빠른 성능을 보일 수 있습니다.
• 서브쿼리는 일부 데이터베이스에서 반복적인 쿼리 실행으로 인해 성능 저하가 발생할 수 있습니다.

 

---

 

실전 쿼리 최적화 사례

 

인덱스 활용을 통한 쿼리 개선

인덱스를 적절히 활용하면 쿼리 성능을 크게 향상시킬 수 있습니다.

예제: 인덱스를 활용한 검색 속도 향상

-- 인덱스 없는 경우
EXPLAIN ANALYZE
SELECT u.username, o.order_id
FROM Users u
JOIN Orders o ON u.user_id = o.customer_id
WHERE u.email = 'john@example.com';

-- 인덱스 추가 후
CREATE INDEX idx_users_email ON Users(email);
CREATE INDEX idx_orders_customer_id ON Orders(customer_id);

EXPLAIN ANALYZE
SELECT u.username, o.order_id
FROM Users u
JOIN Orders o ON u.user_id = o.customer_id
WHERE u.email = 'john@example.com';

결과:

• 인덱스를 추가한 후, 쿼리의 실행 시간이 크게 감소하였습니다.
• 인덱스 스캔을 통해 필요한 데이터만 빠르게 조회할 수 있게 되었습니다.

 

불필요한 데이터 제거

쿼리에서 불필요한 데이터를 제거하면 검색 속도가 향상됩니다. SELECT 절에 필요한 컬럼만 명시하고, WHERE 절을 통해 데이터를 필터링합니다.

예제: 필요한 컬럼만 선택하기

-- 불필요한 컬럼 포함
SELECT u.*, o.*
FROM Users u
JOIN Orders o ON u.user_id = o.customer_id
WHERE u.email = 'john@example.com';

-- 필요한 컬럼만 선택
SELECT u.username, o.order_id
FROM Users u
JOIN Orders o ON u.user_id = o.customer_id
WHERE u.email = 'john@example.com';

결과:

• 필요한 데이터만 조회하여 네트워크 대역폭과 메모리 사용을 절약할 수 있습니다.
• 쿼리 실행 속도가 향상됩니다.

 

집계 함수 최적화

집계 함수는 대용량 데이터에서 성능에 큰 영향을 미칩니다. 인덱스를 활용하여 집계 함수를 최적화할 수 있습니다.

예제: 인덱스를 활용한 집계 함수 최적화

-- 인덱스 없는 경우
EXPLAIN ANALYZE
SELECT COUNT(*)
FROM Orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- 인덱스 추가 후
CREATE INDEX idx_orders_order_date ON Orders(order_date);

EXPLAIN ANALYZE
SELECT COUNT(*)
FROM Orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

결과:

• 인덱스를 추가한 후, 집계 함수의 실행 시간이 단축되었습니다.
• 인덱스를 활용하여 범위 조건에 맞는 데이터만 빠르게 집계할 수 있게 되었습니다.

 

 

 

 

 

 

쿼리 최적화는 데이터베이스 성능을 극대화하는 데 중요한 역할을 합니다. 실행 계획 분석을 통해 쿼리의 병목 지점을 파악하고, 효율적인 조인 전략과 인덱스 활용을 통해 쿼리 성능을 향상시킬 수 있습니다. 또한, 서브쿼리와 조인의 성능 차이를 이해하고, 실전 사례를 통해 최적화 기법을 적용함으로써 데이터베이스의 효율성을 극대화할 수 있습니다.

 

 

 

 

참고 자료

https://ko.wikipedia.org/wiki/%EC%A7%88%EC%9D%98_%EC%B5%9C%EC%A0%81%ED%99%94

질의 최적화 - 위키백과, 우리 모두의 백과사전

 

https://www.geeksforgeeks.org/best-practices-for-sql-query-optimizations/

Best Practices For SQL Query Optimizations - GeeksforGeeks

 

https://www.geeksforgeeks.org/sql-indexes/

SQL Indexes - GeeksforGeeks

 

https://www.atlassian.com/data/sql/sql-join-types-explained-visually

Visualizing SQL Joins | Atlassian

 

https://www.mongodb.com/ko-kr/docs/manual/applications/indexes/

인덱싱 전략 - MongoDB 매뉴얼 v8.0