当前位置: 澳门新濠3559 > 数据库 > 正文

导致有数据上有冲突,1 innodb 自增列出现重复值

时间:2019-10-07 08:53来源:数据库
  1 innodb 自增列出现重复值的问题 [TOC] 在项目过程中遇到一个看似极为基础的问题,但是在深入思考后还是引出了不少问题,觉得有必要把这一学习过程进行记录。 MySQL的自增列(AU

 

澳门新濠3559 1

1 innodb 自增列出现重复值的问题

[TOC]
在项目过程中遇到一个看似极为基础的问题,但是在深入思考后还是引出了不少问题,觉得有必要把这一学习过程进行记录。

MySQL的自增列(AUTO_INCREMENT)和其它数据库的自增列对比,有很多特性和不同点(甚至不同存储引擎、不同版本也有一些不同的特性),让人感觉有点稍微复杂。下面我们从一些测试开始,来认识、了解一下这方面的特殊知识点:

    在一次宕机之后重启Mysql服务器并恢复数据的过程中发现了自增主键列的自增数值会回退,导致有数据上有冲突。经过一番的排查之后发现原来是主键自增值回退了,导致自增主键有重复导致外键关联失效引起的。

  先从问题入手,重现下这个bug 

MySQL主键设计原则

  • MySQL主键应当是对用户没有意义的。
  • MySQL主键应该是单列的,以便提高连接和筛选操作的效率
  • 永远也不要更新MySQL主键
  • MySQL主键不应包含动态变化的数据,如时间戳、创建时间列、修改时间列等
  • MySQL主键应当有计算机自动生成。

 

我们来举个例子说明一下具体的现象,例如,创建一个个InNoDB引擎表:

use test;
drop table t1;
create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;
insert into t1 values (1,2);insert into t1 values (null,2);
insert into t1 values (null,2);
select * from t1;
 ---- ------ 
| id | a |
 ---- ------ 
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
 ---- ------ 
delete from t1 where id=2;
delete from t1 where id=3;
select * from t1;
 ---- ------ 
| id | a |
 ---- ------ 
| 1 | 2 |
 ---- ------ 

主键设计的常用方案

 

CREATE TABLE `bsession`  (

这里我们关闭mysql,再启动mysql,然后再插入一条数据 

自增ID

优点

1、数据库自动编号,速度快,而且是增量增长,聚集型主键按顺序存放,对于检索非常有利。

2、 数字型,占用空间小,易排序,在程序中传递方便。

缺点
1、不支持水平分片架构,水平分片的设计当中,这种方法显然不能保证全局唯一。
2、表锁

在MySQL5.1.22之前,InnoDB自增值是通过其本身的自增长计数器来获取值,该实现方式是通过表锁机制来完成的(AUTO-INC LOCKING)。锁不是在每次事务完成后释放,而是在完成对自增长值插入的SQL语句后释放,要等待其释放才能进行后续操作。比如说当表里有一个auto_increment字段的时候,innoDB会在内存里保存一个计数器用来记录auto_increment的值,当插入一个新行数据时,就会用一个表锁来锁住这个计数器,直到插入结束。如果大量的并发插入,表锁会引起SQL堵塞。
在5.1.22之后,InnoDB为了解决自增主键锁表的问题,引入了参数innodb_autoinc_lock_mode:

  • 0:通过表锁的方式进行,也就是所有类型的insert都用AUTO-inc locking(表锁机制)。
  • 1:默认值,对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作,对于bulk insert 则还是使用表锁的方式进行。
  • 2:对所有的insert-like 自增长值的产生使用互斥量机制完成,性能最高,并发插入可能导致自增值不连续,可能会导致Statement 的 Replication 出现不一致,使用该模式,需要用 Row Replication的模式。

3、自增主键不连续

Create Table: CREATE TABLE `tmp_auto_inc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `talkid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=gbk
1 row in set (0.00 sec) 

当插入10条记录的时候,因为AUTO_INCREMENT=16,所以下次再插入的时候,主键就会不连续。

自增列持久化问题

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

insert into t1 values (null,2);
select * FROM T1;
 ---- ------ 
| id | a |
 ---- ------ 
| 1 | 2 |
 ---- ------ 
| 2 | 2 |
 ---- ------ 

UUID

优点
1、全局唯一性、安全性、可移植性。

2、能够保证独立性,程序可以在不同的数据库间迁移,效果不受影响。

3、保证生成的ID不仅是表独立的,而且是库独立的,在你切分数据库的时候尤为重要

缺点
导致有数据上有冲突,1 innodb 自增列出现重复值的问题。1、针对InnoDB引擎会徒增IO压力,InnoDB为聚集主键类型的引擎,数据会按照主键进行排序,由于UUID的无序性,InnoDB会产生巨大的IO压力。InnoDB主键索引和数据存储位置相关(簇类索引),uuid 主键可能会引起数据位置频繁变动,严重影响性能。
2、UUID长度过长,一个UUID占用128个比特(16个字节)。主键索引KeyLength长度过大,而影响能够基于内存的索引记录数量,进而影响基于内存的索引命中率,而基于硬盘进行索引查询性能很差。严重影响数据库服务器整体的性能表现。

 

`aname` varchar(80) NOT NULL DEFAULT ‘’,

  我们看到插入了(2,2),而如果我没有重启,插入同样数据我们得到的应该是(4,2);

自定义序列表

所谓自定义序列表,就是在库中建一张用于生成序列的表来存储序列信息,序列生成的策略通过程序层面来实现。如下所示,构建一张序列表:

CREATE TABLE `sequence` (
    `name` varchar(50) NOT NULL,
    `id` bigint(20) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`name`)
) ENGINE=InnoDB;

注意区别,id字段不是自增的,也不是主键。在使用前,我们需要先插入一些初始化数据:

INSERT INTO `sequence` (`name`) VALUES 
('users'), ('photos'), ('albums'), ('comments');

接下来,我们可以通过执行下面的SQL语句来获得新的照片ID:

UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id`   1) WHERE `name` = 'photos';
SELECT LAST_INSERT_ID();

我们执行了一个更新操作,将id字段增加1,并将增加后的值传递到LAST_INSERT_ID函数, 从而指定了LAST_INSERT_ID的返回值。

实际上,我们不一定需要预先指定序列的名字。如果我们现在需要一种新的序列,我们可以直接执行下面的SQL语句:

INSERT INTO `sequence` (`name`) VALUES('new_business') ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`   1);
SELECT LAST_INSERT_ID();

