[MySQL] 클러스터링 인덱스

클러스터란 여러 개를 하나로 묶는다는 의미로 주로 사용됩니다. 인덱스에서 클러스터링은 값이 비슷한 것들을 묶어서 저장하는 형태로 구현되는데, 이는 주로 비슷한 값들을 동시에 조회하는 경우가 많다는 점에 착안한 것입니다. MySQL에서 클러스터링 인덱스는 InnoDB와 TokuDB 스토리지 엔진에서만 지원하며, 나머지 스토리지 엔진에서는 지원되지 않습니다.

클러스터링 인덱스

---

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용입니다. 즉 프라이머리 키값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현합니다. 중요한 것은 프라이머리 키값에 의해 레코드의 저장 위치가 결정된다는 것입니다. 또한 프라이머리 키값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미하기도 합니다. 프라이머리 키값으로 클러스터링된 테이블은 프라이머리 키값 자체에 대한 의존도가 상당히 크기 때문에 신중히 프라이머리 키를 결정해야 합니다.

클러스터링 인덱스는 프라이머리 키값에 의해 레코드의 저장 위치가 결정되므로 사실 인덱스 알고리즘이라기보다 테이블 레코드의 저장 방식이라고 볼 수도 있습니다. 그래서 "클러스터링 인덱스"와 "클러스터 테이블"은 동의어로 사용되기도 합니다. 또한 클러스터링의 기준이 되는 프라이머리 키는 클러스터 키라고도 표현합니다. 일반적으로 InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르며, 대신 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느릴 수 밖에 없습니다.

일반적으로 B-Tree 인덱스도 인덱스 키값으로 이미 정렬되어 저장됩니다. 이 또한 어떻게 보면 인덱스의 킥랎으로 클러스터링 된 것으로 생각할 수 있습니다. 하지만 이런 일반적인 B-Tree 인덱스를 클러스터링 인덱스라고 부르지 않습니다. 테이블의 레코드가 프라이머리 키값으로 정렬되어 저장된 경우만을 "클러스터링 인덱스" 또는 "클러스터링 테이블"이라고 합니다.

클러스터링 인덱스 구조를 보면 클러스터링 테이블의 구조 자체는 일반 B-Tree와 많이 비슷하게 닮아 있습니다. 하지만 B-Tree의 리프 노드와는 달리 클러스터링 인덱스의 리프 노드에는 레코드의 모든 컬럼이 같이 저장되어 있습니다. 즉 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리되는 것입니다.

클러스터 테이블에서 다음 쿼리와 같이 프라이머리 키를 변경하는 문장이 실행되면 클러스터 테이블의 데이터 레코드에는 어떤 변화가 일어날까요?

UPDATE tb_test SET emp_no=10002 WHERE emp_no=10007;

emp_no가 10007인 레코드가 3번 페이지에 저장돼 있다고 가정하면, emp_no가 10002로 변경되면서 2번 페이지로 이동할 수도 있습니다. 실제로 프라이머리 키의 값이 변경되는 경우는 거의 없을 것입니다.

MyISAM 테이블이나 기타 InnoDB를 제외한 테이블의 데이터 레코드는 프라이머리 키나 인덱스 키값이 변경된다고 해서 실제 데이터 레코드의 위치가 변경되지는 않습니다. 데이터 레코드가 INSERT될 때 데이터 파일의 끝에(또는 임의의 빈 공간)에 저장됩니다. 이렇게 한번 결정된 위치는 절대 바뀌지 않고, 레코드가 저장된 주소는 mYsql, 내부적으로 레코드를 식별하는 아이디로 인식됩니다. 레코드가 저장된 주소를 ROW-ID라고 표현하며, 일부 DBMS에서는 이 값을 사용자가 직접 조회하거나 쿼리의 조건으로 사용할 수 있습니다. 하지만 MySQL에서는 사용자에게 노출되지는 않습니다.

프라이머리 키가 없는 경우에는 InnoDB 스토리지 엔진이 다음의 우선순위대로 프라이머리 키를 대체할 칼럼을 선택합니다.

1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터 키로 선택

2. NOT NULL 옵션의 유니크 인덱스(UNIQUE INDEX) 중에서 첫번째 인덱스를 클러스터 키로 선택

