인덱스의 단점

위의 그림을 보자. 회원 테이블과 이름 컬럼으로 만든 인덱스, 주소 컬럼으로 만든 인덱스, 연락처 컬럼으로 만든 인덱스가 있다.

select * 
from 회원
where 이름 = '나한일';

위 쿼리를 날리면 먼저 where절에 있는 이름 컬럼으로 인덱스가 만들어져있는지 찾아본다.
보니까 idx_이름 이라는 인덱스가 있다. 그 인덱스에 나한일이 어디 사냐고 물어본다.
인덱스에서 나한일의 ROWID를 보니 B3이다. B3 주소값을 가지고 HDD에서 B3으로 가 나한일을 메모리로 끌어올린다.(Single Block I/O)

where 주소 in ('서울', '부산');

위 쿼리는 주소 인덱스에서 서울과 부산의 주소값을 가지고 하드로 가므로 블록I/O가 두번 일어난다.

where 급여 = 80;

위 쿼리는 급여 컬럼에 대한 인덱스는 존재하지 않으므로 HDD에서 몽땅 관련 블록을 다 끌어올린다.(Multi Block I/O)

여기까지만 보면 모든 컬럼에 인덱스를 다 만들어 놓으면 좋지 않을까 라고 생각할 수 있다.
그러나 이는 좋을 때도 있지만, 안좋을 때도 있다는 것을 알아야 한다.

인덱스를 잘 만들어놓으면 select시에는 빠르게 해당 값을 검색할 수 있을 것이다.
하지만 테이블에 select만 있다는 보장이 없다. 문제는 DML시에 인덱스가 매우 비효율적일 수 있다는 것이다.

위의 테이블에 새로운 데이터인 나문희 회원에 대한 정보를 insert했다고 하자.
인덱스가 있을 시 오라클은 인덱스에 먼저 나문희를 쓴 후 테이블에 나문희를 쓴다.

보통 데이터가 들어오면 아래에 데이터를 넣게 되는데 그렇게 하면 인덱스 내에서 정렬이 깨져버리게 된다. 그러므로 오라클은 이에 대해 기존에 있던 이름 인덱스의 데이터를 반으로 나눠 분리하여 아래쪽은 새로운 블록에 할당을 하고 나문희가 들어가야 할 자리 밑의 데이터부터 한줄씩 밀고 그 사이에 나문희가 들어가도록 한다. 이러한 현상을 인덱스 분리(Index Split) 현상이라고 한다.
지금 이 시점에서 회원 테이블에는 아직도 나문희가 들어가있지 않다. 왜냐하면 인덱스에 먼저 삽입 후 테이블에 삽입하기 때문이다.
회원 테이블에는 이름, 주소, 연락처 인덱스가 있으므로 나미지 주소, 연락처 인덱스도 위와 같은 인덱스 분리를 통해 나문희를 삽입 후 테이블에 삽입한다.

만약에 인덱스가 하나도 없었다면 인덱스 분리 현상 없이 단번에 테이블에 나문희 데이터가 삽입되었을 것이다.
결론적으로 인덱스의 단점은 인덱스가 많으면 많을수록 DML작업시에는 취약하다.
select만 일어나는 테이블에는 인덱스 사용이 좋으나, DML이 많이 일어나는 테이블에는 좋지 않다.

그럼 이제 delete일 때를 생각해보자.
delete시 테이블에는 해당 데이터가 지워지지만 인덱스에는 해당 데이터가 지워지는 것이 아니라 단순히 그 데이터를 안쓴다고 체크만 된다. 즉, 쓰지는 않는데 용량은 그대로 가지고 있다.
전진 데이터를 지웠다고 해보자.

만약 이 상태에서 노태우 회원의 정보를 삽입한다고 하자.
테이블에는 노태우 데이터가 삽입이 되기만 하면 되지만, 인덱스는 안쓰는 전진 데이터까지 포함해서 새로운 노태우가 들어오는 것이기 때문에 인덱스 분리 현상은 계속 일어나게 된다.
이런 식으로 안쓰는 데이터 정보가 많은 망가진 인덱스는 아무리 인덱스를 써도 속도성능을 제대로 보장받을 수 없다.
그래서 가능하다면 주기적으로 인덱스 리빌드(Index Rebuild)작업을 해주는 것이다. 이 작업은 해당 인덱스에서 안쓰는 데이터는 싹 다 삭제해버린다.

이제는 update일 때를 생각해보자.
update시 테이블에서는 수정 한번이지만 인덱스에서는 삭제, 삽입이라는 두번의 과정이 일어나야 한다. 이것만 봐도 DML은 부하가 크다는 것을 짐작할 수 있다.
노태우를 전두환으로 update한다고 가정하자.

이렇게 테이블에서는 노태우를 전두환으로 수정하는 작업 한번이면 되지만, 인덱스에서는 노태우 데이터를 삭제하고 전두환 데이터를 삽입하는 두번의 작업이 필요하다.

---

기존에 4시간으로 잘 가고 있던 쿼리가 새로운 인덱스를 만들면서 그 인덱스를 탔더니 6시간이 걸렸다. 그래서 그 인덱스를 지우고 다시 원래 인덱스를 타게 하면 기존의 4시간으로 돌아올까? 그렇지 않다는 것이 문제이다.
이때는 기존에 4시간으로 잘 돌고있던 쿼리의 실행계획을 미리 백업받아놓는다.
그리고 새로운 인덱스를 적용시켜 본 후 문제가 생기면 Stored Outline 프로시저 를 이용해 백업받아두었던 실행계획을 가져오면 된다.

---

◆ 인덱스의 단점
  - DML에 치명적이다.
  - 기존에 돌고 있던 SQL에 악영향을 미칠 수 있다.

출처 : 겨울섬님 블로그 http://sksstar.tistory.com/88