MySQL의 자동 증가 ID 부족 문제를 해결하는 방법에 대한 심층 분석

이 글은 mysql에 대한 관련 지식을 제공하며, ID 자체 증가와 관련된 문제를 주로 소개합니다. ID에는 상한선이 있기 때문에 항상 소진될 때가 있습니다. . ID를 다 사용했다면 어떻게 해야 할까요? 모두에게 도움이 되기를 바랍니다.

추천 학습: mysql 동영상 튜토리얼

최근에 이런 인터뷰 질문을 봤습니다. MySQL의 자동 증가 ID가 부족합니다. 어떻게 해야 하나요? 이 인터뷰 질문에 대한 해결책은 다음과 같습니다.

MySQL을 사용했거나 이해했다면 기본 키 자동 증가에 대해 알아야 합니다. 각 자동 증가 ID에는 초기 값이 정의되어 있으며 지정된 단계 크기(기본 단계 크기는 1)에 따라 증가합니다. 자연수에는 상한선이 없지만 테이블 구조를 설계할 때 일반적으로 필드 길이를 지정하므로 이때 ID에는 상한선이 있습니다. 상한선이 있기 때문에 항상 소진되는 시기가 있기 마련입니다. 오늘 함께 배워볼까요?

자동 증가 ID

자동 증가 ID에 관해 말하자면, 테이블 구조를 설계할 때 첫 번째 반응은 자동 증가 ID 필드를 사용자 정의하는 것이어야 합니다. 그러면 고유한 기본 키가 있을 수 있습니다. 데이터 삽입 시 SQL 트랜잭션 롤백, 일괄 삽입, 일괄 애플리케이션 자동 증가 값 및 기타 이유로 인해 자동 증가 ID가 불연속됩니다.

온라인 상태가 된 후 표에 정의된 자체 증가 값의 논리는 다음 ID를 신청할 때 동일한 값(최대 값)을 얻게 된다는 것입니다. SQL을 삽입하여 ID를 최대값으로 설정한 다음 ID를 적극적으로 설정하지 않는 문을 삽입하여 이 결론을 확인할 수 있습니다. 이때 다시 삽입하면 기본키 충돌이 발생한다고 합니다~

주의사항: 232-1(4294967295)은 특별히 큰 숫자는 아니지만, 데이터 삽입과 삭제가 빈번한 테이블에서는 사용할 수 있습니다. 위로. 따라서 테이블을 구축할 때 테이블이 이 상한에 도달할 가능성이 있는지 확인해야 합니다. 가능하다면 8바이트 bigint unsigned로 생성해야 합니다.

InnoDB 시스템은 자동으로 row_id를 증가시킵니다.

만약 생성한 InnoDB 테이블이 기본 키를 지정하지 않은 경우 InnoDB는 길이가 6바이트인 보이지 않는 row_id를 생성합니다. InnoDB는 기본 키가 없는 모든 InnoDB 테이블에 대해 데이터 행이 삽입될 때마다 현재 dict_sys.row_id 값이 삽입할 데이터의 row_id로 사용되며 그 다음에는 dict_sys 값이 사용됩니다. .row_id가 1씩 증가합니다.

실제로 코드가 구현되면 row_id는 길이가 8바이트인 부호 없는 긴 정수(bigint unsigned)입니다. 하지만 InnoDB를 설계할 때는 row_id 길이가 6바이트만 남았는데, 이렇게 데이터 테이블에 쓸 때 마지막 6바이트만 넣었기 때문에 쓸 수 있는 row_id 값은 2개가 된다. 데이터 테이블 기능:

row_id 테이블에 기록된 값 범위는 0에서 248-1입니다.

dict_sys.row_id=2^48일 때 데이터를 삽입하는 다른 동작이 있는 경우 적용해야 합니다. row_id에 대해 가져오고 마지막 6바이트를 나중에 가져오면 0이 됩니다.

2^48이라는 숫자는 이미 매우 큰 숫자이지만 시스템이 오랫동안 실행될 수 있으므로 여전히 상한선에 도달할 수 있다는 점을 모두가 알아야 합니다. 이때 다시 신청하면 원래 기록을 덮어쓰게 됩니다. 그러므로 이 옵션을 선택하지 마십시오!

Xid

MySQL에서 redo log와 binlog를 결합하면 Xid라는 공통 필드가 있습니다. MySQL에서 트랜잭션에 대응하는데 사용됩니다.

MySQL은 내부적으로 전역 변수 global_query_id를 유지 관리하며, 명령문이 실행될 때마다 이 변수가 Query_id에 할당되고 이 변수가 1씩 증가됩니다. 현재 명령문이 이 트랜잭션에 의해 실행된 첫 번째 명령문인 경우 MySQL은 이 트랜잭션의 Xid에 Query_id도 할당합니다. Global_query_id는 순수 메모리 변수이며 다시 시작하면 지워집니다. 따라서 동일한 데이터베이스 인스턴스에서 서로 다른 트랜잭션의 XID가 동일할 수 있습니다.

