InnoDB自增处理

vonlinee

已于 2024-12-12 21:05:46 修改

阅读量733

点赞数 9

分类专栏： MySQL 文章标签：数据库 mysql

于 2024-10-20 21:50:27 首次发布

本文链接：https://blog.youkuaiyun.com/qq_40926260/article/details/143097378

版权

MySQL 专栏收录该内容

27 篇文章

订阅专栏

文章目录

自增相关的sql
自增锁模式 innodb_autoinc_lock_mode
自增计数器
自增值用完怎么办

摘自：https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

InnoDB提供了一种可配置的锁机制，可以显著提高向具有自增列的表添加行的SQL语句的可扩展性和性能。要将自增机制用于InnoDB表，必须将AUTO_INCREMENT列定义为某个索引的第一列或唯一的一列，以便可以在表上执行类似SELECT MAX (ai_col)查询的等效操作来获得自增列当前的最大列值，这里ai_col就是自增的那一列。索引可以不是PRIMARY KEY或UNIQUE，但为了避免AUTO_INCREMENT列中的重复值，建议使用这些索引类型。

自增相关的sql

下面先对自增相关的sql类型做一下概念分类，本文后面会用到这些概念

INSERT-like

所有会导致表中产生新行的sql语句，例如INSERT、INSERT ... SELECTREPLACE、REPLACE ... SELECT和LOAD DATA。

简单插入

待插入的行数已经提前可以确定了，包括单行插入，多行插入以及没有嵌套子查询的REPLACE语句，但不包含INSERT ... ON DUPLICATE KEY UPDATE。例如

INSERT INTO t (id, col1) VALUES (1, 1);
INSERT INTO t (id, col1) VALUES (1, 1), (2, 2) ...;

批量插入

待插入的行数不确定，例如INSERT ... SELECT、REPLACE ... SELECT以及LOAD DATA，不包含普通的INSERT语句，例如：

INSERT INTO t SELECT * FROM t2 WHERE id < 1000 AND id > 100;

混合模式插入

这种其实就是简单插入类型，只不过对于自增列指定了自增值，但是并非所有插入的行都指定了

