&Axum SQLx를 활용한 데이터베이스 중급 - 고급 쿼리와 최적화 기법

이번 글에서는 데이터베이스를 효율적으로 사용하기 위한 고급 쿼리 작성 및 최적화 기법을 다룹니다. 특히 CTE(Common Table Expressions), 인덱스 적용 방법, 복잡한 조인과 서브쿼리 작성, 쿼리 성능 분석 및 최적화 방법을 집중적으로 살펴보겠습니다. 이를 통해 복잡한 데이터베이스 작업을 효율적으로 처리하고, 애플리케이션의 성능을 향상시킬 수 있습니다.

---

4-1. CTE(Common Table Expressions)의 이해

1. CTE란 무엇인가?

CTE(Common Table Expression)는 쿼리 내에서 임시 결과셋을 정의하고 이를 다른 쿼리에서 재사용할 수 있도록 하는 SQL 기능입니다. CTE는 WITH 키워드를 사용하여 정의하며, 복잡한 쿼리를 읽기 쉽고 유지보수하기 쉽게 만들어줍니다.

2. CTE의 장점

• 가독성 향상: 복잡한 쿼리를 작은 단위로 나누어 읽기 쉽게 만듭니다.
• 재사용성 증가: 동일한 서브쿼리를 여러 번 사용해야 할 때 중복을 피할 수 있습니다.
• 재귀 쿼리 지원: 자기 자신을 참조하는 재귀적인 쿼리를 작성할 수 있습니다.

3. CTE 사용 예시

예제: 중복 체크 후 데이터 삽입

다음은 CTE를 사용하여 중복된 데이터 여부를 확인한 후, 중복이 없을 경우에만 데이터를 삽입하는 예제입니다.

WITH DuplicateCheck AS (
    SELECT EXISTS (
        SELECT 1
        FROM tb_book AS b
        WHERE b.name = $1
    ) AS is_duplicate
),
InsertBook AS (
    INSERT INTO tb_book (name, type_id)
    SELECT $1, $2
    FROM DuplicateCheck
    WHERE is_duplicate = false
    RETURNING id
)
SELECT 
    (SELECT id FROM InsertBook) AS id,
    (SELECT is_duplicate FROM DuplicateCheck) AS is_duplicated;

설명

1. DuplicateCheck: tb_book 테이블에서 동일한 이름의 가계부가 존재하는지 확인합니다.
   • EXISTS 서브쿼리를 사용하여 중복 여부를 is_duplicate 컬럼으로 반환합니다.
2. InsertBook: 중복이 아닌 경우에만 새로운 가계부를 tb_book 테이블에 삽입합니다.
   • DuplicateCheck의 결과에서 is_duplicate = false인 경우에만 실행됩니다.
   • 삽입된 가계부의 id를 반환합니다.
3. 최종 SELECT: 삽입된 가계부의 id와 중복 여부를 반환합니다.
   • InsertBook과 DuplicateCheck의 결과를 각각 추출하여 반환합니다.

Rust 코드와 매핑

#[derive(Debug, sqlx::FromRow)]
struct InsertResult {
    id: Option<i32>,
    is_duplicated: bool,
}

impl InsertResult {
    pub fn get_id(&self) -> Option<i32> {
        self.id
    }
    pub fn get_duplicated(&self) -> bool {
        self.is_duplicated
    }
}

• InsertResult 구조체: 쿼리 결과를 매핑하기 위한 구조체입니다.
  • id: 삽입된 가계부의 ID입니다. 중복으로 인해 삽입되지 않은 경우 None이 됩니다.
  • is_duplicated: 중복 여부를 나타내는 불리언 값입니다.

핸들러 함수 구현

