Database - Index, Clustered Index vs Non-Clustered Index

Index 종류

• B-tree - high cardinality, re-balanced needed, logN
• Hash - Hash Function
• BitMap - low cardinality columns ( ex. boolean )
• Specialized Index

Index Pros and Cons

index 는 항상 모든 케이스에 적절한것은 아니다. 적은 수의 rows 의 table 의 경우 index search 보다 순차적 search 가 빠를 수 있다. 또한 table 의 대부분의 rows 나 전체 rows 를 조회할 때는 순차적 search 가 더 빠를 수 있다. 그러나, 대부분의 경우 index 가 조회 성능을 향상 시킨다.

1. Where 절을 통한 쿼리
2. Index 에 해당하지 않는 행은 검색에서 제외한다.
3. Join 에서도 성능이 좋다
4. min, max 값을 찾기 좋다.
5. group by, order by 의 성능에도 좋다.
6. table 에서 직접 조회하기 보다, index 를 통해서 조회한다.

index 는 SELECT query 의 속도를 높여주는데 좋은 반면에, 실질적으로 UPDATE, INSERT, DELETE 는 속도를 저하 시킨다. 왜냐하면 MySQL 은 table 에 변화가 일어나면 data 를 reindex 한다.

만약, 조회보다 변경이 많은 테이블에서는 인덱스를 사용하지 않는 것이 좋을 수도 있다.

또한 색인은 저장해야하는 추가 데이터가 있는 테이블로 데이터베이스에서 추가 공간을 차지한다는 점도 고려해야한다. 이는 큰 고려 사항은 아니지만 데이터베이스에 허용하는 공간에 따라 달라질 수 있다.

Clustered Index vs Non-Clustered Index

• Clustered Index - PK 제약 조건과 같이 생성된 테이블은 database engine 이 자동으로 clustered index 를 생성한다. 따라서 data 는 key, value 에 맞게 정렬되어 저장된다.
• Non-Clustered Index - Unique 제약 조건과 같이 생성된 테이블은 database engine 이 자동으로 non-clustered index 를 생성한다. non-clustered index 는 non-clustered index key value 를 포함한다. 그리고 각각의 key value entry 는 data row 를 가리키는 pointer 를 가진다.

• A Disadvantage to Clustered Indexes

만약 record 를 clustered index 가 걸린 컬럼의 값을 변경하게 되면, database 는 전체 row 를 정렬하기 위해서 re-balancing 해야 한다.

해당 행동은 근본적으로 update 를 Delete 후 Insert 작업으로 변환한다. 이는 명백한 성능 저하를 일으킨다. clustered index 는 PK 와 FK 에서 발견할 수 있다. key values 는 일반적으로 한번 insert 되면 변경되지 않기 때문이다.