麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践
616
2023-06-15
自增ID插入时,究竟发生了什么?
《MySQL自增ID,居然大部分人都搞错了?》中的作业题,有少量答对的人,但原理讲得不透,今天简单说下作业题中的答案,以及相关知识点。
作业题是这样的:
drop table t1;create table t1( id int not null auto_increment, name varchar(10) unique, count int default 0, primary key(id), index(name))engine=innodb;insert into t1(id, name) values(1, "shenjian");insert into t1(id, name) values (111, "111"),(NULL, "abc"),(222, "222"),(NULL,"xyz");
执行结束后id分别是:
(1, 'shenjian')(111, '111') // 允许指定值(112, 'abc') // 忽略NULL,从最大值开始增(222, '222') // 允许指定值(223, 'xyz') // 忽略NULL,从最大值开始增
如果继续执行以下语句:
insert into t1(name)values("shenjian"),("aaa"),("bbb")on duplicate key update count=100;
请问:
会不会执行报错?如果报错,为什么呢?如果不报错,得到的数据是什么呢?
知其然,知其所以然。先系统性讲讲相关知识点,InnoDB自增键,插入,如何插入,以及插入之后发生了什么?
画外音:本文均以MySQL5.6,InnoDB引擎为例。
什么是插入?
MySQL有一系列的语句,可以往数据库新增数据,称作泛插入语句(insert-like statement)。不同的插入方式,对自增键的影响是不一样的。
画外音:自增键,是指auto_increment,大家应该都用得很多。
有几种插入方式?
更为细化后,总的来说,插入方式分为三类:
简单插入(simple insert)批量插入(bulk insert)混合插入(mixed-mode insert)
什么是简单插入(simple insert)?
普通的insert/replace语句,不管是单条插入还是多条插入,都是简单插入。
画外音:
不包含递归的子查询;不包含insert … on duplicate key update… ;
例如,对于作业题中的数据表:
insert into t1(id, name) values(1,"shenjian");insert into t1(name) values("zhangsan"),("lisi"),("ww");
都是简单插入。
简单插入的特点是,能够提前知道被插入的行数。因此,这类插入,在处理自增键时,是最容易的。
画外音:很容易保证自增键连续性。
什么是批量插入(bulk insert)?
与简单插入相对,在插入时,不知道被插入的行数,是批量插入。
例如,对于作业题中的数据表:
insert into t1(name) select name from t2;
除此之外,像:
replace … select …load data
都是批量插入。
由于不能够提前知道多少行插入,在处理自增列时,每插入一行,才会赋值新的自增值。
画外音,官网原文是:
InnoDB assigns new values for the AUTO_INCREMENT column one at a time as each row is processed.
这里的潜台词是,在批量插入事务并发时,“可能”出现同一个事务的自增键不连续。
什么是混合插入(mixed-mode insert)?
例如,对于作业题中的SQL:
insert into t1(id, name) values (111,"111"),(NULL, "abc"),(222,"222"),(NULL,"xyz");
有些行插入时指定了自增键,无需数据库生成;有些行插入时未指定自增键(NULL),需要数据库生成。
画外音:具体走哪个分支,实际执行时才知道。
以及,作业题中的如下SQL:
insert into t1(name) values("shenjian"),("aaa"),("bbb")on duplicate key update count=100;
有些行插入实际上是修改,有些行插入实际上就是插入。
画外音:具体走哪个分支,也是实际执行时才知道。
insert … on duplicate key update … 这种情况是最最复杂的,它可能导致,系统生成的自增值,在更新阶段用不上。
画外音,官网原文是:
an INSERT followed by a UPDATE, where the allocated value for the AUTO_INCREMENT column may or may not be used during the update phase.
说了这么多,对于作业题,id分别是:
(1, 'shenjian')(111, '111') // 允许指定值(112, 'abc') // 忽略NULL,从最大值开始增(222, '222') // 允许指定值(223, 'xyz') // 忽略NULL,从最大值开始增
如果继续执行以下语句,答案会是什么呢?
insert into t1(name)values("shenjian"),("aaa"),("bbb")on duplicate key update count=100;
答案如下:
对于:
insert ("shenjian"),("aaa"),("bbb")
系统以为是批量插入,就生成了224,225,226三个连续自增键。
接下来:
on duplicate key update ...
结果有一行插入冲突,实际是更新,导致224这个自增值并没有用上,于是就出现了奇怪的“自增值不连续”的怪异现象。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。