데이터베이스 인덱스(Index) 최적화 방법

Overview

데이터베이스에서 인덱스(Index)는 데이터를 빠르게 조회할 수 있도록 돕는 핵심 요소다.
대규모 데이터베이스에서 인덱스는 성능을 좌우할 정도로 중요하며, 어떤 인덱스를 어떻게 설계하느냐에 따라 쿼리 효율이 크게 달라진다.

 

이 글에서는 데이터베이스 인덱스의 구조별 특징을 비교하고, 실제 환경에서 성능을 최적화할 수 있는 인덱스 튜닝 전략을 알아본다.

그리고 효율적인 인덱스 설계 방법과 인덱스 힌트(Index Hint), 그리고 쿼리 성능 분석을 위한 실행계획(Explain) 사용법에 대해 정리해본다.

 

데이터가 커지고 사용자가 많아질수록 데이터 접근 속도가 전체 애플리케이션 성능에 영향을 미친다.
그만큼 인덱스는 빠른 데이터 검색과 성능 최적화에 필수적인 요소다.

출처 : https://www.geeksforgeeks.org/indexing-in-databases-set-1/

 

 

 

 

 

 

 

---

 

 

인덱스(Index)란?

인덱스는 테이블에서 특정 값을 빠르게 찾기 위해 사용하는 데이터 구조이다.
마치 책의 목차처럼, 데이터가 저장된 위치를 빠르게 알려주는 역할을 한다.

인덱스가 없다면 DB는 원하는 값을 찾기 위해 테이블 전체를 스캔해야 하며, 이는 매우 비효율적이다.

 

인덱스가 없을 경우 → 테이블의 모든 행을 처음부터 끝까지 검사(Full Table Scan)해야 한다.

 

 

 

 

 

인덱스의 주요 유형 비교

종류	특징	장점	단점	활용 사례
B-Tree	균형 이진 트리 기반	범위 검색, 정렬에 효율적	대량 데이터 삽입/삭제 시 오버헤드 발생	대부분의 RDBMS 기본 인덱스
Hash	해시 함수 기반	정확한 키 검색에 매우 빠름	범위 쿼리 불가, 정렬 불가	캐시 조회, 키-값 매핑 정확성
Bitmap	비트맵 기반으로 값 존재 여부 저장	반복 값 적은 컬럼에서 효율적	DML(삽입/수정/삭제)이 많을 경우 성능 저하	성별, 국가, Boolean 값 등
GiST/SP-GiST	범용 인덱스 구조 (PostgreSQL 특화)	공간 데이터, 근사 검색, 범위 트리 처리 가능	구현 복잡성 있음	위치기반 서비스, 추천시스템
GIN	다중 키워드 인덱싱 (역색인 구조)	배열, JSON, Full-Text 검색에 효과적	쓰기 작업 느릴 수 있음	태그 검색, JSON 쿼리

 

 

 

 

 

인덱스가 성능에 미치는 영향

 

✅ 성능 향상

• 검색 속도 향상: 테이블 접근 없이 인덱스만으로 데이터 조회 가능
• 정렬 최소화: `ORDER BY` 절과 잘 설계된 인덱스를 활용하면 정렬 과정 생략 가능
• JOIN 최적화: 조인 대상 컬럼에 인덱스를 적용하면 조인 성능 대폭 개선

 

⚠️ 성능 저하

• 인덱스 남용: 너무 많은 인덱스는 오히려 쓰기(`INSERT, UPDATE, DELETE`) 성능 저하시킴
• 낮은 선택도 인덱스는 효과 없음 (예: 성별, 요일 등 소수의 반복 값)
• 인덱스 스캔보다 테이블 전체 스캔이 빠른 경우 존재

 

 

 

 

---

 

 

 

 

 

실전 인덱스 튜닝 방법

 

1. 선택도(Selectivity) 고려

• 선택도 = 유니크 값 / 전체 행 수
• 선택도가 높은 컬럼일수록 인덱스 효율 ↑
• 선택도가 낮은 컬럼은 인덱스 효과 거의 없음

 

 

2. 복합 인덱스 활용

• 자주 함께 사용되는 조건은 복합 인덱스로 생성
• 선두 컬럼 우선순위에 따라 인덱스 활용 여부가 결정됨
   

-- 복합 인덱스 (first_name, last_name)
SELECT * FROM users WHERE first_name = 'Kim'; -- ✅ 인덱스 사용
SELECT * FROM users WHERE last_name = 'Kim';  -- ❌ 인덱스 사용 안됨

 

 

 

3. Covering Index 고려

