简介

自增列的主要目的是自动生成行的唯一ID,对于不同的数据库或是存储引擎,还有一些特别的意义。

大部分数据库的使用者会使用自增列做为聚簇索引,以innodb为例,在内存中数据存放到B+Tree的一个页子结点上,数据按照主键顺序存放,每次插入数据时,会根据主键插入适当的位置。如果使用自增ID,数据插入相当于顺序插入,不会移动现有数据的位置;如果不使用自增ID,则有可能为了插入数据而移动数据,如果内存页已经回写的磁盘,则需要从磁盘上读回来,同时,频繁的移动和分页,会带来大量的碎片。

自增ID,一般使用一个整数来表示,而其它的一些方案,如UUID等一般会使用一个长字符串来表示(一般为16字节),存储空间和网络都会有一些浪费。

现在业务中接触到的很多客户,可能是习惯或是方便,一般不指定与某个有实际意义的字段为主键,而是使用自增列,其它维度查询列建索引,如果存储系统接口不支持的话,它们就会用UUID来代替,不会考虑与之相关的一些问题。

本文主要是了解一下自增列的实现原理

单机自增列实现方式

单机的数据库,主要有两种自增列的实现方式:

  • DB2,Mysql,Sqlite,SqlServer需要明确指定一个表的某一列做为自增列,多个表之间不能共用。
  • oracle使用sequence来实现自增,多个表可能共用一个Sequence对象,更新灵活一些。