这种方案的问题在于序列生成的逻辑脱离了数据库层,由应用层负责,增加了开发复杂度。当然,其实可以用spring来解决这一问题,因为在spring JDBC中已经对这种序列生成逻辑进行了简单的封装。
我们可以看一下spring的相关源代码:MySQLMaxValueIncrementer.
澳门新濠3559 2

    @Override
    protected synchronized long getNextKey() throws DataAccessException {
        if (this.maxId == this.nextId) {
            /*
            * Need to use straight JDBC code because we need to make sure that the insert and select
            * are performed on the same connection (otherwise we can't be sure that last_insert_id()
            * returned the correct value)
            */
            Connection con = DataSourceUtils.getConnection(getDataSource());
            Statement stmt = null;
            try {
                stmt = con.createStatement();
                DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
                // Increment the sequence column...
                String columnName = getColumnName();
                stmt.executeUpdate("update "  getIncrementerName()   " set "   columnName  
                        " = last_insert_id("   columnName   "   "   getCacheSize()   ")");
                // Retrieve the new max of the sequence column...
                ResultSet rs = stmt.executeQuery(VALUE_SQL);
                try {
                    if (!rs.next()) {
                        throw new DataAccessResourceFailureException("last_insert_id() failed after executing an update");
                    }
                    this.maxId = rs.getLong(1);
                }
                finally {
                    JdbcUtils.closeResultSet(rs);
                }
                this.nextId = this.maxId - getCacheSize()   1;
            }
            catch (SQLException ex) {
                throw new DataAccessResourceFailureException("Could not obtain last_insert_id()", ex);
            }
            finally {
                JdbcUtils.closeStatement(stmt);
                DataSourceUtils.releaseConnection(con, getDataSource());
            }
        }
        else {
            this.nextId  ;
        }
        return this.nextId;
    }

