5.9 클러스터링 인덱스 ~ 5.11 외래키

July 30th, 2021

클러스터란 여러 개를 하나로 묶는다는 의미로 사용된다. MySQL에서 클러스터링 인덱스는 InnoDB와 TokuDB 스토리지 엔진에서만 지원된다.

5.9.1 클러스터링 인덱스

  • 클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다. 즉, 프라이머리 키값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 한다.
  • 레코드의 저장 위치가 PK값에 의해 결정된다.
  • 클러스터링 인덱스는 PK 값에 의해 레코드의 저장 위치가 결정되므로 인덱스 알고리즘이라기 보다 테이블 레코드의 저장 방식이라고 볼 수 있다.

  • 클러스터링 인덱스는 B-Tree와 유사하다. 하지만 클러스터링 인덱스의 리프 노드에는 레코드의 모든 컬럼이 같이 저장되어 있다.
  • 클러스터링 테이블은 그 자체가 하나의 거데한 인덱스 구조로 관리된다고 볼 수 있다.


  • PK가 없는 InnoDB 테이블은 아래의 우선순위대로 PK를 대체할 컬럼을 선택한다 :
    • PK가 있으면 기본적으로 PK를 클러스터 키로 선택
    • NOT NULL 옵션의 유니크 인덱스 중, 첫번째 인덱스를 클러스터 키로 선택
    • 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후, 클러스터 키로 선택(아무 의미없는 숫자이며, 쿼리 문장에 명시적으로 사용 불가)

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

  • MyISAM이나 MEMORY 테이블과 같은 클러스터링 되지 않은 테이블은 INSERT 될때마다 한 번 저장된 공간에서 절대 이동하지 않는다. 그래서 해당 스토리지 엔진에서의 테이블은 PK와 보조 인덱스는 구조적으로 아무런 차이가 없다.
  • InnoDB의 모든 보조 인덱스는 해당 레코드가 저장된 주소가 아니라, 프라이머리 키 값을 저장하도록 구현되어있다.

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

장점 : * PK로 검색할 때 처리 성능이 빠르다. * 테이블의 모든 보조 인덱스가 PK키를 갖고 있기 때문에, 인덱스만으로 처리될 수 있는 경우가 많음

단점 : * 테이블의 모든 보조 인덱스가 PK키를 갖고 있어 클러스터 키 값의 키가 클 경우, 전체적으로 인덱스의 크기가 커진다. * 보조 인덱스를 통해 검색할 때, PK키로 다시 한 번 검색해야 해 성능이 다소 느림 * INSERT시 PK키에 의해 저장 위치가 결정되므로 처리 성능이 느림 * PK키 변경시 레코드를 DELETE하고 INSERT하는 작업이 필요해 처리 성능이 느리다.

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

MyISAM과 같이 클러스터링되지 않은 테이블에 비해 InnoDB에서는 주의해야 할 사항이 몇가지 더 있다.

  • 클러스터 인덱스 키의 크기 : PK키가 커지면, 보조 인덱스가 차지하는 공간도 커지기 때문에 신중히 접근해야 한다.
  • PK키는 AUTO-INCREMENT 혹은 업무적인 컬럼으로 생성할 것 : InnoDB의 PK키는 클러스터 키로 사용되며, 이 값에 의해 레코드의 위치가 결정된다. 설령 컬럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 컬럼을 프라이머리 키로 설정하는 것이 좋다.
  • PK키는 반드시 명시할 것 : InnoDB 테이블에서 PK키를 정의하지 않으면 AUTO_INCREMENT와 같은 자동 증가 컬럼을 내부적으로 추가한다.
  • AUTO-INCREMENT 컬럼을 인조 식별자로 사용시

5.10 유니크 인덱스

유니크란 사실 인덱스라기 보다는 제약 조건에 가깝다고 볼 수 있다. 말그대로 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미한다.

5.10.1 유니크 인덱스와 일반 보조 인덱스의 비교

  • 유니크 인덱스와 유니크하지 않은 일반 보조 인덱스는 구조상 차이가 없다. 하지만 성능상 이점이 있을 수 있다.

인덱스 읽기 :

  • 인덹스 읽기는 유니킄 인덱스와 일반 보조 인덱스와 별반 차이 없다.
  • 일반 보조 인덱스에서 한 번 더 해야 하는 작업은 디스크 읽기가 아니라 CPU에서 값을 비교하는 작업이기 때문이다.

인덹스 쓰기 :

  • 새로운 레코드 INSERT 혹은 값 변경시 인스 쓰기 작업이 필요하다.
  • 유니크 인덱스의 키 값을 쓸 때는 중복 값이 있는지 없는지 체크하는 과정이 한단계 더 필요하다. 그래서 일반 보조 인덱스의 쓰기보다 느리다.

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

  • 하나의 테이블에서 같은 컬러에 유니크 인덱스와 일반 인덱스를 동시에 생성해두지 말자(유니크 인덱스는 일반 다른 인덱스와 역할이 같다)
  • 똑같은 컬럼에 대해 PK키와 유니크 인덱스를 동일하게 생성하는 것도 중복 생성 건으로 유의하자.
  • 유일성이 보장되어야 하는 컬럼에 대해서는 유니크 인덱스를 생성하되, 필요하지 않다면 유니크 인덱스 보다는 유니크하지 않은 보조 인덱스를 생성하는 방법을 고려해보자.

