杨建荣的学习笔记

每天坚持一点点,个人微信公众号: jianrong-notes, 个人邮箱:jeanrock@126.com

  • 博客访问: 12577995
  • 博文数量: 1312
  • 用 户 组: 普通用户
  • 注册时间: 2012-05-14 23:24
  • 认证徽章:
个人简介

每日发文,或技术、或总结,偶有日间小事也以为记,谓之学习笔记,成年累月1200多天,中间几乎没有间断,要旨只有一个:学习交流,共同进步 。 学习笔记精华整理,个人新书《Oracle DBA工作笔记》已开售,在京东,当当,亚马逊,淘宝,天猫均有售,欢迎选购。

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(1312)

文章存档

2017年(246)

2016年(358)

2015年(360)

2014年(278)

2013年(48)

2012年(21)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: MySQL

    MySQL里面有一个问题尤其值得注意,那就是自增列的重复值问题,之前也简单分析过一篇,但是在后续我想了下,还有很多地方需要解释,一个就是从库的自增列是如何维护的,是否重启从库,自增列会受到影响。

   我们继续来测试一下。首先复现这个问题。

   创建表t1,插入3行数据。

use test;
[test]> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.01 sec)
> create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
insert into t1 values (1,2);
insert into t1 values (null,2);
insert into t1 values (null,2);
[test]> select *from t1;               
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    2 |
|  3 |    2 |
+----+------+因为存在3行数据,这个时候自增列的值是4.

[test]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)我们删除id值最大的记录id=3

mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.02 sec)这个时候会发现AUTO_INCREMENT=4的值不会有任何变化。

我们来挖掘一下binlog的内容,就会发现insert语句很特别。

# /usr/local/mysql_5.7.17/bin/mysqlbinlog --socket=/home/data/s1/s1.sock --port=24801 -vv  /home/data/s1/binlog.000001可以看到insert语句是MySQL独有的语法形式。
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
# at 2271

delete也会基于行级变更,定位到具体的记录的方式来删除。

### DELETE FROM `test`.`t1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
# at 2509
我们重启一下数据库。

# mysqladmin --socket=/home/data/s1/s1.sock --port=24801 shutdown                        
# /bin/sh /usr/local/mysql_5.7.17/bin/mysqld_safe --defaults-file=/home/data/s1/s1.cnf &重启之后就会发现情况发生了变化,原来的自增值4现在变为了3,这个也是基于max(id)+1的方式来计算的。

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)这个时候我们来关注一下从库,从库的自增列值会变化吗?
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)这个时候就会发现重启数据库以后,主从的自增列的值不同了。
那么我们来进一步测试,在主库插入一条记录,这样自增列的值就是4.

mysql> insert into t1 values (null,2);
Query OK, 1 row affected (0.01 sec)自增列的值为4,而从库的自增列的值依旧没有任何变化。

继续插入一条记录,这个时候主库的自增列就会是5

mysql> insert into t1 values (null,2);
Query OK, 1 row affected (0.00 sec)而从库呢,这个时候自增列会持续发生变化吗?我们来验证一下,这个时候从库的自增列又开始生效了。

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
还有一点需要注意,那就是指定了自增列的值,这一点上和Oracle有一定的差距,但是又很相似。
这个时候数据库主库中的数据如下:

mysql> select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    2 |
|  3 |    2 |
|  4 |    2 |
|  5 |    2 |
+----+------+
5 rows in set (0.00 sec)为了方便测试,我们继续插入一条数据,这一次我指定了id值。

mysql> insert into t1 values(6,2);
Query OK, 1 row affected (0.00 sec)让人感到安慰的是,这张情况下自增列还是会持续增加。

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)此时查看从库,这个自增列也还是7,,
通过这个案例,我们能够看到在MySQL会存在这样一类问题,实际上在多环境历史数据归档的情况下,如果主库重启,很可能会出现数据不一致的情况。

  我也在MySQL的官方bug列表中看到很多人在讨论这个问题,看来很多人碰到这个坑。而这个问题其实细究起来实现也不是一个很繁琐的工作,为什么一直没有修复。

   这个问题在MySQL很久以前就有,在现在依旧存在,什么时候会修复呢,根据官方的计划会在8.0中修复。让我们拭目以待。



阅读(2598) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册