spring的实现也就是通过update语句对incrementerName表里的columnName 列进行递增,并通过mysql的last_insert_id()返回最近生成的值。并保证了事务性及方法的并发支持。只是这个实现有些过于简单,比如:一个表对应一个序列的做法在实际应用开发中显得过于零碎,所以在实际应用中需要对其实现进行修改,实现一条记录对应一个序列的策略。另外对水平分片的支持并不在这一实现考虑范围内。同时,这种做法依然无法回避表锁的机制,所以这里通过CacheSize()的做法,实现了一次申请并缓存在内存中,以减少表锁的发生频率。

如果一个表拥有自增列,当前最大自增列值为9, 删除了自增列6、7、8、9的记录,重启MySQL服务后,再往表里面插入数据,自增列的值为6还是10呢?  如果表的存储引擎为MyISAM呢,又会是什么情况? 下面实验环境为MySQL 5.7.21

PRIMARY KEY (`id`)

  上面的测试反映了mysql重启后,innodb存储引擎的表自增id可能出现重复利用的情况。

如何解决水平分片的需求

 

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

  自增id重复利用在某些场景下回出现问题。依然用上面的例子,假设t1有个历史表t1_history用来存t1表的历史数据,那么mysqld重启前,ti_history中可能已经有了(2,2)这条数据,而重启后我们又插入了(2,2),当新插入的(2,2)迁移到历史表时,会违反主键约束。

UUID

由于UUID出现重复的概率基本可以忽略,所以对分片是天生支持的。

 

澳门新濠3559 3

 

独立的序列库

单独建立一个库用来生成ID,在Shard中的每张表在这个ID库中都有一个对应的表,而这个对应的表只有一个字段, 这个字段是自增的。当我们需要插入新的数据,我们首先在ID库中的相应表中插入一条记录,以此得到一个新的ID, 然后将这个ID作为插入到Shard中的数据的主键。这个方法的缺点就是需要额外的插入操作,如果ID库变的很大, 性能也会随之降低。所以一定要保证ID库的数据集不要太大,一个办法是定期清理前面的记录

mysql> drop table if exists test;

Query OK, 0 rows affected (0.08 sec)

 

mysql> create table test(id int auto_increment primary key, name varchar(32)) ENGINE=InnoDB;

Query OK, 0 rows affected (0.02 sec)

 

 

mysql> insert into test(name)

    -> select 'kkk1' from dual union all

    -> select 'kkk2' from dual union all

    -> select 'kkk3' from dual union all

    -> select 'kkk4' from dual union all

    -> select 'kkk5' from dual union all

    -> select 'kkk6' from dual union all

    -> select 'kkk7' from dual union all

    -> select 'kkk8' from dual union all

    -> select 'kkk9' from dual;

Query OK, 9 rows affected (0.01 sec)

Records: 9  Duplicates: 0  Warnings: 0

 

 

mysql> select * from test;

 ---- ------ 

| id | name |

 ---- ------ 

|  1 | kkk1 |

|  2 | kkk2 |

|  3 | kkk3 |

|  4 | kkk4 |

|  5 | kkk5 |

|  6 | kkk6 |

|  7 | kkk7 |

|  8 | kkk8 |

|  9 | kkk9 |

 ---- ------ 

9 rows in set (0.00 sec)

 

mysql> delete from test where id>=6;

Query OK, 4 rows affected (0.00 sec)

创建表测试表

2 innodb 自增列出现重复值的原因

复合标识符

这种做法是通过联合主键的策略,即通过两个字段来生成一个唯一标识,前半部分是分片标识符,后半部分是本地生成的标识符(比如使用AUTO_INCREMENT生成)

 

现在插入10条数据,再删除最后的几条,

  

带分库策略的自定义序列表

这种做法可以基于上面提到的自定义序列表的方法的基础上,做一些技巧性的调整。即如下:

UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id`   1) WHERE `name` = 'photos';
SELECT LAST_INSERT_ID();

这里的id初始值设定上要求不同的分片取不同的值,且必须连续。同时将每次递增的步长设定为服务器数目。
比如有3台机器,那么我们只要将初始值分别设置为1,2,3. 然后执行下面的语句即可:

UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id`   3) WHERE `name` = 'photos';
SELECT LAST_INSERT_ID();

