인덱스 설계와 사용법

인덱스 파워링크

바로가기	볼 내용
개요	인덱스 전체 지도, 상황별 바로가기
구조와 종류	B+Tree, 클러스터/보조 인덱스, 물리 동작
B-Tree VS B+Tree	B-Tree와 B+Tree 구조 차이, DB가 B+Tree를 쓰는 이유
설계와 사용법	커버링 인덱스, 카디널리티, CREATE/ALTER/DROP
쿼리 패턴	연산자별 동작, 복합 인덱스, ESR 규칙
운영과 튜닝	고급 기법, 힌트, 통계, 모니터링, 베스트 프랙티스

커버링 인덱스 (Covering Index)

커버링 인덱스: 쿼리가 필요로 하는 모든 컬럼이 인덱스에 포함되어 있어, ==테이블 데이터에 접근하지 않고 인덱스만으로 결과를 반환==하는 것. Secondary Index의 Double Lookup을 제거하여 디스크 I/O를 대폭 줄인다.

Bad — Double Lookup 발생

-- idx_order_member_date: (member_id, created_at)
SELECT * FROM orders
WHERE member_id = 1;

인덱스로 PK를 찾고 → PK로 다시 클러스터 인덱스를 조회한다.

Good — 커버링 인덱스

-- idx_order_member_date: (member_id, created_at)
SELECT member_id, created_at FROM orders
WHERE member_id = 1;

필요한 컬럼이 모두 인덱스에 있으므로 테이블 접근 없이 응답한다.

EXPLAIN으로 확인할 때 Extra 컬럼에 ==Using index==가 표시되면 커버링 인덱스가 사용된 것이다.

EXPLAIN SELECT member_id, created_at FROM orders WHERE member_id = 1;
-- Extra: Using index  ← 커버링 인덱스 사용 중

주의: SELECT *를 남발하면 커버링 인덱스를 활용할 수 없다. 필요한 컬럼만 명시적으로 SELECT하는 습관이 중요하다.

카디널리티와 선택도

카디널리티(Cardinality): 해당 컬럼의 고유 값 개수. 선택도(Selectivity): 카디널리티 / 전체 행 수. 선택도가 1에 가까울수록 인덱스 효과가 크다.

컬럼	전체 행 수	카디널리티	선택도	인덱스 효과
email (UNIQUE)	1,000,000	1,000,000	1.0	매우 높음
created_at	1,000,000	365,000	0.365	높음
gender (M/F)	1,000,000	2	0.000002	거의 없음

-- 카디널리티 확인
SHOW INDEX FROM member;
-- Cardinality 컬럼에 추정값이 표시된다 (InnoDB는 통계 기반 추정)

-- 통계 갱신
ANALYZE TABLE member;

주의: 인덱스로 조회 시 전체 행의 약 5~10% 이하만 읽을 때 인덱스가 효과적이다. 그 이상이면 옵티마이저가 오히려 Full Scan을 선택할 수 있다.

팁: 단일 컬럼의 카디널리티가 낮더라도 복합 인덱스로 조합하면 결합 카디널리티가 높아질 수 있다. 예를 들어 gender(2종류)와 age(100종류)를 조합하면 최대 200종류까지 올라간다.

언제 쓰는지

인덱스를 걸면 좋은 컬럼:

• WHERE 조건에 자주 등장하는 컬럼
• JOIN 조건에 쓰이는 컬럼 (FK 컬럼)
• ORDER BY, GROUP BY 에 쓰이는 컬럼
• 카디널리티(Cardinality)가 높은 컬럼 — 값의 종류가 많을수록 효과적

주의: 성별(M/F), 상태(Y/N) 같이 값의 종류가 2~3개뿐인 컬럼은 인덱스 효과가 거의 없다. 전체 행의 20~30% 이상을 읽어야 한다면 Full Scan이 더 빠를 수 있다.

사용 방법 (CRUD)

기본 문법 구조

CREATE [UNIQUE | FULLTEXT] INDEX index_name
    [USING {BTREE | HASH}]
    ON table_name (col [ASC | DESC], ...)
    [ALGORITHM = {DEFAULT | INPLACE | COPY}]
    [LOCK = {DEFAULT | NONE | SHARED | EXCLUSIVE}];

옵션	설명
UNIQUE	중복 값 허용 안 함 (NULL은 여러 개 허용)
FULLTEXT	전문 검색 인덱스
USING BTREE	기본값. B+Tree 인덱스
USING HASH	Memory 엔진에서만 의미 있음. InnoDB는 무시
ALGORITHM=INPLACE	Online DDL — 테이블 잠금 최소화 (MySQL 8.0 기본값)
LOCK=NONE	무잠금 — 동시 읽기·쓰기 허용

