[SQL] 인덱스 기본 사용법

🔹 1. 인덱스란?

• 인덱스는 테이블의 검색 성능을 향상시키는 데이터 구조입니다.
• 테이블의 특정 컬럼에 대한 정렬된 정보를 별도로 저장하여, 빠르게 데이터를 찾을 수 있도록 돕습니다.
• 도서관에서 책을 찾기 위해 사용하는 목차(Index)와 유사한 개념입니다.

 

---

🔹 2. 인덱스 기본 생성 및 사용법

2-1. 기본 인덱스 생성 (B-Tree Index)

구문:

CREATE INDEX 인덱스명 ON 테이블명(컬럼명);

✅ 예제:

CREATE INDEX emp_name_idx ON emp(emp_name);

• employees 테이블의 employee_name 컬럼에 대해 인덱스를 생성합니다.
• 해당 컬럼을 활용하는 검색 속도가 빨라집니다.

 

2-2. 유니크 인덱스 (Unique Index)

• 유니크 인덱스는 중복 값을 허용하지 않는 인덱스입니다.
• 기본 키(PK)나 유니크(UNIQUE) 제약 조건을 가지는 컬럼에 자동 생성됩니다.

구문:

CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼명);

✅ 예제:

CREATE UNIQUE INDEX emp_email_idx ON emp(email);

• email 컬럼에서 중복 값이 들어가지 않도록 보장합니다.

 

2-3. 다중 컬럼 인덱스 (Composite Index)

• 두 개 이상의 컬럼을 결합하여 만든 인덱스
• 컬럼 순서가 중요하며, 앞쪽 컬럼이 검색 조건에 포함될 때 최적화 효과가 큼

구문:

CREATE INDEX 인덱스명 ON 테이블명(컬럼1, 컬럼2);

✅ 예제:

CREATE INDEX emp_dept_idx ON emp(dept_id, emp_name);

• department_id + employee_name 컬럼을 결합하여 인덱스를 생성
• WHERE department_id = 10 검색 시 인덱스 활용 가능
• WHERE employee_name = 'John' 검색 시 인덱스 활용 불가 (앞쪽 컬럼이 포함되지 않아서)

 

2-4. 함수 기반 인덱스 (Function-Based Index)

• 컬럼 값을 변형하는 함수가 자주 사용되는 경우 함수 결과를 인덱스에 저장하여 최적화 가능

구문:

CREATE INDEX 인덱스명 ON 테이블명(함수(컬럼명));

✅ 예제:

CREATE INDEX emp_upper_idx ON emp(UPPER(emp_name));

• WHERE UPPER(employee_name) = 'JOHN' 쿼리 실행 시 인덱스 사용 가능

 

2-5. 비트맵 인덱스 (Bitmap Index)

• 데이터 값이 중복이 많을 때(카디널리티가 낮을 때) 사용
• OLAP 시스템에서 주로 사용

구문:

CREATE BITMAP INDEX 인덱스명 ON 테이블명(컬럼명);

✅ 예제:

CREATE BITMAP INDEX emp_gender_idx ON emp(gender);

• gender 컬럼이 Male/Female처럼 값이 적은 경우 비트맵 인덱스가 효과적

 

2-6. 클러스터드 인덱스 (Clustered Index)

• 물리적으로 테이블 데이터를 정렬하여 저장
• **Primary Key(PK)**가 있는 테이블에 자동 생성됨
• 한 개의 테이블에 하나만 생성 가능

✅ 예제:

CREATE CLUSTERED INDEX emp_pk_idx ON emp(emp_id);

• employee_id를 기준으로 데이터가 정렬되어 저장됨

 

2-7. 리버스 키 인덱스 (Reverse Key Index)

• 번호가 연속적으로 증가하는 컬럼에서 발생하는 I/O 병목을 방지
• 데이터 삽입 시, 키 값을 반대로 변환하여 저장

✅ 예제:

CREATE INDEX emp_reverse_idx ON emp(emp_id) REVERSE;

• 1001, 1002, 1003 → 1001(1001) → 2001(1002) → 3001(1003)
• 순차 증가 인덱스의 성능 문제를 해결할 때 유용

 

---

🔹 3. 인덱스 사용 시 주의할 점

• 인덱스를 너무 많이 만들면 오히려 성능이 저하될 수 있음
  • INSERT, UPDATE, DELETE 성능 저하 (인덱스도 같이 갱신해야 하기 때문)
  • 필요한 컬럼에만 인덱스를 생성해야 함
• 잘못된 인덱스 사용 예시
  • LIKE '%abc' → 인덱스 사용 불가 (앞쪽 % 때문)
  • WHERE 조건이 인덱스 컬럼이 아닐 때 → 인덱스 사용 불가
  • 함수를 적용한 WHERE → 기본적으로 인덱스 사용 불가

SELECT * FROM emp WHERE LOWER(emp_name) = 'john'; -- 인덱스 사용 불가

 

• EXPLAIN PLAN을 활용하여 실행 계획 분석
  • INDEX UNIQUE SCAN → 인덱스가 잘 활용됨
  • FULL TABLE SCAN → 인덱스가 사용되지 않음 (최적화 필요)

EXPLAIN PLAN FOR
SELECT * FROM emp WHERE emp_id = 100;

 

 

---

🔹 4. 인덱스 삭제 방법

✅ 기본 인덱스 삭제

DROP INDEX emp_name_idx;

✅ 유니크 인덱스 삭제 (UNIQUE 제약 조건 포함 시 먼저 삭제 필요)

ALTER TABLE emp DROP CONSTRAINT emp_email_uk; -- Unique 제약 조건 삭제
DROP INDEX emp_email_idx; -- 인덱스 삭제

 

 

---

🔹 5. 인덱스 성능 최적화 전략

✅ 효율적인 인덱스 사용을 위한 팁

1. 자주 사용하는 검색 조건을 인덱스로 설정
2. 중복 값이 많은 컬럼에는 비트맵 인덱스 고려
3. 인덱스를 남발하지 말고, 필요한 곳에만 사용
4. 다중 컬럼 인덱스를 생성할 경우, 검색 조건에 맞는 컬럼 순서 고려
5. EXPLAIN PLAN을 활용하여 인덱스 활용 여부 점검

 

---

🔹 6. 결론

• 인덱스는 데이터 검색 성능을 향상시키지만, 무조건 좋은 것은 아님
• 잘못된 인덱스 설계는 오히려 성능을 저하시킬 수 있음
• 적절한 인덱스 설계 + 실행 계획(EXPLAIN PLAN) 분석이 중요

🔥 "적절한 인덱스 설계가 성능 최적화의 핵심!" 🚀