MySQL 인덱스 활용 및 쿼리 최적화 전략 요약

1. LIKE 절의 효율적인 인덱스 활용

LIKE 연산자를 사용할 때 'keyword%'와 같이 우측에 와일드카드를 배치하면 인덱스 범위를 효과적으로 탐색할 수 있습니다. 반면, '%keyword'나 '%keyword%'는 인덱스 풀 스캔을 유발하므로 주의가 필요합니다.

MySQL 5.6 버전부터는 인덱스 조건 푸시다운(Index Condition Pushdown, ICP) 기능이 도입되어 성능이 개선되었습니다. 다음은 ICP가 적용되는 사례입니다.

-- (user_name, user_age, user_role) 복합 인덱스가 생성된 경우
EXPLAIN SELECT * FROM member_info 
WHERE user_name LIKE 'Kim%' 
  AND user_age = 30 
  AND user_role = 'admin';

가장 왼쪽 접두사(Leftmost Prefix) 원칙에 따라 user_name 인덱스가 사용됩니다. ICP가 활성화되면 스토리지 엔진 레벨에서 user_age와 user_role 조건까지 미리 체크하여, 조건을 만족하는 데이터만 서버 엔진으로 전달함으로써 테이블 풀 스캔(회표) 횟수를 획기적으로 줄입니다.

  • 커버링 인덱스를 활용하여 성능을 극대화합니다.
  • 와일드카드는 가능한 우측에만 배치합니다.
  • 대량의 텍스트 검색이 필요한 경우 전문 검색(Full-text Search) 엔진 사용을 고려합니다.

2. ORDER BY 최적화와 정렬 알고리즘

MySQL의 정렬 방식은 인덱스를 이용한 정렬(Using index)과 별도의 메모리 공간을 사용하는 파일 정렬(Using filesort)로 나뉩니다. 인덱스 구성과 ORDER BY 절이 일치할 때 최적의 성능을 냅니다.

-- 복합 인덱스 (col_a, col_b, col_c) 기준 예시
SELECT * FROM my_table WHERE col_a = 1 ORDER BY col_b; -- Using index
SELECT * FROM my_table WHERE col_a = 1 ORDER BY col_c; -- Using filesort (인덱스 순서 불일치)
SELECT * FROM my_table WHERE col_a = 1 ORDER BY col_b, col_c; -- Using index
SELECT col_a, col_b, col_c FROM my_table WHERE col_a > 10 ORDER BY col_a; -- Using index (커버링 인덱스)

파일 정렬은 max_length_for_sort_data 설정값에 따라 두 가지 방식으로 동작합니다.

  • 싱글 패스(Single-pass): 정렬에 필요한 모든 컬럼을 sort_buffer에 담아 정렬합니다. 메모리 사용량은 많지만 한 번에 결과를 반환할 수 있습니다.
  • 투 패스(Two-pass): 정렬 키와 행 ID(Row ID)만 sort_buffer에 담아 정렬한 뒤, 다시 테이블을 조회하여 데이터를 가져옵니다.

정렬 성능 최적화를 위해 GROUP BY 작업 시 정렬이 필요 없다면 ORDER BY NULL을 명시적으로 선언하는 것이 좋으며(구버전 호환), 가급적 인덱스 컬럼 내에서 정렬이 완료되도록 설계해야 합니다.

3. 조인(JOIN) 알고리즘의 이해

MySQL은 주로 두 가지 방식의 조인 알고리즘을 사용합니다.

Index Nested-Loop Join (NLJ)

조인 컬럼에 인덱스가 있는 경우 사용됩니다. 드라이빙 테이블(Driving Table)에서 한 행씩 읽어 피드라이빙 테이블(Driven Table)의 인덱스를 탐색하며 매칭되는 데이터를 찾습니다. 이때 데이터 규모가 작은 테이블을 드라이빙 테이블로 선택하는 것이 유리합니다.

Block Nested-Loop Join (BNL)

조인 컬럼에 인덱스가 없을 때 사용되는 방식입니다. 드라이빙 테이블의 데이터를 join_buffer에 담고, 피드라이빙 테이블을 풀 스캔하면서 버퍼 내 데이터와 매칭 여부를 확인합니다. 메모리 내 비교 연산이 발생하므로 NLJ보다 느립니다.

-- t2를 강제로 드라이빙 테이블로 지정하는 예시
SELECT * FROM table_small t2 STRAIGHT_JOIN table_large t1 ON t2.key_id = t1.key_id;

4. COUNT(*) 함수와 성능 차이

일반적으로 COUNT 함수의 성능 우선순위는 상황에 따라 미세하게 다르지만 다음과 같은 특성을 가집니다.

  • COUNT(*): MySQL 옵티마이저가 가장 최적화된 방식으로 행 수를 계산합니다. 값을 하나씩 읽지 않고 전체 카운트를 수행하므로 권장되는 방식입니다.
  • COUNT(1): COUNT(*)와 거의 동일하게 동작하며, 상수를 기준으로 행 수를 계산합니다.
  • COUNT(컬럼명): 해당 컬럼의 값이 NULL이 아닌 행만 계산합니다. 컬럼 데이터를 직접 읽어야 하므로 COUNT(*)보다 약간 느릴 수 있습니다.
  • COUNT(PK): 기본키 인덱스를 사용하여 카운트합니다. 최신 MySQL 버전에서는 보조 인덱스가 있다면 더 작은 파일 크기인 보조 인덱스를 사용하여 최적화합니다.

MyISAM 엔진과 달리 InnoDB 엔진은 행의 전체 개수를 별도로 저장하지 않으므로, 전체 카운트 시 인덱스 스캔이 필수적입니다. 데이터가 매우 많고 정확한 실시간 카운트가 필요하지 않다면 별도의 카운트 전용 캐시 테이블이나 Redis를 활용하는 전략이 필요합니다.

태그: MySQL Index QueryOptimization ExecutionPlan DatabasePerformance

5월 31일 01:54에 게시됨

인기 태그

©2026 괴물 클럽