这就可以解决主键生成冲突的问题。但是如果在运行一段时间后要进行动态扩充分片数的时候,需要对序列初始值做一次调整,以确保其连续性,否则依然可能存在冲突的可能。当然这些逻辑可以封装在数据访问层的代码中。

重启MySQL服务后,然后我们插入一条记录,字段ID会从什么值开始呢? 如下所示,如果表的存储引擎为InnoDB,那么插入的数据的自增字段值为6.

INSERT INTO `bsession` (`aname`) values ('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a’);

mysql> show create table t1G;
*************************** 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=utf8
1 row in set (0.00 sec)

主键的必要性

表中每一行都应该有可以唯一标识自己的一列(或一组列)。虽然并不总是都需要主键,但大多数数据库设计人员都应保证他们创建的每个表有一个主键,以便于以后数据操纵和管理。其实即使你不建主键,MySQL(InnoDB引擎)也会自己建立一个隐藏6字节的ROWID作为主键列,详细可以参见[这里]

因为,InnoDB引擎使用聚集索引,数据记录本身被存于主索引(一颗B Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL 会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)
澳门新濠3559 4

所以在使用innoDB表时要避免随机的(不连续且值的分布范围非常大)聚簇索引,特别是针对I/O密集型的应用。例如:从性能角度考虑,使用UUID的方案就会导致聚簇索引的插入变得完全随机。

 

DELETE FROM `bsession` where `id` in (8,9,10);

    建表时可以指定 AUTO_INCREMENT值,不指定时默认为1.这个值表示当前自增列的起始值大小,如果新插入的数据没有指定自增列的值,那么自增列的值即为这个起始值。建表时这个值会存储在.frm文件中。那么我们插入新的数据后,自增列的起始值会变大,这个变大的值会存回.frm文件吗?

主键的数据类型选择

关于主键的类型选择上最常见的争论是用整型还是字符型的问题,关于这个问题《高性能MySQL》一书中有明确论断:
整数通常是标识列的最好选择,因为它很快且可以使用AUTO_INCREAMENT,如果可能,应该避免使用字符串类型作为标识列,因为很消耗空间,且通常比数字类型慢。

如果是使用MyISAM,则就更不能用字符型,因为MyISAM默认会对字符型采用压缩引擎,从而导致查询变得非常慢。
参考:
1、
2、
3、
4、《高性能MySQL》
5、《高可用MySQL》

 

澳门新濠3559 5

   对于innodb表,这个值不会存回.frm中.而是存在内存中(dict_table_struct.autoinc)。那么又问,既然这个值没有存回.frm中,为什么我们每次插入新的值后, show create table t1看到AUTO_INCREMENT值是跟随变化的。其实show create table t1并没有去读frm取AUTO_INCREMENT,值,而是直接从dict_table_struct.autoinc取得的(ha_innobase::update_create_info)。

澳门新濠3559 6

模拟数据操作

   .frm中的AUTO_INCREMENT值,虽然不是实时更新的,但在我们在执行一些DDL重建表示还是更新auto_澳门新濠3559,increment值的。

 

删除操作之后,表中的数据仅有7条,最大的’id’ = 7。现在进行数据库重启,并重新插入10条数据。此时自增列是从8开始计数,还是从11开始计数呢?我想很多人都认为会从11开始计数,生成新纪录,但实际情况会超出我们的预期,下面我们实际验证一下:

 

 

澳门新濠3559 7

   知道了AUTO_INCREMENT是实时存储内存中的,同时.frm中的AUTO_INCREMENT值时不实时的。那么,mysqld 重启后,从哪里得到AUTO_INCREMENT呢? 内存值肯定是丢失了,.frm中的AUTO_INCREMENT是不准确的(很大可能比实际偏小).实际上mysql采用执行类似select max(id) 1 from t1;方法来得到AUTO_INCREMENT。而这种方法就会造成自增id重复的原因。

接下来,我们创建一个MyISAM类型的测试表。如下所示:

见证奇迹

 

 

你肯定会问了,这是为什么呢?

3 myisam也有这个问题吗

mysql> drop table if exists test;

Query OK, 0 rows affected (0.01 sec)

 

mysql> create table test(id int auto_increment  primary key, name varchar(32)) engine=MyISAM;

Query OK, 0 rows affected (0.02 sec)

 

mysql> 

 

insert into test(name)

select 'kkk1' from dual union all

select 'kkk2' from dual union all

select 'kkk3' from dual union all

select 'kkk4' from dual union all

select 'kkk5' from dual union all

select 'kkk6' from dual union all

select 'kkk7' from dual union all

select 'kkk8' from dual union all

select 'kkk9' from dual;

 

 

mysql> delete from test where id>=6;

Query OK, 4 rows affected (0.00 sec)

同样我也感觉很奇怪,于是就了解MySql的InnoDB引擎是如何处理自增列的:

    myisam是没有这个问题的。myisam表.frm文件也存AUTO_INCREMENT值,同innodb一样,这个值也不是实时的。myisam会将这个值实时存储在.MYI文件中(mi_state_info_write)。mysqld重起后会从.MYI中读取AUTO_INCREMENT值(mi_state_info_read)。因此,myisam表重启是不会出现自增id重复的问题。

 

原因是InnoDB引擎对AUTO_INCREMENT计数器是存放到主内存中的,并非硬盘。所以当重启后内存数据就丢失了!

 

删除了id>=6的记录后,重启MySQL服务,如下所示,测试结果为id =10, 那么为什么出现不同的两个结果呢?这个是因为InnoDB存储引擎中,自增主键没有持久化,而是放在内存中,关于自增主键的分配,是由InnoDB数据字典内部一个计数器来决定的,而该计数器只在内存中维护,并不会持久化到磁盘中。当数据库重启时,该计数器会通过SELECT MAX(ID) FROM TEST FOR UPDATE这样的SQL语句来初始化(不同表对应不同的SQL语句), 其实这是一个bug来着, 对应的链接地址为: 8.0 ,才将自增主键的计数器持久化到redo log中。每次计数器发生改变,都会将其写入到redo log中。如果数据库发生重启,InnoDB会根据redo log中的计数器信息来初始化其内存值。 而对应与MySIAM存储引擎,自增主键的最大值存放在数据文件当中,每次重启MySQL服务都不会影响其值变化。

我们来看官方文档:https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html,中的【InnoDB AUTO_INCREMENT Counter Initialization】一节的详细说明:

4 innodb 自增列出现重复问题修复

 

澳门新濠3559 8

    myisam选择将AUTO_INCREMENT实时存储在.MYI文件头部中。实际上.MYI头部还会实时存其他信息,也就是说写AUTO_INCREMENT只是个顺带的操作。其性能损耗可以忽略。InnoDB 表如果要解决这个问题,有两种方法。1)将auto_increment最大值持久到frm文件中。2)将 auto_increment最大值持久到聚集索引根页trx_id所在的位置。第一种方法直接写文件性能消耗较大,这是一额外的操作,而不是以个顺带的操作。如是我们采用第二种方案。为什么选择存储在聚集索引根页页头trx_id。页头trx_id中存存储trx_id,只对二级索引页和insert buf 页头有效(MVCC).而聚集索引根页页头trx_id这个值是没有使用的,始终保持初始值0.正好这个位置8个字节可存放自增值的值。我们每次更新AUTO_INCREMENT值时,同时将这个值修改到聚集索引根页页头trx_id的位置。 这个写操作跟真正的数据写操作一样,遵守write-ahead log原则,只不过这里只需要redo log ,而不需要undo log。因为我们不需要回滚AUTO_INCREMENT的变化(即回滚后自增列值会保留,即使insert 回滚了,auto_increment值不会回滚)

 