async fn create_book(
    Extension(pool): Extension<PgPool>,
    Json(payload): Json<CreateBook>,
) -> Result<impl IntoResponse, AppError> {
    let result = sqlx::query_as::<_, InsertResult>(
        include_str!("queries/create_book.sql")
    )
    .bind(&payload.name)
    .bind(payload.type_id)
    .fetch_one(&pool)
    .await?;

    if result.get_duplicated() {
        return Err(AppError::ValidationError("이미 동일한 이름의 가계부가 존재합니다.".into()));
    }

    Ok((StatusCode::CREATED, Json(result)))
}

• 쿼리 실행: query_as를 사용하여 쿼리를 실행하고 InsertResult 구조체로 매핑합니다.
• 중복 처리: is_duplicated 값이 true인 경우 에러를 반환합니다.
• 성공 응답: 새로운 가계부의 ID를 포함하여 응답합니다.

4. UNIQUE 제약 조건과 ON CONFLICT 활용

중복 처리를 위해 테이블에 UNIQUE 제약 조건을 설정하고, ON CONFLICT 구문을 사용할 수 있습니다.

테이블에 UNIQUE 제약 조건 추가

ALTER TABLE tb_book
ADD CONSTRAINT unique_book_name UNIQUE (name);

INSERT 문에서 ON CONFLICT 사용

INSERT INTO tb_book (name, type_id)
VALUES ($1, $2)
ON CONFLICT (name) DO NOTHING
RETURNING id;

• 설명: name 컬럼에 중복이 발생하면 아무 작업도 수행하지 않습니다.
• RETURNING: 삽입된 행의 id를 반환합니다. 중복으로 인해 삽입되지 않은 경우 RETURNING은 결과를 반환하지 않습니다.

Rust 코드에서 처리

async fn create_book(
    Extension(pool): Extension<PgPool>,
    Json(payload): Json<CreateBook>,
) -> Result<impl IntoResponse, AppError> {
    let rec = sqlx::query_as::<_, Book>(
        "INSERT INTO tb_book (name, type_id)
         VALUES ($1, $2)
         ON CONFLICT (name) DO NOTHING
         RETURNING id, name, type_id"
    )
    .bind(&payload.name)
    .bind(payload.type_id)
    .fetch_optional(&pool)
    .await?;

    match rec {
        Some(book) => Ok((StatusCode::CREATED, Json(book))),
        None => Err(AppError::ValidationError("이미 동일한 이름의 가계부가 존재합니다.".into())),
    }
}

• fetch_optional: 결과가 없을 수 있으므로 Option으로 반환합니다.
• 중복 처리: 결과가 None인 경우 중복으로 인해 삽입되지 않은 것이므로 에러를 반환합니다.

---

4-2. 인덱스의 개념과 적용 방법

1. 인덱스란 무엇인가?

인덱스(Index)는 데이터베이스에서 테이블의 데이터를 효율적으로 검색하기 위한 데이터 구조입니다. 책의 목차와 유사하게, 특정 컬럼에 대한 인덱스를 생성하면 검색 속도를 크게 향상시킬 수 있습니다.

2. 인덱스의 장점과 단점

• 장점
  • 검색 및 조회 속도 향상
  • 정렬 및 그룹화 작업 성능 향상
• 단점
  • 데이터 삽입, 수정, 삭제 시 추가적인 작업 발생
  • 디스크 공간 추가 사용

3. 인덱스 생성 방법

기본 인덱스 생성

CREATE INDEX idx_tb_book_name ON tb_book (name);

• idx_tb_book_name: 인덱스의 이름입니다.
• tb_book: 인덱스를 적용할 테이블입니다.
• (name): 인덱스를 생성할 컬럼입니다.

4. 인덱스 적용 시 고려사항

• 조회 빈도가 높은 컬럼에 적용: 자주 조회하거나 조인에 사용되는 컬럼에 인덱스를 생성합니다.
• 데이터 중복도가 낮은 컬럼 선택: 중복된 값이 많은 컬럼은 인덱스 효율이 떨어집니다.
• 과도한 인덱스 생성 지양: 너무 많은 인덱스는 데이터 변경 시 성능 저하를 유발합니다.

