[MySQL] 외래키

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

특징

InnoDB의 외래키에는 두 가지 중요한 특징이 있습니다.

• 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합이 발생한다.
• 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합을 발생시키지 않는다.

다음과 같은 테이블이 있습니다.

mysql> CREATE TABLE parent (
         id INT NOT NULL
         fd VARCHAR(100) NOT NULL, PRIMARY KEY (id)
       ) ENGINE=InnoDB;
       
 mysql> CREATE TABLE child (
          id INT NOT NULL,
          pid INT DEFAULT NULL,
          fd VARCHAR(100) DEFAULT NULL,
          PRIMARY KEY(id),
          KEY ix_parent (pid),
          CONSTRAINT child_ibfk_1 FOREIGN KEY (pid) REFERENCES parent (id) ON DELETE CASCADE
        ) ENGINE=InnoDB;
        
mysql> INSERT INTO parent VALUES (1, 'P1'), (2, 'P2');
mysql> INSERT INTO child VALUES (100, 1, 'C1');

위와 같은 테이블에서 다음 쿼리를 실행할 때 언제 잠금 대기가 발생하는지 살펴봅시다.

자식 테이블의 변경이 대기

1. 커넥션 1이 부모 테이블에서 id=2인 레코드에 쓰기 잠금 획득
2. 커넥션 2가 외래키 칼럼이 2(pid=2)인 레코드의 읽기 잠금 대기
3. 커넥션 1이 롤백되면서 커넥션 2 완료됨

자식 테이블의 외래키 칼럼의 변경은 부모테이블의 확인이 필요합니다. 즉, 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 쓰기 잠금이 해제될 때까지 대기합니다(잠금 확장). 자식 테이블의 외래키가 아닌 칼럼의 변경은 외래키로 인한 잠금 확장이 발생하지 않습니다.

부모 테이블의 변경이 대기

1. 커넥션 1이 자식 테이블에 쓰기 잠금 획득
2. 커넥션 2가 id=1인 레코드 삭제
3. 자식 테이블 중 pid=1인 레코드 확인 (DELETE CASCADE)
4. 커넥션1이 롤백되면서 커넥션 2 완료

부모 테이블에서 레코드를 삭제할 때 연관된 자식테이블도 삭제해야합니다(DELETE CASCADE). 때문에 자식 테이블의 쓰기 잠금이 해제될 때까지 대기합니다.

 

DB에서 외래키를 물리적으로 생성하려면 이러한 잠금 경합을 고려하여 모델링을 해야합니다. 물리적 외래키를 생성하면 자식 테이블에 레코드가 추가될 때 부모 테이블에 참조키가 있는지 확인합니다. 이를 위해 연관 테이블에 읽기 잠금을 거는데 이것이 다른 테이블로 확장되면 전체적인 쿼리 성능이 떨어집니다.