MySQL 인덱스 설계와 쿼리 성능 최적화

1. 인덱스 기초

1.1 인덱스란

MySQL에서 인덱스는 데이터를 빠르게 을 수 있도록 정렬된 자료구조입니다. 의 목차나 사전의 색인처럼, 원하는 데이터의 위치를 바로 찾아가게 해주는 역할을 합니다.

인덱스가 없다면 테이블 전체를 스캔해야 하지만, 인덱스가 있으면 대상 레코드가 있는 위치로 바로 이할 수 있습니다. 결과적으로 WHERE 조건 필터링과 ORDER BY 정렬 성능에 큰 영향을 줍니다.

일반적으로 인덱스는 크기가 크기 때문에 모두 메모리에 올리기 어려우며, 별도의 인덱스 파일 형태로 디스크에 저장됩니다. MySQL에서 말하는 인덱스는 대부분 B+트리(B+Tree) 구조를 기반으로 하며, Hash, Full-text, R-Tree 등의 인덱스도 존재합니다.

1.2 인덱스 장단점

장점

  • 검색 성능 향상: 불필요한 디스크 I/O 감소
  • 정렬 성능 향상: ORDER BY 시 CPU와 I/O 비용 절감

단점

  • 가 저장 공간 필요
  • INSERT, UPDATE, DELETE 시 인덱스 재구성으로 인한 쓰기 비용 가
  • 잘못 설계된 인덱스는 오히려 성능 저하 유발

2. 인덱스 분류와 문법

2.1 인덱스 종류

  • 단일 인덱스: 하나의 컬럼만 포함
  • 유니크 인덱스: 값이 중복되지 않음, NULL은 허용
  • 복합 인덱스: 두 개 이상의 컬을 조합

한 테이블에 생성하는 인덱스는 가능한 5개 이내로 제약하는 것이 좋습니다.

2.2 기본 문법

-- 인덱스 생성
CREATE [UNIQUE] INDEX index_name ON table_name(column_list);
ALTER TABLE table_name ADD [UNIQUE] INDEX index_name(column_list);

-- 인덱스 삭제
DROP INDEX index_name ON table_name;

-- 인덱스 조회
SHOW INDEX FROM table_name \G;

-- ALTER로 인덱스 추가
ALTER TABLE table_name ADD PRIMARY KEY(column_list);
ALTER TABLE table_name ADD UNIQUE INDEX index_name(column_list);
ALTER TABLE table_name ADD INDEX index_name(column_list);
ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_list);

3. 인덱스 자료구조

  • B+Tree 인덱스: 범위 검색과 정렬에 유리, MySQL의 기본 인덱스
  • Hash 인덱스: 등호 조건에서 빠른 탐색
  • Full-text 인덱스: 텍스트 검색
  • R-Tree 인덱스: 공간 데이터 처리

4. 인덱스 설계 기준

4.1 인덱스를 고려하는 경우

  • 기본키(PRIMARY KEY)는 자동으로 인덱스 생성
  • WHERE 에 자주 사용되는 컬럼
  • JOIN 조건으로 사용되는 외래키
  • ORDER BY, GROUP BY에 자주 사용되는 컬럼

4.2 인덱스를 피하는 경우

  • 데이터 건수가 매우 적은 테이블
  • 빈번한 INSERT/UPDATE/DELETE가 발생하는 테이블
  • WHERE 조건에 사용되지 않는 컬
  • 선택도가 낮은 컬럼(예: true/false가 50%씩 분포)

5. 쿼리 성능 분석

5.1 MySQL Query Optimizer

MySQL은 SELECT 문을 실행하기 전 Query Optimizer를 통해 통계 정보를 분석하고, 최적의 실행 계획을 수립합니다. 상수 표현식의 단순화, 불필요한 조건 제거, Hint 해석, 통계 정보 기반 비용 계산 등을 수행합니다.

5.2 병목 현상

  • CPU: 메모리 로딩, 대량 데이터 처리 시 발생
  • I/O: 메모리보다 큰 데이터를 디스크에서 읽을 때
  • 하드웨어: top, free, iostat, vmstat 등으로 확인

5.3 EXPLAIN

EXPLAIN은 SQL의 실행 계을 확인할 수 있는 명령어입니다.

EXPLAIN SELECT * FROM member \G;

주요 출력 컬럼:

  • id: 테이블 읽기 순서
  • select_type: 쿼리 유형(SIMPLE, PRIMARY, SUBQUERY 등)
  • type: 접근 방식(system > const > eq_ref > ref > range > index > ALL)
  • possible_keys: 사용 가능한 인덱스
  • key: 실제 사용된 인덱스
  • key_len: 사용된 인덱스 길이
  • ref: 인덱스 참조 조건
  • rows: 예상 조회 행 수
  • Extra: 추가 정보(Using filesort, Using temporary, Using index 등)

