인덱스의 개념, 종류, 생성/관리 방법과 효과적인 사용 전략을 정리합니다.

인덱스란?

인덱스는 테이블의 데이터를 빠르게 검색하기 위한 자료구조입니다.
책의 목차(색인)와 같은 역할로, 전체 데이터를 스캔하지 않고 원하는 데이터를 빠르게 찾을 수 있습니다.

인덱스 유무에 따른 차이

항목 인덱스 없음 (Full Table Scan) 인덱스 있음 (Index Scan)
검색 방식 테이블 전체를 순차 탐색 B-Tree 구조로 빠른 탐색
시간 복잡도 O(n) O(log n)
100만 건 검색 100만 행 스캔 약 20회 비교로 도달

인덱스 종류

종류 설명 생성 방법
PRIMARY KEY 기본키 인덱스, 자동 생성 PRIMARY KEY (id)
UNIQUE INDEX 중복 불가 인덱스 UNIQUE KEY uk_email (email)
INDEX (일반) 중복 허용 인덱스 INDEX idx_name (name)
FULLTEXT INDEX 전문 검색 인덱스 FULLTEXT INDEX ft_content (content)
COMPOSITE INDEX 복합 인덱스 (다중 컬럼) INDEX idx_dept_sal (dept_id, salary)

인덱스 생성 및 관리

생성

-- 테이블 생성 시
CREATE TABLE products (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price    DECIMAL(10,2),
    INDEX idx_category (category),
    INDEX idx_cat_price (category, price)
);

-- 기존 테이블에 추가
CREATE INDEX idx_name ON products (name);
CREATE UNIQUE INDEX uk_name ON products (name);

-- ALTER TABLE로 추가
ALTER TABLE products ADD INDEX idx_price (price);
ALTER TABLE products ADD FULLTEXT INDEX ft_name (name);

조회

-- 테이블의 인덱스 목록
SHOW INDEX FROM products;

-- 인덱스 사용 여부 확인 (EXPLAIN)
EXPLAIN SELECT * FROM products WHERE category = '전자제품';

삭제

DROP INDEX idx_name ON products;
ALTER TABLE products DROP INDEX idx_price;

EXPLAIN으로 실행 계획 확인

EXPLAIN SELECT * FROM products WHERE category = '전자제품' AND price > 10000;

EXPLAIN 주요 컬럼

컬럼 설명 좋은 값
type 접근 방식 const > eq_ref > ref > range > index > ALL
possible_keys 사용 가능한 인덱스 인덱스 이름
key 실제 사용된 인덱스 인덱스 이름 (NULL이면 미사용)
rows 예상 스캔 행 수 작을수록 좋음
Extra 추가 정보 Using index (커버링 인덱스)

type 값 상세

type 설명 성능
const PK/UNIQUE로 1건 조회 최고
eq_ref JOIN에서 PK/UNIQUE 매칭 매우 좋음
ref 일반 인덱스 매칭 좋음
range 인덱스 범위 스캔 보통
index 인덱스 전체 스캔 나쁨
ALL 테이블 전체 스캔 최악

복합 인덱스와 최좌선 원칙

복합 인덱스는 컬럼 순서가 매우 중요합니다.
INDEX idx_abc (a, b, c) 인덱스가 있을 때:

WHERE 조건 인덱스 사용 여부
WHERE a = 1 ✅ 사용
WHERE a = 1 AND b = 2 ✅ 사용
WHERE a = 1 AND b = 2 AND c = 3 ✅ 사용 (전체)
WHERE b = 2 ❌ 미사용
WHERE b = 2 AND c = 3 ❌ 미사용
WHERE a = 1 AND c = 3 ⚠️ a만 사용

핵심: 복합 인덱스는 왼쪽 컬럼부터 순서대로 사용됩니다 (Leftmost Prefix Rule).

인덱스가 사용되지 않는 경우

-- ❌ 컬럼에 함수/연산 적용
SELECT * FROM employees WHERE YEAR(hire_date) = 2024;
-- ✅ 범위 조건으로 변경
SELECT * FROM employees WHERE hire_date >= '2024-01-01' AND hire_date < '2025-01-01';

-- ❌ LIKE 앞쪽 와일드카드
SELECT * FROM products WHERE name LIKE '%노트북';
-- ✅ 앞쪽 고정
SELECT * FROM products WHERE name LIKE '삼성%';

-- ❌ 묵시적 타입 변환
SELECT * FROM products WHERE price = '10000';  -- price가 DECIMAL인데 문자열 비교
-- ✅ 올바른 타입 사용
SELECT * FROM products WHERE price = 10000;

-- ❌ OR 조건 (각 컬럼에 개별 인덱스 필요)
SELECT * FROM products WHERE category = 'A' OR price > 10000;

-- ❌ NOT, != , <> 조건
SELECT * FROM products WHERE category != '전자제품';

인덱스 설계 가이드라인

원칙 설명
WHERE 절에 자주 사용되는 컬럼 검색 조건에 자주 등장하는 컬럼에 인덱스 생성
카디널리티가 높은 컬럼 우선 고유 값이 많은 컬럼(이메일, ID)이 효과적
JOIN 조건 컬럼 FK 등 JOIN에 사용되는 컬럼에 인덱스 필수
ORDER BY / GROUP BY 컬럼 정렬/그룹핑에 사용되는 컬럼
과도한 인덱스 지양 INSERT/UPDATE/DELETE 성능 저하 원인
커버링 인덱스 활용 SELECT 컬럼까지 인덱스에 포함하면 테이블 접근 불필요 
-- 커버링 인덱스 예시
CREATE INDEX idx_cover ON employees (dept_id, salary, name);

-- 이 쿼리는 테이블에 접근하지 않고 인덱스만으로 처리 (Using index)
SELECT dept_id, salary, name FROM employees WHERE dept_id = 1;

관련된 글 (mysql > lecture-mysql)

참고