[SQLD-SQL 최적화 기본원리]2.인덱스 기본

데이터베이스에서 인덱스(Index)는 테이블의 데이터를 빠르게 조회하기 위해 사용되는 데이터 구조입니다.

인덱스는 테이블의 특정 컬럼 또는 컬럼들의 조합에 대해 생성되며, 검색 성능을 크게 향상시킬 수 있습니다.

하지만, 인덱스가 많아지면 쓰기 작업(INSERT, UPDATE, DELETE) 성능에 영향을 줄 수 있으므로 적절한 설계가 중요합니다.

 

대용량 데이터 조회 (select)  시 index를 사용하면 실행시간을 단축할 수 있습니다. 

실무에도 자주 사용되므로 개념 및 사용법(생성, 삭제 등)을 익혀두시면 유용합니다. 

1. 인덱스의 주요 개념

• B-tree 인덱스: 가장 일반적인 인덱스 유형으로, 데이터가 정렬된 상태로 저장되어 빠른 검색이 가능.
• 비트맵 인덱스: 데이터 값이 적고 반복적으로 나타날 때 적합한 인덱스.
• 유일 인덱스(Unique Index): 컬럼 값의 중복을 허용하지 않음.
• 복합 인덱스(Composite Index): 여러 컬럼을 조합하여 생성된 인덱스.
• 함수 기반 인덱스(Function-Based Index): 특정 컬럼에 대해 함수 결과를 기준으로 생성된 인덱스.

---

2. 인덱스 생성

-- 기본 인덱스 생성
CREATE INDEX index_name ON table_name(column_name);

-- 복합 인덱스 생성
CREATE INDEX index_name ON table_name(column1, column2);

-- 유일 인덱스 생성
CREATE UNIQUE INDEX index_name ON table_name(column_name);

---

3. 인덱스 조회

현재 데이터베이스에 생성된 인덱스를 확인하려면 다음과 같은 쿼리를 사용할 수 있습니다:

SELECT index_name, table_name, uniqueness
FROM user_indexes;

---

4. 인덱스 삭제

인덱스를 삭제하려면 다음 명령을 사용합니다:

DROP INDEX index_name;

---

5. 인덱스 관리 주의점 

• 인덱스 사용: WHERE, JOIN, GROUP BY, ORDER BY 절에서 컬럼이 자주 사용될 때 효과적.
• 쓰기 성능: 인덱스는 데이터를 추가하거나 변경할 때도 갱신되므로 과도한 인덱스는 쓰기 성능을 저하시킬 수 있음.
• 분석 도구 활용: Oracle의 EXPLAIN PLAN이나 AUTOTRACE를 활용하여 인덱스 사용 여부를 확인.

---

6. EXPLAIN PLAN으로 인덱스 확인

쿼리에 인덱스가 사용되고 있는지 확인하려면 EXPLAIN PLAN 명령을 사용합니다:

EXPLAIN PLAN FOR
SELECT * FROM table_name WHERE column_name = 'value';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

이렇게 하면 쿼리 실행 계획에서 인덱스 스캔(Index Scan) 여부를 확인할 수 있습니다.

---

7. 자동 인덱스 관리

Oracle 19c 이상에서는 Automatic Indexing 기능이 도입되어 쿼리 성능을 기반으로 자동으로 인덱스를 생성, 삭제, 관리합니다. 이 기능은 DBA의 인덱스 관리 부담을 줄여줍니다.

 

 

인덱스 사용사례 다음 페이지 참조