study MySQL 인덱스 클러스터링 인덱스 멀티 밸류 인덱스 세컨더리 인덱스

클러스터링 인덱스

프라이머리 키

책너두 6기 26일차

백은빈, 이성욱의 Real MySQL8.0 1권 p.267 ~ p.276

내용정리

08 인덱스

8.6.2 함수를 이용한 인덱스

다음과 같이 테이블의 구조를 변경하지 않고, 함수를 직접 사용하는 인덱스를 생성할 수 있다.

create table user(
	user_id BIGINT,
	first_name VARCHAR(10),
	last_name VARCHAR(10),
	PRIMARY KEY (user_id),
	INDEX ix_fullname ((concat(first_name,' ',last_name)))
);

함수를 직접 사용하는 인덱스는 테이블의 구조는 변경하지 않고, 검색을 빠르게 만들어준다. 제대로 활용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용돼야 한다.

8.7 멀티 밸류 인덱스

전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키 값을 가진다. 즉, 인덱스 키와 데이터 레코드는 1:1의 관계를 가진다. 하지만 멀티 밸류(Multi-Value) 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스다.

이를 활용하기 위해서는 일반적인 조건 방식을 사용하면 안 되고, 반드시 다음 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행 계획을 수립한다.

8.8 클러스터링 인덱스

클러스터링 : 여러 개를 하나루 묶는다는 의미.

MySQL에서는 테이블의 레코드를 비슷한 것(프라이머리 키를 기준으로)들끼리 묶어서 저장하는 형태. innoDB 스토리지 엔전에서만 지원된다.

8.8.1 클러스터링 인덱스

테이블의 프라이머리 키에 대해서만 적용되는 내용. 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다. 프라이머리 키 값에 의해 레코드의 저장 위치가 결정되며, 이는 프라이머리 키 값이 변경되면 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미하기도 한다.

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

InnDB 테이블(클러스터링 테이블)의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현돼 있다.

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

일반 프라이머리 키와 클러스터링 인덱스를 비교했을 때

장점

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

단점

  • 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 가져서 클러스터링 키 값의 크기가 크면 전체적인 인덱스의 크기가 커짐
  • 세컨더리 인덱스를 통해 검색하면 프라이머리 키로 다시 한번 검색해야 해서 성능이 느림
  • INSERT할 때 프라이머리 키에 의해 저장 위치가 결정되서 성능이 느림
  • 프라이머리 키를 변경할 때 레코드를 DELETE 하고 INSERT해야 해서 성능이 느림

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

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

프라이머리 키가 커지면 전체 인덱스 크기가 기하급수적으로 커진다.

8.8.4.2 프라이머리 키는 AUTO-INNOCENT보다는 업무적인 칼럼으로 생성

프라이머리 키는 클러스터링 키로 사용되며 이 값으로 레코드의 위치가 결정된다. 중요한 만큼 중요한 역할을 해서 대부분 검색에서 상당히 빈번하게 사용되므로 업무적으로 칼럼의 크기가 크더라도 대표성이 있으면 사용하자

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

없다면 AUTO_INCREMENT 칼럼을 이용해서라도 프라이머리 키는 생성할 것을 권장한다. 그냥 웬만하면 프라이머리 키는 꼭 생성하자.

8.8.4.4 AUTO-INCREMENT 칼럼을 인조 식별자로 사용할 경우

여러 개의 칼럼이 복합으로 프라이머리 키가 만들어지는 경우 프라이머리 키의 크기가 길어질 떄가 있지만 세컨더리 인덱스가 필요치 않다면 그대로 사용하는 것이 좋다.

CATALOG
    MySQL