MySQL自增ID耗尽实例讲解

显示定义ID

表定义的自增值ID达到上限后，在申请下一个ID时，得到的值保持不变

-- (2^32-1) = 4,294,967,295
-- 建议使用 BIGINT UNSIGNED
CREATE TABLE t (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT=4294967295;
INSERT INTO t VALUES (null);

-- AUTO_INCREMENT没有改变
mysql> SHOW CREATE TABLE t;
+-------+------------------------------------------------------+
| Table | Create Table           |
+-------+------------------------------------------------------+
| t  | CREATE TABLE `t` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------+

mysql> INSERT INTO t VALUES (null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'

InnoDB row_id

1、如果创建的InnoDB表没有指定主键，那么InnoDB会创建一个不可见的，长度为6 Bytes的row_id

2、InnoDB维护一个全局的dict_sys.row_id值，所有无主键的InnoDB表，每插入一行数据

• 都将当前的dict_sys.row_id值作为要插入数据的row_id，然后把dict_sys.row_id的值+1

3、代码实现上，row_id是一个8 Bytes的BIGINT UNSIGNED

• 但InnoDB设计时，给row_id只保留了6 Bytes的空间，写到数据表时只会存放最后的6 Bytes
• row_id的取值范围：0 ~ 2^48-1
• 达到上限后，下一个值就是0

4、在InnoDB里面，申请到row_id=N后，就将这行数据写入表中

• 如果表中已经有row_id=N的行，新写入的行就会覆盖原有的行

5、推荐显示创建自增主键

• 表自增ID达到上限后，再插入数据时会报主键冲突的错误，影响的是可用性
• 而覆盖数据，意味着数据丢失，影响的是可靠性
• 一般来说，可靠性优于可用性

XID

1、redolog和binlog相配合的时候，有一个共同的字段XID，对应一个事务

2、生成逻辑

• MySQL内部维护一个全局变量global_query_id
• 每次执行语句的时候将global_query_id赋值给Query_id，然后global_query_id+1
• 如果当前语句是这个事务执行的第一条语句，把Query_id赋值给这个事务的XID

3、global_query_id是一个纯内存变量，重启之后清零

• 因此，在同一个数据库实例中，不同事务的XID也有可能是相同的
• MySQL重启之后，会重新生成新的binlog
  • 保证：同一个binlog文件里，XID是唯一的
• global_query_id达到上限后，就会继续从0开始计数
  • 因此理论上，同一个binlog还是会出现相同的XID，只是概率极低

4、global_query_id是8 Bytes，上限为2^64-1

• 执行一个事务，假设XID是A
• 接下来执行2^64次查询语句，让global_query_id回到A
• 再启动一个事务，这个事务的XID也是A

InnoDB trx_id

1、XID是由Server层维护的

2、InnoDB内部使用的是trx_id，为的是能够在InnoDB事务和Server层之间做关联

3、InnoDB内部维护一个max_trx_id的全局变量

• 每次需要申请一个新的trx_id，就获得max_trx_id的当前值，然后max_trx_id+1

4、InnoDB数据可见性的核心思想

• 每一行数据都记录了更新它的trx_id
• 当一个事务读到一行数据的时候，判断数据可见性的方法
  • 事务的一致性视图和这行数据的trx_id做对比

5、对于正在执行的事务，可以通过information_schema.innodb_trx看到事务的trx_id

操作序列