MySQL自增ID耗尽实例讲解

发布时间:2020-08-31 11:33:37 作者:点滴积累
来源:脚本之家 阅读:178

显示定义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表,每插入一行数据

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

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

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

XID

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

2、生成逻辑

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

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

InnoDB trx_id

1、XID是由Server层维护的

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

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

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

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

操作序列

时刻 session A session B
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);
T4 SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;

-- T2时刻
mysql> SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;
+-----------------+---------------------+
| trx_id   | trx_mysql_thread_id |
+-----------------+---------------------+
| 281479812572992 |     30 |
+-----------------+---------------------+

-- T4时刻
mysql> SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;
+-----------------+---------------------+
| trx_id   | trx_mysql_thread_id |
+-----------------+---------------------+
| 7417540   |     30 |
+-----------------+---------------------+

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+
| Id | User   | Host  | db     | Command | Time | State     | Info    |
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL    | Daemon | 344051 | Waiting on empty queue | NULL    |
| 30 | root   | localhost | test    | Sleep | 274 |      | NULL    |
| 31 | root   | localhost | information_schema | Query |  0 | starting    | SHOW PROCESSLIST |
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+

1、trx_mysql_thread_id=30就是线程ID,即session A所在的线程

2、T1时刻,trx_id的值其实为0,而很大的值只是为了显示用的(区别于普通的读写事务)

3、T2时刻,trx_id是一个很大的数字,因为在T1时刻,session A并未涉及更新操作,是一个只读事务

4、session A在T3时刻执行INSERT语句时,InnoDB才真正分配trx_id

只读事务

1、在上面的T2时刻,很大的trx_id是由系统临时计算出来的

2、同一个只读事务在执行期间,它的指针地址是不会变的

3、如果有多个并行的只读事务,每个事务的trx变量的指针地址肯定是不同的

4、加上2^48的目的:保证只读事务显示的trx_id值比较大,用于区别普通的读写事务

5、trx_id与row_id的逻辑类似,定义长度为8 Bytes

6、只读事务不分配trx_id的好处

7、max_trx_id会持久化存储,重启不会重置为0,只有到达2^48-1的上限后,才会重置为0

thread_id

1、SHOW PROCESSLIST的第一列就是thread_id

2、系统保存了一个环境变量thread_id_counter

3、thread_id_counter定义为4 Bytes,因此达到2^32-1后就会重置为0

do {
  new_id= thread_id_counter++;
} while (!thread_ids.insert_unique(new_id).second);

参考资料

《MySQL实战45讲》

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对亿速云的支持。

推荐阅读:
  1. Mysql自增id的示例分析
  2. mysql中自增id的示例分析

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql 自增 id

上一篇:js实现快速搜索的方法

下一篇:jQuery只实现双击事件的方法

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》