인덱스 운영과 튜닝

인덱스 파워링크

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

인덱스 손익분기점

인덱스 손익분기점: 인덱스를 타는 비용이 Full Table Scan보다 싸지는 지점. 즉, 인덱스 탐색 + 테이블 재조회 비용이 테이블 전체 순차 스캔 비용보다 작아지는 구간이다.

인덱스가 있다고 항상 빠른 것은 아니다. 특히 보조 인덱스로 많은 행을 찾은 뒤 다시 PK로 클러스터 인덱스를 조회하면, 많은 랜덤 I/O가 발생한다. 이 비용이 커지면 옵티마이저는 인덱스 대신 Full Table Scan을 선택할 수 있다.

인덱스가 유리한 경우
  -> 전체 1,000,000건 중 100건 조회
  -> 적은 인덱스 탐색 + 적은 테이블 재조회

Full Scan이 유리할 수 있는 경우
  -> 전체 1,000,000건 중 700,000건 조회
  -> 인덱스로 700,000번 찾아도 결국 대부분의 테이블을 읽음

판단 기준

조건	인덱스 효과
조회 대상이 전체 행의 1% 이하	매우 좋음
조회 대상이 전체 행의 5~10% 이하	대체로 좋음
조회 대상이 전체 행의 20~30% 이상	Full Scan이 더 나을 수 있음
커버링 인덱스	테이블 재조회가 없어 손익분기점이 올라감
SELECT * + 보조 인덱스	Double Lookup 때문에 손익분기점이 낮아짐
행 크기가 큼	필요한 행만 읽는 인덱스가 유리해짐
Buffer Pool에 대부분 캐시됨	디스크 I/O 차이가 줄어 판단이 달라질 수 있음

주의: 5~10%는 암기용 절대값이 아니라 실무 감각을 위한 기준이다. 실제 선택은 테이블 크기, 행 크기, 인덱스 종류, 커버링 여부, 데이터 분포, 캐시 상태에 따라 달라진다.

예시

-- status 값 종류가 적고 DONE이 대부분이면 선택도가 낮다.
-- 보조 인덱스가 있어도 Full Scan이 선택될 수 있다.
EXPLAIN SELECT *
FROM orders
WHERE status = 'DONE';

-- 특정 회원의 최근 주문처럼 결과가 작으면 인덱스 효과가 크다.
EXPLAIN SELECT *
FROM orders
WHERE member_id = 10
  AND created_at >= '2026-01-01';

팁: 인덱스 추가 전에는 EXPLAIN의 type, key, rows, filtered, Extra를 보고 실제로 얼마나 많은 행을 읽는지 먼저 확인한다. MySQL 8.0.18 이상이라면 EXPLAIN ANALYZE로 실제 실행 시간도 함께 볼 수 있다.

---

랜덤 I/O VS 순차 I/O

순차 I/O(Sequential I/O): 디스크나 메모리 페이지를 연속된 순서로 읽는 방식. 랜덤 I/O(Random I/O): 필요한 페이지를 여기저기 점프하며 읽는 방식.

Full Table Scan은 보통 테이블 페이지를 앞에서 뒤로 쭉 읽으므로 순차 I/O에 가깝다. 반면 보조 인덱스 조회는 보조 인덱스에서 PK를 찾은 뒤, 그 PK로 클러스터 인덱스를 다시 찾아가므로 랜덤 I/O가 많이 발생할 수 있다.

Full Table Scan
  -> page 1 -> page 2 -> page 3 -> page 4
  -> 순차적으로 읽음

Secondary Index + Double Lookup
  -> index page -> PK 100 page -> PK 7 page -> PK 992 page
  -> 여기저기 점프하며 읽음

구분	순차 I/O	랜덤 I/O
읽는 방식	연속된 페이지를 순서대로 읽음	떨어진 페이지를 필요할 때마다 읽음
대표 상황	Full Table Scan, 인덱스 Range Scan	보조 인덱스 후 클러스터 인덱스 재조회
장점	대량 읽기에 효율적	필요한 소수 행만 읽을 때 효율적
단점	불필요한 행도 많이 읽을 수 있음	대상 행이 많으면 점프 비용이 커짐
인덱스와 관계	많은 행 조회 시 Full Scan이 이길 수 있음	적은 행 조회 시 인덱스가 이김

왜 인덱스가 느려질 수 있나