type 상세

  • system: 1건만 있는 테이블
  • const: PK/유니크 인덱스로 단 1건 조회
  • eq_ref: 조인에서 한 컬럼이 유일한 값과 매칭
  • ref: 비유니크 인덱스로 여러 건 매칭
  • range: 위 검색
  • index: 전체 인덱스 스캔
  • ALL: 전체 테이블 스캔

Extra 주요 신호

  • Using filesort: 인덱스 없이 정렬 발생
  • Using temporary: 임시 테이블 사용
  • Using index: 커버링 인덱스 사용
  • Using where: WHERE 조건으로 필터링
  • impossible where: 항상 거짓인 WHERE

6. 인덱스 분석 예제

6.1 단일 테이블

게시글 테이블을 만들고 조회합니다.

DROP TABLE IF EXISTS posts;

CREATE TABLE posts (
    id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    board_id INT NOT NULL,
    reply_count INT NOT NULL,
    view_count INT NOT NULL,
    title VARCHAR(255) NOT NULL
);

INSERT INTO posts(board_id, reply_count, view_count, title) VALUES
(1, 1, 10, 'A'),
(1, 2, 30, 'B'),
(1, 5, 20, 'C'),
(2, 1, 50, 'D');

아래 쿼리는 board_id=1이고 reply_count>1인 데이터를 view_count 기준으로 정렬합니다.

EXPLAIN SELECT id, board_id FROM posts
WHERE board_id = 1 AND reply_count > 1
ORDER BY view_count DESC LIMIT 1 \G;

기 상태에서는 ALL(전체 스캔)과 Using filesort가 발생합니다. 복합 인덱스를 추가하면:

CREATE INDEX idx_posts_brv ON posts(board_id, reply_count, view_count);

하지만 reply_count에 부등호 조건이 들어가면 그 뒤의 view_count는 정렬에 인덱스를 활용하지 못합니다. 범위 조건 의 인덱스는 정렬에 사용되지 않기 때문입니다. 따라서 다음과 같이 인덱스를 재구성하는 것이 더 효율적입니다.

DROP INDEX idx_posts_brv ON posts;
CREATE INDEX idx_posts_bv ON posts(board_id, view_count);

6.2 두 테이블 조인

CREATE TABLE category (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code INT NOT NULL
);

CREATE TABLE product (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code INT NOT NULL
);

LEFT JOIN에서 드라이빙 테이블의 조인 컬럼에 인덱스를 거는 것보다, 드리븐 테이블의 조인 컬럼에 인덱스를 거는 것이 일반적으로 유리합니다.

-- LEFT JOIN 시 product.code에 인덱스
CREATE INDEX idx_product_code ON product(code);

-- RIGHT JOIN 시 category.code에 인덱스
CREATE INDEX idx_category_code ON category(code);

6.3 세 테이블 조인

세 테이블 이상 조인 시에도 마찬가지로 드리븐 쪽 테이블의 조인 컬럼에 인덱스를 배치하면 Nested Loop 횟수를 줄일 수 있습니다.

CREATE TABLE stock (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code INT NOT NULL
);

CREATE INDEX idx_product_code ON product(code);
CREATE INDEX idx_stock_code ON stock(code);

7. 인덱스를 못 타는 경우

7.1 대표적인 실패 사례

  • 복합 인덱스의 최좌측 컬럼 누락
  • 인덱스 컬럼에 함수나 연산 적용
  • 범위 조건 이후의 인스 컬럼
  • SELECT * 사용으로 인한 커버링 인덱스 미활용
  • !=, <> 사용
  • IS NULL / IS NOT NULL
  • LIKE '%abc' 형태의 와일드카드
  • 문자열 컬럼에 따옴표 없이 숫자 비교
  • OR 조건

7.2 최좌 전缀 규칙

복합 인덱스는 선두 컬럼부터 순서대로 사용해야 합니다. 마치 다층 건물의 1층을 거야 2층으로 갈 수 있는 것과 같습니다.

CREATE INDEX idx_member_nap ON member(name, age, role);

-- 사용 가능
EXPLAIN SELECT * FROM member WHERE name = 'kim';
EXPLAIN SELECT * FROM member WHERE name = 'kim' AND age = 30;
EXPLAIN SELECT * FROM member WHERE name = 'kim' AND age = 30 AND role = 'dev';

-- 인덱스 사용 불가 또는 부분 사용
EXPLAIN SELECT * FROM member WHERE age = 30;
EXPLAIN SELECT * FROM member WHERE role = 'dev';
EXPLAIN SELECT * FROM member WHERE name = 'kim' AND role = 'dev';

