线上MySQL的自增id用尽怎么办？

mysql> insert into t values(null);

Query OK, 1 row affected (0.00 sec)

mysql> show create table t;

±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| t | CREATE TABLE t (

id int unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (id)

) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

//成功插入一行 4294967295

mysql> insert into t values(null);

ERROR 1062 (23000): Duplicate entry ‘4294967295’ for key ‘t.PRIMARY’

第一个insert成功后，该表的AUTO_INCREMENT还是4294967295，导致第二个insert又拿到相同自增id值，再试图执行插入语句，主键冲突。

2^32 - 1（4294967295）不是一个特别大的数，一个频繁插入删除数据的表是可能用完的。建表时就需要考虑你的表是否有可能达到该上限，若有，就应创建成8字节的bigint unsigned。

InnoDB系统自增row_id

================================================================================

若你创建的InnoDB表未指定主键，则InnoDB会自动创建一个不可见的，6个字节的row_id。InnoDB维护了一个全局的dict_sys->row_id值

所有无主键的InnoDB表，每插入一行数据，都将当前的dict_sys->row_id作为要插入数据的row_id，然后把dict_sys->row_id加1。

代码实现时row_id是个长度为8字节的无符号长整型(bigint unsigned)。但InnoDB在设计时，给row_id留的只是6个字节的长度，这样写到数据表中时只放了最后6个字节，所以row_id能写到数据表中的值，就有两个特征：

1. row_id写入表中的值范围，是从0到2^48 - 1

2. 当dict_sys.row_id=2^48时，如果再有插入数据的行为要来申请row_id，拿到以后再取最后6个字节的话就是0

即写入表的row_id从0~2^48 - 1。达到上限后，下个值就是0，然后继续循环。

2^48 - 1已经很大，但若一个MySQL实例活得久，还是可能达到上限。

InnoDB里，申请到row_id=N后，就将这行数据写入表中；若表中已经存在row_id=N的行，新写入的行就会覆盖原有的行。

验证该结论：通过gdb修改系统的自增row_id。用gdb是为了便于复现问题，只能在测试环境使用。

• row_id用完的验证序列

• row_id用完的效果验证

可见，在我用gdb将dict_sys.row_id设置为2^48之后，再插入a=2会出现在表t的第一行，因为该值的row_id=0。

之后再插入a=3，由于row_id=1，就覆盖了之前a=1的行，因为a=1这一行的row_id也是1。

所以应该在InnoDB表中主动创建自增主键：当表自增id到达上限后，再插入数据时会报主键冲突错误。

毕竟覆盖数据，就意味着数据丢失，影响数据可靠性；报主键冲突，插入失败，影响可用性。一般可靠性优于可用性。

Xid

==================================================================

redo log和binlog有个共同字段Xid，用来对应事务。Xid在MySQL内部是如何生成的呢？

MySQL内部维护了一个全局变量global_query_id

• 每次执行语句时，将它赋值给query_id，然后给该变量+1：

若当前语句是该事务执行的第一条语句，则MySQL还会同时把query_id赋值给该事务的Xid：

而global_query_id是一个纯内存变量，重启之后就清零了。所以同一DB实例，不同事务的Xid可能相同。

但MySQL重启之后会重新生成新binlog文件，这就保证同一个binlog文件里的Xid唯一。

虽然MySQL重启不会导致同一个binlog里面出现两个相同Xid，但若global_query_id达到上限，就会继续从0开始计数。理论上还是会出现同一个binlog里面出现相同Xid。

因为global_query_id8字节，上限2^64 - 1。要出现这种情况，需满足：

1. 执行一个事务，假设Xid是A

2. 接下来执行2^64次查询语句，让global_query_id回到A

2^64太大了，这种可能只存在于理论中。

3. 再启动一个事务，这个事务的Xid也是A

Innodb trx_id

=============================================================================

• Xid由server层维护

InnoDB内部使用Xid，为了关联InnoDB事务和server

但InnoDB自己的trx_id，是另外维护的事务id（transaction id）。

InnoDB内部维护了一个max_trx_id全局变量，每次需要申请一个新的trx_id时，就获得max_trx_id的当前值，然后并将max_trx_id加1。

InnoDB数据可见性的核心思想

每一行数据都记录了更新它的trx_id，当一个事务读到一行数据时，判断该数据是否可见，就是通过事务的一致性视图与这行数据的trx_id做对比。

对于正在执行的事务，你可以从information_schema.innodb_trx表中看到事务的trx_id。

看如下案例：事务的trx_id

| | S1 | S2 |

| — | — | — |

| t1 | begin

select * from t limit 1 | |

| t2 | | use information_schema;

select trx_id, trx_mysql_thread_id from innodb_trx |

| t3 | insert into t values(null) | |

| t3 | | select trx_id, trx_mysql_thread_id from innodb_trx |

S2 的执行记录：

mysql> use information_schema;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select trx_id, trx_mysql_thread_id from innodb_trx;

±----------------±--------------------+

| trx_id | trx_mysql_thread_id |

±----------------±--------------------+

| 421972504382792 | 70 |

±----------------±--------------------+

1 row in set (0.00 sec)

mysql> select trx_id, trx_mysql_thread_id from innodb_trx;

±--------±--------------------+

| trx_id | trx_mysql_thread_id |

±--------±--------------------+

| 1355623 | 70 |

±--------±--------------------+

1 row in set (0.01 sec)

S2从innodb_trx表里查出的这两个字段，第二个字段trx_mysql_thread_id就是线程id。显示线程id，是为说明这两次查询看到的事务对应的线程id都是5，即S1所在线程。

t2时显示的trx_id是一个很大的数；t4时刻显示的trx_id是1289，看上去是一个比较正常的数字。这是为啥？

最后

学习视频：

大厂面试真题：

nnodb_trx表里查出的这两个字段，第二个字段trx_mysql_thread_id就是线程id。显示线程id，是为说明这两次查询看到的事务对应的线程id都是5，即S1所在线程。

t2时显示的trx_id是一个很大的数；t4时刻显示的trx_id是1289，看上去是一个比较正常的数字。这是为啥？

最后

学习视频：

[外链图片转存中…(img-gY3J2YGv-1720125423396)]

大厂面试真题：

[外链图片转存中…(img-j9sovo4b-1720125423396)]