MySQL에서 기본 키를 설계하는 방법

이 글은 Mysql의 기본키 이슈에 대해 설명하고, Mysql 기본키와 관련된 지식을 Why의 관점에서 이해하며, 기본키 생성 솔루션까지 확장합니다. 더 이상 MySQL에 대해 질문을 받을 때 CRUD만 아는 것에 대해 두려워할 필요가 없습니다.

1. 기본 키가 필요한 이유

데이터 레코드는 고유해야 합니다(첫 번째 정규 형식)

데이터는 조인과 연결되어야 합니다

데이터를 검색하려면 데이터베이스의 기본 인덱스가 필요합니다

다음은 말도 안되는 내용이 많기 때문에 다음 섹션으로 직접 건너뛰어도 됩니다.

“정보는 무작위 불확실성을 제거하는 데 사용됩니다.”(Shannon). 사람들은 자연과 사회로부터 서로 다른 정보를 얻고 식별하여 서로 다른 것을 구별함으로써 세상을 이해하고 변화시킬 수 있습니다. 데이터는 객관적인 사물의 속성을 반영하는 기록이며, 정보의 구체적 표현이다. 데이터가 처리된 후에는 정보가 되며, 정보를 저장하고 전송하려면 먼저 디지털 방식으로 데이터로 변환되어야 합니다. 데이터베이스는 데이터 레코드를 저장하는 데 사용됩니다. 그렇다면 기록은 결정론적(상대적) 정보이며 확실성은 고유성입니다. 첫 번째 이유는 다음과 같습니다.

1. 데이터 기록은 고유해야 합니다.

세상은 객관적인 존재와 그 관계로 구성됩니다. 데이터는 디지털 관계와 모델링된 관계에 존재합니다. 데이터 자체의 설명적 가치 외에도 데이터의 가치는 상호 연결성에 있습니다. 연결의 정확성을 얻으려면 데이터가 외부적으로 서로 연결되어야 합니다. 따라서 데이터 저장에 있어서 기본 키의 보조 역할은 존재의 두 번째 요소이기도 합니다.

2. 데이터는 관련성이 있어야 합니다.

데이터는 객관적인 현실을 설명하는 데 사용되며 그 자체로는 의미가 없습니다. 주관적인 필요에 따라 정리하고, 사람들이 사물을 이해할 수 있도록 일정한 방식으로 만족시켜야만 의미를 가질 수 있습니다. 그래서 데이터를 검색하고 정리해야 합니다. 그런 다음 기본 키의 세 번째 역할:

3. 데이터베이스의 기본 인덱스는 데이터를 검색하는 데 사용됩니다.

2. 기본 키가 너무 길어서는 안 되는 이유

이 문제의 핵심은 길이입니다. 그렇다면 긴 것보다 짧은 것의 장점은 무엇일까요? (Hey hey, 내포) - 짧고 공간을 차지하지 않습니다. 그러나 이렇게 적은 양의 디스크 공간은 전체 데이터 볼륨에 비해 미미하며 일반적으로 기본 키 열을 많이 사용하지 않습니다. 그렇다면 그 이유는 속도 때문이어야 하며 원본 데이터와는 거의 관련이 없습니다. 이로써 자연스럽게 지수와 관련이 있고, 지수 읽기와 관련이 있다는 결론이 나온다. 그렇다면 긴 기본 키가 인덱스 성능에 영향을 미치는 이유는 무엇입니까?

위는 Innodb의 인덱스 데이터 구조입니다. 왼쪽에는 기본 키로 데이터 레코드를 찾는 클러스터형 인덱스가 있습니다. 오른쪽에는 컬럼 데이터를 인덱싱하고 컬럼 데이터를 통해 데이터의 기본 키를 검색하는 보조 인덱스가 있습니다. 보조 인덱스를 통해 데이터를 쿼리하는 경우 프로세스는 그림과 같습니다. 먼저 보조 인덱스 트리에서 기본 키를 검색한 후 클러스터형 인덱스의 기본 키를 통해 데이터 행을 검색합니다. 보조 인덱스의 리프 노드는 기본 키 포인터가 아닌 기본 키 값이 직접 저장됩니다. 따라서 기본 키가 너무 길면 보조 인덱스 트리에 저장할 수 있는 인덱스 레코드 수가 줄어들게 되고, 이와 같이 제한된 인덱스 버퍼에서는 디스크 읽기 횟수가 늘어나 성능이 저하됩니다. .

