인덱스(Index)란?
인덱스란 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다. 단어의 뜻 그대로 책의 색인과 같다. 우리는 책에서 원하는 내용을 찾고 싶을 때, 책의 저자가 책의 맨 앞 또는 맨 뒤에 만들어놓은 색인 페이지에서 원하는 단어가 있는 페이지를 쉽게 찾을 수 있다. 이 단어들은 가나다 혹은 abc 순서로 정렬되어있기에 쉽게 찾을 수 있다.
인덱스를 사용하면, 조회하는 SELECT 쿼리 외에도 UPDATE 와 DELETE 의 성능도 함께 향상된다. 왜냐하면 수정 & 삭제를 할 Row 를 찾는 조회 과정이 선행되기 때문이다.
DELETE FROM user
WHERE name = 'giken';
인덱스를 다루는 쿼리
인덱스 생성
--문법
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)--예제 1) 기본
CREATE INDEX EX_INDEX ON B(B_NO, B_TITLE);
--예제 2) 컬럼 값 중복 허용 x 인 인덱스 생성
CREATE[UNIQUE] INDEX EX_INDEX ON B(B_NO, B_TITLE);
인덱스 조회
--문법
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = '테이블명';--예제
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS';
인덱스 삭제
--문법
DROP INDEX [인덱스 명]--예제
DROP INDEX EX_INDEX;
인덱스 리빌드
인덱스 리빌드를 하는 이유
DB의 쿼리 수행 속도 저하의 문제 중 하나는 쿼리 튜닝이 문제일 수도 있지만, 생성한 인덱스에서 INSERT, UPDATE, DELETE 등의 작업을 반복하면서 인덱스의 밸런스가 깨졌을 경우의 가능성도 있다.
인덱스의 밸런스가 깨졌다는 의미는
생성된 인덱스는 트리구조를 가지는데 데이터들이 무작위로 삽입, 수정, 삭제 등이 오랫동안 일어나면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어진다.
이것을 인덱스의 밸런스가 깨졌다고 얘기하고, 이러한 현상으로 인해 인덱스에서 무거워진 쪽은 검색 속도가 떨어지므로 주기적인 리빌딩 작업을 하는 것이 좋다.
인덱스 리빌드할 대상 조회 쿼리
SELECT I.TABLESPACE_NAME,I.TABLE_NAME,I.INDEX_NAME, I.BLEVEL,
DECODE(SIGN(NVL(I.BLEVEL,99)-3), 1, DECODE(NVL(I.BLEVEL,99), 99, '?', 'Rebuild'), 'Check') CNF
FROM USER_INDEXES I
WHERE I.BLEVEL > 4
ORDER BY I.BLEVEL DESC;-> 외우는 것보단 가져다 쓰는 것이 옳다고 보인다.
인덱스 리빌드
--문법
ALTER INDEX [인덱스명] REBUILD;--예제
ALTER INDEX EX_INDEX REBUILD;이렇게 일일히 리빌드 시키기 귀찮다면 ?
USER_INDEXES에 있는 인덱스를 조회하여, 인덱스 리빌드 쿼리를 만들면 한번에 리빌드가 됩니다.
전체 인덱스 리빌드 쿼리문 만들기
SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD; 'FROM USER_INDEXES;
'언제' 인덱스를 사용해야할까?
DBMS 는 index를 항상 정렬된 상태로 유지해야한다. 왜냐하면 인덱스에서는 보통 B+Tree 라는 자료구조를 사용하는데 이진검색을 통해서 인덱스가 적용된 컬럼의 원하는 데이터를 찾기 때문이다. (위의 그림에서 Index 테이블 내에서 company_id 를 찾을 때 이진검색을 한다)
그렇기 때문에 인덱스 테이블이 참조하는 테이블에서 INSERT, UPDATE, DELETE 가 자주 수행된다면 인덱스 테이블에서는 데이터를 정렬하면서 삽입, 삭제, 수정이 이루어지기 때문에 전체적인 성능 저하를 초래할 수 있다.
인덱스는 검색에 최적화된 기능이기 때문에
- 삽입, 삭제, 수정이 자주 일어나는 비즈니스 로직인지?
- 인덱스가 참조할 테이블의 용도가 무엇인지?
에 따라서 인덱스 사용 여부를 고민해봐야 한다.
[배달의 민족]
사용자가 주로 지역별 음식점을 조회하고 검색하는 기능이 서비스의 주요 기능이기 때문에 인덱스 기능을 잘 사용하면 DB 성능을 최적화 할 수 있을 것이다.
[인스타그램]
소셜 미디의 특성상 사용자들이 끊임없이 게시글을 생성, 수정, 삭제한다. 이 경우에는 인덱스를 사용하는 것이 오히려 성능 저하를 야기할 수 있겠다.
[참조한 블로그]
https://mangkyu.tistory.com/96
