Course All lectures for KO purposes - Lecture: 데이터베이스의 키

5.1 소개

인터넷은 관계형 데이터베이스에서 키를 선택하고 사용하는 방법에 대한 독단적인 교훈으로 가득 차 있습니다. 때로는 분쟁이 홀리바르로 변하기도 합니다. 자연 키를 사용해야 합니까 아니면 인공 키를 사용해야 합니까? 자동 증가 정수 또는 UUID?

64개의 기사를 읽고, 5권의 책 섹션을 넘기고, IRC와 StackOverflow에 수많은 질문을 던진 후, 나(원본 기사의 저자인 Joe "begriffs" Nelson)는 퍼즐 조각을 맞춰 놓은 것 같습니다. 이제 적을 화해시킬 수 있습니다. 많은 주요 분쟁은 실제로 다른 사람의 관점에 대한 오해에서 발생합니다.

문제를 분리하고 마지막에 다시 합치자. 먼저 "키"란 무엇입니까?

기본 키는 잠시 잊어버리고 좀 더 일반적인 아이디어에 관심이 있습니다. 키는 행에 중복 값이 없는 열(column) 또는 열입니다 . 또한 열은 환원 불가능하게 고유해야 합니다. 즉, 열의 하위 집합에는 이러한 고유성이 없습니다.

그러나 먼저 몇 가지 이론이 있습니다.

기본 키

기본 키테이블의 행을 식별하는 데 직접 사용됩니다. 다음 제한 사항을 준수해야 합니다.

기본 키는 항상 고유 해야 합니다.
항상 테이블에 있어야 하며 값이 있어야 합니다 .
값을 자주 변경해서는 안 됩니다. 이상적으로는 값을 전혀 변경하지 않아야 합니다 .

일반적으로 기본 키는 테이블의 단일 열을 나타내지만 여러 열로 구성된 복합 키일 수도 있습니다.

복합 키

맞춤 키- 각 테이블 행을 고유하게 식별하는 속성(열)의 조합. 모든 열, 여러 열, 하나가 될 수 있습니다. 이 경우 이러한 속성의 값을 포함하는 행은 반복되어서는 안됩니다.

잠재적인 열쇠

후보 키- 관계(테이블)의 최소 복합 키, 즉 여러 조건을 만족하는 속성 집합을 나타냅니다.

비환원성(Irreducibility) : 환원될 수 없으며 가능한 최소한의 속성 집합을 포함합니다.
고유성 : 행의 변화와 상관없이 고유한 값을 가져야 합니다.
값의 존재 : null 값이 없어야 합니다. 즉, 값이 있어야 합니다.

5.2 기본 키의 이상한 경우

이전 섹션에서 방금 "키"라고 불렀던 것을 일반적으로 "후보 키"라고 합니다. "후보"라는 용어는 이러한 모든 키가 "기본 키"(기본 키)의 명예 역할을 놓고 경쟁하고 나머지는 "대체 키"(대체 키)가 할당됨을 의미합니다.

SQL 구현이 키와 관계형 모델 간의 불일치를 극복하는 데 시간이 걸렸으며 초기 데이터베이스는 기본 키의 하위 수준 개념에 맞춰져 있었습니다. 이러한 데이터베이스의 기본 키는 순차 저장 매체에서 행의 물리적 위치를 식별하는 데 필요했습니다. Joe Celko가 설명하는 방법은 다음과 같습니다.

"키"라는 용어는 순차 파일 시스템에서 처리 작업을 수행하는 데 필요한 파일 정렬 키를 의미했습니다. 한 세트의 천공 카드를 한 번에 한 번만 읽었습니다. 돌아가는 것은 불가능했다. 초기 테이프 드라이브는 동일한 동작을 모방했으며 양방향 액세스를 허용하지 않았습니다. 즉, 원래 Sybase SQL Server는 이전 행을 읽기 위해 테이블을 처음으로 "되감기"해야 했습니다.

