[Oracle] 인덱스(Index) 생성/조회/삭제 및 주의사항

---

회사 프로젝트 관리를 하던중에 개발 서버에서는 별다른 문제 없이 작동했던 쿼리가 운영 서버에서는 굉장히 느리게 작동하는 문제를 발견했다.(웹 페이지 하나 띄우는데 거의 10초가 넘어갔다.)

원인을 찾던 중 데이터를 가져오는 쿼리를 날리는 시점에서 속도차이가 나는 것을 확인하고 이를 개선하면서 index 개념을 공부하고 정리하기 위해 포스팅을 남긴다.

 인덱스(Index)란?

인덱스는 데이터베이스 테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체이며 일종의 색인기술이다. 웹페이지의 즐겨찾기 기능이나 책의 책갈피 기능이라고 생각하면 될 것 같다.

 인덱스 생성

--인덱스 생성
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......);

--주로 사용하는 인덱스명
IDX_[테이블명]
[테이블명]_IDX
INDEX_[테이블명]
[테이블명]_INDEX

 인덱스 조회

--인덱스 조회
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = '[테이블명]';

필요없는 정보는 지우고 인덱스가 참조하는 컬럼까지 보고 싶다면 아래 쿼리를 추천한다

--인덱스 조회(참조 컬럼 포함)
SELECT	A.TABLE_NAME
    	, A.INDEX_NAME
    	, A.COLUMN_NAME 
FROM ALL_IND_COLUMNS A 
WHERE A.TABLE_NAME = '[테이블명]';

 인덱스 삭제

인덱스가 쌓이면 장기적으로 데이터베이스에는 좋지않은게 당연하다.

사용하지 않거나 실수로 생성한 인덱스는 그때그때 삭제해주는 것이 좋다.

--인덱스 삭제
DROP INDEX [인덱스 명];

 인덱스 주의사항

인덱스를 생성한 테이블에 DML 작업(INSERT, DELETE, UPDATE)시 인덱스에도 수정작업이 동시에 발생하기 때문에 과도하게 많은 인덱스를 생성해 놓으면 오히려 성능 부하가 걸릴 수 있다. 

 

• 따라서 테이블 건수가 적당하다면 인덱스를 사용할 필요가 없다. 테이블 전체를 스캔하는 것이 더 빠르다.
• 인덱스 생성시 컬럼의 순서가 중요하다. 보통, 자주 사용하는 컬럼을 앞에 지정한다.
• 테이블에 인덱스 생성시 제한은 없으나, 5개 미만의 인덱스를 가지는 것을 추천한다
• 검색을 위주로 하는 테이블에 생성하는 것이 좋다.
• 속도개선이 필요하다면 일단 SQL 쿼리부터 수정을 해보고 마지막 수단으로 사용하자

필자도 인덱스로 쿼리 코스트를 엄청 줄일 수 있었지만 CRUD가 발생하는 테이블이었고 앞서 말한 성능부하가 걱정되어 쿼리로 최대한 속도 개선을 진행했다.

---

오늘은 인덱스의 간단한 개념과 생성/조회/삭제 및 주의사항을 알아보았다.

다음 포스팅에서는 인덱스와 힌트를 사용해서 쿼리 속도 개선하는 방법을 정리하도록 하겠다.

 

관련글 : [데이터베이스/Oracle] - [Oracle] 인덱스와 힌트를 사용해 쿼리 속도 개선하기