中共是这样的政党 -- 既千方百计阻止你知道真相, 又千方百计指责你不明真相 :)

mysql5.7 主键 auto_inc 问题

MySQL 2018-09-28 浏览量: 983 字数统计: 643 最后更新: 2018-09-28 13:35

文章目录[显示]

[TOC]

问题:

  1. replace 序列表自增字段 同步状态下innodb表和myisam表的区别 ?
  2. update 自增字段 innodb表和myisam表的区别 ?
  3. delete 自增字段 innodb表和myisam表的区别 ?

前提 :

innodb 表中 自增主键分分配是:InnoDB数据字典内部一个计数器来决定的,而该计数器只在内存中维护,并不会持久化到磁盘中。当数据库重启时,该计数器会通过下面这种方式初始化。
SELECT MAX(ai_col) FROM table_name FOR UPDATE;

  • innodb表计数器缓冲在内存 重启后会重新计算,而myisam的计数器是固化在表头
  • update 操作 AUTO_INCREMENT 不会变

replace binlog映射形式:

符合下列条件后replace操作是否冲突映射binlog表现形式
只有主键没冲突insert
只有主键有冲突update
主键唯一键同时存在主键冲突先删除后插入
主键唯一键同时存在唯一键冲突update
主键唯一键同时存在同时冲突先删除再更新

innodb

测试(update 操作 AUTO_INCREMENT 不会变)

"[email protected]:mysql3306.sock  [db1]>select * from t2;
+----+--------+
| id | a      |
+----+--------+
|  3 | mysql  |
|  4 | python |
+----+--------+
2 rows in set (0.00 sec)

"[email protected]:mysql3306.sock  [db1]>update t2 set id=7 where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

"[email protected]:mysql3306.sock  [db1]>show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

"[email protected]:mysql3306.sock  [db1]>select * from t2;
+----+--------+
| id | a      |
+----+--------+
|  3 | mysql  |
|  7 | python |
+----+--------+
2 rows in set (0.00 sec)

"[email protected]:mysql3306.sock  [db1]>insert into t2 values (null,'a');
"[email protected]:mysql3306.sock  [db1]>insert into t2 values (null,'b');
"[email protected]:mysql3306.sock  [db1]>insert into t2 values (null,'c');
ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'       # (此刻的 auto_inc是8,下一次再insert, auto_inc 就是9 了,所以下一次再插入就不会冲突了 )
"[email protected]:mysql3306.sock  [db1]>insert into t2 values (null,'c');
Query OK, 1 row affected (0.01 sec)

测试(主键唯一键同时存在,且唯一键冲突,改为update操作)

A,B两台机器主从,t2自增表
1 replace 序列表自增字段 同步状态下innodb表和myisam表的区别 ?

主库:

"[email protected]:mysql3306.sock  [db1]>show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

"[email protected]:mysql3306.sock  [db1]>select * from t2 order by id;
+----+--------+
| id | a      |
+----+--------+
|  3 | mysql  |
|  5 | a      |
|  6 | b      |
|  7 | python |
|  9 | d      |
| 10 | c      |
| 11 | f      |
+----+--------+
7 rows in set (0.00 sec)

"[email protected]:mysql3306.sock  [db1]>replace into t2 values (null,'mysql');
Query OK, 2 rows affected (0.01 sec)

"[email protected]:mysql3306.sock  [db1]>replace into t2 values (null,'mysql');
Query OK, 2 rows affected (0.01 sec)

"[email protected]:mysql3306.sock  [db1]>select * from t2 order by id;
+----+--------+
| id | a      |
+----+--------+
|  5 | a      |
|  6 | b      |
|  7 | python |
|  9 | d      |
| 10 | c      |
| 11 | f      |
| 13 | mysql  |
+----+--------+
7 rows in set (0.00 sec)

"[email protected]:mysql3306.sock  [db1]>show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

从库:

