이 문서에서는 Spanner의 외래 키와 외래 키를 사용하여 데이터베이스에서 참조 무결성을 적용하는 방법을 설명합니다. 다음 주제에서는 외래 키와 각 사용 방법을 안내합니다.
Spanner의 외래 키 개요
외래 키는 테이블 간의 관계를 정의합니다. 외래 키를 사용하여 Spanner에서 이러한 관계의 데이터 무결성을 유지할 수 있습니다.
전자상거래 비즈니스의 수석 개발자라고 상상해 보세요. 고객 주문을 처리할 데이터베이스를 설계하고 있습니다. 데이터베이스에 각 주문, 고객, 제품에 대한 정보를 저장해야 합니다. 그림 1은 애플리케이션의 데이터베이스 기본 구조를 보여줍니다.
그림 1. 주문 처리 데이터베이스 다이어그램
Customers 테이블은 고객 정보를 저장하도록, Orders 테이블은 모든 주문을 추적하도록, Products 테이블은 각 제품에 대한 정보를 저장하도록 정의합니다.
그림 1은 다음과 같이 실제 관계에 매핑되는 테이블 간의 링크도 보여줍니다.
고객이 주문합니다.
제품 주문이 이루어집니다.
시스템의 주문이 유효한지 확인하기 위해 데이터베이스에서 다음 규칙을 적용하기로 결정합니다.
존재하지 않는 고객의 주문을 만들 수 없습니다.
고객이 제공되지 않는 제품을 주문할 수 없습니다.
이러한 규칙 또는 제약조건을 적용하면 데이터의 참조 무결성이 유지됩니다. 데이터베이스가 참조 무결성을 유지할 때는 부적합한 링크 또는 데이터 간 참조를 일으키는 부적합한 데이터를 추가하려는 모든 시도가 실패합니다. 참조 무결성은 사용자 오류를 방지합니다. 기본적으로 Spanner는 외래 키를 사용하여 참조 무결성을 적용합니다.
외래 키를 사용하여 참조 무결성 정의
다음은 그림 2에 표시된 것처럼 설계에 추가 세부정보가 추가된 주문 처리 예시를 다시 살펴봅니다.
그림 2. 외래 키가 있는 데이터베이스 스키마 다이어그램
이 설계에서는 이제 각 테이블의 열 이름과 유형을 보여줍니다. Orders 테이블은 또한 두 가지 외래 키 관계를 정의합니다. FK_CustomerOrder: Orders의 모든 행에 유효한 CustomerId가 있어야 합니다. FK_ProductOrder 외래 키: Orders 테이블의 모든 ProductId 값이 유효해야 합니다. 다음 표는 이러한 제약조건을 적용하려는 실제 규칙에 매핑합니다.
| 외래 키 이름 | 제약조건 | 실제 상황 설명 |
|---|---|---|
| FK_CustomerOrder | Orders의 모든 행에 유효한 CustomerId가 있어야 합니다. |
유효한 고객이 주문했습니다. |
| FK_ProductOrder | Orders의 모든 행에 유효한 ProductId가 있어야 합니다. |
유효한 제품을 주문했습니다. |
Spanner는 강제 외래 키를 사용하여 지정된 제약조건을 적용합니다. 즉, Spanner에서는 Customers 및 Products 테이블에 CustomerId 또는 ProductId가 없는 행을 Orders 테이블에 삽입하거나 업데이트하려고 시도하는 모든 트랜잭션이 실패합니다. 또한 Orders 테이블의 ID를 무효화하는 Customers 및 Products 테이블의 행을 업데이트하거나 삭제하려고 시도하는 트랜잭션도 실패합니다. Spanner가 제약조건을 검증하는 방법에 대한 자세한 내용은 트랜잭션 제약조건 검증 섹션을 참조하세요.
강제 외래 키와 달리 Spanner는 정보 외래 키의 제약조건을 검증하지 않습니다. 즉, 이 시나리오에서 정보 외래 키를 사용하면 Customers 및 Products 테이블에 존재하지 않는 CustomerId 또는 ProductId를 가진 행을 Orders 테이블에 삽입하거나 업데이트하려는 트랜잭션이 검증되지 않으며, 이로 인해 트랜잭션이 실패하지 않습니다. 또한 강제 외래 키와 달리 정보 외래 키는 PostgreSQL이 아닌 GoogleSQL에서만 지원됩니다.
외래 키 특성
다음은 Spanner의 외래 키 특성 목록입니다.
외래 키를 정의하는 테이블은 참조 테이블이고 외래 키 열은 참조 열입니다.
외래 키는 참조된 테이블의 참조된 열을 참조합니다.
예시에서와 같이 각 외래 키 제약조건의 이름을 지정할 수 있습니다. 이름을 지정하지 않으면 Spanner가 이름을 자동으로 생성합니다. Spanner의
INFORMATION_SCHEMA에서 생성된 이름을 쿼리할 수 있습니다. 제약조건 이름은 테이블 및 색인 이름과 마찬가지로 스키마로 범위가 지정되며, 스키마 내에서 고유해야 합니다.참조 열과 참조된 열의 개수는 동일해야 합니다. 순서가 중요합니다. 예를 들어 첫 번째 참조 열은 첫 번째 참조된 열을 참조하고 두 번째 참조 열은 두 번째 참조된 열을 참조합니다.
참조 열과 참조된 열은 동일한 유형이어야 합니다. 열의 색인을 생성할 수 있어야 합니다.
allow_commit_timestamp=true옵션이 있는 열에는 외래 키를 만들 수 없습니다.배열 구조의 열은 지원되지 않습니다.
JSON 열은 지원되지 않습니다.
외래 키는 동일한 테이블의 열을 참조할 수 있습니다(자체 참조 외래 키). 테이블의
EmployeeId열을 참조하는ManagerId열이 있는Employee테이블이 한 예시입니다.외래 키는 두 테이블이 직접 또는 간접적으로 서로를 참조하는 테이블 간의 순환 관계를 형성할 수도 있습니다. 외래 키를 만들기 전에 참조된 테이블이 있어야 합니다. 즉,
ALTER TABLE문을 사용하여 하나 이상의 외래 키를 추가해야 합니다.참조된 키는 고유해야 합니다. Spanner는 외래 키의 참조된 열이 참조된 테이블의 기본 키 열과 일치하는 경우 참조된 테이블의
PRIMARY KEY를 사용합니다. Spanner가 참조된 테이블의 기본 키를 사용할 수 없으면 참조된 열에 대해UNIQUE NULL_FILTERED INDEX를 만듭니다.외래 키는 사용자가 만든 보조 색인을 사용하지 않습니다. 대신 자체 지원 색인을 만듭니다. 지원 색인은 명시적
force_index지시문을 비롯한 쿼리 평가에 사용될 수 있습니다. Spanner의INFORMATION_SCHEMA에서 지원 색인의 이름을 쿼리할 수 있습니다. 자세한 내용은 지원 색인을 참조하세요.
외래 키 유형
외래 키에는 강제와 정보의 두 가지 유형이 있습니다. 강제 외래 키는 기본값이며 참조 무결성을 적용합니다. 정보 외래 키는 참조 무결성을 적용하지 않으며 쿼리 최적화를 위해 의도한 논리적 데이터 모델을 선언하는 데 가장 적합합니다. 자세한 내용은 다음의 강제 외래 키 및 정보 외래 키 섹션과 외래 키 유형 비교 표를 참조하세요.
강제 외래 키
Spanner의 기본 외래 키 유형인 강제 외래 키는 참조 무결성을 적용합니다. 강제 외래 키는 참조 무결성을 적용하므로 다음 작업을 수행하려는 시도가 실패합니다.
참조된 테이블에 존재하지 않는 외래 키 값이 있는 참조 테이블에 행을 추가하는 작업이 실패합니다.
참조 테이블의 행에서 참조하는 참조된 테이블에서 행을 삭제하는 작업이 실패합니다.
모든 PostgreSQL 외래 키는 강제 외래 키입니다. GoogleSQL 외래 키는 기본적으로 강제 외래 키입니다. 외래 키는 기본적으로 강제 외래 키이므로 GoogleSQL 외래 키가 강제 외래 키임을 지정하도록 ENFORCED 키워드를 사용하는 것은 선택사항입니다.
정보 외래 키
정보 외래 키는 쿼리 최적화를 위해 의도한 논리적 데이터 모델을 선언하는 데 사용됩니다. 참조된 테이블 키는 정보 외래 키에 대해 고유해야 하지만 참조 무결성은 적용되지 않습니다. 정보 외래 키를 사용할 때 참조 무결성을 선택적으로 검증하려면 클라이언트 측에서 검증 로직을 관리해야 합니다. 자세한 내용은 정보 외래 키 사용을 참조하세요.
GoogleSQL 외래 키가 정보 외래 키임을 지정하려면 NOT ENFORCED 키워드를 사용합니다. PostgreSQL은 정보 외래 키를 지원하지 않습니다.
외래 키 유형 비교
강제 외래 키와 정보 외래 키 모두 이점이 있습니다. 다음 섹션에서는 두 가지 유형의 외래 키를 비교하고 몇 가지 권장사항을 소개합니다.
대략적인 외래 키 차이점
대략적으로 강제 외래 키와 정보 외래 키의 차이점은 다음과 같습니다.
적용. 강제 외래 키는 쓰기에 대한 참조 무결성을 검증하고 보장합니다. 정보 외래 키는 참조 무결성을 검증하거나 보장하지 않습니다.
스토리지. 강제 외래 키는 제한된 테이블의 지원 색인에 추가 스토리지가 필요할 수 있습니다.
쓰기 처리량. 강제 외래 키는 정보 외래 키보다 쓰기 경로에서 더 많은 오버헤드가 발생할 수 있습니다.
쿼리 최적화. 두 가지 유형의 외래 키 모두 쿼리 최적화에 사용할 수 있습니다. 옵티마이저가 정보 외래 키를 사용할 수 있는 경우 데이터가 정보 외래 키 관계와 일치하지 않으면 쿼리 결과에 실제 데이터가 반영되지 않을 수 있습니다(예: 일부 제한된 키의 참조된 테이블에 일치하는 참조된 키가 없는 경우).
외래 키 차이점 표
다음 표에는 강제 외래 키와 정보 외래 키의 차이점이 자세히 나와 있습니다.
| 강제 외래 키 | 정보 외래 키 | |
|---|---|---|
| 키워드 | ENFORCED |
NOT ENFORCED |
| GoogleSQL에서 지원 | 예. GoogleSQL의 외래 키는 기본적으로 강제 외래 키입니다. | 예. |
| PostgreSQL에서 지원 | 예. PostgreSQL의 외래 키만 강제 외래 키일 수 있습니다. | 아니요. |
| 스토리지 | 강제 외래 키에는 최대 2개의 지원 색인을 위한 스토리지가 필요합니다. | 정보 외래 키에는 최대 1개의 지원 색인을 위한 스토리지가 필요합니다. |
| 필요한 경우 참조된 테이블 열에 지원 색인을 만듭니다. | 예. | 예. |
| 필요한 경우 참조 테이블 열에 지원 색인을 만듭니다. | 예. | 아니요. |
| 외래 키 작업 지원 | 예. | 아니요. |
| 참조 무결성 검증 및 적용 | 예. | 아니요. 검증하지 않으면 쓰기 성능이 향상되지만 쿼리 최적화를 위해 정보 외래 키가 사용되는 경우 쿼리 결과에 영향을 줄 수 있습니다. 클라이언트 측 검증 또는 강제 외래 키를 사용하여 참조 무결성을 보장할 수 있습니다. |
사용할 외래 키 유형 선택
다음 가이드라인을 사용하여 사용할 외래 키 유형을 결정할 수 있습니다.
강제 외래 키로 시작하는 것이 좋습니다. 강제 외래 키는 항상 데이터와 논리적 모델을 일관되게 유지합니다. 강제 외래 키는 사용 사례에 사용할 수 없는 경우가 아니라면 권장되는 옵션입니다.
다음 각 항목에 해당하는 경우 정보 외래 키를 고려하는 것이 좋습니다.
쿼리 최적화에서 정보 외래 키로 설명되는 논리적 데이터 모델을 사용하려고 합니다.
엄격한 참조 무결성을 유지하는 것이 비실용적이거나 성능에 상당한 영향을 미칩니다. 다음은 정보 외래 키 사용을 고려해볼 수 있는 경우의 예시입니다.
업스트림 데이터 소스가 eventual consistency 모델을 따릅니다. 이 경우 소스 시스템에서 업데이트한 내용이 Spanner에 즉시 반영되지 않을 수 있습니다. 업데이트가 즉각적이지 않을 수 있으므로 외래 키 관계에 일시적인 불일치가 발생할 수 있습니다.
데이터에 다수의 참조 관계가 있는 참조된 행이 포함되어 있습니다. Spanner에서는 참조 무결성 유지와 관련된 모든 행을 검증하거나 경우에 따라 삭제해야 하므로 이러한 행을 업데이트하는 데 많은 리소스가 사용될 수 있습니다. 이 시나리오에서는 업데이트가 Spanner 성능에 영향을 미치고 동시 트랜잭션 속도를 느리게 만들 수 있습니다.
애플리케이션이 잠재적인 데이터 불일치와 이것이 쿼리 결과에 미치는 영향을 처리할 수 있습니다.
정보 외래 키 사용
다음 주제는 정보 외래 키에만 해당합니다. 정보 외래 키와 강제 외래 키 모두에 적용되는 주제는 다음을 참조하세요.
정보 외래 키가 있는 새 테이블 만들기
DDL 문을 사용하여 Spanner 데이터베이스에서 정보 외래 키를 만들고 삭제합니다. CREATE TABLE 문을 사용하여 새 테이블에 외래 키를 추가합니다. 마찬가지로 ALTER TABLE 문을 사용하여 기존 테이블에 외래 키를 추가하거나 삭제할 수 있습니다.
다음 예시에서는 GoogleSQL을 사용하여 정보 외래 키가 있는 새 테이블을 만듭니다. PostgreSQL에서는 정보 외래 키가 지원되지 않습니다.
GoogleSQL
CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);
CREATE TABLE Orders (
OrderId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
Quantity INT64 NOT NULL,
ProductId INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
REFERENCES Customers (CustomerId) NOT ENFORCED
) PRIMARY KEY (OrderId);
PostgreSQL
Not Supported
외래 키를 만들고 관리하는 방법에 대한 예시를 더 확인하려면 외부 키 관계 만들기 및 관리를 참조하세요. DDL 문에 대한 자세한 내용은 DDL 참조를 확인하세요.
쿼리 최적화를 위해 정보 외래 키 사용
강제 외래 키와 정보 외래 키 모두 쿼리 옵티마이저에서 쿼리 성능을 개선하는 데 사용할 수 있습니다. 정보 외래 키를 사용하면 엄격한 참조 무결성 적용 오버헤드 없이 최적화된 쿼리 계획을 활용할 수 있습니다.
쿼리 옵티마이저가 정보 외래 키 정보를 활용하도록 사용 설정하는 경우 최적화의 정확성은 정보 외래 키로 설명되는 논리적 모델과 일치하는 데이터가 있는지에 따라 달라진다는 점을 이해하는 것이 중요합니다. 불일치가 있는 경우 쿼리 결과에 실제 데이터가 반영되지 않을 수 있습니다. 불일치의 예시로는 제한된 열의 값이 참조된 열에서 일치하지 않는 경우입니다.
기본적으로 쿼리 옵티마이저는 NOT ENFORCED 외래 키를 사용합니다. 이를 변경하려면 데이터베이스 옵션 use_unenforced_foreign_key_for_query_optimization을 false로 설정합니다. 다음은 이를 보여주는 GoogleSQL 예시입니다(PostgreSQL에서는 정보 외래 키를 사용할 수 없음).
SET DATABASE OPTIONS (
use_unenforced_foreign_key_for_query_optimization = false
);
불리언 쿼리 문 힌트 @{use_unenforced_foreign_key}는 옵티마이저가 NOT ENFORCED 외래 키를 사용할지 여부를 제어하는 데이터베이스 옵션을 쿼리별로 재정의합니다. 이 힌트 또는 데이터베이스 옵션을 사용 중지하면 예상치 못한 쿼리 결과 문제를 해결하는 데 도움이 될 수 있습니다. 다음은 @{use_unenforced_foreign_key}를 사용하는 방법을 보여줍니다.
@{use_unenforced_foreign_key=false} SELECT Orders.CustomerId
FROM Orders
INNER JOIN Customers ON Customers.CustomerId = Orders.CustomerId;
강제 외래 키 사용
다음 주제는 강제 외래 키에만 적용됩니다. 정보 외래 키와 강제 외래 키 모두에 적용되는 주제는 다음을 참조하세요.
강제 외래 키로 새 테이블 만들기
DDL을 사용하여 Spanner 데이터베이스에서 강제 외래 키를 만들고 삭제합니다. CREATE TABLE 문을 사용하여 새 테이블에 외래 키를 추가합니다. 마찬가지로 ALTER TABLE 문을 사용하여 기존 테이블에 외래 키를 추가하거나 삭제할 수 있습니다.
DDL을 사용하여 Spanner 데이터베이스에서 외래 키를 만들고 삭제합니다. CREATE TABLE 문을 사용하여 새 테이블에 외래 키를 추가합니다. 마찬가지로 ALTER TABLE 문을 사용하여 기존 테이블에 외래 키를 추가하거나 삭제할 수 있습니다.
다음은 강제 외래 키로 새 테이블을 만드는 예시입니다.
GoogleSQL
CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);
CREATE TABLE Orders (
OrderId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
Quantity INT64 NOT NULL,
ProductId INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
REFERENCES Customers (CustomerId) ENFORCED
) PRIMARY KEY (OrderId);
PostgreSQL
CREATE TABLE Customers (
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CustomerId)
);
CREATE TABLE Orders (
OrderId BIGINT NOT NULL,
CustomerId BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductId BIGINT NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerId)
REFERENCES Customers (CustomerId),
PRIMARY KEY (OrderId)
);
외래 키를 만들고 관리하는 방법에 대한 예시를 더 확인하려면 외부 키 관계 만들기 및 관리를 참조하세요.
외래 키 작업
외래 키 작업은 강제 외래 키에만 정의할 수 있습니다.
외래 키 작업은 참조하는 열이 삭제되거나 업데이트될 때 제한된 열에 수행할 작업을 제어합니다. Spanner는 ON DELETE CASCADE 작업의 사용을 지원합니다. 외래 키 ON DELETE CASCADE 작업을 사용하면 참조된 외래 키가 포함된 행을 삭제할 때 해당 키를 참조하는 모든 행도 동일한 트랜잭션에서 삭제됩니다.
DDL을 사용하여 데이터베이스를 만들 때 작업이 포함된 외래 키를 추가할 수 있습니다. CREATE TABLE 문을 사용하여 작업이 포함된 외래 키를 새 테이블에 추가합니다. 마찬가지로 ALTER TABLE 문을 사용하여 외래 키 작업을 기존 테이블에 추가하거나 외래 키 작업을 삭제할 수 있습니다. 다음은 외래 키 작업이 포함된 새 테이블을 만드는 방법의 예시입니다.
GoogleSQL
CREATE TABLE ShoppingCarts (
CartId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);
PostgreSQL
CREATE TABLE ShoppingCarts (
CartId bigint NOT NULL,
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CartId),
CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);
다음은 Spanner에서 외래 키 작업의 특성 목록입니다.
외래 키 작업은
ON DELETE CASCADE또는ON DELETE NO ACTION입니다.INFORMATION_SCHEMA를 쿼리하여 작업이 포함된 외래 키 제약조건을 찾을 수 있습니다.기존 외래 키 제약조건에 외래 키 작업을 추가할 수 없습니다. 작업이 포함된 새 외래 키 제약조건을 추가해야 합니다.
제약조건 검증
제약조건 검증은 강제 외래 키에만 적용됩니다.
Spanner는 트랜잭션이 커밋될 때 또는 쓰기 효과가 트랜잭션의 후속 작업에 표시될 때 강제 외래 키 제약조건을 검증합니다.
참조 열에 삽입된 값은 참조된 테이블과 참조된 열의 값과 일치합니다. 참조 값이 NULL인 행은 선택되지 않습니다. 즉, 이러한 행을 참조 테이블에 추가할 수 있습니다.
Spanner는 DML 문 또는 API를 사용하여 데이터를 업데이트하려고 할 때 적용 가능한 모든 강제 외래 키 참조 제약조건을 검증합니다. 제약조건이 잘못된 경우 대기 중인 모든 변경사항이 롤백됩니다.
검증은 각 DML 문 바로 뒤에서 수행됩니다. 예를 들어 참조 행을 삽입하기 전에 참조된 행을 삽입해야 합니다. 변형 API를 사용하면 트랜잭션이 커밋될 때까지 변형이 버퍼링됩니다. 강제 외래 키 검증은 트랜잭션이 커밋될 때까지 지연됩니다. 이 경우 참조 행을 먼저 삽입할 수 있습니다.
각 트랜잭션은 강제 외래 키 제약조건에 영향을 주는 수정 사항이 있는지 평가됩니다. 이 평가에는 서버에 대한 추가 요청이 필요할 수 있습니다. 또한 트랜잭션 수정을 평가하고 색인을 유지하기 위해 지원 색인에 추가 처리 시간이 필요합니다. 또한 추가 스토리지가 각 색인에 필요합니다.
장기 실행 단계식 삭제 작업
참조된 테이블에서 행을 삭제하면 Spanner는 삭제된 행을 참조하는 참조 테이블의 모든 행을 삭제해야 합니다. 이는 단일 삭제 작업으로 수천 개의 다른 삭제 작업이 발생하는 연쇄 효과로 이어질 수 있습니다. 테이블에 단계식 삭제 작업이 포함된 외래 키 제약조건을 추가하거나 단계식 삭제 작업이 포함된 외래 키 제약조건이 있는 테이블을 만들면 삭제 작업이 느려질 수 있습니다.
외래 키 단계식 삭제 변형 한도 초과
외래 키 단계식 삭제를 사용하여 다수의 레코드를 삭제하면 성능에 영향을 줄 수 있습니다. 이는 삭제된 각 레코드가 관련된 모든 레코드의 삭제를 호출하기 때문입니다. 외래 키 단계식 삭제를 사용하여 다수의 레코드를 삭제해야 하는 경우 상위 테이블에서 행을 삭제하기 전에 하위 테이블에서 행을 명시적으로 삭제해야 합니다. 이렇게 하면 변형 한도 때문에 트랜잭션이 실패하지 않습니다.
강제 외래 키와 테이블 인터리브 처리 비교
Spanner의 테이블 인터리브 처리는 하위 테이블의 기본 키에 상위 테이블의 기본 키 열이 포함된 여러 상위/하위 관계에 적합합니다. 하위 행과 상위 행을 같은 위치에 배치하면 성능이 크게 향상될 수 있습니다.
외래 키는 보다 일반적인 상위/하위 솔루션이며 추가 사용 사례를 해결합니다. 기본 키 열로 제한되지 않으며 테이블에는 여러 외래 키 관계가 일부 관계의 상위 요소와 다른 관계의 하위 요소 모두로 포함될 수 있습니다. 그러나 외래 키 관계가 스토리지 레이어에서 테이블의 동일한 위치를 암시하지 않습니다.
다음과 같이 정의된 Orders 테이블을 예시로 들어 보겠습니다.
그림 3. 강제 외래 키가 있는 데이터베이스 스키마 다이어그램
그림 3의 설계에는 몇 가지 제한사항이 있습니다. 예를 들어 각 주문에는 주문 상품 하나만 포함될 수 있습니다.
고객이 주문당 제품을 두 개 이상 주문할 수 있기를 원한다고 가정해 보겠습니다. 고객이 주문한 각 제품의 항목이 포함된 OrderItems 테이블을 도입하여 설계를 보정할 수 있습니다. Orders와 OrderItems 간의 새로운 일대다 관계를 나타내는 다른 강제 외래 키를 도입할 수 있습니다. 하지만 주문과 각 주문 상품에 대한 쿼리를 실행하고자 하는 경우가 많다는 것도 알고 있습니다. 이 데이터를 같은 위치에 배치하면 성능이 향상되므로 Spanner의 테이블 인터리브 처리 기능을 사용하여 상위/하위 관계를 만들어야 합니다.
다음은 Orders로 인터리브 처리된 OrderItems 테이블을 정의하는 방법입니다.
GoogleSQL
CREATE TABLE Products (
ProductId INT64 NOT NULL,
Name STRING(256) NOT NULL,
Price FLOAT64
) PRIMARY KEY(ProductId);
CREATE TABLE OrderItems (
OrderId INT64 NOT NULL,
ProductId INT64 NOT NULL,
Quantity INT64 NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products (ProductId)
) PRIMARY KEY (OrderId, ProductId),
INTERLEAVE IN PARENT Orders ON DELETE CASCADE;
PostgreSQL
CREATE TABLE Products (
ProductId BIGINT NOT NULL,
Name varchar(256) NOT NULL,
Price float8,
PRIMARY KEY(ProductId)
);
CREATE TABLE OrderItems (
OrderId BIGINT NOT NULL,
ProductId BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
FOREIGN KEY (ProductId) REFERENCES Products (ProductId),
PRIMARY KEY (OrderId, ProductId)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;
그림 4는 Orders로 인터리브 처리된 새 테이블 OrderItems를 도입하여 업데이트된 데이터베이스 스키마를 시각적으로 보여줍니다. 또한 이 두 테이블 간의 일대다 관계도 확인할 수 있습니다.
그림 4. 인터리브 처리된 OrderItems 테이블 추가
이 구성에서는 각 주문에 여러 OrderItems 항목을 포함할 수 있으며 각 주문에 대한 OrderItems 항목은 인터리브 처리되므로 주문과 같은 위치에 배치됩니다. 이러한 방식으로 Orders 및 OrderItems를 물리적으로 인터리브 처리하면 성능이 개선되어 효과적으로 테이블을 사전에 조인하고 디스크 액세스를 최소화하면서 관련 행에 액세스할 수 있습니다. 예를 들어 Spanner는 로컬에서 기본 키로 조인을 수행하여 디스크 액세스와 네트워크 트래픽을 최소화할 수 있습니다.
트랜잭션의 변형 수가 80,000을 초과하면 트랜잭션이 실패합니다. 이러한 대규모 단계식 삭제는 '상위 요소에 인터리브 처리된' 관계가 있는 테이블에는 잘 작동하지만 외래 키 관계가 있는 테이블에는 작동하지 않습니다. 외래 키 관계가 있고 다수의 행을 삭제해야 하는 경우 먼저 하위 테이블에서 행을 명시적으로 삭제해야 합니다.
다른 테이블과의 외래 키 관계가 있는 사용자 테이블이 있고 참조된 테이블에서 행을 삭제하여 수백만 개의 행 삭제가 트리거되는 경우 '상위 요소에 인터리브 처리된' 관계가 있는 단계식 삭제 작업이 있는 스키마를 설계해야 합니다.
비교표
다음 표에서는 강제 외래 키와 테이블 인터리브 처리를 비교하는 방법을 요약합니다. 이 정보를 사용하여 설계한 적합한 것을 결정할 수 있습니다.
| 상위/하위 관계 유형 | 테이블 인터리브 처리 | 강제 외래 키 |
|---|---|---|
| 기본 키 사용 가능 | 예 | 예 |
| 기본 키가 아닌 열 사용 가능 | 아니요 | 예 |
| 지원되는 상위 수 | 0 .. 1 | 0 .. N |
| 상위 및 하위 데이터를 함께 저장 | 예 | 아니요 |
| 단계식 삭제 지원 | 예 | 예 |
| Null 일치 모드 | 모든 참조 값이 참조된 값과 다르면 전달됩니다. Null 값은 Null 값과 다릅니다. Null 값은 Null이 아닌 값과 다릅니다. |
참조 값이 Null이면 전달됩니다. 모든 참조 값이 Null이 아니면 참조된 테이블에 참조 값과 동일한 값이 있는 행이 포함됩니다. 일치하는 행이 없으면 실패합니다. |
| 적용 시기 | 변형 API 사용 시 작업당. DML 사용 시 문당. |
변형 API 사용 시 트랜잭션당. DML 사용 시 문당. |
| 삭제 가능 | 아니요. 전체 하위 테이블을 삭제하지 않는 한 테이블 인터리브 처리는 생성된 후에 삭제될 수 없습니다. | 예 |
지원 색인
외래 키는 사용자가 만든 색인을 사용하지 않습니다. 대신 자체 지원 색인을 만듭니다. 강제 외래 키와 정보 외래 키는 Spanner에서 지원 색인을 다르게 만듭니다.
강제 외래 키의 경우 Spanner는 각 외래 키에 보조 지원 색인을 최대 두 개까지 만들 수 있습니다. 하나는 참조 열용이고 다른 하나는 참조된 열용입니다.
정보 외래 키의 경우 Spanner는 참조된 열에 필요한 경우 최대 1개의 지원 색인을 만들 수 있습니다. 정보 외래 키는 참조 열의 지원 색인을 만들지 않습니다.
강제 외래 키와 정보 외래 키 모두 일반적으로 참조된 테이블의 기본 키를 참조하므로 참조된 테이블의 색인은 일반적으로 필요하지 않습니다. 따라서 정보 외래 키에는 일반적으로 지원 색인이 없습니다. 필요한 경우 참조된 테이블에 생성된 지원 색인은 UNIQUE NULL_FILTERED 색인입니다. 기존 데이터가 색인의 고유성 제약조건을 위반하면 외래 키 생성이 실패합니다.
정보 외래 키에는 참조 테이블의 지원 색인이 없습니다. 강제 외래 키의 경우 참조 테이블의 지원 색인은 NULL_FILTERED입니다.
외래 키 두 개 이상에서 동일한 지원 색인이 필요한 경우 Spanner는 각각에 대해 단일 색인을 만듭니다. 지원 색인을 사용하는 외래 키가 삭제되면 지원 색인도 삭제됩니다. 지원 색인을 변경 또는 삭제할 수 없습니다.
Spanner는 각 데이터베이스의 정보 스키마를 사용하여 지원 색인에 대한 메타데이터를 저장합니다. true의 SPANNER_IS_MANAGED 값이 있는 INFORMATION_SCHEMA.INDEXES 내 행에서 지원 색인을 기술합니다.
정보 스키마를 직접 호출하는 SQL 쿼리 외부에서Google Cloud 콘솔은 데이터베이스의 지원 색인에 대해 어떠한 정보도 표시하지 않습니다.
장기 실행 스키마 변경사항
기존 테이블에 강제 외래 키를 추가하거나 외래 키가 있는 새 테이블을 만들면 장기 실행 작업이 발생할 수 있습니다. 새 테이블의 경우 장기 실행 작업이 완료될 때까지 테이블에 쓸 수 없습니다.
다음 표는 강제 외래 키와 정보 외래 키가 새 테이블 또는 기존 테이블에 있는 경우 Spanner에서 어떤 일이 발생하는지 보여줍니다.
| 테이블 유형 | 강제 외래 키 | 정보 외래 키 |
|---|---|---|
| 새 테이블 | Spanner는 각 외래 키에 필요한 참조된 색인을 백필합니다. | Spanner는 각 외래 키에 필요한 참조된 색인을 백필합니다. |
| 기존 테이블 | Spanner는 필요에 따라 참조 색인과 참조된 색인을 백필합니다. 또한 Spanner는 테이블의 기존 데이터를 검증하여 외래 키의 참조 무결성 제약조건을 준수하는지 확인합니다. 데이터가 잘못된 경우 스키마 변경이 실패합니다. | Spanner는 필요에 따라 참조된 색인을 백필하고 테이블의 기존 데이터를 검증하지 않습니다. |
다음은 지원되지 않습니다.
- 기존 강제 외래 키 제약조건에 외래 키 작업 추가
- 기존 외래 키의 적용 변경
두 경우 모두 대신 다음을 수행하는 것이 좋습니다.
- 필요한 작업 또는 적용이 포함된 새 제약조건을 추가합니다.
- 이전 제약조건을 삭제합니다.
새 제약조건을 추가하고 이전 제약조건을 삭제하면 장기 실행 제약조건 변경 작업 문제가 방지됩니다. 예를 들어 기존 외래 키에 DELETE CASCADE 작업을 추가하려고 한다고 가정해 보겠습니다. ON DELETE CASCADE 작업이 있는 새 외래 키를 만든 후 두 제약조건의 효과는 DELETE CASCADE 작업입니다. 그런 다음 이전 제약조건을 안전하게 삭제할 수 있습니다.
제약조건을 삭제하면 다른 외래 키 제약조건에서 색인이 사용되지 않는 경우 외래 키 지원 색인이 삭제될 수 있습니다. 따라서 먼저 이전 제약조건을 삭제한 후 나중에 작업이 포함된 동일한 외래 키 제약조건을 추가하면 색인 백필, 고유 색인 제약조건 검증, 외래 키 참조 제약조건 검증과 같은 장기 실행 작업이 발생할 수 있습니다.
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE를 쿼리하여 외래 키 생성 상태를 확인할 수 있습니다.
다음 단계
외래 키 관계 만들기 및 관리 방법 알아보기
정보 스키마 자세히 알아보기