최신 SQL에서는 정보의 물리적 표현, 테이블 모델 관계에 집중할 필요가 없으며 행의 내부 순서는 전혀 중요하지 않습니다. 그러나 지금도 SQL 서버는 기본적으로 기본 키에 대한 클러스터형 인덱스를 생성하고 이전 전통에 따라 행의 순서를 물리적으로 정렬합니다.

대부분의 데이터베이스에서 기본 키는 과거의 일이며 반영 또는 물리적 위치 이상을 제공하지 않습니다. 예를 들어 PostgreSQL 테이블에서 기본 키를 선언하면 자동으로 제약 조건이 적용되고 NOT NULL기본 외래 키가 정의됩니다. 또한 기본 키는 연산자의 기본 열입니다 JOIN.

기본 키는 다른 키 선언 가능성을 무시하지 않습니다. 동시에 기본 키로 할당된 키가 없으면 테이블이 여전히 제대로 작동합니다. 어쨌든 번개는 당신을 때리지 않을 것입니다.

5.3 자연 키 찾기

위에서 논의한 키는 아무도 키를 만들고 싶어하지 않더라도 그 자체로 흥미로운 모델링된 개체의 속성이기 때문에 "자연"이라고 합니다.

가능한 자연 키에 대한 테이블을 검사할 때 기억해야 할 첫 번째 사항은 너무 영리하지 않도록 노력하는 것입니다. StackExchange의 사용자 sqlvogel은 다음과 같은 조언을 제공합니다.

일부 사람들은 특정 키가 고유하지 않을 수 있는 가상의 상황을 제시하기 때문에 "자연" 키를 선택하는 데 어려움을 겪습니다. 그들은 작업의 의미를 이해하지 못합니다. 키의 의미는 주어진 시간에 속성이 특정 테이블에서 고유해야 하고 항상 고유해야 하는 규칙을 정의하는 것입니다. 테이블에는 특정하고 잘 이해된 컨텍스트("주제 영역" 또는 "담론 영역")의 데이터가 포함되며 유일한 의미는 해당 특정 영역에 제한을 적용하는 것입니다.

연습에 따르면 열이 사용 가능한 값으로 고유하고 가능한 시나리오에서 그대로 유지될 때 키 제약 조건을 도입해야 합니다. 그리고 필요한 경우 제한을 제거할 수 있습니다(귀찮으시면 아래에서 키 안정성에 대해 설명하겠습니다.)

예를 들어, 취미 동호회 회원들의 데이터베이스는 , , 이라는 2개의 컬럼에서 고유성을 가질 수 first_name있습니다 last_name. 적은 양의 데이터를 사용하면 중복될 가능성이 없으며 실제 충돌이 발생하기 전에 이러한 키를 사용하는 것이 상당히 합리적입니다.

데이터베이스가 커지고 정보의 양이 증가함에 따라 자연 키를 선택하는 것이 더 어려워질 수 있습니다. 우리가 저장하는 데이터는 외부 현실을 단순화한 것이며, 시간이 지남에 따라 변하는 좌표와 같이 세상의 개체를 구별하는 일부 측면을 포함하지 않습니다. 개체에 코드가 없는 경우 공간 배열이나 무게 또는 포장의 약간의 차이를 제외하고 두 캔의 음료수 또는 두 상자의 오트밀을 어떻게 구분할 수 있습니까?

이것이 표준화 기관이 제품에 고유한 마크를 만들고 적용하는 이유입니다. 차량에는 차량 식별 번호(VIN)가 찍혀 있고 책에는 ISBN이 인쇄되어 있으며 식품 포장에는 UPC가 있습니다 . 이러한 수치가 자연스럽지 않다고 반박할 수 있습니다. 그렇다면 왜 자연 키라고 부릅니까?

데이터베이스에 있는 고유 속성의 자연스러움 또는 인공성은 외부 세계와 관련이 있습니다. 표준 기관이나 정부 기관에서 만들 때 인위적이었던 키는 전 세계의 표준이 되고/또는 개체에 인쇄되기 때문에 우리에게 자연스러워집니다.

