MySQL大表添加新字段

开发过程，经常需要给表添加字段名，在测试环境数据少很快就添加好了，但是在生产环境就要小心了，线上的数据库有很多表数据是百万级、千万级, 添加字段就会慢很多，且很容易锁表。

下面使用pt-online-schema-change添加字段 避免生产业务中断

原理

首先它会新建一张一模一样的表，表名一般是_为前缀_new后缀,例如原表为t_user 临时表就是_t_user_new
然后在这个新表执行更改字段操作
然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执行的语句也在新表中执行
最后将原表的数据拷贝到新表中，然后替换掉原表

添加字段

SQL命令

ALTER TABLE TABLE_NAME ADD add COLUMN pre_delivery_date DATETIME DEFAULT null COMMENT '预计发货时间' after is_pre_sale;

试运行

pt-online-schema-change --host=localhost --socket=/tmp/mysql.sock -uroot -pPASSWORD --alter "add COLUMN pre_delivery_date DATETIME DEFAULT null COMMENT '预计发货时间' after is_pre_sale" D=DB_NAME,t=TABLE_NAME  --print --dry-run

[root@db99 ~]# pt-online-schema-change --host=localhost --socket=/tmp/mysql.sock -uroot -pPassword --alter "add COLUMN pre_delivery_date2 DATETIME DEFAULT null" D=sunday_mall,t='order_detail_temp' --print --dry-run
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Starting a dry run.  `sunday_mall`.`order_detail_temp` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
CREATE TABLE `sunday_mall`.`_order_detail_temp_new` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `supplier_id` bigint(20) NOT NULL COMMENT '供应商ID',
  `delivery_type` int(11) NOT NULL DEFAULT '1' COMMENT '发布状态',
  `storage_id` int(11) NOT NULL DEFAULT '0' COMMENT '商品所在仓库ID',
  `return_order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '退货订单状态',
  `return_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '退货类型',
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `product_id` bigint(20) NOT NULL COMMENT '商品ID',
  `platform_product_id` int(11) NOT NULL DEFAULT '0' COMMENT '网店商品ID',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ben_order_detail_supplier_id_index` (`supplier_id`),
  KEY `ben_order_detail_order_id_index` (`order_id`),
  KEY `order_detail_delivery_type_index` (`delivery_type`),
  KEY `order_detail_storage_id_index` (`storage_id`),
  KEY `order_detail_product_id_at_index` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5039369 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Created new table sunday_mall._order_detail_temp_new OK.
Altering new table...
ALTER TABLE `sunday_mall`.`_order_detail_temp_new` add COLUMN pre_delivery_date2 DATETIME DEFAULT null
Altered `sunday_mall`.`_order_detail_temp_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
INSERT LOW_PRIORITY IGNORE INTO `sunday_mall`.`_order_detail_temp_new` (`id`, `supplier_id`, `spread_supplier_id`, `delivery_type`, `storage_id`, `return_order_status`, `return_type`, `order_id`, `product_id`, `platform_product_id`, `created_at`, `updated_at`, `deleted_at`) SELECT `id`, `supplier_id`, `spread_supplier_id`, `delivery_type`, `storage_id`, `return_order_status`, `return_type`, `order_id`, `product_id`, `platform_product_id`, `created_at`, `updated_at`, `deleted_at` FROM `sunday_mall`.`order_detail_temp` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 27729 copy nibble*/SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sunday_mall`.`order_detail_temp` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
DROP TRIGGER IF EXISTS `sunday_mall`.`pt_osc_sunday_mall_order_detail_temp_del`
DROP TRIGGER IF EXISTS `sunday_mall`.`pt_osc_sunday_mall_order_detail_temp_upd`
DROP TRIGGER IF EXISTS `sunday_mall`.`pt_osc_sunday_mall_order_detail_temp_ins`
2024-07-16T10:35:04 Dropping new table...
DROP TABLE IF EXISTS `sunday_mall`.`_order_detail_temp_new`;
2024-07-16T10:35:04 Dropped new table OK.
Dry run complete.  `sunday_mall`.`order_detail_temp` was not altered.

运行

pt-online-schema-change --host=localhost --socket=/tmp/mysql.sock -uroot -pPASSWORD --alter "add COLUMN pre_delivery_date DATETIME DEFAULT null COMMENT '预计发货时间' after is_pre_sale" D=DB_NAME,t='TABLE_NAME'  --execute

[root@db99 ~]# pt-online-schema-change --host=localhost --socket=/tmp/mysql.sock -uroot -pPASSWORD --alter "add COLUMN pre_delivery_date2 DATETIME DEFAULT null" D=sunday_mall,t='order_detail_temp' --execute
1> Cannot connect to S=/tmp/mysql.sock,h=192.168.77.99,p=...,u=root
No slaves found.  See --recursion-method if host db91 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `sunday_mall`.`order_detail_temp`...
Creating new table...
Created new table sunday_mall._order_detail_temp_new OK.
Altering new table...
Altered `sunday_mall`.`_order_detail_temp_new` OK.
2024-07-16T10:35:39 Creating triggers...
2024-07-16T10:35:39 Created triggers OK.
2024-07-16T10:35:39 Copying approximately 3050680 rows...
Copying `sunday_mall`.`order_detail_temp`:  22% 01:44 remain
Copying `sunday_mall`.`order_detail_temp`:  44% 01:15 remain
Copying `sunday_mall`.`order_detail_temp`:  66% 00:44 remain
Copying `sunday_mall`.`order_detail_temp`:  87% 00:16 remain
2024-07-16T10:37:59 Copied rows OK.
2024-07-16T10:37:59 Analyzing new table...
2024-07-16T10:37:59 Swapping tables...
2024-07-16T10:37:59 Swapped original and new tables OK.
2024-07-16T10:37:59 Dropping old table...
2024-07-16T10:37:59 Dropped old table `sunday_mall`.`_order_detail_temp_old` OK.
2024-07-16T10:37:59 Dropping triggers...
2024-07-16T10:37:59 Dropped triggers OK.
Successfully altered `sunday_mall`.`order_detail_temp`.

修改表字段

ALTER TABLE `tb_test` MODIFY COLUMN `num` int(11) unsigned NOT NULL DEFAULT '0';

pt-online-schema-change --host=localhost --socket=/tmp/mysql.sock -uroot -pPASSWORD --alter "MODIFY COLUMN num int(11) unsigned NOT NULL DEFAULT '0'" D=sunday_mall,t='order_detail_temp' --execute

修改表字段名

ALTER TABLE `tb_test` CHANGE COLUMN age adress varchar(30);

pt-online-schema-change --host=localhost --socket=/tmp/mysql.sock -uroot -pPASSWORD --alter "CHANGE COLUMN age address varchar(30)" D=sunday_mall,t='order_detail_temp' --execute

添加索引

ALTER TABLE `tb_test` ADD INDEX idx_address(address);

pt-online-schema-change --host=localhost --socket=/tmp/mysql.sock -uroot -pPASSWORD --alter "ADD INDEX idx_address(address)" D=sunday_mall,t='order_detail_temp' --execute

这里添加字段的测试表数据量为300W，很稳定就修改了表结构，

注：
一定要在业务低峰期做，这样才能将风险降到最低。