MySQL 自增ID耗尽

曾经终败给现在 2023-02-28 05:41 40阅读 0赞

## 显示定义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。
    
    1.  但InnoDB设计时，给row\_id只保留了6 Bytes的空间，写到数据表时只会存放最后的6 Bytes
    2.  row\_id的取值范围：0 ~ 2^48-1
    3.  达到上限后，下一个值就是0
4.  在InnoDB里面，申请到row\_id=N后，就将这行数据写入表中。**如果表中已经有row\_id=N的行，新写入的行就会覆盖原有的行**
5.  推荐显示创建自增主键
    
    1.  表自增ID达到上限后，再插入数据时会报主键冲突的错误，影响的是可用性
    2.  而覆盖数据，意味着数据丢失，影响的是可靠性
    3.  一般来说，可靠性优于可用性

## XID ##

1.  redolog和binlog相配合的时候，有一个共同的字段XID，对应一个事务
2.  生成逻辑
    
    1.  MySQL内部维护一个全局变量global\_query\_id
    2.  每次执行语句的时候将global\_query\_id赋值给Query\_id，然后global\_query\_id+1
    3.  如果当前语句是这个事务执行的第一条语句，把Query\_id赋值给这个事务的XID
3.  global\_query\_id是一个纯内存变量，重启之后清零
    
    1.  因此，在同一个数据库实例中，不同事务的XID也有可能是相同的
    2.  MySQL重启之后，会重新生成新的binlog。 **保证：同一个binlog文件里，XID是唯一的**
4.  global\_query\_id达到上限后，就会继续从0开始计数 。因此理论上，同一个binlog还是会出现相同的XID，只是概率极低
5.  global\_query\_id是8 Bytes，上限为2^64-1
    
    1.  执行一个事务，假设XID是A
    2.  接下来执行2^64次查询语句，让global\_query\_id回到A
    3.  再启动一个事务，这个事务的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数据可见性的核心思想：
    
    1.  每一行数据都记录了更新它的trx\_id
    2.  当一个事务读到一行数据的时候，判断数据可见性的方法
    3.  事务的一致性视图和这行数据的trx\_id做对比
5.  对于正在执行的事务，可以通过information\_schema.innodb\_trx看到事务的trx\_id。

<table> 
 <thead> 
  <tr> 
   <th>时刻</th> 
   <th>SessionA</th> 
   <th>SessionB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>T1</td> 
   <td><code>BEGIN;SELECT * FROM t LIMIT 1;</code></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T2</td> 
   <td></td> 
   <td><code>USE information_schema;SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;</code></td> 
  </tr> 
  <tr> 
   <td>T3</td> 
   <td><code>INSERT INTO t VALUES (null);</code></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>T4</td> 
   <td></td> 
   <td><code>SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;</code></td> 
  </tr> 
 </tbody> 
</table>

1.  trx\_mysql\_thread\_id=30就是线程ID，即session A所在的线程
2.  T1时刻，trx\_id的值其实为0，而很大的值只是为了显示用的（区别于普通的读写事务）
3.  T2时刻，trx\_id是一个很大的数字，因为在T1时刻，session A并未涉及更新操作，是一个只读事务。**对于只读事务，InnoDB不会分配trx\_id**
4.  session A在T3时刻执行INSERT语句时，InnoDB才真正分配trx\_id

### 只读事务 ###

1.  在上面的T2时刻，很大的trx\_id是由系统临时计算出来的。 **把当前事务的trx变量的指针地址转成整数，再加上2^48**
2.  同一个只读事务在执行期间，它的指针地址是不会变的。 **不论是在innodb\_trx还是innodb\_locks表里，同一个只读事务查出来的trx\_id都是一样的**
3.  如果有多个并行的只读事务，每个事务的trx变量的指针地址肯定是不同的。 **不同的并发只读事务，查出来的trx\_id是不同的**
4.  加上2^48的目的：保证只读事务显示的trx\_id值比较大，用于区别普通的读写事务
5.  trx\_id与row\_id的逻辑类似，定义长度为8 Bytes
    
    1.  在理论上，可能会出现一个读写事务与一个只读事务显示的trx\_id相同的情况
    2.  但概率极低，并且没有什么实质危害
6.  只读事务不分配trx\_id的好处
    
    1.  可以减少事务视图里面活跃数组的大小。 当前正在运行的只读事务，是不影响数据的可见性判断。因此，在创建事务的一致性视图时，只需要拷贝读写事务的trx\_id
    2.  可以减少trx\_id的申请次数。
        
        1.  在InnoDB里，即使只执行一条普通的SELECT语句，在执行过程中，也要对应一个只读事务。
        2.  如果普通查询语句不申请trx\_id，就可以大大减少并发事务申请trx\_id的锁冲突
        3.  由于只读事务不分配trx\_id，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.  thread\_id\_counter定义为4 Bytes，因此达到2^32-1后就会重置为0
    
    1.  但不会在SHOW PROCESSLIST里面看到两个相同的thread\_id
    2.  因为MySQL设计了一个唯一数组的逻辑，给新线程分配thread\_id，逻辑代码如下

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

PS：参考资料 《MySQL实战45讲》