---

인덱스 생성 (CREATE)

단일 컬럼 인덱스

CREATE INDEX idx_member_email ON member (email);

복합 인덱스

-- 컬럼 순서가 쿼리 성능에 결정적 영향을 미친다 (ESR 원칙 참고)
CREATE INDEX idx_order_member_date ON orders (member_id, created_at);

UNIQUE 인덱스

-- 중복 삽입 시 오류 발생. NULL은 중복으로 취급하지 않아 여러 개 허용
CREATE UNIQUE INDEX uq_member_email ON member (email);

ASC / DESC 인덱스 (MySQL 8.0+)

오름차순·내림차순 인덱스: MySQL 8.0부터 컬럼별로 정렬 방향을 지정할 수 있다. 복합 인덱스에서 컬럼마다 정렬 방향이 혼합될 때 진가를 발휘한다.

-- 기본: ASC (오름차순) — 생략 시 기본값
CREATE INDEX idx_member_name ON member (name ASC);

-- DESC (내림차순)
CREATE INDEX idx_orders_date_desc ON orders (created_at DESC);

-- 복합 인덱스에서 방향 혼합 — 이게 DESC 인덱스의 핵심 사용 사례
CREATE INDEX idx_orders_member_date
    ON orders (member_id ASC, created_at DESC);

팁: MySQL 5.7 이하 vs 8.0+

버전	DESC 인덱스 동작
MySQL 5.7 이하	DESC 키워드를 파싱하지만 실제 저장은 ASC와 동일 — 역순 스캔(Backward Scan)으로 처리. 혼합 정렬에서 filesort 발생
MySQL 8.0+	진정한 내림차순 B+Tree 생성 — Forward Scan으로 처리해 혼합 정렬에서도 filesort 없음

언제 DESC 인덱스가 필요한가

단일 컬럼만 정렬하는 경우는 ASC 인덱스를 역방향으로 읽으면 충분하다. 복합 인덱스에서 컬럼마다 정렬 방향이 다를 때 DESC 인덱스가 실질적인 성능 차이를 만든다.

-- 이 쿼리를 filesort 없이 처리하려면
SELECT * FROM orders
WHERE member_id = 1
ORDER BY member_id ASC, created_at DESC;

-- ❌ 같은 방향 복합 인덱스 → filesort 발생
CREATE INDEX idx_orders ON orders (member_id ASC, created_at ASC);

-- ✅ 혼합 방향 복합 인덱스 → filesort 없음 (MySQL 8.0+)
CREATE INDEX idx_orders ON orders (member_id ASC, created_at DESC);

FULLTEXT 인덱스

-- 한국어는 ngram parser 필수 (공백 기반 토큰화 불가)
CREATE FULLTEXT INDEX ft_article ON article (title, content) WITH PARSER ngram;

테이블 생성 시 인덱스 정의

CREATE TABLE orders (
    id         BIGINT AUTO_INCREMENT,
    member_id  BIGINT NOT NULL,
    order_code VARCHAR(50),
    status     VARCHAR(20),
    amount     INT,
    created_at DATETIME,
    PRIMARY KEY (id),                                           -- 클러스터 인덱스 자동 생성
    UNIQUE KEY uq_order_code (order_code),                     -- UNIQUE 인덱스
    INDEX idx_order_member_date (member_id ASC, created_at DESC), -- 복합 + DESC
    INDEX idx_order_status (status)                             -- 단순 인덱스
) ENGINE=InnoDB;

ALTER TABLE로 인덱스 추가

-- 단순 인덱스 추가 (Online DDL — 서비스 중 실행 가능)
ALTER TABLE member
    ADD INDEX idx_member_name (name),
    ALGORITHM = INPLACE,
    LOCK = NONE;

-- UNIQUE 인덱스 추가
ALTER TABLE member
    ADD UNIQUE INDEX uq_member_phone (phone);

-- 여러 인덱스 한 번에 추가 (DDL 횟수 최소화)
ALTER TABLE orders
    ADD INDEX idx_status (status),
    ADD INDEX idx_amount (amount);

---

인덱스 조회 (READ)

SHOW INDEX — 가장 빠른 확인

SHOW INDEX FROM orders;
-- 또는
SHOW INDEX FROM orders FROM mydb;

