Unique index와 NULL 처리: 정합성 보장과 예외 패턴
MySQL Unique index에서 NULL이 중복으로 허용되는 원리와 복합 키·조건부 유일성·정규화 키 설계 패턴을 운영 관점에서 정리한다.
Unique index는 단순히 “중복을 막는 인덱스”가 아니다. MySQL에서 NULL은 알려지지 않은 값을 뜻하며 다른 NULL과 같다고 판정되지 않는다. 따라서 nullable 컬럼에 만든 Unique index는 여러 개의 NULL을 허용한다. 이 동작을 모르고 이메일, 외부 식별자, 활성 상태 같은 업무 규칙을 Unique index 하나에 맡기면 애플리케이션의 기대와 데이터베이스가 실제로 보장하는 범위가 달라질 수 있다.
반대로 이 특성을 의도적으로 이용하면 “값이 있을 때만 유일해야 한다”, “활성 행은 하나만 허용하되 이력 행은 여러 개 보관한다”와 같은 조건부 유일성을 간결하게 구현할 수 있다. 이 글은 MySQL 8.0 이상을 기준으로 Unique index의 비교 의미, 복합 Unique index에서의 NULL, generated column을 이용한 예외 패턴, 운영 중 변경 절차를 차례로 설명한다.
1. Unique index가 실제로 보장하는 것
SQL의 3-valued logic에서 비교 결과는 TRUE, FALSE, UNKNOWN 가운데 하나다. NULL = NULL의 결과는 TRUE가 아니라 UNKNOWN이다. MySQL의 Unique index도 이 의미를 반영하여, 인덱스 키를 구성하는 컬럼에 NULL이 포함된 행끼리는 중복 키로 취급하지 않는다.
flowchart TD
A[행 INSERT 또는 키 UPDATE] --> B{Unique key 컬럼에<br/>NULL이 있는가?}
B -- 아니요 --> C[동일한 non-NULL key 탐색]
C --> D{기존 key 존재?}
D -- 예 --> E[Duplicate entry 오류]
D -- 아니요 --> F[행과 인덱스 엔트리 기록]
B -- 예 --> G[NULL을 포함한 key는<br/>다른 NULL key와 동일 판정하지 않음]
G --> F
이 규칙에서 구분해야 할 사항은 다음과 같다.
PRIMARY KEY컬럼은 암시적으로NOT NULL이므로 이 예외가 없다.UNIQUE NOT NULL은 모든 행에 값이 있고 그 값이 서로 달라야 한다.UNIQUE NULL은 non-NULL 값의 중복만 막고NULL은 여러 행에서 허용한다.- 빈 문자열
'', 숫자0, 문자열'NULL'은 SQLNULL이 아니다. 이 값들은 일반 값처럼 중복 검사를 받는다. - Unique constraint와 Unique index는 MySQL에서 사실상 같은 인덱스 구조로 구현되지만, 설계 의도는 “조회 가속”보다 “정합성 제약”에 가깝다.
다음 예제는 nullable 이메일 컬럼에 대한 기본 동작을 확인한다. 의도적인 중복 입력은 검증 세션을 중단하지 않도록 INSERT IGNORE로 실행하고, 바로 SHOW WARNINGS에서 서버가 반환한 오류를 확인한다.
DROP TABLE IF EXISTS unique_email_demo;
CREATE TABLE unique_email_demo (
user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NULL,
PRIMARY KEY (user_id),
UNIQUE KEY uk_email (email)
) ENGINE = InnoDB;
INSERT INTO unique_email_demo (email)
VALUES (NULL), (NULL), ('dba@example.com');
INSERT IGNORE INTO unique_email_demo (email)
VALUES ('dba@example.com');
SHOW WARNINGS;
SELECT user_id, email
FROM unique_email_demo
ORDER BY user_id;
DROP TABLE unique_email_demo;
실행 결과(MySQL 8.0.x):
mysql> DROP TABLE IF EXISTS unique_email_demo;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE unique_email_demo (
-> user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> email VARCHAR(255) NULL,
-> PRIMARY KEY (user_id),
-> UNIQUE KEY uk_email (email)
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO unique_email_demo (email)
-> VALUES (NULL), (NULL), ('dba@example.com');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT IGNORE INTO unique_email_demo (email)
-> VALUES ('dba@example.com');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------+
| Warning | 1062 | Duplicate entry 'dba@example.com' for key 'unique_email_demo.uk_email' |
+---------+------+------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT user_id, email
-> FROM unique_email_demo
-> ORDER BY user_id;
+---------+-----------------+
| user_id | email |
+---------+-----------------+
| 1 | NULL |
| 2 | NULL |
| 3 | dba@example.com |
+---------+-----------------+
3 rows in set (0.00 sec)
mysql> DROP TABLE unique_email_demo;
Query OK, 0 rows affected (0.00 sec)
두 NULL 행은 모두 저장되지만 두 번째 dba@example.com은 uk_email과 충돌한다. INSERT IGNORE는 운영 코드에서 중복을 숨기기 위한 권장 패턴이 아니라, 이 예제에서 경고를 관찰하기 위한 장치다. 실제 쓰기 경로에서는 Duplicate entry를 명시적으로 처리하고, 무시해도 되는 업무 충돌인지 구분해야 한다.
2. InnoDB 내부에서 보는 유일성 검사
InnoDB의 secondary index 엔트리는 secondary key 뒤에 clustered index의 Primary Key를 포함한다. 이 구조 덕분에 동일한 secondary key 값에 속한 여러 행도 물리적으로 구별할 수 있다. 그러나 Unique secondary index의 논리적 중복 검사는 사용자가 정의한 Unique key 컬럼을 대상으로 수행한다. 모든 Unique key 컬럼이 non-NULL일 때 동일한 키가 이미 존재하면 쓰기가 거부된다.
쓰기 경로를 단순화하면 다음과 같다.
- 서버 계층이 입력 값을 자료형과 collation 규칙에 맞게 변환한다.
- InnoDB가 대상 Unique index에서 충돌 가능한 키 범위를 탐색한다.
- 동시 트랜잭션의 미커밋 키까지 고려해 필요한 record/gap 계열 잠금으로 검사 결과를 직렬화한다.
- 충돌이 없으면 clustered index와 secondary index 변경을 기록한다.
- 충돌이 있으면
ERROR 1062 (23000): Duplicate entry ...를 반환한다.
그러므로 애플리케이션에서 먼저 SELECT로 존재 여부를 조회한 뒤 INSERT하는 방식은 Unique index를 대체하지 못한다. 두 세션이 동시에 “없음”을 확인할 수 있기 때문이다. 최종 정합성 경계는 데이터베이스의 Unique constraint여야 하며, 선행 조회는 사용자 메시지나 불필요한 시도를 줄이는 보조 수단으로만 사용한다.
또한 문자열 키의 동일성은 collation의 영향을 받는다. 대소문자를 구분하지 않는 collation에서는 'DBA@example.com'과 'dba@example.com'이 같은 키로 판정될 수 있다. trailing space 처리, accent 민감도, Unicode 정규화 요구도 업무 규칙과 맞는지 확인해야 한다. Unique index를 만들었다는 사실만으로 애플리케이션이 생각하는 문자열 동일성까지 자동으로 보장되지는 않는다.
3. 복합 Unique index: 컬럼 하나의 NULL이 만드는 예외
복합 Unique index에서는 구성 컬럼 가운데 하나라도 NULL이면 해당 키 조합끼리 중복으로 판정되지 않는다. 예를 들어 (tenant_id, external_id)가 Unique여도 external_id IS NULL인 행은 같은 tenant에 여러 개 저장할 수 있다.
DROP TABLE IF EXISTS tenant_object_demo;
CREATE TABLE tenant_object_demo (
object_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
tenant_id BIGINT UNSIGNED NOT NULL,
external_id VARCHAR(64) NULL,
payload VARCHAR(100) NOT NULL,
PRIMARY KEY (object_id),
UNIQUE KEY uk_tenant_external (tenant_id, external_id)
) ENGINE = InnoDB;
INSERT INTO tenant_object_demo (tenant_id, external_id, payload)
VALUES
(10, NULL, 'draft-a'),
(10, NULL, 'draft-b'),
(10, 'EXT-100', 'confirmed'),
(20, 'EXT-100', 'another-tenant');
INSERT IGNORE INTO tenant_object_demo (tenant_id, external_id, payload)
VALUES (10, 'EXT-100', 'duplicate-in-same-tenant');
SHOW WARNINGS;
SELECT tenant_id, external_id, payload
FROM tenant_object_demo
ORDER BY object_id;
DROP TABLE tenant_object_demo;
실행 결과(MySQL 8.0.x):
mysql> CREATE TABLE tenant_object_demo (...,
-> UNIQUE KEY uk_tenant_external (tenant_id, external_id)
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO tenant_object_demo (tenant_id, external_id, payload)
-> VALUES (10, NULL, 'draft-a'), (10, NULL, 'draft-b'),
-> (10, 'EXT-100', 'confirmed'),
-> (20, 'EXT-100', 'another-tenant');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT IGNORE INTO tenant_object_demo (tenant_id, external_id, payload)
-> VALUES (10, 'EXT-100', 'duplicate-in-same-tenant');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1062 | Duplicate entry '10-EXT-100' for key 'tenant_object_demo.uk_tenant_external' |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT tenant_id, external_id, payload
-> FROM tenant_object_demo ORDER BY object_id;
+-----------+-------------+----------------+
| tenant_id | external_id | payload |
+-----------+-------------+----------------+
| 10 | NULL | draft-a |
| 10 | NULL | draft-b |
| 10 | EXT-100 | confirmed |
| 20 | EXT-100 | another-tenant |
+-----------+-------------+----------------+
4 rows in set (0.00 sec)
mysql> DROP TABLE tenant_object_demo;
Query OK, 0 rows affected (0.00 sec)
이 설계는 외부 ID가 아직 발급되지 않은 초안 행을 허용하려는 경우에는 적절하다. 그러나 “tenant마다 external_id 미지정 행도 하나만 허용”하려는 요구에는 맞지 않는다. 그 요구라면 NULL을 특정 sentinel 값으로 바꾸는 generated key를 설계하거나, 상태를 포함한 별도의 제약 키가 필요하다.
복합 키를 검토할 때는 다음 질문을 컬럼별로 답해야 한다.
- 어느 컬럼이 nullable인가?
- nullable 컬럼이 하나라도 있을 때 여러 행을 허용하는가?
NULL은 “미정”, “해당 없음”, “삭제됨” 가운데 무엇을 뜻하는가?- tenant 경계가 Unique key의 선두에 포함되어 있는가?
- 나중에
NULL을 실제 값으로 갱신할 때 기존 행과 충돌할 수 있는가?
마지막 항목은 특히 중요하다. 초안 여러 개가 NULL로 쌓이는 것은 가능하지만, 나중에 두 행을 같은 외부 ID로 확정하려 하면 두 번째 UPDATE가 실패한다. 쓰기 시점뿐 아니라 상태 전이 시점의 충돌 처리도 설계해야 한다.
4. 패턴 1: 값이 있을 때만 유일한 선택 속성
사용자 프로필의 전화번호, 아직 연결되지 않은 외부 시스템 ID처럼 값이 없는 행은 여러 개 허용하고 값이 생긴 뒤에는 유일해야 하는 속성은 nullable Unique index와 잘 맞는다.
권장 조건은 명확하다.
- 값 없음의 표현을 SQL
NULL하나로 통일한다. '', 공백 문자열,'N/A',0같은 여러 sentinel을 섞지 않는다.- 애플리케이션 입력 정규화 후에도 DB collation과 길이 제한이 같은 동일성 규칙을 갖는지 확인한다.
NULL에서 non-NULL로 바뀌는UPDATE의 중복 오류를 정상적인 업무 충돌로 처리한다.
단순한 nullable Unique index로 충분한데도 모든 미입력 값을 임의 문자열로 채우면 오히려 한 개의 미입력 행만 허용하는 제약이 생긴다. sentinel 값이 실제 데이터와 충돌할 가능성도 있다. “값 없음”을 표현하는 목적이라면 NULL의 의미를 일관되게 유지하는 편이 안전하다.
5. 패턴 2: 활성 행 하나만 허용하는 조건부 유일성
MySQL은 PostgreSQL의 partial unique index와 같은 WHERE status = 'ACTIVE' 구문을 제공하지 않는다. 대신 조건을 만족하는 행에는 실제 키를 반환하고, 나머지 행에는 NULL을 반환하는 generated column을 만든 뒤 Unique index를 적용할 수 있다. NULL이 여러 번 허용되는 특성을 의도적으로 사용하는 방식이다.
다음 테이블은 계정별로 ACTIVE 구독을 하나만 허용하면서 CANCELLED 이력은 여러 개 보관한다.
DROP TABLE IF EXISTS subscription_demo;
CREATE TABLE subscription_demo (
subscription_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
account_id BIGINT UNSIGNED NOT NULL,
status ENUM('ACTIVE', 'CANCELLED') NOT NULL,
active_account_id BIGINT UNSIGNED
GENERATED ALWAYS AS (
CASE WHEN status = 'ACTIVE' THEN account_id ELSE NULL END
) STORED,
PRIMARY KEY (subscription_id),
UNIQUE KEY uk_one_active_subscription (active_account_id),
KEY ix_account_history (account_id, subscription_id)
) ENGINE = InnoDB;
INSERT INTO subscription_demo (account_id, status)
VALUES
(101, 'CANCELLED'),
(101, 'CANCELLED'),
(101, 'ACTIVE'),
(202, 'ACTIVE');
INSERT IGNORE INTO subscription_demo (account_id, status)
VALUES (101, 'ACTIVE');
SHOW WARNINGS;
SELECT subscription_id, account_id, status, active_account_id
FROM subscription_demo
ORDER BY subscription_id;
DROP TABLE subscription_demo;
실행 결과(MySQL 8.0.x):
mysql> CREATE TABLE subscription_demo (...,
-> active_account_id BIGINT UNSIGNED GENERATED ALWAYS AS
-> (CASE WHEN status = 'ACTIVE' THEN account_id ELSE NULL END) STORED,
-> UNIQUE KEY uk_one_active_subscription (active_account_id), ...
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO subscription_demo (account_id, status)
-> VALUES (101, 'CANCELLED'), (101, 'CANCELLED'),
-> (101, 'ACTIVE'), (202, 'ACTIVE');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT IGNORE INTO subscription_demo (account_id, status)
-> VALUES (101, 'ACTIVE');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1062 | Duplicate entry '101' for key 'subscription_demo.uk_one_active_subscription' |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT subscription_id, account_id, status, active_account_id
-> FROM subscription_demo ORDER BY subscription_id;
+-----------------+------------+-----------+-------------------+
| subscription_id | account_id | status | active_account_id |
+-----------------+------------+-----------+-------------------+
| 1 | 101 | CANCELLED | NULL |
| 2 | 101 | CANCELLED | NULL |
| 3 | 101 | ACTIVE | 101 |
| 4 | 202 | ACTIVE | 202 |
+-----------------+------------+-----------+-------------------+
4 rows in set (0.00 sec)
mysql> DROP TABLE subscription_demo;
Query OK, 0 rows affected (0.00 sec)
이 패턴의 핵심은 CANCELLED 행의 active_account_id가 모두 NULL이므로 이력 보관을 막지 않고, ACTIVE 행만 원래 account_id를 키로 사용한다는 점이다. 다만 상태 종류가 늘어날 때 표현식의 의미를 다시 검토해야 한다. PAUSED도 유일성 대상인지, PENDING과 ACTIVE를 합쳐 하나만 허용할지에 따라 generated expression이 달라진다.
상태 전이는 한 트랜잭션 안에서 순서를 신중하게 정한다. 기존 활성 행을 CANCELLED로 바꾸기 전에 새 행을 ACTIVE로 넣으면 일시적으로 Unique key가 충돌한다. 일반적으로 기존 활성 행을 먼저 비활성화한 뒤 새 활성 행을 넣되, 동시 요청이 있을 수 있으므로 계정 행 잠금이나 Unique 충돌 재시도 정책을 함께 둔다.
6. 패턴 3: 입력 정규화 후 유일성 보장
업무 규칙이 “앞뒤 공백과 대소문자를 무시한 이메일은 하나만 허용하고, NULL·빈 문자열·공백만 있는 값은 미입력으로 취급한다”라면 원본 컬럼의 Unique index만으로는 의도가 충분히 드러나지 않는다. generated column에서 정규화 키를 만들고 그 키를 Unique로 제한할 수 있다.
DROP TABLE IF EXISTS normalized_email_demo;
CREATE TABLE normalized_email_demo (
user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NULL,
email_key VARCHAR(255)
GENERATED ALWAYS AS (NULLIF(LOWER(TRIM(email)), '')) STORED,
PRIMARY KEY (user_id),
UNIQUE KEY uk_normalized_email (email_key)
) ENGINE = InnoDB;
INSERT INTO normalized_email_demo (email)
VALUES
(NULL),
(''),
(' '),
('DBA@example.com');
INSERT IGNORE INTO normalized_email_demo (email)
VALUES (' dba@EXAMPLE.com ');
SHOW WARNINGS;
SELECT user_id,
COALESCE(CONCAT('[', email, ']'), 'NULL') AS original_email,
email_key
FROM normalized_email_demo
ORDER BY user_id;
DROP TABLE normalized_email_demo;
실행 결과(MySQL 8.0.x):
mysql> CREATE TABLE normalized_email_demo (...,
-> email_key VARCHAR(255) GENERATED ALWAYS AS
-> (NULLIF(LOWER(TRIM(email)), '')) STORED,
-> UNIQUE KEY uk_normalized_email (email_key)
-> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO normalized_email_demo (email)
-> VALUES (NULL), (''), (' '), ('DBA@example.com');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT IGNORE INTO normalized_email_demo (email)
-> VALUES (' dba@EXAMPLE.com ');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------+
| Warning | 1062 | Duplicate entry 'dba@example.com' for key 'normalized_email_demo.uk_normalized_email' |
+---------+------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT user_id,
-> COALESCE(CONCAT('[', email, ']'), 'NULL') AS original_email,
-> email_key
-> FROM normalized_email_demo ORDER BY user_id;
+---------+-------------------+-----------------+
| user_id | original_email | email_key |
+---------+-------------------+-----------------+
| 1 | NULL | NULL |
| 2 | [] | NULL |
| 3 | [ ] | NULL |
| 4 | [DBA@example.com] | dba@example.com |
+---------+-------------------+-----------------+
4 rows in set (0.00 sec)
mysql> DROP TABLE normalized_email_demo;
Query OK, 0 rows affected (0.00 sec)
NULLIF(..., '')가 미입력 표현을 NULL로 통합하므로 미입력 행은 여러 개 허용된다. 값이 있는 행은 LOWER(TRIM(...)) 결과를 기준으로 중복이 차단된다. 이 예시는 정책을 설명하기 위한 축소 모델이며, 실제 이메일 동일성 규칙은 더 복잡할 수 있다. 국제화 도메인, 로컬 파트의 대소문자 의미, 공급자별 점(.)·별칭 처리까지 임의로 정규화하면 서로 다른 주소를 합칠 위험이 있다. 정규화 함수는 반드시 업무에서 합의한 동일성 규칙만 구현해야 한다.
MySQL 8.0은 functional key part도 지원하지만, generated column은 계산 결과를 조회할 수 있어 마이그레이션 전 충돌 데이터와 운영 문제를 진단하기 쉽다. STORED 컬럼은 행 저장 공간과 쓰기 비용을 추가하므로 테이블 규모, 키 폭, 변경 빈도를 함께 평가한다.
7. 실패하기 쉬운 설계와 오해
7.1 애플리케이션 사전 조회만으로 중복을 막는다
SELECT 후 INSERT 사이에는 경쟁 조건이 있다. 여러 애플리케이션 인스턴스와 재시도가 존재하는 운영 환경에서는 반드시 DB 제약을 최종 방어선으로 둔다. 중복 오류는 “발생해서는 안 되는 예외”가 아니라 동시성 경쟁에서 가능한 결과로 처리한다.
7.2 NULL이 하나만 저장될 것이라고 가정한다
일부 DBMS의 과거 동작이나 다른 제품 경험을 MySQL에 그대로 적용하면 안 된다. MySQL의 nullable Unique key에는 여러 NULL이 들어갈 수 있다. 복합 키에서는 어느 한 컬럼의 NULL도 같은 효과를 만든다.
7.3 빈 문자열과 NULL을 혼용한다
NULL, '', ' '을 모두 “미입력”으로 취급하면서 원본 컬럼에만 Unique index를 두면 데이터 표현과 제약 규칙이 어긋난다. 쓰기 API에서 정규화하거나 검증된 generated key로 표현을 통합한다.
7.4 collation을 확인하지 않는다
Unique 문자열 키의 중복 판정은 collation 의미를 따른다. 대소문자와 accent를 구분해야 하는 식별자라면 적절한 binary/case-sensitive collation이나 이진 자료형을 검토한다. 반대로 대소문자를 무시해야 한다면 애플리케이션 정규화와 DB collation이 서로 다른 결과를 만들지 확인한다.
7.5 INSERT IGNORE로 모든 충돌을 숨긴다
INSERT IGNORE는 중복뿐 아니라 일부 변환 문제를 warning으로 낮출 수 있다. 반환된 affected rows와 warning을 확인하지 않으면 애플리케이션은 저장되지 않은 행을 성공으로 오해할 수 있다. 멱등 쓰기가 필요하면 자연 키, 명시적인 충돌 처리, 트랜잭션 경계를 함께 설계한다.
7.6 넓은 문자열을 무심코 Unique key로 사용한다
긴 문자열 Unique index는 B+Tree 페이지당 엔트리 수를 줄이고 buffer pool 효율, 쓰기 증폭, DDL 시간에 영향을 준다. 해시 generated column을 대안으로 사용할 때는 해시 충돌 가능성을 무시해서는 안 된다. 해시만 Unique로 두면 이론적 충돌이 정합성 오류가 될 수 있으므로 원본 값 재검증과 충돌 처리 설계가 필요하다.
8. 기존 테이블에 Unique index를 추가하는 절차
운영 테이블에는 이미 중복 non-NULL 값이 있을 수 있다. 바로 ALTER TABLE ... ADD UNIQUE를 실행하기 전에 다음 순서로 진행한다.
- 업무상의 동일성 규칙과
NULL허용 의미를 문서화한다. - 실제 collation과 자료형을 기준으로 중복 후보를 집계한다.
- 중복 행의 보존·병합·삭제 정책을 소유 팀과 합의한다.
- 정리 중 신규 중복 유입을 막을 쓰기 경로 배포 순서를 정한다.
- 스테이징 또는 복제본에서 DDL 시간, 잠금, redo/replication 부하를 측정한다.
- 운영 DDL 후 제약과 인덱스 정의를 재확인하고 오류율을 관찰한다.
중복 후보를 찾는 기본 형태는 다음과 같다. 실제 테이블명과 컬럼명으로 바꾸어 사용하며, 큰 테이블에서는 전체 스캔과 임시 공간 사용량을 먼저 평가한다.
SELECT email, COUNT(*) AS duplicate_count
FROM app_user
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1;
복합 키라면 nullable 컬럼을 제외할지 포함할지 업무 규칙에 맞게 결정해야 한다. 현재 MySQL Unique 동작과 동일한 충돌 후보를 찾으려면 모든 Unique key 구성 컬럼이 non-NULL인 범위부터 확인한다.
DDL 알고리즘과 잠금 수준은 MySQL 버전, 작업 종류, 테이블 정의에 따라 달라진다. ALGORITHM과 LOCK을 명시하더라도 서버가 지원하지 않으면 실패할 수 있다. 대형 운영 테이블에서는 “온라인 DDL”이라는 이름만 믿지 말고 metadata lock 대기, 장기 트랜잭션, 임시 공간, replica lag, failover 시 영향까지 런북에 포함한다.
9. Aurora MySQL에서의 운영 해석
Aurora MySQL도 MySQL 호환 SQL 의미에 따라 nullable Unique index에서 여러 NULL을 허용한다. 따라서 이 글의 논리 모델과 generated column 패턴은 호환 버전에서 동일하게 검토할 수 있다. 다만 운영 절차에는 다음 차이가 있다.
- 스토리지는 분산되어 있어도 Unique 검사와 트랜잭션 정합성은 writer 인스턴스의 쓰기 경로에서 수행된다.
- reader endpoint에서 수행한 사전 조회는 replica 지연과 라우팅 특성 때문에 쓰기 정합성 근거가 될 수 없다. writer의 Unique constraint가 최종 방어선이다.
- 대형 인덱스 생성은 writer의 CPU, I/O, buffer cache와 replica 적용 지연에 영향을 줄 수 있다. Aurora 버전별 online DDL 지원과 관측 지표를 사전 검증한다.
- failover나 애플리케이션 재시도 뒤에는 같은 요청이 다시 도착할 수 있다. Unique business key와 idempotency key를 이용해 결과를 판별해야 하며, 오류 문자열만으로 성공 여부를 추정하지 않는다.
- 파라미터 그룹, Performance Insights/Database Insights, CloudWatch 지표를 함께 사용해 DDL 기간의 부하와 lock wait를 관찰한다.
Aurora의 분산 스토리지는 업무 제약을 대신 설계해 주지 않는다. 어떤 행을 같은 것으로 볼지, 어떤 상태에서만 유일해야 할지는 여전히 스키마와 트랜잭션이 명시해야 한다.
10. 설계·배포 체크리스트
의미와 모델
-
NULL - 여러
NULL - 빈 문자열, 공백,
0, 임의 sentinel을NULL
문자열과 정규화
동시성과 오류 처리
-
ERROR 1062 -
INSERT IGNORE
마이그레이션과 관측
11. 정리
MySQL Unique index는 non-NULL 키의 유일성을 강하게 보장하지만, NULL끼리는 동일하다고 판정하지 않으므로 여러 행을 허용한다. 복합 키에서는 컬럼 하나의 NULL도 이 예외를 만든다. 이 동작은 실수로 정합성 구멍이 될 수도 있고, 선택 속성이나 조건부 유일성을 표현하는 유용한 도구가 될 수도 있다.
안전한 설계의 핵심은 “Unique를 걸었다”가 아니라 어떤 값을 같은 것으로 보고, 어느 상태에서 유일해야 하며, 값 없음은 무엇을 뜻하는지를 명시하는 것이다. nullable Unique index, generated key, collation, 상태 전이, 오류 처리를 하나의 정합성 설계로 다루어야 한다. 이후 인덱스 설계에서는 중복 방지뿐 아니라 Foreign Key와 참조 무결성, 온라인 스키마 변경이 이 제약과 어떻게 상호작용하는지도 함께 살펴볼 필요가 있다.