[MYSQL] Real MySQL 8장 - 인덱스

클러스터링 인덱스

- 클러스터링 : 클러스터링이란 여러개를 하나로 묶는다는 의미로 주로 사용됨 , 클러스터링 인덱스도 비슷하다

MySQL에서 클러스터링 인덱스는 InnoDV스토리지 엔진에서만 지원하며, 나머지 스토리지 엔진에서는 지원되지 않는다

 

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

- 즉, 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 ㅍ ㅛ현한다.

- 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다

- 프라이머리 키 값으로 클러스터링 된 테이블은, 프라이머리 키 값 자체에 대한 의존도가 상당히 크기 때문에 신중히 프라이머리 키를 결정해야 한다.

- 프라이 머리 키 값에 의해 레코드의 저장위치가 결정되므로 클러스터링 인덱스와 클러스터링 테이블은 동의어로 사용되기도 한다

- InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르다, 대신 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다.

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

- InnoDB에서 pk를 설정하지 않으면 결국 의미없는 숫자값으로 클러스터링 되는 것 이기 때문에 InnoDB 테이블에서 클러스터링 인덱스는 테이블당 단 하나만 가질 수 있는 엄청난 혜택이므로 가능하다면 프라이머리 키를 명시적으로 생성하자.

 

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

- MyISAM이나 MEMORY 테이블 같은 클러스터링 되지 않은 테이블은 INSERT될 떄 청므 저장된 공간에서 절대 이동하지 않는다.

 - 데이터 레코드가 저장된 주소는 내부적인 레코드 아이디(ROWID)를 이용해서 실제 데이터 레코드를 찾아온다

- 그래서 MyISM테이블이나 MEMORY테이블에서는 프라이머리키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다.

- 근디,, InnoDB에서는 세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있다면, 클러스터링 키 값이 변경될 때 마다 데이터 레코드의 주소가 변경되고 그때마다 해당 테이블의 모든 인덱스에 저장된  주솟값을 변경해야한다.

- 이런 오버헤드를 제거하기 위해 Inno DB 테이블의 모든 세컨더리 인덱슨느 ㅎ ㅐ당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현돼있다.

 

예시) ix_first라는 이름의 인덱스를 테이블을 하나 만들면서 생성 시

1. MyISAM : ix_first 인덱스를 검색해서 레코드의 주소를 확인한 후,해당 레코드의 주소를 이용해 최종레코드를 가져옴

2. InnoDB : ix_first 인덱스를 검색해서 프라이머리 키 값을 확인후,프라이머리 키 인덱스를 검색해서최종레코드를가져옴

 

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

- 장점 : 프라이머리키(클러스터링키)로 검색할 때 처리 성능이 매우 빠름 / 테이블의 모든 세컨더리 인덱스가 프라이머리 키를  가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음

- 단점 : 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐 / 세컨더리 인ㄷㄱ스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림

 

=> 클러스터링 인덱스의 장점은 빠른 읽기, 단점은 느린 쓰기

=> 조금 느린 쓰기를 감수하고 읽기를 빠르게 유지하는 것은 중요하다

 

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

1) 클러스터링 인덱스 키의 크기

- 클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키 값을 포함한다.

- 그래서 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다.

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

- InnoDB의 프라이머리 키는 클러스터링 키로 사용되며, 이 값에 의해 레코드의 위치가 결정된다.

- 즉, 프라이머리 키로 검색하는 경우 클러스터링되지 않은 테이블에 비해 매우 빠르게 처리될 수 있다.

- 그만큼 중요한 역할을 하기 때문에, 그 칼럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 프라이머리 키로 설정하는 것이 좋다

3) 프라이머리 키는  반드시 명시할 것

- 프라이머리 키는 AUTO_INCREMENT를 이용해서라도 생성할 것을 권장하는 바이다

- PK 키를 정의하지 않으면 InnoDB 스토리지 엔진이 내부적으로 일련번호 칼럼을 추가하는데, 자동으로 추가된 칼럼은 사용자에게 보이지 않기 때문에 사용자가 접근할 수 가 없다

4) AUTO_INCREMENT 칼럼을 인조 식별자로 사용할 경우

- 로그 테이블과 같이 조회보다는 INSERT 위주의 테이블들은 AUTO_INCREMENT를 이용한 인조식별자를 프라이머리키로 설정하는 것이 성능 향상에 도움이 된다.

 

유니크 인덱스

- 유니크는 사실 인덱스라기 보다는 제약조건에 가깝다고 볼 수 있는데, 말 그대로 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미한다, 근데 MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.

- * 유니크 인덱스에서 NULL도 저장될 수 있는데, NULL은 특정값이 아니므로 2개이상 저장될 수 있다.

 

1) 유니크 인덱스와 일반 세컨더리 인덱스의 비교

- 유니크 인덱스와 유니크하지 않은 일반 세컨더리 인덱스는 사실 인덱스의 구조상 아무런 차이점이 없다.

 

1-1) 인덱스 읽기

- 유니크 인덱스가 빠르다고 생각하는데, 사실 그렇지 않다

- 유니크 하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야할 레코드가 많아서 느린것이지, 인덱스 자체의 특성 때문에 느린 것이 아니다.

 

1-2) 인덱스 쓰기

- 새로운 레코드가 INSERT되거나 인덱스 칼럼의 값이 변경되는 경우에는 인덱스 쓰기 작업이 필요하다.

- 그런데 유니크 인덱스의 키 값을 쓸때는 중복된 값이 있는지 없는지 체크하는 과정이 한단계 더 필요하다.

- 그래서 유니크 하지 않은 세컨더리 인덱스의 쓰기보다 쓰기가 느리다.

- 인덱스의 저장이나 변경작업이 InnoDB의 인덱스 키의 저장을 버퍼링하기 위해 쓰는 체인지 버퍼로인해 빨리 처리되지만,

유니크 인덱스는 반드시 중복체크를 해야하므로 작업 자체를 버퍼링하지 못한다.

- 이 때문에 유니크 인덱스는 일반 세컨더리 인덱스보다 변경작업이 더 느리게 작동한다.

 

2) 유니크 인덱스 사용 시 주의사항

- 성능이  좋아질 것으로 생각하고 불필요하게 유니크 인덱스를 생성하는 것은 지양해라.

- PK키와 유니크 인덱스를 같은 칼럼에 대해 생성하는 경우도 있는데, 이 또한 불필요한 중복이므로 주의해라.

- 결론! 유일성이 꼭 보장돼야하는 칼럼에 대해서는 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크 인덱스보다는 유니크 하지 않은 세컨더리 인덱스를 생성하는 방법도 고려해보자.

 

외래키

- 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성된다.

- 외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없다.

 

=> 테이블의 변경이 발생하는 경우에만 잠금 경합이 발생한다

=> 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.