[데이터베이스] 클러스터링 인덱스, 유니크 인덱스, 외래 키

cs-study에서 스터디를 진행하고 있습니다.

클러스터링 인덱스

개념 및 특징

• 테이블의 PK에 대해 적용되는 인덱스이다.
• PK 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다.
• PK 값에 의해 해당 레코드의 물리적인 저장 위치가 결정된다.
  • PK 값이 바뀌면 해당 레코드의 물리적인 저장 위치도 바뀐다.
• MySQL에서 클러스터링 인덱스는 InnoDB 스토리지 엔진에서만 지원한다.
  • PK 기반 검색이 매우 빠르지만, 레코드의 저장이나 PK 값을 변경할 때 속도가 다른 방식에 비해 느리다.
• 항상 PK 값을 기준으로 정렬 상태를 유지한다.
  • 아래에서 설명하겠지만, 해당 특징으로 인해 물리적으로 행이 재배열된다.

 

클러스터링 인덱스의 구조

 

위의 클러스터링 인덱스 구조를 보면 일반 B-Tree와 비슷해 보인다. 하지만 세컨더리 인덱스를 위한 B-Tree의 리프 노드와는 달리 클러스터링 인덱스의 리프 노드에는 레코드의 모든 컬럼이 같이 저장되어 있는 것을 알 수 있다. 즉, 클러스터링 인덱스는 그 자체가 하나의 거대한 인덱스 구조로 관리되는 것이다.

 

 

세컨더리 인덱스는 위와 같이 PK 이외의 컬럼에 대해 적용되는 인덱스를 말하는데, 리프 노드에는 Key에 해당하는 인덱스 레코드와 물리적 주소에 해당하는 레코드 주소만 갖고 있는 것을 알 수 있다.

 

PK 값의 변화가 일어난다면?

만약 위 클러스터링 인덱스에서 다음 쿼리와 같이 PK 값을 변경하면 클러스터링 인덱스의 데이터 레코드에는 무슨 일이 벌어질까?

 

UPDATE tb_test SET emp_no = 100002 WHERE emp_no = 100007;

 

결과는 다음과 같다.

 

 

기존 emp_no가 10007이 100002로 바뀌면서 3번 페이지에서 2번 페이지로 바뀐 것을 알 수 있다. 즉, PK 값 변경으로 인해 PK가 존재하는 데이터 레코드가 통으로 다른 페이지로 이동하게 된 것이다. 따라서 PK 값은 바꾸지 않는 것을 권장한다.

 

클러스터링 인덱스에서 PK가 존재하지 않는다면?

InnoDB 스토리지 엔진은 다음과 같은 우선 순위대로 PK를 대체할 컬럼을 선택한다.

 

1. PK가 있으면 기본적으로 PK를 클러스터링 키로 선택한다.
2. NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 키로 선택한다.
3. 자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후, 그것을 클러스터링 키로 선택한다.

 

특히 3번 과정은 InnoDB 스토리지 엔진이 내부적으로 레코드의 일련번호 컬럼을 생성하는데, 해당 컬럼은 쿼리 문장에 명시적으로 사용할 수 없으며 사용자에게 노출되지도 않는다. 그래서 아무런 의미가 없는 값이 되므로 되도록 PK를 명시적으로 생성하는 것을 권장한다.

또한, 웬만하면 테이블에 PK는 존재하므로 아래에서 PK와 클러스터링 키는 동일한 의미로 사용하고자 한다.

 

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

• MyISAM이나 MEMORY 테이블 같은 클러스터링되지 않은 테이블은 INSERT될 때 처음 저장된 공간에서 절대 이동하지 않는다.
  • 데이터 레코드가 저장된 주소는 내부적인 레코드 아이디(ROWID) 역할을 하며, PK나 세컨더리 인덱스의 각 키는 그 주소(ROWID)를 이용하여 실제 데이터 레코드를 찾아 온다.
  • 그래서 MyISAM이나 MEMORY 테이블은 PK와 세컨더리 인덱스에 대해 차이가 없다.