통화, 언어, 금융 상품, 화학 물질 및 의료 진단을 포함하여 다양한 주제에 대한 많은 산업, 공공 및 국제 표준이 있습니다. 다음은 자연 키로 자주 사용되는 값 중 일부입니다.

ISO 3166 국가 코드
ISO 639 언어 코드
ISO 4217에 따른 통화 코드
주식 기호 ISIN
UPC/EAN, VIN, GTIN, ISBN
로그인 이름
이메일 주소
방 번호
네트워크 맥 주소
지구 표면의 지점에 대한 위도, 경도

가능하고 합리적일 때마다 키를 선언하는 것이 좋습니다. 테이블당 여러 키일 수도 있습니다. 그러나 위의 모든 항목에는 예외가 있을 수 있음을 기억하십시오.

모든 사람이 이메일 주소를 가지고 있는 것은 아니지만 일부 데이터베이스 조건에서는 허용될 수 있습니다. 또한 사람들은 때때로 이메일 주소를 변경합니다. (나중에 키 안정성에 대해 자세히 설명합니다.)
ISIN 주식 기호는 수시로 변경됩니다. 예를 들어 GOOG 및 GOOGL 기호는 회사가 Google에서 알파벳으로 개편되는 과정을 정확하게 설명하지 않습니다. 때때로 TWTR 및 TWTRQ와 마찬가지로 일부 투자자가 Twitter IPO 중에 실수로 후자를 구입하는 등 혼란이 발생할 수 있습니다.
사회 보장 번호는 미국 시민권자만 사용하고 개인 정보 보호 제한이 있으며 사후에 재사용됩니다. 또한 문서 도난 후 사람들은 새 번호를 얻을 수 있습니다. 마지막으로 동일한 번호로 사람과 소득세 식별자를 모두 식별할 수 있습니다.
우편 번호는 도시에 좋지 않은 선택입니다. 일부 도시에는 공통 색인이 있거나 그 반대의 경우 한 도시에 여러 색인이 있습니다.

5.4 인공 키

키가 각 행에 고유한 값이 있는 열이라는 점을 감안할 때 키를 만드는 한 가지 방법은 속이는 것입니다. 각 행에 가상의 고유한 값을 쓸 수 있습니다. 이들은 인공 키입니다. 즉, 데이터나 개체를 참조하는 데 사용되는 발명된 코드입니다.

코드가 데이터베이스 자체에서 생성되고 데이터베이스 사용자를 제외한 누구에게도 알려지지 않는 것이 매우 중요합니다. 이것이 인공 키와 표준화된 자연 키를 구별하는 것입니다.

자연 키는 테이블에서 중복되거나 일관되지 않은 행으로부터 보호하는 이점이 있지만 인공 키는 사람이나 다른 시스템이 행을 참조하기 쉽게 만들고 사용하지 않기 때문에 조회 및 조인 속도를 높이기 때문에 유용합니다. 문자열(또는 다중 열) 비교 키.

대리인

인공 키는 앵커로 사용됩니다. 규칙과 열이 어떻게 변경되더라도 한 행은 항상 같은 방식으로 식별될 수 있습니다. 이러한 목적으로 사용되는 인공 키를 "대리 키"라고 하며 특별한 주의가 필요합니다. 아래에서 대리자를 고려할 것입니다.

비대리 인공 키는 데이터베이스 외부에서 행을 참조하는 데 유용합니다. 인공 키는 데이터나 개체를 간략하게 식별합니다. URL로 지정하거나, 송장에 첨부하거나, 전화로 받아 적거나, 은행에서 얻거나, 번호판에 인쇄할 수 있습니다. (자동차 번호판은 우리에게는 자연적인 열쇠이지만, 정부가 인공 열쇠로 설계한 것입니다.)

오타 및 오류를 최소화하기 위해 가능한 전송 수단을 고려하여 합성 키를 선택해야 합니다. 키는 말하고, 인쇄하고, SMS를 통해 보내고, 손으로 쓰고, 키보드로 입력하고, URL에 삽입할 수 있습니다. 또한 신용 카드 번호와 같은 일부 인공 키에는 체크섬이 포함되어 있어 특정 오류가 발생하더라도 최소한 인식할 수 있습니다.

