--- title: "Foreign key 인덱스와 잠금: 제약 조건이 쓰기 성능에 미치는 영향" description: "InnoDB Foreign key의 인덱스 요구 조건, 참조 무결성 검사 잠금, cascade 비용과 운영 진단 절차를 쓰기 경로 중심으로 정리한다." tags: [ MySQL, InnoDB, 인덱스, 락, 운영 ] image: "mysql-report-bg.png" published: "2026-07-25" updated: "2026-07-25" author: "MySQL 기술 노트" source_url: "" --- Foreign key는 애플리케이션 코드만으로는 완전히 보장하기 어려운 참조 무결성을 데이터베이스 경계에서 강제한다. 그러나 이 보장은 무료가 아니다. 자식 행을 입력하거나 참조 키를 변경할 때 InnoDB는 부모 키의 존재 여부를 확인해야 하고, 부모 행을 삭제하거나 키를 변경할 때는 참조 중인 자식 행이 있는지 검사해야 한다. 이 과정에는 양쪽 인덱스 탐색과 record lock이 필요하다. 따라서 Foreign key가 있는 시스템에서 쓰기 지연을 분석할 때는 “제약 조건이 있으니 느리다”는 결론보다 어떤 인덱스를 탐색했고, 어느 행을 잠갔으며, 어떤 트랜잭션과 충돌했는지를 구분해야 한다. 이 글은 MySQL 8.0 이상 InnoDB를 기준으로 Foreign key 인덱스의 구조, 참조 검사 잠금, `RESTRICT`와 `CASCADE`의 비용, 운영 진단과 변경 절차를 설명한다. ## 1. Foreign key는 두 방향의 탐색 경로를 요구한다 다음과 같은 관계를 생각해 보자. - 부모 테이블 `customers`의 `customer_id`가 참조 대상 키다. - 자식 테이블 `orders`의 `customer_id`가 Foreign key 컬럼이다. - 자식 `INSERT`는 부모 키가 존재하는지 찾아야 한다. - 부모 `DELETE` 또는 참조 키 `UPDATE`는 관련 자식 행이 존재하는지 찾아야 한다. ```mermaid flowchart LR subgraph Child[자식 orders 쓰기] C1[INSERT 또는
customer_id UPDATE] C2[자식 행과 인덱스 기록] end subgraph Parent[부모 customers 검사] P1[참조 인덱스로
부모 key 탐색] P2[부모 record에
참조 검사 잠금] end subgraph ParentWrite[부모 행 쓰기] P3[DELETE 또는
참조 key UPDATE] C3[자식 인덱스로
참조 행 탐색] C4[RESTRICT 판정 또는
CASCADE 작업] end C1 --> P1 --> P2 --> C2 P3 --> C3 --> C4 ``` InnoDB는 Foreign key 검사를 위해 다음 인덱스 구조를 요구한다. 1. **자식 쪽 인덱스**: Foreign key 컬럼들이 인덱스의 첫 번째 컬럼부터 같은 순서로 배치되어야 한다. 2. **부모 쪽 참조 인덱스**: 참조되는 컬럼들이 인덱스의 첫 번째 컬럼부터 같은 순서로 배치되어야 한다. 가장 명확하고 이식성 높은 설계는 `PRIMARY KEY` 또는 `UNIQUE NOT NULL`을 참조하는 것이다. 3. **인덱스 prefix 불가**: `VARCHAR(255)`의 일부 길이만 색인하는 `col(20)` 같은 prefix index는 Foreign key 지원 인덱스로 사용할 수 없다. 이 때문에 index prefix만 가능한 `TEXT`나 `BLOB` 컬럼은 Foreign key 컬럼으로 적합하지 않다. 4. **자료형 호환성**: 정수형은 크기와 부호를 맞춰야 하며, 문자열형은 문자 집합과 collation까지 의도적으로 일치시켜야 한다. 자식 쪽에 적합한 인덱스가 없으면 InnoDB가 Foreign key 생성 시 자동으로 인덱스를 만든다. 자동 생성은 DDL 실패를 줄여 주지만 좋은 물리 설계를 대신하지 않는다. 자동 인덱스는 대개 Foreign key 컬럼만 포함하므로 실제 조회의 정렬·범위 조건이나 covering 요구를 충족하지 못할 수 있다. 반대로 같은 leading column을 가진 복합 인덱스를 먼저 설계하면 참조 검사와 업무 조회가 하나의 인덱스를 공유할 수 있다. 복합 Foreign key의 컬럼 순서도 중요하다. `(tenant_id, account_id)`를 참조하는 제약 조건에는 `(tenant_id, account_id, created_at)` 인덱스가 대응할 수 있지만 `(account_id, tenant_id)`는 대응하지 못한다. 이는 복합 인덱스의 leftmost-prefix 규칙과 같다. ## 2. 자동 생성되는 자식 인덱스 확인 먼저 검증 환경과 잠금 진단 객체를 확인한다. `performance_schema.data_locks`와 `data_lock_waits`는 MySQL 8.0에서 InnoDB record/table lock과 대기 관계를 관찰하는 기본 객체다. ```sql SELECT VERSION() AS mysql_version, @@foreign_key_checks AS foreign_key_checks, @@transaction_isolation AS transaction_isolation; SELECT ENGINE, SUPPORT FROM information_schema.ENGINES WHERE ENGINE = 'InnoDB'; SHOW TABLES FROM performance_schema LIKE 'data_lock%'; ``` 실행 결과(MySQL 8.0.x): ```text mysql> SELECT VERSION() AS mysql_version, -> @@foreign_key_checks AS foreign_key_checks, -> @@transaction_isolation AS transaction_isolation; +---------------+--------------------+-----------------------+ | mysql_version | foreign_key_checks | transaction_isolation | +---------------+--------------------+-----------------------+ | 8.0.46 | 1 | REPEATABLE-READ | +---------------+--------------------+-----------------------+ 1 row in set (0.00 sec) mysql> SELECT ENGINE, SUPPORT -> FROM information_schema.ENGINES -> WHERE ENGINE = 'InnoDB'; +--------+---------+ | ENGINE | SUPPORT | +--------+---------+ | InnoDB | DEFAULT | +--------+---------+ 1 row in set (0.00 sec) mysql> SHOW TABLES FROM performance_schema LIKE 'data_lock%'; +-------------------------------------------+ | Tables_in_performance_schema (data_lock%) | +-------------------------------------------+ | data_lock_waits | | data_locks | +-------------------------------------------+ 2 rows in set (0.00 sec) ``` 다음 DDL은 자식 테이블에 `customer_id` 인덱스를 명시하지 않는다. 그럼에도 Foreign key 생성은 성공하며, `information_schema.STATISTICS`에서 제약 조건 이름과 같은 자식 인덱스를 확인할 수 있다. ```sql DROP TABLE IF EXISTS fk_orders; DROP TABLE IF EXISTS fk_customers; CREATE TABLE fk_customers ( customer_id BIGINT UNSIGNED NOT NULL, customer_name VARCHAR(100) NOT NULL, customer_status ENUM('active', 'inactive') NOT NULL DEFAULT 'active', PRIMARY KEY (customer_id) ) ENGINE = InnoDB; CREATE TABLE fk_orders ( order_id BIGINT UNSIGNED NOT NULL, customer_id BIGINT UNSIGNED NOT NULL, ordered_at DATETIME NOT NULL, amount DECIMAL(12,2) NOT NULL, PRIMARY KEY (order_id), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES fk_customers (customer_id) ON UPDATE RESTRICT ON DELETE RESTRICT ) ENGINE = InnoDB; SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, SEQ_IN_INDEX, COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('fk_customers', 'fk_orders') ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX; SELECT CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE, TABLE_NAME, REFERENCED_TABLE_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME = 'fk_orders_customer'; ``` 실행 결과(MySQL 8.0.x): ```text mysql> CREATE TABLE fk_customers (... PRIMARY KEY (customer_id)) ENGINE = InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE fk_orders (... CONSTRAINT fk_orders_customer -> FOREIGN KEY (customer_id) REFERENCES fk_customers (customer_id) -> ON UPDATE RESTRICT ON DELETE RESTRICT) ENGINE = InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, SEQ_IN_INDEX, COLUMN_NAME -> FROM information_schema.STATISTICS ...; +--------------+--------------------+------------+--------------+-------------+ | TABLE_NAME | INDEX_NAME | NON_UNIQUE | SEQ_IN_INDEX | COLUMN_NAME | +--------------+--------------------+------------+--------------+-------------+ | fk_customers | PRIMARY | 0 | 1 | customer_id | | fk_orders | fk_orders_customer | 1 | 1 | customer_id | | fk_orders | PRIMARY | 0 | 1 | order_id | +--------------+--------------------+------------+--------------+-------------+ 3 rows in set (0.00 sec) mysql> SELECT CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE, -> TABLE_NAME, REFERENCED_TABLE_NAME -> FROM information_schema.REFERENTIAL_CONSTRAINTS ...; +--------------------+-------------+-------------+------------+-----------------------+ | CONSTRAINT_NAME | UPDATE_RULE | DELETE_RULE | TABLE_NAME | REFERENCED_TABLE_NAME | +--------------------+-------------+-------------+------------+-----------------------+ | fk_orders_customer | RESTRICT | RESTRICT | fk_orders | fk_customers | +--------------------+-------------+-------------+------------+-----------------------+ 1 row in set (0.00 sec) ``` 긴 `CREATE TABLE` 정의와 반복되는 `WHERE` 절은 위 실행 결과에서 줄였으며, 인덱스·제약 조건 조회 결과는 검증된 출력 그대로 제시했다. 여기서 자동 생성된 `fk_orders_customer(customer_id)`는 제약 조건 검사에 필요한 최소 구조다. 실제 서비스 쿼리가 고객별 주문을 시간 역순으로 조회한다면 다음처럼 `(customer_id, ordered_at)`을 leading column으로 갖는 복합 인덱스가 더 적합할 수 있다. ```text ALTER TABLE orders ADD INDEX ix_orders_customer_time (customer_id, ordered_at DESC); ``` 위 문장은 운영 테이블 이름을 사용한 설계 예시이므로 검증용 SQL이 아니라 `text`로 표시했다. 실제 변경 전에는 기존 자동 인덱스가 새 인덱스와 중복되는지, 새 인덱스가 Foreign key 지원 인덱스로 인정되는지, 쿼리의 projection과 정렬까지 충족하는지를 별도로 확인해야 한다. 자동 인덱스를 제거하려면 먼저 대체 인덱스가 확실히 존재해야 하며, DDL은 staging에서 같은 MySQL minor version으로 검증하는 편이 안전하다. ## 3. 참조 무결성 검사와 record lock Foreign key 검사에는 단순 조회와 다른 잠금 의미가 있다. InnoDB는 다른 트랜잭션이 검사 직후 부모 행을 삭제하여 참조가 깨지는 일을 막아야 한다. 따라서 자식 행을 입력할 때 참조한 부모 record에 shared 성격의 record lock을 획득하고, 트랜잭션이 끝날 때까지 참조의 유효성을 보호한다. 일반적인 잠금 관계는 다음과 같다. | 쓰기 작업 | 검사 대상 | 대표적인 충돌 | |---|---|---| | 자식 `INSERT` | 부모 참조 key 존재 여부 | 같은 부모를 `DELETE`하거나 참조 key를 변경하는 작업 | | 자식 Foreign key `UPDATE` | 새 부모 key 존재 여부 | 새 부모의 `DELETE`·key 변경 작업 | | 부모 `DELETE ... RESTRICT` | 자식 참조 행 존재 여부 | 미완료 자식 쓰기, 이미 존재하는 참조 행 | | 부모 key `UPDATE ... RESTRICT` | 자식 참조 행 존재 여부 | 미완료 자식 쓰기, 이미 존재하는 참조 행 | | 부모 `DELETE/UPDATE ... CASCADE` | 관련 자식 행 탐색과 변경 | 자식 행을 갱신 중인 트랜잭션, 광범위한 연쇄 잠금 | 여러 자식 트랜잭션이 같은 부모를 참조하며 얻는 shared 잠금끼리는 보통 서로 충돌하지 않는다. 문제가 되는 지점은 부모를 삭제·변경하려는 exclusive 잠금과 만날 때다. 예를 들어 주문 생성이 계속되는 “미삭제 고객” 행을 정리 작업이 동시에 삭제하려 하면, 부모 삭제와 자식 입력 가운데 한쪽이 기다리게 된다. 다음 예제는 정상 참조 입력과 위반 동작을 확인한다. 의도적인 오류가 검증 세션을 중단하지 않도록 `INSERT IGNORE`, `DELETE IGNORE`를 사용하고 `SHOW WARNINGS`로 서버 판정을 관찰한다. 이는 운영 코드에서 참조 오류를 무시하라는 권장이 아니다. ```sql INSERT INTO fk_customers (customer_id, customer_name) VALUES (100, '고객-100'), (200, '고객-200'); INSERT INTO fk_orders (order_id, customer_id, ordered_at, amount) VALUES (1001, 100, '2026-07-25 09:00:00', 15000.00), (1002, 100, '2026-07-25 09:05:00', 23000.00); INSERT IGNORE INTO fk_orders (order_id, customer_id, ordered_at, amount) VALUES (1003, 999, '2026-07-25 09:10:00', 9000.00); SHOW WARNINGS; DELETE IGNORE FROM fk_customers WHERE customer_id = 100; SHOW WARNINGS; SELECT (SELECT COUNT(*) FROM fk_customers) AS parent_rows, (SELECT COUNT(*) FROM fk_orders) AS child_rows, (SELECT COUNT(*) FROM fk_orders WHERE order_id = 1003) AS orphan_rows; ``` 실행 결과(MySQL 8.0.x): ```text mysql> INSERT INTO fk_customers (customer_id, customer_name) -> VALUES (100, '고객-100'), (200, '고객-200'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT INTO fk_orders (...) VALUES (...), (...); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT IGNORE INTO fk_orders (...) VALUES (1003, 999, ...); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1452 | Cannot add or update a child row: ... | +---------+------+-------------------------------------------+ 1 row in set (0.00 sec) mysql> DELETE IGNORE FROM fk_customers WHERE customer_id = 100; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+-------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------+ | Warning | 1451 | Cannot delete or update a parent row: ... | +---------+------+-------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT (SELECT COUNT(*) FROM fk_customers) AS parent_rows, -> (SELECT COUNT(*) FROM fk_orders) AS child_rows, -> (SELECT COUNT(*) FROM fk_orders WHERE order_id = 1003) AS orphan_rows; +-------------+------------+-------------+ | parent_rows | child_rows | orphan_rows | +-------------+------------+-------------+ | 2 | 2 | 0 | +-------------+------------+-------------+ 1 row in set (0.01 sec) ``` 긴 Foreign key 식별 문자열과 반복되는 `INSERT` 값은 실행 결과에서 줄였으며, 오류 코드와 정합성 확인 결과는 검증된 출력과 일치한다. 존재하지 않는 부모 `999`를 참조한 주문은 저장되지 않는다. 자식 주문이 남아 있는 고객 `100`의 삭제도 `RESTRICT` 규칙에 따라 거부된다. 중요한 점은 이 판정이 애플리케이션의 사전 `SELECT`가 아니라 같은 쓰기 작업 안에서 원자적으로 수행된다는 것이다. ## 4. 한 트랜잭션에서 Foreign key 검사 잠금 관찰 동시 세션 없이도 `performance_schema.data_locks`에서 현재 트랜잭션이 보유한 잠금의 방향을 확인할 수 있다. 다음 예제는 이미 커밋된 부모 `200`을 참조하는 자식 행을 트랜잭션 안에서 입력한 후, 관련 테이블의 잠금을 조회한다. ```sql START TRANSACTION; INSERT INTO fk_orders (order_id, customer_id, ordered_at, amount) VALUES (2001, 200, '2026-07-25 09:20:00', 45000.00); SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks WHERE OBJECT_SCHEMA = DATABASE() AND OBJECT_NAME IN ('fk_customers', 'fk_orders') ORDER BY OBJECT_NAME, LOCK_TYPE, INDEX_NAME, LOCK_DATA; ROLLBACK; ``` 실행 결과(MySQL 8.0.x): ```text mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO fk_orders (order_id, customer_id, ordered_at, amount) -> VALUES (2001, 200, '2026-07-25 09:20:00', 45000.00); Query OK, 1 row affected (0.00 sec) mysql> SELECT OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA -> FROM performance_schema.data_locks -> WHERE OBJECT_SCHEMA = DATABASE() -> AND OBJECT_NAME IN ('fk_customers', 'fk_orders') -> ORDER BY OBJECT_NAME, LOCK_TYPE, INDEX_NAME, LOCK_DATA; +--------------+------------+-----------+---------------+-------------+-----------+ | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------------+------------+-----------+---------------+-------------+-----------+ | fk_customers | PRIMARY | RECORD | S,REC_NOT_GAP | GRANTED | 200 | | fk_customers | NULL | TABLE | IS | GRANTED | NULL | | fk_orders | NULL | TABLE | IX | GRANTED | NULL | +--------------+------------+-----------+---------------+-------------+-----------+ 3 rows in set (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.00 sec) ``` 출력에서 자식 테이블에는 `IX` 의도 잠금이, 부모 `PRIMARY` record에는 참조 검사를 위한 `S,REC_NOT_GAP` 잠금이 나타난다. 새로 삽입한 자식 record의 implicit lock은 다른 트랜잭션과 충돌하지 않는 동안 `data_locks`에 별도 행으로 나타나지 않을 수 있다. `LOCK_MODE` 문자열의 세부 조합과 `LOCK_DATA` 표시는 MySQL minor version, 격리 수준, 계측 상태에 따라 달라질 수 있다. 따라서 운영에서는 문자열 하나를 고정된 정답으로 외우기보다 다음을 함께 읽어야 한다. - `OBJECT_NAME`: 어느 테이블에서 충돌했는가 - `INDEX_NAME`: 어떤 인덱스 탐색 경로의 record인가 - `LOCK_TYPE`: `TABLE`인지 `RECORD`인지 - `LOCK_MODE`: shared/exclusive와 gap 성격이 무엇인가 - `LOCK_STATUS`: 획득한 잠금인지 대기 중인 잠금인지 - `LOCK_DATA`: 어떤 key 값 또는 page/record를 가리키는가 `ROLLBACK` 뒤에는 입력한 주문 `2001`과 그 트랜잭션의 잠금이 사라진다. 이 예제의 목적은 장시간 대기를 인위적으로 만드는 것이 아니라 Foreign key 검사가 부모 인덱스 record 잠금으로 이어진다는 점을 확인하는 데 있다. ## 5. 실제 잠금 대기에서 blocker 찾기 운영 장애에서는 `data_locks` 전체를 덤프하기보다 `data_lock_waits`를 중심으로 대기 트랜잭션과 차단 트랜잭션을 연결한다. 다음 쿼리는 현재 발생한 InnoDB data lock 대기를 보여 준다. 테스트 컨테이너에는 동시 대기가 없으므로 `Empty set`이 정상이며, 실제 환경에서는 필요한 Performance Schema 권한과 instrumentation 상태를 먼저 확인해야 한다. ```sql SELECT r.trx_id AS waiting_trx_id, rt.PROCESSLIST_ID AS waiting_thread, bs.SQL_TEXT AS waiting_query, b.trx_id AS blocking_trx_id, bt.PROCESSLIST_ID AS blocking_thread, es.SQL_TEXT AS blocking_query, rl.OBJECT_SCHEMA, rl.OBJECT_NAME, rl.INDEX_NAME, rl.LOCK_MODE AS waiting_lock_mode, bl.LOCK_MODE AS blocking_lock_mode, rl.LOCK_DATA FROM performance_schema.data_lock_waits w JOIN information_schema.INNODB_TRX r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID JOIN information_schema.INNODB_TRX b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID JOIN performance_schema.data_locks rl ON rl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID AND rl.ENGINE = w.ENGINE JOIN performance_schema.data_locks bl ON bl.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID AND bl.ENGINE = w.ENGINE LEFT JOIN performance_schema.threads rt ON rt.THREAD_ID = w.REQUESTING_THREAD_ID LEFT JOIN performance_schema.threads bt ON bt.THREAD_ID = w.BLOCKING_THREAD_ID LEFT JOIN performance_schema.events_statements_current bs ON bs.THREAD_ID = w.REQUESTING_THREAD_ID LEFT JOIN performance_schema.events_statements_current es ON es.THREAD_ID = w.BLOCKING_THREAD_ID ORDER BY r.trx_started; ``` 실행 결과(MySQL 8.0.x): ```text mysql> SELECT r.trx_id AS waiting_trx_id, -> rt.PROCESSLIST_ID AS waiting_thread, -> bs.SQL_TEXT AS waiting_query, -> b.trx_id AS blocking_trx_id, -> bt.PROCESSLIST_ID AS blocking_thread, -> es.SQL_TEXT AS blocking_query, -> rl.OBJECT_SCHEMA, -> rl.OBJECT_NAME, -> rl.INDEX_NAME, -> rl.LOCK_MODE AS waiting_lock_mode, -> bl.LOCK_MODE AS blocking_lock_mode, -> rl.LOCK_DATA -> FROM performance_schema.data_lock_waits w -> JOIN information_schema.INNODB_TRX r -> ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID -> JOIN information_schema.INNODB_TRX b -> ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID -> JOIN performance_schema.data_locks rl -> ON rl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID -> AND rl.ENGINE = w.ENGINE -> JOIN performance_schema.data_locks bl -> ON bl.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID -> AND bl.ENGINE = w.ENGINE -> LEFT JOIN performance_schema.threads rt -> ON rt.THREAD_ID = w.REQUESTING_THREAD_ID -> LEFT JOIN performance_schema.threads bt -> ON bt.THREAD_ID = w.BLOCKING_THREAD_ID -> LEFT JOIN performance_schema.events_statements_current bs -> ON bs.THREAD_ID = w.REQUESTING_THREAD_ID -> LEFT JOIN performance_schema.events_statements_current es -> ON es.THREAD_ID = w.BLOCKING_THREAD_ID -> ORDER BY r.trx_started; Empty set (0.00 sec) ``` 조회 결과가 생기면 다음 순서로 해석한다. 1. `OBJECT_NAME`과 `INDEX_NAME`이 부모 참조 인덱스인지 자식 Foreign key 인덱스인지 확인한다. 2. `waiting_query`만 보지 말고 `blocking_query`와 `INNODB_TRX.trx_started`를 함께 본다. 차단 세션은 현재 idle처럼 보여도 이전 문장 뒤 commit하지 않은 트랜잭션일 수 있다. 3. `LOCK_DATA`가 특정 부모 key에 집중되는지 확인한다. 한 부모를 수많은 자식 쓰기가 참조하는 모델은 부모 변경 작업과 충돌하기 쉽다. 4. 즉시 `KILL`하기 전에 트랜잭션의 업무 중요도, rollback 예상량, 재시도 안전성을 확인한다. 5. 대기가 해소된 뒤에는 단일 blocker 처리로 끝내지 말고 트랜잭션 경계, Foreign key action, batch 크기와 인덱스 구조를 수정한다. `events_statements_current.SQL_TEXT`는 해당 thread가 현재 문장을 실행 중일 때만 유용할 수 있다. statement history 설정이나 timing에 따라 `NULL`이 나올 수 있으므로, SQL text가 비어 있다는 이유만으로 blocker가 없다고 판단하면 안 된다. ### 두 세션 재현 절차 실제 대기를 재현하려면 별도의 MySQL client 두 개를 사용한다. 다음은 운영에서 실행할 명령이 아니라 격리된 테스트 환경용 절차다. ```text -- 세션 A START TRANSACTION; DELETE FROM fk_customers WHERE customer_id = 200; -- commit하지 않고 유지 -- 세션 B SET SESSION innodb_lock_wait_timeout = 10; INSERT INTO fk_orders(order_id, customer_id, ordered_at, amount) VALUES (2002, 200, NOW(), 1000.00); -- 세션 A의 부모 record 변경과 충돌하여 대기 -- 세션 C 또는 별도 진단 연결 -- 위 data_lock_waits 진단 쿼리 실행 -- 정리 ROLLBACK; -- 세션 A ROLLBACK; -- 세션 B가 timeout 전 해제되어 입력됐다면 필요 ``` 재현 순서는 스케줄링에 민감하므로 세션 A가 `DELETE`를 실행한 뒤 세션 B를 시작해야 한다. 실제 서비스에서 이런 실험을 수행하면 업무 트랜잭션을 막을 수 있으므로 전용 테스트 인스턴스에서만 실행한다. ## 6. Foreign key가 쓰기 성능에 주는 비용 ### 6.1 자식 쓰기마다 부모 인덱스를 읽는다 자식 `INSERT`와 Foreign key 컬럼 `UPDATE`에는 부모 참조 인덱스 탐색이 추가된다. 부모 page가 Buffer Pool에 없으면 I/O가 발생할 수 있고, 참조 key가 넓으면 양쪽 인덱스의 page 밀도가 낮아진다. 그러나 이 비용만 보고 제약 조건을 제거하면 데이터 정합성 검사가 애플리케이션의 모든 쓰기 경로로 분산된다. 보통은 먼저 batch 크기, 부모 key 폭, 불필요한 자식 secondary index, 트랜잭션 길이를 개선하는 편이 낫다. ### 6.2 자식 인덱스 유지 비용이 추가된다 Foreign key 지원 인덱스도 일반 secondary index다. 자식 행을 입력·삭제하거나 Foreign key 값을 바꾸면 해당 B+Tree도 갱신되고 redo log와 Buffer Pool dirty page가 늘어난다. 자동 생성 인덱스와 업무용 복합 인덱스가 사실상 중복이면 같은 leading key를 두 번 유지할 수 있다. 다만 “첫 컬럼이 같으니 무조건 중복”이라고 단정하면 안 된다. 다음 요소를 함께 비교해야 한다. - 컬럼 순서와 길이 - 정렬 방향 - `UNIQUE` 여부 - covering에 필요한 후행 컬럼 - 실제 쿼리의 filter·join·order 요구 - 긴 복합 인덱스로 대체했을 때 증가하는 page와 write amplification ### 6.3 `RESTRICT`는 실패하더라도 검사 비용이 있다 `ON DELETE RESTRICT`와 `ON UPDATE RESTRICT`는 참조 행이 있으면 부모 변경을 거부한다. 실패하는 문장도 자식 인덱스를 탐색하고 잠금을 획득할 수 있다. 대규모 정리 batch가 참조 행을 계속 만나 실패하면 “아무것도 삭제되지 않았는데”도 I/O와 lock wait가 누적될 수 있다. ### 6.4 `CASCADE`는 작은 부모 변경을 큰 자식 쓰기로 확대한다 `ON DELETE CASCADE`는 애플리케이션 코드를 줄여 주지만, 한 부모 삭제가 수천·수백만 자식 행 삭제로 확장될 수 있다. 연쇄 작업은 자식 row lock, secondary index 갱신, undo/redo, 복제 적용량, rollback 비용을 함께 키운다. `ON UPDATE CASCADE`도 참조 key 변경 범위만큼 자식 인덱스를 갱신한다. 대량 cascade가 필요한 모델이라면 다음을 검토한다. - 한 트랜잭션에서 삭제할 부모와 자식 수의 상한 - 업무적으로 soft delete가 가능한지 - 자식을 명시적 작은 batch로 먼저 정리할 수 있는지 - 장애 시 재시작 가능한 idempotent 작업인지 - binlog 기반 replica의 적용 지연과 Aurora reader 지연을 함께 감시하는지 ### 6.5 긴 트랜잭션이 잠금 수명을 늘린다 Foreign key 검사 잠금은 문장 종료만이 아니라 트랜잭션 경계의 영향을 받는다. 애플리케이션이 자식 입력 뒤 외부 API를 호출하거나 사용자 응답을 기다린 채 commit하지 않으면 부모 변경을 불필요하게 오래 막을 수 있다. 연결 pool의 autocommit 설정과 예외 경로의 rollback 누락도 함께 점검해야 한다. ## 7. 흔한 오해와 위험한 우회 ### “부모가 존재하는지 먼저 SELECT하면 Foreign key가 필요 없다” 사전 조회와 자식 입력 사이에 다른 트랜잭션이 부모를 삭제할 수 있다. 잠금과 격리 수준까지 정확히 설계하지 않은 애플리케이션 검사는 원자적 보장이 아니다. 또한 batch, 관리 도구, 데이터 이관처럼 다른 쓰기 경로가 검사를 빠뜨릴 수 있다. ### “`foreign_key_checks=0`이면 대량 적재가 안전하게 빨라진다” `foreign_key_checks`를 끄면 검사를 우회할 수 있지만 다시 `1`로 바꾼다고 기존 행 전체를 소급 검증하지 않는다. 따라서 고아 행이 남을 수 있다. 이 설정은 복구·이관 절차에서 데이터 순서와 사후 검증을 통제할 때 제한적으로 사용해야 하며, 일반적인 성능 튜닝 스위치로 사용해서는 안 된다. ### “Foreign key 인덱스는 조회에도 항상 최적이다” 자동 생성 인덱스는 제약 조건을 유지하기 위한 최소 인덱스다. 날짜 범위, 상태 조건, 정렬, covering 요구를 자동으로 해결하지 않는다. 반대로 조회용 인덱스를 무작정 추가하면 쓰기 비용만 증가한다. 실제 실행 계획과 인덱스 사용 통계를 함께 검토해야 한다. ### “`CASCADE`면 애플리케이션에서 삭제 순서를 생각하지 않아도 된다” 정합성 규칙은 간단해지지만 작업량과 잠금 범위가 사라지는 것은 아니다. 대량 삭제의 최대 fan-out, 실패 시 rollback 시간, replica 지연을 사전에 측정해야 한다. 연쇄 참조가 깊을수록 장애 원인과 영향 범위를 추적하기도 어려워진다. ### “Deadlock은 Foreign key 결함이다” Deadlock은 대개 여러 테이블과 부모 key를 서로 다른 순서로 접근할 때 발생한다. 예를 들어 트랜잭션 A는 부모 10의 자식을 쓴 뒤 부모 20을 변경하고, 트랜잭션 B는 반대 순서로 접근할 수 있다. 제약 조건을 제거하기보다 key 접근 순서를 통일하고 트랜잭션을 짧게 유지하며, deadlock을 정상적인 동시성 제어 결과로 재시도 가능하게 처리해야 한다. ## 8. 운영 변경과 Aurora MySQL 해석 기존 대형 테이블에 Foreign key를 추가하는 작업은 단순 메타데이터 변경으로 가정하면 안 된다. 기존 데이터가 제약을 만족하는지 확인해야 하고, 필요한 인덱스 생성과 metadata lock이 애플리케이션 DDL/DML에 영향을 줄 수 있다. 대상 버전, 테이블 크기, online DDL 지원 범위를 staging에서 확인하고 작업 창과 중단 기준을 정해야 한다. 권장 순서는 다음과 같다. 1. 부모 key와 자식 key의 자료형·부호·문자 집합·collation을 비교한다. 2. 고아 행을 사전 탐지하고 업무 규칙에 따라 정리한다. 3. 자식 쪽 기존 복합 인덱스가 Foreign key leading column 요구를 충족하는지 확인한다. 4. 예상 DDL 알고리즘, 임시 공간, metadata lock 대기를 검증한다. 5. 추가 직후 오류율, write latency, lock wait, deadlock, replica lag을 관찰한다. 6. rollback은 제약 조건 삭제뿐 아니라 새로 생성한 인덱스의 유지 여부까지 포함해 준비한다. Aurora MySQL도 MySQL 호환 InnoDB 계층에서 Foreign key 검사와 record lock 의미를 유지한다. 분산 스토리지는 참조 검사, 자식 secondary index 유지, 긴 트랜잭션, cascade의 논리적 비용을 제거하지 않는다. 쓰기는 writer instance에서 처리되므로 writer의 lock wait와 commit latency를 우선 확인하고, 큰 cascade나 batch가 reader lag과 failover 시간에 주는 영향도 관찰해야 한다. 또한 Aurora Global Database나 서로 다른 cluster 사이에는 일반 Foreign key가 참조 무결성을 보장하지 않는다. Foreign key는 같은 데이터베이스 엔진 안의 테이블 관계를 위한 제약이며, 서비스·cluster 경계를 넘는 정합성은 별도의 이벤트, 보상, 검증 설계가 필요하다. Aurora 버전별 DDL 지원과 Performance Schema 노출 범위는 해당 Aurora MySQL release와 parameter group에서 다시 확인한다. ## 9. DBA 점검표 ### 설계 시 - [ ] 부모 참조 key는 가능하면 `PRIMARY KEY` 또는 `UNIQUE NOT NULL`인가? - [ ] 부모와 자식 컬럼의 자료형, 정수 부호, 문자 집합, collation이 호환되는가? - [ ] 복합 Foreign key의 컬럼 순서가 양쪽 인덱스의 leading column과 일치하는가? - [ ] 자동 생성 자식 인덱스 대신 실제 조회까지 지원하는 복합 인덱스를 설계할 필요가 있는가? - [ ] `RESTRICT`, `CASCADE`, `SET NULL` 중 업무 생명주기에 맞는 action을 선택했는가? - [ ] 최대 자식 fan-out과 한 번의 부모 변경이 만드는 쓰기량을 추정했는가? ### 배포 전 - [ ] 고아 행 사전 검사와 정리 결과를 기록했는가? - [ ] 같은 MySQL/Aurora major·minor 계열에서 DDL과 rollback을 검증했는가? - [ ] 인덱스 생성 공간, DDL 시간, metadata lock 대기 한도를 정했는가? - [ ] `foreign_key_checks=0`을 일상적인 우회책으로 사용하지 않는가? - [ ] batch 크기와 트랜잭션별 row 수에 상한이 있는가? ### 장애 분석 시 - [ ] `performance_schema.data_lock_waits`에서 waiting/blocking thread를 연결했는가? - [ ] `OBJECT_NAME`, `INDEX_NAME`, `LOCK_DATA`로 부모·자식 어느 방향의 검사인지 확인했는가? - [ ] blocker의 현재 문장뿐 아니라 트랜잭션 시작 시각과 미커밋 이전 작업을 확인했는가? - [ ] cascade fan-out, 긴 트랜잭션, 접근 순서 불일치를 함께 검토했는가? - [ ] 세션 종료 전에 rollback 비용과 업무 재시도 안전성을 판단했는가? - [ ] 조치 후 write latency, deadlock, replica/Aurora reader lag이 정상화됐는가? ## 10. 정리 Foreign key의 쓰기 비용은 하나의 숨은 세금이 아니라 두 방향의 인덱스 탐색과 참조를 보호하는 record lock에서 나온다. 자식 쓰기는 부모의 존재를 확인하고, 부모 변경은 자식의 존재를 확인하거나 연쇄 변경한다. 이 메커니즘을 이해하면 자동 생성 인덱스의 중복, hot parent와의 충돌, 대량 cascade, 긴 트랜잭션을 서로 다른 문제로 진단할 수 있다. 운영 설계의 목표는 제약 조건을 무조건 제거하는 것이 아니라 정합성 보장을 유지하면서 탐색 경로와 트랜잭션 범위를 통제하는 것이다. 다음 인덱스·잠금 주제에서는 InnoDB가 동일한 key 범위에서 record lock, gap lock, next-key lock을 어떻게 선택하는지 살펴보면 Foreign key 대기와 일반 범위 잠금의 차이를 더 정확히 이해할 수 있다. ```sql DROP TABLE fk_orders; DROP TABLE fk_customers; ``` 실행 결과(MySQL 8.0.x): ```text mysql> DROP TABLE fk_orders; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE fk_customers; Query OK, 0 rows affected (0.00 sec) ```