当然,也可以使用Mysql(table + function)来实现oracle sequence的功能(http://blog.csdn.net/maidou_2011/article/details/7758282)。个人感觉oracle这种方式更为简洁,使用起来也方便,mysql的这个设计,与表耦合太大。

innodb自增列实现

单纯的自增列实现原理很简单,但是因为其它的一些因素(非常用SQL,性能,数据同步,事务)等问题,则导致需要注意很多细节,所以理解其原理,对于使用,以及设计分布式环境下的自增机制还是有意义的。

mysql不同存储引擎的自增列实现也不同,比如MyISAM支持索引的第二列做为自增列,从而实现局部自增的功能(这个功能还是很有用的),而innodb仅支持索引的第一列为自增列。这里以innodb为例来分析自增列的实现。

所有产生新行的SQL都有可能涉及到自增列,主要有:INSERT,INSERT … ON DUPLICATE KEY UPDATE, REPLACE, REPLACE … SELECT, LOAD DATA。可以将其分为三类,innodb对于这三种不同的SQL有不同的处理方式:

  • “Simple inserts”:
    可以提前知道插入行数
    包含:INSERT, REPLACE

  • “Bulk inserts”:
    无法提前预知会插入多少行
    包含:INSERT … SELECT, REPLACE … SELECT, LOAD DATA

  • “Mixed-mode inserts:
    是”Simple inserts”的一种特殊情况, 手动指定了部分自增列的值
    包含:INSERT … ON DUPLICATE KEY UPDATE, INSERT
    实例:INSERT INTO t1 (c1,c2) VALUES (1,’a’), (NULL,’b’), (5,’c’), (NULL,’d’);

基本实现原理
innodb会在内存中保存自增列的当前值,每次插入数据,如果能提前知道要插入多少行数据,则一次性从中取出n个值,并将其值+n(假设使用默认步长:auto_increment_increment=1)。

1
2
3
4
5
6
7
struct dict_table_t {
...
lock_t* autoinc_lock; //表级自增值锁
ib_mutex_t* autoinc_mutex; //轻量级的自增值锁
ib_uint64_t autoinc; //当前自增值
...
}

innodb提供了3种锁模式:
0: “traditional” lock mode
1: “consecutive” lock mode
2: “interleaved” lock mode

“traditional” local mode

最严格的一种方式,对于所有的insert-like stmt,在执行stmt时加表级的自增值锁,stmt执行完释放;这种方式可以保证在大部分情况下自增值的连续性(事务回滚可能导致不连续),这种机制对于基于stmt的主备同步方式也是安全的。因为需要加表级的自增值锁,则高并发写时,性能是个问题。

“consecutive” lock mode

innodb默认的锁模式,对于bulk insert,需要在stmt执行时加表级的自增值锁,stmt执行完释放,而对于simple insert如果当前没有事务持有表级的自增值锁,则只需要加一个轻量级的自增值锁,分配自增值后就释放,而不是等到stmt执行完,锁粒度更小。这种锁方式的在大部分情况下,同”traditional” lock mode的结果是一样的,唯一的区别是对于mixed insert,因为要预取自增值,所以stmt之间可能会产生空洞(从代码上看,innodb对于mixed insert的处理不够精细,实际上是可以避免这个问题,当然也有可能是出于性能的考虑)。

“interleaved” lock mode

不使用表级的自增值锁,只使用轻量级的自增值锁,自增值分配完成后立即释放,不会等到stmt执行完,对于bulk insert在同一个stmt中插入的可能会产生空洞,对于基本stmt的同步可能造成不一致。

innodb自增列的一些限制

自增列必须为索引列的第一列

一个原因是自增列的值必须是唯一的,如果不是索引列,则新插入数据时,无法确定重复性,第二个原因是,innodb启动时,要通过select max(id) from table_name; 来恢复当前自增列的值,如果不是索引列的话,就会造成全表扫描,性能也是不可接受的。

扩展

一旦使用自增列,可扩展性是一个问题,如果考虑到以后拆表的需求,innodb提供了一套机制(auto_increment_increment和auto_increment_offset来设定偏移和步长),来实现分表。

主备复制

基于stmt的主备方式谨慎使用,一旦使用不当可能会导致不一致

分布式自增列设计

在分布式环境下,要支持自增列,要根据需求做一些取舍。功能由高到低可以分为

  • 自增列值的唯一性
  • 自增列值的递增性
  • stmt内部id的连续性
  • stmt之间id的连续性

一般有两种方案,一种是提前划分好范围,一种是使用一个中心点来分配自增值。

自增列值分区

可以使用高位分区,也可以使用类似于innodb支持的这种低位分区,这种方式的优点是实现简单,在区段内可以做成语义和innodb一样, 缺点是限制了自增列的范围,而且需要提前定义好分区数,不容易扩展。

中心点分配自增值方式

所有的insert like stmt都需要去一个中心点去获取自增值(可以用redis等来实现),但是如果每次去取的话,性能一段很差,可以预取一部分,如果涉及到bulk insert或是mixed-mode insert,则需要根据一些条件,来决定是否清空其它结点的预取缓存,来保证维一性,还需要考虑一些异常情况,比如结点重启,实现起来特别复杂,这里不再详述,当时设计这个东西,状态积很复杂,性能也是问题,所以最好限制bulk insert和mixed insert的使用(实际上bulk insert和mixed insert也只有在一些导入数据的场景下会用到)。

总之分布式架构下,自增列的设计比较灵活,一般不会完整实现mysql类似的语义,除非是想尽可能的兼容mysql,所以很多系统设计schema时会用UUID来代替自增值,以牺牲一些单机的性能和网络的开销为代价。

自增列的那些坑

删除最新一行后重启

1
2
3
4
5
6
7
8
9
10
11
create table t1(c1 int auto_increment, c2 int, primary key(c1));
insert into t1 values(NULL,1), (NULL, 2), (NULL, 3);
select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
如果删除第三行,再插入新一行(NULL, 4),自增列的值,则为(4, 4),但是如果删除第三行后,重启mysql,则再插入新一行(NULL, 4),自增列的值为(3, 4);

这个问题的根本原因是因为innodb的自增列当前值是放在内存中,并没有持久化,重启后是通过select max(c1) from t1来恢复的,所以会导致不一致。这个问题在大部分情况下也不会导致问题,但是涉及到主备同步的时候则会出问题,特点是基于stmt的方式。

那很问题是innodb为什么不将这个值持久化呢,很可能是认为每次进行自增列分配都写log会有性能问题,但是应该对性能影响很小,根据阿里mysql团队做的一个测试,性能影响在1%左右。实际上MyISAM每次都是持久化的。

mixed-mode insert预取规则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
insert into t1 values(1,1), (NULL, 2), (2, 3);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
insert into t1 values(1,1), (NULL, 2), (4, 3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

insert into t1 values(NULL, 5);
Query OK, 1 row affected (0.04 sec)

select * from t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 4 | 3 |
| 5 | 2 |
| 8 | 5 |
+----+------+
4 rows in set (0.00 sec)

为什么最后一条记录是(8,5)而不是(4,5),这是因为innodb的实现中,是先分配自增列值,然后插入时才进行唯一性判断,第一条SQL分配自增值成功,但是插入失败,所以虽然stmt执行没有成功,但是自增值已经加上去。

mixed-mode insert多预取一位

1
2
3
4
5
6
7
8
9
10
11
12
13
insert into t1 values(1, 1), (NULL, 2);                                  Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0

insert into t1 values(NULL, 3); Query OK, 1 row affected (0.04 sec)

select * from t1; +----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 4 | 3 |
+----+------+
3 rows in set (0.00 sec)

实际上这种情况可以避免,为什么设计成这样,还没有找到原因。

自增列溢出问题

自增列溢出并不会报错,只有检测到重复才会报错,所以最好定义为uint64

自增与事务

当前自增值不依赖于事务,事务回滚,自增值不会回滚

参考资料

1.全局唯一ID生成方案对比
2.MySQL为什么需要一个主键
3.MySQL 自增ID 和 UUID 做主键的初步性能研究
4.用mysql实现oracle sequence功能
5.为什么InnoDB表要建议用自增列做主键
6.AUTO_INCREMENT Handling in InnoDB
7.捉虫动态·auto_increment
8.MySQL内核月报_2015.01

Comments

2016-06-21