MySQL官网说明

    因此,AUTO_INCREMENT值存储在聚集索引根页trx_id所在的位置,实际上是对内存根页的修改和多了一条redo log(量很小),而这个redo log 的写入也是异步的,可以说是原有事务log的一个顺带操作。因此AUTO_INCREMENT值存储在聚集索引根页这个性能损耗是极小的。

澳门新濠3559 9

不过这个特性将在Mysql的下一个版本8.0中更改,自增计数器每次更改时,当前的最大自增计数器值将会被写入redo log中,并保存到每个检查点的 InnoDB引擎的私有系统表中,实现自增计数器的持久化,重启后会保持一致。

 

 

当服器在Crash中的恢复重启过程中,InnoDB使用存储在系统字典表里的当前最大自增值初始化到内存,并且从最后一个检查点开始扫描Redo Log中写入的计数器值。如果Redo Log中的值大于内存中的计数器值,Redo Log中的值将会被采用。

5 修复后的性能对比

 

关于后续版本中对于 自增列的处理机制 请查看官方文档的详细说明,这里不在赘述。https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

  我们新增了全局参数innodb_autoinc_persistent  取值on/off; on 表示将AUTO_INCREMENT值实时存储在聚集索引根页。off则采用原有方式只存储在内存。 

 

 