3. 자동 증가 ID를 권장하는 이유

InnoDB는 위 그림과 같이 데이터 레코드 자체가 메인 인덱스의 리프 노드(B+Tree)에 저장됩니다. ). 이를 위해서는 동일한 리프 노드(메모리 페이지 또는 디스크 페이지 하나의 크기)에 있는 각 데이터 레코드가 기본 키 순서로 저장되어야 하므로 새 레코드가 삽입될 때마다 MySQL은 이를 기본 키를 기반으로 적절한 노드에 삽입합니다. . 및 위치, 페이지가 로드 비율(InnoDB 기본값은 15/16)에 도달하면 새 페이지(노드)가 열립니다.

테이블이 자동 증가 기본 키를 사용하는 경우 새 레코드가 삽입될 때마다 레코드는 현재 인덱스 노드의 후속 위치에 순서대로 추가됩니다. 페이지가 가득 차면 새 페이지가 추가됩니다. 자동으로 열렸습니다. 그 결과 대략 순차적으로 채워지는 컴팩트한 인덱스 구조가 만들어집니다. 기존 데이터를 삽입할 때마다 이동할 필요가 없기 때문에 아래 그림의 왼쪽과 같이 매우 효율적이며 인덱스 유지에 많은 오버헤드를 추가하지 않습니다. 그렇지 않으면 매번 삽입되는 기본 키의 값이 대략 무작위이기 때문에 각각의 새로운 레코드를 기존 인덱스 페이지의 중간 어딘가에 삽입해야 하며, MySQL은 새 레코드를 적절한 위치에 삽입하기 위해 데이터를 이동해야 합니다. , 아래 그림의 오른쪽과 같이 이로 인해 일정량의 오버헤드가 발생합니다. 이로 인해 Mysql은 인덱스를 유지하기 위해 버퍼를 자주 새로 고쳐야 할 수 있으며, 이로 인해 메소드 디스크 IO 수가 증가하고 인덱스 구조를 재구성해야 하는 경우가 많습니다.

4. 비즈니스 키 VS 논리 키

비즈니스 키, 즉 주문 일련번호를 주문 테이블의 기본 키로 사용하는 등 비즈니스 의미가 있는 ID를 키로 사용하는 것입니다. Logical Key, 즉 업무와 관련이 없는 Key는 Auto-incrementing Key 등 특정 규칙에 따라 Key를 생성합니다.

비즈니스 키의 장점

키는 비즈니스적 의미를 가지며 쿼리에서 검색 키워드로 직접 사용할 수 있습니다.

추가 열과 인덱스 공간이 필요하지 않습니다

일부 조인 작업을 줄일 수 있습니다.

비즈니스 키의 단점

비즈니스가 변경되면 기본 키를 변경해야 하는 경우도 있습니다.

여러 열의 키가 포함될 때 작동이 더 어렵습니다

비즈니스 키가 더 길고 더 많은 공간을 차지하는 경우가 많습니다. , 결과적으로 디스크 IO가 증가합니다

키가 결정되기 전에는 데이터를 유지할 수 없습니다. 때로는 데이터 키를 결정하지 않은 경우 먼저 레코드를 추가한 다음 비즈니스 키를 업데이트하려고 합니다.

쉽고 쉬운 키 생성 솔루션을 설계하는 것은 어렵습니다. 사용 및 성능

논리 키의 장점

비즈니스 변경으로 인해 키 논리를 수정할 필요가 없습니다

간단한 조작과 관리 용이

논리 키는 크기가 더 작고 성능이 더 좋습니다

논리 키는 사용하기 더 쉽습니다. 고유성 보장

더 쉬운 최적화

논리 키 단점

기본 키 열과 기본 키 인덱스를 쿼리하려면 추가 디스크 공간이 필요함

데이터 삽입 및 업데이트 시 추가 IO 필요

더 많은 조인 가능

고유성 정책이 없는 경우 제한 사항, 중복 키가 생기기 쉽습니다

테스트 환경과 공식 환경 키가 일치하지 않아 문제 해결에 도움이 되지 않습니다.

