기본 키 – GUID 또는 자동 증가

09.04.2019

새 레코드의 자동 증가는 테이블에 고유한 숫자를 생성합니다.

필드 자동 증가

일반적으로 기본 키 필드 값이 자동으로 생성될 때마다 새 레코드를 삽입하려고 합니다.

테이블에 자동 증가 필드를 만들 수 있습니다.

MySQL 구문

테이블 사람 만들기
ID int NOT NULL AUTO_INCREMENT,

이름 varchar(255),
주소 varchar(255),
도시 varchar(255),
기본 키(ID)
)

MySQL은 AUTO_INCREMENT 키워드를 사용하여 자동 증가를 수행합니다.

기본적으로 AUTO_INCREMENT의 초기 값은 1이며 새 항목이 있을 때마다 1씩 증가합니다.

AUTO_INCREMENT 시퀀스가 다른 값으로 시작되도록 하려면 다음 SQL 구문을 사용하십시오.

ALTER TABLE 인원 AUTO_INCREMENT=100

VALUES ("라스","몬센")

SQL Server 구문

"ID" 열에 있는 "Persons" 테이블의 다음 SQL 문은 자동 증가 기본 키 필드로 정의됩니다.

테이블 사람 만들기
ID int IDENTITY(1,1) 기본 키,
성 varchar(255) NOT NULL,
이름 varchar(255),
주소 varchar(255),
도시 바르샤르(255)
)

MS SQL Server는 IDENTITY 키워드를 사용하여 자동 증가를 수행합니다.

위의 예에서 ID 값은 1부터 시작하고 새 항목마다 1씩 증가합니다.

조언:원래 10단계와 5단계에 "ID" 열을 지정하려면 사람(10,5)을 식별하십시오.

"Persons" 테이블에 새 레코드를 삽입하기 위해 사전 정의된 값을 갖는 "ID" 열이 필요하지 않습니다(자동으로 고유한 값을 추가함).

개인(이름, 성)에 삽입
VALUES ("라스","몬센")

위의 SQL 문은 Persons 테이블에 새 레코드를 삽입합니다. "ID" 열에는 고유한 값이 할당됩니다. "FirstName" 열은 "Lars"로 설정되고, "LastName" 열은 "MONSEN"으로 설정됩니다.

액세스 구문

"ID" 열에 있는 "Persons" 테이블의 다음 SQL 문은 자동 증가 기본 키 필드로 정의됩니다.

테이블 사람 만들기
ID 정수 기본 키 자동 증가,
성 varchar(255) NOT NULL,
이름 varchar(255),
주소 varchar(255),
도시 바르샤르(255)
)

MS Access에서는 AUTOINCREMENT 키워드를 사용하여 자동 증가를 수행합니다.

기본적으로 AUTOINCREMENT의 초기 값은 1이며 새 항목이 있을 때마다 1씩 증가됩니다.

조언:"ID" 열을 10과 5 단위로 원래 열로 지정하려면 자동 증가를 자동 증가(10.5)로 설정하세요.

"Persons" 테이블에 새 레코드를 삽입하기 위해 사전 정의된 값을 갖는 "ID" 열이 필요하지 않습니다(자동으로 고유한 값을 추가함).

개인(이름, 성)에 삽입
VALUES ("라스","몬센")

Oracle 구문

Oracle에서는 코드가 좀 더 복잡합니다.

일련의 숫자를 생성하는 시퀀스 객체에서 자동 증가 필드를 생성해야 합니다.

다음 CREATE SEQUENCE 구문을 사용하세요.

시퀀스 생성 seq_person
최소값 1
1부터 시작하세요
1씩 증가
캐시 10

위 코드는 seq_person이라는 시퀀스 객체를 생성하는데, 이는 1에서 시작하여 1씩 증가합니다. 10개의 객체 캐시 값을 사용하여 성능을 향상시킵니다. 캐시 옵션은 저장될 시퀀스 값의 수에 대한 빠른 액세스를 제공합니다.

Persons 테이블에 새 레코드를 삽입하려면 NEXTVAL 함수를 사용해야 합니다(이 함수는 seq_person 시퀀스에서 다음 값을 반환합니다).

개인(ID, 이름, 성)에 삽입
VALUES (seq_person.nextval,"Lars","Monsen")

위의 SQL 문은 Persons 테이블에 새 레코드를 삽입합니다. "ID" 열에는 seq_person의 순서대로 다음 번호가 할당됩니다. "FirstName" 열은 "Lars"로 설정되고, "LastName" 열은 "MONSEN"으로 설정됩니다.