"[email protected]:mysql3307.sock  [db1]>show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

"[email protected]:mysql3307.sock  [db1]>select * from t2 order by id;
+----+--------+
| id | a      |
+----+--------+
|  5 | a      |
|  6 | b      |
|  7 | python |
|  9 | d      |
| 10 | c      |
| 11 | f      |
| 13 | mysql  |
+----+--------+
7 rows in set (0.00 sec)

主库的binlog

### UPDATE `db1`.`t2`
### WHERE
###   @1=12 /* INT meta=0 nullable=0 is_null=0 */
###   @2='mysql' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### SET
###   @1=13 /* INT meta=0 nullable=0 is_null=0 */
###   @2='mysql' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */

结论:主库上执行replace更新auto,生成update binlog形式,传递到从库,从库auto不变。

问题:既然update不会更新auto_inc 的值,为什么从库的auto_inc没变而 主库的 auto_inc却变了???


测试(主键 唯一索引同时冲突 或主键冲突,为先删除再插入)

主库:

"[email protected]:mysql3306.sock  [db1]>show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

"[email protected]:mysql3306.sock  [db1]>select * from t2;
+----+--------+
| id | a      |
+----+--------+
|  5 | a      |
|  6 | b      |
| 10 | c      |
|  9 | d      |
| 11 | e      |
|  3 | mysql  |
|  7 | python |
+----+--------+
7 rows in set (0.00 sec)

"[email protected]:mysql3306.sock  [db1]>replace into t2 values (11,'e');
Query OK, 2 rows affected (0.00 sec)

"[email protected]:mysql3306.sock  [db1]>show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

"[email protected]:mysql3306.sock  [db1]>replace into t2 values (11,'f');
Query OK, 2 rows affected (0.00 sec)

"[email protected]:mysql3306.sock  [db1]>show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

从库:

"[email protected]:mysql3307.sock  [db1]>show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

主库binlog

### DELETE FROM `db1`.`t2`
### WHERE
###   @1=11 /* INT meta=0 nullable=0 is_null=0 */
###   @2='e' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### INSERT INTO `db1`.`t2`
### SET
###   @1=11 /* INT meta=0 nullable=0 is_null=0 */
###   @2='f' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 64338

结论:主库replace主键冲突,生成binlog形式为 先删除后插入,传到从库,auto变更。


结论就是 replace 主库正常涨,如果是update传到从库那么就不涨,如果是 delete 然后 insert就会触发涨

有个小问题:replace 唯一键冲突的话 ,同样都是转化成 update ,为什么主库auto_inc 就涨了 而从库没涨(从库不涨很正常,因为 update 操作都不涨)???


8.0 自增主键持久化的解决思路

将自增主键的计数器持久化到redo log中。每次计数器发生改变,都会将其写入到redo log中。如果数据库发生重启,InnoDB会根据redo log中的计数器信息来初始化其内存值。为了尽量减小对系统性能的影响,计数器写入到redo log中,并不会马上刷新。具体可参考:https://dev.mysql.com/worklog/task/?id=6204

非 8.0 如果想实现持久化类似的功能 可以参考

https://mp.weixin.qq.com/s/fZhYlfyIaRycrYoDditMIA

myisam 表的自增是持久化到表头

所以 myisam 不会出现 innodb 类似的问题

小蜗牛 说:
Freedom is the source from which all meaning and all values spring .


文章版权归 原文作者所有丨本站默认采用CC-BY-NC-SA 4.0协议进行授权| 转载必须包含本声明,并以超链接形式注明原文作者和本文原始地址: https://www.tougetu.com/2018/09/mysql57-auto.html

2 条评论

  1. 就不填

    代码高亮给你满分?

  2. wukuai

    补充一个,如果发生了n个不包含主键的冲突,某一行有2个以上的唯一键冲突,则也是先删除后插入

添加新评论

代码 Pastebin Gist 加粗 删除线 斜体 链接 签到