Foreign key 인덱스와 잠금: 제약 조건이 쓰기 성능에 미치는 영향
InnoDB Foreign key의 인덱스 요구 조건, 참조 무결성 검사 잠금, cascade 비용과 운영 진단 절차를 쓰기 경로 중심으로 정리한다.
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는 관련 자식 행이 존재하는지 찾아야 한다.
flowchart LR
subgraph Child[자식 orders 쓰기]
C1[INSERT 또는<br/>customer_id UPDATE]
C2[자식 행과 인덱스 기록]
end
subgraph Parent[부모 customers 검사]
P1[참조 인덱스로<br/>부모 key 탐색]
P2[부모 record에<br/>참조 검사 잠금]
end
subgraph ParentWrite[부모 행 쓰기]
P3[DELETE 또는<br/>참조 key UPDATE]
C3[자식 인덱스로<br/>참조 행 탐색]
C4[RESTRICT 판정 또는<br/>CASCADE 작업]
end
C1 --> P1 --> P2 --> C2
P3 --> C3 --> C4
InnoDB는 Foreign key 검사를 위해 다음 인덱스 구조를 요구한다.
- 자식 쪽 인덱스: Foreign key 컬럼들이 인덱스의 첫 번째 컬럼부터 같은 순서로 배치되어야 한다.
- 부모 쪽 참조 인덱스: 참조되는 컬럼들이 인덱스의 첫 번째 컬럼부터 같은 순서로 배치되어야 한다. 가장 명확하고 이식성 높은 설계는
PRIMARY KEY또는UNIQUE NOT NULL을 참조하는 것이다. - 인덱스 prefix 불가:
VARCHAR(255)의 일부 길이만 색인하는col(20)같은 prefix index는 Foreign key 지원 인덱스로 사용할 수 없다. 이 때문에 index prefix만 가능한TEXT나BLOB컬럼은 Foreign key 컬럼으로 적합하지 않다. - 자료형 호환성: 정수형은 크기와 부호를 맞춰야 하며, 문자열형은 문자 집합과 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과 대기 관계를 관찰하는 기본 객체다.
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):
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에서 제약 조건 이름과 같은 자식 인덱스를 확인할 수 있다.
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):
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으로 갖는 복합 인덱스가 더 적합할 수 있다.
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로 서버 판정을 관찰한다. 이는 운영 코드에서 참조 오류를 무시하라는 권장이 아니다.
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):
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을 참조하는 자식 행을 트랜잭션 안에서 입력한 후, 관련 테이블의 잠금을 조회한다.
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):
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 상태를 먼저 확인해야 한다.
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):
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)
조회 결과가 생기면 다음 순서로 해석한다.
OBJECT_NAME과INDEX_NAME이 부모 참조 인덱스인지 자식 Foreign key 인덱스인지 확인한다.waiting_query만 보지 말고blocking_query와INNODB_TRX.trx_started를 함께 본다. 차단 세션은 현재 idle처럼 보여도 이전 문장 뒤 commit하지 않은 트랜잭션일 수 있다.LOCK_DATA가 특정 부모 key에 집중되는지 확인한다. 한 부모를 수많은 자식 쓰기가 참조하는 모델은 부모 변경 작업과 충돌하기 쉽다.- 즉시
KILL하기 전에 트랜잭션의 업무 중요도, rollback 예상량, 재시도 안전성을 확인한다. - 대기가 해소된 뒤에는 단일 blocker 처리로 끝내지 말고 트랜잭션 경계, Foreign key action, batch 크기와 인덱스 구조를 수정한다.
events_statements_current.SQL_TEXT는 해당 thread가 현재 문장을 실행 중일 때만 유용할 수 있다. statement history 설정이나 timing에 따라 NULL이 나올 수 있으므로, SQL text가 비어 있다는 이유만으로 blocker가 없다고 판단하면 안 된다.
두 세션 재현 절차
실제 대기를 재현하려면 별도의 MySQL client 두 개를 사용한다. 다음은 운영에서 실행할 명령이 아니라 격리된 테스트 환경용 절차다.
-- 세션 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에서 확인하고 작업 창과 중단 기준을 정해야 한다.
권장 순서는 다음과 같다.
- 부모 key와 자식 key의 자료형·부호·문자 집합·collation을 비교한다.
- 고아 행을 사전 탐지하고 업무 규칙에 따라 정리한다.
- 자식 쪽 기존 복합 인덱스가 Foreign key leading column 요구를 충족하는지 확인한다.
- 예상 DDL 알고리즘, 임시 공간, metadata lock 대기를 검증한다.
- 추가 직후 오류율, write latency, lock wait, deadlock, replica lag을 관찰한다.
- 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 -
RESTRICT,CASCADE,SET NULL
배포 전
-
foreign_key_checks=0
장애 분석 시
-
performance_schema.data_lock_waits -
OBJECT_NAME,INDEX_NAME,LOCK_DATA
10. 정리
Foreign key의 쓰기 비용은 하나의 숨은 세금이 아니라 두 방향의 인덱스 탐색과 참조를 보호하는 record lock에서 나온다. 자식 쓰기는 부모의 존재를 확인하고, 부모 변경은 자식의 존재를 확인하거나 연쇄 변경한다. 이 메커니즘을 이해하면 자동 생성 인덱스의 중복, hot parent와의 충돌, 대량 cascade, 긴 트랜잭션을 서로 다른 문제로 진단할 수 있다.
운영 설계의 목표는 제약 조건을 무조건 제거하는 것이 아니라 정합성 보장을 유지하면서 탐색 경로와 트랜잭션 범위를 통제하는 것이다. 다음 인덱스·잠금 주제에서는 InnoDB가 동일한 key 범위에서 record lock, gap lock, next-key lock을 어떻게 선택하는지 살펴보면 Foreign key 대기와 일반 범위 잠금의 차이를 더 정확히 이해할 수 있다.
DROP TABLE fk_orders;
DROP TABLE fk_customers;
실행 결과(MySQL 8.0.x):
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)