• 사용된 컬럼 전체가 인덱스에 포함되어 있다면 테이블 접근 생략 가능
• MySQL: `EXPLAIN` 에서 `Using index` 라고 표시됨

 

 

4. 인덱스 모니터링

• PostgreSQL: `pg_stat_user_indexes`
• MySQL: `SHOW INDEX FROM 테이블명`
• Oracle: `DBA_INDEXES`

 

 

5. 인덱스 리빌드 및 정리

• `ANALYZE`, `REINDEX`, `OPTIMIZE TABLE` 명령으로 인덱스 최적화
• 대량 삭제 후 → 인덱스 리빌드 필요

 

 

 

 

---

 

 

 

 

 

인덱스 관련 실무 예제

 

조회 성능 개선

-- 비효율적인 쿼리 (인덱스 미사용)
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

-- 효율적인 쿼리 (함수를 피하고 인덱스 활용)
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

 

 

잘못된 인덱스 예시

-- 선택도 낮은 성별 컬럼에 인덱스 → 효과 미미
CREATE INDEX idx_gender ON users(gender);

 

 

 

다중 컬럼 인덱스 활용

-- 복합 인덱스 생성 (name, age 순서)
CREATE INDEX idx_name_age ON users(name, age);

-- 인덱스가 사용됨 (순서 일치)
SELECT * FROM users WHERE name = 'Alice' AND age = 30;

-- 부분만 사용 (앞 컬럼만 일치 → 인덱스 일부만 사용 가능)
SELECT * FROM users WHERE name = 'Alice';

-- 인덱스가 사용되지 않음 (뒤 컬럼만 조건 → 비효율적)
SELECT * FROM users WHERE age = 30;

• Tip: 복합 인덱스는 선두 컬럼부터 순서대로 조건에 포함되어야 인덱스가 효과적으로 작동한다.

 

 

 

다중 조건 인덱스 (복합 인덱스)

-- 인덱스 없이 다중 조건을 사용하면 풀스캔 발생 가능
SELECT * FROM employees WHERE department_id = 10 AND job_id = 'SA_REP';

-- 복합 인덱스를 생성하여 성능 향상
CREATE INDEX idx_dept_job ON employees(department_id, job_id);

-- 주의: 인덱스는 선언된 순서대로 검색할 때 효과적

• 참고: `WHERE job_id = 'SA_REP' AND department_id = 10` 도 성능이 좋지만, 인덱스 순서와 일치하지 않으면 인덱스 범위를 덜 활용함.

 

 

LIKE 와 와일드카드 주의

-- 인덱스가 무용지물인 예 (앞에 와일드카드 사용)
SELECT * FROM products WHERE name LIKE '%phone';

-- 인덱스가 사용되는 경우 (뒤에만 와일드카드)
SELECT * FROM products WHERE name LIKE 'phone%';

-- name 컬럼에 인덱스
CREATE INDEX idx_product_name ON products(name);

 

 

 

OR 조건 시 주의

-- OR 조건은 인덱스를 무시하거나 일부만 사용하게 만듦
SELECT * FROM orders WHERE customer_id = 1001 OR order_status = 'SHIPPED';

-- 해결책: UNION으로 분리하여 각각 인덱스 사용 유도
SELECT * FROM orders WHERE customer_id = 1001
UNION
SELECT * FROM orders WHERE order_status = 'SHIPPED';

 

 

 

함수 사용으로 인덱스 미사용

-- 인덱스 비효율 (함수 사용)
SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';

-- 인덱스 효율적 사용 (범위 조건)
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59';

-- created_at 컬럼에 인덱스
CREATE INDEX idx_created_at ON users(created_at);

 

 

 

NULL 조건 인덱스 활용

-- 일부 DBMS에서는 NULL 비교 조건이 인덱스를 사용하지 않음
SELECT * FROM employees WHERE manager_id IS NULL;

-- DBMS 설정 또는 함수로 보완 필요 (예: NVL, COALESCE 등)

 

 

 

Unique 제약과 인덱스

-- 이메일 중복을 방지하고 빠른 검색을 위해 유니크 인덱스 활용
CREATE UNIQUE INDEX idx_email ON users(email);

-- 또는 DDL 정의 시
CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

 

 

 

덮어쓰기(Covering Index) 활용

-- covering index: 인덱스에 포함된 컬럼만으로 결과를 반환하는 쿼리
CREATE INDEX idx_order_summary ON orders(order_date, total_amount);

