클러스터링 인덱스

MyISAM엔진 테이블은 데이터 파일을 레코드 주소에서 바로 찾아가 찾는다. 반면, InnoDB엔진 테이블은 인덱스에 저장돼 있는 프라이머리 값을 이용해서 프라이머리 키 인덱스를 검색하고 프라이머리 키 인덱스의 리프 페이지에 저장되어 있는 레코드를 읽는다.

단순히 과정으로만 봤을 때, InnoDB엔진이 성능이 더 떨어져 보인다. B-Tree를 다시 한번 더 검색을 해야되기 때문이다. 그러나, 꼭 그렇지만은 않다.

클러스터링 인덱스

클러스터링 인덱스

• 클러스터링: '여러 개를 하나로 묶는다.'는 의미

• MySQL에서의 클러스터링: 테이블 레코드가 비슷한 것끼리 묶어서 저장하는 형태로 구현하는 것

  • 여기서 비슷한 것끼리 묶는다는 건 프라이머리 키를 기준으로 묶는다는 의미이다.

• MySQL 8.0 Reference Manual에서 클러스터링 인덱스는 primary key와 동의어라고 한다.

  Each InnoDB table has a special index called the clustered index that stores row data. Typically, the clustered index is synonymous with the primary key.

클러스터링 인덱스는 위와 같이 프라이머리 키 값과 관련이 있다. 만약 프라이머리 키 값이 변경되면 그 레코드의 저장 위치도 바뀌어야 한다. 그렇기 때문에 클러스터링 인덱스는 pk에 의존력이 매우 크다.

만약, 기본키를 사용할 논리적으로 unique하고 null이 아닌 열, 열 집합이 없는 경우 auto-increment column이 추가된다.

pk가 정의되어 있지 않다면, InnoDB는 모든 키 열이 NOT NULL로 정의된 첫번째 UNIQUE INDEX를 클러스터링 인덱스로 사용한다.

만약, 테이블이 PK 또는 적절한 UNIQUE 인덱스가 없다면 InnoDB는 row ID 값이 포함된 합성 열에 GEN_CLUST_INDEX라고 불리는 숨겨진 클러스터링 인덱스를 생성한다. 이것은 아무 의미 없는 숫자 값으로 클러스터링되는 것이다.

따라서 InnoDB 테이블에서 가능하다면 primary key를 설정해주는게 바람직하다.

세컨더리 인덱스

클러스터된 인덱스 이외의 인덱스를 세컨더리 인덱스라 한다.

MyISAM에서 프라이머리키나 세컨더리 인덱스의 키는 그 주소를 통해 실제 데이터를 갖고 온다.

InnoDB에서 모든 세컨더리 인덱스는 프라이머리 키 값을 저장한다.

만약, 세컨더리 키가 실제 레코드가 저장된 주소를 가지고 있으면 프라이머리 키 값이 바뀔 때 마다 실제 저장된 주소가 바뀌게 되고 그렇게 되면 세컨더리 키를 바꿔줘야 한다. 실제로 이런 현상이 나타나지 않게 하려고 InnoDB테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현돼 있다.

장점과 단점

장점

• 프라이머리 키가 중심인 인덱스이기 때문에 프라이머리 키로 검색을 할 때, 처리 성능이 매우 빠르다.

• 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 있다.

단점

• 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 이 키 값이 커질수록 전체적으로 인덱스 크기도 커진다.

• 세컨더리 인덱스로 검색을 할 때 pk를 거쳐야 되기 때문에 성능이 안좋다.

• INSERT할 때 pk에 의해 레코드의 저장 위치가 결정되기 때문에 성능이 느리다.

주의 사항

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

위에서 말한대로 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있다. 세컨더리 인덱스가 4개인 테이블이 있다고 하자. 프라이머리 키가 20바이트와 60바이트일 때를 비교해본다.

프라이머리 키가 20바이트일 때

레코드당 증가하는 인덱스 크기: 20 * 4 = 80(바이트)

1000만건 레코드 저장시 증가하는 인덱스 크기: 1000만 * 80 = 8억

프라이머리 키가 80바이트일 때

레코드당 증가하는 인덱스 크기: 80 * 4 = 320(바이트)

1000만건 레코드 저장시 증가하는 인덱스 크기: 1000만 * 320 = 32억

즉, 프라이머리 키 입장에서의 바이트단위 4배 차이가 레코드 수가 대용량일 때는 억단위의 4배가 되게 된다.

AUTO-INCREMENT보다는 업무적인 칼럼

클러스터링 인덱스에서 primary key는 매우 중요한 의미를 갖고 있기 때문에 칼럼의 크기가 크더라도 업무적으로 대표하는 칼럼이 있다면 그 칼럼을 primary key로 설정하는 것이 좋다.

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

서두에 말한것 처럼 프라이머리 키를 명시하지 않으면 내부적으로 GEN_CLUST_INDEX라고 불리는 숨겨진 클러스터링 인덱스를 생성한다. 이 인덱스는 사용자에게 공개되지 않기 때문에 아무 의미없는 인덱스일 뿐이다. 따라서, AUTO-INCREMENT를 이용해서라도 프라이머리 키를 생성하는 것이 좋다.

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

세컨더리 인덱스가 필요한데 프라이머리 키의 크기가 크다면 AUTO-INCREMENT칼럼을 추가하여 이것을 pk로 저장한다. 그러면 아무래도 INSERT할 때 성능 향상에 도움이 된다.