INSERT INTO t1 (id, c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

如果全都指定了，则不算混合模式插入类型，全都指定了就和自增没关系了，不需要处理自增。

INSERT ... ON DUPLICATE KEY UPDATE也属于混合模式插入这一类型，这种sql有点特殊，最坏的情况下，实际上是INSERT和UPDATE两个操作，而在UPDATE阶段可以使用也可以不使用INSERT为AUTO_INCREMENT列分配的自增值。

自增锁模式 innodb_autoinc_lock_mode

变量innodb_autoinc_lock_mode控制自增锁使用的算法，它允许选择如何在可预测的自动递增值序列和插入操作的最大并发性之间进行权衡。innodb_autoinc_lock_mode变量有3种可选值：0，1或者2

0：traditional
1：consecutive
2：interleaved

MySQL 8.0，consecutive 是默认值，也就是2，8.0 以前默认值为 1，这个变化和主从复制从基于语句的复制到基于行的复制作为默认复制类型的变化有关。基于语句的复制需要连续的自增锁模式，以确保为给定的SQL语句序列以可预测和可重复的顺序分配自增值，而基于行的复制对SQL语句的执行顺序不敏感，因为行数据中已经有自增值了。

mysql> SELECT @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          2 |
+----------------------------+
1 row in set (0.00 sec)

0 - traditional

这个模式就是有innodb_autonic_lock_mode这个变量之前的自增处理行为，主要是为了后向兼容性

在这种模式下，插入数据到具有AUTO_INCREMENT列的表时，所有INSERT-like类型的sql都会先加一个特殊的AUTO-INC锁，这个锁是表锁。这个锁通常在该sql语句结束时就被释放，而不是事务结束才释放，以确保对给定的INSERT语句序列以可预测和可重复的顺序分配自增值，并确保任何给定语句分配的自增值都是连续的。

1 - consecutive

INSERT ... SELECT，REPLACE ... SELECT以及LOAD DATA这些sql会用到AUTO-INC表锁，同一时间只能执行一个持有AUTO-INC锁的sql。如果批量插入操作的源表与目标表不同，则在对从源表中选择的第一行加一个共享锁，然后对目标表加AUTO-INC锁。如果批量插入操作的源和目标是同一个表，则在对所有选定行加共享锁后，然后再加AUTO-INC锁。

对于预先知道要插入的行数的sql，会使用一个互斥的Mutex，这是一个轻量级的锁，通过它的控制可以获得所需数量的自增值，从而避免表级的AUTO-INC锁，Mutex仅在分配过程中保持，直到语句完成时进行释放。除非另一个事务持有AUTO-INC锁，否则不会使用表级AUTO-INC锁。如果另一个事务持有AUTO-INC锁，则简单插入会等待AUTO-INC锁，此时就和批量插入一样了。

这个模式可确保在存在行数未知的INSERT语句的情况下（以及在语句进行过程中分配自动递增编号的情况下），任何INSERT-like类型的sql分配的所有自增值都是连续的，并且这个操作对于基于Statement的复制是安全的.

简单地说，这种锁定模式显著提高了可扩展性，同时可以安全地用于基于语句的复制。此外，与traditional模式一样，任何给定sql分配的自增值都是连续的。

2 - interleaved

在这种模式下，INSERT-like类型的语句不会使用表级的AUTO-INC锁，并且可以同时执行多个语句。这种模式是最快、最可扩展的模式。

但在使用基于语句的复制或恢复场景时，当从binlog中重放SQL语句时，它是不安全的。
这种模式下，自增值保证是唯一的，并且在所有并发执行的INSERT-like类型的语句中单调递增。然而，由于多个语句可以同时生成自增值，因此单条sql插入的行生成的自增值可能不是连续的。

如果执行的唯一语句是简单插入类型，其中要插入的行数是提前知道的，那么除了混合模式插入之外，为单个sql生成的自增值中没有间隙。当执行批量插入时，任何给定sql分配的自增值都可能存在间隙。

自增计数器

初始化计数器

在崩溃恢复期间重新启动服务器时，InnoDB会使用数据字典中存储的当前最大自增值初始化内存中的自增计数器，并扫描redo log查找自上次checkpoint以来写入的自增计数值。如果redo log里面的值大于内存中的计数器值，则应用redo log里面存的值。但是在服务器意外退出的情况下，无法保证重用以前分配的自增值。由于每次INSERT或UPDATE操作而更改当前最大自增值时，新值都会写入redo log，但如果在将redo log刷新到磁盘之前发生意外而退出，则服务器重新启动后，自增计数器初始化时可以重用之前分配的值。

查看自增计数

注意，不能仅从当前表中的数据观察得出当前自增值

可以通过几种方法查看表的当前自增计数（AUTO_INCREMENT 值）。以下是几种常见的方法：

1.使用 SHOW TABLE STATUS

以film表为例，通过SHOW TABLE STATUS LIKE 'film';进行查看

mysql> SHOW TABLE STATUS LIKE 'film'\G;
*************************** 1. row ***************************
           Name: film
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1000
 Avg_row_length: 196
    Data_length: 196608
Max_data_length: 0
   Index_length: 81920
      Data_free: 0
 Auto_increment: 1000    # 当前自增值
    Create_time: 2024-09-27 02:49:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

2.使用 SELECT 查询 information_schema

查询sql如下：

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'sakila' AND TABLE_NAME = 'film';

3.插入数据后查看

如果您想在插入新记录后查看自增计数，可以通过以下步骤：

INSERT INTO your_table_name (column1, column2) VALUES (value1, value2);
# 查询插入后的自增计数, 这个查询将返回最近插入记录的自增 ID
SELECT LAST_INSERT_ID();

修改自增计数

可以通过ALTER TABLE ... AUTO_INCREMENT = N来修改当前的自增计数值，注意，只能改大不能改小，改小不会报错

mysql> ALTER TABLE film AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

自增值用完怎么办

一般用不完的，别瞎操心了。。。

当AUTO_INCREMENT整数列的值用完时，后续的INSERT操作将返回Duplicate Key错误。

自增值用完时，可以通过改变数据类型、重置自增值、清空表、归档数据或使用 UUID 等方式来处理。选择合适的策略可以确保数据库的正常运行。以下是处理自增值用完的几种方法：

1.数据类型选择

首先，确保选择的列数据类型能够满足预期的最大值。例如：

TINYINT：最大值 255
SMALLINT：最大值 65535
MEDIUMINT：最大值 16777215
INT：最大值 2147483647
BIGINT：最大值 9223372036854775807，64 位有符号整数的最大值

如果预计会超过当前数据类型的最大值，可以考虑将自增列的数据类型更改为更大的类型（如从 INT 更改为 BIGINT）。

ALTER TABLE your_table_name MODIFY your_column BIGINT;

2.重置自增值

如果表中的数据被删除，您可以重置自增值，使用 ALTER TABLE 语句来设置下一个自增值。例如，将下一个自增值设置为 1：

ALTER TABLE your_table_name AUTO_INCREMENT = 1;

3.清空表

如果您不再需要表中的数据，可以使用 TRUNCATE 清空表并重置自增值

TRUNCATE TABLE your_table_name;

4.处理数据溢出

如果您发现自增值即将用尽，可以采取以下措施：
数据归档：将旧数据转移到其他表或数据库，以释放自增值。
分区表：使用表分区，将数据分散到多个逻辑表中，以延长自增值的使用。
使用 UUID：如果可以接受使用非整数自增值，可以考虑使用 UUID 作为主键，这样可以避免自增值用尽的问题。

5.监控和预警

定期监控自增值的使用情况，并设置预警，以便可以提前采取措施，防止自增值用尽。