3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터 키로 선택

InnoDB 스토리지 엔진이 적절한 클러스터 키 후보를 찾지 못해서 내부적으로 자동 증가 칼럼을 추가한 경우, 자동 추가된 칼럼은 사용자에게 노출되지 않으며, 쿼리 문장에 명시적으로 사용할 수 없습니다. 즉, 프라이머리 키나 유니크 인덱스가 전혀 없는 InnoDB 테이블에서는 아무런 의미없는 숫자 값으로 클러스터링이 되고 있는 것이며, 이것은 우리에게 아무런 혜택을 주지 않습니다. InnoDB 테이블에서 프라이머리 키는 클러스터링 인덱스의 기준이 됩니다. 클러스터링 인덱스는 (InnoDB 테이블별로) 단 한번만 가질 수 있는 엄청난 혜택이므로 가능하다면 프라이머리 키를 명시하는 것이 좋습니다.

보조 인덱스(Secondary index)에 미치는 영향

---

MyISAM이나 MEMORY 테이블과 같은 클러스터링되지 않은 테이블은 INSERT될 때 한번 저장된 공간에서 절대 이동하지 않습니다. 데이터 레코드가 저장된 주소는 내부적인 레코드 아이디(ROWID) 역할을 합니다. 그리고 프라이머리 키나 보조 인덱스의 각 키는 그 주소(ROWID)를 이용해 실제 데이터 레코드를 찾아옵니다. 그래서 MyISAM 테이블이나 MEMORY 테이블에서는 파라이머리 키와 보조인덱스는 구조적으로 아무런 차이가 없습니다. 그렇다면 InnoDB 테이블에서 보조 인덱스가 실제 레코드가 저장된 주소를 가지고 있다면 어떻게 될까요? 클러스터 키값이 변경될 때마다 데이터 레코드의 주소가 변경되고 그때마다 해당 테이블의 모든 인덱스에 저장된 주소 값을 변경해야 할 것입니다. 이런 번거로움을 방지하고자 InnoDB 테이블(클러스터 테이블)의 모든 보조 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키값을 저장하도록 구현돼 있습니다.

Employees 테이블에서 first_name 칼럼으로 검색하는 경우 프라이머리 키로 클러스터링된 InnoDB와 그렇지 않은 MyISAM에서 어떤 차이가 있는지 한번 살펴 보겠습니다.

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

SELECT * FROM employees WHERE first_name = 'Aamer';

MyISAM

ix_firstname 인덱스를 검색해서 레코드의 주소를 확인

레코드의 주소를 이용해 최종 레코드를 가져 옴

InnoDB

ix_firstname 인덱스를 검색해 레코드의 프라이머리 키값을 확인

프라이머리 키값을 이용해 다시 한번 테이블을 검색한 후 최종 레코드를 가져옴

InnoDB가 MyISAM보다 조금 더 복잡하게 처리된다는 것을 알 수 있습니다. 하지만 InnoDB 테이블에서 프라이머리 키로 레코드를 읽어 오는 과정은 매우 빠르게 처리되므로 성능을 걱정할 필요는 없습니다.

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

---

MyISAM과 같은 일반 클러스터 되지 않은 일반 프라이머리 키와 클러스터 인덱스를 비교했을 때의 상대적인 장단점을 정리하면 다음과 같습니다.

장점

프라이머리 키(클러스터 키)로 검색할 때 처리 성능이 매우 빠름(특히, 프라이머리 키를 범위 검색하는 경우 매우 빠름)

테이블의 모든 보조 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(이를 커버링 인덱스라고 함)

단점

테이블의 모든 보조 인덱스가 클러스터 키를 갖기 때문에 클러스터 키값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐

보조 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 조금 느림

INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림

프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느림

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

---

MyISAM과 같이 클러스터링되지 않은 테이블에 비해 InnoDB(클러스터 테이블)에서는 조금 더 주의해야 할 사항이 있습니다.

클러스터 인덱스 키의 크기

