클러스터드 인덱스와 넌 클러스터드 인덱스

참고 : https://lng1982.tistory.com/144

생각 : 클러스터드 인덱스는 테이블당 하나만 생성되며 pk가 이 클러스터드 인덱스다.
클러스터드 인덱스는 정렬되어있기 때문에 leaf level의 인덱스가 필요하지 않아서 db 용량을 덜 차지한다.
저장할때 물리적으로 정렬하여 저장한다
넌 클러스터드 인덱스는 검색 향상을 위해 사용자가 추가하는 것이 해당된다.
한 테이블당 249개까지 생성이 가능하다
넌 클러스터드 인덱스는 별도의 공간에 테이블을 생성하여 데이터를 정렬하고 leaf level의 인덱스가 필요하다
테이블 전체 데이터 집합 중에 특정 조건에 의해 데이터가 걸러지는 비율을 선택도라 하며 클러스터드 인덱스는 정려되어 있기 대문에 넌 클러스터드 인덱스보다 높다




MySQL은 PK에 대해 클러스터드 인덱스를 생성한다.
클러스터드 인덱스는 B+ 트리 자료구조로 리프노드 = 데이터이다.
클러스터드 인덱스 구조에서 데이터 페이지는 키(PK) 값으로 정렬되어 있다.
FK 인덱스 또는 Create Index 명령으로 생성한 인덱스는 넌클러스터드 인덱스이다.
MySQL의 넌클러스터드 인덱스에서 리프노드는 PK 값을 가진다.


클러스터드 인덱스
- 물리적으로 행을 재배열
- 인덱스 페이지 용량이 넌 클러스터드 인덱스 페이지 용량보다 작다
- 30% 이내에서 사용해야 좋은 선택도
- 테이블당 1개

넌 클러스터드 인덱스
- 물리적으로 재배열 하지 않는다
- 클러스터드 인덱스 페이지 용량보다 크다
- 3% 이내에서 사용해야 좋은 선택도
- 테이블당 249개

차이
CREATE TABLE TBL_CLUSTERED_TEST (
LOG_DATE CHAR(8) NOT NULL,
MEDIA_ID CHAR(1) NOT NULL,
PROCEEDS DOUBLE DEFAULT NULL,
PRIMARY KEY (LOG_DATE,MEDIA_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130618', 'A', 1000);
INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130619', 'A', 1000);
INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130619', 'C', 2000);
INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130619', 'B', 1000);
INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130613', 'B', 3000);
pk가 log_data, media_id 복합키로 이루어져있고 pk는 클러터드 인덱스가 된다.
클러스터드 인덱스는 물리적으로 행을 재배열하기 때문에 인덱스 키 순서인 log_date - media_id 순으로 오름차순으로 재배열되어 저장된다.
따라서

mysql> select * from tbl_clustered_test;
+----------+----------+----------+
| LOG_DATE | MEDIA_ID | PROCEEDS |
+----------+----------+----------+
| 20130613 | B | 3000 |
| 20130618 | A | 1000 |
| 20130619 | A | 1000 |
| 20130619 | B | 1000 |
| 20130619 | C | 2000 |
+----------+----------+----------+
와 같이 정렬되어 저장되는 것이다.

넌클러스터드 인덱스는 일반적으로 조회문 성능 향상을 위해 우리가 추가하는 인덱스가 넌 클러스터드 인덱스다.
이것은 클러스터드 인덱스와 다르게 물리적으로 정렬되어 저장되지 않는다.
넌 클러스터드 인덱스는 별도의 공간에 인덱스 테이블을 생성하여 데이터를 정렬한다.

클러스터드 인덱스는 이미 데이터가 정렬되어 있는 상태로 저장되기 때문에 leaf level의 인덱스 테이블이 필요치 않다.
즉, 클러스터드 인덱스는 넌 클러스터드 인덱스보다 DB 용량을 덜 차지한다.

[선택도]
선택도라함은 테이블의 전체 데이터 집합 중에 특정 조건에 의해 데이터가 걸러지는 비율을 얘기한다.
여기서 클러스터드 인덱스는 테이블의 데이터가 물리적으로 정렬되어 저장되어 있기 때문에 부분 범위 처리에 활용하면 적은 I/O를 통해 원하는 데이터를 추출할 수 있다.
예를 들어서 7, 8, 9의 데이터를 추출한다고 했을 때 클러스터드 인덱스는 단 한 번의 I/O를 통해서 데이터를 추출할 수 있다. 왜냐하면 7, 8, 9는 3번 데이터 페이지에 모두 존재하기 때문이다.


넌 클러스터드 인덱스는 leaf level 인덱스 페이지에 테이블 데이터의 위치가 저장되어 있기 때문에 테이블 데이터로 직접 access가 가능하다.
위의 예에서 설명한 것과 같이 넌 클러스터드 인덱스에서 7, 8, 9의 데이터를 추출하게 된다면 2번의 I/O가 발생한다. 왜냐하면 7값은 2번 데이터 페이지에 존재하고 8, 9는 3번 데이터 페이지에 존재하기 때문이다.
그래서 넌 클러스터드 인덱스는 선택도의 범위가 3% 이내일 때 효율적이다.



[최대 갯수]
클러스터드 인덱스는 물리적으로 정렬되어 있는 상태로 데이터가 저장되기 때문에 딱 1개만 생성 가능하다. 만약 2개의 클러스터드 인덱스가 생성된다면 데이터 정렬이 꼬이기 때문에 딱 한 개만 생성할 수 있는 것이다. 이에 반해 넌 클러스터드 인덱스는 인덱스 테이블을 별도로 생성하여 데이터를 정렬하기에 충분히(249개) 생성할 수 있다.