-- idx_status(status)가 있다고 가정
SELECT *
FROM orders
WHERE status = 'DONE';

DONE이 전체 주문의 80%라면, DB는 보조 인덱스에서 수많은 PK를 찾고 다시 클러스터 인덱스를 수많이 조회해야 한다. 이때 랜덤 I/O가 폭증한다. 차라리 테이블을 처음부터 끝까지 순차적으로 읽는 Full Scan이 더 빠를 수 있다.

랜덤 I/O를 줄이는 방법

• SELECT * 대신 필요한 컬럼만 조회해 커버링 인덱스를 노린다.
• 선택도가 낮은 단일 컬럼 인덱스보다 복합 인덱스로 조회 범위를 더 좁힌다.
• PK는 순차 증가 값으로 설계해 페이지 분할과 단편화를 줄인다.
• EXPLAIN에서 rows가 너무 크면 인덱스 추가보다 조건 재설계를 먼저 검토한다.

SSD에서도 중요하다: SSD는 HDD보다 랜덤 I/O 비용이 낮지만, DB 입장에서는 여전히 페이지 탐색, Buffer Pool 미스, 래치 경합, CPU 캐시 미스 비용이 남는다. 랜덤 I/O가 공짜가 되는 것은 아니다.

---

고급 인덱스 기법

프리픽스 인덱스 (Prefix Index)

프리픽스 인덱스: VARCHAR(500), TEXT 같은 긴 컬럼에 전체가 아닌 앞 N글자만 인덱스로 사용하는 방식. 인덱스 크기를 줄이면서 적절한 선택도를 유지한다.

-- 전체 컬럼 인덱스: VARCHAR(500)이면 키 크기가 커져 페이지당 저장 가능한 키 수 감소
CREATE INDEX idx_member_name ON member (name);

-- 프리픽스 인덱스: 앞 20글자만 인덱스로 저장
CREATE INDEX idx_member_name ON member (name(20));

적절한 prefix 길이 찾기 — 선택도가 전체 길이와 비슷해지는 최소 길이를 사용한다:

SELECT
    COUNT(DISTINCT LEFT(name, 5))  / COUNT(*) AS s5,
    COUNT(DISTINCT LEFT(name, 10)) / COUNT(*) AS s10,
    COUNT(DISTINCT LEFT(name, 20)) / COUNT(*) AS s20,
    COUNT(DISTINCT name)           / COUNT(*) AS sfull
FROM member;
-- s10 ≈ sfull 이면 10글자면 충분

주의: 프리픽스 인덱스는 커버링 인덱스로 사용할 수 없다. 인덱스에 컬럼 일부만 저장되어 있으므로, SELECT에서 해당 컬럼을 포함하면 반드시 테이블 접근이 발생한다.

---

함수 기반 인덱스 (Functional Index, MySQL 8.0.13+)

함수 기반 인덱스: 컬럼 자체가 아닌 함수·표현식의 결과값에 인덱스를 생성한다. 컬럼에 함수를 적용하면 인덱스가 무력화되는 문제를 근본적으로 해결한다.

-- ❌ 인덱스 무력화 — 컬럼에 YEAR() 함수 적용
WHERE YEAR(created_at) = 2024;

-- ✅ 해결책 1 (권장): 함수 없이 범위 조건으로 재작성
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- ✅ 해결책 2: 함수 기반 인덱스 생성 (표현식을 이중 괄호로 감쌈)
CREATE INDEX idx_orders_year ON orders ((YEAR(created_at)));
-- 이제 YEAR(created_at) = 2024 조건에서 인덱스 사용 가능

다른 활용 예:

-- 이메일 대소문자 무관 검색 (LOWER 함수 기반)
CREATE INDEX idx_email_lower ON member ((LOWER(email)));
SELECT * FROM member WHERE LOWER(email) = 'test@example.com';

-- JSON 필드 인덱스
CREATE INDEX idx_order_meta ON orders ((JSON_UNQUOTE(meta->>'$.type')));
SELECT * FROM orders WHERE JSON_UNQUOTE(meta->>'$.type') = 'NORMAL';

팁: 해결책 1(범위 조건 재작성)이 더 이식성이 높고 단순하다. 함수 기반 인덱스는 쿼리 재작성이 불가능한 경우에 사용한다.

---

인비저블 인덱스 (Invisible Index, MySQL 8.0+)

