[MySQL] 함수 인덱스, 클러스터링 인덱스

기존의 Real MySQL (5.0, 5.1 버전) 책을 너무 유익하게 봤는데, Real MySQL 8.0이 전면 개정판이 나와서... 설레는 마음에 보면서 개인적인 공부용으로 정리하고 있습니다.
역시 믿고 보는 Real MySQL... 👍 한번 사서 보시는 것을 강력 추천드립니다!

함수 기반 인덱스

---

일반적인 인덱스는 칼럼의 값 일부 또는 전체에 대해서만 인덱스 생성이 허용된다. 하지만 때로는 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때도 있는데, 이러한 경우 함수 기반의 인덱스를 활용하면 된다.

MySQL 서버는 8.0 버전부터 함수 기반 인덱스를 지원하기 시작했는데, MySQL 서버에서 함수 기반 인덱스를 구현하는 방법은 다음 두 가지로 구분할 수 있다.

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

MySQL 서버의 함수 기반의 인덱스는 인덱싱할 값을 계산하는 과정의 차이만 있을 뿐, 실제 인덱스의 내부적인 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.

first_name, last_name 칼럼이 있는데, 두 칼럼을 합쳐서 검색해야 하는 요건인 경우

가상 칼럼을 이용한 인덱스

• 이전 버전의 MySQL 서버에서는 full_name이라는 칼럼을 추가하고 모든 레코드에 대해 full_name을 업데이트하는 작업을 거쳐야만 full_name 칼럼에 대해 인덱스를 생성할 수 있었다.
• 하지만 MySQL 8.0 버전 부터는 가상 칼럼을 추가하고 그 가상 칼럼에 인덱스를 생성할 수 있게 됐다.

ALTER TABLE user
    ADD full_name VARCHAR(30) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
    ADD INDEX idx_fullname(full_name);

가상 칼럼이 VIRTUAL이나 STORED 옵션 중 어떤 옵션으로 생성됐든 관계없이 해당 가상 칼럼에 인덱스를 생성할 수 있다.

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

함수를 이용한 인덱스

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

CREATE TABLE (
    ...
    first_name VARCHAR(10),
    LAST_NAME VARCHAR(10),
    ...
    INDEX idx_fullname((CONCAT(first_name, ' ', last_name)))
);

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

SELECT * FROM user WHERE CONCAT(first_name, ' ', last_name)='Kang seungho';

클러스터링 인덱스

---

InnoDB 스토리지 엔진에서만 지원하며, 클러스터링 인덱스란 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것이다.

• 즉, 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다는 것이다.
• 이러한 이유로 프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 변경되어야 한다.

특징

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

클러스터링 인덱스 구조

• 세컨더리 인덱스를 위한 B-Tree의 리프 노드와는 달리, 클러스터링 인덱스의 리프 노드에는 레코드의 모든 칼럼이 같이 저장돼 있다.

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

• MyISAM 테이블이나 MEMORY 테이블에서는 프라이머리키와 세컨더리 인덱스는 구조적으로 아무런 차이가 없다.
• 하지만 InnoDB 테이블에서는 클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경되고 그때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야 한다.
• 이런 오버헤드를 제거하기 위해 InnoDB 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현돼 있다.

즉 InnoDB 테이블에서 세컨더리를 이용해서 검색하면, 세컨더리 인덱스를 통해 레코드의 프라이머리 키 값을 확인한 후, 프라이머리 키 인덱스를 검색해서 최종 레코드를 가져온다.

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

장점

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

단점

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

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

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

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

• 따라서 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다. (일반적으로 테이블에 세컨더리 인덱스가 4~5개 정도 생성된다는 것을 고려하면 세컨더리 인덱스 크기는 급격히 증가한다)

가능한 경우 프라이머리 키는 AUTO_INCREMENT보다는 업무적인 칼럼으로 생성

칼럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 프라이머리 키로 설정하는 것이 좋다.

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

InnoDB 테이블에서 프라이머리 키를 정의하지 않으면 InnoDB 스토리지 엔진이 내부적으로 일련번호 칼럼을 추가한다.

• 하지만 자동으로 추가된 칼럼은 사용자에게 보이지 않기 때문에 사용자가 접근 사용할 수 없다.

AUTO_INCREMENT 칼럼을 이용해서라도 프라이머리 키는 생성하는 것을 권장한다.

AUTO_INCREMENT 칼럼을 인조 식별자로 사용하는 경우

여러 개의 칼럼이 복합으로 프라이머리 키가 만들어지는 경우 프라이머리 키가 길어질 때가 가끔 있다.

하지만 프라이머리 키의 크기가 길어도 세컨더리 인덱스가 필요치 않다면 그대로 프라이머리 키를 사용하는 것이 좋다.

• 세컨더리 인덱스도 필요하고 프라이머리 키의 크기도 길다면 AUTO_INCREMENT 칼럼을 추가하고, 이를 프라이머리 키로 설정하면 된다.
• 이렇게 프라이머리 키를 대체하기 위해 인위적으로 추가된 프라이머리 키를 인조 식별자라고 한다.
• 로그 테이블과 같이 조회보다는 INSERT 위주의 테이블들은 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 된다.

유니크 인덱스

---

테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없는 제약 조건.

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

유니크 인덱스 vs 일반 세컨더리 인덱스

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

인덱스 읽기

• 레코드의 수에 차이만 있을 뿐, 읽어야 할 레코드 건수가 같다면 성능상의 차이는 미미하다.

인덱스 쓰기

• 새로운 레코드가 INSERT되거나 인덱스 칼럼의 값이 변경되는 경우에는 인덱스 쓰기 작업이 필요하다.
• 유니크 인덱스의 키 값을 쓸때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다.
• 그래서 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느리다.
  • MySQL에서는 유니크 인덱스에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번히 발생한다.
  • InnoDB 스토리지 엔진에는 인덱스 키의 저장을 버퍼링 하기 위해 체인지 버퍼가 사용돼서 인덱스의 저장이나 변경 작업이 상당히 빨리 처리되지만, 유니크 인덱스는 반드시 중복 체크를 해야 하므로 작업 자체를 버퍼링 하지 못한다.

유니크 인덱스 주의사항

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

외래키

---

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

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

외래 키 특징

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