|
|
假设前提条件
- 假设字段类型设计不合理
- 最大程度保证线上程序正常运行
- 保证数据不丢失
- 保证原ID不变改
自增ID消耗完可能原因：
- 业务写入数据量巨大
- 频繁写入、删除，继续写入再删除...
- 历史原因 ...
假设自增ID字段数据类型不符合业务实际使用场景，如使用TINYINT、SMALLINT、MEDIUMINT、INT
MySQL Integer 字段数值范围
| 类型 | 字节数 (Bytes) | 有符号最小值 | 无符号最小值 | 有符号最大值 | 无符号最大值 | | TINYINT | 1 | -128 | 0 | 127 | 255 | | SMALLINT | 2 | -32768 | 0 | 32767 | 65535 | | MEDIUMINT | 3 | -8388608 | 0 | 8388607 | 16777215 | | INT | 4 | -2147483648 | 0 | 2147483647 | 4294967295 | | BIGINT | 8 | -263 | 0 | 263-1 | 264-1 |
解决方法
1. 直接修改表字段类型
如果使用TINYINT、SMALLINT(不论是否无符号)，大胆改表结构。可以根据实际业务预估的数据量修改为更大的类型，因为数量本身不大，锁表时间也不会很长。
ALTER TABLE `table_name` CHANGE `id` `id` bigint(20) Unsigned NOT NULL auto_increment;
Query 1 ERROR: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
2. 重命名迁移：大于MEDIUMINT的数据类型，分步修改结构
此时表数据量已经比较大。如需要保证线上业务正常，直接修改表结构，在修改过程中MySQL可能会进行重建表，以及数据逐行复制等操作，将导致表长时间无法写入和读取
此方法优点：
- 只有在重命名是才锁定表，而重命名本身很快
- 导入数据分两步：导入存量数据到新表；锁定表写入并导入新增数据
- 完整保留原ID和数据
重命名迁移
- 创建与原表结构一致的新表，ID字段修改为合适的类型，为表创建合适的索引
- 将原表数据写入新表
- 保证在同一事务执行，且锁定旧表。重命名旧表为_bak，重命新表为旧表名
-- 基于原表结构创建一个新表
CREATE TABLE `stats_macro_indicator_new` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`dt` date DEFAULT NULL COMMENT '日期',
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `IDX_MULITPLE_COLS` (`dataset_code`,`indicator_code`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--导入已有数据到新表
INSERT INTO stats_macro_indicator_new
SELECT * FROM stats_macro_indicator
-- 重命名,ALGORITHM = INSTANT(MySQL8.0)后续说明
-- 禁用自动提交事务
SET autocommit=0;
-- 锁定原表数据写入
LOCK TABLES stats_macro_indicator WRITE ;
--开始事务
START TRANSACTION;
--补全数据
INSERT INTO stats_macro_indicator_new SELECT * FROM stats_macro_indicator WHERE id > (SELECT MAX(id) FROM stats_macro_indicator_new);
--重命名（LOCK = DEFAULT 可以不需要）
ALTER TABLE stats_macro_indicator RENAME TO stats_macro_indicator_bak, ALGORITHM = INSTANT , LOCK = DEFAULT;
ALTER TABLE stats_macro_indicator_new RENAME TO stats_macro_indicator, ALGORITHM = INSTANT, LOCK = DEFAULT;
COMMIT;
UNLOCK TABLES;
延伸阅读 - MySQL 8.0：InnoDB Instant 即时添加列
从MySQL 5.6 开始支持 INPLACE DDL的版本。在这之前执行 DDL的唯一方法是逐行复制。INPLACE DDL 主要由 InnoDB 处理，而 COPY 逐行在服务器层处理
8.0开始 MySQL支持 ALGORITHM=INSTANT 即时算法，INSTANT算法的好处是只在数据字典中进行元数据更改。在更改期间不需要获取元数据锁，不接触表的数据。无需为 INSTANT 算法指定 LOCK。在 ALGORITHM=INSTANT 的情况下，LOCK 不能设置为 DEFAULT 以外的任何其他值，否则会出现错误。
各DDL算法对比
InnoDB 支持INSTANT算法的DDL操作：
- 修改索引
- 重命名表（以 ALTER 方式）
- 设置/删除列的默认值
- 修改列（列数据类型修改不支持）
- 添加/删除虚拟列（可以理解为自动计算列）
- 添加列（非生成的）—— 即时添加列
使用 ALGORITHM=INSTANT 在单个语句中执行以上多个DDL的组合 值得一提，值得一赞的是INSTANT ADD COLUMN 补丁由腾讯游戏 DBA 团队贡献
更多关于INSTANT介绍：MySQL 8.0: InnoDB now supports Instant ADD COLUMN |
|
发表于 2023-1-1 19:32:48