키 값이 데이터와 연관되지 않으며 세 가지 패러다임을 준수하지 않습니다.

키워드 검색에 사용할 수 없습니다.

다른 데이터베이스 시스템의 특정 구현에 따라 다르며, 이는 기본 데이터베이스 교체에 도움이 되지 않습니다.

5. 기본 키 생성

일반적인 상황에서 우리 모두는 MySQL의 자동 증가 ID를 테이블의 기본 키로 사용하는 것은 매우 간단하며, 위에서 언급한 대로 성능도 가장 좋습니다. 그러나 하위 데이터베이스 및 하위 테이블의 경우 자동 증가 ID는 요구 사항을 충족할 수 없습니다. 다양한 데이터베이스가 ID를 생성하는 방법을 살펴보고 일부 분산 ID 생성 솔루션도 살펴볼 수 있습니다. 우리만의 분산 ID 생성 서비스를 고민하고 구현하는 것도 도움이 됩니다.

데이터베이스 구현

Mysql 자동 증가

Mysql은 자동 증가 카운터에 액세스할 때마다 자동 증가 카운터를 유지 관리하며 InnoDB는 명령문이 끝날 때까지 AUTO-INC라는 잠금을 추가합니다. (잠금은 트랜잭션이 끝나지 않고 명령문이 끝날 때까지만 유지됩니다.) AUTO-INC 잠금은 auto_increment가 포함된 열의 동시 삽입 가능성을 향상시키는 데 사용되는 특수 테이블 수준 잠금입니다.

분산 상황에서는 실제로 별도의 서비스와 데이터베이스를 사용하여 ID를 생성할 수 있으며 여전히 Mysql의 테이블 ID 자동 증가 기능을 사용하여 타사 서비스에 대한 ID를 균일하게 생성할 수 있습니다. 성능상의 이유로 비즈니스마다 다른 테이블을 사용할 수 있습니다.

Mongodb ObjectId

Mongodb는 기본 키 충돌을 방지하기 위해 ObjectId를 기본 키 ID로 설계했습니다. 12바이트 16진수 숫자로 구성되며 다음 부분이 포함됩니다:

시간: 타임스탬프. 4바이트. 초.

기계: 기계 식별. 3바이트. 일반적으로 이는 시스템 호스트 이름의 해시 값입니다. 이렇게 하면 서로 다른 호스트가 서로 다른 시스템 해시 값을 생성하여 배포에 충돌이 없고 동일한 시스템이 동일한 값을 갖도록 보장합니다.

PID: 프로세스 ID. 2바이트. 위의 Machine은 서로 다른 머신에서 생성된 objectId가 충돌하지 않도록 하기 위한 것이고, pid는 동일한 머신에서 서로 다른 mongodb 프로세스에서 생성된 objectId가 충돌하지 않도록 하기 위한 것입니다.

INC: 자체 증가 카운터. 3바이트. 처음 9바이트는 1초 내에 서로 다른 시스템에서 서로 다른 프로세스에 의해 생성된 objectId가 충돌하지 않도록 보장합니다. 자체 증가 카운터는 동일한 초 내에 생성된 objectId가 충돌하지 않도록 하는 데 사용됩니다. 256의 세 번째 거듭제곱은 동일합니다. 16777216개의 항목을 기록합니다.

Cassandra TimeUUID

Cassandra는 다음 규칙을 사용하여 고유 ID를 생성합니다: 시간 + MAC + 시퀀스

Scheme

Zookeeper 자동 증가: zk의 자동 증가 메커니즘을 통해 구현됩니다.

Redis 자동 증가: Redis의 자동 증가 메커니즘을 통해 구현됩니다.

UUID: UUID 문자열을 키로 사용합니다.

눈송이 알고리즘: Mongodb 구현과 유사하며 1개의 부호 비트 + 41개의 타임스탬프(밀리초 수준) + 10개의 데이터 기계 비트 + 밀리초 내의 12개 시퀀스입니다.

오픈 소스 구현

Baidu UidGenerator: 눈송이 알고리즘을 기반으로 합니다.

Meituan Leaf: 또한 Mysql 자동 증가(최적화) 및 눈송이 알고리즘을 기반으로 하는 메커니즘을 구현합니다.

위 내용은 MySQL에서 기본 키를 설계하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!