프로그램 작성

개발자가 데이터 모델을 생성할 때 기본 키 유형이 "습관에 따라" 선택되는 경우가 많으며, 대부분 자동 증가 정수 필드입니다. 그러나 실제로는 이것이 항상 최적의 솔루션은 아닙니다. 어떤 상황에서는 GUID가 더 바람직할 수 있기 때문입니다. 실제로는 더 희귀한 다른 유형의 키도 가능하지만 이 기사에서는 이에 대해 고려하지 않습니다.

다음은 각 옵션의 이점입니다.

자동 증가

부피를 적게 차지
이론적으로 새로운 가치의 생성이 더 빨라짐
더 빠른 역직렬화
번호를 기억하기가 훨씬 쉽기 때문에 디버깅 및 지원 중에 작동하기가 더 쉽습니다.

GUID

새 레코드가 둘 이상의 복제본에 추가되는 여러 데이터베이스 인스턴스 간 복제 시 GUID는 충돌 없음을 보장합니다.
데이터베이스에 저장하기 전에 클라이언트에서 레코드 식별자를 생성할 수 있습니다.
첫 번째 요점의 일반화 - 하나의 테이블 내에서뿐만 아니라 일부 솔루션에 중요할 수 있는 식별자의 고유성을 보장합니다.
식별자를 일부 공개 API에 전달하여 레코드를 얻을 수 있는 경우 키를 "추측"하는 것이 거의 불가능합니다.

"이론적으로 새로운 의미의 생성이 더 빠르다"는 말은 설득력이 없어 보입니다. 실제 사례를 통해 이러한 고려 사항을 뒷받침하는 것이 항상 더 좋습니다. 그러나 테스트용 프로그램을 작성하기 전에 이 두 가지 유형 각각에 기본 키를 구현하는 옵션이 무엇인지 고려해 보겠습니다.

GUID는 클라이언트와 데이터베이스 자체 모두에서 생성될 수 있습니다. 두 가지 옵션이 있습니다. 또한 MS SQL에는 고유 식별자를 얻는 두 가지 함수(NEWID 및 NEWSEQUENTIALID)가 있습니다. 그들의 차이점이 무엇인지, 그리고 그것이 실제로 중요한지 알아 봅시다.

Guid.NewGuid()를 통해 .NET에서 일반적인 고유 식별자 생성은 어떤 패턴으로도 서로 관련되지 않은 많은 값을 제공합니다. 이 함수에서 얻은 다수의 GUID가 정렬된 목록에 보관되면 각각의 새로 추가된 값이 목록의 어느 부분에든 "들어갈" 수 있습니다. MS SQL의 NEWID() 함수도 비슷하게 작동합니다. 그 값 중 상당수가 매우 혼란스럽습니다. 결과적으로 NEWSEQUENTIALID()는 동일한 고유 식별자를 제공하며 이 함수의 각 새 값만 이전 값보다 크고 식별자는 "전역적으로 고유"하게 유지됩니다.

Entity Framework Code First를 사용하고 다음과 같이 기본 키를 선언하는 경우

공개 GUID ID( get; set; )
기본값이 NEWSEQUENTIALID()인 기본 클러스터 키를 사용하여 데이터베이스에 테이블이 생성됩니다. 이는 성능상의 이유로 수행되었습니다. 이론적으로는 목록 중간에 새 값을 삽입하는 것이 목록 끝에 추가하는 것보다 비용이 더 많이 듭니다. 물론 데이터베이스는 메모리 내 배열이 아니며 행 목록 중간에 새 항목을 삽입해도 모든 후속 항목이 물리적으로 이동되지는 않습니다. 그러나 추가 오버헤드(페이지 분할)가 발생합니다. 결과적으로 인덱스가 심하게 조각화되어 데이터 검색 성능에 영향을 미칠 수도 있습니다. 클러스터링된 테이블에 데이터가 삽입되는 방법에 대한 자세한 설명은 이 링크의 포럼 답변에서 찾을 수 있습니다.

따라서 GUID의 경우 성능 측면에서 분석할 가치가 있는 4가지 옵션이 있습니다. 클라이언트에서 생성되는 순차 및 비순차 GUID와 동일한 옵션 쌍이지만 데이터베이스 측에서 생성되는 옵션입니다. 문제는 클라이언트에서 순차적 GUID를 얻는 방법입니다. 안타깝게도 .NET에는 이 목적을 위한 표준 기능이 없지만 P/Invoke를 사용하여 수행할 수 있습니다.