• 만약 InnoDB 스토리지 엔진에서 세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있으면 어떻게 될까?
  • PK가 아닌 그 외 클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경되고 그때마다 해당 테이블의 모든 인덱스에 저장된 주소 값을 변경해야 한다.
  • 예를 들어 닉네임을 클러스터링 키로 선정한다면, 회원이 닉네임을 변경할 때마다 해당 닉네임이 존재하는 데이터 레코드의 주소 값을 바꿔야 한다.
  • 이런 오버헤드를 제거하기 위해 InnoDB 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 PK 값을 저장하도록 구현되어 있다.
  • 아래 그림처럼 InnoDB 테이블의 세컨더리 인덱스를 보면, 리프 노드에 레코드의 모든 컬럼이 저장되어 있는 것도 아니고 레코드의 주소가 저장되어 있는 것도 아닌 PK가 저장되어 있는 것을 알 수 있다. 이 PK를 이용하여 클러스터링 인덱스인 데이터 파일을 탐색한다.

 

 

마지막으로 employees 테이블에서 first_name 컬럼으로 검색하는 경우, PK로 클러스터링된 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 인덱스를 검색해서 레코드의 PK 값을 확인한 후, PK 인덱스를 검색해서 최종 레코드를 가져온다.

 

InnoDB가 좀 더 복잡하게 처리되지만, PK 인덱스로 곧장 검색할 경우 뛰어난 성능을 보여주기 때문에 성능 저하에 너무 걱정할 필요는 없다.

 

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

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

 

정리하자면, 클러스터링 인덱스의 장점은 빠른 읽기이고 단점은 느린 쓰기인 것을 알 수 있다. 일반적으로 웹 서비스와 온라인 트랜잭션 환경에서는 쓰기와 읽기 비율이 1:9 혹은 2:8 정도이므로 빠른 읽기에 비중을 두는 것이 중요하다.

 

클러스터링 인덱스 사용 시 주의 사항

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

• 클러스터링 인덱스의 경우 모든 세컨터리 인덱스가 PK 값을 포함한다. 그래서 PK의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다.
  • 일반적으로 테이블에 세컨더리 인덱스가 4~5개 정도 생성된다는 것을 고려하면 세컨더리 인덱스의 크기가 급격히 증가한다.
• 따라서 PK 값을 되도록 작게 설정하는 것이 좋다.

 

PK는 AUTO_INCREMENT보다는 업무적인 컬럼으로 생성 (가능한 경우)

• InnoDB의 PK는 클러스터링 키로 사용되며 이 값에 의해 레코드의 위치가 결정된다.
• PK는 대부분 검색에서 빈번하게 사용되므로 PK의 크기가 조금 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 컬럼을 PK로 선택하는 것이 좋다.

 

PK는 반드시 명시할 것

• 가능하면 AUTO_INCREMENT를 사용하더라도 PK를 꼭 만들자.
• 앞서 말했듯이, PK가 없고 NOT NULL + Unique 인덱스도 없으면 InnoDB 내부적으로 일련 번호 컬럼을 추가한다.
• 이렇게 자동으로 생성된 컬럼은 사용자에게 보이지도 않고, 사용자가 전혀 접근할 수도 없으므로 의미가 없는 값이 된다.

 

AUTO_INCREMENT 컬럼을 인조 식별자로 사용할 경우

• PK의 크기가 커도 세컨더리 인덱스가 거의 필요 없다면 그대로 PK를 사용하는 것이 좋다.
• 세컨더리 인덱스도 필요하고 PK의 크기가 크다면 AUTO_INCREMENT 컬럼을 추가하고, 이를 PK로 지정하면 된다.
• 이렇게 업무 상의 의미를 지닌 PK를 대체하기 위해 AUTO_INCREMENT과 같은 방식을 사용하여 인위적으로 추가된 PK를 인조 식별자라고 부른다.
• 로그 테이블과 같이 조회보다는 INSERT 위주의 테이블들은 인조 식별자를 PK로 정하는 것이 좋다.

 