인비저블 인덱스: 옵티마이저에게 인덱스가 없는 것처럼 보이게 하지만 물리적으로는 인덱스 구조를 유지하는 기능. 인덱스 삭제 전 영향도를 안전하게 사전 검증할 수 있다.

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

-- 실행 계획 확인 → 해당 인덱스 없어도 성능이 괜찮은지 검증
EXPLAIN SELECT * FROM member WHERE email = 'test@test.com';

-- 문제 없으면 삭제, 문제 있으면 복원
DROP INDEX idx_member_email ON member;   -- 영향 없으면 삭제
ALTER TABLE member ALTER INDEX idx_member_email VISIBLE; -- 문제 있으면 복원

활용 시나리오:

시나리오	사용법
오래된 인덱스 실사용 여부 확인	INVISIBLE 전환 후 모니터링
신규 인덱스 추가 영향도 검증	INVISIBLE 상태로 먼저 생성 후 전환
인덱스 삭제 리스크 없는 롤백	INVISIBLE → 확인 → DROP 또는 VISIBLE 복원

팁: 세션 단위로 인비저블 인덱스를 강제 활성화해 테스트할 수 있다.

SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN SELECT * FROM member WHERE email = 'test@test.com';
-- 인비저블 인덱스도 사용하는 실행 계획 확인 가능

---

인덱스 힌트

옵티마이저가 잘못된 인덱스를 선택할 때, 개발자가 직접 인덱스를 지정할 수 있다.

-- 특정 인덱스 사용 권장 (옵티마이저가 더 유리한 방법이 있으면 무시 가능)
SELECT * FROM member USE INDEX (idx_member_email)
WHERE email = 'test@test.com';

-- 특정 인덱스 강제 사용 (Full Scan보다 항상 선택)
SELECT * FROM member FORCE INDEX (idx_member_email)
WHERE email = 'test@test.com';

-- 특정 인덱스 사용 금지
SELECT * FROM member IGNORE INDEX (idx_member_name)
WHERE name = '김철수' AND email = 'test@test.com';

힌트	동작	언제 사용
USE INDEX	지정 인덱스를 권장 — 옵티마이저가 더 유리하면 무시 가능	방향만 제시할 때
FORCE INDEX	지정 인덱스 강제 — Full Scan보다 항상 우선	잘못된 Full Scan을 막을 때
IGNORE INDEX	지정 인덱스 사용 금지	특정 인덱스를 피해야 할 때

주의: 인덱스 힌트는 임시방편이다. 스키마·데이터가 변경되면 힌트가 오히려 독이 될 수 있다. ANALYZE TABLE로 통계를 갱신하거나 쿼리를 재작성해서 근본 원인을 해결하는 것을 권장한다.

---

인덱스 통계와 모니터링

통계 정보 관리

InnoDB는 인덱스의 카디널리티 통계를 관리하며, 옵티마이저가 이를 기반으로 실행 계획을 수립한다. 통계가 오래되면 잘못된 실행 계획이 선택된다.

-- 통계 갱신 (슬로우 쿼리 발생 시 먼저 시도)
ANALYZE TABLE member;

-- 인덱스 상세 통계 조회
SELECT
    table_name,
    index_name,
    seq_in_index,
    column_name,
    cardinality
FROM information_schema.STATISTICS
WHERE table_schema = 'mydb'
  AND table_name = 'member'
ORDER BY index_name, seq_in_index;

사용되지 않는 인덱스 찾기

-- 사용 안 되는 인덱스 목록 (sys 스키마, MySQL 5.7+)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'mydb';

-- 인덱스별 조회·쓰기 사용 횟수
SELECT
    table_name,
    index_name,
    rows_selected,
    rows_inserted,
    rows_updated,
    rows_deleted
FROM sys.schema_index_statistics
WHERE table_schema = 'mydb'
ORDER BY rows_selected DESC;

팁: sys.schema_unused_indexes는 서버 재시작 이후 쌓인 통계 기준이다. 충분한 시간(최소 1~2주) 이후에 조회해야 신뢰도가 높다. 또한 배치 쿼리처럼 특정 시간대에만 쓰는 인덱스는 "미사용"으로 보일 수 있으므로 삭제 전 운영 스케줄을 함께 확인하자.

인덱스 단편화 측정 및 해소

