[SQL] 인덱스(INDEX)의 장점과 단점, 활용 방법

 

인덱스의 종류와 생성, 변경, 삭제, 조회에 대한 내용은

아래 글을 참고해주세요!

 

[SQL] 인덱스(INDEX)의 종류와 생성, 변경, 삭제 방법 (tistory.com)

[SQL] 인덱스(INDEX)의 종류와 생성, 변경, 삭제 방법

 

이번 글에서는 인덱스를 어떻게 활용하는지 다뤄보고

인덱스의 장점과 단점에 대해서 생각해보고자 한다.

 

 

인덱스 활용방법

 

일반적으로 Oracle에서는 인덱스를 사용하는지 여부를

직접 확인하기 위해서는 "쿼리 실행 계획"을 살펴봐야 한다.

여기서 "실행 계획"은 Oracle 옵티마이저가 쿼리를

실행하는 방식을 설명하는 정보를 말한다.

CREATE table member3(
id varchar2(10) primary key,
name varchar2(10) not null,
department varchar2(10)
);

CREATE INDEX member_index ON member3 (name, department);

INSERT INTO member3 (id, name, department) VALUES ('1', 'John', 'Sales');
INSERT INTO member3 (id, name, department) VALUES ('2', 'Alice', 'HR');
INSERT INTO member3 (id, name, department) VALUES ('3', 'Bob', 'IT');
INSERT INTO member3 (id, name, department) VALUES ('4', 'Emma', 'Marketing');
INSERT INTO member3 (id, name, department) VALUES ('5', 'Michael', 'Sales');
INSERT INTO member3 (id, name, department) VALUES ('6', 'Sophia', 'IT');
INSERT INTO member3 (id, name, department) VALUES ('7', 'David', 'HR');
INSERT INTO member3 (id, name, department) VALUES ('8', 'Olivia', 'Marketing');
INSERT INTO member3 (id, name, department) VALUES ('9', 'James', 'Sales');
INSERT INTO member3 (id, name, department) VALUES ('10', 'Emily', 'IT');

//쿼리 실행계획 설명
EXPLAIN PLAN FOR
SELECT * 
FROM member3 
WHERE name = 'John' AND department = 'Sales';

//쿼리 실행계획 조회
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

 

라인 450~453에서 쿼리 실행계획을 설명하는 명령문을 실행한 뒤에

해당 설명을 출력하는 코드를 455번에서 실행하여

인덱스를 사용하는지 여부를 확인할 수 있다.

인덱스가 사용되면 위 결과에서 나오는 것과 같이

결과 8번 라인에서 해당 인덱스가 실행 계획에 나타난다.

 

 

인덱스를 사용하여 특정 테이블의 튜플을 조회하려면

일반적으로 WHERE 절에서 인덱스를 활용할 수 있다.

예를 들어, "member3" 테이블의 "department" 컬럼에 대한

인덱스를 사용하여 "Sales" 부서에 속한 튜플을 조회하려면

다음과 같이 쿼리를 작성할 수 있다.

SELECT * 
FROM member3 
WHERE department = 'Sales';

 

위의 쿼리는 "member3" 테이블에서 "department"가 'Sales'인

모든 튜플을 조회한다. 이때 Oracle 옵티마이저는

필요한 경우 적절한 인덱스를 선택하여 쿼리의 성능을 최적화한다.

 

이러한 과정을 통해 인덱스를 생성하여 

검색 속도를 높이고 정렬된 데이터를 조회할 수 있지만,

Oracle은 자동으로 옵티마이저를 사용하여

가장 효율적인 실행 계획을 선택하기 때문에

인덱스를 사용하지 않더라도 쿼리의 성능이 충분히 높을 수 있다.

인덱스를 사용하는 것이 반드시 성능 향상을 보장하지는 않는다.

인덱스를 사용할지 여부는 검색 속도 향상측면 뿐만 아니라

데이터의 분포, 인덱스의 품질, 테이블 크기 등

여러 요인에 의해 결정되어야 한다.

 

 

인덱스의 장점과 단점

 

인덱스의 장점은 특히 데이터의 범위가 넓고 

중복값이 적고, 조회해야하는 컬럼이 많거나 

정렬된 상태가 유용한 컬럼을 검색할 경우 효과적이다.