---

4-3. 복잡한 조인과 서브쿼리 작성 방법

1. 조인의 종류

• INNER JOIN: 두 테이블에서 조인 조건을 만족하는 행만 반환합니다.
• LEFT JOIN: 왼쪽 테이블의 모든 행과 오른쪽 테이블의 조인 조건을 만족하는 행을 반환합니다.
• RIGHT JOIN: 오른쪽 테이블의 모든 행과 왼쪽 테이블의 조인 조건을 만족하는 행을 반환합니다.
• FULL OUTER JOIN: 두 테이블의 모든 행을 반환하며, 조인 조건을 만족하지 않는 행은 NULL로 채워집니다.
• CROSS JOIN: 두 테이블의 모든 조합을 반환합니다. (데카르트 곱)

2. CROSS JOIN 사용 예시

CROSS JOIN은 두 테이블의 모든 행을 조합하여 반환합니다. 주의해서 사용해야 하며, 필요한 경우에만 활용합니다.

예제: 모든 가계부와 모든 카테고리의 조합

SELECT b.name AS book_name, c.name AS category_name
FROM tb_book AS b
CROSS JOIN tb_category AS c;

• 설명: 모든 가계부와 모든 카테고리의 조합을 생성합니다.
• 주의: 결과 행 수가 급격히 증가할 수 있으므로 필요할 때만 사용합니다.

3. 조인 사용 예시

예제: 가계부와 사용자 역할 정보 조인

SELECT b.id AS book_id, b.name AS book_name, ubr.user_id, ubr.role
FROM tb_book AS b
INNER JOIN tb_user_book_role AS ubr ON b.id = ubr.book_id
WHERE ubr.user_id = $1;

• 설명: 특정 사용자가 소유하거나 참여한 가계부와 그 역할을 조회합니다.

4. 서브쿼리 사용 방법

예제: 특정 사용자가 소유한 가계부의 수 계산

SELECT u.id, u.name, (
    SELECT COUNT(*)
    FROM tb_user_book_role AS ubr
    WHERE ubr.user_id = u.id AND ubr.role = 'owner'
) AS book_count
FROM tb_user AS u;

• 설명: 각 사용자별로 소유한 가계부의 수를 계산하여 반환합니다.

---

4-4. 쿼리 성능 분석과 최적화 방법

1. 쿼리 성능 분석 도구

• EXPLAIN: 쿼리의 예상 실행 계획을 제공합니다. 쿼리를 실행하지 않습니다.
• EXPLAIN ANALYZE: 쿼리를 실제로 실행하고, 실제 실행 시간과 함께 실행 계획을 제공합니다.

2. EXPLAIN 및 EXPLAIN ANALYZE 사용 방법

• 개발 환경에서만 사용: 성능 튜닝을 위해 개발자가 직접 실행하여 분석합니다.
• SQL 클라이언트 사용: psql 등 데이터베이스 클라이언트를 통해 쿼리를 실행합니다.

EXPLAIN과 EXPLAIN ANALYZE의 차이

• EXPLAIN: 쿼리를 실제로 실행하지 않고, 예상되는 실행 계획을 보여줍니다.
• EXPLAIN ANALYZE: 쿼리를 실제로 실행하고, 실제 실행 시간과 함께 실행 계획을 제공합니다.

EXPLAIN ANALYZE 사용 시 주의사항

• 쿼리가 실제로 실행되므로 데이터에 영향을 줄 수 있습니다. (예: INSERT, UPDATE, DELETE 쿼리)
• 실행 시간이 오래 걸리거나 시스템 부하가 큰 쿼리의 경우 성능 저하를 유발할 수 있습니다.
• 운영 환경에서는 사용을 지양하고, 필요할 경우 개발 환경에서만 사용하도록 합니다.

예제: 쿼리 실행 계획 확인

EXPLAIN
SELECT b.id, b.name
FROM tb_book AS b
WHERE b.name = '가계부1';

