RealMySQL 8.8 ~ 8.10

클러스터링 인덱스

• 프라이머리 키 값에 의해 레코드의 저장 위치가 결정

• 프라이머리 키가 없는 경우 InnoDB 스토리지 엔진이 프라이머리 키를 대체할 칼럼을 선택한다.

• 클러스터 인덱스는 트리로 저장되어, Root페이지와 Leaf페이지로 구성된다. 또한 Root페이지는 Leaf페이지의 주소로 구성하고, Leaf페이지는 실제 데이터 페이지로 구성된다. 따로 추가적인 저장소에 인덱스 페이지를 만들지 않는다.

프라이머리 키가 없는 InnoDB 테이블의 클러스터링 테이블 구성

• 프라이머리 키가 있다면 클러스터링 키로 선택

• NOT NULL 옵션의 유니크 인덱스 중 첫 번째 인덱스를 선택

• 자동으로 테이블 내 auto-increment 컬럼을 생성 선택

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

• 클러스터링 테이블의 세컨더리 인덱스가 프라이머리 키 값을 포함, 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 커짐

• 프라이머리 키는 Auto Increment보다는 업무적인 칼럼으로

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

• 프라이머리 키는 반드시 명시할 것
  (InnoDB 테이블에서 프라이머리 키를 정의하지 않으면 스토리지 엔진이 내부적으로 일련번호 칼럼을 추가한다. 이렇게 추가된 칼럼은 사용자에게 보이지 않아 접근할 수 없다.)

• Auto Increment 칼럼을 인조 식별자로 사용할 경우
  (세컨더리 인덱스도 필요하고 프라이머리 키의 크기도 길다면 Auto INCREMENT 칼럼을 추가하고 이를 프라이머리 키로 설정하면 된다. 이를 인조 식별자라고 한다.)

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

• 빠른 검색, 느린 생성, 수정 삭제

• 인덱스는 Balancing - Tree구조이기 때문에 기본적으로 모두 같은 크기의 페이지를 유지한다.

• 그래서 새로운 데이터가 추가되면 새로운 데이터가 추가 될 테이블의 기존 데이터 절반이 새로운 페이지로 이동한 후에 새로운 행이 추가된다.

• 이와 같이, 인덱스를 생성/수정/삭제할 때 페이지 분할이 일어나며 데이터 페이지 전체를 다시 정렬해야 하기 때문에 느려진다.

• 항상 순서를 유지해야 한다.

유니크 인덱스

인덱스 읽기

• 유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지 인덱스 자체의 특성 때문에 느린 것이 아니다.

인덱스 쓰기

• 유니크 인덱스의 키 값을 쓸 때는 중복된 값 유무를 체크하는 과정이 필요

• 그래서 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느리다.

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

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

외래키

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

외래키 특징

• 테이블의 변경이 발생하는 경우만 잠금 경합이 발생한다.

• 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.

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

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

커넥션 -1
1. BEGIN;
2. UPDATE tb_parent SET fd = 'changed-2' WHRE id=2;
3. 대기
4. 대기
5. ROLLBACK;
6.

커넥션 -2
3. BEGIN;
4. UPDATE tb_child SET pid=2 WHERE id=100;
5.
6. Query OK

• 1번 커넥션에서 먼저 트랜잭션을 시작하고 부모 테이블에서 ID가 2인 레코드에 UPDATE를 실행한다. 이과정에서 1번 커넥션이 부모 테이블에서 ID가 2인 레코드에 대해 쓰기 잠금을 획득한다.

• 2번 커넥션에서 자식 테이블의 외래키 칼럼인 PID를 2로 변경하는 쿼리를 실행, 이 쿼리는 부모 테이블의 변경 작업이 완료될 때까지 대기한다.

• 다시 1번 커넥션에서 ROLLBACK이나 COMMIT으로 트랜잭션을 종료하면 2번 커넥션의 대기중이던 작업이 즉시 처리되는 것을 확인할 수 있다.

• 즉 자식 테이블의 외래키 칼럼의 변경은 부모 테이블의 확인이 필요

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

커넥션 - 1
1. BEGIN;
2. UPDATE tb_child SET fd='changed-100' WHERE id = 100;
3.
4.
5. ROLLBACK
6.

커넥션 - 2
1.
2.
3. BEGIN;
4. DELETE FROM tb_parent WHERE id=1;
5.
6. Query Ok

• 트랜잭션 1의 부모키를 참조하는 자식 테이블의 레코드를 변경하면 쓰기 잠금을 획득
• 트랜잭션 2의 부모 테이블의 레코드를 삭제하는 경우 쓰기 잠금이 해제될 때까지 기다려야 한다.
• 이는 자식 테이블이 생성될 때 정의된 외래키의 특성 ONDELETE CASCADE 때문에