[Real MySQL 8.0]8장 - 다양한 종류의 인덱스 (2/2)

📝8.4 R-Tree 인덱스

아마도 MySQL의 공간 인덱스라는 말을 한 번쯤은 들어본 적이 있을 것이다. 공간 인덱스는 R-Tree 인덱스 알고리즘을 이용한 2차원 데이터를 인덱싱하고 검색하는 목적의 인덱스이다. 기본 내부 메커니즘은 B-Tree와 흡사하다. 그러나 R-Tree는 칼럼의 값이 2차원의 공간 개념 값이다. 

 

그렇다면 왜 쓰는 것일까?

최근 GPS나 지도 서비스를 내장하는 스마트 폰이 대중화되면서 GIS와 GPS 기반의 서비스가 많아졌다. 이러한 위치 기반의 서비스를 구현하는 방법은 여러 가지가 있겠지만 MySQL의 공간 확장을 이용하면 간단하게 구현 가능하다. 

 

아래는 공간확장의 기능이다.

• 공간 데이터를 저장하는 데이터 타입 지원
• 공간 데이터 검색을 위한 공간 인덱스
• 공간 데이터의 연산 함수

 

📝8.5 전문 검색 인덱스

지금가지 살펴본 인덱스 알고리즘은 일반적으로 크지 않은 데이터 또는 이미 키워드화한 작은 값에 대한 인덱싱 알고리즘이다.

 

문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문 검색에는 일반 B-Tree를 사용할 수 없다.

그렇기에 문서 전체를 분석, 검색을 위한 인덱싱 알고리즘을 전문 검색 인덱스라 한다.

 

해당 인덱스는 2가지 중요한 과정을 거쳐서 검색한다.

• 불용어 처리(필요없는 단어 필터링)
• 어근 분석(검색어로 선정된 단어의 원형을 찾는 작업)

 

사실 이러한 전문 검색 인덱스는 MySQL를 쓸 이유가 없다 Elasticsearch를 쓰자... 

 

📝8.6 함수 기반 인덱스

일반적인 인덱스는 칼럼의 값 일부 또는 전체에 대해서만 인덱스 생성을 허용한다.

하지만 때로는 칼럼이 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야한다. 이러한 경우 함수 기반 인덱스를 사용하면 된다.

 

구현 방법은 다음과 같다.

• 가상 칼럼을 이용한 인덱스
• 함수를 이용한 인덱스

📌가상 칼럼 

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    full_name VARCHAR(100) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
);

CREATE INDEX idx_full_name ON employees (full_name);

📌함수

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE INDEX idx_first_name_upper ON employees (UPPER(SUBSTRING(first_name, 1, 1)));

 

📝8.7 멀티 밸류 인덱스

전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키 값을 가진다. 즉, 인덱스 키와 데이터가 1대 1 관계이다. 

하지만 멀티 밸류 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태이다.

일반적인 RDBMS에서 정규화에 위배되는 형태이지만 최근들어 JSON 타입도 지원하면서 필요성이 대두되었다.

 

 

📝8.8 클러스터링 인덱스

MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(PK 기준)들 끼리 묶어서 저장하는 것을 말한다.

그러므로 PK 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야하고, PK 기반 검색이 매우 빠르지만, 레코드 저장 및 PK의 변경이 느리다.(하지만 MySQL에서 PK가 변경되는 일은 매우 적다)

 

만약 PK가 없다면 아래와 같은 로직으로 대체 컬럼을 선택한다.

1. NOT NULL 옵션의 유니크 인덱스 중 첫 번째 인덱스를 클러스터링 키로 선택한다.
2. 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후 클러스터링 키로 선택한다.

 

클러스터링 인덱스는 세컨더리 인덱스와 다르게 리프 노드의 레코드의 모든 칼럼이 같이 저장돼 있다. 즉, 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리되는 것이다.

 

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

InnoDB의 세컨더리 인덱스는 클러스터링 인덱스에 영향을 받아 모든 리프노드는 해당 레코드의 저장 주소가 아니라 프라이머리 키 값을 저장한다.

그리고 조회를 하면 세컨더리 인덱스의 조회값을 클러스터링 인덱스에 한번 더 조회하는 방식으로 구현된다. 

 

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

장점	PK로 검색시 매우 빠름 테이블의 모든 세컨더리 인덱스가 PK를 가져 인덱스만으로 처리될 수 없는 경우가 발생(커버링 인덱스)
단점	모든 세컨더리 인덱스가 클러스터링 키 값을 가지기에 키 값이 클 경우 인덱스 크기 비례 증가 세컨더리 인덱스로 조회할 경우 클러스터링 인덱스를 한번 더 거치기에 처리 성능이 느리다. INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기에 처리 성능이 느림 프라이머리 키 변경이 느림

이것을 알 수 있는 것은 클러스터링 인덱스는 일긱가 빠르며 쓰기가 느리다는 것이다.

 

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

• 인덱스 키의 크기를 줄여라
• 프라이머리 키는 AUTO_INCREMENT보다 업무적인 칼럼이 좋다(가능한 경우)
• 프라이머리 키는 반드시 명시할 것

 

📝8.9 유니크 인덱스

유니크는 사실 인덱스라기보다는 제약 조건에 가깝다고 볼 수 있다. 말 그대로 테이블이나 인덱스에 같은 값이 2개 이상이 될 수 없다는 것이다. NULL은 허용되며 NULL은 2개 이상 가능하다.

 

유니크 인덱스의 특징은 쓰기 작업 중에 중복 값이 있는지 체크하는 과정이 필요하다.

그래서 유니크 인덱스는 세컨더리 인덱스보다 쓰기가 느리며 데드락도 많이 발생한다.

 

 

📝8.10 외래키

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

 

InnoDB 의 외래키 관리에는 중요한 두 가지 특징이 있다.

• 테이블 변경이 발생하는 경우 잠금 경합이 발생한다.
• 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.

자식 테이블이 변경될 때 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려있다면 잠금 대기를 발생시킨다. 

 

자식 테이블을 변경하기 위해 레코드 잠금이 걸린 상태에서 부모 테이블에서 레코드를 삭제시킨다면 연관된 자식 레코드도 삭제되어야한다. 이때에도 자식 테이블  레코드가 잠금이 걸려있기에 해제될 때까지 기다려야한다.