[Real MySQL 8.0] 그 외에 다양한 인덱스

멀티 밸류 인덱스

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

JSON의 배열 타입의 필드에 저장된 원소들에 대한 인덱스를 지원하기 위해 MySQL 8.0 버전에서 업그레이드됐다.

// 멀티 밸류 인덱스 생성
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))
            )
);

// INSERT
INSERT INTO user VALUES (1, 'Matt', 'Lee1', '{"credit.scores":[360, 353, 351]}');

위와 같은 멀티 밸류 인덱스를 활용하려면 반드시 다음 함수들을 활용해서 검색해야만 옵티마이저가 실행 계획을 수립할 수 있다.

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

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

+----------+------------+----------+--------------------------------------+
| user_id  | first_name |last_name | credit_info                          |
+----------+------------+----------+--------------------------------------+
|       1  | Matt       | Lee      | {"credit_scores": [360, 353, 351]}   |
+----------+------------+----------+--------------------------------------+

 

클러스터링 인덱스

프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다. 이는 테이블의 프라이머리 키에 대해서만 적용되는 내용이며 InnoDB 스토리지 엔진에서만 지원한다.

여기서 중요한 점은 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다는 것이다. 이 말은 프라이머리 키 값이 변경되면 물리적인 저장 위치도 변경되어야 한다는 것을 나타낸다. 따라서 클러스터링 된 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크기 때문에 신중히 프라이머리 키를 결정해야 한다.

클러스터링 인덱스는 알고리즘이라기보다 테이블 레코드 저장 방식으로 볼 수 있다. 그래서 클러스터링 인덱스와 클러스터링 테이블은 동의어로 사용되기도 한다.

클러스터링 인덱스(테이블)의 구조

 

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

InnoDB 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현돼 있다. 만약 세컨더리 인덱스가 프라이머리 키 값이 아닌 실제 레코드 주소를 가지고 있었다면 클러스터링 키 값이 변경될 때마다 레코드의 주소가 변경되고, 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야 하는 오버헤드가 발생했을 것이기 때문이다.

employees 테이블에서 first_name 칼럼으로 검색하는 경우 프라이머리 키로 클러스터링 된 InnoDB 동작 예시를 살펴보자.

CREATE TABLE employees ( 
    emp_no INT NOT NULL,
    first_name VARCHAR(20) NOT NULL, 
    PRIMARY KEY (emp_no),
    INDEX ix_firstname (first_name)
);

mysql) SELECT * FROM employees WHERE first_name='Aamer';

1. ix_firstname 인덱스를 검색
2. 레코드의 프라이머리 키 값을 확인
3. 확인한 프라이머리 키 인덱스를 검색해 최종 레코드를 가져옴

InnoDB는 MyISAM처럼 실제 레코드 주소를 확인하는 것이 아니라 레코드의 프라이머리 키 값을 확인하고 그 프라이머리 키 값의 인덱스를 검색해 레코드를 가져온다.

MyISAM에 비해 조금 더 복잡하게 처리되지만, 프라이머리 키(클러스터링 인덱스)는 더 큰 장점을 제공하기 때문에 성능 저하에 대해 너무 걱정할 필요가 없다.

 

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

• 장점
  • 프라이머리 키(클러스터링 인덱스)로 검색할 때 처리 성능이 매우 빠르다.
    • 특히, 프라이머리 키를 범위 검색하는 경우 매우 빠름
  • 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많다.(커버링 인덱스)
• 단점
  • 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커진다.
  • 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느리다.
  • INSERT 할 때 프라이머리 키에 의해 레코드 위치가 결정되므로 처리 성능이 느리다.
  • 프라이머리 키를 변경할 때 레코드를 DELETE 하고 INSERT 하는 작업이 필요하기 때문에 성능이 느리다.

클러스터링 인덱스의 장점과 단점을 요약해보면 장점은 빠른 읽기(SELECT)이며 단점은 느린 쓰기(INSERT, UPDATE, DELETE)라고 볼 수 있다. 일반적인 웹 서비스는 읽기가 80% ~ 90%를 차지하므로 느린 쓰기를 감수하고 빠른 읽기를 유지하는 것이 중요하다. 따라서 클러스터링 인덱스는 합리적인 선택이라고 볼 수 있다.

 

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

• 클러스터링 인덱스 키의 크기를 신경 쓰자.
  • 모든 세컨더리 인덱스는 프라이머리 키를 가지기 때문에 프라이머리 키의 크기가 커지면 세컨더리 인덱스의 크기도 함께 커진다.
  • 프라이머리 키가 10바이트에서 50바이트로 커지면 레코드 건수가 100만 건이면 190MB, 1000만 건이면 1.9GB 증가한다.