유니크 인덱스

• 말 그대로 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없는 것을 뜻한다.
• 유니크 인덱스에는 NULL이 저장될 수 있는데, NULL은 값이 아니므로 여러 개 저장되어도 무방하다.
• MyISAM이나 MEMORY 테이블에서 PK는 사실 NOT NULL 성질이 있는 유니크 인덱스로 볼 수 있지만, InnoDB 테이블의 PK는 클러스터링 키의 역할을 하므로 단순히 NOT NULL 성질의 유니크 인덱스와는 결이 다르다.

 

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

• 유니크한 세컨더리 인덱스는 결국 유니크 인덱스가 된다.
• 유니크 인덱스와 유니크하지 않은 세컨더리 인덱스는 구조 상 차이가 없다.

 

인덱스 읽기

• 흔한 오해: 유니크 인덱스는 세컨더리 인덱스보다 성능이 좋다.
  • 유니크 인덱스는 1건의 레코드만 읽으면 되지만, 세컨더리 인덱스는 중복된 여러 건의 레코드를 읽어야 한다.
• 위 오해에서 잘못된 점은 인덱스의 구조로 인해 세컨더리 인덱스가 느린 것이 아니라, 단순히 중복된 값을 읽어야 해서 느린 것이다.
  • 만약 0.1초동안 레코드 1건을 읽었고, 0.2초동안 레코드 2건을 읽었으면 두 케이스의 성능은 동일한 것이다.
• 물론 중복된 레코드가 굉장히 많다면 전체 소요 시간 측면에서 유니크 인덱스가 빠른 것은 맞다. 다만, 두 인덱스의 구조는 차이가 없으므로 읽기 성능은 동일하다고 보는 것이 타당하다.

 

인덱스 쓰기

• 유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 필요하다.
  • MySQL에서는 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 값 쓰기를 할 때는 쓰기 잠금을 사용하여 데드락이 빈번히 발생한다.
• 따라서 세컨더리 인덱스보다 유니크 인덱스가 쓰기 작업에서는 성능 상 느리다.

 

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

• 유일성이 꼭 보장되어야 하는 작업에 대해서는 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크 인덱스보다는 세컨더리 인덱스를 사용하는 것을 권장한다.

 

외래 키

• MySQL에서 외래 키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래 키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스까지 생성된다. 외래 키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없다.
• InnoDB의 외래 키 관리에는 중요한 2가지 특징이 있다.
  • 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
  • 외래 키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.

 

