SQL 쿼리 성능 최적화를 위한 핵심 전략

인덱스 설계 및 활용 전략

쿼리 성능을 개선하기 위해 가장 먼저 고려해야 할 사항은 인덱스의 적절한 사용입니다. WHERE 절과 ORDER BY 절에서 사용되는 컬럼에는 반드시 인덱스를 생성하는 것이 바람직합니다. 특히 복합 인덱스의 경우, 인덱스의 선두 컬럼이 조건문에 포함되어야만 인덱스가 효과적으로 활용됩니다. 또한 조건 필드의 순서는 인덱스 정의 시의 순서와 일치하도록 구성하는 것이 중요합니다.

NULL 값 처리 방식 개선

WHERE 조건에서 IS NULL 체크는 인덱스 스캔을 비활성화할 수 있습니다. 이를 방지하기 위해, 해당 컬럼에 기본값(예: 0 또는 빈 문자열)을 설정하고 다음과 같이 쿼리를 재작성하는 것이 좋습니다:

SELECT id FROM table_name WHERE status = 0;

이 방식은 인덱스 기반 검색을 유지하면서도 논리적 필터링을 가능하게 합니다.

비효율적인 연산자 피하기

다음과 같은 연산자는 풀 테이블 스캔을 유발할 수 있으므로 주의가 필요합니다:

  • != 또는 <> 연산자: 대신 범위 쿼리나 IN/EXISTS를 고려하세요.
  • OR 연결 조건: 아래 예제처럼 UNION ALL로 분리하면 인덱스를 유지할 수 있습니다.
SELECT id FROM table_name WHERE num = 10
UNION ALL
SELECT id FROM table_name WHERE num = 20;

IN과 BETWEEN의 효율적 선택

연속된 숫자 범위 조회 시 IN 대신 BETWEEN을 사용하면 성능이 향상됩니다.

-- 비효율적
SELECT id FROM table_name WHERE value IN (1, 2, 3);

-- 효율적
SELECT id FROM table_name WHERE value BETWEEN 1 AND 3;

패턴 매칭 최적화

접두사 없이 와일드카드를 사용하는 LIKE 쿼리는 인덱스를 무시합니다.

SELECT id FROM table_name WHERE name LIKE '%abc%';

이 경우 전체 테이블 스캔이 발생하며, 해결책으로는 MySQL의 풀텍스트 인덱스(FULLTEXT)를 사용하는 것이 권장됩니다.

파라미터화된 쿼리와 인덱스

런타임 파라미터를 사용하는 쿼리는 실행 계획 수립 시 인덱스 선택에 어려움을 겪을 수 있습니다. 이 문제를 완화하기 위해 인덱스 힌트를 명시적으로 지정할 수 있습니다.

SELECT id FROM table_name WITH (INDEX(idx_num)) WHERE num = @input_value;

함수 및 표현식 사용 제한

컬럼에 함수나 산술 연산을 적용하면 인덱스가 무효화됩니다.

-- 비효율적
SELECT id FROM table_name WHERE amount / 2 > 500;

-- 효율적
SELECT id FROM table_name WHERE amount > 1000;

비슷하게, SUBSTRING() 대신 LIKE 'prefix%'를 사용하면 인덱스를 활용할 수 있습니다.

데이터 타입 및 스키마 설계 최적화

성능에 영향을 미치는 스키마 설계 원칙:

  • 숫자 정보는 문자형보다 정수형으로 저장하세요. 비교 연산 시 성능 차이가 큽니다.
  • VARCHAR를 CHAR보다 우선 사용하여 저장 공간을 절약하세요.
  • 필요 없는 SELECT * 사용을 지양하고, 필요한 컬럼만 명시하세요.
  • ENUM은 제한된 값 집합에 대해 저장 효율과 성능을 동시에 개선합니다.
  • 가급적이면 모든 컬럼을 NOT NULL로 설정하고, NULL이 필요한 경우에만 허용하세요.

임시 테이블과 테이블 변수

대량의 임시 데이터 처리 시 테이블 변수(table variable)를 사용하면 로그 부하를 줄일 수 있지만, 인덱스 지원이 제한적임에 유의해야 합니다. 반복적으로 참조되는 대규모 데이터셋에는 임시 테이블이 적합할 수 있으나, 사용 후 반드시 다음 순서로 정리해야 합니다:

TRUNCATE TABLE #temp;
DROP TABLE #temp;

커서(Cursor) 사용 자제

행 단위 처리는 성능 저하의 주요 원인이 됩니다. 1만 건 이상의 데이터를 처리할 경우, 가능한 한 집합 기반 쿼리(SELECT, JOIN, EXISTS 등)로 리팩터링해야 합니다. 다만 소규모 결과집합에서는 FAST_FORWARD 커서가 타당할 수 있습니다.

EXISTS vs IN

서브쿼리 조건 시 EXISTS가 일반적으로 더 효율적입니다.

SELECT a.id FROM table_a a
WHERE EXISTS (SELECT 1 FROM table_b b WHERE b.ref_id = a.id);

특히 상관 서브쿼리에서 성능 이점이 두드러집니다.

클러스터형 인덱스 주의사항

클러스터형 인덱스는 데이터의 물리적 저장 순서를 결정하므로, 그 키 컬럼이 자주 갱신되면 큰 리소스 오버헤드가 발생합니다. 빈번한 UPDATE가 예상되는 컬럼은 클러스터형 인덱스 대상에서 배제하는 것이 좋습니다.

통계 정보 관리

쿼리 최적화기는 통계 정보에 의존하여 실행 계획을 수립합니다. 주기적으로 다음 명령어를 실행해 정확성을 유지해야 합니다:

ANALYZE TABLE table_name;

이 작업은 키워드 분포를 재분석하여 최적의 접근 경로를 도출하는 데 도움을 줍니다.

테이블 구조 최적화

대량의 삭제 또는 갱신 이후에는 공간 단편화가 발생할 수 있습니다. MyISAM, InnoDB 테이블의 경우 다음 명령으로 조각해결이 가능합니다:

OPTIMIZE TABLE table_name;

이 작업은 백그라운드에서 실행되며, 트래픽이 적은 시간대에 수행하는 것이 이상적입니다.

기타 실용 팁

  • 큰 트랜잭션은 동시성을 저하시키므로 가능한 작게 분할하세요.
  • 저장 프로시저 시작 시 SET NOCOUNT ON을 설정하여 네트워크 트래픽을 줄이세요.
  • 인덱스는 많을수록 좋은 것이 아닙니다. INSERT/UPDATE 성능 저하와 관리 오버헤드가 발생하므로, 6개 이하로 유지하는 것이 일반적입니다.
  • 중복 데이터가 많은 컬럼(예: 성별)에는 인덱스의 효과가 미미하므로 신중히 검토하세요.

태그: SQL optimization indexing strategy query performance Database Tuning MySQL optimization

6월 6일 17:18에 게시됨

인기 태그

©2026 괴물 클럽