클러스터 테이블의 경우, 모든 보조 인덱스가 프라이머리 키(클러스터 키) 값을 포함합니다. 그래서 프라이머리 키의 크기가 커지면 보조 인덱스도 자동으로 크기가 커집니다. 하지만 일반적으로 테이블에 보조 인덱스가 4~5개 정도로 생성된다는 것을 고려하면 보조 인덱스 크기는 급격히 증가합니다. 5개의 보조 인덱스를 가지는 테이블의 프라이머리 키가 10바이트인 경우와 50바인트인 경우를 한번 비교해 보겠습니다.

프라이머리 키 크기	레코드 하나당  증가하는 인덱스 크기	100만 건의 레코드를 저장했을 때  증가하는 인덱스 크기
10 바이트	10 바이트 * 5 = 50 바이트	50 바이트 * 1,000,000 = 47 MB
30 바이트	50 바이트 * 5 = 250 바이트	250 바이트 * 1,000,000 = 238 MB

레코드 한 건 한 건을 생각하면 50바이트쯤이야 대수롭지 않지만 레코드 건수가 100만 건만 돼도 인덱스의 크기가 거의 190MB(238MB - 47MB)나 증가했습니다. 1,000만 건이 되면 1.9GB가 증가하게 됩니다. 또한 인덱스가 커질수록 같은 성능을 내기 위해 그만큼의 메모리가 더 필요해진다는 뜻이므로 InnoDB 테이블의 프라이머리 키는 신중하게 선택해야 합니다.

프라이머리 키는 AUTO-INCREMENT보다는 업무적인 컬럼으로 생성할 것(가능한 경우)

InnoDB의 프라이머리 키는 클러스터 키로 사용되며, 이 값에 의해 레코드의 위치가 결정됩니다. 즉, 프라이머리 키로 검색하는 경우(특히 범위로 많은 레코드를 검색하는 경우) 클러스터되지 않은 테이블에 비해 매우 빠르게 처리될 수 있음을 의미합니다. MyISAM과 같이 클러스터되지 않는 테이블에서는 사실 프라이머리 키로 뭘 선택해도 성능의 차이는 별로 없을 수 있지만 InnoDB에서는 엄청난 차이를 만들어 냅니다. 또한 프라이머리 키는 그 의미만큼이나 중요한 역할을 하기 때문에 대부분 검색에서 상당히 빈번하게 사용되는 것이 일반적입니다. 그러므로 설령 그 칼럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 프라이머리 키로 설정하는 것이 좋습니다.

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

가끔 프라이머리 키가 없는 테이블을 자주 보게 되는데, 가능하면 AUTO_INCREMENT 칼럼을 이용해라도 프라이머리 키는 설정하길 권장합니다. InnoDB 테이블에서 프라이머리 키를 정의하지 않으면 AUTO_INCREMENT와 같은 자동 증가 칼럼을 내부적으로 추가합니다. 하지만 이렇게 자동으로 추가된 칼럼은 사용자에게 보이지 않기 때문에 SQL에서 전혀 사용할 수가 없습니다. 즉, InnoDB 테이블에 프라이머리 키를 정의하지 않는 경우와 AUTO_INCREMENT 칼럼을 생성하고 프라이머리 키로 설정하는 것이 결국 똑같습니다. 그렇다면 사용자가 사용할 수 잇는 값(AUTO_INCREMENT 값)을 프라이머리 키로 설정하는 것이 좋을 것입니다.

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

여러 개의 칼럼이 복합으로 프라이머리 키가 만들어지는 경우 프라이머리 키의 크기가 길어질 때가 가끔 있습니다. 하지만 프라이머리 키의 크기가 길어도 보조 인덱스가 필요치 않다면 그대로 프라이머리 키를 사용하는 것이 좋습니다. 만약 보조 인덱스도 필요하고 프라이머리 키의 크기도 같다면 AUTO INCREMENT 칼럼을 추가하고, 이를 프라이머리 키로 설정하면 됩니다. 이렇게 프라이머리 키를 대체하기 위해 인위적으로 추가된 프라이머리 키를 인조식별자라고 합니다. 그리고 로그 테이블과 같이 조회보다는 INSERT 위주의 테이블들은 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 됩니다.

출처: https://12bme.tistory.com/149?category=682920 [길은 가면, 뒤에 있다.]