내부 정적 클래스 SequentialGuidUtils ( public static Guid CreateGuid() ( Guid guid; int result = NativeMethods.UuidCreateSequential(out guid); if (result == 0) ( var bytes = guid.ToByteArray(); var indexes = new int ( 3 , 2, 1, 0, 5, 4, 7, 6, 8, 9, 10, 11, 12, 13, 14, 15 ); return new Guid(indexes.Select(i => bytes[i]).ToArray ()); ) else throw new Exception("순차적 GUID 생성 중 오류 발생"); 내부 정적 클래스 NativeMethods( public static extern int UuidCreateSequential(out Guid guid); )
특별한 바이트 재배열 없이는 GUID를 제공할 수 없습니다. 식별자는 정확하지만 SQL 서버의 관점에서 볼 때 일관성이 없으므로 이론적으로도 "일반" GUID에 비해 이점이 없습니다. 불행하게도 잘못된 코드는 여러 소스에서 제공됩니다.

자동 증가 기본 키인 다섯 번째 옵션을 목록에 추가해야 합니다. 클라이언트에서는 정상적으로 생성할 수 없으므로 다른 옵션이 없습니다.

옵션을 결정했지만 테스트를 작성할 때 고려해야 할 매개변수가 하나 더 있습니다. 바로 테이블 행의 물리적 크기입니다. MS SQL의 데이터 페이지 크기는 8KB입니다. 유사하거나 훨씬 더 큰 크기의 레코드는 한 단계 더 작은 레코드보다 각 주요 옵션에 대한 성능 차이가 더 클 수 있습니다. 레코드 크기를 변경하는 기능을 제공하려면 각 테스트 테이블에 NVARCHAR 필드를 추가하면 충분합니다. 그런 다음 필요한 문자 수로 채워집니다(NVARCHAR 필드의 한 문자는 2바이트를 차지합니다).

테스트

이 링크에는 위의 고려 사항을 고려하여 개발된 프로그램이 포함된 프로젝트가 포함되어 있습니다.

다음은 이 계획에 따라 수행된 테스트 결과입니다.

레코드의 텍스트 필드 길이가 각각 80, 800 및 8000바이트인 세 가지 일련의 테스트만 있습니다(NVARCHAR의 한 문자가 차지하므로 테스트 프로그램의 문자 수는 각 경우의 절반이 됩니다). 2바이트).
각 계열에는 5개의 실행이 포함되어 있으며 각 실행은 각 테이블에 10,000개의 레코드를 추가합니다. 각 실행의 결과를 기반으로 테이블에 이미 있는 행 수에 대한 삽입 시간의 의존성을 추적하는 것이 가능합니다.
각 시리즈가 시작되기 전에 테이블이 완전히 지워집니다.

그리고 각 실행별로 분석된 결과는 다음과 같습니다.

결과를 통해 다음 사항이 즉시 명확해졌습니다.

데이터베이스 측에서 GUID 생성을 사용하는 것은 클라이언트 측에서 생성하는 것보다 몇 배 더 느립니다. 이는 새로 추가된 식별자를 읽는 오버헤드 때문입니다. 이 문제에 대한 자세한 내용은 기사 끝부분에서 논의됩니다.
자동 증가 키를 사용하여 레코드를 삽입하는 것은 클라이언트에 할당된 GUID를 사용하는 것보다 약간 느립니다.
순차 GUID와 비순차 GUID의 차이는 작은 항목에서는 거의 눈에 띄지 않습니다. 큰 레코드에서는 테이블의 행 수가 증가함에 따라 차이가 나타나지만 유의미한 것으로 보이지는 않습니다.

마지막 요점은 생각해 볼 가치가 있습니다. 빈번한 페이지 분할 외에 일관되지 않은 GUID를 사용할 때 속도 저하를 일으킬 수 있는 것은 무엇입니까? 디스크에서 "무작위" 페이지를 자주 읽는 것이 원인일 가능성이 높습니다. 순차 GUID를 사용하는 경우 추가는 인덱스의 끝으로만 이동하므로 원하는 페이지는 항상 메모리에 있습니다. 불일치를 사용하면 임의의 인덱스 위치에 많은 삽입이 발생하며 모든 경우에 필요한 페이지가 메모리에 있는 것은 아닙니다. 이러한 무작위 읽기가 테스트 결과에 얼마나 영향을 미치는지 테스트하기 위해 어떤 테이블도 메모리에 완전히 들어갈 수 없도록 SQL Server 메모리 양을 인위적으로 제한합니다.

대략적인 계산에 따르면 행 길이가 4000자(8000바이트)이고 레코드 수가 50,000,000개인 테스트에서 테이블 크기는 최소 400MB가 됩니다. 허용되는 SQL Server 메모리 양을 256MB로 제한하고 이 테스트를 반복해 보겠습니다.

예상할 수 있듯이 키의 GUID가 일관되지 않은 테이블에 삽입하면 속도가 눈에 띄게 느려지고 테이블의 행 수가 늘어날수록 속도가 느려집니다. 동시에 순차 GUID 및 자동 증가에 대한 삽입 성능은 안정적으로 유지됩니다.

결론

문서 시작 부분에 지정된 일부 기준에 따라 GUID를 기본 키로 사용해야 하는 경우 성능 측면에서 가장 좋은 옵션은 클라이언트의 각 레코드에 대해 생성된 순차 GUID입니다.
어떤 이유로 클라이언트에서 GUID를 생성할 수 없는 경우 NEWSEQUENTIALID()를 통해 데이터베이스 측에서 식별자 생성을 사용할 수 있습니다. Entity Framework는 데이터베이스 측에서 생성된 GUID 키에 대해 기본적으로 이 작업을 수행합니다. 하지만 클라이언트 측에서 식별자를 생성하는 것에 비해 삽입 성능이 눈에 띄게 낮다는 점을 명심하세요. 테이블에 대한 삽입 수가 적은 프로젝트의 경우 이 차이는 중요하지 않습니다. 또한 삽입된 레코드의 식별자를 즉시 얻을 필요가 없는 시나리오에서는 이 오버헤드를 피할 수 있지만 이러한 솔루션은 보편적이지 않습니다.
프로젝트에서 이미 일관되지 않은 GUID를 사용하고 있는 경우, 테이블 삽입 수가 많고 데이터베이스 크기가 사용 가능한 RAM 크기보다 훨씬 크다면 이를 수정하는 것을 고려해야 합니다.
다른 DBMS의 경우 성능 차이가 완전히 다를 수 있으므로 얻은 결과는 Microsoft SQL Server와 관련해서만 고려할 수 있습니다. 기사 시작 부분에 명시된 기본 기준은 특정 DBMS에 관계없이 유효합니다.

UPD: 데이터베이스 측에서 GUID 키를 생성하는 옵션이 느린 이유는 무엇입니까?

Entity Framework가 자동 증가 키를 사용하여 테이블에 삽입을 수행할 때 명령의 SQL은 다음과 같습니다.
INSERT .(, ) 값 (@0, @1) 에서 선택 . @@ROWCOUNT > 0 AND = 범위_식별()
서버 측에서 생성된 GUID의 경우 더 복잡한 옵션이 제공됩니다.
DECLARE @generated_keys 테이블(uniqueidentifier) INSERT .(, ) OUTPUT이 삽입되었습니다. INTO @generated_keys VALUES (@name, @count) SELECT t. FROM @generated_keys AS g JOIN . AS t ON g. = 티. @@ROWCOUNT > 0인 곳
따라서 클라이언트가 새로 추가된 행의 키를 받을 수 있도록 삽입 시 키 값이 입력되는 테이블 변수를 생성한 후 이 테이블 변수에서 선택을 수행하여 결과 값을 클라이언트에 반환합니다. 고객.

간단한 테스트를 통해 이 명령 세트가 동일한 테이블을 단순히 INSERT하는 것보다 평균적으로 거의 3배 느린 것으로 나타났습니다. 자동 증가의 경우 추가 테이블 변수가 사용되지 않으므로 오버헤드가 더 적습니다.

"*.mdf" 파일에 있는 MS SQL Server 데이터베이스 테이블에 자동 증가 필드(카운터) 생성

이 항목에서는 "MyDataBase.mdf" 파일에 있는 Microsoft SQL Server 데이터베이스 테이블에 카운터 필드(고유 필드)를 만드는 방법을 보여줍니다.

이 항목은 이전 항목을 기반으로 합니다.

작업

Microsoft SQL Server 관계형 데이터베이스 관리 시스템에서 작동하도록 설계된 데이터베이스 파일 "MyDatabase.mdf" 및 "MyDataBase.ldf"가 지정됩니다. 파일은 아카이브에서 다운로드할 수 있습니다.

데이터베이스에는 Source라는 테이블이 하나 포함되어 있습니다. 소스 테이블에는 다음 필드가 포함되어 있습니다.

ID_Source - 정수형(int);
이름 – 50자의 "문자열" 유형입니다.
주소는 100자로 구성된 문자열입니다.

작업에서 ID_Source 필드를 고유한 카운터로 만들어야 합니다. 테이블에 새 레코드를 추가할 때 필드 값은 1씩 증가해야 합니다(자동 증가 필드). 즉, 고유해야 합니다.

성능

1. MS Visual Studio에서 “*.mdf” 데이터베이스 파일 연결

MS 비주얼 스튜디오를 실행하세요.

서버 탐색기 유틸리티를 사용하여 이전에 생성된 파일 “MyDataBase.mdf” 및 “MyDataBase.ldf”를 연결해야 합니다. 파일이 포함된 아카이브를 다운로드할 수 있습니다.

파일을 동일한 디렉터리에 배치하는 것이 좋습니다.
미리 만들어진(이전에 생성된) "*.mdf" 로컬 데이터베이스 파일을 MS Visual Studio에 추가하는 예는 다음 문서에 자세히 설명되어 있습니다.

데이터베이스를 연결하면 "MyDataBase.mdf" 데이터베이스가 서버 탐색기 창에 표시됩니다(그림 1).

데이터베이스에는 작업 조건에 따른 필드가 포함된 하나의 Source 테이블(그림 2)이 포함되어 있습니다.

쌀. 1. 서버 탐색기 창의 데이터베이스 MyDataBase.mdf

쌀. 2. 소스 테이블

2. ID_Source 필드를 카운터로 구성합니다. 속성 "ID 열"

작업 조건에 따라 ID_Source 필드는 고유할 수 있습니다. 최신 데이터베이스는 고유한 필드를 지원합니다. 즉, 새 레코드가 데이터베이스에 추가되면 새 고유 값이 자동으로 생성됩니다. 일반적으로 정수 유형에 대한 새 항목을 추가할 때 새 고유 값은 이전 고유 값에 비해 1씩 증가합니다(선택 사항). 카운터(고유 값)가 있는 필드 항목의 값을 수동으로 또는 프로그래밍 방식으로 변경할 수 없습니다. 이 모든 작업은 데이터베이스 관리 시스템에 의해 인계됩니다.

ID_Source 필드를 고유하게 설정하려면 다음을 수행해야 합니다.

MyDataBase.mdf 데이터베이스에서 테이블 탭을 엽니다(그림 3).
테이블 탭에서 마우스 오른쪽 버튼을 클릭하고 상황에 맞는 메뉴에서 "테이블 정의 열기" 명령을 호출합니다(그림 3). 결과적으로 테이블 필드를 정의하는 창이 열립니다.
ID_Source 필드를 활성화하고 "속성" 창에서 속성 값을 설정합니다. "ID 열 = ID_Source"(그림 4);
소스 테이블을 저장하고 닫습니다.

쌀. 3. "테이블 정의 열기" 명령

쌀. 4. ID 열 속성을 ID_Source로 설정

완료되면 ID_Source 필드가 자동으로 고유한 정수 값을 생성합니다.

이제 프로젝트에서 이 테이블을 사용할 수 있습니다.

3. 테이블을 데이터로 채우기

테이블의 ID_Source 필드를 고유 카운터로 설정한 후 프로그래밍 방식으로 또는 수동으로 테이블을 데이터(레코드)로 채울 수 있습니다.

테이블을 데이터(레코드)로 채우려면 다음을 수행해야 합니다.

소스 테이블을 마우스 오른쪽 버튼으로 클릭하고 컨텍스트 메뉴에서 "테이블 데이터 표시" 명령을 선택하여 컨텍스트 메뉴를 호출합니다(그림 5). 결과적으로 테이블에 데이터를 입력하는 창이 열립니다(그림 6).
소스 테이블에 데이터를 입력합니다. ID_Source 필드는 카운터이므로 이 필드에 데이터를 입력할 수 없습니다. 이름 및 주소 필드에만 데이터를 입력할 수 있습니다. 소스 필드 값이 자동으로 생성됩니다.

쌀. 5. "테이블 데이터 표시" 명령

쌀. 6. 소스 테이블에 데이터 입력