更改表的存储引擎时,不适用于新存储引擎的表选项会保留在表定义,以便在必要时将具有先前定义选项的表恢复到原始存储引擎。例如,将存储引擎从 InnoDB 更改为 MyISAM 时,将保留 InnoDB 特定的选项,例如 ROW_FORMAT=COMPACT。
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPACT ENGINE=InnoDB;
mysql> ALTER TABLE t1 ENGINE=MyISAM;
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE t1
(
c1
int(11) NOT NULL,
PRIMARY KEY (c1
)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
创建禁用严格模式的表时,若不支持指定的行格式,则使用存储引擎的默认行格式。表的实际行格式在 Row_format 列中报告,以响应
SHOW TABLE STATUS。 SHOW CREATE TABLE 显示在 CREATE TABLE 语句中指定的行格式。
AUTO_INCREMENT=2
,表示下一次插入数据时,若需要自动生成自增值,会生成id=2。
这个输出结果容易引起误解:自增值是保存在表结构定义里的。实际上,表的结构定义存在.frm
文件,但不会保存自增值。
MyISAM
自增值保存在数据文件中。
InnoDB
自增值保存在内存,MySQL 8.0后,才有了“自增值持久化”能力,即才实现了“若重启,表的自增值可以恢复为MySQL重启前的值”,具体情况是:
≤5.7,自增值保存在内存,无持久化。每次重启后,第一次打开表时,都会去找自增值的最大值max(id),然后将max(id)+1作为这个表当前的自增值。
若一个表当前数据行里最大的id是10,AUTO_INCREMENT=11
。这时,我们删除id=10的行,AUTO_INCREMENT还是11。但若马上重启实例,重启后,该表的AUTO_INCREMENT就会变成10。
即MySQL重启可能会修改一个表的AUTO_INCREMENT值。
MySQL 8.0将自增值的变更记录在redo log,重启时依靠redo log恢复重启之前的值。
理解了MySQL对自增值的保存策略以后,我们再看看自增值修改机制。
若字段id被定义为AUTO_INCREMENT,在插入一行数据时,自增值的行为如下:
-
若插入数据时id字段指定为0、null 或未指定值,则把该表当前AUTO_INCREMENT值填到自增字段
-
若插入数据时id字段指定了具体值,则使用语句里指定值
根据要插入的值和当前自增值大小关系,假设要插入值X,而当前自增值Y,若:
-
X<Y,则该表的自增值不变
-
X≥Y,把当前自增值修改为新自增值
自增值生成算法
从
-
auto_increment_offset(自增的初始值)开始
-
以auto_increment_increment(步长)持续叠加
直到找到第一个大于X的值,作为新的自增值。
两个系统参数默认值都是1。
某些场景使用的就不全是默认值。比如,双M架构要求双写时,可能设置成auto_increment_increment=2,让一个库的自增id都是奇数,另一个库的自增id都是偶数,避免两个库生成的主键发生冲突。
所以,默认情况下,若准备插入的值≥当前自增值:
-
新自增值就是“准备插入的值+1”
-
否则,自增值不变
=======================================================================
- 表t里面已有如下记录
再执行一条插入数据命令
该唯一键冲突的语句执行流程:
-
执行器调用InnoDB引擎接口写入一行,传入的这一行的值是(0,1,1)
-
InnoDB发现用户没有指定自增id的值,获取表t当前的自增值2
-
将传入的行的值改成(2,1,1)
-
将表的自增值改成3
-
继续执行插入数据(2,1,1),由于已存在c=1,所以报Duplicate key error
-
语句返回
该表的自增值已经改成3,是在真正执行插入数据之前。而该语句真正执行时,因唯一键冲突,所以id=2这行插入失败,但却没有将自增值改回去。
- 此后再成功插入新数据,拿到自增id就是3了
如你所见,自增主键不连续了!所以唯一键冲突是导致自增主键id不连续的一大原因。
事务回滚是二大原因。
为何现唯一键冲突或回滚时,MySQL不把自增值回退?
这么设计是为了提升性能。
假设有俩并行执行的事务,在申请自增值时,为避免两个事务申请到相同自增id,肯定要加锁,然后顺序申请。
假设事务 B 稍后于 A
| 事务A | 事务B |
| — | — |
| 申请到id=2 | |
| | 申请到id=3 |
| 此时表t的自增值4 | 此时表t的自增值4 |
| | 正确提交了 |
| 唯一键冲突 | |
若允许A把自增id回退,即把t的当前自增值改回2,则:表里已有id=3,而当前自增id值是2。
接下来,继续执行其它事务就会申请到id=2,然后再申请到id=3:报错“主键冲突”。
要解决该主键冲突,怎么办?
-
每次申请id前,先判断表里是否已存该id。若存在,就跳过该id。但这样操作成本很高。因为申请id本来很快的,现在竟然还要人家再去主键索引树判断id是否存在
-
把自增id的锁范围扩大,必须等到一个事务执行完成并提交,下一个事务才能再申请自增id。但这样锁的粒度太大,系统度大大下降!
低级的工程师想到的这些方案都会导致性能问题。之所以走进如此的怪圈,就因为“允许自增id回退”这个前提的存在。
所以InnoDB放弃这样的设计,语句即使执行失败了,也不回退自增id!
所以自增id只保证是递增的,但不保证是连续的!
=======================================================================
所以自增id的锁并非事务锁,而是每次申请完就马上释放,其它事务可以再申请。其实,在MySQL 5.1版本之前,并不是这样的。
MySQL 5.0时,自增锁的范围是语句级别:若一个语句申请了一个表自增锁,该锁会等语句执行结束以后才释放。显然,这样影响并发度
MySQL 5.1.22版本引入了一个新策略,新增参数innodb_autoinc_lock_mode,默认值1
。该参数的值为0时,表示采用5.0的策略,设置为1时:
- 普通insert语句
申请后,马上释放;
- 类似insert … select 这样的批量插入语句
等语句结束后,才释放
设置为2时,所有的申请自增主键的动作都是申请后就释放锁。
为什么默认设置下的insert … select 偏偏要使用语句级锁?为什么该参数默认值不是2?
为了数据的一致性。
看个案例:批量插入数据的自增锁
| session1 | session2 |
| — | — |
| insert into t values (null, 2, 2);
insert into t values (null, 3, 3);
insert into t values (null, 4, 4);
| |
| | create table t2 like t; |
| insert into t values (null, 5, 5); | insert into t2(c,d) select c,d from t; |
若session2申请了自增值后,马上释放自增锁,则可能发生:
-
session2先插入了两个记录,(1,1,1)、(2,2,2)
-
然后,session1来申请自增id得到id=3,插入(3,5,5)
-
session2继续执行,插入两条记录(4,3,3)、 (5,4,4)
这好像也没关系吧,毕竟session 2语义本身就没有要求t2的所有行数据都和session1相同。
从数据逻辑角度看是对的。但若此时binlog_format=statement
,binlog会怎么记录呢?
先看看 MySQL 此时的告警:
mysql> insert into t2(c,d) select c,d from t;
Query OK, 4 rows affected, 1 warning (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 1
mysql> show warnings;
±------±-----±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
最后
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长,自己不成体系的自学效果低效漫长且无助。
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,不论你是刚入门Java开发的新手,还是希望在技术上不断提升的资深开发者,这些资料都将为你打开新的学习之门!
如果你觉得这些内容对你有帮助,需要这份全套学习资料的朋友可以戳我获取!!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
图片转存中…(img-Oy0tUqW0-1715852550700)]
[外链图片转存中…(img-ZXJQ01uV-1715852550700)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,不论你是刚入门Java开发的新手,还是希望在技术上不断提升的资深开发者,这些资料都将为你打开新的学习之门!
如果你觉得这些内容对你有帮助,需要这份全套学习资料的朋友可以戳我获取!!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!