본문 바로가기
🥞 Database

MySQL 에서 외래키가 있으면 어떤 것들을 검증할까?

by iirin 2023. 9. 12.
  • 외래키 제약 조건은 관련 데이터의 일관성을 유지하는 데 사용합니다.

<aside>

💡 다른 종류의 제약 조건과 마찬가지로 데이터가 일치하지 않을 경우 데이터가 삽입되거나 업데이트되는 것을 방지할 수 있습니다. …… 또는 DML 작업이 수행될 때 제약 조건으로 인해 하위 행 의 데이터가 삭제되거나, 다른 값으로 변경되거나, 외래 키를 생성할 때 지정된 옵션 에 따라 null 로 설정될 수 있습니다. 참고링크

</aside>

외래키

  • 먼저 공식 문서를 읽어보면…
    • 외래 키는 관련 정보의 빠른 조회를 가능하게 하는 것 외에도 데이터가 삽입, 업데이트 및 삭제될 때 이러한 포인터가 무효화되는 것을 방지하여 참조 무결성을 강화하는 데 도움이 됩니다. 이 강제 메커니즘은 제약 조건 의 한 유형입니다.
    • 연결된 외래 키 값이 다른 테이블에 없으면 다른 테이블을 가리키는 행을 삽입할 수 없습니다.
    • 행이 삭제되거나 외래 키 값이 변경되고 다른 테이블의 행이 해당 외래 키 값을 가리키는 경우 삭제를 방지하도록 외래 키를 설정하여 다른 테이블의 해당 열 값이 null이 되도록 할 수 있거나 다른 테이블에서 해당 행을 자동으로 삭제합니다.
    • 정규화된 데이터베이스를 설계하는 단계 중 하나 는 조인 작업을 사용하여 중복된 데이터를 식별하고 해당 데이터를 새 테이블로 분리하고 외래 키 관계를 설정하여 여러 테이블을 단일 테이블처럼 쿼리할 수 있도록 하는 것입니다 .
  • 지금 있는 외래키를 확인할 수 있는 방법
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;

외래키 제약 조건

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option] -- Cascade, Set null, Set default, Restrict, No Action(default, Restrict와 동일)
    [ON UPDATE reference_option] -- Cascade, Set null, Set default

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
  • 상위 및 하위 테이블은 동일한 스토리지 엔진을 사용해야 합니다. 임시 테이블로 정의할 수 없습니다.
    • 임시테이블 : 단일 세션에서 여러 번 재사용할 수 있는 임시 결과 세트를 저장할 수 있는 특수한 유형의 테이블 참고링크

외래키 제약 조건의 생성

  • 외래 키 제약 조건을 만들려면
  • 외래 키와 참조 키의 해당 열은 유사한 데이터 유형을 가져야 합니다.
    • 문자열 유형의 길이는 같을 필요가 없습니다.
    • 이진이 아닌(문자) 문자열 열의 경우 문자 집합과 데이터 정렬이 동일해야 합니다.
  • 외래키와 인덱스
    • 기본 키 제약 조건과 달리 외래 키 제약 조건을 만들어도 해당 인덱스가 자동으로 생성되지 않습니다.
    • 쿼리 최적화를 위해 인덱스를 만드는 것을 추천합니다.
    • (MySQL은 자동 생성됩니다) MySQL은 외래 키 검사가 빠르고 테이블 스캔이 필요하지 않도록 외래 키 및 참조 키에 대한 인덱스가 필요합니다.
      • 참조 테이블에는 외래 키 열이 동일한 순서로 첫 번째 열로 나열되는 인덱스가 있어야 합니다.
      • 이러한 인덱스는 존재하지 않는 경우 참조하는 테이블에 자동으로 생성됩니다. 외래 키 제약 조건을 적용하는 데 사용할 수 있는 다른 인덱스를 만들면 나중에 이 인덱스가 자동으로 삭제될 수 있습니다.
  • 외래 키 관계의 테이블은 다른 스토리지 엔진을 사용하도록 변경할 수 없습니다. 스토리지 엔진을 변경하려면 먼저 외래 키 제약 조건을 삭제해야 합니다.
-- 생성할 때 외래키도 같이 생성
CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

-- 수정할 때 외래키 생성
ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]
  • 외래키 삭제
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

외래키 제약 조건 체크

  • MySQL에서 InnoDB는 외래키 제약 조건 검사를 지원합니다.
  • 기본적으로 활성화되어있는 FOREIGN_KEY_CHECKS 변수에 의해 제어됩니다.