自增列细节特性

./bin/sysbench --test=sysbench/tests/db/insert.lua --mysql-port=4001 --mysql-user=root --mysql-table-engine=innodb --mysql-db=sbtest --oltp-table-size=0 --oltp-tables-count=1 --num-threads=100 --mysql-socket=/u01/zy/sysbench/build5/run/mysql.sock  --max-time=7200 --max-requests run
set global innodb_autoinc_persistent=off;
tps: 22199 rt:2.25ms
set global innodb_autoinc_persistent=on;
tps: 22003 rt:2.27ms

 

可以看出性能损耗在%1以下。

1:SQL模式的NO_AUTO_VALUE_ON_ZERO值影响AUTO_INCREMENT列的行为。

 

 

6 改进

mysql> drop table if exists test;

Query OK, 0 rows affected (0.01 sec)

 

mysql> create table test(id int auto_increment primary key, name varchar(32));

Query OK, 0 rows affected (0.02 sec)

 

mysql> select @@sql_mode;

 ------------------------------------------------------------------------------------------------------------------------------------------- 

| @@sql_mode                                                                                                                                |

 ------------------------------------------------------------------------------------------------------------------------------------------- 

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

 ------------------------------------------------------------------------------------------------------------------------------------------- 

1 row in set (0.00 sec)

 

mysql> insert into test(id, name) value(0, 'kerry');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

mysql> 

  新增参数innodb_autoinc_persistent_interval 用于控制持久化auto_increment值的频率。例如:innodb_autoinc_persistent_interval=100,auto_incrememt_increment=1时,即每100次insert会控制持久化一次auto_increment值。每次持久的值为:当前值 innodb_autoinc_persistent_interval.

 

  

如上所示,如果在SQL模式里面没有设置NO_AUTO_VALUE_ON_ZERO的话,那么在默认设置下,自增列默认一般从1开始自增,插入0或者null代表生成下一个自增长值。如果用户希望插入的值为0,而该列又是自增长的,那么这个选项就必须设置

测试结果如下

 

  innodb_autoinc_persistent=OFF

innodb_autoinc_persistent=ON

innodb_autoinc_persistent_interval=1

innodb_autoinc_persistent=ON

innodb_autoinc_persistent_interval=10

innodb_autoinc_persistent=ON

innodb_autoinc_persistent_interval=100

TPS 22199 22003

22069

22209

RT(ms)

2.25

2.27 2.26 2.25
mysql> SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test(id, name) value(0, 'kerry');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  0 | kerry |

|  1 | kerry |

 ---- ------- 

2 rows in set (0.00 sec)

 

mysql> 

 

 

注意:如果我们使用需要开启innodb_autoinc_persistent,应该在参数文件中指定,

 

innodb_autoinc_persistent= on

 如果这样指定set global innodb_autoinc_persistent=on;重启后将不会从聚集索引根页读取auto_increment最大值. 

 

两个疑问:

2:如果把一个NULL值插入到一个AUTO_INCREMENT数据列里去,MySQL将自动生成下一个序列编号。如下所示,这个语法对于熟悉SQL Server中自增字段的人来来看,简直就是不可思议的事情。

1 对于innodb和 myisam 存储引擎,.frm中的AUTO_INCREMENT是多余的。其他存储引擎没有研究,不知道有没有用处。

 

2 innodb表,重启通过select max(id) 1 from t1得到AUTO_INCREMENT值,如果id上有索引那么这个语句使用索引查找就很快。那么,这个可以解释mysql 为什么要求自增列必须包含在索引中的原因。 如果没有指定索引,则报如下错误,

mysql> drop table if exists test;

Query OK, 0 rows affected (0.03 sec)

 

