8. index(인덱스)(2)

인덱스의 생성

인덱스는 데이터 검색을 빨리 하기 위해 사용한다. 하지만 인덱스를 생성했다고 해서 데이터검색이 무조건 빨라지는 것은 아니다. 데이터의 양이 별로 없거나 데이터값이 몇종류 안 되어 선택도가 높으면 인덱스가 없는게 더 빠를 수 있다. 여기서 선택도란 '1/서로 다른 값의 개수'를 말하는 것으로, 예를 들어 100개의 행을 가진 테이블에 값이(남, 여) 두 가지라면 선택도가 높다고 할 수 있다.

 

이처럼 의미없이 인덱스를 생성하면 검색이 더 느려지고 저장공간만 낭비하게 된다. 따라서 인덱스 생성에 앞서 다음의 고려사항을 충분히 살펴봐야 한다.

• 인덱스는 where절에 자주 사용되는 속성이어야 한다.
• 인덱스는 조인에 자주 사용되는 속성이어야 한다.
• 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다.(테이블당 4~5개 정도 권장).
• 속성이 가공되는 경우 사용하지 않는다.
• 속성의 선택도가 낮을 때 유리하다.(속성의 모든 값이 다른 경우).

인덱스 생성

인덱스의 수정과 삭제

B-tree 인덱스는 데이터의 수정, 삭제, 삽입이 잦으면 노드의 갱신이 주기적으로 일어나 단편화 현상이 나타난다. 단편화란 삭제된 레코드의 인덱스 값 자리가 비게 되는 상태를 말하는데, 이는 검색 시 성능 저하로 이어진다.

MYSQL 에서는 인덱스 수정은 삭제 후 다시 만들어 준다.

인덱스 수정

인덱스가 인기 있는 이유는?

1. SQL문을 변경하지 않아도 성능 개선을 할 수 있다.
2. 테이블의 데이터에 영향을 주지 않는다.
3. 일정한 효과를 기대할 수 있다.

인덱스를 만들 때 어떤 기준으로 만드는게 좋을까?

• 크기가 큰 테이블만 만든다.

크기가 작은 테이블에는 인덱스나 풀 스캔이나 큰 차이가 없다.( 테이블의 크기 때문에 성능이 떨어진다 싶을 때가 인덱스가 필요하다는 신호)

• 기본키 제약이나 유일성 제약이 부여된 열에는 불필요하다.

PK가 부여된 열에는 자동으로 인덱스가 작성되어 있고, 유일성 제약이 붙어있는 컬럼 또한 같다.

이 2가지 제약이 붙은 열에 암묵적으로 인덱스가 작성된 이유는 값의 중복체크를 하려면 데이터를 정렬해야하는데 인덱스를 작성해 정렬하는 것이 편리하기 때문이다.

•  Cardimality(카디널리티)가 높은 열에 만든다.

인덱스를 만드는 열을 결정하는 지침으로써 가장 중요한 것이 카디널리티이다. 'Cardinality'란 '값의 분산도'를 뜻한다. 특정 열에 대해 많은 종류의 값을 가지고 있다면 Cardinality가 높다. 하지만, 값의 종류가 적으면 Cardinality가 낮다는 의미이다.

 

'주민등록번호' 는 한국 국민에게 중복되지 않도록 고유의 번호를 지니고 있고, 대한민국 국민의 수만큼 종류의 값을 지니고 있는데, 이는 Cardinality가 높다고 할 수 있다. 하지만, 성별은 남자, 여장, 미상의 3종류 밖에 얻을 수 없어 Cardinality가 매우 낮다고 할 수 있다.

•  Cardinality가 낮은 열에 인덱스 효과를 기대하기 어려운 이유는 인덱스 트리를 따라가는데 조작이 증가(중복된 값이 많기 때문에 걸러야함)해서 오버헤드도 증가해 인덱스의 혜택을 받기 어렵다.

인덱스의 역효과

• 인덱스 갱신의 오버헤드로 갱신 처리의 성능이 떨어진다.

인덱스는 테이블에 새로운 데이터가 추가(insert)되거나 기존 데이터에 갱신(update) 또는 삭제(delete)되면 자동으로 인덱스 자신도 갱신하는 기능을 갖추고 있다.

 

인덱스가 없으면 추가, 갱신, 삭제 시 해당 row만 처리하면 되지만, 인덱스가 추가되면 인덱스 또한 갱신하기 때문에 오버헤드가 발생한다.

인덱스는 SELECT문을 고속화한 대신 INSERT나 UPDATE, DELETE처럼 갱신을 늦춘 댓가라고 볼 수 있다.

•  의도한 것과 다른 인덱스가 사용된다.

한 개의 테이블에 복수의 인덱스를 작성한 경우 발생하는 문제다. 한 테이블에 다양한 SQL문이 이용되고, 거기에 대응해 작성된 인덱스가 많아질 수 있다.

 

느린 SQL 문에 댛대한 실행계획을 보면 '왜 이 인덱스를 쓰고 있지'라는 의문이 드는 경우가 있다. 더 빠른 인덱스가 있음에도 의도한 것과는 다른 인덱스를 사용해 오히려 느려지는 케이스가 발생할 수 있다.

 

이런 경우가 발생하는 이유는 옵티마이저가 만능은 아니기 때문이다. 옵티마이저가 고도의 실행계획을 만들도록 설계되어 있긴 해도 인덱스 후보가 많으면 옵티마이저도 인간처럼 헤메게 된다.