예제: 실제 실행 시간 분석

EXPLAIN ANALYZE
SELECT b.id, b.name
FROM tb_book AS b
WHERE b.name = '가계부1';

3. Rust에서 EXPLAIN 사용하기

Rust 애플리케이션에서 EXPLAIN을 사용하여 쿼리의 실행 계획을 받아올 수 있습니다.

예제: Rust에서 EXPLAIN 결과 가져오기

개발 단계에서 쿼리의 실행 계획을 분석하기 위해 EXPLAIN 또는 EXPLAIN ANALYZE를 사용할 수 있습니다. 하지만 운영 환경의 코드에서는 EXPLAIN을 사용하지 않는 것이 일반적입니다.

async fn explain_query(
    Extension(pool): Extension<PgPool>,
    Json(payload): Json<QueryPayload>,
) -> Result<impl IntoResponse, AppError> {
    let rows = sqlx::query_scalar::<_, String>(
        "EXPLAIN ANALYZE SELECT * FROM tb_book WHERE name = $1"
    )
    .bind(&payload.name)
    .fetch_all(&pool)
    .await?;

    let explain_result = rows.join("\n");

    Ok(Json(json!({ "explain": explain_result })))
}

• query_scalar: 단일 컬럼의 스칼라 값을 가져올 때 사용합니다.
• fetch_all: 모든 행을 가져옵니다.
• 결과 처리: String 값의 벡터로 반환되므로 이를 합쳐서 전체 실행 계획을 구성합니다.

EXPLAIN 결과 예시

{
  "explain": "Index Scan using idx_tb_book_name on tb_book b  (cost=0.28..8.30 rows=1 width=72)\n  Index Cond: (name = '가계부1')"
}

• 설명: 실행 계획을 JSON 형태로 반환하여 애플리케이션에서 활용할 수 있습니다.

4. 실행 계획 해석

• Index Scan: 인덱스를 사용하여 데이터를 검색합니다.
• Seq Scan: 전체 테이블을 순차적으로 스캔합니다.
• Cost: 쿼리 실행 비용을 나타내며, 앞의 숫자는 시작 비용, 뒤의 숫자는 총 비용입니다.
• Rows: 예상 결과 행 수입니다.
• Width: 예상 행의 평균 바이트 크기입니다.

5. 개발 환경에서 Rust를 통해 EXPLAIN ANALYZE 결과 가져오기

개발 환경에서 쿼리의 성능을 분석하기 위해 Rust 애플리케이션에서 EXPLAIN ANALYZE를 실행하고 결과를 가져올 수 있습니다.

use axum::extract::Extension;
use axum::Json;
use serde_json::json;
use sqlx::PgPool;
use crate::error::AppError;

#[derive(Deserialize)]
struct QueryPayload {
    query: String,
}

async fn explain_analyze_query(
    Extension(pool): Extension<PgPool>,
    Json(payload): Json<QueryPayload>,
) -> Result<Json<serde_json::Value>, AppError> {
    // 개발 환경에서만 실행하도록 제한
    if cfg!(not(debug_assertions)) {
        return Err(AppError::ValidationError(
            "EXPLAIN ANALYZE는 개발 환경에서만 사용할 수 있습니다.".into(),
        ));
    }

    let explain_query = format!("EXPLAIN ANALYZE {}", payload.query);

    let rows = sqlx::query_scalar::<_, String>(&explain_query)
        .fetch_all(&pool)
        .await
        .map_err(|e| AppError::DatabaseError(e.into()))?;

    let explain_result = rows.join("\n");

    Ok(Json(json!({ "explain_analyze": explain_result })))
}