컬럼	설명
Key_name	인덱스 이름. PRIMARY = PK (클러스터 인덱스)
Seq_in_index	복합 인덱스에서 컬럼 순서 (1부터 시작)
Column_name	인덱스가 걸린 컬럼명
Non_unique	0 = UNIQUE 인덱스, 1 = 중복 허용
Cardinality	추정 고유 값 수 (통계 기반 — ANALYZE TABLE 후 갱신)
Collation	A = ASC 저장, D = DESC 저장 (MySQL 8.0+에서 의미 있음)
Sub_part	프리픽스 인덱스면 글자 수, 아니면 NULL
Index_type	BTREE / FULLTEXT / HASH
Visible	YES / NO — 인비저블 인덱스 여부

SHOW CREATE TABLE

-- 테이블 전체 정의 확인 (인덱스 방향·옵션 포함)
SHOW CREATE TABLE orders\G

information_schema로 상세 조회

-- 특정 테이블의 모든 인덱스 상세 정보
SELECT
    index_name,
    seq_in_index,
    column_name,
    collation,      -- A: ASC 저장, D: DESC 저장
    cardinality,
    nullable,
    index_type,
    is_visible
FROM information_schema.STATISTICS
WHERE table_schema = 'mydb'
  AND table_name = 'orders'
ORDER BY index_name, seq_in_index;

EXPLAIN으로 실행 계획 확인

EXPLAIN SELECT * FROM member WHERE email = 'test@test.com';

EXPLAIN 결과에서 type 컬럼은 인덱스 사용 방식을 나타낸다. 위에 있을수록 좋다.

type	설명	예시
const	PK 또는 UNIQUE로 1건 조회. 상수 취급	WHERE id = 1
eq_ref	JOIN에서 PK/UNIQUE로 정확히 1건 매칭	JOIN member m ON m.id = o.member_id
ref	논유니크 인덱스로 여러 건 매칭	WHERE member_id = 1 (FK 인덱스)
range	인덱스 범위 스캔	WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
index	인덱스 전체 스캔 (Full Index Scan)	SELECT member_id FROM orders (커버링이지만 전체 스캔)
ALL	테이블 전체 스캔 (Full Table Scan)	인덱스 미사용

위험: type이 ALL이면 Full Table Scan이다. 반드시 인덱스 추가 또는 쿼리 개선을 검토해야 한다. index도 인덱스 전체를 읽는 것이므로 대용량 테이블에서는 느릴 수 있다.

팁: Extra 컬럼도 함께 확인하자.

Extra 값	의미
Using index	커버링 인덱스 사용 (테이블 접근 없음)
Using where	인덱스로 찾은 후 추가 필터링
Using filesort	정렬에 인덱스를 사용하지 못해 별도 정렬 수행
Using temporary	임시 테이블 생성 (GROUP BY, DISTINCT 등)
Backward index scan	DESC 인덱스 또는 역방향 스캔 사용

---

인덱스 수정 (ALTER)

인덱스 이름 변경 (MySQL 5.7+)

ALTER TABLE member RENAME INDEX idx_member_email TO idx_email;

인덱스 가시성 변경 — 인비저블 (MySQL 8.0+)

-- 옵티마이저에게 숨기기 (물리 인덱스는 유지)
ALTER TABLE member ALTER INDEX idx_member_email INVISIBLE;

-- 복원
ALTER TABLE member ALTER INDEX idx_member_email VISIBLE;

주의: 인덱스의 컬럼 목록, 순서, 타입 자체는 수정할 수 없다. 변경이 필요하면 DROP 후 다시 CREATE해야 한다.

-- 인덱스 구조 변경 = DROP + CREATE
DROP INDEX idx_old ON member;
CREATE INDEX idx_new ON member (email DESC, name ASC);

---

인덱스 삭제 (DROP)

-- 방법 1: DROP INDEX (가장 일반적)
DROP INDEX idx_member_email ON member;

-- 방법 2: ALTER TABLE로 삭제 (여러 작업과 함께 묶을 때 유리)
ALTER TABLE member DROP INDEX idx_member_email;

-- PK 삭제 (AUTO_INCREMENT 컬럼이 있으면 먼저 AUTO_INCREMENT 제거 필요)
ALTER TABLE member DROP PRIMARY KEY;

삭제 전 인비저블 인덱스로 영향 검증 → 확인 후 삭제

-- ① 인비저블로 전환 (물리 인덱스는 유지, 옵티마이저만 무시)
ALTER TABLE member ALTER INDEX idx_member_email INVISIBLE;

-- ② 운영 환경에서 충분히 모니터링 (최소 1~2일, 배치 포함 시 더 길게)
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';

-- ③ 이상 없으면 삭제
DROP INDEX idx_member_email ON member;

-- ③-1 문제 발생 시 즉시 복원 (DROP 전이라면)
ALTER TABLE member ALTER INDEX idx_member_email VISIBLE;

---

이전: 인덱스 구조와 종류 · 다음: 인덱스 쿼리 패턴