TiDB：AUTO_INCREMENT 实现连续自增的改造要点

作者： cchouqiang 原文来源：https://tidb.net/blog/846b2efb

一、AUTO_INCREMENT 简介

AUTO_INCREMENT 是用于自动填充缺省列值的列属性。当 INSERT 语句没有指定 AUTO_INCREMENT 列的具体值时，系统会自动地为该列分配一个值。 出于对性能考虑，系统批量分配给每台 TiDB 服务器的自增序列值（默认 3 万个值），自增编号能保证全局唯一性，并且保证在单个 TiDB 节点上具有连续单调递增，但无法保证在所有 TiDB 节点上实现全局连续递增。

二、AUTO_INCREMENT ****MySQL 兼容模式

从 v6.4.0 开始，TiDB 实现了中心化分配自增 ID 的服务，可以支持 TiDB 实例不缓存数据，而是每次请求都访问中心化服务获取自增 ID。

TiDB 数据库实现 AUTO_INCREMENT 全局连续递增的方法是：建表时将 AUTO_ID_CACHE 设置为 1

CREATE TABLE t(a int AUTO_INCREMENT key) AUTO_ID_CACHE 1;

注意： 在 TiDB 各个版本中，AUTO_ID_CACHE 设置为 1 都表明 TiDB 不再缓存 ID，但是不同版本的实现方式不一样： 对于 TiDB v6.4.0 之前的版本，由于每次分配 ID 都需要通过一个 TiKV 事务完成 AUTO_INCREMENT 值的持久化修改，因此设置 AUTO_ID_CACHE 为 1 会出现性能下降。 对于 v6.4.0 及以上版本，由于引入了中心化的分配服务，AUTO_INCREMENT 值的修改只是在 TiDB 服务进程中的一个内存操作，相较于之前版本更快。 将 AUTO_ID_CACHE 设置为 0 表示 TiDB 使用默认的缓存大小 30000。

使用 MySQL 兼容模式后，能保证 ID 唯一、单调递增，行为几乎跟 MySQL 完全一致。即使跨 TiDB 实例访问，ID 也不会出现回退。只有当中心化服务的“主” TiDB 实例异常崩溃时，才有可能造成少量 ID 不连续。这是因为主备切换时，“备” 节点需要丢弃一部分之前的“主” 节点可能已经分配的 ID，以保证 ID 不出现重复。

解决方法及风险点

方案一：使用 MySQL 模式，将 AUTO_ID_CACHE 设置为 1（默认 3w） TiDB 实现了中心化分配自增 ID 的服务，可以支持 TiDB 实例不缓存数据，而是每次请求都访问中心化服务获取 ID。 使用 MySQL 兼容模式后，能保证 ID 唯一、单调递增，行为跟 MySQL 完全一致。即使跨 TiDB 实例访问，ID 也不会出现回退。

风险点：

（1）当前生产表无法直接修改，只有新创建表才能生效，需要在业务停机窗口操作

（2）如果使用该方式，建议先在测试环境验证无误后，生产操作。

（3）使用该方式对集群性能略有消耗，另外，tidb 实例挂掉或异常宕机后，有可能会造成部分 id 不连续。

方案二：业务层面，能通过业务逻辑进行调整。

三、使用限制

目前在 TiDB 中使用 AUTO_INCREMENT 有以下限制：

• 对于 v6.6.0 及更早的 TiDB 版本，定义的列必须为主键或者索引前缀。

• 只能定义在类型为整数、FLOAT 或 DOUBLE 的列上。

• 不支持与列的默认值 DEFAULT 同时指定在同一列上。

• 不支持使用 ALTER TABLE 来添加 AUTO_INCREMENT 属性，包括使用 ALTER TABLE ... MODIFY/CHANGE COLUMN 语法为已存在的列添加 AUTO_INCREMENT 属性，以及使用 ALTER TABLE ... ADD COLUMN 添加带有 AUTO_INCREMENT 属性的列。

• 支持使用 ALTER TABLE 来移除 AUTO_INCREMENT 属性。但从 TiDB 2.1.18 和 3.0.4 版本开始，TiDB 通过 session 变量 @@tidb_allow_remove_auto_inc 控制是否允许通过 ALTER TABLE MODIFY 或 ALTER TABLE CHANGE 来移除列的 AUTO_INCREMENT 属性，默认是不允许移除。

• ALTER TABLE 需要 FORCE 选项来将 AUTO_INCREMENT 设置为较小的值。

• 将 AUTO_INCREMENT 设置为小于 MAX(<auto_increment_column>) 的值会导致重复键，因为预先存在的值不会被跳过。

四、全局连续自增改造方法

由于 AUTO_ID_CACHE 只能在建表时设置为 1，不能通过 alter table 语句来修改 AUTO_ID_CACHE 的值，因此对已有数据的表需要通过数据迁移方式来修改，迁移过程中该表的业务需要停止写入。

1、创建中间表 t_tmp

show create table t;#t_tmp表结构跟t表一样，t_tmp表增加 “AUTO_ID_CACHE 1”CREATE TABLE t_tmp(a int AUTO_INCREMENT key,b varchar(10),c int) AUTO_ID_CACHE 1;

2、查看表中数据量

select count(*) from t;

3、将业务数据迁移到新表中（业务停止写入）

insert into t_tmp select * from t;

若想把之前不连续的自增列修改为全局连续递增，可以使用如下语句进行插入

insert into t_tmp(b,c) select b,c from t;

当数据量较多时，如几亿数据，可以通过分批的方式迁移

SELECT CONCAT('insert into t_tmp select * from t where _tidb_rowid between ', MIN(rid),' and ', MAX(rid),' ;') AS insert_scriptFROM(SELECT _tidb_rowid AS rid, ROW_NUMBER()over(ORDER BY _tidb_rowid) AS rkFROM t ) aGROUP BY FLOOR((rk-1)/500000);
结果集过多可以输出到文件中，或使用客户端mysql -u username -p -D database_name -e "SELECT CONCAT('insert into t_tmp select * from t where _tidb_rowid between ', MIN(rid),' and ', MAX(rid),' ;') AS insert_scriptFROM(SELECT _tidb_rowid AS rid, ROW_NUMBER()over(ORDER BY _tidb_rowid) AS rkFROM t ) aGROUP BY FLOOR((rk-1)/500000);" > /path/to/your.log

调大 tidb_mem_quota_query 变量

• 在 v6.5.0 及之后的版本中，该变量用来设置单个会话整体的内存使用限制，如果某个会话执行过程中使用的内存量超过该阈值，会触发系统变量 tidb_mem_oom_action 中指定的行为。需要注意的是，自 v6.5.0 版本开始，会话的内存使用量包含会话中事务所消耗的内存。v6.5.0 及之后版本对事务内存的控制行为有所变化，详见 txn-total-size-limit。

4、修改表名

将原业务表名修改为备份表名，将新表修改为业务表名

alter table t rename to t_bk;  alter table t_tmp rename to t;

5、校验数据

select count(*) from t;select count(*) from t_bk;

6、收集新表统计信息

analyze table t;
show analyze status;

7、查看表健康度

show stats_healthy where table_name='t';