[Real MySQL] 인덱스 (2)

8.6 함수 기반 인덱스

칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축할 수 있게 한다.

인덱스 내부적인 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.

함수 기반 인덱스를 구현하는 방법

1. 가상 칼럼을 이용한 인덱스
2. 함수를 이용한 인덱스

8.6.1 가상 칼럼을 이용한 인덱스

ALTER TABLE user 
        ADD full_name VARCHAR(30) AS (CONCAT(first_bame,' ',last_name)) VURTUAL,
        ADD INDEX ix_fullname (full_name);

가상 칼럼은 VIRTUL과 STORED 옵션이 있다.

테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.

8.6.2 함수를 이용한 인덱스

MySQL8.0부터는 테이블의 구조를 변경하지 않고 함수를 직접 하용하는 인덱스를 생성할 수 있게 됐다.

CREATE TABLE user (
        user_id BIGINT,
        first_name VARCHAR(10),
        last_name VARCHAR(10),
        PRIMARY KEY (user_id),
        INDEX ix_FULLNAME((CONCAT(first_bame,' ',last_name)))
        );

조건절에 함수기반 인덱스에 명시된 표현식이 그대로 사용돼야 한다.

8.7 Multi-Value 인덱스

하나의 데이터 레코드가 여러개의 인덱스 키 값을 가진다.

정규화에 위배되지만, JSON 타입의 필드에 저장된 원소에 대한 인덱스를 만들기 위해 생겼다.

CREATE TABLE user (
        user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(10),
        last_name VARCHAR(10),
        credit_info JSON,
        INDEX mx_creditscores((CAST(credit_info->'$.credit_scores' AS UNSIGNED ARRAY)))
        );

→ 연산자: JSON 필드를 가져온다.

$: JSON 문서의 루트를 가리킨다.

credit_info->'$.credit_scores': credit_info JSON칼럼의 루트에서 credit_scores라는 키를 찾는다.

반드시 다음 함수를 이용해서 검색해야 옵티마이저가 인덱스를 활용한 실행 계획을 수립한다.

• MEMBER OF()
• JSON_CONTAINS()
• JSON_OVERLAPS()

SELECT * FROM user WHERE 360 MEMBER OF(credit_info->'$.credit_scores');

8.8 클러스터링 인덱스

테이블의 레코드를 비슷한 것(프라이머리 키를 기준으로)들까리 묶어서 저장하는 형태로 구현된다.

InnoDB에서만 사용된다.

InnoDB 스토리지 엔진이 다음 우선순위대로 클러스터링 인덱스를 선택한다.

1. 프라이머리 키
2. NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스
3. 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후 클러스터링 키로 선택

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

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

8.9 유니크 인덱스

MySQL에서는 인덱스 없이 유니크 제약만 설정할 수 없다.

유니크 인덱스에 NULL이 저장될 수 있다. NULL은 특정 값이 아니므로 2개 이상 저장될 수 있다.

세컨더리 인덱스와 구조상 차이가 없다.

쓰기시, 중복된 값을 체크하기 위해 읽기 잠금을 사용하고, 쓰기를 할 때 쓰기 잠금을 사용한다.

InnoDB 스토리지 엔진에서 인덱스 키의 저장을 버퍼링하기 위해 체인지버퍼가 사용되지만, 유니크 인덱스는 반드시 중복 체크를 해야하므로 작업을 버퍼링하지 못한다. 이 때문에 유니크 인덱스는 일반 세컨더리 인덱스보다 변경 작업이 더 느리게 작동한다.

8.10 외래키

1. 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
2. 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경함(잠금 대기)을 발생시키지 않는다.