8.8 클러스터링 인덱스

8.8 클러스터링 인덱스

클러스터링 : 여러개를 하나로 묶는다.

MySQL 에서의 클러스터링은 테이블의 레코드를 비슷한 것들(프라이머리키를 기준으로)끼리 묶어서 저장하는 형태로 구현.

InnoDB 만 지원하고 나머지 스토리지 엔진은 지원되지 않는다.

8.8.1 클러스터링 인덱스

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용됨.

=> 즉 프라이머리 키값에 의해 레코드의 저장 위치가 결정됨.

=> pk가 변경되면 레코드의 물리적인 저장위치가 변경되어야함.

클러스터링 인덱스로 저장되는 테이블은 pk 기반의 검색이 매우 빠르지만 저장이나 pk의 변경이 상대적으로 느림.

구조는 B-Tree 와 비슷하지만 세컨더리 인덱스를 위한 B-Tree의 리프노드와는 달리

클러스터링 인덱스의 리프노드에는 레코드의 모든 컬럼이 같이 저장되어 있다.

즉, 클러스터링 테이블은 그자체가 하나의 거대한 인덱스 구조로 관리되고 있는 것임.

update tb_test set emp_no = 100002 where emp_no = 100007;

위처럼 pk 값을 변경 한다면 아래 처럼 데이터의 저장위치가 변경된다.(pk만 봐주세요;;)

만약 pk가 없는 InnoDB 테이블이라면?

1. pk가 있을때는 pk를 클러스터링 키 선택
2. not null 옵션의 유니크 인덱스 중에서 첫번째 인덱스를 클러스터링 키로 선택
3. 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후, 클러스터링 키로 선택

3번 같은 경우는 내부적으로 생성된 일련번호가 노출되지도 않고 쿼리에 명시적으로 사용할 수도 없어서

아무 의미없는 숫자가 클러스터링 키가 되는 것이다. => 아무런 혜택을 주지 못함.

8.8.2 세컨더리 인덱스에 미치는 영향

MyISAM 이나 MEMORY 의 경우

데이터 레코드가 저장된주소는 내부적인 레코드 아이디(ROWID) 역할을 한다.

프라이머리 키나 세컨더리 인덱스나 모두 레코드 아이디를 통해서 데이터를 찾아온다.

즉 구조적으로 아무런 차이가 없다.

InnoDB의 경우

만약 세컨더리 인덱스가 실제 레코드 주소를 가지고 있다면

클러스터링 키값(pk)이 변경 될때마다 세컨더리 인덱스의 저장된 레코드 주소값을 변경해야하는 오버헤드가 발생

=> 이런 오버헤드를 제거하지 위해 주소값 대신 pk 값을 저장함.

MyISAM과 InnoDB의 검색 차이

create table employees(
  emp_no not null,
  first_name varchar(20) not null,
  primary key (emp_no),
  index ix_firstname (first_name)
);
select * from employees where first_name = 'Aamer';

• MyISAM : 인덱스를 검색해서 레코드의 주소를 확인 후 레코드 주소를 이용해 레코드를 가져옴
• InnoDB : 인덱스를 검색해 pk 값을 확인 후 pk를 검색해 레코드를 가져옴
  
  
  

8.8.3 클러스터링 인덱스의 장점과 단점

MyISAM과 비교한 장단점

구분	내용
장점	+ 프라이머리 키로 검색할 때 성능이 매우 빠름(특히 pk를 범위 검색하는 경우) + 테이블의 모든 세컨더리 인덱스가 pk를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(커버링 인덱스, 10장에서..)
단점	+ 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스커링 키값이 클경우 전체적으로 인덱스 크기가 커짐 + 세컨더리 인덱스를 통해 검색할 때 pk로 다시 한번 검색해야 해서 처리 성능이 느림 + insert할때 pk에 의해 레코드 저장위치가 결정되기 때문에 느림 + pk 변경 시 레코드를 delete하고 insert하는 작업 때문에 느림

한줄 정리 : 빠른 읽기 느린 쓰기.

8.8.4 클러스터링 테이블 사용 시 주의사항

8.8.4.1 클러스터링 인덱스키의 크기

5개의 세컨더리 인덱스를 가지는 테이블의 pk가 10byte인 경우와 50byte인 경우

pk 키 크기(byte)	레코드당 증가하는 인덱스 크기	100만 건 레코드 저장시 증가하는 인덱스 크기
10	10 * 5 = 50	50 * 1,000,000 = 47MB
50	50 * 5 = 250	250 * 1,000,000 = 238MB

데이터가 많아지면 인덱스 크기도 같이 매우 커지니 pk는 신중하게 선택하자.

8.8.4.2 pk는 auto-increment보다는 업무적인 컬럼으로 생성(가능한 경우)

InnoDB 의 pk는 클러스터링 키로 사용되고 레코드 위치가 결정됨.

=> pk로 검색하는 경우(특히 범위) 클러스터링 되지 않은 테이블에 비해 매우 빠름

또한 pk는 검생에 상당히 빈번하게 사용되는것이 일반적임.

그러므로 그 컬럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 pk로 설정하는 것이 좋다.

8.8.4.3 pk는 반드시 명시할 것

가능하면 auto-increment 컬럼을 이용해서라도 pk는 생성하는것을 권장함.

InnoDB에서 pk를 지정하지 않으면 내부적으로 일련번호를 추가하는데

사용자에게 보이지 않기 때문에 이 일련번호를 사용해서 접근할 수가 없다.

ROW기반의 복제나 InnoDB Cluster에서는 모든 테이블이 pk를 가져야만 정상적인 복제성능을 보장하기도함.

8.8.4.4 auto-increment 컬럼을 인조 식별자로 사용하는 경우

조합키로 pk를 맘ㄴ들어 pk의 크기가 길어질 때가 있음.

하지만 pk가 커져도 세컨더리 인덱스가 필요하지 않다면 그대로 사용해도 좋음.

세컨더리 인덱스도 필요하고 pk도 길다면?

auto-increment 컬럼을 추가하고 이걸 pk로 설정하면 됨.

이렇게 pk 를 대체하기 위해 인위적으로 추가된 pk를 인조식별자(Surrogate key)라고함.

그리고 insert 위주의 테이블은 auto-increment를 이용한 인조식별자를 pk로 설정하는 것이 성능 향상에 도움이 된다.