SET FOREIGN_KEY_CHECKS=0; // 끄기
SET FOREIGN_KEY_CHECKS=1; // 켜기

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
  • 써보신 적이 있나요?
  • 보통은 이런 에러에서 자식 테이블 값을 수정하고 다시 제약 조건을 켜는 용도로 많이 사용합니다..
  • 혹은 데이터를 dump 할 때 비활성화합니다. mysqldump는 자동으로 외래키 제약조건을 끄는 쿼리가 포함되어 있습니다.
  • foreign_key_checks를 비활성화하면 외래 키 검사를 피할 수 있어 가져오기 작업 속도가 빨라집니다.
  • 주의사항
    • 1 로 값을 바꾸더라도 이전에 있었던 테이블 데이터에 대한 스캔을 실행하지 않습니다.
      • 즉, 0 으로 설정되어있을 때 추가된 테이블에 대해서도 검증하지 않습니다.
    • 외래키 제약 조건에 필요한 index 를 삭제하는 작업은 해당 설정의 설정값과 상관 없이 허용되지 않습니다.

외래키와 참조 무결성

  • 이 제약 조건은 기본 키 테이블의 데이터를 변경할 때 외래 키 테이블에 있는 데이터로의 연결이 무효화될 가능성이 있으면 그 데이터를 변경하지 못하도록 하여 참조 무결성을 강제 적용합니다.
    • 삭제되거나 변경되는 기본 키 값이 다른 테이블의 외래키로 참조되고 있다면, 동작이 수행되지 않을 수도 있습니다.
  • 이를 지키기 위해 참조하는 키가 삭제 혹은 업데이트 하려고 할 때 데이터베이스 엔진이 수행할 동작을 지정할 수 있습니다.
    • NO ACTION 부모 테이블에 행에 대한 삭제 또는 업데이트 동작이 롤백된다.
    • SET NULL 해당 외래키를 구성하는 모든 값이 null 이 된다.
    • CASCADE 삭제 전파. 만약 참조되는 키의 일부인 경우(다른 곳에서도 참조되고 있는 경우)에는 CASCADE를 지정할 수 없다. 부모 테이블에서 자식의 자식 테이블까지 계단식으로 작동할 수 있다. 다만, 계단식 외래키 작업으로 트리거가 실행되지는 않는다. 참고링크
    • SET DEFAULT 기본값으로 설정
    • RESTRICT 상위 테이블에 대한 삭제 또는 업데이트 작업을 아예 막는다.
create table if not exists chat_log
(
    id           bigint auto_increment
        primary key,
    contents     varchar(300) null,
    sender_id    bigint       not null,
    reciver_id   bigint       not null,
    chat_room_id bigint       not null,
    constraint fk_chat_log_member1
        foreign key (sender_id) references member (id),
    constraint fk_chat_log_member2
        foreign key (reciver_id) references member (id),
    constraint fk_chat_log_member_chat_about_item1
        foreign key (chat_room_id) references chatroom (id)
		**on delete cascade** // 삭제 전파
);

외래키 제약조건이 켜져있으면 성능 저하를 유발하는 것들

DDL

  • 전반적으로 데이터의 양이 더 많아지고 복잡해집니다.
  • 참조되는 릴레이션이 선행되어야 합니다. 이것은 DDL 뿐만 아니라 레코드를 생성, 수정할 때마다 순서를 신경써줘야합니다.

DML

INSERT, UPDATE, DELETE 모두 적용됩니다…

  • CASCADE 옵션이 있다면 관련된 작업들을 처리해야합니다.
    • 참조하는 릴레이션의 데이터가 모두 변경됩니다.
  • 데이터 정합성을 위해 외래키 제약 조건을 만족하는지 확인합니다.
  • MySQL은 상위 테이블에 일치하는 후보 키 값이 없으면 하위 테이블에 외래 키 값을 생성하려는 작업을 거부합니다.

Lock

  • MySQL은 필요에 따라 외래키 제약 조건과 관련된 테이블까지 잠금을 확장합니다.
    • DML 및 DDL 작업이 관련 테이블에서 동시에 실행되는 것을 방지하기 위해서입니다.
    • 부모 테이블의 메타데이터가 업데이트 될 때 외래키 메타데이터 업데이트까지 전파할 수 잇게 됩니다.
      • MySQL 8.0에서는 자식 테이블을 통해서도 외래키 메타데이터를 업데이트 할 수 있습니다.
  • 테이블이 LOCK_TABLES 로 명시적으로 잠길 때, 외래키 제약 조건에 의해 관련된 모든 테이블이 암시적으로 같이 잠깁니다.
    • 외래키 검사를 할 때 테이블에 대해 shared read-only lock이 걸립니다. LOCK_TABLES_READ 에 추가됩니다.
    • 계단식 업데이트를 할 때는 shared-nothing write lock이 걸립니다. LOCK_TABLES_WRTIE 에 추가됩니다.
  • 이렇게 같이 lock되기 때문에, 외래키로 성능 저하가 일어날 수 있습니다.

Refs.

MySQL :: MySQL 8.0 Reference Manual :: 13.1.20.5 FOREIGN KEY Constraints

MySQL :: MySQL 8.0 Reference Manual :: 8.3.4 Foreign Key Optimization

Can foreign keys hurt query performance

저작자표시 비영리 동일조건

관련글

티스토리툴바