예:

미국 자동차 번호판의 경우 O, 0과 같은 모호한 문자 사용에 대한 규칙이 있습니다.
병원과 약국은 의사의 필적을 감안할 때 특히 주의해야 합니다.
문자 메시지로 확인 코드를 보내나요? GSM 03.38 문자 집합을 벗어나지 마십시오.
임의의 바이트 데이터를 인코딩하는 Base64와 달리 Base32는 사람이 이전 컴퓨터 시스템에서 사용하고 처리하기 편리한 제한된 문자 집합을 사용합니다.
Proquints는 읽고 쓸 수 있고 발음할 수 있는 식별자입니다. 이들은 명확하게 이해되는 자음과 모음의 PRO-명사 가능 QUINT-업렛입니다.

인공 키를 세상에 소개하자마자 사람들은 이상하게도 그것에 특별한 관심을 갖기 시작할 것입니다. 악명 높은 자동 저주 생성기가 된 "도둑" 번호판이나 발음 가능한 식별자 생성 시스템을 살펴보십시오.

숫자키에 국한하더라도 13층과 같은 금기가 있다. proquints는 음성 음절당 정보 밀도가 더 높지만 수신자가 키가 숫자뿐이라는 것을 알고 있는 한 URL, 핀 키보드 및 손으로 쓴 메모와 같은 여러 가지 방법으로 숫자도 괜찮습니다.

그러나 공개 숫자 키에서 순차적 순서를 사용하면 안 됩니다. 이렇게 하면 리소스(/videos/1.mpeg, /videos/2.mpeg 등)를 뒤질 수 있고 번호에 대한 정보도 유출될 수 있기 때문입니다. 데이터. 일련의 숫자에 Feistel 그물을 중첩하고 숫자의 순서를 숨기면서 고유성을 유지합니다.

추가 키 선언에 반대하는 유일한 주장은 각각의 새 키가 다른 고유 인덱스를 가져오고 테이블에 쓰는 비용을 증가시킨다는 것입니다. 물론 데이터의 정확성이 얼마나 중요한지에 따라 다르지만 키는 여전히 선언되어야 합니다.

또한 여러 개의 인공 키(있는 경우)를 선언할 가치가 있습니다. 예를 들어 조직에는 구직자(지원자)와 직원(직원)이 있습니다. 각 직원은 한때 후보였으며 직원의 키이기도 한 자체 식별자로 후보를 참조합니다. 또 다른 예로 직원 ID와 로그인 이름을 직원에 두 개의 키로 설정할 수 있습니다.

5.5 대리 키

이미 언급한 바와 같이 중요한 유형의 인공 키를 "대리 키"라고 합니다. 다른 인공 키처럼 간결하고 통과 가능할 필요는 없지만 항상 문자열을 식별하는 내부 레이블로 사용됩니다. SQL에서 사용되지만 응용 프로그램은 명시적으로 액세스하지 않습니다.

PostgreSQL의 시스템 열에 익숙하다면 서로게이트를 거의 데이터베이스 구현 매개변수(예: ctid)로 생각할 수 있지만 절대 변경되지 않습니다. 서로게이트 값은 행당 한 번 선택되며 이후에는 변경되지 않습니다.

대리 키는 외래 키로 훌륭하며 ON UPDATE RESTRICT대리 키의 불변성과 일치하도록 계단식 제약 조건을 지정해야 합니다.

반면에 공개적으로 공유된 키에 대한 외래 키는 ON UPDATE CASCADE최대 유연성을 제공하기 위해 로 표시되어야 합니다. 계단식 업데이트는 주변 트랜잭션과 동일한 격리 수준에서 실행되므로 동시성 문제에 대해 걱정하지 마십시오. 엄격한 격리 수준을 선택하면 데이터베이스에 문제가 없습니다.

