MySQL 핵심 기능 및 쿼리 최적화

1. 주요 내장 함수 활용

MySQL 은 다양한 데이터 처리를 위한 내장 함수를 제공합니다. 데이터 타입 변환에는 CAST() 를 사용하며, 문자열 패딩에는 LPAD() 및 RPAD() 함수를 활용할 수 있습니다. 소수점 처리 시 FORMAT() 은 문자열을 반환하고 반올림을 수행하며, ROUND() 는 수치 연산에 적합합니다. TRUNCATE() 는 버림 처리를 위해 사용됩니다.

2. 조인 (JOIN) 조건과 필터링의 차이

LEFT JOIN 수행 시 ON 절과 WHERE 절의 동작 방식에는 중요한 차이가 있습니다. ON 절의 조건은 임시 테이블을 생성하는 단계에서 적용되며, 좌측 테이블의 모든 레코드를 유지하려는 LEFT JOIN 의 특성을 해치지 않습니다. 반면 WHERE 절은 조인이 완료된 결과셋에 대해 필터링을 수행하므로, 좌측 테이블의 데이터라도 조건에 맞지 않으면 제외됩니다.

예를 들어, 좌측 테이블의 모든 데이터를 유지하면서 우측 테이블의 특정 조건만 반영하려면 ON 절에 조건을 기술해야 합니다.

3. 재귀 쿼리 (WITH RECURSIVE)

계층적 데이터나 순차적 번호 생성 시 WITH RECURSIVE 구문을 사용합니다. Oracle 의 CONNECT BY 와 유사한 기능으로, MySQL 8.0 이상에서 지원합니다.

WITH RECURSIVE seq_num(val) AS (
    SELECT 1
    UNION ALL
    SELECT val + 1 FROM seq_num WHERE val < 100
)
SELECT SUM(val) FROM seq_num;

4. 권한 관리 로직 구현

SQL 레벨에서 사용자 권한을 검증할 때는 생성자 확인과 권한 매핑 테이블을 함께 조회합니다. 다음 예시는 프로젝트 소유자이거나 권한 부여 테이블에 기록된 사용자만 접근하도록 제한합니다.

SELECT
    pm.id,
    pm.project_name
    FROM proj_master pm
    WHERE
    (pm.owner_id = #{currentUserId}
    OR EXISTS (
        SELECT 1 FROM proj_auth_map pam
        WHERE pam.proj_id = pm.id AND pam.user_id = #{currentUserId}
    ))
    ORDER BY pm.reg_date DESC;

PostgreSQL 고급 쿼리 기법

1. 문자열 및 배열 처리

PostgreSQL 은 강력한 배열 및 문자열 함수를 제공합니다. regexp_split_to_table 는 구분자를 기준으로 행을 분리하며, array_to_string 는 배열 요소를 단일 문자열로 변환합니다.

SELECT array_to_string(
    ARRAY (SELECT code_name FROM sys_code_map WHERE code_val IN (
        SELECT * FROM regexp_split_to_table(input_codes, ',')
    )), ','
) AS result_string;

2. 페이지네이션과 전체 카운트 동시 획득

윈도우 함수를 활용하면 추가 쿼리 없이 페이지 데이터와 전체 건수를 동시에 조회할 수 있습니다.

SELECT * FROM (
    SELECT
        ROW_NUMBER() OVER() AS row_num,
        COUNT(1) OVER() AS total_count,
        user_id
    FROM public.account_table
) sub
WHERE row_num BETWEEN 1 AND 10;

3. 조건부 집계 및 날짜 생성

FILTER 절을 사용하면 특정 조건에 맞는 행만 카운트할 수 있습니다. 또한 GENERATE_SERIES() 함수를 통해 특정 기간의 날짜 목록을 쉽게 생성할 수 있습니다.

-- 조건부 카운트
COUNT(*) FILTER (WHERE status <> 'CLOSED') AS active_count

-- 날짜 시리즈 생성
SELECT generate_series('2023-01-01', '2023-12-31', '1 month'::interval);

4. 중복 제거 및 정렬

DISTINCT ON 은 그룹별로 특정 기준의 첫 번째 행만 추출할 때 유용합니다. 정렬 시 여러 컬럼을 지정하면 우선순위에 따라 순차적으로 정렬이 적용됩니다.

다중 데이터베이스 호환성 설정

MySQL 과 PostgreSQL 을 동시에 지원하려면 마이바티스 (MyBatis) 의 databaseIdProvider 를 설정하여 SQL 문법을 동적으로 분기 처리해야 합니다.

1. 데이터소스 및 설정 클래스

Spring Boot 환경에서 데이터베이스 벤더 정보를 매핑하는 설정 클래스입니다.

@Configuration
public class DbConfig {
    @Bean
    public DatabaseIdProvider databaseIdProvider() {
        VendorDatabaseIdProvider provider = new VendorDatabaseIdProvider();
        Properties props = new Properties();
        props.setProperty("MySQL", "mysql");
        props.setProperty("PostgreSQL", "postgresql");
        provider.setProperties(props);
        return provider;
    }
}

2. XML 매퍼 분기 처리

마이바티스 XML 에서 _databaseId 변수를 사용하여 데이터베이스 종류에 따라 다른 SQL 을 실행합니다.

<select id="selectList" resultType="com.core.domain.User">
    <choose>
        <when test="_databaseId == 'mysql'">
            SELECT * FROM users LIMIT #{offset}, #{limit}
        </when>
        <when test="_databaseId == 'postgresql'">
            SELECT * FROM users OFFSET #{offset} ROWS FETCH NEXT #{limit} ROWS ONLY
        </when>
    </choose>
</select>

3. 날짜 함수 호환성

데이터베이스별 날짜 처리 방식 차이도 choose 태그로 해결합니다.

<choose>
    <when test="_databaseId == 'mysql'">
        WHERE DATE(create_time) = #{date}
    </when>
    <when test="_databaseId == 'postgresql'">
        WHERE create_time::date = #{date}
    </when>
</choose>