-- 테이블별 단편화율 확인
SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(data_free  / 1024 / 1024, 2) AS free_mb,
    ROUND(data_free / (data_length + data_free) * 100, 1) AS frag_pct
FROM information_schema.TABLES
WHERE table_schema = 'mydb'
  AND engine = 'InnoDB'
ORDER BY frag_pct DESC;

-- 단편화 해소 (소규모 테이블, 트래픽 적은 시간대)
OPTIMIZE TABLE member;

-- 대용량 테이블 — Online DDL 활용 (잠금 최소화)
ALTER TABLE member ENGINE = InnoDB;

주의: OPTIMIZE TABLE은 실행 중 테이블 잠금이 발생할 수 있다. 수백만 건 이상의 대용량 테이블에서는 pt-online-schema-change 또는 gh-ost 같은 무중단 DDL 도구를 사용하자.

---

장점

장점	설명
조회 성능 향상	Full Scan O(N) → B-Tree O(log N) 탐색으로 대폭 단축
정렬 최적화	인덱스 순서와 ORDER BY가 일치하면 별도 filesort 없음
커버링 인덱스	필요한 컬럼이 모두 인덱스에 있으면 테이블 접근 자체를 생략
잠금 범위 최소화	인덱스로 대상 행을 좁혀 Row Lock 범위 감소 → 트랜잭션 충돌 완화
Full Scan 방지	대용량 테이블에서 불필요한 디스크 I/O 제거

단점

단점	설명
쓰기 성능 저하	INSERT/UPDATE/DELETE 시 인덱스 구조도 함께 갱신
저장 공간 증가	인덱스 구조 자체가 별도 공간 차지 (대용량 테이블에서는 수십 GB 이상)
인덱스 과잉 부작용	인덱스가 많을수록 쓰기 비용 누적 + 옵티마이저 판단 오류 가능성 증가
통계 의존성	카디널리티 통계가 부정확하면 옵티마이저가 잘못된 실행 계획 선택
유지보수 비용	스키마 변경·인덱스 재구성 시 운영 영향도 별도 관리 필요

주의할 점

인덱스를 무력화하는 패턴 — 인덱스가 있어도 사용되지 않는 경우:

-- ❌ 컬럼에 함수 적용
WHERE YEAR(created_at) = 2024
-- ✅ 대신
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

-- ❌ 앞쪽 LIKE 와일드카드
WHERE name LIKE '%철수'
-- ✅ 뒤쪽 와일드카드는 OK
WHERE name LIKE '김%'

-- ❌ 묵시적 형변환
WHERE member_id = '100'  -- member_id가 INT인데 문자열 비교

-- ❌ NOT / 부정 조건
WHERE status != 'DELETED'
WHERE status NOT IN ('DELETED', 'EXPIRED')
-- ✅ 긍정 조건으로 전환
WHERE status IN ('ACTIVE', 'PENDING')

-- ❌ OR 조건 (서로 다른 컬럼)
WHERE name = '김철수' OR age > 30
-- 각 컬럼이 다른 인덱스를 사용해야 하므로 Full Scan 가능
-- ✅ UNION ALL로 분리
SELECT * FROM member WHERE name = '김철수'
UNION ALL
SELECT * FROM member WHERE age > 30 AND name != '김철수';

팁: MySQL InnoDB에서 IS NULL은 인덱스를 사용할 수 있다(ref_or_null). 반면 IS NOT NULL은 대부분의 행을 조회하게 되어 옵티마이저가 Full Scan을 선택하는 경우가 많다.

WHERE email IS NULL      -- ✅ 인덱스 사용 가능
WHERE email IS NOT NULL  -- ⚠️ 대부분 행 조회 → Full Scan 가능

특징

