MySQL 기본 키 자동 증가로 인해 발생하는 함정을 해결하는 방법

1. UUID를 사용하면 안되는 이유

그러므로 UUID 문자열을 기본 키로 사용하면 데이터가 삽입될 때마다 B+Tree에서 해당 위치를 찾아야 합니다. 나중에 노드를 이동해야 합니다(배열에 레코드를 삽입하는 것과 같습니다). 나중에 노드를 이동하면 페이지 분할이 포함될 수 있으며 삽입 효율성이 감소합니다. 반면, 비클러스터형 인덱스에서는 리프 노드가 기본 키 값을 저장합니다. 기본 키가 긴 UUID 문자열인 경우 (int에 비해) 더 큰 저장 공간을 차지합니다. 동일한 리프 노드에 저장할 수 있는 기본 키 값이 줄어들어 트리가 커질 수 있으며, 이는 쿼리 중 IO 수가 증가하고 쿼리 효율성이 감소한다는 의미입니다.

위의 분석을 바탕으로 우리는 MySQL에서 UUID를 기본 키로 사용하지 않으려고 노력합니다. UUID가 없으면 기본 키를 사용하여 자동 증가할 수 있을까?라고 생각할 수도 있습니다.

기본 키의 자동 증가는 UUID를 기본 키로 사용할 때 발생하는 두 가지 문제를 분명히 해결할 수 있습니다. 기본 키는 자동으로 증가하므로 매번 트리 끝에 추가하기만 하면 됩니다. 기본적으로 페이지 분할 문제는 발생하지 않습니다. 기본 키 자동 증가는 기본 키가 숫자이고 비클러스터형의 경우 차지하는 저장 공간이 상대적으로 작습니다. 인덱싱의 영향도 더 작습니다.

그렇다면 기본 키를 자동 증가시키는 것이 최선의 해결책일까요? 기본 키가 자동으로 증가할 때 주의해야 할 문제가 있나요?

2. 기본 키 자동 증가 문제

일반적으로 기본 키 자동 증가에는 문제가 없습니다. 그러나 동시성이 높은 환경에서는 문제가 발생합니다.

우선 가장 쉽게 생각하는 것은 동시 삽입시 발생하는 테일 핫스팟 문제입니다. 동시 삽입시 모든 사람이 이 값을 쿼리한 후 자신의 기본 키 값을 계산한 다음 기본 키의 상한값을 계산해야 합니다. 키는 핫스팟 데이터가 되며, 동시 삽입 중에 여기에서 잠금 경쟁이 발생합니다.

이 문제를 해결하려면 우리에게 맞는 innodb_autoinc_lock_mode를 선택해야 합니다.

2.1 세 가지 형태의 데이터 삽입

먼저 데이터 테이블에 데이터를 삽입할 때 일반적으로 다음과 같은 세 가지 형태가 있습니다. innodb_autoinc_lock_mode。

2.1 数据插入的三种形式

首先，我们在向数据表中插入数据的时候，一般来说有三种不同的形式，分别如下：

• insert into user(name) values('javaboy') 或者 replace into user(name) values('javaboy') ，这种没有嵌套子查询并且能够确定具体插入多少行的插入叫做 simple insert，不过需要注意的是 INSERT ... ON DUPLICATE KEY UPDATE 不算是 simple insert。

• load data 或者 insert into user select ... from ....，这种都是批量插入，叫做 bulk insert，这种批量插入有一个特点就是插入多少条数据在一开始是未知的。

• insert into user(id,name) values(null,'javaboy'),(null,'江南一点雨')，这种也是批量插入，但是跟第二种又不太一样，这种里边包含了一些自动生成的值（本案例中的主键自增），并且能够确定一共插入多少行，这种称之为 mixed insert，对于前面第一点提到的 INSERT ... ON DUPLICATE KEY UPDATE 也算是一种 mixed insert。

将数据插入分为这三类，主要是因为在主键自增的时候，锁的处理方案不同，我们继续往下看。

2.2 innodb_autoinc_lock_mode

我们可以通过控制 innodb_autoinc_lock_mode 变量的值，来控制在主键自增的时候，MySQL 锁的处理思路。

innodb_autoinc_lock_mode 变量一共有三个不同的取值：

• 0: 这个表示 traditional，在这种模式下，我们上面提到的三种不同的插入 SQL，对于自增锁的处理方案是一致的，都是在插入 SQL 语句开始的时候，获取到一个表级的 AUTO-INC 锁，然后当插入 SQL 执行完毕之后，再释放掉这把锁，这样做的好处是可以确保在批量插入的时候，自增主键是连续的。

• 1: 这个表示 consecutive，在这种模式下，对 simple insert（能够确定具体插入行数的，对应上面 1、3 两种情况）做了一些优化，由于 simple insert
사용자(이름) 값('javaboy')에 삽입 또는 사용자(이름) 값('javaboy')으로 대체, 이는 포함되지 않습니다. 중첩된 쿼리를 사용하고 삽입할 행 수를 결정할 수 있는 삽입을 단순 삽입이라고 하지만 INSERT ... ON DUPLICATE KEY UPDATE는 고려되지 않는다는 점에 유의해야 합니다. 단순 삽입.

• 🎜데이터 로드 또는 insert into user select ... from ...., 이는 대량 삽입이라고 불리는 대량 삽입입니다. 이 일괄 삽입의 특징 중 하나는 삽입할 데이터의 개수를 처음부터 알 수 없다는 것입니다. 🎜🎜🎜🎜insert into user(id,name) 값(null,'javaboy'),(null,'Jiangnan a little rain'), 이것도 일괄 삽입이지만 다릅니다. 두 번째부터는 동일하지 않습니다. 여기에는 자동으로 생성된 값이 포함되어 있으며(이 경우 기본 키는 자동 증가됨) 총 삽입되는 행 수를 결정할 수 있습니다. 이를 혼합 삽입이라고 합니다. 이전 부분의 경우 앞서 언급한 INSERT ... ON DUPLICATE KEY UPDATE도 혼합 삽입으로 간주됩니다. 🎜🎜