7.3 인덱스 컬럼에 함수 적용

-- 인덱스 사용
EXPLAIN SELECT * FROM member WHERE name = 'kim';

-- 함수 적용 시 인덱스 사용 불가
EXPLAIN SELECT * FROM member WHERE LEFT(name, 3) = 'kim';

7.4 위 조건 이후 인덱스 무효화

-- name, age, pos 모두 인덱스 사용
EXPLAIN SELECT * FROM member WHERE name = 'kim' AND age = 30 AND role = 'dev';

-- age 범위 조건 이후 role은 인덱스 사용 불가
EXPLAIN SELECT * FROM member WHERE name = 'kim' AND age > 20 AND role = 'dev';

7.5 커버링 인덱스

SELECT 에 인덱스 컬럼만 포함되면 테이블 본체를 조회하지 않아도 됩니다. 이를 커버링 인덱스라고 하며, SELECT * 대신 필요한 컬만 명시하는 습관이 중요합니다.

-- 커버링 인덱스 미사용
EXPLAIN SELECT * FROM member WHERE name = 'kim' AND age = 30 AND role = 'dev';

-- 커버링 인덱스 사용
EXPLAIN SELECT name, age, role FROM member
WHERE name = 'kim' AND age = 30 AND role = 'dev';

7.6 LIKE 패턴

-- 전체 스캔
EXPLAIN SELECT * FROM member WHERE name LIKE '%kim%';
EXPLAIN SELECT * FROM member WHERE name LIKE '%kim';

-- 인덱스 사용
EXPLAIN SELECT * FROM member WHERE name LIKE 'kim%';

와일드카드를 에 쓰면 인덱스를 사용할 수 없지만, 필요한 경우 커버링 인덵스로 완전 회피는 어우나 일부 성능 개선이 가능합니다.

7.7 암시적 타입 변환

-- 인덱스 사용
EXPLAIN SELECT id, name FROM member WHERE name = 'kim';

-- 문자열 컬럼에 숫자 비교: 시적 형변환으로 인덱스 실패
EXPLAIN SELECT * FROM member WHERE name = 1000;

8. 실전 문제 풀이

다음과 같은 복합 인덱스를 가정합니다.

CREATE TABLE idx_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    c1 CHAR(10),
    c2 CHAR(10),
    c3 CHAR(10),
    c4 CHAR(10),
    c5 CHAR(10)
);

CREATE INDEX idx_demo_c1234 ON idx_demo(c1, c2, c3, c4);

쿼리별 인덱스 사용 여부를 살봅니다.

-- c1, c2, c3, c4 전체 사용
EXPLAIN SELECT * FROM idx_demo
WHERE c1 = 'a' AND c2 = 'b' AND c3 = 'c' AND c4 = 'd';

-- Optimizer가 순서를 재배열하므로 c1, c2, c3, c4 전체 사용
EXPLAIN SELECT * FROM idx_demo
WHERE c4 = 'd' AND c3 = 'c' AND c2 = 'b' AND c1 = 'a';

-- c1, c2, c3까지 사용, c4는 범위 뒤로 무효
EXPLAIN SELECT * FROM idx_demo
WHERE c1 = 'a' AND c2 = 'b' AND c3 > 'c' AND c4 = 'd';

-- c1, c2, c3 사용, c4는 정렬은 되지만 key_len에 반영되지 않음
EXPLAIN SELECT * FROM idx_demo
WHERE c1 = 'a' AND c2 = 'b' ORDER BY c3;

-- c1, c2 사용, c4 정렬은 Using filesort 발생
EXPLAIN SELECT * FROM idx_demo
WHERE c1 = 'a' AND c2 = 'b' ORDER BY c4;

9. 인덱스 최적화 요약

  • 단일 인덱스는 필터링 효과가 큰 컬럼에 적용
  • 복합 인덱스는 필터링 효과가 큰 컬럼을 앞쪽에 배치
  • WHERE 절의 조건을 최대한 많이 포함할 수 있도록 설계
  • 통계 정보와 EXPLAIN을 활용해 실행 계획 점검
  • JOIN은 작은 결과집합이 큰 결과집합을 드라이빙하도록 유도

심 요약:

  • 최좌 컬럼은 반드시 사용
  • 중간 컬럼을 건너뛰면 인덱스 일부 무효
  • 인덱스 컬럼에 연산/함수 금지
  • 범위 조건 뒤 인덱스 무효
  • 커버링 인덱스 적극 활용
  • LIKE는 접두사 패턴 사용
  • 문자열은 따옴표 명시
  • OR, != 사용은 지양

태그: MySQL Index B+Tree EXPLAIN Query Optimization

9월 25일 09:12에 게시됨

인기 태그

©2026 괴물 클럽