CREATE TABLE tb_parent (
    id INT NOT NULL,
    fd VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB;

CREATE TABLE tb_child (
    id INT NOT NULL,
    pid INT DEFAULT NULL,
    fd VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY ix_parentid (pid),
    CONSTRAINT 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');

 

위와 같은 테이블에서 언제 자식 테이블의 변경이 잠금 대기를 하고, 언제 부모 테이블의 변경이 잠금 대기를 하는지 아래 예제에서 살펴 보자.

 

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

 

왼쪽은 1번 세션, 오른쪽은 2번 세션이라고 정의하자. 1번 세션에서 먼저 트랜잭션을 시작하고 부모 테이블에서 id가 2인 레코드에 UPDATE를 실행한다. 그리고 2번 세션에서 자식 테이블의 외래 키 컬럼인 pid를 2로 변경하는 쿼리를 실행한다. 그러면 2번 세션에서 1번 세션의 쓰기 잠금 해제할 때까지 기다리다고 결국 lock wait timeout이 초과되어 트랜잭션이 강제 종료되는 것을 확인할 수 있다.

 

 

만약 1번 세션에서 도중에 rollback이나 commit을 수행하면 쓰기 잠금이 자동으로 해제되므로 2번 세션에서는 자식 테이블의 UPDATE 작업을 이어서 할 수 있다.

정리하자면, 자식 테이블의 외래 키 컬럼의 변경은 부모 테이블의 확인이 필요한데, 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때까지 기다리게 되는 것이다. 이것이 InnoDB의 외래 키 관리의 첫 번째 특징이다.

만약 외래 키(pid)가 아닌 다른 컬럼을 UPDATE한다면 잠금 대기가 발생하지 않는다. 이것이 InnoDB의 외래 키 관리의 두 번째 특징이다.

 

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

 

변경하는 테이블의 순서만 바꾸어 보았다. 1번 세션에서는 부모 키 “1”을 참조하는 자식 테이블의 레코드를 변경하고 있으니, tb_child 테이블의 레코드에 대해 쓰기 잠금을 획득한다. 이 상태에서 2번 세션이 tb_parent 테이블에서 id가 1인 레코드를 삭제하는 경우, 해당 쿼리는 tb_child 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 기다려야 한다. 이는 자식 테이블이 생성될 때 정의된 외래 키의 특성 (ON DELETE CASCADE)때문에 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되는 식으로 작동하기 때문이다.

 

출처

• Real MySQL

 

예상 면접 질문 및 답변

클러스터링 인덱스란?

• 테이블의 PK에 대해 적용되는 인덱스이다.
• PK 값에 의해 해당 레코드의 물리적인 저장 위치가 결정된다.
• MySQL에서 클러스터링 인덱스는 InnoDB 스토리지 엔진에서만 지원한다.
• 항상 PK 값을 기준으로 정렬 상태를 유지되며, 이 특징으로 인해 물리적으로 행이 재배열된다.

 

넌클러스터링 인덱스란? (= 세컨더리 인덱스란?)

• 테이블의 PK 외에 대해 적용되는 인덱스이다.

 

InnoDB 스토리지 엔진에서 클러스터링 인덱스와 세컨더리 인덱스가 동작하는 과정을 설명하라.

• 클러스터링 인덱스
  • 클러스터링 인덱스의 리프 노드에는 레코드의 모든 컬럼이 같이 저장되어 있다. 그래서 일반적인 레인지 인덱스 스캔 방식으로 탐색하다가 리프 노드에 도달하면, 해당 클러스터링 인덱스 키에 해당하는 레코드의 모든 정보를 가져온다.
• 세컨더리 인덱스
  • 세컨더리 인덱스의 리프 노드에는 클러스터링 인덱스의 리프 노드와는 달리 PK가 존재한다. 그래서 일반적인 레인지 인덱스 스캔 방식으로 탐색하다가 리프 노드에 도달하면, 해당 세컨더리 인덱스 키에 해당하는 레코드의 PK를 이용하여 데이터 파일을 탐색한다. 이때 데이터 파일은 클러스터링 인덱스이므로 이 데이터 파일의 리프 노드에는 해당 PK에 해당하는 레코드의 모든 정보를 가져온다.

 

MyISAM이나 Memory 스토리지 엔진에서 클러스터링 인덱스를 지원하는가?

지원하지 않는다. 그래서 해당 엔진을 사용하는 인덱스의 리프 노드에는 인덱스 키와 찾으려는 레코드의 물리적 주소를 담고 있고, 이 물리적 주소를 사용하여 물리적 파일의 최종 레코드를 가져오게 된다.

 

클러스터링 인덱스의 장점과 단점을 설명하라.

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

 

정리하자면, 클러스터링 인덱스의 장점은 빠른 읽기이고 단점은 느린 쓰기이다.

 

유니크 인덱스란?

• 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없는 것을 뜻한다.

 

유니크 인덱스와 세컨더리 인덱스의 성능적 차이를 설명하라.

• 읽기 성능은 차이가 없지만. 쓰기 성능은 중복 체크를 위한 읽기 잠금 때문에 유니크 인덱스가 더 느리다.