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

一、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_script
FROM(
SELECT _tidb_rowid AS rid, ROW_NUMBER()over(
ORDER BY _tidb_rowid) AS rk
FROM t
 ) a
GROUP 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_script
FROM(
SELECT _tidb_rowid AS rid, ROW_NUMBER()over(
ORDER BY _tidb_rowid) AS rk
FROM t
 ) a
GROUP 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。

show variables like '%tidb_mem_quota_query%'

SET tidb_mem_quota_query = 8 << 30; 设置单个 session 内存使用最大为 8G；

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';