• cfg!(not(debug_assertions)): 코드가 릴리즈 모드인지 확인하여, 개발 환경에서만 실행되도록 합니다.
• 쿼리 실행: 사용자가 입력한 쿼리에 EXPLAIN ANALYZE를 추가하여 실행합니다.
• 보안상 주의: 실제 애플리케이션에서는 사용자의 입력을 그대로 실행하는 것은 SQL 인젝션의 위험이 있으므로, 이 예제는 학습 목적으로만 사용해야 합니다.

EXPLAIN ANALYZE 결과 예시

{
  "explain_analyze": "Seq Scan on tb_book  
  (cost=0.00..1.05 rows=1 width=72) 
  (actual time=0.012..0.013 rows=0 loops=1)
  Filter: (name = '가계부1')
  Rows Removed by Filter: 10
  Planning Time: 0.100 ms
  Execution Time: 0.030 ms"
}

• actual time: 실제 실행 시간입니다.
• rows: 실제로 처리된 행의 수입니다.
• Execution Time: 전체 쿼리 실행 시간입니다.

5. 쿼리 최적화 기법

5.1. 적절한 인덱스 사용

• 쿼리에서 자주 사용되는 조건 컬럼에 인덱스를 생성합니다.
• 복합 조건에 맞는 복합 인덱스를 활용합니다.

5.2. 불필요한 데이터 조회 최소화

• 필요한 컬럼만 선택적으로 조회합니다.
• 서브쿼리 대신 조인을 사용하여 불필요한 중첩을 피합니다.

5.3. 쿼리 재작성

• 쿼리 구조를 변경하여 효율적인 실행 계획을 유도합니다.
• CTE나 뷰를 활용하여 복잡한 로직을 단순화합니다.

6. 예제: 쿼리 최적화 적용

원래 쿼리

SELECT *
FROM tb_book
WHERE name LIKE '%가계부%'
ORDER BY created_at DESC;

문제점

• LIKE '%...%' 패턴은 인덱스를 사용할 수 없어 전체 테이블 스캔을 유발합니다.

해결 방법

• Full-Text Search 기능을 사용하여 인덱스를 활용합니다.
• GIN 인덱스를 생성하여 검색 성능을 향상시킵니다.

-- 확장 기능 설치 (최초 1회)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- GIN 인덱스 생성
CREATE INDEX idx_tb_book_name_gin ON tb_book USING GIN (name gin_trgm_ops);

개선된 쿼리

SELECT *
FROM tb_book
WHERE name ILIKE '%가계부%'
ORDER BY created_at DESC;

• ILIKE와 GIN 인덱스를 함께 사용하여 대소문자 구분 없는 검색을 효율적으로 수행합니다.

---

결론

이번 글에서는 CTE를 활용한 복잡한 쿼리 작성, 인덱스를 통한 성능 향상, 복잡한 조인과 서브쿼리 작성 방법, 쿼리 성능 분석과 최적화 기법을 살펴보았습니다. 특히, CTE를 사용하여 중복 체크 및 조건부 삽입을 구현하고, UNIQUE 제약 조건과 ON CONFLICT 구문을 활용하여 중복 처리를 효율화하는 방법을 배웠습니다.

또한, EXPLAIN과 EXPLAIN ANALYZE를 Rust 애플리케이션에서 활용하여 쿼리의 실행 계획을 분석하고 성능을 최적화하는 방법을 알아보았습니다. 이러한 고급 기법들을 잘 활용하면 데이터베이스 작업의 효율성을 크게 높일 수 있습니다.

---

참고 자료

• PostgreSQL 공식 문서 - CTE
• PostgreSQL 공식 문서 - 인덱스
• PostgreSQL 공식 문서 - EXPLAIN
• SQL 튜닝 가이드
• Rust SQLx Documentation
• PostgreSQL GIN Indexes and Full-Text Search

---

주의: 이 글은 학습 목적을 위해 작성되었으며, 실제 환경에서는 데이터베이스 성능 최적화를 위해 더 많은 고려 사항이 필요합니다. 테스트 환경에서 충분히 검증한 후 프로덕션 환경에 적용하시기 바랍니다.