대리 키를 "자연스럽게" 만들지 마십시오. 최종 사용자에게 대리 키의 값을 보여주거나 최악의 경우 해당 값으로 작업하도록 하면(특히 조회를 통해) 효과적으로 키에 값을 부여하는 것입니다. 그러면 데이터베이스에서 표시된 키가 다른 사람의 데이터베이스에서 자연 키가 될 수 있습니다.

외부 시스템이 전송을 위해 특별히 설계된 다른 인공 키를 사용하도록 강제하면 대리자와 내부 참조 무결성을 유지하면서 변화하는 요구 사항을 충족하기 위해 필요에 따라 해당 키를 변경할 수 있습니다.

자동 증가 INT/BIGINT

대리 키의 가장 일반적인 용도는 IDENTITY 라고도 하는 자동 증가 "bigserial" 열입니다 . (실제로 PostgreSQL 10은 이제 Oracle과 마찬가지로 IDENTITY 구조를 지원합니다. CREATE TABLE을 참조하십시오.)

그러나 자동 증가 정수는 대리 키에 적합하지 않은 선택이라고 생각합니다. 이 의견은 인기가 없으므로 설명하겠습니다.

직렬 키의 단점:

모든 시퀀스가 1에서 시작하여 점진적으로 증가하면 다른 테이블의 행은 동일한 키 값을 갖게 됩니다. JOIN이 옵션은 이상적이지 않습니다. 예를 들어 쿼리가 실수로 상수를 혼동하여 예기치 않은 결과를 반환 하지 않도록 테이블에서 분리된 키 집합을 사용하는 것이 좋습니다 . (또는 교집합이 없는지 확인하기 위해 서로 다른 소수의 배수에서 각 시퀀스를 구성할 수 있지만 이는 다소 힘들 것입니다.)
오늘날의 분산 SQL에서 시퀀스를 생성하라는 호출은 nextval() 전체 시스템이 제대로 확장되지 않는 결과를 초래합니다.
순차 키도 사용하는 데이터베이스에서 데이터를 사용하면 순차 값이 시스템 간에 고유하지 않기 때문에 충돌이 발생합니다.
철학적인 관점에서 볼 때 숫자의 순차적인 증가는 줄의 순서가 암시된 오래된 시스템과 관련이 있습니다. 이제 행을 정렬하려면 타임스탬프 열 또는 데이터에서 의미가 있는 항목을 사용하여 명시적으로 정렬하십시오. 그렇지 않으면 첫 번째 정규형이 위반됩니다.
약한 이유이지만 이러한 짧은 식별자는 누군가에게 말하고 싶은 유혹이 있습니다.

UUID

임의의 패턴에 따라 생성된 큰 정수(128비트)를 사용하는 다른 옵션을 살펴보겠습니다. 이러한 UUID(Universally Unique Identifier)를 생성하는 알고리즘은 두 개의 서로 다른 프로세서에서 동시에 실행되는 경우에도 동일한 값을 두 번 선택할 확률이 매우 낮습니다.

이 경우 UUID는 대리 키로 사용하기에 자연스러운 선택처럼 보입니다. 그렇지 않습니까? 고유한 방식으로 행에 레이블을 지정하려면 고유한 레이블보다 좋은 것은 없습니다!

그렇다면 왜 모두가 PostgreSQL에서 사용하지 않는 것일까요? 이에 대한 몇 가지 고안된 이유와 해결할 수 있는 하나의 논리적 이유가 있습니다. 제 요점을 설명하기 위해 벤치마크를 제시하겠습니다.

먼저, 무리한 이유에 대해 이야기하겠습니다. 일부 사람들은 UUID가 대시가 있는 전통적인 16진수 표기법으로 작성되기 때문에 문자열이라고 생각합니다 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. 실제로 일부 데이터베이스에는 압축(128비트) uuid 유형이 없지만 PostgreSQL은 2의 크기를 가지고 있습니다. bigint즉, 데이터베이스의 다른 정보 양과 비교할 때 오버헤드는 무시할 수 있습니다.

