그러므로 UUID 문자열을 기본 키로 사용하면 데이터가 삽입될 때마다 B+Tree에서 해당 위치를 찾아야 합니다. 나중에 노드를 이동해야 합니다(배열에 레코드를 삽입하는 것과 같습니다). 나중에 노드를 이동하면 페이지 분할이 포함될 수 있으며 삽입 효율성이 감소합니다. 반면, 비클러스터형 인덱스에서는 리프 노드가 기본 키 값을 저장합니다. 기본 키가 긴 UUID 문자열인 경우 (int에 비해) 더 큰 저장 공간을 차지합니다. 동일한 리프 노드에 저장할 수 있는 기본 키 값이 줄어들어 트리가 커질 수 있으며, 이는 쿼리 중 IO 수가 증가하고 쿼리 효율성이 감소한다는 의미입니다.
위의 분석을 바탕으로 우리는 MySQL에서 UUID를 기본 키로 사용하지 않으려고 노력합니다. UUID가 없으면 기본 키를 사용하여 자동 증가할 수 있을까?라고 생각할 수도 있습니다.
기본 키의 자동 증가는 UUID를 기본 키로 사용할 때 발생하는 두 가지 문제를 분명히 해결할 수 있습니다. 기본 키는 자동으로 증가하므로 매번 트리 끝에 추가하기만 하면 됩니다. 기본적으로 페이지 분할 문제는 발생하지 않습니다. 기본 키 자동 증가는 기본 키가 숫자이고 비클러스터형의 경우 차지하는 저장 공간이 상대적으로 작습니다. 인덱싱의 영향도 더 작습니다.
그렇다면 기본 키를 자동 증가시키는 것이 최선의 해결책일까요? 기본 키가 자동으로 증가할 때 주의해야 할 문제가 있나요?
2. 기본 키 자동 증가 문제
다음 내용은 우리 테이블에 기본 키 자동 증가가 있다는 공통 전제를 가지고 있습니다.일반적으로 기본 키 자동 증가에는 문제가 없습니다. 그러나 동시성이 높은 환경에서는 문제가 발생합니다.
우선 가장 쉽게 생각하는 것은 동시 삽입시 발생하는 테일 핫스팟 문제입니다. 동시 삽입시 모든 사람이 이 값을 쿼리한 후 자신의 기본 키 값을 계산한 다음 기본 키의 상한값을 계산해야 합니다. 키는 핫스팟 데이터가 되며, 동시 삽입 중에 여기에서 잠금 경쟁이 발생합니다.
이 문제를 해결하려면 우리에게 맞는 innodb_autoinc_lock_mode를 선택해야 합니다.
먼저 데이터 테이블에 데이터를 삽입할 때 일반적으로 다음과 같은 세 가지 형태가 있습니다. innodb_autoinc_lock_mode。
首先，我们在向数据表中插入数据的时候，一般来说有三种不同的形式，分别如下：
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。
将数据插入分为这三类，主要是因为在主键自增的时候，锁的处理方案不同，我们继续往下看。
我们可以通过控制 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도 혼합 삽입으로 간주됩니다. 🎜🎜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 不生效。
接下来我们来通过一个简单的 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
insert into user(username,uuid) 값('javaboy')입니다. ,uuid()) . 🎜🎜🎜🎜mixed: 이 모드에서 MySQL은 특정 SQL 문을 기반으로 로그 형식을 결정합니다. 즉, 문과 행 중에서 하나를 선택합니다. 🎜🎜🎜🎜이 세 가지 모드의 경우 명령문 모드가 마스터-슬레이브 복제 중에 마스터-슬레이브 데이터에 불일치를 일으킬 수 있다는 것이 명백하므로 이제 MySQL의 기본 binlog 형식은 행입니다. 🎜🎜질문으로 돌아가서: 🎜🎜🎜🎜binlog 형식이 행인 경우 innodb_autoinc_lock_mode 값을 2로 설정하여 동시 데이터 삽입 기능을 최대한 보장하고 동시에 마스터-슬레이브 데이터 불일치가 발생하지 않습니다. 문제입니다. 🎜🎜🎜🎜binlog 형식이 구문인 경우 innodb_autoinc_lock_mode 값을 1로 설정하여 단순 삽입의 동시 삽입 기능이 향상되는 것이 일괄 삽입의 경우 더 좋습니다. AUTO-INC 잠금을 먼저 획득하기 위해 해제하기 전에 삽입이 성공할 때까지 기다리면 마스터-슬레이브 데이터 불일치를 방지하고 데이터 복제의 보안을 보장할 수 있습니다. 🎜🎜🎜🎜위 두 가지 사항은 주로 InnoDB 스토리지 엔진에 대한 것입니다. MyISAM 스토리지 엔진인 경우 AUTO-INC 잠금을 먼저 획득한 다음 삽입이 완료된 후 해제됩니다. innodb_autoinc_lock_mode 변수가 MyISAM에 적용되지 않습니다. 🎜🎜🎜🎜🎜제가 사용하고 있는 버전 8.0.32의 현재 기본값은 2인 것을 볼 수 있습니다. 🎜🎜먼저 /etc/my.cnf 파일에 innodb_autoinc_lock_mode=0 줄을 추가하여 0으로 변경했습니다: 🎜🎜
🎜🎜변경을 완료한 후 다음으로 다시 시작하세요. ：🎜🎜🎜 🎜이제 변경된 것을 볼 수 있습니다. 🎜🎜이제 다음 테이블이 있다고 가정합니다. 🎜rrreee🎜이 증분은 100부터 시작합니다. 이제 다음 SQL 삽입이 있다고 가정합니다. 🎜rrreee🎜삽입이 완료된 후 쿼리 결과를 살펴보겠습니다. 🎜🎜🎜🎜이전에 따르면 소개, 이 상황은 설명 가능해야 하므로 여기서는 자세히 설명하지 않겠습니다. 🎜🎜다음으로 innodb_autoinc_lock_mode 값을 다음과 같이 1로 변경했습니다. 🎜🎜🎜🎜위와 여전히 동일한 SQL이므로 다시 실행해 보겠습니다. 실행이 완료된 후 결과는 위와 같습니다. 🎜🎜하지만! ! ! **위 SQL을 실행한 후 다시 데이터를 삽입하려고 하는데, 새로 삽입된 ID에 값이 지정되어 있지 않으면 자동으로 생성된 ID 값이 104인 것을 알 수 있습니다. **innodb_autoinc_lock_mode=1로 설정했기 때문입니다. 이때 삽입을 위해 simple insert를 실행할 때 시스템이 4개의 레코드를 삽입하고 싶다고 보고 미리 4개의 ID를 뽑아 주었습니다. , 각각 100, 101, 102 및 103입니다. 결과적으로 SQL은 실제로 두 개의 ID만 사용하고 나머지 두 개는 쓸모가 없지만 다음 삽입은 여전히 104부터 시작됩니다. 🎜위 내용은 MySQL 기본 키 자동 증가로 인해 발생하는 함정을 해결하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
