클러스터드 인덱스와 넌 클러스터드 인덱스 차이점

클러스터드 인덱스와 넌 클러스터드 인덱스 차이점

• 클러스터 인덱스
  -입력된 데이터를 인덱스 기준으로 저장한다. 행 데이터를 인덱스로 지정한 열로 자동 정렬한 후에 루트 페이지를 만들게 된다.
  -인덱스 자체의 리프 페이지가 곧 데이터이다. 즉 테이블 자체가 인덱스이다. (따로 인덱스 페이지를 만들지 않는다.)
  -PK설정 시 그 칼럼은 자동으로 클러스터드 인덱스가 만들어진다.
  -데이터 위치를 바로 알기 때문에 그 데이터로 바로 접근할 수 있다. 넌클러스터드 인덱스 보다 검색 속도는 더 빠르다. 하지만 데이터의 입력/수정/삭제는 느리다.(인덱스를 생성/수정/삭제할 때 페이지 분할이 일어나며 느려진다. 데이터 페이지 전체를 다시 정렬)
  -테이블 당 한 개만 생성 가능하다. PK가 없을 때 UNIQUE NOT NULL로만 생성된 경우 클러스터드 인덱스가 생성되지만, PK 설정과 같이 들어가면 PK가 우선으로 클러스터디 인덱스로 생성되고 UNIQUE NOT NULL 컬럼은 보조 인덱스로 구성된다.

<클러스터드 인덱스 생성>
ALTER TABLE user		// 테이블명
ADD CONSTRAINT PK_user_id	// PK명
PRIMARY KEY (id);		// 컬럼명

<클러스터드 인덱스 삭제>
ALTER TABLE user
drop primary key;

• 넌 클러스터드 인덱스 (보조인덱스)
  -테이블 당 여러 개 생성 가능하고, UNIQUE 혹은 UNIQUE NULL에 의해 자동으로 생성한다.
  -UNIQUE로 설정된 인덱스는 동일한 데이터 값이 입력될 수 없다. (고유한 인덱스, 디폴트는 중복 허용 인덱스)
  -데이터 페이지를 건들지 않고 , 별도의 장소에 인덱스 페이지를 생성합니다. 인덱스 페이지의 리프 페이지에 인덱스로 구성한 열을 정렬하고 데이터 위치 포인터를 생성
  (인덱스 페이지를 한번 거쳐서 데이터에 접근하는 방식)
  -페이지 번호 + #오프셋'이 기록되어 바로 데이터 위치를 가리킨다.
  -검색 속도는 느리지만, 데이터의 입력,수정,삭제가 더 빠르다. (클러스터드 인덱스보다 페이지 분할이 적게 일어남)

<넌 클러스터드 인덱스>
ALTER TABLE user
ADD CONSTRAINT UK_user_email		// 인덱스명
UNIQUE (email);				// 컬럼명

CREATE UNIQUE INDEX UK_user_email	// 중복 값 허용하지 않는 인덱스, 인덱스명
ON user (email);		   	// 테이블 이름 (컬럼명)

// 두 개 열을 조합한 인덱스 생성
CREATE INDEX idx_user_id_email		// 인덱스명
ON user (id, email);			// 테이블명 (컬럼명1, 컬럼명2)

인덱스 삭제 작업 시 주의사항

인덱스를 모두 제거할 때는 보조 인덱스부터 삭제하도록 한다. 클러스터드 인덱스부터 제거하면 루트 페이지가 삭제되기 때문에 보조 인덱스의 리프 페이지는 모두 루트 페이지를 지정하고 있으므로 보조 인덱스의 리프 페이지가 페이비번호+#오프셋으로 바뀌어야 하는데 보조 인덱스를 다시 재구성해야 한다. 하지만, 재구성 후에 보조 인덱스도 삭제되기 때문에 고생해서 재구성한 것을 또 삭제해야 한다.

DROP INDEX email ON user
ALTER TABLE user DROP INDEX email;

*참고 자료 - https://junghn.tistory.com/entry/DB-%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0-%EC%9D%B8%EB%8D%B1%EC%8A%A4%EC%99%80-%EB%84%8C%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0-%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EA%B0%9C%EB%85%90-%EC%B4%9D%EC%A0%95%EB%A6%AC
https://gocoder.tistory.com/1826