• 프라이머리 키는 Auto-Increment보다는 업무적인 컬럼으로 생성하자.
• 클러스터링 키(프라이머리 키) 값에 의해 레코드의 위치가 결정되므로 아주 중요하다. 또한, 프라이머리 키는 검색 성능을 매우 빠르게 만들어준다. 만약 컬럼의 크기가 크더라도 업무적으로 해당 레코드를 대표한다면 그 컬럼을 프라이머리 키로 설정하자.

• 프라이머리 키를 반드시 명시하자.
• 프라이머리 키를 정의하지 않으면 InnoDB는 내부적으로 일련번호 컬럼을 추가한다. 하지만 해당 일련번호는 사용자에게 보이지 않으므로 사용할 수가 없다. Auto-Increment로 프라이머리 키를 명시한 경우와 명시하지 않은 경우는 결국 같지만 프라이머리 키를 명시한 경우에는 사용자가 사용할 수 있으므로 반드시 프라이머리 키를 명시하자.
• Auto-Increment 컬럼을 인조 식별자로 사용하는 경우
• 프라이머리 키를 대체하기 위해 인위 적으로 추가된 프라이머리 키를 인조 식별자라고 한다. 여러 개의 컬럼이 복합으로 프라이머리 키가 만들어지는 경우 키의 크기가 길어진다. 이때 세컨더리 인덱스가 필요하고 프라이머리 키가 길어지면 이는 메모리 낭비로 이어질 수 있다. 이런 경우 Auto-Increment 컬럼을 추가하고 이를 프라이머리 키로 설정하자.

 

유니크 인덱스

유니크 인덱스는 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미하는데, 이는 인덱스라기보다는 제약 조건에 가깝다고 볼 수 있다. 프라이머리 키는 Null이 허용되지 않는 유니크 인덱스와 같지만 클러스터링 키의 역할도 하므로 유니크 인덱스와는 근복적으로 다르다.

 

인덱스 읽기

많은 사람들이 유니크 인덱스와 일반 세컨더리 인덱스의 읽기 성능이 차이 난다고 생각하지만 이는 사실이 아니다. 유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 더 많은 컬럼을 읽을 뿐 인덱스 성능과는 무관하다. 만약 읽어야 할 레코드 건 수가 똑같다면 성능상 차이는 아주 미미하다.

 

인덱스 쓰기

새로운 레코드가 Insert 될 때 인덱스 쓰기 작업이 필요하다.
그런데 유니크 인덱스의 키 값을 쓸 때는 키 값에 대한 중복 체크 과정이 별도로 필요하기 때문에 일반 세컨더리 인덱스 쓰기보다 느리게 동작한다. 또한, 키의 중복 체크를 하는 과정 때문에 작업 자체를 버퍼링하지 못한다. 따라서 변경 작업이 더 느리게 작동한다.

MySQL에서는 유니크 인덱스 중복 체크 시 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이때 데드락이 빈번하게 발생하니 주의하자.

 

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

• 성능이 좋아질 것으로 생각하고 불필요한 유니크 인덱스를 생성하지 말자.
• 똑같은 컬럼에 대해 프라이머리 키와 유니크 인덱스를 동일하게 생성하지 말자.(불필요한 중복임)

 

외래 키

외래 키는 InnoDB 스토리지 엔진에서 생성할 수 있으며, 외래 키 제약이 설정되면 연관 테이블 컬럼에 인데스까지 생성된다. 이러한 InnoDB의 외래키 관리에는 중요한 두 가지 특징이 있다.

1. 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
자식 테이블의 외래 키 컬럼의 변경(INSERT, UPDATE)은 부모 테이블의 확인이 필요하고, 이 상태에서 부모 테이블의 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때까지 기다리게 된다.

 

2. 외래 키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.
자식 테이블의 외래 키가 아닌 컬럼의 변경은 외래 키로 인한 잠금 확장이 발생하지 않는다.

물리적인 외래 키의 고려 사항은 부모 테이블에 참조키가 존재하는지 체크하기 위해 테이블에 읽기 잠금을 거는 것을 말한다. 외래 키를 물리적으로 생성하려면 이러한 현상으로 인한 잠금 경합까지 고려해 모델링을 진행해야 한다. 만약 잠금이 다른 테이블로 확장되면 그만큼 동시 처리 성능이 떨어지기 때문이다.