UUID는 또한 성가시다는 부당한 비난을 받지만 누가 UUID를 발음하거나 입력하거나 읽을까요? 우리는 인공 키가 표시되는 것이 타당하지만 아무도 (정의상) 대리 UUID를 볼 수 없어야 한다고 말했습니다. 시스템을 디버깅하기 위해 psql에서 SQL 명령을 실행하는 개발자가 UUID를 처리할 수 있지만 그게 전부입니다. 그리고 개발자는 주어진 경우 보다 편리한 키를 사용하여 문자열을 참조할 수도 있습니다.

UUID의 실제 문제는 미리 쓰기 로그(WAL)에 대한 전체 페이지 쓰기로 인해 고도로 무작위화된 값이 쓰기 증폭으로 이어진다는 것입니다 . 그러나 성능 저하는 실제로 UUID 생성 알고리즘에 따라 다릅니다.

쓰기 증폭을 측정해 봅시다 . 사실 문제는 오래된 파일 시스템에 있습니다. PostgreSQL이 디스크에 쓸 때 디스크의 "페이지"를 변경합니다. 컴퓨터의 전원을 끄면 대부분의 파일 시스템은 데이터가 디스크에 안전하게 저장되기 전에 쓰기 성공을 보고합니다. PostgreSQL이 이러한 작업을 순진하게 완료된 것으로 인식하면 다음 시스템 부팅 중에 데이터베이스가 손상됩니다.

PostgreSQL은 연속성을 제공하기 위해 대부분의 운영 체제/파일 시스템/디스크 구성을 신뢰할 수 없기 때문에 데이터베이스는 변경된 디스크 페이지의 전체 상태를 가능한 충돌로부터 복구하는 데 사용할 수 있는 미리 쓰기 로그에 저장합니다. UUID와 같이 고도로 무작위화된 값을 인덱싱하려면 일반적으로 서로 다른 여러 디스크 페이지가 포함되며 각 새 항목에 대해 전체 페이지 크기(일반적으로 4KB 또는 8KB)가 WAL에 기록됩니다. 이것이 소위 전체 페이지 쓰기(full-page write, FPW)입니다.

일부 UUID 생성 알고리즘(예: PostgreSQL의 uuid-ossp 확장에 있는 Twitter의 "눈송이" 또는 uuid_generate_v1())은 각 시스템에서 단조롭게 증가하는 값을 생성합니다. 이 접근 방식은 쓰기를 더 적은 수의 디스크 페이지로 통합하고 FPW를 줄입니다.

5.6 결론 및 권장 사항

다양한 유형의 키와 그 용도를 살펴보았으므로 이제 데이터베이스에서 사용하기 위한 권장 사항을 나열하겠습니다.

각 테이블에 대해:

모든 자연 키를 정의하고 선언합니다.
기본값이 인 UUID<table_name>_id 유형의 서로게이트 키를 만듭니다 . 기본 키로 표시할 수도 있습니다. 이 식별자에 테이블 이름을 추가하면 가 단순화됩니다 . 대신 받습니다 . 이 키를 클라이언트에 전달하지 말고 데이터베이스 외부에 노출하지 마십시오.uuid_generate_v1()JOINJOIN foo USING (bar_id)JOIN foo ON (foo.bar_id = bar.id)
를 통과하는 중간 테이블의 경우 JOIN모든 외래 키 열을 단일 복합 기본 키로 선언합니다.
선택적으로 URL 또는 기타 문자열 참조 표시에 사용할 수 있는 인공 키를 추가합니다. Feistel 그리드 또는 pg_hashids를 사용하여 자동 증가 정수를 마스킹합니다.
ON UPDATE RESTRICT대리 UUID를 외래 키로 사용하고 인공 외래 키에 계단식 제약 조건을 지정합니다 ON UPDATE CASCADE. 자신의 논리에 따라 자연 키를 선택하십시오.

이 접근 방식은 자연 키를 허용하고 보호하면서 내부 키의 안정성을 보장합니다. 또한 눈에 보이는 인공 키는 어떤 것에도 연결되지 않습니다. 모든 것을 올바르게 이해하면 "기본 키"에만 매달릴 수 없으며 키 사용의 모든 가능성을 사용할 수 있습니다.