mysql> create table test(id int auto_increment primary key, name varchar(32));

Query OK, 0 rows affected (0.05 sec)

 

mysql> insert into test(id , name) value(null, 'kerry');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

 

而myisam表竟然也有这个要求,感觉是多余的。

 

 

 

附:

3:获取当前自增列的值

innodb_autoinc_lock_mode 这个参数主要解决自增列主备复制问题的,用于控制自增列值连续性的。与本文无关,详细可以参考这里

 

innodb 自增列出现重复值的问题 先从问题入手,重现下这个bug use test; drop table t1; create table t1(id int auto_increment, a int , primary key (id)) engine...

    获取当前自增列的值,可以使用 LAST_INSERT_ID函数,注意,这个是一个系统函数,可获得自增列自动生成的最后一个值。但该函数只与服务器的本次会话过程中生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值,则该函数返回0

 

mysql> select last_insert_id();

 ------------------ 

| last_insert_id() |

 ------------------ 

|                1 |

 ------------------ 

1 row in set (0.00 sec)

 

mysql> insert into test(name) value('jimmy');

Query OK, 1 row affected (0.00 sec)

 

mysql> select last_insert_id();

 ------------------ 

| last_insert_id() |

 ------------------ 

|                2 |

 ------------------ 

1 row in set (0.00 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

|  2 | jimmy |

 ---- ------- 

2 rows in set (0.00 sec)

 

如果要获取自增列的下一个值,那么可以使用show create table tablename查看。如下截图所示

 

澳门新濠3559 10

 

 

4:自增列跳号

 

MySQL中,自增字段可以跳号:可以插入一条指定自增列值的记录(即使插入的值大于自增列的最大值),如下所示,当前自增列最大值为1,我插入一个200的值,然后就会以200为基础继续自增,而且我还可以继续插入ID=100的记录,无需任何额外设置。

 

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

mysql> insert into test value(200, 'test');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 200 | test  |

 ----- ------- 

2 rows in set (0.00 sec)

 

mysql> insert into test(name) value('test2');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 200 | test  |

| 201 | test2 |

 ----- ------- 

3 rows in set (0.00 sec)

 

mysql> 

mysql> insert into test(id, name) value(100, 'ken');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 100 | ken   |

| 200 | test  |

| 201 | test2 |

 ----- ------- 

4 rows in set (0.00 sec)

 

 

另外一个是关于自增列逻辑跳号问题,在一个事务里面,使用遇到事务回滚,自增列就会跳号,如下所示,id从201 跳到 203了。

 

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test(name) value('kkk');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 100 | ken   |

| 200 | test  |

| 201 | test2 |

| 202 | kkk   |

 ----- ------- 

5 rows in set (0.00 sec)

 

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test(name) value('kkk');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 100 | ken   |

| 200 | test  |

| 201 | test2 |

| 203 | kkk   |

 ----- ------- 

5 rows in set (0.00 sec)

 

当然,无论MySQL还是其他关系型数据库,都会遇到这种逻辑跳号的情况,例如ORACLE的序列也会存在这种逻辑跳号问题。为提高自增列的生成效率,都将生成自增值的操作设计为非事务性操作,表现为当事务回滚时,事务中生成的自增值不会被回滚。

 

5:truncate table操作会引起自增列从头开始计数

 

mysql> truncate table test;

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into test(name) value('kerry');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

mysql> 

 

6:修改AUTO_INCREMENT的值来修改自增起始值。

 

mysql> select * from test;

 ---- ------- 

| id | name  |

 ---- ------- 

|  1 | kerry |

 ---- ------- 

1 row in set (0.00 sec)

 

mysql> alter table test auto_increment=100;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> insert into test(name) value('k3');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test;

 ----- ------- 

| id  | name  |

 ----- ------- 

|   1 | kerry |

| 100 | k3    |

 ----- ------- 

2 rows in set (0.00 sec)

 

当然MySQL还有一些相关知识点,这里没有做总结,主要是没有遇到过相关场景。以后遇到了再做总结,另外一方面,写技术文章,很难面面俱到,这样太耗时也太累人了!

 

 

 

参考资料:

 

编辑:数据库 本文来源:导致有数据上有冲突,1 innodb 自增列出现重复值

关键词: 澳门新濠3559