mysql 자체증가 ID 소진시 해결방법

MySQL의 자체 증가 ID를 모두 사용했습니다. 어떻게 해야 하나요?

프로그래머로서 취업 면접에서 이런 문제를 겪어본 적이 있는지 궁금합니다.

Jong Zhang은 최근 인터뷰를 위해 인터넷 회사에 갔는데 면접관이 그런 질문을 했습니다.

인터뷰어: "MySQL을 사용해 본 적이 있나요? 데이터 테이블의 기본 키 ID로 자동 증가 기본 키나 UUID를 사용하시나요?"

Zhang Gong: "자동 증가 기본 키를 사용하시나요?"

인터뷰어: "왜 자동 증가 기본 키인가요? ?"

Gong Zhang: "자동 증가 기본 키를 사용하기 때문에 물리적 구조에 데이터가 순차적으로 저장되고 성능이 좋습니다."

인터뷰어: "그렇다면 자동 증가하는 기본키가 최대값에 도달했습니다. 다 썼다면 어떻게 해야 하나요? 워커: "다 소진되면 사용하고 다시 적용하세요"

인터뷰어: "가셔도 됩니다. 돌아가서 알림을 기다리세요"

오늘은 자동 증가 기본 키가 모두 사용된 경우 어떻게 해야 하는지에 대해 이야기해 보겠습니다.

mysql에서 int 정수형의 범위는 다음과 같습니다. int의 값 범위는 -2^31——2^31-1이며, 이는 -2147483648—2147483647

그림과 같습니다.

예를 들어 부호 없는 정수는 0부터 4294967295까지의 값(약 43억개)을 저장할 수 있습니다. 자동 증가된 ID가 최대값에 도달한 후 계속 삽입하면 어떤 예외가 발생하나요?

연습해 볼까요?

먼저 tb_user 테이블을 생성하세요. 이 테이블에는 자동 증가 ID만 포함되어 있습니다

create table  tb_user(id int unsigned auto_increment primary key) ;

그런 다음 이 테이블에 데이터 조각을 삽입하세요.

insert into tb_user values(null);

show 명령어를 사용하여 테이블 생성 tb_user를 확인하세요.

주의하세요. AUTO_INCREMENT가 2가 된 것을 알 수 있는데 이는 최대값인 4294967295와는 거리가 멀습니다. 4294967295가 되려면 많은 레코드를 삽입해야 합니다. 사실 꼭 그럴 필요는 없습니다. 그래서 번거롭습니다. 테이블을 생성할 때 AUTO_INCREMENT를 직접 선언할 수 있습니다.

방금 만든 테이블 생성 문을 조정하세요. 먼저 지금 테이블을 삭제한 다음, 테이블을 생성할 때 auto_increment = 4294967295

CREATE TABLE `tb_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

를 추가하세요. 그런 다음 같은 방식으로 테이블에 레코드를 삽입하세요.

create table tb_user(id int unsigned auto_increment primary key) auto_increment = 4294967295;

저희도 마찬가지로 show 명령어로 tb_user 테이블의 테이블 구조를 확인합니다.

insert into tb_user values(null);

CREATE TABLE `tb_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8

를 통해 id가 4294967295임을 쿼리하는데, 이때 테이블에 데이터를 삽입하려고 하면 이미 최대값입니다. , 기본 키 충돌 예외는 다음과 같이 보고됩니다.

select * from tb_user

이는 다시 삽입할 때 사용된 자동 증가 ID가 여전히 4294967295이고 기본 키 충돌 예외가 보고된다는 것을 보여줄 수 있습니다.

4294967295, 이 숫자는 이미 대부분의 시나리오에 대처할 수 있습니다. 서비스에서 데이터를 자주 삽입하고 삭제하는 경우 여전히 부족할 위험이 있습니다.

bigint unsigned를 사용하는 것이 좋습니다. 이 숫자는 커집니다.

그래서 해결 방법이 있나요? 대답은 '예'이고 해결 방법은 매우 간단합니다. Int 유형을 BigInt 유형으로 변경하면 다음과 같습니다

-2^63-1에서 2^63-1.

-9223372036854775808 9223372036854775807

데이터 테이블에 1초마다 10,000개의 데이터가 삽입되어 100년 동안 실행해도 데이터가 얼마나 되는지 살펴보자

10000*2 4*3600*365*100=31536000000000

이 디지털 거리는 여전히 BigInt의 상한선과 차이가 멀기 때문에 자동 증가 ID를 BigInt 유형으로 설정하면 문제를 해결할 수 있습니다.

면접 때 면접관에게 이렇게 대답한다면.

You: "이건 간단하지 않습니다. 자동 증가 기본 키 유형을 BigInt 유형으로 변경하면 해결됩니다!"

인터뷰어: "온라인에서 열의 데이터 유형을 어떻게 변경합니까?" 당신:" alter table tb_user 변경 id id bigint;"

인터뷰어: "실제 경험이 있습니까?"

당신: "...실제 경험이 없습니다"

이 방법은 단지 myl5.6+에서 지원됩니다. MySQL은 테이블을 수정하는 동안 대부분의 작업에서 원본 테이블을 읽고 쓸 수 있습니다.

데이터 유형 수정과 같은 작업에는 동시 DML 작업이 지원되지 않습니다! 즉, 온라인에서 테이블 데이터 구조를 수정하기 위해 alter와 같은 문을 직접 사용하는 경우 이 테이블은 업데이트 작업(삭제, 업데이트, 삽입)을 수행할 수 없습니다. 따라서 생산 라인에서 테이블 구조를 수정하는 것은 불가능합니다.

더 좋은 방법이 있을까요? 이 문제는 나중에 논의하겠습니다.

이러한 상황을 눈치채셨는지 궁금합니다. 기본키 자동 증가 ID는 0부터 시작하지만, 즉 현재 사용할 수 있는 범위는 0~2147483647이지만 일부 값은 실제 데이터의 ID는 연속적이지 않습니다.

실제 생산 테이블에 수억 개가 넘는 데이터가 담긴 단일 테이블이 있는데, 이때 데이터 테이블에 데이터를 쓰려고 한다면 당연히 성능에 영향을 미치게 되므로 빠르게 하위 데이터베이스와 테이블.

데이터베이스가 테이블로 분할된 경우 각 테이블의 자동 증가 ID를 사용하여 전역적으로 데이터를 고유하게 식별할 수 없습니다. 샤딩 데이터베이스 및 샤딩 테이블 환경을 지원하려면 전역적으로 고유한 ID 번호를 생성하기 위한 전략을 제공해야 합니다.

실제로는 자동 증가된 기본 키가 모두 사용될 때까지 기다릴 방법이 없습니다.

좀 더 친근한 답변은 다음과 같습니다.

인터뷰어: "그러면 자동 증가 기본 키가 최대값에 도달했습니다. 모두 사용하면 어떻게 해야 하나요?

You: 저는 이런 문제를 겪은 적이 없습니다." 자동 증가 기본 키로 int를 사용하기 때문에 일반적으로 최대값에 도달하지 않으므로 하위 테이블과 하위 데이터베이스를 고려해야 합니다.

면접관이 계속해서 쫓아다니면서 서브 데이터베이스와 서브 테이블의 핵심 사항에 대해 계속해서 질문한다면 목표한 방식으로 대답할 수 있으며 이는 해당 분야에 완전한 개발 경험이 있음을 나타냅니다. 이 인터뷰에 포인트를 추가할 수 있습니다.

위 내용은 mysql 자체증가 ID 소진시 해결방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!