Chapter 08.08 클러스터링 인덱스(Clustering Index)

8.8 클러스터링 인덱스(Clustering Index)

• 클러스터링: 여러 개를 하나로 묶는다는 의미로 사용한다. 
• Def) 클러스터링: MySQL에서 클러스터링은 테이블의 레코드를 비슷한 것들끼리 묶어서 저장하는 형태로 구현된다. 
  • 클러스터링 인덱스는 InnoDB 엔진에서만 지원한다. 

 

8.8.1 클러스터링 인덱스(Clustering Index)

• Def)  클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용된다. PK가 비슷한 레코들끼리 묶어서 저장하는 것을 말한다. 
• PK 값에 의해서 레코드의 저장 위치가 결정된다. 
  • PK값이 변경된다면 레코드의 저장위치가 바뀌어야한다는 뜻이다. 
  • PK 값으로 클러스터링된 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크기 때문에 PK를 신중하게 결정한다. 
• 클러스터링 인덱스는 인덱스 알고리즘 보다는 테이블 레코드 저장 방식이라고 볼 수 있다. 
• 클러스터링 인덱스와 클러스터링 테이블은 동의어로 사용되기도 한다. 
• 기준이 되는 PK를 "클러스터링 키"라고 표현한다. 
• InnoDB 처럼 항상 클러스터링 인덱스로 저장되는 테이블은 PK 기반의 검색이 매우 빠르고 대신 레코드 저장이나 PK의 변경이 상대적으로 느리다. 

• 위의 사진을 보면 구조는 B-Tree와 테이블 구조가 비슷한다.
•  B-Tree의 리프 노드와는 달리 클러스터링 인덱스의 리프 노드에는 레코드의 모든 칼럼이 같이 저장돼 있다. 
  • 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리되는 것이다. 

 

 

 

  Ex) 클러스터링 테이블에서 다음 쿼리와 같이 프라이머리 키를 변경하는 문장이 실행되면 레코드에 어떤 변화가 일어날까?

UPDATE tb_test SET emp_no=100002 
WHERE emp_no=100007;

 

• 그림 8.25에서는 emp_no가 100007인 레코드는 3번 페이지에 저장돼 있다. 
• 그런데 8.26을 보면 emp_no가 10002로 변경되면서 2번 페이지로 이동한 걸 볼 수 있다. 
  • 실제로는 변경되는 경우가 거의 없다. 

 

  Ex) PK가 없는 InnoDB 테이블은 어떻게 클러스터링 테이블로 구성될까?

• InnoDB 스토리지 엔진이 다음 우선순위대로 PK 대체 칼럼을 선택한다. 
  1. PK가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택한다. 
  2. NOT NULL 옵션의 유니크 인덱스(UNIQUE INDEX)중에서 첫 번째 인덱스를 클러스터링 키로 선택한다. 
  3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택한다.   ????

 

 

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

• PK가 세컨더리 인덱스에 미치는 영향은 무엇이 있을까?
• 클러스터링 되지 않은 MyISAM, MEMORY 테이블은 처음 저장된 공간에서 절대로 이동하지 않는다. 
•  레코드가 저장된 주소는 내부적인 레코드 아이디(ROWID) 역할을 한다. 
• PK, 세턴더리 인덱스는 ROWID를 이용해서 실제 데이터를 찾아온다. 
  • 따라서, MyISAM 테이블이나 MEMORY 테이블에서는 PK, 세컨더리 인덱스는 구조적으로 차이가 없다. 
• InnoDB에서 세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있는 경우는?
  • 클러스터링 키 값이 변경될 때마다 레코드의 주소가 변경되고 그 때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야한다.
  • 이런 오버헤드(overhead, 어떤 처리를 하기 위해 들어가는 간접적인 처리 시간, 메모리 등을 말한다.)를 제거하기 위해 InnoDB 테이블의 모든 세컨더리 인덱스는 레코드 주소가 아니라 PK값을 저장하도록 구현돼있다. 

 

 

  Ex) first_name 칼럼으로 검색하는 경우, PK로 클러스터링된 InnoDB와  MyISAM의 차이점

CREATE TABLE employees (
	emp_no INT NOT NULL, 
    first_name VARCHAR20() NOT NULL, 
	PRIMARY KEY (emp_no),
    INDEX ix_firstname (first_name)
);
SELECT *
FROM employees 
WHERE first_name = 'Aamer';

• MyISAM: ix_firstname 인덱스를 검색해서 레코드의 주소를 확인한 후, 레코드 주소를 이용해서 최종 레코드를 가져온다. 
• InnoDB: ix_firstname 인덱스를 검색해서 레코드의 PK 값을 확인한 후, PK 인덱스를 검색해서 최종 레코드를 가져온다. 
  • InnoDB가 더 복잡한 편이다. 

 

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

• 장점
  • PK(클러스터링 키)로 검색할 때 매우 빠르다. 
  • 테이블의 모든 세컨더리 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많다. (커버링 인덱스)
• 단점
  • 케이블의 모든 세컨더리인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우, 전체적으로 인덱스 크기가 커진다. 
  • 세컨더리 인덱슬르 통해 검색할 때 PK 로 다시 한번 검색하므로 성능이 느리다. 
  • INSERT 할 때 PK 에 의해 레코드의 저장 위치가 결정되므로 성능이 느리다. 
  • PK를 변경할 때 레코드를 DELETE 하고 INSERT 하는 작업이 필요하기 때문에 느리다. 
  • 느린 쓰기(DELETE, INSERT, UPDATE)
• 웹 서비스 같은 온라인 트랜잭션 환경(OLPT, OnLine Transaction Processing)에서는 쓰기와 읽기의 비율이 2:8, 1:9이기 때문에 느린 쓰기를 감수하고 빠른 읽기를 유지하는 게 중요하다.

 

 

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

 

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

• 클러스터링 테이블의 경우 모든 세컨더리 인덱스가 PK 값을 포함한다. 
• 그래서 PK가 커지면 세컨더리 인덱스도 자동으로 커진다. 
• 일반적으로 테이블에 세컨더리 인덱스가 4 ~ 5개 정도 생성된다는 것을 고려하면 세컨더리 인덱스 크기는 급격히 증가한다. 
•  

 

 

8.8.4.2  프라이머리 키는 AUTO-INCREMENT 보다는 업무적인 칼럼으로 생성 가능한 경우

 

 

 

 

 

 

 

 

• 오토인크리먼트를 그냥 인티저로 잡으면 

 

 

• 날짜와 시간 가지고 만들면 안 겹치나??
•  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

출처 - Real MySQL 8.0