MySQL大表添加STORED生成列超时，求可行解决方案

对于4500万行的大表，添加STORED生成列耗时极长是正常的——因为STORED列需要逐行计算done_at的日期值并写入磁盘，这会产生大量IO和计算开销，很容易触发超时。以下是几种可行的解决方案：

方案一：普通列+分批更新+触发器（简单易操作） #

绕开直接添加STORED列的高开销，分步完成需求：

1. 快速添加空date列
   InnoDB在MySQL 5.6及以上版本支持异步添加空列，这个操作几乎瞬间完成：

   ALTER TABLE activities ADD COLUMN done_on date NULL;
   

2. 分批更新历史数据
   一次性更新全表会锁表且超时，分批次处理（每次1万行，可根据服务器性能调整）：

   SET @rows_updated = 1;
   WHILE @rows_updated > 0 DO
       UPDATE activities 
       SET done_on = CAST(done_at AS DATE)
       WHERE done_on IS NULL
       LIMIT 10000;
       SET @rows_updated = ROW_COUNT();
   END WHILE;
   

   建议在业务低峰期执行，避免影响线上业务。

3. 添加约束并建立触发器维护一致性
   如果需要done_on非空，先确保无空值再修改约束：

   UPDATE activities SET done_on = CAST(done_at AS DATE) WHERE done_on IS NULL;
   ALTER TABLE activities MODIFY COLUMN done_on date NOT NULL;
   

   创建触发器，让后续插入/更新自动同步done_on与done_at：

   DELIMITER //
   CREATE TRIGGER trg_activities_insert_done_on BEFORE INSERT ON activities
   FOR EACH ROW SET NEW.done_on = CAST(NEW.done_at AS DATE);
   //
   CREATE TRIGGER trg_activities_update_done_on BEFORE UPDATE ON activities
   FOR EACH ROW SET NEW.done_on = CAST(NEW.done_at AS DATE);
   //
   DELIMITER ;
   

4. 创建索引满足查询需求
   现在可以给done_on创建索引：

   CREATE INDEX idx_activities_done_on ON activities(done_on);
   

方案二：在线DDL工具（无锁操作，适合高并发业务） #

如果业务不能中断，推荐使用Percona的pt-online-schema-change工具，实现无锁表结构修改：

1. 安装Percona Toolkit（例如通过yum install percona-toolkit或对应系统包管理器）
2. 执行在线修改命令：

   pt-online-schema-change \
     --alter="ADD COLUMN done_on date GENERATED ALWAYS AS (CAST(done_at AS DATE)) STORED" \
     D=你的数据库名,t=activities \
     --execute
   

   该工具会创建临时表逐行复制数据，同时通过触发器同步增量变更，最后替换原表，全程几乎不阻塞业务。

额外优化建议 #

• 操作前务必备份表，规避数据风险；
• 执行分批更新时，可临时关闭binlog（SET sql_log_bin=0;）减少IO开销（前提是不需要同步到从库）；
• 调整innodb_buffer_pool_size，让更多数据缓存到内存，降低磁盘IO压力。

内容的提问来源于stack exchange，提问作者jibai31