즉, 여러 개의 컬럼의 값을 정렬된 내용으로

신속하게 검색할 때 유용하게 쓰일 수 있다.

 

꼭 위와 같은 경우가 아니더라도

SELECT문으로 조회할 경우,

WHERE 조건절에 '='로 비교되는 컬럼을

대상으로 인덱스를 생성하면 검색 속도를

높일 수 있다는 점도 큰 장점이다.

 

하지만 인덱스가 어떤 상황에서든 좋은 효과를 내는 것은 아니다.

 

예를 들어 성별과 같이 값의 범위가 적은 컬럼인 경우,

(많아 봤자 여성, 남성, 기타 정도라면)

인덱스로 검색하고 나서도 40대, 서울 거주, 여성, ... 등과 같이

특정 데이터를 조회한다고 하면 인덱스 조회 외에도 결국

추가 검색작업이 필요하다. 

이런 경우에는 굳이 인덱스를 지정하여 사용할 필요가 없다.

 

게다가 인덱스를 관리하려면 추가 작업이 필요하다.

인덱스를 생성할 때는 인덱스 자체에 키와 매핑 주소 값을 별도로 저장한다.

(추가 저장 공간이 필요하다는 의미이기도 하다.)

 

따라서 테이블에 데이터를 입력, 삭제, 수정할 경우 

인덱스에 저장된 정보도 이에 따라 생성과 수정이 이루어진다. 

 

그러므로 인덱스를 너무 많이 만들면, 

데이터 insert, delete, update 시 인덱스에 저장된 값에도 변화가 생겨서

부하가 발생할 수 있고, 이는 전체적인 데이터베이스의 성능을 저하시킬 수 있다.

 

*참고로 select의 경우는 데이터에 변경이 없기 때문에

인덱스에 저장된 정보가 변경되지 않아도 되어서 별도로 부하를

발생시키지 않는다.

 

이 외에도 여러 가지 취약점이 있기 때문에 인덱싱할 컬럼을 신중하게 선택하고

정기적으로 성능을 모니터링해야 한다. 

모든 열을 인덱싱하기 보다는 자주 검색하거나 조인하는 중요한 열인 경우

인덱싱을 하는 것이 더 나을 수 있다. 또한 성능을 향상시키지 않거나

거의 사용하지 않는 인덱스는 제거해주는게 혼잡을 줄일 수 있다. 

 

인덱스를 만들었다고 끝이 아니라, 인덱스를 제대로 사용하도록

효율적인 SQL문을 작성하는 것이 더 중요하다는 점도 생각해보면 좋겠다.

 

끝으로 인덱스 생성 시 고려해야 할 사항을 정리해보자.

인덱스는 결국 '적합한' 경우에만 '적당히' 만들면 좋다..!

'적합한', '적당히'를 분별하는 게 참 어렵다... 

 

인덱스 생성 시 고려할 사항

 

1. 일반적으로 테이블 전체 튜플 수의 대략 15% 이하의 데이터를 조회할 때 인덱스를 생성한다.

** 물론 15%는 정해진 값이 아니며 테이블 건수, 데이터 분포 정도에 따라 달라질 수 있다.

너무 많은 튜플을 조회하기 위해 사용한다면 비효율적일 수 있다는 정도만 확인해도 충분하다.

 

2. 테이블 건수가 적다면(코드성 테이블) 굳이 인덱스를 만들 필요가 없다.

**데이터 추출을 위해 테이블이나 인덱스를 탐색하는 것을 스캔(Scan)이라고 하는데,

테이블 건수가 적으면 인덱스를 경유하기보다 테이블 전체를 바로 스캔하는 것이 빠르다.

 

3.  데이터의 유일성 정도가 좋거나 범위가 넓은 값을 가진 컬럼을 인덱스로 만드는 것이 좋다.

 

4. NULL이 많이 포함된 컬럼은 인덱스 컬럼으로 만들기에 적당하지 않다.

 

5. 복합 인덱스(결합 인덱스)를 만들 때는 컬럼의 순서가 중요하다.

** 보통 자주 사용되는 컬럼을 순서상 앞에 두는 것이 좋다.

 

6. 테이블에 만들 수 있는 인덱스 수의 제한은 없으나 너무 많이 만들면 오히려 성능 부하가 발생한다.