• B+Tree 기반 (InnoDB): 내부 노드에는 키만, 리프 노드에 키+데이터를 저장. 리프 노드끼리 연결 리스트로 이어져 있어 범위 스캔이 빠르다
• 클러스터 인덱스 = 테이블 자체: InnoDB의 테이블은 클러스터 인덱스 B+Tree로 물리 저장됨. "테이블 파일"과 "클러스터 인덱스 파일"이 동일
• 보조 인덱스 리프에 PK 저장: 물리 주소 대신 PK를 저장해 클러스터 인덱스 재구성 시 보조 인덱스를 갱신하지 않아도 됨 (Double Lookup의 트레이드오프)
• 통계 기반 옵티마이저: 카디널리티 통계가 부정확하면 인덱스가 있어도 Full Scan을 선택할 수 있음. 주기적인 ANALYZE TABLE이 필요
• InnoDB Buffer Pool 캐싱: 인덱스 페이지도 Buffer Pool에 캐시됨. 자주 조회하는 인덱스(핫 인덱스)는 메모리에서 서비스 → 디스크 I/O 없음
• Adaptive Hash Index: InnoDB가 자주 접근하는 B-Tree 리프를 자동으로 Hash 형태로 캐시. 별도 설정 없이 동작하며 등호 검색 성능을 추가로 향상
• MVCC 연동: 트랜잭션 격리 수준에 따라 인덱스 탐색 시 Undo Log를 참조해 버전별 데이터를 반환
• Online DDL (MySQL 8.0): ALTER TABLE ... ADD INDEX는 기본적으로 잠금 없이 실행 가능 (ALGORITHM=INPLACE, LOCK=NONE)

---

베스트 프랙티스

인덱스 설계 원칙

• EXPLAIN 먼저 — 인덱스를 추가하기 전에 실행 계획을 확인한다. type=ALL이 확인되면 그때 인덱스를 검토한다
• PK는 BIGINT AUTO_INCREMENT — UUID PK는 무작위 삽입으로 페이지 분할이 잦아짐. UUID가 불가피하면 시간 정렬이 가능한 UUIDv7 또는 ULID를 고려한다
• ESR 원칙 준수 — 복합 인덱스는 Equality → Sort → Range 순으로 컬럼을 배치한다
• 인덱스 수는 최소화 — 테이블당 5개 이하를 목표로. 인덱스가 많을수록 쓰기 성능 저하와 옵티마이저 혼란이 증가한다
• SELECT * 자제 — 필요한 컬럼만 명시해야 커버링 인덱스 활용이 가능하다
• 미사용 인덱스 정기 점검 — sys.schema_unused_indexes를 주기적으로 확인해 사용 안 되는 인덱스를 제거한다

운영 원칙

• 대용량 테이블 인덱스 추가 — Online DDL (ALTER TABLE ... ADD INDEX) 또는 pt-online-schema-change / gh-ost를 사용해 서비스 중단 없이 적용한다
• 삭제 전 인비저블 인덱스로 검증 — ALTER INDEX ... INVISIBLE로 전환 후 영향도를 확인하고, 이상 없으면 DROP한다
• 통계 주기적 갱신 — 데이터 분포가 크게 바뀐 테이블은 ANALYZE TABLE로 통계를 최신화한다
• 단편화 모니터링 — information_schema.TABLES의 data_free를 주기적으로 확인하고 단편화가 심하면 OPTIMIZE TABLE 또는 ALTER TABLE ... ENGINE=InnoDB로 재구성한다

---

실무에서는?

상황	접근법
슬로우 쿼리 발생	slow_query_log 활성화 → EXPLAIN 분석 → type=ALL 확인 → 인덱스 추가
인덱스 추가 후 개선 없음	ANALYZE TABLE로 통계 갱신 → 힌트로 강제 확인 → 쿼리 재작성 검토
운영 중 인덱스 추가	MySQL 8.0 Online DDL (ALGORITHM=INPLACE) 또는 pt-osc 사용
인덱스 삭제 리스크 존재	인비저블 인덱스로 전환 후 충분히 모니터링 → 이상 없으면 DROP
불필요한 인덱스 정리	sys.schema_unused_indexes 주기적 점검 (서버 재기동 후 1~2주 이후)
카디널리티 낮은 컬럼 검색	단독 인덱스 대신 복합 인덱스로 결합 카디널리티를 높임
긴 VARCHAR 컬럼 검색	프리픽스 인덱스로 크기 절약, 또는 Full-Text Index 검토
대용량 텍스트 검색	Full-Text Index → 수천만 건 이상이면 Elasticsearch/OpenSearch 전환 고려
UUID PK 강제 사용 환경	UUIDv7(시간 정렬) 또는 ULID 도입으로 페이지 분할 최소화
함수 조건 인덱스 무력화	범위 조건으로 쿼리 재작성 → 불가능하면 함수 기반 인덱스 (MySQL 8.0.13+)

---

이전: 인덱스 쿼리 패턴 · 처음: 인덱스 개요