2.2 innodb_autoinc_lock_mode

🎜🎜0: 이는 기존 모드를 나타냅니다. SQL 위에서 언급한 세 가지 다른 삽입은 자동 증가에 대한 동일한 솔루션을 갖습니다. SQL 문 삽입 시작 시 테이블 수준의 AUTO-INC 잠금을 획득하고 삽입된 SQL 문 실행이 완료된 후 잠금을 해제합니다. - 일괄 삽입 중에 기본 키 증가가 계속됩니다. 🎜🎜🎜🎜1: 이는 연속을 의미합니다. 이 모드에서는 simple insert에 대한 일부 최적화가 이루어졌습니다(위의 두 상황 1과 3에 해당하는 삽입된 행의 특정 수를 결정할 수 있음). 간단 삽입은 삽입할 행 수를 계산하기 쉽기 때문에 여러 개의 연속된 값을 한 번에 생성하여 해당 삽입 SQL 문에 사용할 수 있습니다. INC 잠금을 미리 해제하고 잠금 횟수를 줄여 동시 삽입 효율성을 높일 수 있습니다. 🎜🎜🎜🎜2: 이 경우에는 AUTO-INC 잠금이 없다는 의미입니다. 일괄적으로 삽입할 경우 기본 키가 증가되지만 그렇지 않은 문제가 있을 수 있습니다. 마디 없는. 🎜

위 소개에서 볼 수 있듯이 사실 세 번째 유형, 즉 innodb_autoinc_lock_mode 값이 2일 때 동시성 효율성이 가장 강합니다. 그러면 innodb_autoinc_lock_mode=2로 설정해야 할까요?

상황에 따라 다릅니다.

송 형제는 이전에 친구들에게 MySQL binlog 로그 파일의 세 가지 형식을 소개하는 기사를 작성했습니다.

• row: binlog에 기록되는 것은 원본 SQL이 아닌 특정 값입니다. , 테이블의 한 필드가 UUID이고 사용자가 실행한 SQL이 user(username,uuid) 값('javaboy',uuid())에 삽입이라고 가정하고, 마지막으로 SQL이 binlog는 사용자(사용자 이름,uuid) 값에 삽입('javaboy',‘0212cfa0-de06-11ed-a026-0242ac110004’)입니다. insert into user(username,uuid) values('javaboy',uuid())，那么最终记录到 binlog 中的 SQL 是 insert into user(username,uuid) values('javaboy',‘0212cfa0-de06-11ed-a026-0242ac110004’)。

• statement：binlog 中记录的就是原始的 SQL 了，以 row 中的为例，最终 binlog 中记录的就是 insert into user(username,uuid) values('javaboy',uuid())。

• mixed：在这种模式下，MySQL 会根据具体的 SQL 语句来决定日志的形式，也就是在 statement 和 row 之间选择一种。

对于这三种不同的模式，很明显，在主从复制的时候，statement 模式可能会导致主从数据不一致，所以现在 MySQL 默认的 binlog 格式都是 row。

回到我们的问题：

• 如果 binlog 格式是 row，那么我们就可以设置 innodb_autoinc_lock_mode 的值为 2，这样就能尽最大程度保证数据并发插入的能力，同时不会发生主从数据不一致的问题。

• 如果 binlog 格式是 statement，那么我们最好设置 innodb_autoinc_lock_mode 的值为 1，这样对于 simple insert 的并发插入能力进行了提高，批量插入还是先获取 AUTO-INC 锁，等插入成功之后再释放，这样也能避免主从数据不一致，保证数据复制的安全性。

• 以上两点主要是针对 InnoDB 存储引擎，如果是 MyISAM 存储引擎，都是先获取 AUTO-INC 锁，插入完成再释放，相当于 innodb_autoinc_lock_mode 变量的取值对 MyISAM 不生效。

2.3 实践

接下来我们来通过一个简单的 SQL 来和小伙伴们演示一下 innodb_autoinc_lock_mode 不同取值对应不同结果的情况。

我们可以使用以下 SQL 查询来查看当前 innodb_autoinc_lock_mode 的设置：

可以看到，我使用的 8.0.32 这个版本目前默认值是 2。

我先把它改成 0，修改方式就是在 /etc/my.cnf 文件中添加一行 innodb_autoinc_lock_mode=0：

改完之后再重启查看，如下：

可以看到，现在就已经改过来了。

现在假设我有如下表：

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

这个自增是从 100 开始计的，现在假设我有如下插入 SQL：

insert into user(id,username) values(1,'javaboy'),(null,'江南一点雨'),(3,'www.javaboy.org'),(null,'lisi');

插入完成之后，我们来看查询结果：

按照我们前文的介绍，这个情况应该是可以解释的通的，我这里不再赘述。

接下来，我把 innodb_autoinc_lock_mode 取值改为 1，如下：

还是上面相同的 SQL，我们再执行一遍。执行完成之后结果也和上文相同。

但是！！！**当上面的 SQL 执行完毕之后，如果我们还想再插入数据，并且新插入的 ID 不指定值，则我们发现自动生成的 ID 值为 104。**这就是因为我们设置了 innodb_autoinc_lock_mode=1，此时，执行 simple insert

2.3 연습

위 내용은 MySQL 기본 키 자동 증가로 인해 발생하는 함정을 해결하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!