02-二、MySQL 面试题 InnoDB AUTO_INCREMENT ( 上 )
想必你已经很熟悉 MySQL Innodb 中的 AUTO_INCREMENT
,它是一个约束条件,如果某个字段添加了这个约束条件,插入数据的时候,如果没有给该字段指定一个值,那么它就会自动插入一个自增长的值。
本章节我们不讲 AUTO_INCREMENT
如何使用,而是讲讲 Innodb 是怎么处理它的,有关 AUTO_INCREMENT
的用法,你可以查阅我们的 MySQL 基础教程:MySQL 自增序列 AUTO_INCREMENT
AUTO_INCREMENT 约束
AUTO_INCREMENT
是 Innodb 提供的一种可配置的锁定机制,如果某个表的某一列具有 AUTO_INCREMENT
约束,那么向该表添加数据的时候可以很明显的提高 SQL 语句的性能和可伸缩性。
为了充分使用 Innodb 表的 AUTO_INCREMENT
机制,必须 将 AUTO_INCREMENT
字段 ( 或列,下面提到的 「 列 」 和字段可以等价 ) 定义为 「 索引 」 的一部分,这样就可以在表上使用索引执行下面的语句来查找最大的列值
SELECT MAX (ai_col ) FROM tablename;
ai_col
为定义了 AUTO_INCREMENT
约束的列。
通常情况下,为了最大化性能,添加了 AUTO_INCREMENT
约束的列要么独自成一个索引 ( 主索引 ),那么是组合索引中的第一列
需要注意的是: 虽然我们日常使用中会把 AUTO_INCREMENT 添加为主键,但它其实也可以不是主键的。甚至可以不是唯一索引。
AUTO_INCREMENT
不仅仅是一个字段约束条件,它还是一个 「 锁 」,也就是那个很少见到的 「 AUTO_INCREMENT 锁」。
本章节接下来的部分,我们就来介绍介绍这个 「 AUTO_INCREMENT 锁」的模式和行为,包括不同的 「 AUTO_INCREMENT 锁」模式的使用意义,以及 Innodb 如何初始化 「 AUTO_INCREMENT 计数器 」
Innodb AUTO_INCREMENT 锁的模式
使用了 AUTO_INCREMENT 那么多次,我们已经知道它的主要作用就是产生一个不重复的 「 自增值 」。
我们知道,插入多条数据有两种插入方法,一种是一条一条的执行 INSERT INTO,另一种是 INSERT INTO VALUES(...),(...)
多条一起插入
这两种插入方法都能正确的自增 AUTO_INCREMENT 列,它们是如何做的呢 ?
这就仰赖了 AUTO_INCREMENT 锁,为了适应这两种插入方法,它同时也具有多种模式。
术语
在我们继续讲解之前,为了方便大家理解一些术语或概念,我们先罗列在此
1、 「 insert like 」 语句
所有可以在表中添加新行的语句,我们称之为 「 insert like 」 语句,例如
INSERT, INSERT ... SELECT
REPLACE
REPLACE ... SELECT
LOAD DATA
- 其它的还有 「 simple-inserts 」、「 bulk-inserts 」和 「 mixed-mode 」 三种插入语句
2、 「 simple-inserts 」 语句
「 simple-inserts 」 是可以预先确定要插入的行数的语句 ( 最初处理语句时 )。包括不带子查询的 单行 和 多行 INSERT
和REPLACE
语句,但不包括 INSERT ... ON DUPLICATE KEY UPDATE
语句
3、 「 Bulk inserts 」 批量插入
「 Bulk inserts 」是预先不知道要插入的行数(以及所需的自动增量值的数量)的语句。
包括 INSERT ... SELECT
,REPLACE ... SELECT
和 LOAD DATA
语句,但不包括普通的 INSERT
在处理每一行时,InnoDB 都会重新为 AUTO_INCREMENT
列分配一个新值
4、 「 Mixed-mode inserts 」 混合模式插入
「 Mixed-mode inserts 」 是指「 simple-inserts 」 语句中,有些指定了 AUTO_INCREMENT
列的值,而另一些则没有。
例如下面的 SQL 语句,其中 c1
是表 t1
的 AUTO_INCREMENT
列
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
另一种类型的 「 Mixed-mode inserts 」 是 INSERT ... ON DUPLICATE KEY UPDATE
,这种语句最坏的情况下实际上是 INSERT
后跟 UPDATE
,其中在更新阶段,可能会也可能不会为 AUTO_INCREMENT
列的分配值
innodb_autoinc_lock_mode
我们先不讨论有几种模式,我们先来看看它是如何配置的。
「 AUTO_INCREMENT 锁」模式的配置变量为 innodb_autoinc_lock_mode
,我们可以通过下面的语句查看当前的模式是什么
show variables like 'innodb_autoinc_lock_mode';
在我的 5.7.22
的版本的 MySQL 中,输出结果为
mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1 |
+--------------------------+-------+
配置参数 innodb_autoinc_lock_mode
有三个可选的值,分别是 0
、1
和 2
,分别代表着 「 传统 」,「 连续 」 或 「 交错 」 三种锁模式
在不同的版本下,innodb_autoinc_lock_mode
的默认值是不一样的,在 mysql >= 8.0.3
版本中是 2
,也就是 「 交错 」 模式,而 mysql <= 8.0.2
版本中是 1
,也就是 「 连续 」 模式
对于 8.0.3
版本中的这种变更,也反应了 Innodb 的默认 「 复制模式 」 已经从基于 SQL 语句 变更为基于 行 ( row )
基于 SQL 语句的复制需要 「 连续 」 模式的 「 AUTO_INCREMENT 锁」,以确保为给定的 SQL 语句序列以可预测和可重复的顺序分配自动增量值,而基于行的复制对 SQL 语句的执行顺序不敏感
innodb_autoinc_lock_mode = 0
传统锁模式
传统锁模式是在 MySQL 5.1
中引入 innodb_autoinc_lock_mode
配置参数之前的默认模式。现在,传统锁模式存在的意义,仅仅是用于向后兼容,性能测试以及解决 「 混合模式插入 」 问题,因为语义方面可能存在差异。
在这种锁模式下,为了向具有 AUTO_INCREMENT
列的表中插入数据,所有的 「 insert like 」 语句都会获得一个特殊的 表级 AUTO-INC 锁,这种锁会自动添加到 SQL 语句的末尾 ( 不是事务的末尾 ),以确保以可预测且可重复的顺序为给定的 INSERT 语句序列分配自增值,并确保为任何给定语句分配的自增值都是连续的。
在基于 SQL 语句的 ( 主从 ) 复制环境中,在从服务器上运行复制 SQL 语句时,自增量列的值和主服务器的值相同,这样执行多个 INSERT 语句的结果是确定性的,并且从服务器的数据和主服务器的数据一摸一样。
如果多个 INSERT 语句生成的自增值是交错的,那么两个并发 INSERT 语句的结果将是不确定的,这样就无法使用基于 SQL 语句的复制模式将数据可靠地复制到从服务器
讲解的有点拗口,我们看看一些示例,假设存在一张表 t1
,它的建表语句如下
CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (c1)
) ENGINE=InnoDB;
把表建完后,我们假设有两个事务在运行,两个事务都是往 t1
表中插入数据,第一个事务使用一个事务使用 INSERT ... SELECT
语句插入 1000 行的,另一个事务使用简单的 INSERT 语句插入一行数据
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
第一个事务 Tx1
,因为 InnoDB 无法预先判断在 INSERT 语句从 SELECT 中检索了多少行,所以它会在每插入一条数据的时候分配一个自增值。这种情况下,会使用 表锁 ,会在 SQL 语句的末尾自动添加一个表锁,一次只能在表 t1
执行一条 INSERT
语句,这样就能保证每条 INSERT 语句的自增值是连续的且不会交错。
这样由 Tx1 INSERT ... SELECT
语句生成的自增值是连续的,并且 Tx2 事务中 INSERT
语句使用的 ( 单个 ) 自增值要小于或大于 Tx1 的所有自增值,结果具体取决于哪个语句先执行
这时候在主从复制或数据恢复时,只要以二进制日志重放 SQL 语句时 ( 使用基于语句的复制时或恢复方案中)以相同的顺序执行,那么重放的结果与 Tx1 和 Tx2 首次运行时的结果相同
如果前面的示例没有使用 「 表锁 」 ,那么 Tx2 中 INSERT 的自增列的值取决于语句执行的时间。如果 Tx2 的 INSERT 在 Tx1 的 INSERT 运行时 ( 而不是在它开始之前或完成之后 ) 执行,则两个 INSERT 语句分配的特定自增值是不确定的,并且可能因运行而异。
在 「 连续锁 」模式下,InnoDB 可以避免将表级 AUTO-INC
锁用于 「 insert like 」 语句,因为行数已预先知道,而且还可以确保基于语句的复制的确定性执行和安全性
在恢复或复制数据的重放 SQL 语句时如果不使用二进制日志,那么可以使用 「 交错锁 」模式用来消除表级 AUTO-INC 锁的使用,以获得更高的并发性和性能,但代价语句分配的自增值数字可能不是连续的,而且可能因为并发的执行而存在重复的数字
后记
知识量好大… 单单一个 「 传统 」模式就这么复杂…
希望读者能够给小编留言,也可以点击[此处扫下面二维码关注微信公众号](https://www.ycbbs.vip/?p=28 "此处扫下面二维码关注微信公众号")
看完两件小事
如果你觉得这篇文章对你挺有启发,我想请你帮我两个小忙:
- 把这篇文章分享给你的朋友 / 交流群,让更多的人看到,一起进步,一起成长!
- 关注公众号 「方志朋」,公众号后台回复「666」 免费领取我精心整理的进阶资源教程
本文著作权归作者所有,如若转载,请注明出处
转载请注明:文章转载自「 Java极客技术学习 」https://www.javajike.com