5.11 외래키

  • MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약 설정시 자동으로 연관되는 테이블의 컬럼에 인덱스가 생성된다.
  • 외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없다.

InnoDB의 외래키 관리에는 두가지 주요한 특징이 있다.

  • 테이블의 변경이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
  • 외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.
CREATE TABLE tb_parent (
	id INT NOT NULL,
	fd VARHAR(100) NOT NULL,
	PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE tb_child ( 
	id INT NOT NULL,
	pid INT DEFULAT NULL, -- // parent.id 
	fd VARCHAR(100) DEFAULT NULL,
	PRIMARY KEY(id),
	KEY ix_parentid (pid),
	CONTRAINT child_ibfk_1 FOREIGN KEY (pid) REFERENCES tb_parent (id) ON DELETE CASCADE
) ENGINE = INNODB;

INSERT INTO tb_parent VALUES (1, 'parent-1'), (2, 'parent-2');
INSERT INTO tb_child VALUES (100, 1, 'child-100');

5.11.1 자식 테이블의 변경이 대기하는 경우

아래의 순서대로 커넥션이 실행된다고 가정해보자.

  1. BEGIN;(Transaction 1)
  2. UPDATE tb_parent SET fd = ‘changed-2’ WHERE id=2; (Transaction 1)
  3. BEGIN; (Transcation 2)
  4. UPDATE tb_child SET pid=2 WHERE id=100; (Transaction 2) 대기 발생
  5. ROLLBACK; (Transaction 1)
  • 자식 테이블의 외래키 컬럼의 변경(INSERT, UPDATE)은 부모 테이블의 확인이 필요하며, 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때까지 기다린다.
  • 만약 자식 테이블의 외래키(pid)가 아닌 컬럼의 변경은 외래키로 인한 잠금확장이 발생하지 않는다.

5.11.2 부모 테이블의 변경 작업이 대기하는 경우

이제 변경하는 테이블의 순서만 변경해보자 :

  1. BEGIN;(Transaction 1)
  2. UPDATE tb_child SET fd = ‘changed-2’ WHERE pid=1; (Transaction 1)
  3. BEGIN; (Transcation 2)
  4. DELETE FROM tb_parent WHERE id=1; (Transaction 2) 대기 발생
  5. ROLLBACK; (Transaction 1)
  • 첫번째 커넥션에서 부모키 1을 참조하는 자식 테이블의 레코드를 변경하면, tb_child 테이블의 레코드에 대해 쓰기 잠금을 획득한다.
  • 이 상태에서 부모 테이블의 레코드를 삭제하려면 tb_child 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 기다려야 한다.
  • 이는 자식 테이블이 생성될때 정의된 외래키 특성(ON DELETE CASCADE) 때문이다. 이 특성은 부모 레코드 삭제시 자식 레코드도 동시에 삭제되도록 작동하기 때문이다.

5.12 기타 주의사항

  • 스토리지 엔진별 지원 인덱스 목록
    • MyISAM : B-Tree, R-Tree, Fulltext-index
    • InnoDB : B-Tree
    • Memory : B-Tree
    • TokuDB : Fractal-Tree
    • NDB(MySQL Cluster) : Hash, B-Tree

analyze와 optimize의 필요성

MyISAM이나 InnoDB 테이블의 경우, 인덱스에 대한 통계정보를 관리하고, 각 통계 정보를 기반으로 쿼리 실행 계획을 수립한다.
통계 정보중 가장 중요한 컬럼은 Cardinality 항목이다. InnoDB와 MyISAM 모두 컬럼의 Cardinality에 의존해서 실행계획을 수립한다.
이 통계 정보는 자주 업데이트 되기 때문에, 쿼리 실행 계획을 최적화 하거나 동일하게 유지하기 위해 별도로 통계 정보를 백업했다가 복구하는 작업은 할 수 없을 뿐더러 한다고 해도 의미가 없다. (MySQL 서버가 처음 테이블을 열거나, DDL이 수행될때 갱신된다). 쿼리의 실행 계획이 의도했던 것과 다르게 만들어질 때는 인덱스의 통계 정보가 실제와 다르게 수집되어 있는 것은 아닌지 한 번 확인하자. 이 때 ANALYZE 명령으로 통계 정보를 다시 수집할 수 있다.

  • 테이블의 데이터가 별로 없는 경우(개발 DB에서 많이 발생)
  • 단시간에 대량의 데이터가 늘어나거나 줄어든 경우

참고자료 : Real MySQL - 5.9 클러스터링 인덱스 ~ 5.12 기타 주의사항

oksusutea's blog

꾸준히 기록하려고 만든 블로그