-- 인덱스만으로 쿼리 해결 (테이블 액세스 불필요)
SELECT order_date, total_amount FROM orders WHERE order_date = '2024-01-01';

 

 

 

 

 

 

고급 인덱스 팁

• Partial Index (부분 인덱스): 특정 조건만 인덱스 적용 (PostgreSQL에서 유용)
• Function-based Index: 특정 함수 결과에 대해 인덱스 생성 가능 (Oracle, PostgreSQL 지원)
• Descending Index: 내림차순 정렬 최적화를 위한 인덱스

 

 

 

---

 

 

 

인덱스 설계 가이드

 

1. WHERE 조건에 자주 사용되는 컬럼

→ 자주 검색하는 조건에는 인덱스를 반드시 생성

SELECT * FROM employees WHERE department_id = 10;

 

 

 

2. JOIN 조건 컬럼

→ 테이블 간 JOIN에 사용되는 키에도 인덱스를 고려

SELECT e.name, d.name FROM employees e
JOIN departments d ON e.department_id = d.department_id;

 

 

 

 

3. ORDER BY, GROUP BY 컬럼

→ 정렬이나 그룹화에 사용될 경우 성능 향상 가능

SELECT * FROM sales ORDER BY created_at DESC;

 

 

 

 

4. 선행 컬럼 우선 전략

→ 복합 인덱스에서는 앞쪽 컬럼부터 사용하는 조건이 있어야 인덱스가 잘 타진다.

CREATE INDEX idx_user_email ON users (name, email);

-- WHERE name = 'Kim' → 사용됨
-- WHERE email = 'kim@example.com' → 인덱스 사용 안 됨!

 

 

 

 

 

---

 

 

 

 

 

 

인덱스 힌트(Index Hint)

DBMS가 자동으로 최적의 인덱스를 선택하지만,
때로는 개발자가 직접 특정 인덱스를 사용하도록 힌트를 줄 수 있다.

 

 

MySQL 예시

SELECT * FROM employees USE INDEX (idx_department) WHERE department_id = 10;

• FORCE INDEX, IGNORE INDEX 도 가능
  • FORCE INDEX: 반드시 해당 인덱스를 사용하게 함
  • IGNORE INDEX: 특정 인덱스를 사용하지 않도록 지시

 

 

 

 

 

---

 

 

 

 

 

실행계획(EXPLAIN)

`EXPLAIN` 은 쿼리 실행 전에 어떻게 처리될지 계획을 보여주는 도구이다.
인덱스를 사용하는지, 풀스캔이 발생하는지, `JOIN` 순서 등을 확인할 수 있다.

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

 

id	select_type	table	type	key	rows	Extra
1	SIMPLE	employees	ref	idx_department	5	Using where

• type: 접근 방식 (`ALL, index, range, ref, eq_ref, const`)
• key: 사용된 인덱스
• rows: 예상 탐색 횟수
• Extra: 추가 정보 (`Using where, Using index, Using temporary, Using filesort`)

 

 

 

 

 

 

---

 

 

 

 

 

 

마무리

인덱스는 읽기 성능 향상의 가장 효과적인 수단이지만,
그만큼 쓰기 성능에 영향을 주기 때문에 설계, 사용, 관리가 매우 중요하다.

 

• 인덱스는 무조건 많이 만든다고 좋은 것이 아님 → 쓰기 성능, 디스크 공간, 인덱스 유지비용 증가
• 조회 쿼리 패턴을 파악하고 선택도가 높은 컬럼, `JOIN` 에 자주 쓰이는 컬럼, `ORDER BY` 대상 컬럼 등에 신중하게 적용

 

 

 

💡 반드시 기억할 점

• 선택도 높은 컬럼에 인덱스 설정
• 빈도 높은 쿼리 조건을 기반으로 인덱스를 구성
• 주기적인 인덱스 모니터링과 불필요한 인덱스 제거
• 쓰기/읽기 비중에 따라 균형 있는 인덱스 설계

 

• 인덱스는 `WHERE, JOIN, ORDER BY` 절에 활용되는 컬럼에 적절히 생성
• `EXPLAIN` 을 통해 쿼리의 실행 계획을 미리 분석하고 병목을 찾아낸다
• 때로는 인덱스 힌트로 DB 엔진의 결정을 보완할 수 있다

 

 

 

 

효율적인 인덱스 전략은 쾌적한 사용자 경험을 만드는 성능의 핵심 무기가 될 수 있다.

 

 

 

 

 

 

 

---

Reference

 

• PostgreSQL Documentation – Indexes
• MySQL Performance Tuning Guide
• Oracle Database Performance Tuning Guide