Innodb trx_id

InnoDB는 내부적으로 max_trx_id 전역 변수를 유지 관리합니다. 새로운 trx_id를 적용해야 할 때마다 max_trx_id의 현재 값을 얻은 다음 max_trx_id가 1씩 증가합니다.

InnoDB 데이터 가시성의 핵심 아이디어는 데이터의 각 행이 이를 업데이트하는 trx_id를 기록한다는 것입니다. 트랜잭션이 데이터 행을 읽을 때 데이터가 표시되는지 확인하는 방법은 일관성 보기를 사용하는 것입니다. 트랜잭션과 행 데이터의 trx_id를 비교합니다. 하지만 이 과정에서 더티 읽기(dirty read)가 발생하므로 ID가 원자적이지 않으며 중복 가능성이 있습니다.

thread_id

사실 스레드 ID는 MySQL에서 가장 일반적인 자체 증가 ID입니다. 일반적으로 다양한 장면을 확인할 때 show processlist의 첫 번째 열은 thread_id입니다.

thread_id의 논리는 이해하기 쉽습니다. 시스템은 새 연결이 생성될 때마다 thread_id_counter가 이 새 연결의 스레드 변수에 할당됩니다.

thread_id_counter의 크기는 4바이트로 정의되어 있으므로 232-1에 도달하면 0으로 재설정되고 계속 증가합니다. 결과는 row_id와 동일하며 원본 레코드를 덮어씁니다.

위에서는 MySQL의 자체 자동 증가 ID 중 일부를 소개합니다. 실제로 실제 애플리케이션에서는 외부 자동 증가 기본 키를 선택한 다음 이를 데이터베이스에 유지하여 데이터베이스 자체의 자동 증가 ID를 대체할 수도 있습니다. 아래에서 그것에 대해 이야기합시다.

Redis 자체 증가 기본 키

사실 외부 자체 증가 기본 키를 생성하는 방법은 여러 가지가 있습니다. 왜 Redis를 도입해야 할까요? 실제 적용에서 많은 장점을 발견했기 때문입니다.

Redis 자체는 원자적이므로 높은 동시성도 스레드로부터 안전합니다. 기본 키 필드 길이가 20이라고 가정하면 시간 + 자동 증가 숫자를 사용하여 기본 키를 형성합니다(예: 8자리 날짜 + 12 자동 증가 숫자). 그러면 업무의 성격에 따라 시간을 연, 월, 일, 밀리초 단위로 정할 수 있는데, 그러면 밀리초 사이에 자동증가 숫자가 반복될 확률은 극히 적어서 적용할 수 있습니다. 기본 사업.

요약

위에는 여러 유형의 자동 증가 ID가 소개되어 있습니다. 각 자동 증가 ID에는 고유한 적용 시나리오가 있으며 상한에 도달한 후의 성능도 다릅니다.

1, 자동 증가 후 테이블의 ID가 상한에 도달하면 다시 적용해도 값이 변경되지 않으므로 계속 데이터를 삽입하면 기본 키 충돌 오류가 발생합니다.
2, row_id가 상한에 도달한 후에는 0으로 돌아가고 다시 증가합니다. 동일한 row_id가 나타나면 나중에 작성된 데이터는 이전 데이터를 덮어쓰려면
3, Xid만 동일한 binlog 파일에 중복된 값이 없어야 합니다. 이론적으로는 중복된 값이 나타나겠지만 확률은 극히 낮아 무시할 수 있습니다
4. InnoDB의 max_trx_id 증분 값은 MySQL을 다시 시작할 때마다 저장되므로 우리 기사에서 언급한 더티 읽기 예제는 필수입니다. 버그, 다행스럽게도 아직 시간이 많습니다
5, thread_id는 우리가 사용하는 가장 일반적인 것이며 가장 잘 처리되는 자동 증가 ID 논리이기도 합니다
6, redis 외부 자동 증가, 밀리초 수준, 이론적으로는 중복된 값이 있을 수 있지만 확률은 매우 낮아 무시할 수 있습니다
7, 실제로 각 자체 증가 ID에는 적용 가능한 시나리오가 있으며 일상 사용에서 특정 시나리오에 따라 선택할 수 있습니다. 그러나 시스템의 실행 시간과 데이터 저장을 종합적으로 고려해야 하므로 비가 오는 날에 대비하여 시스템 작동 중에 즉시 반복되지 않는 것을 선택하십시오. 배웠나요?

추천 학습: mysql 비디오 튜토리얼

위 내용은 MySQL의 자동 증가 ID 부족 문제를 해결하는 방법에 대한 심층 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!