创建一个表 t,其中 id 是自增主键字段、c 是唯一索引。
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
在这个空表 t 里面执行 insert into t values(null, 1, 1); 插入一行数据,再执行 show create table 命令,就可以看到如下图所示的结果:
图 1 自动生成的 AUTO_INCREMENT 值
可以看到,表定义里面出现了一个 AUTO_INCREMENT=2,表示下一次插入数据时,如果需要自动生成自增值,会生成 id=2。
这个输出结果容易引起这样的误解:自增值是保存在表结构定义里的。实际上,表的结构定义存放在后缀名为.frm 的文件中,但是并不会保存自增值。
不同的引擎对于自增值的保存策略不同。
在 MySQL 里面,如果字段 id 被定义为 AUTO_INCREMENT,在插入一行数据的时候,自增值的行为如下:
根据要插入的值和当前自增值的大小关系,自增值的变更结果也会有所不同。假设,某次要插入的值是 X,当前的自增值是 Y。
新的自增值生成算法是:
从自增的初始值(auto_increment_offset) 开始,以步长(auto_increment_increment) 为步长,持续叠加,直到找到第一个大于 X 的值,作为新的自增值。auto_increment_offset 和 auto_increment_increment默认值都是 1
在一些场景下,使用的就不全是默认值。比如,双 M 的主备结构里要求双写的时候,我们就可能会设置成 auto_increment_increment=2,让一个库的自增 id 都是奇数,另一个库的自增 id 都是偶数,避免两个库生成的主键发生冲突
当 auto_increment_offset 和 auto_increment_increment 都是 1 的时候,新的自增值生成逻辑很简单,就是:
在这两个参数都设置为 1 的时候,自增主键 id 却不能保证是连续的,这是什么原因呢?
要回答这个问题,要看一下自增值的修改时机。
假设,表 t 里面已经有了 (1,1,1) 这条记录,这时我再执行一条插入数据命令:
insert into t values(null, 1, 1);
语句的执行流程就是:
执行流程图如下:
图 2 insert(null, 1,1) 唯一键冲突
可以看到,这个表的自增值改成 3,是在真正执行插入数据的操作之前。 语句真正执行的时候,因为碰到唯一键 c 冲突,所以 id=2 这一行并没有插入成功,但也没有将自增值再改回去。
所以,在这之后,再插入新的数据行时,拿到的自增 id 就是 3。
图 3 一个自增主键 id 不连续的复现步骤
唯一键冲突是导致自增主键 id 不连续的第一种原因。
同样地,事务回滚也会产生类似的现象,这就是第二种原因。
假设有两个并行执行的事务,在申请自增值的时候,为了避免两个事务申请到相同的自增 id,肯定要加锁,然后顺序申请。
为了解决这个主键冲突,有两种方法:
这两个方法都会导致性能问题。因此,InnoDB 放弃了这个设计,语句执行失败也不回退自增 id。也正是因为这样,所以才只保证了自增 id 是递增的,但不保证是连续的。
自增 id 锁并不是一个事务锁,而是每次申请完就马上释放,以便允许别的事务再申请。
在 MySQL 5.0 版本的时候,自增锁的范围是语句级别。
如果一个语句申请了一个表自增锁,这个锁会等语句执行结束以后才释放。显然,这样设计会影响并发度。
MySQL 5.1.22 版本引入了一个新策略,新增参数 innodb_autoinc_lock_mode,默认值是 1。
疑问:
为什么默认设置下,insert … select 要使用语句级的锁?为什么这个参数的默认值不是 2?
答案是,这么设计还是为了数据的一致性。
看一下这个场景:
图 4 批量插入数据的自增锁
往表 t1 中插入了 4 行数据,然后创建了一个相同结构的表 t2,然后两个 session 同时执行向表 t2 中插入数据的操作。
如果 session B 是申请了自增值以后马上就释放自增锁,那么就可能出现这样的情况:
如果我们现在的 binlog_format=statement,由于两个 session 是同时执行插入数据命令的,所以 binlog 里面对表 t2 的更新日志只有两种情况:要么先记 session A 的,要么先记 session B 的。但不论是哪一种,这个 binlog 拿去从库执行,或者用来恢复临时实例,备库和临时实例里面,session B 这个语句执行出来,生成的结果里面,id 都是连续的。这时,这个库就发生了数据不一致。
问题的原因:因为原库 session B 的 insert 语句,生成的 id 不连续。这个不连续的 id,用 statement 格式的 binlog 来串行执行,是执行不出来的。
要解决这个问题,有两种思路:
因此,在生产上,尤其是有 insert … select 这种批量插入数据的场景时,从并发插入数据性能的角度考虑,建议你这样设置:innodb_autoinc_lock_mode=2 ,并且 binlog_format=row. 这样做,既能提升并发性,又不会出现数据一致性问题。
这里说的批量插入数据,包含的语句类型是 insert … select、replace … select 和 load data 语句。
在普通的 insert 语句里面包含多个 value 值的情况下,即使 innodb_autoinc_lock_mode 设置为 1,也不会等语句执行完成才释放锁。因为这类语句在申请自增 id 的时候,是可以精确计算出需要多少个 id 的,然后一次性申请,申请完成后锁就可以释放了。
也就是说,批量插入数据的语句,之所以需要这么设置,是因为“不知道要预先申请多少个 id”。
对于批量插入数据的语句,MySQL 有一个批量申请自增 id 的策略:
一起看看下面的这个语句序列:
insert into t values(null, 1,1);
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 t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);
insert…select,实际上往表 t2 中插入了 4 行数据。但是,这四行数据是分三次申请的自增 id,第一次申请到了 id=1,第二次被分配了 id=2 和 id=3, 第三次被分配到 id=4 到 id=7。
由于这条语句实际只用上了 4 个 id,所以 id=5 到 id=7 就被浪费掉了。之后,再执行 insert into t2 values(null, 5,5),实际上插入的数据就是(8,5,5)。
这是主键 id 出现自增 id 不连续的第三种原因。
在 binlog_format=statement 时,语句 A 先获取 id=1,然后语句 B 获取 id=2;接着语句 B 提交,写 binlog,然后语句 A 再写 binlog。这时候,如果 binlog 重放,是不是会发生语句 B 的 id 为 1,而语句 A 的 id 为 2 的不一致情况呢?
不会发生。
create table t(id int auto_increment primary key);
insert into t values(null);
在 insert 语句之前,还有一句 SET INSERT_ID=1。
这条命令的意思是,这个线程里下一次需要用到自增值的时候,不论当前表的自增值是多少,固定用 1 这个值。
这个 SET INSERT_ID 语句是固定跟在 insert 语句之前的
因此,即使两个 INSERT 语句在主备库的执行顺序不同,自增主键字段的值也不会不一致。
小结
思考
在最后一个例子中,执行 insert into t2(c,d) select c,d from t; 这个语句的时候,如果隔离级别是可重复读(repeatable read),binlog_format=statement。这个语句会对表 t 的所有记录和间隙加锁。
为什么需要这么做呢?
答案见下节正文
MySQL 对自增主键锁做了优化,尽量在申请到自增 id 以后,就释放自增锁。
因此,insert 语句是一个很轻量的操作。不过,这个结论对于“普通的 insert 语句”才有效。也就是说,还有些 insert 语句是属于“特殊情况”的,在执行过程中需要给其他资源加锁,或者无法在申请到自增 id 以后就立马释放自增锁。
表 t 和 t2 的表结构、初始化数据语句如下
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(null, 1,1);
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
一起来看看为什么在可重复读隔离级别下,binlog_format=statement 时执行:
insert into t2(c,d) select c,d from t;
这个语句时,需要对表 t 的所有行和间隙加锁呢?
这个问题我们需要考虑的还是日志和数据的一致性,看下这个执行序列:
图 1 并发 insert 场景
实际的执行效果:
如果 session B 先执行,由于这个语句对表 t 主键索引加了 (-∞,1]这个 next-key lock,会在语句执行完成后,才允许 session A 的 insert 语句执行。
如果没有锁的话,就可能出现 session B 的 insert 语句先执行,但是后写入 binlog 的情况。于是,在 binlog_format=statement 的情况下,binlog 里面就记录了这样的语句序列:
insert into t values(-1,-1,-1);
insert into t2(c,d) select c,d from t;
这个语句到了备库执行,就会把 id=-1 这一行也写到表 t2 中,出现主备不一致。
执行 insert … select 的时候,对目标表也不是锁全表,而是只锁住需要访问的资源。
有这么一个需求:要往表 t2 中插入一行数据,这一行的 c 值是表 t 中 c 值的最大值加 1。
SQL 语句 :
insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
这个语句的加锁范围,就是表 t 索引 c 上的 (3,4]和 (4,supremum]这两个 next-key lock,以及主键索引上 id=4 这一行。
执行流程也比较简单,从表 t 中按照索引 c 倒序,扫描第一行,拿到结果写入到表 t2 中。因此整条语句的扫描行数是 1。
慢查询日志(slow log),如下图所示:
图 2 慢查询日志 – 将数据插入表 t2
Rows_examined=1,验证了执行这条语句的扫描行数为 1。
如果是要把这样的一行数据插入到表 t 中的话:
insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
图 3 慢查询日志 – 将数据插入表 t
Rows_examined 的值是 5。
图 4 explain 结果
这个语句用到了临时表。也就是说,执行过程中,需要把表 t 的内容读出来,写入临时表。
看看 InnoDB 扫描了多少行。如图 5 所示,是在执行这个语句前后查看 Innodb_rows_read 的结果。
图 5 查看 Innodb_rows_read 变化
这个语句执行前后,Innodb_rows_read 的值增加了 4。因为默认临时表是使用 Memory 引擎的,所以这 4 行查的都是表 t,也就是说对表 t 做了全表扫描。
整个执行过程:
也就是说,这个语句会导致在表 t 上做全表扫描,并且会给索引 c 上的所有间隙都加上共享的 next-key lock。所以,这个语句执行期间,其他事务不能在这个表上插入数据。
原因是这类一边遍历数据,一边更新数据的情况,如果读出来的数据直接写回原表,就可能在遍历过程中,读到刚刚插入的记录,新插入的记录如果参与计算逻辑,就跟语义不符。
由于实现上这个语句没有在子查询中就直接使用 limit 1,从而导致了这个语句的执行需要遍历整个表 t。它的优化方法也比较简单,就是用前面介绍的方法,先 insert into 到临时表 temp_t,这样就只需要扫描一行;然后再从表 temp_t 里面取出这行数据插入表 t1。
当然,由于这个语句涉及的数据量很小,你可以考虑使用内存临时表来做这个优化。使用内存临时表优化时,语句序列的写法如下:
create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;
举一个简单的唯一键冲突的例子。
图 6 唯一键冲突加锁
例子也是在可重复读(repeatable read)隔离级别下执行的。可以看到,session B 要执行的 insert 语句进入了锁等待状态。
就是说,session A 执行的 insert 语句,发生唯一键冲突的时候,并不只是简单地报错返回,还在冲突的索引上加了锁。 一个 next-key lock 就是由它右边界的值定义的。这时候,session A 持有索引 c 上的 (5,10]共享 next-key lock(读锁)。
分享一个经典的死锁场景
图 7 唯一键冲突 – 死锁
在 session A 执行 rollback 语句回滚的时候,session C 几乎同时发现死锁并返回。
这个死锁产生的逻辑是这样的:
图 8 状态变化图 – 死锁
这个例子是主键冲突后直接报错,如果是改写成
insert into t values(11,10,10) on duplicate key update d=100;
的话,就会给索引 c 上 (5,10] 加一个排他的 next-key lock(写锁)。
insert into … on duplicate key update 这个语义的逻辑是,插入一行数据,如果碰到唯一键约束,就执行后面的更新语句。
注意,如果有多个列违反了唯一性约束,就会按照索引的顺序,修改跟第一个索引冲突的行。
表 t 里面已经有了 (1,1,1) 和 (2,2,2) 这两行,再来看看下面这个语句执行的效果:
图 9 两个唯一键同时冲突
可以看到,主键 id 是先判断的,MySQL 认为这个语句跟 id=2 这一行冲突,所以修改的是 id=2 的行。
需要注意的是,执行这条语句的 affected rows 返回的是 2,很容易造成误解。实际上,真正更新的只有一行,只是在代码实现上,insert 和 update 都认为自己成功了,update 计数加了 1, insert 计数也加了 1。
小结
思考
两个表之间拷贝数据用的是什么方法,有什么注意事项吗?在你的应用场景里,这个方法,相较于其他方法的优势是什么呢?
如果可以控制对源表的扫描行数和加锁范围很小的话,我们简单地使用 insert … select 语句即可实现。
为了避免对源表加读锁,更稳妥的方案是先将数据写到外部文本文件,然后再写回目标表。常用方案见下节 3.如何快速复制一张表
先创建一个表 db1.t,并插入 1000 行数据,同时创建一个相同结构的表 db2.t。
create database db1;
use db1;
create table t(id int primary key, a int, b int, index(a))engine=innodb;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t values(i,i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
create database db2;
create table db2.t like db1.t
假设,要把 db1.t 里面 a>900 的数据行导出来,插入到 db2.t 中。
使用 mysqldump 命令将数据导出成一组 INSERT 语句。可以使用下面的命令:
mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
把结果输出到临时文件。
命令中,主要参数含义如下:
通过这条 mysqldump 命令生成的 t.sql 文件中就包含了如图 1 所示的 INSERT 语句。
图 1 mysqldump 输出文件的部分结果
可以看到,一条 INSERT 语句里面会包含多个 value 对,这是为了后续用这个文件来写入数据的时候,执行速度可以更快。
如果希望生成的文件中一条 INSERT 语句只插入一行数据的话,可以在执行 mysqldump 命令时,加上参数–skip-extended-insert。
可以通过下面这条命令,将这些 INSERT 语句放到 db2 库里去执行。
mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"
需要说明的是,source 并不是一条 SQL 语句,而是一个客户端命令。
mysql 客户端执行这个命令的流程是这样的:
服务端执行的并不是这个“source t.sql"语句,而是 INSERT 语句。所以,不论是在慢查询日志(slow log),还是在 binlog,记录的都是这些要被真正执行的 INSERT 语句。
直接将结果导出成.csv 文件。MySQL 提供了下面的语法,用来将查询结果导出到服务端本地目录:
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
使用这条语句时,需要注意如下几点。
得到.csv 导出文件后,就可以用下面的 load data 命令将数据导入到目标表 db2.t 中。
load data infile '/server_tmp/t.csv' into table db2.t;
这条语句的执行流程如下所示。
如果 binlog_format=statement,这个 load 语句记录到 binlog 里以后,怎么在备库重放呢?
由于 /server_tmp/t.csv 文件只保存在主库所在的主机上,如果只是把这条语句原文写到 binlog 中,在备库执行的时候,备库的本地机器上没有这个文件,就会导致主备同步停止。
所以,这条语句执行的完整流程,其实是下面这样的。
db2
.t
。执行流程如图 2 所示:
图 2 load data 的同步流程
这里备库执行的 load data 语句里面,多了一个“local”。它的意思是“将执行这条命令的客户端所在机器的本地文件 /tmp/SQL_LOAD_MB-1-0 的内容,加载到目标表 db2.t 中”。
load data 命令有两种用法:
注意
select …into outfile 方法不会生成表结构文件, 所以导数据时还需要单独的命令得到表结构定义。mysqldump 提供了一个–tab 参数,可以同时导出表结构定义文件和 csv 数据文件。这条命令的使用方法如下:
mysqldump -h$host -P$port -u$user ---single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv
这条命令会在 $secure_file_priv 定义的目录下,创建一个 t.sql 文件保存建表语句,同时创建一个 t.txt 文件保存 CSV 数据。
前面提到的 mysqldump 方法和导出 CSV 文件的方法,都是逻辑导数据的方法,也就是将数据从表 db1.t 中读出来,生成文本,然后再写入目标表 db2.t 中。
直接把 db1.t 表的.frm 文件和.ibd 文件拷贝到 db2 目录下,是否可行呢?
答案是不行的。因为,一个 InnoDB 表,除了包含这两个物理文件外,还需要在数据字典中注册。直接拷贝这两个文件的话,因为数据字典中没有 db2.t 这个表,系统是不会识别和接受它们的。
在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法,可以通过导出 + 导入表空间的方式,实现物理拷贝表的功能。
假设现在的目标是在 db1 库下,复制一个跟表 t 相同的表 r,具体的执行步骤如下:
至此,拷贝表数据的操作就完成了。这个流程的执行过程图如下:
图 3 物理拷贝表
关于拷贝表的这个流程,有以下几个注意点:
小结
对比一下这三种方法的优缺点。
后两种方式都是逻辑备份方式,是可以跨引擎使用的。
思考
binlog_format=statement 的时候,binlog 记录的 load data 命令是带 local 的。既然这条命令是发送到备库去执行的,那么备库执行的时候也是本地执行,为什么需要这个 local 呢?如果写到 binlog 中的命令不带 local,又会出现什么问题呢?
为了确保备库应用 binlog 正常。因为备库可能配置了 secure_file_priv=null,所以如果不用 local 的话,可能会导入失败,造成主备同步延迟。
另一种应用场景是使用 mysqlbinlog 工具解析 binlog 文件,并应用到目标库的情况。你可以使用下面这条命令 :mysqlbinlog $binlog_file | mysql -h$host -P$port -u$user -p$pwd
把日志直接解析出来发给目标库执行。增加 local,就能让这个方法支持非本地的 $host。
来自林晓斌《MySql实战45讲》
文章浏览阅读4.2k次。打开src目录下的AppDelegate.cpp文件,若无修改则在第45行处找到全局声明的Size变量,修改`designResolutionSize`中的大小即可。_cocos2dx设置窗口大小
文章浏览阅读1.6k次。测试代码:@PostMapping() public void test(@RequestBody Student student){ System.out.println(student.getLover().name()); }class Student{ private Lover lover; public Lover getLover() { return lover; } public void setLover_springboot get请求怎么接收前端传递的枚举数字
文章浏览阅读1.5w次,点赞24次,收藏120次。简单来说就是去量纲后的回归(因为你要比较不同变量之间的显著性的大小,那么带着量纲怎么比,所以先把量纲去掉,然后再比较)官话:为了更为精准的研究影响评价量的重要因素(去除量纲的影响),我们可考虑使用标准化回归系数。_stata两个虚拟变量的交互项
文章浏览阅读203次。有时候安装mysql后使用mysql命令时报错 Can't connect to MySQL server on localhost (10061),或者用net start mysql 时报服务名无效,一般是因为mysql服务没有启动。这时候可以用管理身份运行cmd.exe(注意必须是管理..._c:\program files\mysql\mysql server 5.6\bin>mysqld --install install/remove
文章浏览阅读6.2k次,点赞3次,收藏44次。亚信联创科技校园招聘B 卷考试时间60_分钟 _考试方式(闭)卷(本试卷满分 100 分,答案请写在答题卡上)请不要在问卷上答题或涂改,笔试结束后请务必交回试卷部分内容分值备注一、计算机基础40分C/C++语言基础40分技能部分二、二选一JAVA 语言基础40分三、数据库20分总分100 分第一部分——计算机基础一、选择题(每题 2 分,总分 40分)1.CPU 状态分为目态和管态两种..._亚信科技java实习笔试题
文章浏览阅读1.3k次。3年对一个程序员来说是非常重要的。像我自己本身就是做程序员的,目前的薪资待遇是13K左右,虽然在我所在的公司不是最高的,但在所在的这个城市的消费水平来说,除了日常的开支,包括房租、水电、伙食、人际交往等费用之外,还能留下一部分闲钱自己存起来。不同城市的薪资待遇是不一样的,这主要是由于当地的消费水平和经济发展水平不同,所以如果你想要更高的薪资待遇,就要考虑在一线城市或者经济发达的城市工作。一个有着丰富工作经验的程序员,他的技能水平、经验和能力都比没有经验的程序员更加出色,所以他们的薪资待遇也会更高一些。_三线城市学java
文章浏览阅读418次。标签PostgreSQL , 标签 , 推荐系统 , 实时圈人 , 数组 , gin , gist , 索引 , rum , tsvector , tsquery , 万亿 , user , tag , 淘宝背景我们仅用了PostgreSQL的两个小特性,却解决了业务困扰已久的大问题。推荐系统是广告营销平台的奶牛,其核心是精准、实时、..._实时圈人
文章浏览阅读430次。软件测试风险追踪表风险追踪表 项目名称: 填制人: 编号 风险描述 影响 风险等级 发生的可能性 应对策略 状态 责任人 备注 ..._软件测试风险管理表格
文章浏览阅读1.2k次。一、AAC音频格式种类有哪些AAC音频格式是一种由MPEG-4标准定义的有损音频压缩格式。AAC包含两种格式 ADIF(Audio Data Interchange Format音频数据交换格式)和ADTS(Audio Data transport Stream音频数据传输流)。ADIF特点:可以确定的找到音视频数据的开始,不需要进行在音视频数据流中间开始的解码,它的解码必须在明确的定义开始。应用场景:常用在磁盘文件中。ADTS特点:具有同步字的比特流,解码可以在这个流中任何位置开始。类似于mp_aac adts
文章浏览阅读213次。像要使用Resouce类,必须创建一个 Resouce 文件夹,然后把需要的资源放进去,才可以在代码中设置路径进行访问_unity基本概念
文章浏览阅读2.4k次。指定自定义 CI/CD 配置文件,顾名思义就是在项目中指定文件来代替默认的.gitlab-ci.yml文件的方式来运行流水线。以往我们在使用流水线的时候,都是默认将.gitlab-ci.yml文件存在在项目的跟路径下,但是我们也可以指定备用文件名路径,或者不想在每个项目中来维护这个yml文件,那么通过自定义 CI/CD 配置文件便可以实现。_gitlab配置cicd
文章浏览阅读1w次。出现这个表示如果设置了自动增长,字段类型应该设置为int整型。_sql 错误 [1063] [42000]: incorrect column specifier for column 'id' incorrec