数据库自增列简介自增列的主要目的是自动生成行的唯一 ID，对于不同的数据库或是存储引擎，还有一些特别的意义。 大部分数据库的使用者会使用自增列做为聚簇索引，以 innodb 为例，在内存中数据存放到 B Tree 的一个页子结点上，数据按照主键顺序存放，每次插入数据时，会根据主键插入适当的位置。如果使用自增 ID，数据插入相当于顺序插入，不会移动现有数据的位置; 如果不使用自增 ID，则有可能为了插入数据而移动数据，如果内存页已经回写的磁盘，则需要从磁盘上读回来，同时，频繁的移动和分页，会带来大量的碎片。 自增 ID，一般使用一个整数来表示，而其它的一些方案，如 UUID 等一般会使用一个长字符串来表示（一般为 16 字节），存储空间和网络都会有一些浪费。 现在业务中接触到的很多客户，可能是习惯或是方便，一般不指定与某个有实际意义的字段为主键，而是使用自增列，其它维度查询列建索引，如果存储系统接口不支持的话，它们就会用 UUID 来代替，不会考虑与之相关的一些问题。 本文主要是了解一下自增列的实现原理
单机自增列实现方式单机的数据库，主要有两种自增列的实现方式：
当然，也可以使用 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 有不同的处理方式：
基本实现原理 innodb 会在内存中保存自增列的当前值，每次插入数据，如果能提前知道要插入多少行数据，则一次性从中取出 n 个值，并将其值 n（假设使用默认步长：auto_increment_increment=1）。
innodb 提供了 3 种锁模式： “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 的主备方式谨慎使用，一旦使用不当可能会导致不一致 分布式自增列设计在分布式环境下，要支持自增列，要根据需求做一些取舍。功能由高到低可以分为
一般有两种方案，一种是提前划分好范围，一种是使用一个中心点来分配自增值。 自增列值分区 可以使用高位分区，也可以使用类似于 innodb 支持的这种低位分区，这种方式的优点是实现简单，在区段内可以做成语义和 innodb 一样, 缺点是限制了自增列的范围，而且需要提前定义好分区数，不容易扩展。 中心点分配自增值方式 所有的 insert like stmt 都需要去一个中心点去获取自增值（可以用 redis 等来实现），但是如果每次去取的话，性能一段很差，可以预取一部分，如果涉及到 bulk insert 或是 mixed-mode insert，则需要根据一些条件，来决定是否清空其它结点的预取缓存，来保证维一性，还需要考虑一些异常情况，比如结点重启，实现起来特别复杂，这里不再详述，当时设计这个东西，状态积很复杂，性能也是问题，所以最好限制 bulk insert 和 mixed insert 的使用（实际上 bulk insert 和 mixed insert 也只有在一些导入数据的场景下会用到）。 总之分布式架构下，自增列的设计比较灵活，一般不会完整实现 mysql 类似的语义，除非是想尽可能的兼容 mysql，所以很多系统设计 schema 时会用 UUID 来代替自增值，以牺牲一些单机的性能和网络的开销为代价。 自增列的那些坑删除最新一行后重启
这个问题的根本原因是因为 innodb 的自增列当前值是放在内存中，并没有持久化，重启后是通过 select max(c1) from t1 来恢复的，所以会导致不一致。这个问题在大部分情况下也不会导致问题，但是涉及到主备同步的时候则会出问题，特点是基于 stmt 的方式。 那很问题是 innodb 为什么不将这个值持久化呢，很可能是认为每次进行自增列分配都写 log 会有性能问题，但是应该对性能影响很小，根据阿里 mysql 团队做的一个测试，性能影响在 1% 左右。实际上 MyISAM 每次都是持久化的。 MIXED-MODE INSERT 预取规则
为什么最后一条记录是 (8,5) 而不是 (4,5)，这是因为 innodb 的实现中，是先分配自增列值，然后插入时才进行唯一性判断，第一条 SQL 分配自增值成功，但是插入失败，所以虽然 stmt 执行没有成功，但是自增值已经加上去。 MIXED-MODE INSERT 多预取一位
实际上这种情况可以避免，为什么设计成这样，还没有找到原因。 自增列溢出问题 自增列溢出并不会报错，只有检测到重复才会报错，所以最好定义为 uint64 自增与事务 当前自增值不依赖于事务，事务回滚，自增值不会回滚 参考资料1. 全局唯一 ID 生成方案对比 http://www.trueeyu.com/?p=2105 sohu-dba 本文转载自：微信公众账号 - SOHU-DBA，版权归原作者所有！ | ||||||||
|
声明：文章版权归原作者所有 部分文章转自互联网 如有侵权请联系
[邮箱地址] 删除
|
注册
登录
